Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

SQL
(Notes by Apna College)
What is Database?
Database is a collection of interrelated data
...

What is RDBMS?
● RDBMS (Relational Database Management System) - is a DBMS based on the
concept of tables (also called relations)
...

● Eg - MySQL, PostgreSQL, Oracle etc
...

(It is not a database, it is a language used to interact with database)
We use SQL for CRUD Operations :
● CREATE - To create databases, tables, insert tuples in tables etc
● READ - To read data present in the database
...

● DELETE - Delete database, table or specific data point/tuple/row or multiple rows
...
Eg: select is the same as SELECT in SQL
...


SQL Data Types
In SQL, data types define the kind of data that can be stored in a column or variable
...
mysql
...
0/en/data-types
...
x can range from 1 to 64

BIT(2)

FLOAT

Decimal number - with precision to 23 digits

FLOAT

DOUBLE

Decimal number - with 24 to 53 digits

DOUBLE

BOOLEAN

Boolean values 0 or 1

BOOLEAN

DATE

date in format of YYYY-MM-DD ranging from
1000-01-01 to 9999-12-31

DATE

TIME

HH:MM:SS

TIME

YEAR

year in 4 digits format ranging from 1901 to 2155

YEAR

*Note - CHAR is for fixed length & VARCHAR is for variable length strings
...

We can also use UNSIGNED with datatypes when we only have positive values to add
...
DQL (Data Query Language) : Used to retrieve data from databases
...
DDL (Data Definition Language) : Used to create, alter, and delete database objects
like tables, indexes, etc
...
DML (Data Manipulation Language): Used to modify the database
...
DCL (Data Control Language): Used to grant & revoke permissions
...
TCL (Transaction Control Language): Used to manage transactions
...
Data Definition Language (DDL)
Data Definition Language (DDL) is a subset of SQL (Structured Query Language)
responsible for defining and managing the structure of databases and their objects
...

Key DDL Commands are:

● CREATE TABLE:
○ Used to create a new table in the database
...

○ Example:
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50),
salary DECIMAL(10, 2));

● ALTER TABLE:
○ Used to modify the structure of an existing table
...

○ Example: ALTER TABLE employees ADD COLUMN email VARCHAR(100);
● DROP TABLE:
○ Used to delete an existing table along with its data and structure
...

○ Improves query performance by enabling faster data retrieval
...

○ Example: DROP INDEX idx_employee_name;
● CREATE CONSTRAINT:
○ Used to define constraints that ensure data integrity
...


○ Example: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN
KEY (customer_id) REFERENCES customers(id);

● DROP CONSTRAINT:
○ Used to remove an existing constraint from a table
...

○ Syntax – TRUNCATE TABLE table_name

2
...

The SELECT statement is the foundation of DQL and allows us to extract specific columns
from a table
...


Syntax: SELECT column1, column2,
...
are the field names of the table
...

Syntax: SELECT column1, column2,
...

Note: In some versions of SQL this operator may be written as !=

●

AND, OR and NOT:

-

The WHERE clause can be combined with AND, OR, and NOT operators
...


-

The OR operator displays a record if any of the conditions separated by OR is TRUE
...


Syntax:

SELECT column1, column2,
...
;
SELECT column1, column2,
...
;
SELECT column1, column2,
...

Syntax: SELECT DISTINCT column1, column2 FROM table_name;

●

LIKE:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column
...

Example: SELECT * FROM products WHERE category_id IN (1, 2, 3);

●

BETWEEN:

Filters results within a specified range in the WHERE clause
...

Example: SELECT * FROM customers WHERE email IS NULL;

●

AS:

Renames columns or expressions in query results
...

Basic Syntax:
-

The ORDER BY clause is used after the SELECT statement to sort query results
...

You can explicitly specify descending order using the DESC keyword
...

Rows are first sorted based on the first column, and for rows with equal values,
subsequent columns are used for further sorting
...

Example: SELECT product_name, price, price * 1
...

You can control the sorting behaviour of NULL values using the NULLS FIRST or
NULLS LAST options
...

Example: SELECT product_name, price FROM products ORDER BY 2 DESC, 1
ASC;

GROUP BY

The GROUP BY clause in SQL is used to group rows from a table based on one or more
columns
...


-

Syntax: SELECT column1, aggregate_function(column2) FROM table_name
GROUP BY column1;

-

Aggregation Functions:
○ Aggregation functions (e
...
, COUNT, SUM, AVG, MAX, MIN) are often used
with GROUP BY to calculate values for each group
...

It's similar to the WHERE clause but operates on grouped data
...

