Data Analytics

 

Database and Tables Creation

·      How to Create database

·      How to Create Tables

 

DML Operations on Tables

·      The Different ways of Inserting Data into tables

·      How to Update the table values

·      How to Delete the records from table

·      How to Drop the table

·      Difference between Truncate and Delete command

·      How to Get the structure of the table

·      How to take backup of the table

 

SQL Constraints

·      What are SQL Constraints

·      Primary Key

·      Unique Key

·       Not Null

·      Default

·      Check Constraint

·      Foreign Key Constraint

·      How to Create Composite Primary Key

·      Candidate Key

 

Operators

·      In, not in,=,>=,<=,<>,like, between, if exists ,And, Or,Like

·      Usage of Wild character operators

·      How to Use In, Not In and  Between Operators

·      What are Alias names and the usage of Alias Names

·      How to Limit the Number of records

 

Group By

·      Group By with Having clause

·      When to Use Group by Having

·      Difference between Having and where clause

·      How to Use Case statement

 

Clauses

·      What are Clauses in SQL

·      What is the Order of execution of Clauses

 

Joins and Unions

·      Different types of Joins with examples

·      Inner join, Left join, Right join, Full outer join, Cross join, Self Join

·      Explanation about Union and UNION All with examples

·      When to use Union with real time scenario

 

Schema

·      How to Create Schema

·      How to Create a table for a particular schema

·      How to Transfer table from one schema to other

 

Adding, Modifying and Drop a column on the existing table

·      How to Alter a column by using SQL query

·      How to Add, Drop a column by using SQL query

·      How to add a Primary Key constraint on an Existing table

Table to table update

 

System Functions

·      Scalar functions

·      Aggregate functions

 

Date Functions

·      1.Dateadd

·      2.Datediff

·      3.DatePart

·      4.DateName

·      Month Start Date

·      Month End Date

 

String Functions

·      Char

·      Ascii

·      Left, Right

·      Len,  data length

·      Ltrim, Rtrim

·      Replace, replicate, Reverse

·      CharIndex, PatIndex

·      Substring, Stuff(String,start,end,str2replace)

·      str

·      Coalesce

 

·      Numeric Functions

 

·      Abs, Log, Log10

·      Power, round, SQRT,Floor,Ceiling

 

RANK Functions

·      Row_Number

·      Rank

·      Dense Rank

·      Ntile

 

Window Functions

·      LAG, Lead, First_value, Last_Value, Running_total

 

Other Functions

·      @@Version,@@Rowcount, db_id

·      Cast function, Convert and Is NULL

 

Identity Column

·      How to Impose Identity values in a table

·      How to get the last inserted Identity seed value in a database

·      @@Identity, Scope_Identity(), Ident_Current()

 

Sequence Generator:

·      How to impose Identity values in a table Using Sequence

 

Views

·      How to create, alter and drop Views

·      Indexed views

 

 

 

Intersect and Except

·      what is Intersect

·      what is Except

 

 

Temp Tables

·      Global Temp Tables

·      local Temp Tables

·      Table Variables

·      Difference between Global, Temporary and Table Variable

·      CTE’S

·      Recursive CTE

 

 

Sub Queries

·      Scalar and Multivalve Sub Queries

·      Correlated sub queries

·      Derived tables

 

 

Merge Statement with example

·      Merge statement Syntax

·      How to do Incremental Load using Merge in SQL

 

 

Cursors

·      How to create Cursors

 

Pivot and unpivot

·      How to use Pivot and Unpivot with example

 

 

Transactions

·      Transaction Syntax

·      What are Rollback transactions

·      what are Nested Transactions with examples

·      How to find number of Open transactions

·      Different Types of Transaction available with examples

·      Auto commit

·      Explicit transaction

·      Implicit transaction

 

Isolation Levels

·      What are ACID Properties

·      Different types of Isolation Levels with examples

·      Read Uncommitted

·      Read committed

·      Repeatable Read

·      Serializable

·      Snapshot

 

 

Locks

·      Locks granularity

·      Shared Lock (s),Exclusive Lock (x), Update Lock (u)

·      Different levels where we can apply Locks Granularity

·      Row level, Table level

·      Page level, Extent level,Key level

 

 

Dead Locks

·      What is Dead Lock

·      How to Identify Dead Lock through SQL Profiler

·      How to resolve when Dead Lock occurs

 

 

 

 

 

Stored Procedures

·      How to create stored procedures without parameters

·      How to create stored procedures with  parameters

·      How to create stored procedures with default parameters

·      How to create stored procedures with output or Return parameters

·      Nested Procedure

 

User Defined Functions

·      Scalar valued functions ,Inline table functions, Multi value functions with examples

·      Difference between Stored Procedure and Functions

 

Triggers

·      Instead of Triggers and After Triggers

·      How Triggers used for Audit Purpose

·      Magic tables

·      Inserted and Deleted tables with examples

·      DML and DDL triggers

 

Indexes

·      Clustered Index and Non Clustered Index with examples

·      Unique Indexes

·      Column Stored Indexes

·      Difference between Clustered and Non Clustered Index

·      What are Table Scan and Index scan

·      What is Index seek

·      Difference between Table Scan and Index Seek

 

Normalization

·      What are First Normal form, Second Normal form, Third Normal form ,BCNF OR Fourth normal form with examples

 

Dealing with XML Data

·      Dealing with XML

·      What are different types XML Data Formats

 

Taking Backup and Restore

·      How to Take Back up and Restore of a database

·      How to Attach and Detach a database

 

 

How to create dynamic SQL

·      Creating dynamic sql

·      How to execute dynamic sql

 

Error handling

·      Using Try Catch block

 

 

Table Partitioning

·      How to Partition a table which is having High volume of data

·      How to create File groups, Partition Function and Partition Scheme

·      How to map the Partition schemas to the Secondary files

 

Linked Servers

·      How to create Linked server

·      How to create Synonyms

 

 

Query Optimization\ Performance Tuning:

·      Will explain 25 tips to improve sql query performance

 

Sql Profiler: How to trace the queries using sql profiler

Star schema model, Snowflake model

DATA BASE Modeling concepts

Logical, Conceptual , Physical data models