Structured Query Language-SQL : Mastering SQL Queries: From Beginner to Expert

• SQL is designed for maintaining the data in Relational Database Management System.

• Standard language for accessing manipulation database.

SQL Commands
i.) DDL - Data Definition Language
Data Definition Language is used to change the structure of the table like creating the table, altering the table and deleting the table.

• CREATE:

CREATE TABLE Employee (
 Employee ID int.
 FirstName varchar(55),
 LastName varchar(55)
 Email varchar(50)
 WorkDetails varchar(100);
)

• ALTER: Allows to add, modify and delete columns of an existing table.

ALTER TABLE Employee
ADD Department varchar(50);

• DROP: Deletes the record of the table in a database, deletes both the structure & records stored in table.

ALTER TABLE Employee 
DROP COLUMN WorkDetails;

• TRUNCATE: A TRUNCATE SQL command is used to remove all rows (complete data) from a table.

TRUNCATE TABLE Employee;

ii.) DML - Data Manipulation Language

• INSERT INSERT command is used to insert a single or a multiple records in a table.

INSERT INTO Customers(ContactName, Age, City)
VALUES(Harry, 19, Tokyo);

• UPDATE: UPDATE statement is used to modify the existing record in a table.

UPDATE Customers:
SET ContactName = 'George', City = 'Sydney'
WHERE CustomerID = 101;

• DELETE: DELETE statement is used to delete existing records in a table.

DELETE FROM Customers 
WHERE CustomerName = 'George';

iii.) DCL - Data Control Language

• ORANT: Assigns specific permissions (e.g., SELECT, INSERT, UPDATE, DELETE) to users or roles on database objects.

• REVOKE: Removes previously granted permissions from users or roles.

• DENY: Explicitly denies specific permissions to users or roles, preventing them from performing certain actions even if they have a higher role with granted permissions.

iv.) TCL - Transaction Control Language

• COMMIT: This command permanently saves the changes made by a successful transaction to the database.

• ROLLBACK: If an error occurs during a transaction, ROLLBACK undoes all the changes made within that transaction, restoring the database to its state before the transaction began.

• SAVEPOINT (optional): This command allows you to define a specific point within a transaction to which you can optionally roll back if needed.

Retrieval clauses - Retrieval clauses are a group of clauses used within a SELECT statement in SQL to specify how data is retrieved from a database.

• WHERE: Filters the results based on a specified condition.
  SELECT * FROM customers WHERE city = 'New York';

• ORDER BY: Sorts the results based on one or more columns.
  SELECT * FROM products ORDER BY price ASC;

• GROUP BY: Groups rows that share a common value in a specific column.
  SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

• HAVING Clause: Filters groups created by the GROUP BY clause based on a condition applied to aggregate functions.
  SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING employee_count > 10;

• LIMIT and OFFSET:
  LIMIT: Restricts the number of rows returned by the SELECT statement.
  OFFSET: Specifies the number of rows to skip before starting to return results.
  SELECT * FROM products LIMIT 10 OFFSET 20;

SQL Keys
In SQL, keys are fundamental elements used to establish relationships between tables, enforce uniqueness among records, and enhance the ability to retrieve data efficiently. Here’s an overview of the main types of keys used in SQL databases:

i.) Primary Key: Uniquely identifies each record in a table. It cannot accept NULL values. If a primary key consists of more than one column, it is referred to as a composite key.

CREATE TABLE Customer (
    CustomerID int NOT NULL,
    Email varchar(255) NOT NULL,
    FirstName varchar(100),
    LastName varchar(100),
    PRIMARY KEY (CustomerID)
);

ii.) Foreign Key: A field (or collection of fields) in one table that uniquely identifies a row of another table. It is used to establish and enforce a link between the data in two tables.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber varchar(255) NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

iii.) Unique Key: Ensures that all values in a column are different. Unlike the primary key, it can accept multiple NULL values (except in SQL Server, where only one NULL value is allowed by default).

