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

About Instructor

KudVenkat

Software Architect, Trainer, Author and Speaker in Pragim Technologies.

Reviews

SQL

Average Rating

0

0 ratings

5 1

Details

5 Stars
0
4 Stars
0
3 Stars
0
2 Stars
0
1 Stars
0

ADD A REVIEW

Name
Email
Review Title
Rating
Review Content

Subscribe Email Alerts

If you wish to receive email alerts when new articles, videos or interview questions are posted on PragimTech.com, you can subscribe by providing your valid email.