COMPREHENSIVE SQL NOTES

Table of Contents
1. Introduction to SQL
Overview of SQL
Characteristics of SQL
Advantages of SQL
SQL Data Types and Literals
2. Types of SQL Commands
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
Data Query Language (DQL)
3. Basic SQL Queries
SELECT Statement
Filtering with WHERE Clause
Sorting with ORDER BY
Removing Duplicates with DISTINCT
4. Logical Operators
BETWEEN Operator
IN Operator
AND Operator
OR Operator
NOT Operator
5. NULL Values
Understanding NULL Values
Disallowing NULL Values
Comparisons Using NULL Values
IS NULL and IS NOT NULL Operators
6. Integrity Constraints
Primary Key Constraints
NOT NULL Constraints
UNIQUE Constraints
CHECK Constraints
Referential Integrity (Foreign Key)
7. Nested Queries
Introduction to Nested Queries
Types of Subqueries
Correlated Nested Queries
Set-Comparison Operators
EXISTS and NOT EXISTS
8. Aggregate Functions
Common Aggregate Functions
The GROUP BY Clause
The HAVING Clause
Combining GROUP BY and HAVING
9. Joins
Inner Joins
Outer Joins
Left Outer Joins
 Right Outer Joins
Full Outer Joins
Self Joins
10. Other SQL Objects
Views
Sequences
Indexes
Triggers
Stored Procedures
11. Advanced SQL Concepts
Window Functions
Common Table Expressions (CTEs)
Transactions
Database Security

Introduction to SQL
Overview of SQL
SQL (Structured Query Language) is a specialized programming language designed for managing data in relational database management systems
(RDBMS). Developed in the early 1970s at IBM by Donald D. Chamberlin and Raymond F. Boyce, SQL has evolved to become the standard language
for relational database management.

SQL allows users to:

Create, read, update, and delete database records

Define and manage database structures
Control access to data within a database
Execute queries against a database to retrieve information

The SQL language is divided into several components, including data definition, data manipulation, data control, and data query elements. Through
these components, SQL provides a comprehensive set of tools for managing relational databases.

Most major database management systems implement SQL, including:

Oracle Database
Microsoft SQL Server
MySQL
PostgreSQL
SQLite
IBM Db2
MariaDB

While each database system may have its own proprietary extensions to SQL, the core language remains largely consistent across platforms, making
SQL knowledge transferable between different database environments.

Characteristics of SQL
SQL possesses several key characteristics that make it a powerful and widely used language for database management:

1. Declarative Nature: SQL is primarily a declarative language, meaning that users specify what results they want rather than how to obtain them.
The database system determines the most efficient way to execute queries.
 2. Non-Procedural: Unlike procedural programming languages that require step-by-step instructions, SQL statements describe the data to be
retrieved or manipulated without specifying the exact process.

3. Set-Based Operations: SQL operates on sets of data rather than processing records one at a time. This set-based approach allows for
efficient data processing.

4. English-Like Syntax: SQL uses commands and clauses that resemble English language phrases, making it relatively easy for non-
programmers to learn and understand.

5. ANSI Standard: SQL is standardized by both the American National Standards Institute (ANSI) and the International Organization for
Standardization (ISO), ensuring a level of consistency across different implementations.

6. Data Independence: SQL separates the logical structure of a database from its physical implementation, providing data independence.

7. Client-Server Architecture: SQL typically operates in a client-server model, where clients send SQL requests to a database server, which
processes the requests and returns results.

8. Integrated Data Definition: SQL includes capabilities for both data definition (creating and modifying database structures) and data
manipulation (inserting, updating, and querying data).

9. Support for Integrity Constraints: SQL allows for the definition of various integrity constraints to ensure data accuracy and consistency.

10. Transaction Support: SQL includes commands for managing transactions, allowing for the grouping of operations that must be executed
together.

Advantages of SQL
SQL offers numerous advantages that have contributed to its widespread adoption:

1. Standardization: As an ANSI/ISO standard language, SQL provides consistency across different database platforms, reducing the learning
curve when switching between systems.

2. Simplicity and Readability: The English-like syntax of SQL makes it relatively easy to learn, read, and write compared to many programming
languages.

3. Powerful Data Manipulation: SQL provides robust capabilities for querying, filtering, sorting, and aggregating data, allowing users to extract
precisely the information they need.

4. Data Independence: SQL abstracts the physical storage details from users, allowing them to work with logical data structures without needing
to understand the underlying storage mechanisms.

5. Reduced Data Redundancy: Through normalization and relational design, SQL databases can minimize data duplication, leading to more
efficient storage and reduced inconsistencies.

6. Data Integrity: SQL supports various constraints that help maintain data accuracy and consistency, including primary keys, foreign keys, and
check constraints.

7. Concurrent Access: SQL databases are designed to handle multiple users accessing data simultaneously, with built-in mechanisms to prevent
conflicts.

