Structured Query Language-SQL : Mastering

Structured Query Language

• 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

• REVOKE

iv.) TCL - Transaction Control Language

• COMMIT
• ROLLBACK
• SAVEPOINT

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;

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.