ADVANCED

DATABASE
SYSTEMS

Joy Chrisky V. Lobendino

 2

DATABASE CONCEPT
Database
• It is simply a collection of structured data that is organized and stored in a
way that allows easy access, management, and updating. It typically consists of:
o Tables (for relational databases) or other structures (for NoSQL databases
like collections, documents, etc.).
o Data stored in these tables or structures (e.g., rows in a table).
o Types: Relational, NoSQL, Object-oriented, Data Warehouses, Distributed
databases, Document database
o Examples: Relational – MySQL, Microsoft SQL Server, Oracle Database;
NoSQL – MongoDB, Cassandra, Couchbase
• It is managed using specialized software called a Database Management
System (DBMS), which allows users to store, retrieve, and manipulate data
efficiently.
• Its primary role is to store data efficiently and securely.
 3

DATABASE CONCEPT
Database Management System (DBMS)
• It is software that helps you interact with the database to perform
operations such as creating, reading, updating, and deleting data.
• It includes a database engine, user interface, and data manipulation
languages.
• It handles tasks like data storage, backup and recovery, security (control
access to the data, user permissions), and data integrity (ensure
consistency)
• Perform queries (via SQL in relational DBMS or other query languages
for NoSQL).
 4

DATABASE CONCEPT
Relational Database
• A relational database is a type of database that organizes data into rows
and columns, which collectively form a table where the data points are
related to each other.
• Data is typically structured across multiple tables, which can be joined
together via a primary key or a foreign key.
Table
• It is a collection of related data entries and it consists of columns and
rows.
Record or Row
• It an an individual entry that exists in a table
 5

DATABASE CONCEPT
 6

DATABASE CONCEPT
Column
• It represents a set of data values of a particular type, one for each row of
the table. For instance, the Student Name column would contain the
names of all students in the Students table.
Field
• It is frequently used in the context of a single record;
• It is the intersection of a row and a column in a table. It contains the
actual value for a specific attribute of a record.
• For instance, in a Student record, the Student Name field could contain
the value “John Doe”.
 7

DATABASE CONCEPT
Attribute
• In an Entity-Relationship Diagram (ERD), an attribute is a characteristic or
property that defines an entity in the model. When an ERD is translated
into a physical database model, entities become tables, and attributes
become columns.
 8

DATABASE CONCEPT
Attribute
• Within the context of a database, the term attribute can mean any property
that a database object (table, column, schema, trigger, stored procedure,
view, etc.) can possess.
• However, it is most often used to discuss the properties of a table’s column.
1. Name: Every column must have a unique name within its table.
2. Data Type: This defines the kind of data the column can store. It can
be integers, floating-point numbers, strings, dates, Boolean values, or
other types of data depending on the database system.
3. Length or Size: For some data types, such as strings (VARCHAR,
CHAR), you can specify the maximum length.
4. Default Value: You can specify a default value the database system uses
if no value is explicitly provided when a row is inserted.
 9

5. Constraints: Constraints are rules that the database system enforces.

They include:
o NOT NULL: This means the column must always have a value (it
can’t be NULL).
o UNIQUE: This means all values in the column must be unique.
o PRIMARY KEY: This means the column is the primary key for the
table. Its values uniquely identify each row in the table.
o FOREIGN KEY: This indicates that the column is linked to the
primary key of another table.
o CHECK: This allows you to specify a condition that the values in
the column must meet.
6. Collation: This is a set of rules determining how string data is sorted
and compared. This is relevant for columns that store string data.
7. Auto-Increment: This attribute is available in some database systems. It
automatically generates a unique number for each row, typically used
with a primary key column.
 10

DATABASE CONCEPT
Expressions
• Are combinations of symbols, operators, values, and functions used to perform
calculations, manipulate data, or evaluate conditions. They are a fundamental
part of querying and working with data in a database..

Null Value
• A null value represents the absence of any value. It means that the data is
unknown or missing. It can be assigned to fields of any data type, including
strings, integers, and dates. Importantly, the field is not allocated memory since
NULL signifies an unknown value.
Blank (Empty) Value
• It refers to an empty character or a whitespace character. While its meaning
may seem similar to NULL, it is stored and retrieved like any other character
in a text field. Empty strings are specific to string columns and cannot be
applied to different data types.
 11