8. Security: SQL includes features for controlling access to data through permissions and privileges, allowing administrators to restrict who can
view or modify specific data.

9. Scalability: Modern SQL implementations can scale to handle massive datasets and high transaction volumes.

10. Integration Capabilities: SQL databases can be easily integrated with various programming languages and applications, making them
versatile for different use cases.

11. Transaction Processing: SQL supports ACID (Atomicity, Consistency, Isolation, Durability) properties for reliable transaction processing.
12. Improved Data Organization: The relational model underlying SQL facilitates logical organization of data into tables with defined relationships.

SQL Data Types and Literals

SQL supports various data types to store different kinds of information. Here's a comprehensive overview:

Numeric Data Types

1. INTEGER / INT: Stores whole numbers without decimal places.

CREATE TABLE Products (

ProductID INT,
Quantity INT
);

Effect: Creates a table with two columns that can store integer values:

ProductID Quantity
NULL NULL

For example, if we insert data:

INSERT INTO Products (ProductID, Quantity) VALUES (1, 100);

INSERT INTO Products (ProductID, Quantity) VALUES (2, 250);

The table would now contain:

ProductID Quantity
1 100
2 250

2. SMALLINT: Similar to INTEGER but uses less storage space and has a smaller range.

CREATE TABLE Settings (

SettingID SMALLINT,
Value SMALLINT
);

Effect: Creates a table with columns that can store small integers:

SettingID Value
NULL NULL

After inserting data:

INSERT INTO Settings (SettingID, Value) VALUES (1, 5);

INSERT INTO Settings (SettingID, Value) VALUES (2, 10);

SettingID Value
1 5
2 10

3. DECIMAL(p,s) / NUMERIC(p,s): Fixed-point numbers where 'p' is precision (total digits) and 's' is scale (digits after decimal).
 CREATE TABLE Finances (
TransactionID INT,
Amount DECIMAL(10,2) -- 10 digits in total, 2 after decimal point
);

Effect: Creates a table where the Amount column can store exact decimal values:

TransactionID Amount
NULL NULL

After inserting data:

INSERT INTO Finances (TransactionID, Amount) VALUES (1, 125.45);

INSERT INTO Finances (TransactionID, Amount) VALUES (2, 9999.99);
INSERT INTO Finances (TransactionID, Amount) VALUES (3, 1234567.89);

TransactionID Amount
1 125.45
2 9999.99
3 1234567.89

Character/String Data Types

1. CHAR(n): Fixed-length character string (padded with spaces if shorter).

CREATE TABLE States (

StateCode CHAR(2), -- Always 2 characters
CountryCode CHAR(3) -- Always 3 characters
);

Effect: Creates a table with fixed-length character columns:

StateCode CountryCode
NULL NULL

After inserting data:

INSERT INTO States (StateCode, CountryCode) VALUES ('CA', 'USA');

INSERT INTO States (StateCode, CountryCode) VALUES ('NY', 'USA');

StateCode CountryCode
'CA' 'USA'
'NY' 'USA'

Note: 'CA' is stored as 'CA ' (with a trailing space) internally since it's shorter than the defined length, but the trailing space is typically trimmed
when displaying the data.

2. VARCHAR(n) / CHARACTER VARYING(n): Variable-length character string with maximum length 'n'.

CREATE TABLE Customers (

CustomerID INT,
Name VARCHAR(100),
Address VARCHAR(255)
);
 Effect: Creates a table with variable-length text columns:

CustomerID Name Address

NULL NULL NULL

After inserting data:

INSERT INTO Customers (CustomerID, Name, Address) VALUES (1, 'John Smith', '123 Main St, Anytown, USA');
INSERT INTO Customers (CustomerID, Name, Address) VALUES (2, 'Jane Doe', '456 Oak Ave');

CustomerID Name Address

1 John Smith 123 Main St, Anytown, USA
2 Jane Doe 456 Oak Ave

Date and Time Data Types

1. DATE: Stores date values (year, month, day).

CREATE TABLE Events (

EventID INT,
EventDate DATE
);

Effect: Creates a table with a date column:

EventID EventDate
NULL NULL

After inserting data:

INSERT INTO Events (EventID, EventDate) VALUES (1, '2023-06-15');

INSERT INTO Events (EventID, EventDate) VALUES (2, '2023-12-25');

EventID EventDate
1 2023-06-15
2 2023-12-25

2. TIMESTAMP / DATETIME: Stores both date and time.

CREATE TABLE Logs (

LogID INT,
CreatedAt TIMESTAMP
);

Effect: Creates a table with a timestamp column:

LogID CreatedAt
NULL NULL

After inserting data:

INSERT INTO Logs (LogID, CreatedAt) VALUES (1, '2023-06-15 14:30:00');

INSERT INTO Logs (LogID, CreatedAt) VALUES (2, '2023-06-15 15:45:12');

LogID CreatedAt
 LogID CreatedAt
1 2023-06-15 14:30:00
2 2023-06-15 15:45:12

Boolean Data Type

