Complete SQL

SQL Concepts
Sl/ Concepts Definitions
No
1 Creating Database This concept will make you learn how to create your own database.

2 Creating Tables and From this concept, you will learn how to create tables inside the database and
adding data insert data into them.

3 SELECT Clause Retrieve or fetch data from a database.

4 FROM Clause From which table in the database do you have to select data?

5 WHERE Clause It forms the condition based on which data have to be queried.

6 DELETE Statement For deletion tasks.

7 INSERT INTO For insertion tasks.

8 AND and OR operator Selecting data based on AND or operator.

9 Drop and Truncate It will drop or truncate the collection as per the condition.

10 NOT Operator It will select the data which is not based on the given condition.
Sl/No Concept Definition
11 WITH Clause Understanding the concept of with clause and using it to give the sub-query
block a name.

12 FETCH Clause It will fetch the filtered data based upon certain conditions like fetching only the
top 3 rows.

13 Arithmetic Operators Using arithmetic operators to filter the data conveniently and precisely.

14 Wildcard Operators To intelligently select the exact data like names starting or ending with T.

15 UPDATE Statement Updating certain data entries based upon the condition provided.

16 ALTER Table Adding, dropping, or modifying the table based on the given condition.

17 LIKE Clause It will follow the pattern given on the condition for search.

18 BETWEEN and IN It will select the data range between or in the given condition.
operator

19 CASE Statement It will check for the conditionals and will query the data as per the respective
case.

20 EXISTS It will form the nested query to filter out the data which exists in another query.
Sl/No Concept Definition
21 DISTINCT Clause It will select only the distinct data, not repetitive.

22 Count Function Returns the total count of the data filtered.

23 Sum Function Return the sum of all the data being queried.

24 Average Function Return the average of all the data being queried.

25 Minimum Function It will return the minimum data from the whole data that is being queried.

26 Maximum Function It will return the maximum data from the whole data that is being queried.

27 ORDER BY This statement will order the queried data as per your convenience in ascending or
descending order.

28 GROUP BY This statement will group all your queried data with the column given in the condition.

29 ALL and ANY Clause They are logical operators in SQL and return boolean values as a result.

30 TOP Clause Used to fetch the limited number of rows from a database.
Sl/
Concept Definition
No

31 Union Clause Just like the mathematical union operator, this clause will make the union of the tables given.

32 Intersection Clause It will join the two or more tables where they are intersecting.

33 Aliases It will give an alias to the table which we can refer to later.

34 Cartesian Join and Sometimes to query out some data, we have to self join the table to itself.
Self Join

35 Inner, Left, Right and These four types of joining come into play when we have to join one table with another.
Full Joins

36 Division Clause Division is typically required when you want to find out entities that are interacting with all entities of a set of different
types of entities.

37 Using Clause If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be
modified with the USING clause to specify the columns that should be used for an EQUIJOIN.

38 Combining values Combining aggregate and non-aggregate values in SQL using Joins and Over clause.

39 MINUS Operator It is used as “except” which means it will join the two intersecting tables and will minus one table so that only the
intersection and the other table are covered.

40 Joining 3 or more Although used very rarely, this will make you learn and understand how to join 3 or more tables and then carry out the
tables querying operations.
Sl/No
Concept Definition
41 Views Virtual tables created by querying data from one or more tables.

42 Creating Roles Defining roles and permissions within a database.

43 Constraints Rules enforced on data columns to ensure data integrity.

44 Transactions Sequence of operations performed as a single logical unit of work.

45 Mathematical Functions Functions to perform mathematical operations on data.

46 Date Functions Functions to handle and manipulate date and time values.

47 Conditional Expressions Expressions that perform different actions based on given conditions.

48 General Functions Various built-in functions for handling data manipulation.

49 String Functions Functions to manipulate and handle string data.

50 Conversion Function Functions to convert data from one type to another.

51 Top-N Queries Queries that retrieve the top N records from a dataset.

52 Advanced Functions Complex functions for specialized data manipulation and analysis.
SQL CREATE DATABASE

CREATE DATABASE testDB ;

CREATE DATABASE IF NOT EXISTS testDB ;

CREATE TABLE

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
 CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT CHECK (Age >= 0 AND Age <= 99),
Phone int(10)
);

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

SQL SELECT Query

SELECT CustomerName, LastName FROM Customer;

SELECT * FROM Customer;