When Does a Column Value Become NULL?

A NULL value represents the absence of a value or the unknown state of a field. It is
not the same as a blank or empty value. A column value becomes NULL when:

1. No Value is Provided:
When a row is inserted, and a value is not explicitly assigned to a column, it
defaults to NULL (if the column allows NULL values).

INSERT INTO Students (Student_ID, Name) VALUES (1, 'Alice’);

• If the Grade column is not specified, its value will be NULL (assuming no
default value is set).
 12

2. Explicitly Set to NULL:

When an INSERT or UPDATE statement explicitly assigns NULL to the column.

UPDATE Students SET Grade = NULL WHERE Student_ID = 1;

3. Column Allows NULL:

If the column is defined with NULL allowed (e.g., Grade INT NULL), it can hold
NULL values.
 13

When Does a Column Value Become EMPTY?

An empty value (e.g., an empty string '' for text fields) indicates that a value is present,
but it contains no characters. A column value becomes empty when:

1. Explicitly Assigned an Empty String:

When an INSERT or UPDATE statement assigns an empty string.

INSERT INTO Students (Student_ID, Name, Grade) VALUES (2, 'Bob', '');
 14

2. User Input Results in an Empty String:

If the application layer (e.g., a form or interface) allows saving empty strings as
valid input.

• Example: A user leaves a text field blank, and the application saves it as ''
instead of NULL.

3. No Default Value for a Column:

If a column is set to accept empty strings as valid data and no other value is
provided.

CREATE TABLE Students (

Student_ID INT,
Name VARCHAR(50),
Grade VARCHAR(10) DEFAULT ‘’
);
 15

DATABASE CONCEPT
Structured Query Language (SQL)
• SQL, or Structured Query Language, is a standardized language used
to manage and manipulate data within relational databases.
• It allows users to perform various operations on data, including
retrieving, inserting, updating, and deleting information.
• SQL is essential for organizing, controlling, and accessing large sets
of structured data in applications ranging from small-scale business
software to large-scale enterprise systems.
 16

DATABASE CONCEPT
Projection
• A certain column from a table is selected.
• Controls which columns to display, without filtering rows.
• SQL Keyword: SELECT column_name
Selection
• A subset of rows or records in a table is retrieved once it satisfies a selection
condition.
• Focus: Controls which rows to display, keeping all columns.
• SQL Keyword: WHERE
Joining
• In this operation, data are combined from two or more tables based on one or
more common column values.
• SQL Keyword: JOIN ON
 17

SQL CATEGORIES OVERVIEW

• DDL (Data Definition Language) : These are the collection of commands which are required to define
the database.
e.g. CREATE, ALTER, RENAME, TRUNCATE, DROP, etc.
• DQL (Data Query Language) : Retrieves data from tables.
e.g. SELECT
• DML (Data Manipulation Language) : These are the collection of commands which are required to
manipulate the data stored in a database.
e.g UPDATE, INSERT, DELETE, etc.
• DCL (Data Control Language) : These are the collection of commands which are dealt with the user
permissions and controls of the database system.
e.g. GRANT, and REVOKE.
• TCL (Transaction Control Language) : These are the collection of commands which are required to
deal with the transaction of the database.
e.g COMMIT, ROLLBACK, and SAVEPOINT.
 18

CHAPTER 1
DATA QUERY LANGUAGE (DQL)
 19

SELECT STATEMENT SYNTAX

• The SELECT statement is used to select data from a database.

SELECT [ALL or * | DISTINCT] column1, column2, ...

FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...]
[LIMIT number [OFFSET number]];
 20

SELECT AND FROM CLAUSES

• SELECT represents the command to retrieve information from the table
• FROM represents the command to identify where the information to gather will come from.
• Syntax:
SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name;

• Example:
SELECT name, description FROM department_list;
 21

WHERE CLAUSE
• The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition.
• Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
• Example:
SELECT *
FROM Employees
WHERE Position = ‘Manager’;
 22