1. BOOLEAN / BOOL: Stores TRUE or FALSE values.

CREATE TABLE UserPreferences (

UserID INT,
ReceiveEmails BOOLEAN,
DarkModeEnabled BOOLEAN
);

Effect: Creates a table with Boolean columns:

UserID ReceiveEmails DarkModeEnabled

NULL NULL NULL

After inserting data:

INSERT INTO UserPreferences (UserID, ReceiveEmails, DarkModeEnabled) VALUES (1, TRUE, FALSE);
INSERT INTO UserPreferences (UserID, ReceiveEmails, DarkModeEnabled) VALUES (2, FALSE, TRUE);

UserID ReceiveEmails DarkModeEnabled

1 TRUE FALSE
2 FALSE TRUE

SQL Literals
Literals are fixed values used in SQL statements. They represent constant values of different data types:

1. String Literals: Enclosed in single quotes.

SELECT * FROM Employees WHERE Department = 'Finance';

Effect: If we have an Employees table:

EmployeeID FirstName LastName Department Salary

1 John Smith IT 75000
2 Jane Doe Finance 80000
3 Bob Johnson Finance 85000
4 Mary Brown Marketing 70000

The query would return:

EmployeeID FirstName LastName Department Salary

2 Jane Doe Finance 80000
3 Bob Johnson Finance 85000

2. Numeric Literals: Written without quotes.

SELECT * FROM Products WHERE Price > 19.99;

Effect: If we have a Products table:

 ProductID ProductName Price
1 Basic Widget 9.99
2 Premium Widget 24.99
3 Super Widget 49.99
4 Mini Widget 14.99

The query would return:

ProductID ProductName Price

2 Premium Widget 24.99
3 Super Widget 49.99

3. Date Literals: Format varies by DBMS but often uses quotes with specific formats.

SELECT * FROM Orders WHERE OrderDate = '2023-05-15';

Effect: If we have an Orders table:

OrderID CustomerID OrderDate TotalAmount

1 101 2023-05-10 125.50
2 102 2023-05-15 230.00
3 103 2023-05-15 75.25
4 101 2023-05-20 310.75

The query would return:

OrderID CustomerID OrderDate TotalAmount

2 102 2023-05-15 230.00
3 103 2023-05-15 75.25

4. NULL Literal: Represents the absence of a value.

SELECT * FROM Customers WHERE Phone IS NULL;

Effect: If we have a Customers table:

CustomerID Name Phone Email

101 John Smith 555-123-4567 [email protected]
102 Jane Doe NULL [email protected]
103 Bob Johnson 555-987-6543 [email protected]
104 Mary Brown NULL [email protected]

The query would return:

CustomerID Name Phone Email

102 Jane Doe NULL [email protected]
104 Mary Brown NULL [email protected]

Types of SQL Commands

SQL commands are categorized based on their functionality. Understanding these categories helps in organizing and utilizing SQL effectively.

Data Definition Language (DDL)

Data Definition Language (DDL) commands are used to define, modify, and remove database structures. These commands work with database objects
rather than the data within them.

CREATE
The CREATE command is used to create new database objects such as tables, views, indexes, and stored procedures.

Creating a Database:

CREATE DATABASE EmployeeManagement;

Effect: Creates a new empty database named "EmployeeManagement".

Creating a Table:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
BirthDate DATE,
HireDate DATE DEFAULT CURRENT_DATE,
Department VARCHAR(50),
Salary DECIMAL(10, 2) CHECK (Salary > 0),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

Effect: Creates a table with the following structure:

Column Name Data Type Constraints

EmployeeID INT PRIMARY KEY
FirstName VARCHAR(50) NOT NULL
LastName VARCHAR(50) NOT NULL
BirthDate DATE
HireDate DATE DEFAULT CURRENT_DATE
Department VARCHAR(50)
Salary DECIMAL(10, 2) CHECK (Salary > 0)
ManagerID INT FOREIGN KEY REFERENCES Employees(EmployeeID)

Creating a View:

CREATE VIEW EmployeeSummary AS

SELECT
EmployeeID,
FirstName + ' ' + LastName AS FullName,
Department,
Salary
FROM
Employees;

Effect: Creates a virtual table that presents selected columns from the Employees table. When queried, it would look like:

EmployeeID FullName Department Salary

1 John Smith IT 75000.00
2 Jane Doe Finance 80000.00
 Creating an Index:

CREATE INDEX idx_employee_dept ON Employees(Department);

Effect: Creates an index on the Department column, which improves query performance when searching or filtering by Department. This doesn't
change the visible structure of the table but affects how the database engine accesses the data.

ALTER
The ALTER command modifies existing database objects.

Adding a Column:

ALTER TABLE Employees

ADD Email VARCHAR(100) UNIQUE;

Effect: Adds a new column to the Employees table:

Before:

EmployeeID FirstName LastName BirthDate HireDate Department Salary ManagerID