SELECT Statement with WHERE Clause

SELECT CustomerName FROM Customer where Age = '21';

SELECT Statement with GROUP BY Clause (It works with Aggregate functions)

SELECT COUNT (item), Customer_id FROM Orders GROUP BY order_id;

SELECT Statement with HAVING Clause

SELECT Department, sum(Salary) as Salary

FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;
SELECT Statement with ORDER BY clause in SQL

SELECT * FROM Customer ORDER BY Age DESC;

 Operator Description
List of Operators that Can > Greater Than
be Used with WHERE Clause
>= Greater than or Equal to

< Less Than

<= Less than or Equal to

= Equal to

<> Not Equal to

BETWEEN In an inclusive Range

LIKE Search for a pattern

To specify multiple possible values for a

IN
column
Let’s see for the same concepts, one example.
Sl/No
Concept Example
1 Creating Database CREATE DATABASE myDatabase; [ Select Name from sys.databases ]

2 Creating Tables and CREATE TABLE myTable (id INT, name VARCHAR(255)); INSERT INTO myTable VALUES
adding data (1, 'John');

3 SELECT Clause SELECT * FROM myTable;

4 FROM Clause SELECT * FROM myTable;

5 WHERE Clause SELECT * FROM myTable WHERE id = 1;

6 DELETE Statement DELETE FROM myTable WHERE id = 1;

7 INSERT INTO INSERT INTO myTable (id, name) VALUES (2, 'Jane');

8 AND and OR operator SELECT * FROM myTable WHERE id = 1 OR name = 'Jane';

9 Drop and Truncate DROP TABLE myTable; TRUNCATE TABLE myTable;

10 NOT Operator SELECT * FROM myTable WHERE NOT id = 1;

Sl/No
Concept Example
11 WITH Clause WITH subQuery AS (SELECT * FROM myTable) SELECT * FROM subQuery;

12

13 Arithmetic Operators SELECT id, (id * 2) AS double_id FROM myTable;

14 Wildcard Operators SELECT * FROM myTable WHERE name LIKE 'J%';

15 UPDATE Statement UPDATE myTable SET name = 'John Doe' WHERE id = 1;

16 ALTER Table ALTER TABLE myTable ADD COLUMN age INT;

17 LIKE Clause SELECT * FROM myTable WHERE name LIKE 'J%';

18 BETWEEN and IN SELECT * FROM myTable WHERE id BETWEEN 1 AND 10;

operator

19 CASE Statement SELECT id, CASE WHEN id = 1 THEN 'One' ELSE 'Other' END FROM myTable;

20 EXISTS SELECT * FROM myTable WHERE EXISTS (SELECT 1 FROM otherTable WHERE
otherTable.id = myTable.id);
Sl/No Concept Example
21 DISTINCT Clause SELECT DISTINCT name FROM myTable;

22 Count Function SELECT COUNT(*) FROM myTable;

23 Sum Function SELECT SUM(id) FROM myTable;

24 Average Function SELECT AVG(id) FROM myTable;

25 Minimum Function SELECT MIN(id) FROM myTable;

26 Maximum Function SELECT MAX(id) FROM myTable;

27 ORDER BY SELECT * FROM myTable ORDER BY name ASC;

28 GROUP BY SELECT name, COUNT(*) FROM myTable GROUP BY name;

29 ALL and ANY Clause SELECT * FROM myTable WHERE id = ANY (SELECT id FROM otherTable);

30 TOP Clause SELECT TOP 10 * FROM myTable;

Sl/ Concept Example
No
31 Union Clause SELECT * FROM table1 UNION SELECT * FROM table2;
UNION ALL UNION removes duplicate records, whereas UNION ALL includes
all duplicates.
32 Intersection Clause SELECT * FROM table1 INTERSECT SELECT * FROM table2;

33 Aliases SELECT t1.name AS table1Name FROM myTable t1;

34 Cartesian Join and Self SELECT * FROM myTable t1, myTable t2 WHERE t1.id = t2.id;
Join

35 Inner, Left, Right and Full SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
Joins

36 Division Clause SELECT A.name FROM A, B WHERE A.id = B.id;

37 Using Clause SELECT * FROM table1 JOIN table2 USING (common_column);

38 Combining values SELECT AVG(value) OVER (PARTITION BY category) FROM myTable;