GROUP BY CLAUSE
• The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of
some functions. i.e. if a particular column has the same values in different rows then it will
arrange these rows in a group.
• Syntax:
SELECT column1, function_name(column2)
FROM table_name
GROUP BY column1, column2
• Example:
SELECT name, SUM(sal)
FROM emp
GROUP BY name;
 23

GROUP BY CLAUSE

SELECT Department, sum(Salary) as Salary

FROM Employee
GROUP BY department;
 24

GROUP BY CLAUSE
The most commonly used SQL aggregate functions are:

MIN() - returns the smallest value within the selected column

MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT()).
 25

HAVING BY CLAUSE
• Allows the filtering of query results based on aggregate functions and groupings. Unlike the
WHERE clause, which filters individual rows before any grouping occurs, the HAVING clause
works on groups of data that have been aggregated using functions like SUM(), AVG(), COUNT(),
and others.
• The HAVING clause is used to filter the result of GROUP BY based on the specified condition.
The conditions are Boolean type i.e. use of logical operators (AND, OR). This clause was included
in SQL as the WHERE keyword failed when we used it with aggregate expressions.
• Syntax:
SELECT column1, function_name(column2)
FROM table_name
GROUP BY column1, column2
HAVING condition
 26

Using HAVING to Filter Aggregated Results

SELECT Department, sum(Salary) as Salary FROM Employee GROUP BY department;

 27

SELECT Department, sum(Salary) as Salary FROM Employee

GROUP BY department
HAVING Salary >= 70000;
 28

ORDER BY CLAUSE
• The ORDER BY statement in SQL is used to sort the fetched data in either ascending or
descending according to one or more columns.
• SQL ORDER BY default mode is sorting data into ascending order.
• Syntax:
SELECT * FROM table_name ORDER BY column_name ASC | DESC
• Syntax to use ORDER BY Clause with Column Number:
. . . ORDER BY Column_Number asc/desc

Example:
SELECT Roll_no, Name, Address FROM studentinfo ORDER BY 1
ORDER BY 1 means sorting values according to first column in the SELECT statement.
 29

Example 1 : Sort According To a Single Column using ORDER BY Clause

SELECT * FROM students ORDER BY ROLL_NO DESC;

 30

Example 2 : Sort According To Multiple Columns using ORDER BY Clause

SELECT * FROM students ORDER BY age DESC , name ASC;

 31

LIMIT CLAUSE
• The LIMIT clause allows you to specify the maximum number of records returned by a query. It is
commonly used for limiting query results when only a subset of the data is required, such as for
pagination, filtering top values, or analyzing a smaller portion of a large table.
• MySQL Syntax:
SELECT column_name(s) FROM table_name WHERE condition
LIMIT number;
• SQL/MS Access:
SELECT TOP number|percent column_name(s) FROM table_name
WHERE condition;
• Oracle 12:
SELECT column_name(s) FROM table_name ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
 32

SELECT * FROM student LIMIT 3;

 33

ALIASES
• Aliases are the temporary names given to tables or columns for the purpose of a particular SQL
query. It is used when the name of a column or table is used other than its original name, but the
modified name is only temporary.
o Aliases are created to make table or column names more readable.
o The renaming is just a temporary change and the table name does not change in the original
database.
o Aliases are useful when table or column names are big or not very readable.
o These are preferred when there is more than one table involved in a query.
• There are two types of aliases in SQL:
o Column Aliases: Temporary names for columns in the result set.
o Table Aliases: Temporary names for tables used within a query.
 34

ALIASES
• A column alias is used to rename a column in the output of a query. Column aliases are useful for making the result
set more readable or when performing calculations or aggregations.
Syntax:
SELECT column_name AS alias_name FROM table_name;

Example:
SELECT EmployeeID AS ID FROM Employee;
or
SELECT EmployeeID AS “Employee ID” FROM Employee;

o column_name: The column name can be defined as the column on which we are going to create an alias name.
o alias_name: It can be defined as a temporary name that we are going to assign for the column or table.
o AS: It is optional. If you have not specified it, there is no effect on the query execution.
 35