1 John Smith 1985-05-15 2020-01-10 IT 75000.00 NULL
2 Jane Doe 1990-02-20 2019-06-15 Finance 80000.00 1

After:

EmployeeID FirstName LastName BirthDate HireDate Department Salary ManagerID Email

1 John Smith 1985-05-15 2020-01-10 IT 75000.00 NULL NULL
2 Jane Doe 1990-02-20 2019-06-15 Finance 80000.00 1 NULL

Modifying a Column:

ALTER TABLE Employees

ALTER COLUMN Department VARCHAR(100);

Effect: Changes the Department column's data type from VARCHAR(50) to VARCHAR(100), allowing longer department names.

Adding a Constraint:

ALTER TABLE Employees

ADD CONSTRAINT chk_salary CHECK (Salary >= 3000);

Effect: Adds a check constraint to ensure that all salary values are at least 3000. This constraint is applied to both existing and future data.

DROP
The DROP command removes existing database objects.

Dropping a Table:

DROP TABLE Employees;

Effect: Completely removes the Employees table and all its data from the database.

Dropping a Database:
 DROP DATABASE EmployeeManagement;

Effect: Completely removes the EmployeeManagement database and all its objects and data.

Dropping a View:

DROP VIEW EmployeeSummary;

Effect: Removes the EmployeeSummary view from the database. This does not affect the underlying Employees table.

TRUNCATE
The TRUNCATE command quickly removes all records from a table without logging individual row deletions.

TRUNCATE TABLE Employees;

Effect:

Before:

EmployeeID FirstName LastName Department Salary

1 John Smith IT 75000.00
2 Jane Doe Finance 80000.00

After:

EmployeeID FirstName LastName Department Salary

The table structure remains, but all data is removed.

Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are used to manipulate data stored in database objects.

INSERT
The INSERT command adds new records to a table.

Insert a Single Record:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)

VALUES (1, 'John', 'Doe', 'IT', 75000);

Effect:

Before:

EmployeeID FirstName LastName Department Salary

After:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00

Insert Multiple Records:

 INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES
(2, 'Jane', 'Smith', 'HR', 65000),
(3, 'Michael', 'Johnson', 'Finance', 80000),
(4, 'Emily', 'Williams', 'Marketing', 70000);

Effect:

Before:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00

After:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
2 Jane Smith HR 65000.00
3 Michael Johnson Finance 88000.00
4 Emily Williams Marketing 70000.00

After:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
3 Michael Johnson Finance 88000.00
4 Emily Williams Marketing 70000.00

Delete with Subqueries:

DELETE FROM Employees

WHERE EmployeeID IN (
SELECT EmployeeID FROM PerformanceReviews WHERE Rating < 3
);

Effect:

If we have:

PerformanceReviews table:

ReviewID EmployeeID Rating ReviewDate

1 1 4 2023-01-15
2 3 2 2023-01-20
3 4 5 2023-01-25

Employees table (before):

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
3 Michael Johnson Finance 88000.00
4 Emily Williams Marketing 70000.00

Employees table (after):

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
4 Emily Williams Marketing 70000.00
 Delete All Records:

DELETE FROM Employees;

Effect:

Before:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
4 Emily Williams Marketing 70000.00

After:

EmployeeID FirstName LastName Department Salary

MERGE
The MERGE command (also called UPSERT in some systems) performs INSERT, UPDATE, and DELETE operations in a single statement based on a
condition.

MERGE INTO Employees AS target

USING NewEmployeesData AS source
ON (target.EmployeeID = source.EmployeeID)
WHEN MATCHED THEN
UPDATE SET
target.FirstName = source.FirstName,
target.LastName = source.LastName,
target.Department = source.Department,
target.Salary = source.Salary
WHEN NOT MATCHED THEN
INSERT (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Department, source.Salary);

Effect:

If we have:

Employees table (before):

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
2 Jane Smith HR 65000.00

NewEmployeesData table:

EmployeeID FirstName LastName Department Salary

1 John Doe Engineering 85000.00
3 Michael Johnson Finance 80000.00

Employees table (after):

EmployeeID FirstName LastName Department Salary

1 John Doe Engineering 85000.00
2 Jane Smith HR 65000.00
3 Michael Johnson Finance 80000.00
 Data Control Language (DCL)
Data Control Language (DCL) commands are used to control access to data within the database.

GRANT
The GRANT command gives specific privileges to users or roles.

GRANT SELECT, INSERT ON Employees TO HR_Staff;

Effect: Gives the HR_Staff role or user the ability to view (SELECT) and add (INSERT) records in the Employees table, but not UPDATE or DELETE
them.

GRANT ALL PRIVILEGES ON DATABASE CompanyDB TO Admin;

Effect: Gives the Admin user full control over the CompanyDB database, including all objects within it.

REVOKE
The REVOKE command removes previously granted privileges from users or roles.

REVOKE INSERT ON Employees FROM HR_Staff;

Effect: Removes the ability for HR_Staff to insert new records into the Employees table. They would still retain SELECT privileges if those were
previously granted.

