SQL

• SQL is a standard database language

• used to access and manipulate data in databases.
• SQL stands for Structured Query Language.
• IBM Computer Scientists developed it in the 1970s.
• SQL is a query language that communicates with databases.
• By executing queries SQL can create, update, delete, and retrieve data in databases like MySQL,
Oracle, PostgreSQL, etc.
Why SQL?
SQL is essential in the realm of databases because:
Standardization:
SQL is a standardized language, meaning it has a well-defined syntax and semantics that is consistent across
different database systems (though some variations exist).
Simplicity and Flexibility:
SQL is relatively easy to learn and use.
Its syntax is human-readable, making it accessible to both developers and non-developers.
Powerful Data Manipulation:
SQL allows users to perform complex operations on large amounts of data efficiently, including filtering,
grouping, and aggregating data.
Interoperability:
Most relational database management systems (RDBMS) support SQL, making it a widely applicable skill for
interacting with different database systems like MySQL, PostgreSQL, Oracle, SQL Server, etc.
Components of a SQL System
Match the Following
• ? are structured collections of data organized into tables, rows, and columns.
They serve as repositories for storing information efficiently and provide a way to manage and access data.
• ? are the fundamental building blocks of a database, consisting of rows (records) and columns (attributes or fields).
They ensure data integrity and consistency by defining the structure and relationships of the stored information.
• ? are SQL commands used to interact with databases.
They enable users to retrieve, update, insert, or delete data from tables, allowing for efficient data manipulation and retrieval.
• ? are rules applied to tables to maintain data integrity.
They define conditions that data must meet to be stored in the database, ensuring accuracy and consistency.
• ? are pre-compiled SQL statements stored in the database.
They can accept parameters, execute complex operations, and return results, enhancing efficiency, reusability, and security in database
management.
• ? are groups of SQL statements that are executed as a single unit of work.
They ensure data consistency and integrity by allowing for the rollback of changes if any part of the transaction fails.

a) Tables | b) Queries | c) Transactions | d) Constraints | e) Stored Procedures | f) Databases

How SQL Works?
• Parser
Tokenization: Replaces words in the SQL statement with unique symbols.
Correctness Check: Ensures the SQL statement follows the rules, like ending with a
semicolon.
Authorization: Verifies that the user has the necessary permissions to execute the query.
• Relational Engine
Query Strategy: Develops a plan to efficiently retrieve, write, or update data.
Optimization: Looks for similar queries or uses previous methods to improve efficiency.
Byte Code: Converts the SQL statement into an intermediate representation for execution.
• Storage Engine
Execution: Interprets the byte code and performs the SQL operations.
Data Handling: Reads from and writes to the physical disk storage.
Result Delivery: Sends the outcome back to the requesting application.
Syntax/Rules for SQL
• End with a Semicolon: Every SQL statement should end with a ;.
• Line Splitting: You can split statements across multiple lines, but keywords must stay together.
• Spacing: Use spaces or other delimiters to separate identifiers, operator names, and literals.
• Comma Separation: Use a comma , to separate parameters within a clause.
• Clause Separation: Use a space to separate different clauses.
• Reserved Words: Reserved words can’t be used as identifiers unless you enclose them in
double quotes.
• Identifier Length: Identifiers can be up to 30 characters long.
• Identifier Start: Identifiers must start with a letter.
• Literals in Quotes: Enclose characters and date literals in single quotes.
• Numeric Literals: Numeric values can be written as simple numbers.
• Comments: Use /* and */ to enclose comments, which can span multiple lines
SQL Data Types
• For every database, data types are primarily classified into three categories.
Numeric Datatypes | Date and Time Datatypes |String Datatypes

Numeric Data Types in MySQL

BigInt: Stores very large integers. |Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Int: Stores large integers.| Range: -2,147,483,648 to 2,147,483,647.

SmallInt: Stores small integers. | Range: -32,768 to 32,767.

TinyInt: Stores very small integers. | Range: 0 to 255.

Bit: Stores binary values (0 or 1).

Decimal/Numeric: Stores exact numeric values with fixed precision. |Range: -10^38+1 to 10^38-1.
SQL Data Types
Approximate Numeric Data Types:

Float: Stores floating-point numbers. | Range: -1.79E+308 to 1.79E+308.

Real: Stores floating-point numbers with less precision. | Range: -3.40E+38 to 3.40E+38.

Character String Data Types