39 MINUS Operator SELECT * FROM table1 MINUS SELECT * FROM table2;

40 Joining 3 or more tables SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id JOIN table3 t3 ON
Sl/No Concept Example
41 Views CREATE VIEW myView AS SELECT * FROM myTable;

42 Creating Roles CREATE ROLE myRole;

43 Constraints ALTER TABLE myTable ADD CONSTRAINT myConstraint UNIQUE (id);

44 Transactions BEGIN; UPDATE myTable SET name = 'Jane' WHERE id = 2; COMMIT;

45 Mathematical Functions SELECT SQRT(id) FROM myTable;

46 Date Functions SELECT CURRENT_DATE;

47 Conditional Expressions SELECT CASE WHEN id > 1 THEN 'Greater' ELSE 'Smaller' END FROM myTable;

48 General Functions SELECT COALESCE(name, 'Unknown') FROM myTable;

49 String Functions SELECT CONCAT(first_name, ' ', last_name) FROM myTable;

50 Conversion Function SELECT CAST(id AS VARCHAR) FROM myTable;

51 Top-N Queries SELECT * FROM myTable ORDER BY id LIMIT 5;

52 Advanced Functions SELECT RANK() OVER (ORDER BY id) FROM myTable;

How to create a foreign key
Step 1 :- Create Employee table which should have DeptId as column.

Create Table Employee(

EmpId int primary key identity(1,1),

FirstName nvarchar(50) not null,

LastName nvarchar(50) null,

DeptId int null,

Salary int null )

Step 2 :- Create Dept table where DeptId is Primary key

Create table Dept (

DeptId int primary key identity(1,1),

Name nvarchar(50) not null,

City nvarchar(100) null )

Step 3 :- Now right click on Employee table → Design → right click on
DeptId then choose Relationships as in pic → Click on Add button → click
on 3 dots of Tables and Colums spec
Step 4 :- Select Primary key table as Dept → also select DeptId as below
column → Again from right side select column as DeptId then click ok →
then close the popup window and save table.
Now check foreign key Fk is created in Employee
table or not ? (Refresh the employee table to see
the keys)
 departments
employees +------------+------------+
+------------+---------+ | department_id | name
| employee_id| name | |
+------------+---------+ +------------+------------+
|1 | Alice | | 10 | Sales |
|2 | Bob | | 20 | Marketing |
|3 | Charlie | | 30 | Engineering|
+------------+---------+ +------------+------------+
Let's assume there's a third table, employee_departments, that serves as a
junction table for a many-to-many relationship between employees and departments:

employee_departments
+------------+------------+
| employee_id | department_id |
+------------+------------+
|1 | 10 |
|1 | 20 |
|2 | 20 |
|3 | 30 |
+------------+------------+
Self join
Explanation

ManagerI
D
Interview Question : Select 2nd highest Salary
 Row_Number Function in SQL Server with Examples
The ROW_NUMBER function in SQL Server is a window function that assigns a unique
number to each row within a partition of a result set.

It’s often used to create ordered sequences, particularly when you need to add row
numbering based on specific criteria without altering the underlying data.

Basic ROW_NUMBER with ORDER BY

SELECT

EmployeeId, FirstName, LastName, Salary,

ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum

FROM Employees;
Function How it handles tied values

ROW_NUMBE Assigns a unique number to each row, even if there are

R duplicates

RANK Assigns the same rank to tied values, but may skip subsequent
ranks

DENSE_RAN Assigns the same rank to tied values without skipping ranks
K
select Salary as SecondHighestSalary

from (select Salary,

row_number() over (order by Salary desc) as rowNumb

from Employee

)e

where rowNumb = 2
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call
it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the
parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name

AS

sql_statement

GO;

Execute a Stored Procedure

EXEC procedure_name;
Example
CREATE PROCEDURE SelectAllCustomers

@City nvarchar(30)

AS

SELECT * FROM Customers WHERE City = @City

GO;

EXEC SelectAllCustomers @City = 'London';

—--------------------------------------------------------------------------------

CREATE PROCEDURE GetEmployeesByDepartment

@DepartmentId INT

AS

BEGIN

SET NOCOUNT ON;

SELECT EmployeeId, FirstName, LastName, DepartmentId FROM Employees

WHERE DepartmentId = @DepartmentId;

How to optimize SQL query performance in SQL Server?

optimize = to make effective or perfect as possible.

