SQL Concepts with Examples

 PRIMERY Key:
Primary key uniquely identifies each row in a table. The values in a primary
key cannot be NULL. Every row must have a value in the primary key
column.

 FOREIGN Key:
A foreign key refers to the primary key or a unique key in another table. It is
used to make relationship between two tables based on the primary key.

 WHERE Clause:
Where clause is used to find output based on some condition. It is used to
filter records before any grouping are made.

 Scenario:
Imagine you have a table called Employees with columns name,
salary, city. You want to find all the employee’s details in particular
city. In that case you can use Where clause.

 HAVING Clause:
Having clause is also used to find output based on some condition, but it
used after grouping operation is done.

 Scenario:
Suppose you have an employee table with columns name, salary,
department. If you want to find departments with an average salary
greater than 50000.

 GROUP BY:
The Group by clause is used to group rows that have the same values in
specified columns into aggregated data. Mostly it is used with aggregate
functions like count, sum, avg.
If you use group by without aggregate function then it will give you unique
combination.
  Scenario:
Suppose you have an employee table and you want to find the
average salary per department.

 ORDER BY:
The order by clause is used to sort the result set in either ascending or
descending order.

 Scenario:
It is used to sort the employees by their salary in descending order.

 INDEX:
Indexes are used to retrieve the data from database more quickly. Index
improves the speed of data retrieval operations on a table. It allows the
database to find rows much faster than without the index.

 Scenario:
Imagine you have a large book on history with thousand of pages. To
find a specific topic or team, you wouldn’t want to read through
every page. Instead, you use the books index at the front page, which
lists topics alphabetically along with the page number.

 VIEW:
View can be representing a subset of a data present in the table. It is a
virtual table that provides a way to look at data from one or more tables.
View is used for security purpose. It simplifies complex queries, provide
consistency.

 Scenario:
Let consider a database for a university with the tables Students,
Courses, Enrolment’s. Suppose you frequently needs a list of all
students along with the courses they are enrolled in and their grades.
Then you can create a view to simplify this. Now, you can query the
view instead of writing the complex join query every time.
 INNER Join:
An inner join is used to combine rows from two or more tables based on a
related column between them. An inner join keyword selects all the rows
from both the tables as long as there is a match between the columns.

 Scenario:
Consider two tables called Customers and Orders, and you want to
get a list of customers and their orders, then you can use inner join.

 LEFT Join:
Left join returns all rows from the left table and matched rows from right
table. If there is no match, the result is NULL on the side of the right table.

 Scenario:
Let consider Orders and Customers table and if you want to see all
the customers and any order they have made or even if they do not
make any order. It will give all the customers and orders associated
with them.

 RIGHT Join:
The Right Join returns all rows from the right table and the matched rows
from left table. The result is NULL on the side of the left table.

 Scenario:
Let consider Students and Enrolments table. You want to get all
enrolments and include the students details if available, you can use
the right join.

 CROSS Join:
A Cross Join is also known as Cartesian Join, it returns the cartesian product
of two tables involved in the join. It combines each row from first table with
every row from the second table.

 Scenario:
Imagine you are running a restaurant and you have two tables called
MainDishes and Sides. To find all possible combinations of main
 dishes and sides that can be offered together, you can use a cross
join.

 FULL Outer Join:

 Scenario:
Let consider tow tables Students and Enrolments. If you want to list
all students and their enrolments, including students who are not
enrolled in any courses and enrolments that do not have a matching
student record.

 STORE Procedure:
A stored procedure is a set of SQL statements that can be stored in the
database and executed on demand. It is a prepared SQL code that you can
save, so the code can be used again and again.

 Scenario:
Let consider e-commerce platform that manages Customers,
Products, Orders information. When a customer places an order,
several database operations need to performed like validate
customer, update inventory, calculate total. So stored procedures can
encapsulate all these operations into a single callable unit.

 RANK:
Rank function is used to assign a unique rank value to each row. If the
multiple rows have the same value, then they receive same rank and the
next rank will be skipped.

 Scenario:
Let consider table called Employee Salary.
If you want to rank employees based on their salary across the entire
table. In that case you can use rank.
 If you want to rank employees based on their salary within each
department, there also you can use rank.

 ROW Number:
Row number is used to assign a unique number to each row, It provides
consistent numbering for all rows. It starting at 1 for the first row in each
partition. Row number does not handle ties by giving the same rank to
multiple rows.

 Scenario:
Let consider the same Employee Salary table as before. If you want
to assign a unique row number to each row in the entire table based
on salary in descending order.
If you want to assign a row number to each row within each
department based on salary in descending order.

 DENSE Rank:
Dense rank assigns unique rank value to each row similar to regular rank. If
multiple rows have the same value, then they receive the same rank and
the next rank is not skipped.

 Scenario:
Let consider table called Employee Salary.
If you want to rank employees based on their salary across the entire
table. In that case you can use dense rank. If you don’t want to skip
next rank then you can go with dense rank.

 TRIGGERS
Triggers are essentially piece of code that are automatically executed when
the defined events occur. This event can include actions like insert, update
and delete records in table.
1. BEFORE INSERT:
Before insert trigger is a trigger that is executed automatically before
a new record is inserted into table

 Scenario:
Suppose you want to create employee id before inserting employee
records in table, in that case you can use before insert table.

2. AFTER INSERT:
After insert trigger is a type of trigger that is automatically executed
after a new record is inserted in the table.

 Scenario:
Suppose you want to send welcome email to new employees after
they are added to the dataset.

3. BEFORE UPDATE:
Before Update trigger is a type of trigger that is automatically
executed before updating a new record in the table.

 Scenario:
Validate and ensure data integrity before updating an employee
salary in the database.

4. AFTER UPDATE:
After Update trigger is a type of trigger that is automatically executed
after updating a new record in the table.

 Scenario:
Log the changes to the employee’s salary in an audit table after the
update operation.
5. BEFORE DELETE:
Before delete trigger is a type of trigger that is automatically
executed before deleting a record in the table.

 Scenario:
Ensure an order cannot be deleted if it is already shipped. This trigger
prevents the deletion of an order if it has already been shipped.

6. AFTER DELETE:
After delete trigger is a type of trigger that is automatically executed
after deleting a record in the table.

 Scenario:
Archives details of deleted customers record into new table.

Thank You