REVOKE ALL PRIVILEGES ON DATABASE CompanyDB FROM Analyst;

Effect: Removes all privileges that the Analyst user had on the CompanyDB database.

DENY
The DENY command (available in some DBMS like SQL Server) explicitly prevents a user from having a specific privilege, even if granted through
group memberships.

DENY DELETE ON Employees TO HR_Staff;

Effect: Prevents HR_Staff from deleting records from the Employees table, even if they belong to another role that has DELETE privileges.

Transaction Control Language (TCL)

Transaction Control Language (TCL) commands are used to manage the changes made by DML statements.

COMMIT
The COMMIT command permanently saves changes made during the current transaction.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 456;
COMMIT;
 Effect:

Before:

AccountID CustomerName Balance

123 John Smith 5000.00
456 Jane Doe 3000.00

After (changes are permanently applied):

AccountID CustomerName Balance

123 John Smith 4000.00
456 Jane Doe 4000.00

ROLLBACK
The ROLLBACK command undoes changes made during the current transaction.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 123;
-- Oops, wrong account!
ROLLBACK;

Effect:

Before:

AccountID CustomerName Balance

123 John Smith 5000.00
456 Jane Doe 3000.00

During transaction (temporary state):

AccountID CustomerName Balance

123 John Smith 4000.00
456 Jane Doe 3000.00

After ROLLBACK (reverts to original state):

AccountID CustomerName Balance

123 John Smith 5000.00
456 Jane Doe 3000.00

SAVEPOINT
The SAVEPOINT command creates points within a transaction to which you can later roll back.
 BEGIN TRANSACTION;
INSERT INTO Orders (OrderID, CustomerID, Amount) VALUES (1001, 5, 150.00);
SAVEPOINT order_inserted;

INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (1001, 101, 2);
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (1001, 102, 1);
-- Oops, wrong product!

ROLLBACK TO order_inserted;
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (1001, 101, 2);
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (1001, 103, 1);
COMMIT;

Effect:

Orders table (before):

OrderID CustomerID Amount

1000 3 75.50

OrderItems table (before):

OrderID ProductID Quantity

1000 101 1

After the COMMIT:

Orders table:

OrderID CustomerID Amount

1000 3 75.50
1001 5 150.00

OrderItems table:

OrderID ProductID Quantity

1000 101 1
1001 101 2
1001 103 1

Note that we rolled back to the savepoint, so the incorrect product entries were not saved.

Data Query Language (DQL)

Data Query Language (DQL) is used to query and retrieve data from database objects.

SELECT
The SELECT statement is the most commonly used SQL command for retrieving data.

SELECT * FROM Employees;

Effect: Retrieves all columns and all rows from the Employees table:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
2 Jane Smith HR 65000.00
EmployeeID FirstName LastName Department Salary
3 Michael Johnson Finance 80000.00
4 Emily Williams Marketing 70000.00

SELECT FirstName, LastName, Salary FROM Employees;

Effect: Retrieves only specified columns from all rows:

FirstName LastName Salary

John Doe 75000.00
Jane Smith 65000.00
Michael Johnson 80000.00
Emily Williams 70000.00

SELECT FirstName, LastName, Salary * 1.1 AS NewSalary FROM Employees;

Effect: Calculates a new column based on an existing column:

FirstName LastName NewSalary

John Doe 82500.00
Jane Smith 71500.00
Michael Johnson 88000.00
Emily Williams 77000.00

Basic SQL Queries

SELECT Statement
The SELECT statement is the fundamental SQL command used to retrieve data from a database. It allows you to specify which columns to retrieve,
filter records, sort results, and perform calculations.

Basic Syntax

SELECT column1, column2, ...

FROM table_name;

Selecting All Columns

SELECT * FROM Customers;

Effect: Retrieves all columns from the Customers table:

CustomerID Name Email Phone City

1 John Smith [email protected] 555-123-4567 New York
2 Jane Doe [email protected] 555-987-6543 Chicago
3 Bob Johnson [email protected] 555-456-7890 Los Angeles
4 Mary Williams [email protected] 555-789-0123 Houston

Selecting Specific Columns

 SELECT Name, Email, City FROM Customers;

Effect: Retrieves only specified columns:

Name Email City

John Smith [email protected] New York
Jane Doe [email protected] Chicago
Bob Johnson [email protected] Los Angeles
Mary Williams [email protected] Houston

Column Aliases
Column aliases provide temporary names for columns in the result set, making the output more readable or meaningful.

SELECT
Name AS CustomerName,
Email AS ContactEmail,
Phone AS ContactPhone
FROM Customers;

Effect:

CustomerName ContactEmail ContactPhone

John Smith [email protected] 555-123-4567
Jane Doe [email protected] 555-987-6543
Bob Johnson [email protected] 555-456-7890
Mary Williams [email protected] 555-789-0123

Calculated Columns
You can perform calculations in the SELECT clause to create derived columns.

