SQL Server

Course Duration & Payment Details
Days: Tuesday & Thursday
Timing: 07:00 to 08:30pm
Duration: 12 hours
Starting from: Tuesday, 19 January 2021
Last date of registration: Friday, 15 January 2021
Actual Fee: 18000/- | Early bird discount: 9000/-
How to Pay: Online Payment
Bank: Habib Bank Limited (26 street, Badar Commercial, DHA Karachi)
Title: Irfan
Account No: 0011557900292501 | PK02HABB0011557900292501
What You’ll Learn:
If you have no technical background, don’t be afraid! We’ve distilled our knowledge and experience using SQL into a short course so that by the end, you’ll have the raw skills to do some real data analysis for your company using SQL – a language virtually EVERY company users note: this course teaches you real-world SQL – not just the theory in abstract, but real skills you can use to get more data-driven in your current job.
- Complete literal SELECT statements.
- Complete basic SELECT statements querying against a single table
- Filter data using the WHERE clause
- Sort data using the ORDER BY clause
- Learn about database design and normalization
- Using joins, return columns from multiple tables in the same query
- Understand the difference between the INNER JOIN, LEFT/RIGHT OUTER JOIN, and FULL OUTER JOIN
- Complete SQL statements that use aggregate functions
- Group data together using the GROUP BY clause
- Filter groups of data using the HAVING clause
How This Course is Structured:
- In this course we’ll be pretending we’re a real business (i.eBlockbuster) so the stuff you learn will be easy to apply to your own situation/company abstract/theoretical mumbo jumbo.
- We’ll go through queries a real business would run while also teaching you the raw skills undergirding those queries so you can adapt those skills to create custom queries for your own specific purposes
- To reinforce learning, we have exercises and quizzes scattered throughout the course so you can learn by doing
- We’ll have a bonus section where new lectures will be added occasionally (including student-requested lectures, more advanced topics, strategies for getting unstuck, etc.)
- We’ll be actively involved in the discussion board answering any questions you might have! Don’t be afraid to ask!

SQL Server – From the Basic
- Install SQL Server – Step by Step – Developer Edition
- Connecting to SQL Server using SSMS
- Creating altering and dropping a database
- Creating and working with tables
- Adding a default constraint
- Cascading referential integrity constraint
- Adding a check constraint
- Identity Column
- How to get the last generated identity column value
- Unique key constraint
- Select statement
- Group by
- Joins
- Advanced or intelligent joins
- Self-join
- Different ways to replace NULL
- Coalesce function
- Union and union all
- Stored procedures
- Stored procedures with output parameters
- Stored procedure output parameters or return values
- Advantages of stored procedures
- Built-in string functions
- LEFT, RIGHT, CHARINDEX, and SUBSTRING functions
- Replicate, Space, Patindex, Replace, and Stuff string functions
- DateTime functions
- IsDate, Day, Month, Year, and DateTime DateTime functions
- Date, DateAdd, and DateDiff functions
- Cast and Convert functions
- Mathematical functions
- Scalar user-defined functions
- Inline table-valued functions
- Multi statement table-valued functions
- Important concepts related to functions
- Choose function
- IIF function
- TRY PARSE function
- TRY CONVERT function
- EOMONTH function
- DATEFROMS function
- Difference between DateTime and SmallDateTime
- DateTimeFroms function
- Difference between DateTime and DateTime
- Offset fetch next
- Temporary tables
- Indexes
- Clustered and non clustered indexes
- Unique and Non-Unique Indexes
- Advantages and disadvantages of indexes
- Views
- Updatable views
- Indexed views
- View limitations
- DML triggers
- After update trigger
- Instead of insert trigger
- Instead of update triggers
- Derived tables and common table expressions
- CTE
- Updatable common table expressions
- Recursive CTE
- Database normalization
- Second normal form and third normal form
- Pivot
- Error handling
- Transactions
- Transactions and ACID Tests
- Subqueries in SQL
- Correlated subquery in SQL
- Creating a large table with random data for performance testing
- What to choose for performance SubQuery or Joins
- Cursors
- Replacing cursors using joins
- List all tables in a SQL Server database using a query
- Writing re runnable SQL Server scripts
- Alter database table columns without dropping the table
- Optional parameters stored procedures
- Merge
- SQL server concurrent transactions
- SQL server dirty read example
- SQL server lost update problem
- Non-repeatable read example
- Phantom reads example
- Snapshot isolation level
- Read committed snapshot isolation level
- Difference between snapshot isolation and read committed snapshot
- SQL Server deadlock example
- SQL Server deadlock victim selection
- Logging deadlocks
- SQL Server deadlock analysis and prevention
- Capturing deadlocks in SQL profiler
- SQL Server deadlock error handling
- Handling deadlocks in ado net
- Retry logic for deadlock exceptions
- How to find blocking queries
- SQL Server except operator
- Difference between except and not
- Intersect operator
- Difference between union intersect and except
- Cross apply and outer apply
- DDL Triggers
- Server scoped ddl triggers
- SQL server trigger execution order
- Audit table changes
- Logon Triggers
- Select into
- Difference between where and having
- Table-valued parameters
- Send datatable as parameter to stored procedure
- Grouping Sets
- Rollup
- Cube
- Difference between cube and rollup
- Grouping function
- GROUPING ID function
- Debugging sql server stored procedures
- Over clause
- Row Number function
- Rank and Dense Rank
- Difference between rank dense rank and row number in SQL
- Calculate running total
- NTILE function
- Lead and Lag functions
- FIRST VALUE function
- Window functions
- Difference between rows and range
- LAST VALUE function
- UNPIVOT
- Reverse PIVOT table
- Identifying object dependencies
- sys dm SQL referencing entities
- Difference between sequence and identity
- How to check GUID is null or empty
- Dynamic SQL
- Implement search web page using ASP NET and Stored Procedure
- Implement search web page using ASP NET and Dynamic SQL
- Prevent SQL injection with dynamic SQL
- Dynamic SQL in Stored Procedure
- SQL Server query plan cache
- Exec vs sp execute SQL
- Dynamic SQL table name variable
- Quote name function
- Dynamic SQL vs Stored Procedure
- Dynamic SQL output parameter
- Temp tables in dynamic SQL