1. Set NoCount On
2. Avoid using * (specify column names)
3. Minimise the use of SubQueries
4. Avoid using unnecessary Column names
5. Optimize Join Operations
6. Use Indexes effectively
7. Consider Partitioning and sharding
EXEC GetEmployeesByDepartment @DepartmentId = 2;

CREATE PROCEDURE is used to create a new stored procedure.

GetEmployeesByDepartment is the name of the procedure.

@DepartmentId INT is a parameter that the procedure accepts, allowing you to filter
employees by department.

SET NOCOUNT ON is optional but prevents the message about the number of rows
affected from being returned, which can slightly improve performance.

The SELECT statement retrieves employees who belong to the specified department.
Advantages of Using Stored Procedures
Encapsulation of Logic: You can put complex logic into a stored procedure and reuse it as
needed.

Performance: Stored procedures can be optimized and cached by the database engine.

Security: By limiting access to certain data and requiring only specific parameters, you can
help control data exposure.

Maintainability: Changes to business logic can be done centrally within the procedure
instead of in multiple places in application code.
VIEWS
Definition: An SQL view is a virtual table based on the result set of a
query.
Explanation: It contains rows and columns like a real table but does
not store data itself; it derives data from underlying tables.
Analogy: Think of a view as a "saved query" that can be accessed as
if it were a table.
Creating a View
CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example :-

CREATE VIEW EmployeeDetails AS

SELECT EmployeeID, Name, Salary

FROM Employee

WHERE Salary = 10000;

Execute View
SELECT * from EmployeeDetails
What is the difference between table, views and stored procedure?

1. Tables are used for permanent data storage.