ALIASES
• A table alias is used when you want to give a table a temporary name for the duration of a query.
Table aliases are especially helpful in JOIN operations to simplify queries, particularly when the
same table is referenced multiple times (like in self-joins).
• Example: Table Alias for Joining Tables
We want to join the Customer table with itself to find customers who have the same
country and are aged 21. We’ll use table aliases for each instance of the Customer table

SELECT [Link], [Link]

FROM Customer AS c1, Customer AS c2
WHERE [Link] = [Link] AND [Link] = [Link];
 36

SQL OPERATOR
• SQL AND Operator. It allows you to filter data based on multiple conditions, all of which must
be true for the record to be included in the result set.
Syntax:
SELECT * FROM table_name
WHERE condition1 AND condition2 AND …conditionN;

• SQL OR Operator. It displays the records where any one condition is true, i.e. either condition1
or condition2 is True.
Syntax:
SELECT * FROM table_name
WHERE condition1 OR condition2 OR… conditionN;
 37

Exercises:
1. Write a query to fetch all the records from the Student table where Age is 18 and ADDRESS is
Delhi.
2. Fetch all the records from the Student table where NAME is Ram or NAME is SUJIT.
 38

SQL OPERATOR
• SQL LIKE Operator. It is used with the WHERE clause to search for a specified pattern in a
column. LIKE operator finds and returns the rows that fit in the given pattern. LIKE operator is
case-insensitive by default in most database systems. This means that if you search for “apple”
using the LIKE operator, it will return results that include “Apple”, “APPLE”, “aPpLe”, and so on.
Syntax:
SELECT column1, column2, … FROM table_name
WHERE column_name LIKE pattern;

o column_name: The column to be searched.

o pattern: The pattern to search for, which can include wildcard characters.
o For making the LIKE operator case-sensitive, you can use the “BINARY” keyword in MySQL
or the “COLLATE” keyword in other database systems.
e.g. SELECT * FROM products WHERE name LIKE BINARY 'apple%'
 39

SQL OPERATOR
Pattern Meaning
o Wildcards are used with the LIKE operator
‘a%’ Match strings that start with ‘a’
to search for specific patterns in strings.
Wildcard characters substitute one or more ‘%a’ Match strings with end with ‘a’
characters in the string. There are four
wildcard characters in SQL: Match strings that contain the start with ‘a’ and
‘a%t’
end with ‘t’.
▪ % (Percent): Represents zero or more
characters. Match strings that contain the substring ‘wow’ in
‘%wow%’
▪ _ (Underscore): Represents a single them at any position.
character. Match strings that contain the substring ‘wow’ in
‘_wow%’
▪ [] (Square Brackets): Represents any them at the second position.
single character within brackets.
Match strings that contain ‘a’ at the second
▪ – (Hyphen): Specify a range of ‘_a%’
position.
characters inside brackets.
Match strings that start with ‘a and contain at least
‘a_ _%’
2 more characters.
 40

Exercises:
1. Retrieve SupplierID, Name, and Address from the suppliers table where the supplier name
starts with 'Ca’
2. Retrieve entire table, where address contains OKHLA.
 41

SQL OPERATOR
• IN Operator. The IN Operator in SQL is used to specify multiple values/sub-queries in the WHERE
clause. It provides an easy way to handle multiple OR conditions.
o We only pass a single condition in the WHERE clause, however there might be situations where we
need to select data based on multiple conditions. For such cases, the IN operator is used.
o If any of the conditions are passed using the IN operator, they will be considered true.
o Syntax:
SELECT column_name FROM table_name
WHERE condition IN (condition_value1, condition_value2 …..);
o Example:
SELECT * FROM employee
WHERE Name IN (‘Ram’,’Suresh’);

SELECT * FROM employee

WHERE Ssn IN (SELECT Ssn FROM manager);
 42

Exercises:
1. Write a query to get Fname and Lname of personnel who have address in Almora and Ponta
2. Write a query to get Fname and Lname of personnel who do not have addresses in either Almora
or Ponta
 43