SELECT
ProductName,
UnitPrice,
UnitsInStock,
UnitPrice * UnitsInStock AS InventoryValue
FROM Products;

Effect:

ProductName UnitPrice UnitsInStock InventoryValue

Product A 10.00 50 500.00
Product B 25.00 20 500.00
Product C 15.50 30 465.00
Product D 5.00 100 500.00

String Concatenation
You can combine text fields using string concatenation (the syntax varies by database system).
 -- SQL Server / SQLite
SELECT
FirstName + ' ' + LastName AS FullName
FROM Employees;

-- MySQL / PostgreSQL
SELECT
CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

Effect:

FullName
John Doe
Jane Smith
Michael Johnson
Emily Williams

Filtering with WHERE Clause

The WHERE clause is used to filter records based on specified conditions.

Basic Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition;

Comparison Operators

SELECT ProductName, UnitPrice

FROM Products
WHERE UnitPrice > 20;

Effect:

ProductName UnitPrice
Product B 25.00

Common comparison operators:

Equal to: =
Not equal to: <> or !=
Greater than: >
Less than: <
Greater than or equal to: >=
Less than or equal to: <=

String Comparisons
 SELECT CustomerID, Name, City
FROM Customers
WHERE City = 'Chicago';

Effect:

CustomerID Name City

2 Jane Doe Chicago

Pattern Matching with LIKE

The LIKE operator is used for pattern matching with wildcards:

% represents zero, one, or multiple characters

_ represents a single character

SELECT Name, Email

FROM Customers
WHERE Name LIKE 'J%';

Effect: Finds all customers whose names start with 'J':

Name Email
John Smith [email protected]
Jane Doe [email protected]

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%A%';

Effect: Finds all products with 'A' anywhere in the name:

ProductName
Product A

SELECT FirstName
FROM Employees
WHERE FirstName LIKE '_o%';

Effect: Finds names with 'o' as the second letter:

FirstName
John
Bob

Sorting with ORDER BY

The ORDER BY clause is used to sort the result set by one or more columns.

Basic Syntax
 SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Sorting by a Single Column

SELECT ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice DESC;

Effect: Sorts products by price in descending order (highest first):

ProductName UnitPrice
Product B 25.00
Product C 15.50
Product A 10.00
Product D 5.00

Sorting by Multiple Columns

SELECT FirstName, LastName, Salary

FROM Employees
ORDER BY Department ASC, Salary DESC;

Effect: Sorts employees first by department (alphabetically) and then by salary (highest first) within each department:

FirstName LastName Salary Department

Michael Johnson 80000.00 Finance
Jane Smith 65000.00 HR
John Doe 75000.00 IT
Emily Williams 70000.00 Marketing

Sorting by Column Position

You can also sort by the position of columns in the SELECT list:

SELECT ProductName, CategoryID, UnitPrice

FROM Products
ORDER BY 2 ASC, 3 DESC;

Effect: Sorts by CategoryID (ascending) and then by UnitPrice (descending):

ProductName CategoryID UnitPrice

Product C 1 15.50
Product A 1 10.00
Product B 2 25.00
Product D 3 5.00

Removing Duplicates with DISTINCT

The DISTINCT keyword is used to eliminate duplicate values from the result set.
 Basic Syntax

SELECT DISTINCT column1, column2, ...

FROM table_name;

Single Column DISTINCT

SELECT DISTINCT Department

FROM Employees;

Effect: Shows each department only once:

Department
IT
HR
Finance
Marketing

Without DISTINCT, if there were multiple employees in each department, each department would appear multiple times in the result.

Multi-Column DISTINCT

SELECT DISTINCT City, Country

FROM Customers;

Effect: Shows each unique city-country combination:

City Country
New York USA
Chicago USA
London UK
Paris France

This treats each combination of City and Country as a distinct entity.

DISTINCT with COUNT

You can use DISTINCT with aggregate functions to count unique values:

SELECT COUNT(DISTINCT Department) AS NumberOfDepartments

FROM Employees;

Effect:

NumberOfDepartments
4

Logical Operators
Logical operators allow you to combine multiple conditions in SQL queries, providing more complex filtering capabilities.

BETWEEN Operator
 The BETWEEN operator selects values within a specified range (inclusive).

Syntax

SELECT column1, column2, ...

FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Numeric Range Examples

SELECT ProductName, UnitPrice

FROM Products
WHERE UnitPrice BETWEEN 10 AND 20;

Effect: Finds products priced between $10 and $20 (inclusive):

ProductName UnitPrice
Product A 10.00
Product C 15.50

Date Range Examples

SELECT OrderID, CustomerID, OrderDate

FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

Effect: Finds orders placed in January 2023:

OrderID CustomerID OrderDate

1001 3 2023-01-05
1002 1 2023-01-12
1003 4 2023-01-25

NOT BETWEEN
You can negate the BETWEEN operator:

SELECT ProductName, UnitPrice

