Mastering-SQL

This repo is built one step at a time and helps one Master SQL.

How to use this repository

SQL Server objects

• Database
• Table

Different ways to replace NULLS in SQL Server

Three ways to replace NULL values

1. ISNULL() function
2. CASE statement
3. COALESCE() statement

Control Flow in SQL Server

• BEGIN…END
• BREAK
• CONTINUE
• ELSE (IF..ELSE)
• END
• GOTO
• RETURN
• THROW
• TRY…CATCH
• IF EXISTS
• IF
• WAITFOR
• WHILE

System variables

• @@ROWCOUNT

Data types

• Date and Time data types

Built in FUNCTIONS in SQL Server

Mathematical functions in SQL Server

• ABS

  Date functions in SQL Server

String functions in SQL Server

• ASCII
• CHAR
• CHARINDEX
• CONCAT
• CONCAT_WS
• DIFFERENCE
• FORMAT
• LEFT
• LEN
• DATALENGTH
• LOWER
• LTRIM
• NCHAR
• PATINDEX
• QUOTENAME
• REPLACE
• REPLICATE
• REVERSE
• RIGHT
• RTRIM
• SOUNDEX
• SPACE
• STR
• STRING_AGG
• STRING_ESCAPE
• STRING_SPLIT
• STUFF
• SUBSTRING
• TRANSLATE
• TRIM
• UNICODE
• UPPER

Aggregate Functions in SQL Server

• AVG
• MIN
• MAX
• SUM
• COUNT

CTE (Common Table Expression)

Non-Recursive CTE

• Simple CTE
• Multiple CTE

Recursive CTE

• Recursive CTE

Other functions in SQL Server

• HOST_Name()

ERROR FUNCTIONS in SQL Server

• ERROR_NUMBER
• ERROR_STATE
• ERROR_SEVERITY
• ERROR_LINE
• ERROR_PROCEDURE
• ERROR_MESSAGE

Intersect

• Intersect operator in SQL Server

Questions and answers

• Difference between varchar and nvarchar in SQL Server?
• Find Number of Spaces in a String
• Difference between JOIN and UNION
• UNION and UNION ALL in SQL Server

Hacker Rank

• The Report

The concept required to solve this question is that the JOIN condition is to be applied using BETWEEN clause.

• Placements

The concept required to solve this question is that we need to use the same table twice in order to JOIN in 2 instances. Table Placements is used as P1 and P2, this will create 2 different instances of the same table.

Interview Questions

• Full Outer Join