This creates a hierarchical grouping based on the specified columns
...

Example: SELECT department, COUNT(*) FROM employees GROUP BY
department ORDER BY COUNT(*) DESC;

AGGREGATE FUNCTIONS

These are used to perform calculations on groups of rows or entire result sets
...

Common Aggregate Functions:
-

COUNT():
Counts the number of rows in a group or result set
...


-

AVG():

Computes the average of numeric values in a group or result set
...


-

MIN():
Retrieves the minimum value in a group or result set
...
DATA MANIPULATION LANGUAGE

Data Manipulation Language (DML) in SQL encompasses commands that manipulate data
within a database
...

●

INSERT:

-

The INSERT statement adds new records to a table
...
) VALUES (value1, value2,

...

Syntax: UPDATE table_name SET column1 = value1, column2 = value2,
...

Syntax: DELETE FROM table_name WHERE condition;
Example: DELETE FROM employees WHERE last_name = 'Doe';

4
...

DCL commands are used to control who can access the data, modify the data, or perform
administrative tasks within a database
...

There are two main DCL commands in SQL: GRANT and REVOKE
...
GRANT:
The GRANT command is used to provide specific privileges or permissions to users or roles
...

Syntax:
GRANT privilege_type
ON object_name
TO user_or_role;
In this syntax:

● privilege_type refers to the specific privilege or permission being granted (e
...
,
SELECT, INSERT, UPDATE, DELETE)
...
g
...

● user_or_role is the name of the user or role that is being granted the privilege
...
REVOKE:
The REVOKE command is used to remove or revoke specific privileges or permissions that
have been previously granted to users or roles
...

● object_name is the name of the database object from which the privilege is being
revoked
...

Example: Revoking the SELECT privilege on the "Employees" table from the "Analyst" user:
REVOKE SELECT ON Employees FROM Analyst;

DCL and Database Security:
DCL plays a crucial role in ensuring the security and integrity of a database system
...
Proper use of GRANT and REVOKE commands
ensures that only users who require specific privileges can perform certain actions on
database objects
...
Transaction Control Language (TCL)

Transaction Control Language (TCL) deals with the management of transactions within a
database
...

Transactions ensure data consistency, integrity, and reliability in a database by grouping
related operations together and either committing or rolling back changes based on the
success or failure of those operations
...


1
...


It makes all the changes applied to the database since the last COMMIT or ROLLBACK
command permanent
...

Example: Committing changes made during a transaction:
UPDATE Employees
SET Salary = Salary * 1
...
ROLLBACK:
The ROLLBACK command is used to undo changes made during a transaction
...

ROLLBACK is typically used when an error occurs during the execution of a transaction,
ensuring that the database remains in a consistent state
...
SAVEPOINT:
The SAVEPOINT command creates a named point within a transaction, allowing you to set a
point to which you can later ROLLBACK if needed
...

Syntax: SAVEPOINT savepoint_name;
Example: Using SAVEPOINT to create a point within a transaction:
BEGIN;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 123;
SAVEPOINT before_withdrawal;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 456;
-- An error occurs here
ROLLBACK TO before_withdrawal;
-- The first update is still applied
COMMIT;

TCL and Transaction Management:
Transaction Control Language (TCL) commands are vital for managing the integrity and
consistency of a database's data
...

TCL commands are used in combination with Data Manipulation Language (DML) and other
SQL commands to ensure that the database remains in a reliable state despite unforeseen
errors or issues
...

Joins are used to retrieve data from multiple tables by linking them together using a common
key or column
...

2
...

4
...

The result of an inner join includes only the rows where the join condition is met in all
participating tables
...

Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1
...
column;
Here:

●
●
●
●

columns refers to the specific columns you want to retrieve from the tables
...

column is the common column used to match rows between the tables
...


Example: Consider two tables: Customers and Orders
...
CustomerName, Orders
...
CustomerID = Orders
...
However, unlike inner joins, outer joins also include rows that do not have
matching values in both tables
...


Types:
There are three types of outer joins: left outer join, right outer join, and full outer join
...
Left Outer Join (Left Join):
A left outer join returns all the rows from the left table and the matching rows from the right
table
...

Example:
SELECT Customers
...
Product
FROM Customers
LEFT JOIN Orders ON Customers
...
CustomerID;
Result:

CustomerName

Product

Alice

Laptop

Bob

Headphones

Carol

Smartphone

NULL

Monitor

In this example, the left outer join includes all rows from the Customers table
...


2
...

If there is no match in the left table, the result will still include the right table's row with NULL
values in the left table's columns
...

SELECT Customers
...
Product
FROM Customers
RIGHT JOIN Orders ON Customers
...
CustomerID;
Result:

CustomerName

Product

Alice

Laptop

Carol

Smartphone

Bob

Headphones

NULL

Keyboard

Here, the right outer join includes all rows from the Orders table
...


3
...

If there's no match, NULL values appear in columns from the table where there's no
corresponding value
...

SELECT Customers
...
Product
FROM Customers
FULL OUTER JOIN Orders ON Customers
...
CustomerID;
Result:

CustomerName

Product

Alice

Laptop

Bob

Headphones

Carol

Smartphone

NULL

Monitor

NULL

Keyboard

In this full outer join example, all rows from both tables are included in the result
...


3) Cross Join

