logo-mini
  1. Excel Guru
  2. SQL Server

SQL Server

Enhance Your Data Analytics Career With Real-World Data Science Exercises

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