2. Table holds actual data, while a view is just like a
virtual table deriving data from one or more tables.
3. Stored Procedures are best used for INSERT-UPDATE-
DELETE statements. Whereas Views are used for
SELECT statements.
Benefits of Using Views
1. Simplified Querying: Encapsulates complex SQL queries for easy access.
2. Data Security: Restricts access to specific data by displaying only selected
columns.
3. Data Consistency: Ensures consistent data representation across the
application.
4. Reusability: Saves time and reduces duplication of code by reusing complex
queries.
5. Performance Optimization: Can optimize querying in some cases (e.g.,
materialized views).
Use Cases for Views
1. Data Aggregation: Summarizing large datasets for reporting.
2. Data Masking: Exposing only specific columns to protect sensitive
data.
3. Joining Multiple Tables: Simplifying complex joins and making data
easier to query.
4. Subsetting Data: Displaying a subset of rows based on specific
criteria.
CTE
Definition: A CTE (Common Table Expression) is a temporary result set
that you can reference within a SELECT, INSERT, UPDATE, or DELETE
statement.
Explanation: CTEs are used to improve readability and simplify complex
queries by breaking them into more manageable parts.
Scope: Only exists within the query that defines it.
WITH NthHighestSalary AS (

select Salary, row_number() over (order by Salary desc) as rowNumb

from Employee

SELECT * FROM NthHighestSalary WHERE rowNumb = 1

Triggers
Definition: An SQL trigger is a set of instructions that automatically executes in
response to specific events on a table or view.

Explanation: Triggers are commonly used to enforce business rules,

automatically update data, or maintain audit trails.

Trigger Timing: Triggers execute either before or after certain events, such as
INSERT, UPDATE, or DELETE.
Types of SQL Triggers
Before Triggers: Execute before an operation (INSERT, UPDATE,
DELETE), often used for validation or transformation.
After Triggers: Execute after an operation, useful for logging, updating
related records, or triggering further actions.
Instead of Triggers (some databases): Replace the intended operation,
commonly used on views to allow INSERT, UPDATE, or DELETE.
Trigger Syntax
CREATE TRIGGER trigger_name

[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]

ON table_name

FOR EACH ROW

BEGIN

-- Trigger logic here

END;
Example of a Before Trigger
CREATE TRIGGER SetLastUpdated

BEFORE UPDATE ON Employees

FOR EACH ROW

BEGIN

SET NEW.LastUpdated = CURRENT_TIMESTAMP;

END;
Example of an After Trigger
CREATE TRIGGER LogDelete

AFTER DELETE ON Employees

FOR EACH ROW

BEGIN

INSERT INTO EmployeeDeletions (EmployeeID, DeletedAt)

VALUES (OLD.EmployeeID, CURRENT_TIMESTAMP);

END;
Practical Use Cases for Triggers
Audit Logging: Track changes to critical data.
Cascade Actions: Update or delete related records
automatically.
Data Validation: Prevent changes that violate business rules.
History Tracking: Maintain a record of data changes over time.
What is a SQL Transaction?
A SQL transaction is a sequence of one or more SQL statements that
are executed as a single logical unit of work. A transaction ensures that
the operations are either fully completed or fully rolled back, maintaining
the integrity of the database.
Transactions are essential for ensuring ACID properties:

1. Atomicity: The transaction is all-or-nothing. If one part fails, the entire

transaction is rolled back.
2. Consistency: The database moves from one valid state to another.
3. Isolation: Concurrent transactions do not interfere with each other.
4. Durability: Once a transaction is committed, the changes are
permanent, even in case of a system failure.
Basic SQL Transaction Commands
1. BEGIN TRANSACTION: Starts a transaction.
2. COMMIT: Saves the changes made by the transaction to the
database.
3. ROLLBACK: Reverts the database to its state before the
transaction began, undoing any changes made during the
transaction.
BEGIN TRANSACTION;

BEGIN TRY

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

COMMIT; -- Commit if all operations succeed

END TRY

BEGIN CATCH

ROLLBACK; -- Revert changes if an error occurs

PRINT 'Transaction rolled back due to an error.';

END CATCH;
Best Practices for Transactions
1. Keep transactions short to reduce contention and improve performance.
2. Use error handling (e.g., TRY...CATCH) to manage failures effectively.
3. Ensure proper indexing to minimize lock contention.
4. Avoid user interaction (e.g., prompts) within a transaction.
5. Use appropriate isolation levels based on the application's concurrency
requirements.
6. Test transactions under high-concurrency conditions to identify
potential bottlenecks.
1. Normalization & DeNormalization
2. Temp Table
3. Derived Table
4. Local Variable & Global Variable
5. Clustered Indexes and non clustered indexes
6. Database Shrading
7. Sp vs Functions
What is SQL Normalization and
Denormalization?
SQL Normalization

Normalization in SQL is a database design technique that organizes tables in a manner that
reduces redundancy and dependency.

It involves dividing a database into two or more tables and defining relationships between them to
achieve a more efficient database structure.
Characteristics

● Reduces Redundancy: Avoids duplication of data.

● Improves Data Integrity: Ensures data accuracy and consistency.

● Database Design: Involves creating tables and establishing relationships through primary

and foreign keys.

Original Table (Before Normalization)

Imagine a single table that stores all customer orders:

After Normalization

To normalize this, we would split it into two or more tables to reduce redundancy.
Levels (Normal Forms)

● 1NF (First Normal Form): Data is stored in atomic form with no repeating groups.

● 2NF (Second Normal Form): Meets 1NF and has no partial dependency on any candidate

key.

● 3NF (Third Normal Form): Meets 2NF and has no transitive dependency.
SQL Denormalization

Denormalization, on the other hand, is the process of combining tables to reduce the complexity of
database queries.

This can introduce redundancy but may lead to improved performance by reducing the number of
joins required.
Characteristics

● Increases Redundancy: May involve some data duplication.

● Improves Query Performance: Reduces the complexity of queries by reducing the number of

joins.

● Data Retrieval: Optimized for read-heavy operations.

Denormalization Example

Denormalization would involve combining these tables back into a single table to optimize read
performance. Taking the above table:
When to Use

● In read-heavy database systems where query performance is a priority.

● In systems where data changes are infrequent and a slightly less normalized structure

doesn't compromise data integrity.

Derived Tables in SQL Server

● A derived table is an example of a subquery that is used in the

FROM clause of a SELECT statement to retrieve a set of records.
● We can use derived tables to break a complex query into separate
logical steps and they are often a neat alternative to using
temporary tables.
The Basic Syntax of a Derived Table

To create a derived table you need to start with a valid SELECT statement.
Here's an example of a query which we will convert into a derived table:
Subquery
Clustered vs Non-clustered Indexes in SQL

What Is Clustered Index in SQL?

Clustered indexes determine the physical order of data in a table. There can
only be one clustered index per table.

What is a Non-Clustered Index in SQL?

Non-clustered indexes are additional indexes that don’t determine the
physical order of data. They provide a separate structure that holds data
pointers.