SQL OPERATOR
• The SQL NOT operator is used to reverse the boolean result of a condition in SQL. It helps in
retrieving records that do not match a specific condition. It is mostly used to specify what should
not be included in the results table.
o Syntax:
SELECT column1, colomn2, …
FROM table_name WHERE NOT condition;
o Examples:
SELECT * FROM Customers WHERE NOT Country = 'UK’;
SELECT * FROM Customers WHERE NOT Country IN ('USA', 'UK’);
SELECT * FROM Customers WHERE NOT CustomerName LIKE 'R%’;
SELECT * FROM Customers WHERE NOT PostalCode IS NULL;
 44

SQL OPERATOR
• NOT EQUAL Operator in SQL is used to compare two values and return if they are not equal.
This operator returns boolean values. If given expressions are equal, the operator returns false
otherwise true. If any one expression is NULL, it will return NULL.
o Note: <> and != perform the same operation i.e. check inequality. The only difference between
<> and != is that <> follows the ISO standard but != does not. So it is recommended to use <>
for NOT EQUAL Operator.
o Syntax:
SELECT * FROM table_name WHERE column_name != value;

NOT Operator != (Not Equal) Operator

Purpose Negates a Boolean condition Compares two values
Usage Used with conditions (NOT IN, Used for value comparison in WHERE
NOT EXISTS, NOT LIKE) clauses
Example WHERE NOT (salary > 50000) WHERE department != 'HR'
 45

SQL OPERATOR
• The IS NULL operator is used to check if a column contains a NULL value. If a column value is
NULL, the operator returns TRUE; otherwise, it returns FALSE. It's commonly used in WHERE
clauses to filter rows that contain NULL values in specific columns.
o A NULL value is different from a Zero Value and Blank Spaces. A field that has NULL value
means the field was left blank.
o Syntax:
SELECT * FROM table_name
WHERE column_name IS NULL;
o Example:
SELECT * FROM students
WHERE email IS NULL;
 46

SQL OPERATOR
• The SQL BETWEEN operator is used to filter the result set within a specified range. It can be
applied to numeric, date, and text columns. The BETWEEN operator is inclusive, meaning it
includes the start and end values in the result set..
o Syntax:
SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2;
o Example:
SELECT * FROM products
WHERE price BETWEEN 10 AND 20;
 47

SQL OPERATOR
• The SQL EXCEPT operator allows you to return the rows that exist in the first result set but not
in the second. It is useful for finding records in one table that do not have corresponding records
in another table.
o Note: The two SELECT queries must return the same number of columns and the data types
must be compatible.
o Syntax:
SELECT column_name(s) FROM table1 EXCEPT SELECT column_name(s) FROM
table2;

Feature Except Not In

Duplicate Handling Removes duplicates from the result Retains duplicates in the result
Performance Removes duplicates from the result May be slower for large datasets, especially when checking
multiple conditions
Use Case When you need to find rows that exist When you need to check a specific column’s values against a
in one result set but not the other list
 48
SELECT Name
FROM Students
EXCEPT
SELECT Name
Students Table Teaching Assistant Table
FROM TA;
 49

SQL OPERATOR
• The SQL UNION operator combines the results of two or more SELECT statements into one
result set. By default, UNION removes duplicate rows, ensuring that the result set contains only
distinct records.
o Rules for using the UNION OPERATOR:
▪ Each table used within UNION must have the same number of columns.
▪ The columns must have the same data types.
▪ The columns in each table must be in the same order.
• UNION ALL includes all rows, including duplicate rows.
o Syntax:
SELECT columnnames FROM table1 UNION SELECT columnnames FROM table2;;
 50

SELECT Country
FROM Emp1
UNION
SELECT Country
FROM Emp2
ORDER BY Country;
 51

CONCATENATION OPERATOR
• The concatenation operator in SQL is used to combine two or more strings or columns into a
single string. It is especially useful for creating meaningful outputs by joining text values from
different columns or adding literals (static text) to the output.
o Combines multiple columns or strings into one.
o Works with different data types (text, numbers, dates).
o Enhances query output by making it more readable.
o Syntax: SELECT concat(column1,column2, …) AS new_column FROM table_name;
o Example:
SELECT id, first_name, last_name, concat(first_name, ‘ ‘, last_name)
AS full_name, salary
FROM myTable;
 52

FUNCTIONS IN SQL
• Categories:
o String functions. They are used to manipulate and transform the text string.
o Numeric functions. Accept numeric values, perform a mathematic operation on the values
and return resulting sheet
o Date and Time function. Used to manipulate and transform date and time data stored in
tables. Date functions operate on values of the DATE datatype.
• Some commonly used string functions: 53
o CONCAT(). Concatenates two or more strings together.
• Syntax: CONCAT(string1, string2, ...)
• Example: SELECT CONCAT('Hello', ' ', 'World!’); --Output: Hello World!

o SUBSTRING(). Extracts a substring from a string.

• Syntax: SUBSTRING(string, start_position, length)
• Example: SELECT SUBSTRING('Hello World', 7, 5); --Output: World

o UPPER(). Converts a string to uppercase.

• Syntax: UPPER(string)
• SELECT UPPER('hello’);

o LOWER(). Converts a string to lowercase.

• Syntax: LOWER(string)
• Example: SELECT LOWER('HELLO’);

o LENGTH(). Returns the length of a string in bytes.

• Syntax: LENGTH(string)
• Example: SELECT LENGTH('Hello’); --Output: 5
• Some commonly used string functions: 54
o TRIM(). Removes leading and trailing spaces from a string.
• Syntax: TRIM(string)
• Example: SELECT TRIM(‘ Hello World ‘); --Output: Hello World

o REPLACE(). Replaces all occurrences of a substring within a string.

• Syntax: REPLACE(string, old_substring, new_substring)
• Example: SELECT REPLACE('Hello World', 'World', 'MySQL’); --Output: Hello MySql

o INSTR(). Returns the position of the first occurrence of a substring in a string.

• Syntax: INSTR(string, substring)
• Example: SELECT INSTR('Hello World', 'World’); --Output: 7

o LEFT(). Returns the first N characters of a string.

• Syntax: LEFT(string, number_of_characters)
• Example: SELECT LEFT('Hello World', 5); --Output: Hello

o RIGHT(). Returns the last N characters of a string.

• Syntax: RIGHT(string, number_of_characters)
• Example: SELECT RIGHT('Hello World', 5); --Output: World
• Example: 55
56
• Some commonly used numeric functions: 57
o ABS(). Returns the absolute value of a number.
• Syntax: ABS(number)
• Example: SELECT ABS(-10); --Output: 10

o ROUND(). Rounds a number to a specified number of decimal places.

• Syntax: ROUND(number, decimal_places)
• Example: SELECT ROUND(4.678, 2); --Output: 4.68

o TRUNCATE(). Truncates a number to a specified number of decimal places.

• Syntax: TRUNCATE(number, decimal_places)
• Example: SELECT TRUNCATE(4.678, 2); --Output: 4.67

o MOD(). Returns the remainder of a division operation.

• Syntax: MOD(dividend, divisor)
• Example: SELECT MOD(10, 3); --Output: 1

o POWER(). Returns the result of a number raised to a power.

• Syntax: POWER(base, exponent)
• Example: SELECT POWER(2, 3); --Output: 8
• Some commonly used date and time functions: 58
o CURRENT_DATE() / CURDATE(). Returns the current date.
• Syntax: CURRENT_DATE() or CURDATE()
• Example: SELECT CURRENT_DATE(); --Output: 2025-02-09

o CURRENT_TIME() / CURTIME(). Returns the current time.

• Syntax: CURRENT_TIME() or CURTIME()
• Example: SELECT CURRENT_TIME(); --Output: [Link]

o NOW(). Returns the current date and time.

• Syntax: NOW()
• Example: SELECT NOW();
• Output: '2025-02-06 [Link]' (depends on current date and time)

o DATE(). Extracts the date part from a datetime value.

• Syntax: DATE(datetime_value)
• Example: SELECT DATE('2025-02-06 [Link]');
• Output: '2025-02-06'

o TIME(). Extracts the time part from a datetime value.

• Syntax: TIME(datetime_value)
• Example: SELECT TIME('2025-02-06 [Link]');
• Output: '[Link]'
• Some commonly used date and time functions: 59
o YEAR(), MONTH(), DAY(). xtracts the year, month, or day from a date.
• Example: SELECT YEAR('2025-02-06'), MONTH('2025-02-06'), DAY('2025-02-06');
• Output: 2025, 2, 6

o HOUR(), MINUTE(), SECOND(). Extracts the hour, minute, or second from a time value.
• Syntax: HOUR(time_value) / MINUTE(time_value) / SECOND(time_value)
• Example: SELECT HOUR('[Link]'), MINUTE('[Link]'), SECOND('[Link]');
• Output: 14, 30, 25

o DATEDIFF(). Calculates the number of days between two dates.

• Syntax: DATEDIFF(date1, date2)
• Example: SELECT DATEDIFF('2025-12-31', '2025-01-01');
• Output: 364

o TIMEDIFF(). Calculates the difference between two time values.

• Syntax: TIMEDIFF(time1, time2)
• Example: SEL SELECT TIMEDIFF('[Link]', '[Link]');
• Output: '[Link]’
• Some commonly used date and time functions: 60

o STR_TO_DATE(). Converts a string to a date based on a given format.

• Syntax: STR_TO_DATE(string, format)
• Example: SELECT STR_TO_DATE('06-02-2025', '%d-%m-%Y');
• Output: '2025-02-06'

o DATE_FORMAT(). Formats a date value in a specific format.

• Syntax: DATE_FORMAT(date, format)
• Example: SELECT DATE_FORMAT('2025-02-06', '%M %d, %Y');
• Output: 'February 06, 2025'
 61

Format Code Description Example

%Y Year (4 digits) 2025
%y Year (2 digits) 25
%M Month name February
%m Month (2 digits) 02
%d Day of the month 06
%H Hour (24-hour format) 14
%i Minutes 30
%s Seconds 25
 62

SUBQUERIES
• A subquery is embedded inside another query and acts as input or output for that query.
Subqueries are also called inner queries and they can be used in various complex operations in
SQL.
• It can be used with an outer query which is used as input to the outer query. It can be used with
SELECT, FROM, and WHERE clauses.
• It is executed first before the execution of the outer query.
 63

Subquery with WHERE Clause

• Example: Let's select employees from sq_employee table with the department id as 1.
 64

Subquery with comparison operators

• Example: Let's select employees whose salary is less than average salary of all employees.

• Let’s select employees whose salary is greater than or equal to average salary of all employees.
 65

Subquery with IN and NOT IN operators

• IN operator: Let’s select all employees whose department is in departments table.

• NOT IN operator: Let’s select all employees whose department is not in department table.
 66

Subquery with FROM clause

• Select all departments from employee table with nested query.

 67

Correlated Subquery

• Correlated subquery is the one which uses columns from outer table for execution.
• Example: Select EmpId and Name of employee from sq_employee where salary is less than
average salary and department is same as outer table.
 68

Subquery with EXISTS and NOT EXISTS

• EXISTS operator is used in MySQL to test for the existence of any record in a subquery. EXISTS
Operator works with a parent query and a child query. A parent query will execute if the child
query returns any value.
• Example: Select employees for which there exists at least 1 department where department of
employee is same as department in departments.
 69

Subquery with EXISTS and NOT EXISTS

• Question: Find customers who placed at least one order.

• Query:
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = [Link]);
 70

Subquery with EXISTS and NOT EXISTS

Step-by-Step Explanation:
1. The query looks at each customer in the customers table.
2. For each customer, it runs the subquery:
SELECT 1 FROM orders o WHERE o.customer_id = [Link];

• If the subquery finds at least one matching order, EXISTS returns TRUE, and that
customer is included.
• If no matching order is found, EXISTS returns FALSE, and that customer is excluded.

• Bob is not in the result because he never placed an order.

71
 72

REFERENCES
• What is a relational database?: [Link]
• [Link]
• [Link]
• [Link]
THANK
YOU