SQL
SQL Course Contents
Database and Tables Creation
- 1. How to Create database
- 2. How to Create Tables
DML Operations on Tables
- 1. The Different ways of Inserting Data into tables
- 2. How to Update the table values
- 3. How to Delete the records from table
- 4. How to Drop the table
- 5. Difference between Truncate and Delete command
- 6. How to Get the structure of the table
- 7. How to take backup of the table
SQL Constraints
- 1. What are SQL Constraints
- 2. Primary Key
- 3. Unique Key
- 4. Not Null
- 5. Default
- 6. Check Constraint
- 7. Foreign Key Constraint
- 8. How to Create Composite Primary Key
- 9. Candidate Key
Operators
- 1. In, not in,=,>=,<=,<>,like, between, if exists ,And, Or,Like
- 2. Usage of Wild character operators
- 3. How to Use In, Not In and Between Operators
- 4. What are Alias names and the usage of Alias Names
- 5. How to Limit the Number of records
Group By
- 1. Group By with Having clause
- 2. When to Use Group by Having
- 3. Difference between Having and where clause
- 4. How to Use Case statement
Clauses
- 1. What are Clauses in SQL
- 2. What is the Order of execution of Clauses
Joins and Unions
- 1. Different types of Joins with examples
- 2. Explanation about Union and UNION All with examples
- 3. When to use Union with real time scenario
Intersect and Except
- 1. what is Intersect
- 2. what is Except
Schema
- 1. How to Create Schema
- 2. How to Create a table for a particular schema
- 3. How to Transfer table from one schema to other
Adding, Modifying and Drop a column on the existing table
- 1. How to Alter a column by using SQL query
- 2. How to Add, Drop a column by using SQL query
- 3. How to add a Primary Key constraint on an Existing table
Table to table update
System Functions
- 1. Scalar functions
- 2. Aggregate functions
Date Functions
- 1. Dateadd
- 2. Datediff
- 3. DatePart
- 4. DateName
- 5. Month Start Date
- 6. Month End Date
String Functions
- 1. Char
- 2. Ascii
- 3. Left, Right
- 4. Len, data length
- 5. Ltrim, Rtrim
- 6. Replace, replicate, Reverse
- 7. CharIndex, PatIndex
- 8. Substring, Stuff(String,start,end,str2replace)
- 9. Coalesce
- 10. Concat
- 11. Ceiling
- 12. Floor
Numeric Functions
- 1. Abs, Log, Log10
- 2. Power, round, SQRT
RANK Functions
- 1. Row_Number
- 2. Rank
- 3. Dense Rank
- 4. Ntile
Window Functions
- 1. LAG, Lead, First_value, Last_Value, Running_Total
Other Functions
- 1. @@Version,@@Rowcount, db_id
- 2. Cast function, Convert and Is NULL
Identity Column
- 1. How to Impose Identity values in a table
- 2. How to get the last inserted Identity seed value in a database
- 3. @@Identity, Scope_Identity(), Ident_Current()
Sequence Generator:
- 1. How to impose Identity values in a table Using Sequence
Views
- 1. How to create, alter and drop Views
- 2. Indexed views
Temp Tables
- 1. Global Temp Tables
- 2. local Temp Tables
- 3. Table Variables
- 4. Difference between Global, Temporary and Table Variable
- 5. CTE'S
- 6. Recursive CTE
- 7. How to Identify and remove the duplicate values
Sub Queries
- 1. Scalar and Multivalve Sub Queries
- 2. Correlated sub queries
- 3. Derived tables
Merge Statement with example
- 1. Merge statement Syntax
- 2. How to do Incremental Load using Merge in SQL
Cursors
- 1. How to create Cursors
Pivot and unpivot
- 1. How to use Pivot and Unpivot with example
Transactions
- 1. Transaction Syntax
- 2. What are Rollback transactions
- 3. what are Nested Transactions with examples
- 4. How to find number of Open transactions
- 5. Different Types of Transaction available with examples
- 6. Auto commit
- 7. Explicit transaction
- 8. Implicit transaction
Isolation Levels
- 1. What are ACID Properties
- 2. Different types of Isolation Levels with examples
- 3. Read Uncommitted
- 4. Read committed
- 5. Repeatable Read
- 6. Serializable
- 7. Snapshot
Locks
- 1. Locks granularity
- 2. Shared Lock (s),Exclusive Lock (x), Update Lock (u)
- 3. Different levels where we can apply Locks Granularity
- 4. Row level, Table level
- 5. Page level, Extent level,Key level
Dead Locks
- 1. What is Dead Lock
- 2. How to Identify Dead Lock through SQL Profiler
- 3. How to resolve when Dead Lock occurs
Stored Procedures
- 1. How to create stored procedures without parameters
- 2. How to create stored procedures with parameters
- 3. How to create stored procedures with default parameters
- 4. How to create stored procedures with output or Return parameters
- 5. Nested Procedure
User Defined Functions
- 1. Scalar valued functions ,Inline table functions, Multi value functions with examples
- 2. Difference between Stored Procedure and Functions
Triggers
- 1. Instead of Triggers and After Triggers
- 2. How Triggers used for Audit Purpose
- 3. Magic tables
- 4. Inserted and Deleted tables with examples
- 5. DML and DDL triggers
Indexes
- 1. Clustered Index and Non Clustered Index with examples
- 2. Unique Indexes
- 3. Column Stored Indexes
- 4. Difference between Clustered and Non Clustered Index
- 5. What are Table Scan and Index scan
- 6. What is Index seek
- 7. Difference between Table Scan and Index Seek
Normalization
- 1. What are First Normal form, Second Normal form, Third Normal form ,BCNF OR Fourth normal form with examples
Dealing with XML Data
- 1. Dealing with XML
- 2. What are different types XML Data Formats
Taking Backup and Restore
- 1. How to Take Back up and Restore of a database
- 2. How to Attach and Detach a database
How to create dynamic SQL
- 1. Creating dynamic sql
- 2. How to execute dynamic sql
Error handling
- 1. Using Try Catch block
Table Partitioning
- 1. How to Partition a table which is having High volume of data
- 2. How to create File groups, Partition Function and Partition Scheme
- 3. How to map the Partition schemas to the Secondary files
Linked Servers
- 1. How to create Linked server
- 2. How to create Synonyms
Query Optimization Performance Tuning:
- 1. Will explain 25 tips to improve sql query performance
Sql Profiler: How to trace the queries using sql profiler
-CDC (Change Data Capture) in sql