CREATE TABLE Employee (
    EmployeeID int NOT NULL,
    SSN varchar(11) UNIQUE,  -- Ensures no two employees have the same SSN
    FirstName varchar(100),
    LastName varchar(100),
    PRIMARY KEY (EmployeeID)
);

iv.) Composite Key: A combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined; it may also be a primary or unique key.

CREATE TABLE Enrollment (
    StudentID int,
    CourseID int,
    EnrollmentDate date,
    PRIMARY KEY (StudentID, CourseID)  -- Composite primary key
);

SQL joins
SQL joins are used to combine rows from two or more tables based on a related column between them. Here are the main types of joins used in SQL:-

• INNER JOIN: Returns rows when there is a match in both tables.

-- Select all orders with customer information
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• LEFT OUTER JOIN (or LEFT JOIN): Returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

-- Select all customers and their orders, if any
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• RIGHT OUTER JOIN (or RIGHT JOIN): Returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

-- Select all orders and the customers who made them, if known
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• FULL OUTER JOIN (or FULL JOIN): Returns rows when there is a match in one of the tables. It combines the effects of both LEFT and RIGHT joins.

-- Select all customers and all orders, showing who ordered what, if anything
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• CROSS JOIN: Returns the Cartesian product of the rows from the tables involved in the join. In other words, it will combine each row from the first table with each row from the second table.

-- Combine every customer with every product
SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;

• SELF JOIN: A regular join, but the table is joined with itself.

-- Find all pairs of employees who share the same job title
SELECT A.EmployeeName AS Employee1, B.EmployeeName AS Employee2
FROM Employees A, Employees B
WHERE A.JobTitle = B.JobTitle AND A.EmployeeID != B.EmployeeID;

• NATURAL JOIN: Performs a join using all columns with the same name for equality checks. It’s similar to an INNER JOIN but without explicitly specifying the join condition.

-- Select employee and department information based on the DepartmentID
SELECT *
FROM Employees
NATURAL JOIN Departments;

Indexing
Indexing in SQL is a database optimization technique used to speed up the retrieval of records from a table. Essentially, an index is a data structure that allows quick lookup of rows in a table, helping to improve query performance and response time.

Types of Indexes
-Single-column index: An index on a single column. It's used when queries frequently filter or sort based on that column.
-Composite index: An index that involves two or more columns. These are useful when queries filter or sort using multiple columns.
-Unique index: Ensures that two rows cannot have the same values in certain columns, enforcing uniqueness for the indexed columns.
-Full-text index: Used primarily on text-based columns for fast searching of text within those columns.

-Clustered and Non-clustered Indexes:

SQL Labeling: This refers to the practice of assigning labels or aliases to columns, tables, or even to the results of expressions within SQL queries. Labeling can help make SQL queries more readable and manageable, especially when dealing with complex queries or joins.

Here’s a basic example of SQL labeling:

-- Labeling columns with aliases
SELECT
    first_name AS FirstName,
    last_name AS LastName,
    date_of_birth AS DOB
FROM
    employees;

In this example, the columns first_name, last_name, and date_of_birth are labeled as FirstName, LastName, and DOB, respectively.

Pagination **
Pagination in SQL is a common technique to manage large datasets by dividing them into smaller, more manageable chunks.
**Understanding the Clauses:
LIMIT: This clause specifies the maximum number of rows you want to return in the result set.
OFFSET: This clause defines the number of rows to skip before starting to return results.

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name (ASC/DESC)
LIMIT number_of_rows
OFFSET offset_value;

Example: Let's say you have a products table and want to display 20 products per page. To retrieve page number 3:

SELECT product_id, name, price
FROM products
ORDER BY product_id ASC
LIMIT 20
OFFSET 40;

This query retrieves the first 20 records after skipping the first 40 rows (2 pages * 20 rows/page).