• Char:
Fixed-length non-Unicode characters. Max length: 8000 characters.
• Varchar:
Variable-length non-Unicode characters. Max length: 8000 characters.
• Varchar(max):
Variable-length non-Unicode characters. Max length: 2^31 characters.
• Text:
Variable-length non-Unicode characters. Max length: 2,147,483,647 characters.
SQL Keywords
•SELECT: Retrieves data from a database.
•FROM: Specifies the table from which to retrieve or modify data.
•WHERE: Filters records based on specified conditions.
•INSERT INTO: Adds new records to a table.
•UPDATE: Modifies existing records in a table.
•DELETE: Removes records from a table.
•ORDER BY: Sorts the result set in ascending or descending order.
•GROUP BY: Groups rows based on the columns
•HAVING: Filters groups based on a condition (used with GROUP BY).
•JOIN: Combines rows from two or more tables based on a related column.
•AND, OR, NOT: Logical operators used in filtering records.
Scenario:
Indian kings, dreaming of inter-universal exploration, book imaginative super
spacecrafts to embark on extraordinary journeys beyond our universe.
Each king represents a famous dynasty from Indian history, each spacecraft is a futuristic
vessel, and every journey is a booking recorded with essential details like timing and cost.

Kings
king_id (INT) name (VARCHAR) dynasty (VARCHAR) age (INT) kingdom (VARCHAR)
1 Akbar Mughal 45 Agra
2 Chola Raja Chola 52 Thanjavur

3 Krishnadevaraya Vijayanagara 40 Hampi

4 Shivaji Maratha 38 Pune
5 Maharana Pratap Sisodia 47 Mewar
Spacecrafts
spacecraft_id (INT) name (VARCHAR) speed (INT km/s) capacity (INT) price (DECIMAL) departure_universe
(VARCHAR)
1 Akash Missile 22000 10 5200000.00 Earth
2 Surya Speeder 18000 15 6000000.00 Mars
3 Chandra Voyager 25000 8 8000000.00 Moon
4 Vayu Glide 20000 6 3500000.00 Jupiter
5 Indra Rocket 30000 12 9000000.00 Venus

Bookings
booking_id (INT) king_id (INT) spacecraft_id (INT) journey_date journey_time total_price
(DATE) (TIME) (DECIMAL)

1 1 3 2025-12-01 09:00:00 8000000.00

2 4 1 2025-12-04 17:00:00 5200000.00
3 2 5 2025-12-10 13:30:00 9000000.00
4 3 2 2025-12-21 07:45:00 6000000.00
5 5 4 2025-12-25 16:15:00 3500000.00
 Write basic Queries

-- 1. Select all kings’ names and dynasties -- 1. Count number of kings

SELECT name, dynasty FROM kings; SELECT COUNT(*) AS king_count FROM kings;

-- 2. List all spacecrafts with speed and price -- 2. Find average spacecraft speed
SELECT name, speed, price FROM spacecrafts; SELECT AVG(speed) AS avg_speed FROM spacecrafts;

-- 3. Find bookings after December 10, 2025 -- 3. Sum total booking revenue
SELECT * FROM bookings WHERE journey_date > '2025- SELECT SUM(total_price) AS total_revenue FROM bookings;
12-10';
-- 4. Find the minimum price among spacecrafts
-- 4. Get distinct kingdoms among the kings SELECT MIN(price) AS min_price FROM spacecrafts;
SELECT DISTINCT kingdom FROM kings;
-- 5. Find the maximum age among kings
-- 5. Show all spacecrafts with capacity greater than 10 SELECT MAX(age) AS max_king_age FROM kings;
SELECT name, capacity FROM spacecrafts WHERE
capacity > 10;
 Guess the Key Constraints
• A. Ensures that a column or set of columns contains unique values and no null values.
• B. Ensures referential integrity by linking a column in one table to a primary key in another table.
• C. Guarantees that all values in a column or set of columns are unique across the table, but can
contain null values.
• D. A primary key that consists of more than one column.
• E. Ensures that a column cannot contain any null values.
• F. Ensures that all values in a column satisfy a specific condition.
• G. Automatically assigns a default value to a column if no value is provided during an insert.
• H. Automatically generates a unique number for the column when a new record is inserted.
• G.set of one or more columns (attributes) that can uniquely identify each row in a table.
Aggregate Functions
• Aggregate functions in SQL are used to perform calculations on multiple rows of a table's column and return a single value.
• These functions are commonly used in conjunction with the GROUP BY clause to group the result set by one or more columns.

•COUNT(): Counts the number of rows in a set.