FROM Products
WHERE UnitPrice NOT BETWEEN 10 AND 20;

Effect: Finds products with prices less than $10 or greater than $20:

ProductName UnitPrice
Product B 25.00
Product D 5.00

IN Operator
The IN operator allows you to specify multiple values in a WHERE clause, functioning as a shorthand for multiple OR conditions.

Syntax
 SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Simple IN Example

SELECT CustomerID, Name, City

FROM Customers
WHERE City IN ('New York', 'Chicago', 'Los Angeles');

Effect: Finds customers from specified cities:

CustomerID Name City

1 John Smith New York
2 Jane Doe Chicago
3 Bob Johnson Los Angeles

IN with Subquery
The IN operator can also be used with a subquery:

SELECT ProductName, ProductID

FROM Products
WHERE CategoryID IN (
SELECT CategoryID
FROM Categories
WHERE CategoryName IN ('Beverages', 'Condiments')
);

Effect: If the Categories table has:

CategoryID CategoryName
1 Beverages
2 Condiments
3 Confections

And Products table has:

ProductID ProductName CategoryID

101 Product A 1
102 Product B 2
103 Product C 3
104 Product D 1

The query would return:

ProductName ProductID
Product A 101
Product B 102
Product D 104

NOT IN
You can negate the IN operator:
 SELECT CustomerID, Name, City
FROM Customers
WHERE City NOT IN ('New York', 'Chicago');

Effect: Finds customers not from New York or Chicago:

CustomerID Name City

3 Bob Johnson Los Angeles
4 Mary Williams Houston

AND Operator
The AND operator combines two or more conditions, and returns records that satisfy all conditions.

Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example

SELECT ProductName, UnitPrice, UnitsInStock

FROM Products
WHERE UnitPrice > 10 AND UnitsInStock > 20;

Effect: Finds products that cost more than $10 and have more than 20 units in stock:

ProductName UnitPrice UnitsInStock

Product C 15.50 30
Product B 25.00 20

Multiple AND Conditions

SELECT EmployeeID, FirstName, LastName, Salary, Department

FROM Employees
WHERE Salary >= 70000 AND Department = 'IT' AND HireDate >= '2020-01-01';

Effect: Finds IT employees with salaries of at least $70,000 who were hired after January 1, 2020:

EmployeeID FirstName LastName Salary Department

1 John Doe 75000.00 IT

OR Operator
The OR operator combines two or more conditions, and returns records that satisfy at least one of the conditions.

Syntax
 SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example

SELECT ProductName, UnitPrice, UnitsInStock

FROM Products
WHERE UnitPrice > 20 OR UnitsInStock > 50;

Effect: Finds products that either cost more than $20 or have more than 50 units in stock:

ProductName UnitPrice UnitsInStock

Product B 25.00 20
Product D 5.00 100

Combining AND and OR

When combining AND and OR operators, use parentheses to specify the order of evaluation:

SELECT EmployeeID, FirstName, LastName, Salary, Department

FROM Employees
WHERE (Department = 'IT' OR Department = 'Finance') AND Salary > 75000;

Effect: Finds employees in either IT or Finance departments with salaries over $75,000:

EmployeeID FirstName LastName Salary Department

3 Michael Johnson 80000.00 Finance

Without parentheses, the query might not produce the expected results due to operator precedence (AND typically has higher precedence than OR).

NOT Operator
The NOT operator negates a condition, returning records that do not match the condition.

Syntax

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

Simple NOT Example

SELECT ProductName, CategoryID

FROM Products
WHERE NOT CategoryID = 1;

Effect: Finds products that are not in category 1:

ProductName CategoryID
Product B 2
Product C 3
 NOT with Other Operators
NOT can be combined with other operators:

SELECT CustomerID, Name, City

FROM Customers
WHERE NOT City IN ('New York', 'Chicago') AND NOT Name LIKE 'J%';

Effect: Finds customers not from New York or Chicago, and whose names don't start with 'J':

CustomerID Name City

3 Bob Johnson Los Angeles
4 Mary Williams Houston

NULL Values
NULL in SQL represents a missing or unknown value. Understanding how to work with NULL values is crucial for effective database querying and
management.

Understanding NULL Values

NULL is not the same as zero, an empty string, or a string containing only spaces. NULL means "no value" or "unknown value". Some important
characteristics of NULL:

NULL is not equal to anything, including another NULL

Arithmetic operations involving NULL usually result in NULL
Comparisons with NULL using standard operators typically yield NULL, not TRUE or FALSE

Disallowing NULL Values

You can prevent columns from containing NULL values by using the NOT NULL constraint.

Creating Tables with NOT NULL Constraints

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(20),
Address VARCHAR(200)
);

Effect: The Name and Email columns cannot contain NULL, but Phone and Address can.

Adding NOT NULL Constraint to Existing Columns

ALTER TABLE Customers

ALTER COLUMN Address VARCHAR(200) NOT NULL;