A cross join, also known as a Cartesian product, is a type of join operation in a Database
Management System (DBMS) that combines every row from one table with every row from
another table
...
Instead, it generates a result set that contains all possible combinations
of rows from both tables
...

Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;
In this syntax:

● columns refers to the specific columns you want to retrieve from the cross-joined
tables
...

Example: Consider two tables: Students and Courses
...
StudentName, Courses
...
As a result, each student is paired with each
course, leading to a total of four rows in the result set
...

This technique is useful when a table contains hierarchical or related data and you need to
compare or analyse rows within the same table
...

In a self join, you treat the table as if it were two separate tables, referring to them with
different aliases
...
column = alias2
...

● table1 is the name of the table you're joining with itself
...

● column is the column you use as the join condition to link rows from the same table
...

Employees Table:
EmployeeID

EmployeeName

ManagerID

1

Alice

3

2

Bob

3

3

Carol

NULL

4

David

1

Self Join Query:
SELECT e1
...
EmployeeName AS Manager
FROM Employees AS e1
JOIN Employees AS e2 ON e1
...
EmployeeID;
Result:
Employee

Manager

Alice

Carol

Bob

Carol

David

Alice

In this example, the self join is performed on the Employees table to find the relationship
between employees and their managers
...


SET OPERATIONS
Set operations in SQL are used to combine or manipulate the result sets of multiple SELECT
queries
...

Set operations provide powerful tools for managing and manipulating data, enabling you to
analyse and combine information in various ways
...
UNION:
The UNION operator combines the result sets of two or more SELECT queries into a single
result set
...

Example:
Assume we have two tables: Customers and Suppliers
...
INTERSECT:
The INTERSECT operator returns the common rows that exist in the result sets of two or
more SELECT queries
...


Example: Using the same tables as before
...


3
...

Example: Using the same tables as before
...


4
...
It simply concatenates all rows from the different
result sets
...

SELECT CustomerName FROM Customers
UNION ALL
SELECT SupplierName FROM Suppliers;
Result:

CustomerName
Alice
Bob
SupplierA
SupplierB

Difference between Set Operations and Joins

Aspect

Purpose

Set Operations

Manipulate result sets based on
set theory principles
...


Data Source

Combining Rows

Result sets of SELECT queries
...


Combine rows from different

Combine rows from different

result sets
...


conditions
...


Can combine columns from
different tables, regardless of
data types or column numbers
...


JOIN, FULL JOIN
...


for combining data
...


Joins do not inherently handle
duplicates; it depends on the join
type and data
...


their relationships
...


counts
...


Joins can be more complex and
resource-intensive, especially for
larger datasets
...

Subqueries are often used to retrieve data that will be used for filtering, comparison, or
calculation within the context of a larger query
...

Syntax:
SELECT columns

FROM table
WHERE column OPERATOR (SELECT column FROM table WHERE condition);
In this syntax:

●
●
●
●
●

columns refers to the specific columns you want to retrieve from the outer query
...

column is the column you're applying the operator to in the outer query
...

(SELECT column FROM table WHERE condition) is the subquery that provides the
input for the comparison
...

Products Table:
ProductID

ProductName

Price

1

Laptop

1000

2

Smartphone

500

3

Headphones

50

Orders Table:
OrderID

ProductID

Quantity

101

1

2

102

3

1

For Example: Retrieve the product names and quantities for orders with a total cost greater
than the average price of all products
...


Combine data from related tables
based on specified conditions
...


Data from multiple related tables
...


Combines rows from different tables
based on specified join conditions
...


Joins return multi-column result
sets
...


Joins can be more efficient for
combining data from multiple tables
...


Joins can become complex, but are
more suited for handling large-scale
data retrieval and combination
tasks
...


Joins are primarily used in the
FROM clause for combining tables

---