logo-mini
  1. Excel Guru
  2. SQL Server

SQL Server

SQL Server is a relational database management system developed by Microsoft. It is used to manage and store large amounts of data for businesses and organizations. Some of its key features include data encryption, data warehousing, and support for big data applications. SQL Server also provides tools for data analysis and reporting, including a business intelligence platform called SQL Server Reporting Services. It is compatible with Windows, Linux, and Azure cloud platforms, making it a versatile and powerful choice for businesses of all sizes. It is also highly scalable, allowing it to handle large volumes of data without slowing down. Overall, SQL Server is a powerful and reliable database management system that is widely used in the business world.

SQL Server

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

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
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