Effect: Changes the Address column to disallow NULL values. This would fail if any existing rows contain NULL in the Address column.
 Setting Default Values
You can provide default values for columns to avoid NULL values:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL DEFAULT CURRENT_DATE,
Status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

Effect: If OrderDate is not specified during insertion, it will get the current date instead of NULL.

Comparisons Using NULL Values

Regular comparison operators (=, <>, <, >, etc.) don't work as expected with NULL values.

Incorrect NULL Comparison

-- This won't work as expected

SELECT CustomerID, Name, Phone
FROM Customers
WHERE Phone = NULL;

Effect: Returns no rows, regardless of the data, because x = NULL evaluates to NULL (not TRUE) for any value of x.

Correct NULL Comparisons

To properly check for NULL values, use the IS NULL and IS NOT NULL operators:

SELECT CustomerID, Name, Phone

FROM Customers
WHERE Phone IS NULL;

Effect: Finds customers with no phone number:

CustomerID Name Phone

2 Jane Doe NULL
4 Mary Williams NULL

SELECT CustomerID, Name, Phone

FROM Customers
WHERE Phone IS NOT NULL;

Effect: Finds customers with a phone number:

CustomerID Name Phone

1 John Smith 555-123-4567
3 Bob Johnson 555-456-7890

IS NULL and IS NOT NULL Operators

 IS NULL with Multiple Conditions

SELECT ProductID, ProductName, SupplierID, CategoryID

FROM Products
WHERE SupplierID IS NULL OR CategoryID IS NULL;

Effect: Finds products missing either supplier or category information:

ProductID ProductName SupplierID CategoryID

102 Product B NULL 2
104 Product D 3 NULL

COALESCE Function
The COALESCE function returns the first non-NULL value in a list:

SELECT
ProductName,
COALESCE(SupplierName, 'No Supplier') AS Supplier
FROM Products
LEFT JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID;

Effect: Shows supplier name or "No Supplier" for products without a supplier:

ProductName Supplier

Product A Supplier X
Product B No Supplier
Product C Supplier Y
Product D Supplier Z

NULLIF Function
The NULLIF function returns NULL if two expressions are equal, otherwise it returns the first expression:

SELECT
ProductName,
UnitsInStock,
ReorderLevel,
NULLIF(UnitsInStock, ReorderLevel) AS StockDifference
FROM Products;

Effect: Returns NULL when stock equals reorder level, otherwise returns stock level:

ProductName UnitsInStock ReorderLevel StockDifference

Product A 50 Michael Johnson Finance 80000.00
4 Emily Williams Marketing 70000.00

Insert Using a SELECT Statement:

 INSERT INTO EmployeeArchive (EmployeeID, FirstName, LastName, Department, Salary)
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE HireDate < '2020-01-01';

Effect:

If we have:

Employees table:

EmployeeID FirstName LastName Department Salary HireDate

1 John Doe IT 75000.00 2020-05-15
2 Jane Smith HR 65000.00 2019-02-10
3 Michael Johnson Finance 80000.00 2019-11-20
4 Emily Williams Marketing 70000.00 2021-03-05

EmployeeArchive table (before):

EmployeeID FirstName LastName Department Salary

EmployeeArchive table (after):

EmployeeID FirstName LastName Department Salary

2 Jane Smith HR 65000.00
3 Michael Johnson Finance 80000.00

UPDATE
The UPDATE command modifies existing records in a table.

Update a Single Column for All Records:

UPDATE Employees
SET Department = 'Information Technology'
WHERE Department = 'IT';

Effect:

Before:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
2 Jane Smith HR 65000.00
3 Michael Johnson Finance 80000.00
4 Emily Williams Marketing 70000.00

After:

EmployeeID FirstName LastName Department Salary

1 John Doe Information Technology 75000.00
2 Jane Smith HR 65000.00
3 Michael Johnson Finance 80000.00
4 Emily Williams Marketing 70000.00

Update Multiple Columns:

 UPDATE Employees
SET Salary = Salary * 1.1,
LastUpdated = CURRENT_TIMESTAMP
WHERE Department = 'Finance';

Effect:

Before:

EmployeeID FirstName LastName Department Salary LastUpdated

1 John Doe IT 75000.00 NULL
2 Jane Smith HR 65000.00 NULL
3 Michael Johnson Finance 80000.00 NULL
4 Emily Williams Marketing 70000.00 NULL

After:

EmployeeID FirstName LastName Department Salary LastUpdated

1 John Doe IT 75000.00 NULL
2 Jane Smith HR 65000.00 NULL
3 Michael Johnson Finance 88000.00 2023-06-15 10:30:15
4 Emily Williams Marketing 70000.00 NULL

DELETE
The DELETE command removes records from a table.

Delete Specific Records:

DELETE FROM Employees

WHERE Department = 'HR';

Effect:

Before:

EmployeeID FirstName LastName Department Salary

1 John Doe IT 75000.00
2 Jane Smith HR 65000.00
3