•SUM(): Calculates the total sum of a numeric column.
•AVG(): Computes the average value of a numeric column.
•MIN(): Finds the minimum value in a column.
•MAX(): Finds the maximum value in a column.
•GROUP_CONCAT() (MySQL) / STRING_AGG() (PostgreSQL, SQL Server): Concatenates
values into a single string.
Psuedo Syntax
• CREATE TABLE table_name
• ( column1 datatype, column2 datatype, Col3-- Other columns can be
defined here PRIMARY KEY (column1, column2) -- Composite key );
• CREATE TABLE table_name ( column1 datatype, column2 datatype, --
Other columns FOREIGN KEY (column1) REFERENCES
other_table_name(other_column) );
• CREATE TABLE table_name ( column1 datatype, column2 datatype, --
Other columns UNIQUE (column1, column2) -- Composite Unique
Key );
Sub-Queries
• A subquery is a query nested inside another SQL query. It is used to provide results
for the outer query. Subqueries can be used in the SELECT, WHERE, or FROM
clauses.
• Subqueries, also known as nested queries, are a powerful feature in SQL that
allows you to perform complex operations by nesting one query within another.
• They allow us to select specific rows that satisfy certain conditions at the run time.
• They are also known as the inner query or inner select, and the query that contains
them is known as the outer query or outer select.
• Syntax
• SELECT column_name FROM table_name WHERE column_name expression
operator (SELECT column_name FROM table_name WHERE ...);
Find the Odd Rules Out for Sub-
Queries
• Subqueries can be placed in FROM, WHERE, or HAVING clauses of the main (parent) query.
• They can be used with SELECT, UPDATE, INSERT, and DELETE statements.
• Subqueries can work with comparison operators like >, <, >=, <=, <>, and logical operators like
SOME, ANY, ALL, and IN.
• If the subquery has no connection (correlation) with the main query, it generally runs first. In
correlated subqueries, the query engine decides the execution order dynamically.
• Subqueries should always be enclosed in parentheses.
• They typically appear on the right side of a comparison operator.
• Use single-row operators for subqueries returning one result (e.g., =, >).
• Use multiple-row operators for subqueries returning multiple results (e.g., IN, ANY).
• You can nest up to 255 subquery levels in the WHERE clause, though real-world cases rarely
involve more than 5 levels.
• No limit for nesting subqueries in the FROM clause.
 Match the Types of Sub-Queries
• A subquery that returns only one row and one column. Used with single-row operators like =, >, <.
• A subquery that returns multiple rows but one column. Used with multiple-row operators like IN,
ANY, ALL.
• A subquery that returns multiple rows and multiple columns. Used to compare multiple columns
with the main query.
• A subquery that references a column from the outer query. It is executed once for each row of the
outer query.
• A subquery within another subquery. These can be multi-level.
• Subqueries with INSERT Statement - Subqueries can be used to insert data into a table based on the
results of another query.
• Subqueries with UPDATE Statement- Subqueries can be used to update data in a table based on the
results of another query.
• Subqueries with DELETE Statement- Subqueries can be used to delete data from a table based on
the results of another query.
Multiple-Row Subquery |Single-Row Subquery | Correlated Subquery | Nested Subqueries | Multiple-Column Subquery
Try These Pseudo Queries
• SELECT emp_name // for single-row
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);
• SELECT emp_name // for multiple-row
FROM Employees
WHERE dept_id IN (SELECT dept_id FROM Departments WHERE location = 'New York’);

• SELECT emp_name, salary // for multiple columns and rows

FROM Employees
WHERE (dept_id, salary) IN (
SELECT dept_id, MIN(salary)
FROM Employees
GROUP BY dept_id
);
Try These Pseudo Queries
• SELECT emp_name, salary // for co-related
FROM Employees e
WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);
• SELECT emp_name // for
FROM Employees
WHERE dept_id IN (
SELECT dept_id
FROM Departments
WHERE location IN ( SELECT city FROM Offices WHERE office_count > 5
)
);

• INSERT INTO Employees_Archive (emp_id, emp_name, salary)

SELECT emp_id, emp_name, salary
FROM Employees
WHERE salary > 50000;
Write a Query
• Write a Sub-query which gives raise in salary to all Employees in HR
department
• UPDATE Employees
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'HR’);
• Without Sub-Query
• UPDATE Employees
JOIN Departments ON Employees.dept_id = Departments.dept_id
SET Employees.salary = Employees.salary * 1.10
WHERE Departments.dept_name = 'HR';
NULL Values in SQL
• NULL is a special identifier used in SQL to indicate that a data value
does not exist in the database.
• Knowing NULL values is crucial in designing databases, writing SQL
queries, or performing data analysis.
• In SQL, NULL signifies the absence of information or a missing value.
This is different from a zero value or a field that contains spaces.
• NULL is not data—it represents a lack of data
Handling NULL Values in SQL
• NULL values may lead to some conflicts because any operation on
NULL yields NULL which gives unexpected results.
• IS NULL and IS NOT NULL operators to check for NULL values.
• SELECT * FROM Artists WHERE Pay IS NULL; //query to find all artists who
haven't been paid yet.
• SELECT * FROM Artists WHERE Pay IS NOT NULL; //query to find all artists who
have been paid.
SQL NULL Functions
• ISNULL() - replaces NULL values with a specified value.
It takes two arguments, where it checks if the first one is NULL, and if it is, it returns
the second argument. If not, it returns the first argument
Eg: SELECT artistName, ISNULL(Pay, ‘Not Paid') AS Payments FROM Remunerations;
• NULLIF() - compares two expressions and returns NULL if they're equal; if they
aren't, it returns the first expression.
• Eg: SELECT artistName, NULLIF(Pay, ‘Not Paid') AS Payments FROM
Remunerations;
• the NULLIF() function will return NULL if a artist’s pay is ‘not paid'; otherwise, it will return the
payAmount.
Views in SQL