School of Computer Science and

Engineering

Assignment – 2
Name of the Course Database Management Systems
Course Code B22EF0402

Year and Semester II Year (IV Sem)

Academic Year 2024-2025

Submitted By

Giridharan B
Name of the Student
SRN & Section R23EF082 4B

Name of the Faculty Dr. P V Bhaskar Reddy

Max. Marks 05 Marks Signature of the

Signature of the Faculty
Obtained Student

1
 Unit – 3

SQL:
SQL data definition and data types, specifying constraints in SQL, basic retrieval queries in
SQL, insert, delete, update statements in SQL, additional features of SQL, schema change
statements in SQL, retrieving data using the SQL Select Statement, Restricting and sorting
data, Using Single row functions, Joins, More complex SQL retrieval queries, views in SQL.

Index

Topic Page No
1. Datatypes in SQL 3
2. SQL Constraints 7
3. SQL Commands 9
4. SQL Operators 13
5. SQL Functions 17
6. SQL Example Programs 24
7. Data Independence & Three – Schema Structure 34
8. SQL Joins 36
9. SQL Views 40
10. SQL Indexes 43
11. SQL Clauses 46
12. SQL Wildcard Characters 52
13. References 56

2
 Data Types in SQL

Data types are used to represent the nature of the data that can be stored in
the database table. List and explain the various datatypes available in SQL.

In SQL, data types are fundamental; they specify the kind of data that can be stored within a
column in a table. These types are crucial for ensuring data integrity, optimizing storage, and
enabling accurate data manipulation. The choice of a data type for a column directly impacts
the operations that can be performed on the data and the amount of storage space required. A
well-chosen data type helps prevent data inconsistencies and errors.

Detailed Explanation:

SQL provides a rich set of built-in data types, which can be broadly classified into several
categories:

Fig 1 SQL Datatypes

• Numeric Data Types: These data types are used to store numerical values. Different numeric
types offer varying degrees of precision and storage capacity.

3
 • INT (or INTEGER): This data type is used to store whole numbers (integers). The

range of values that can be stored depends on the specific database system (e.g., -
2147483648 to 2147483647 in many systems).
• Syntax: CREATE TABLE table_name (column_name INT);
• Example: CREATE TABLE Employees (EmployeeID INT);
• SMALLINT: Stores smaller whole numbers than INT.

• Syntax: CREATE TABLE table_name (column_name SMALLINT);

• Example: CREATE TABLE Departments(DeptID SMALLINT);
• BIGINT: Stores larger whole numbers than INT.

• Syntax: CREATE TABLE table_name (column_name BIGINT);

• Example: CREATE TABLE Transactions(TransactionID BIGINT);
• DECIMAL(size, d) (or NUMERIC(size, d)): This data type is used to store exact

numerical values with a specified precision and scale. size represents the total number
of digits, and d represents the number of digits after the decimal point. This is important
for financial data.
• Syntax: CREATE TABLE table_name (column_name DECIMAL(size, d));
• Example: CREATE TABLE Products (Price DECIMAL(10, 2));
• FLOAT(p): Stores approximate numerical data. The precision p specifies the number
of significant bits.
• Syntax: CREATE TABLE table_name (column_name FLOAT(p));
• Example: CREATE TABLE Measurements(Value FLOAT(24));
• REAL: A smaller floating-point number.

• Syntax: CREATE TABLE table_name (column_name REAL);

• Example: CREATE TABLE Distances(Length REAL);
• DOUBLE PRECISION: A larger floating point number

• Syntax: CREATE TABLE table_name (column_name DOUBLE PRECISION);

• Example: CREATE TABLE ScientificData(Reading DOUBLE PRECISION);

• String Data Types: These data types are used to store sequences of characters.
• VARCHAR(length): This data type stores variable-length character strings. The

length parameter specifies the maximum number of characters that can be stored. It's

efficient for strings of varying lengths.

4
 • Syntax: CREATE TABLE table_name (column_name VARCHAR(length));
• Example: CREATE TABLE Customers (Name VARCHAR(255));
• CHAR(length) (or CHARACTER(length)): This data type stores fixed-length

character strings. If the string is shorter than the specified length, it is padded with
spaces.
• Syntax: CREATE TABLE table_name (column_name CHAR(length));
• Example: CREATE TABLE Usernames (Username CHAR(10));
• TEXT: This data type is used to store large amounts of text data. The maximum size of
a TEXT column varies depending on the database system.
• Syntax: CREATE TABLE table_name (column_name TEXT);
• Example: CREATE TABLE Articles (Content TEXT);
• LONGTEXT: Holds very large text

• Syntax: CREATE TABLE table_name (column_name LONGTEXT);

• Example: CREATE TABLE BookDescriptions(Description LONGTEXT);

• Date and Time Data Types: These data types are used to store temporal values.
• DATE: This data type stores calendar dates (e.g., '2024-03-15').

• Syntax: CREATE TABLE table_name (column_name DATE);

• Example: CREATE TABLE Orders (OrderDate DATE);
• TIME: Stores time of day

• Syntax: CREATE TABLE table_name (column_name TIME);

• Example: CREATE TABLE Events(StartTime TIME);
• DATETIME (or TIMESTAMP): This data type stores both date and time values (e.g.,

'2024-03-15 10:30:00'). TIMESTAMP often has special behavior related to time zones.
• Syntax: CREATE TABLE table_name (column_name DATETIME);
• Example: CREATE TABLE Logs (Timestamp DATETIME);
• YEAR: Stores a year

• Syntax: CREATE TABLE table_name (column_name YEAR);

• Example: CREATE TABLE Publications(Year YEAR);

• Other Data Types:

• BOOLEAN: Stores true/false values.

5
• Syntax: CREATE TABLE table_name (column_name BOOLEAN);
• Example: CREATE TABLE Users(IsActive BOOLEAN);
• BLOB (Binary Large Object): Stores binary data (e.g., images, audio, video).

• Syntax: CREATE TABLE table_name (column_name BLOB);

• Example: CREATE TABLE Images (ImageData BLOB);

6
 SQL Constraints

Constraints are the rules that we can apply on the type of data in a table.
Illustrate all the constraints available in SQL with proper syntax and
example.

SQL constraints are rules that are enforced on data within a database table. They are essential
for maintaining data integrity, ensuring the accuracy and reliability of the data, and preventing
invalid data from being entered into the database. Constraints can be defined at the table level
or the column level.

Fig 2 Constraints in SQL

Detailed Explanation:

Constraints can be categorized as follows:

• Column Constraints: These constraints are applied to individual columns within a table.
• NOT NULL: This constraint ensures that a column cannot contain a NULL value. A
NULL value indicates that the value is unknown or missing.
• Syntax: CREATE TABLE table_name (column_name data_type NOT
NULL);

7
 • Example: CREATE TABLE Employees (Name VARCHAR(255) NOT NULL);
(The Name column cannot be empty)

• UNIQUE: This constraint ensures that all values in a column are distinct. No two
rows can have the same value for the constrained column (or set of columns).
• Syntax: CREATE TABLE table_name (column_name data_type UNIQUE);
• Example: CREATE TABLE Users (Email VARCHAR(255) UNIQUE); (Each
user must have a unique email address)

• PRIMARY KEY: This constraint uniquely identifies each row in a table. A primary
key must be both UNIQUE and NOT NULL. A table can have only one primary key.
It is used to enforce entity integrity.
• Syntax: CREATE TABLE table_name (column_name data_type PRIMARY
KEY);
• Example: CREATE TABLE Products (ProductID INT PRIMARY KEY);
(ProductID uniquely identifies each product)

• CHECK: This constraint ensures that all values in a column satisfy a specified
condition. It defines a business rule for the data.
• Syntax: CREATE TABLE table_name (column_name data_type CHECK
(condition));
• Example: CREATE TABLE Employees (Age INT CHECK (Age >= 18));
(Only employees with age 18 or greater can be added)

• DEFAULT: This constraint specifies a default value for a column. If a user does not
provide a value for the column when inserting a new row, the default value is
automatically assigned.
• Syntax: CREATE TABLE table_name (column_name data_type DEFAULT
value);
• Example: CREATE TABLE Orders (OrderStatus VARCHAR(20) DEFAULT
'Pending'); (If no order status is given, it defaults to 'Pending')

8
 SQL Commands

Explain the following SQL Commands with syntax and Examples.

SQL commands are the instructions used to interact with a relational database. They are
categorized into several sublanguages, each serving a specific purpose in managing and
manipulating data.

Detailed Explanation:

SQL commands are broadly classified into the following categories:

Fig 3 SQL Commands

1. Data Definition Language (DDL): DDL commands are used to define and manage the
structure of the database. They deal with creating, modifying, and deleting database objects.

• CREATE: This command is used to create database objects such as tables, views,

indexes, and schemas.

• Syntax: CREATE TABLE table_name (column1 datatype constraint,
column2 datatype constraint, ...);

9
 • Example: CREATE TABLE Students (StudentID INT PRIMARY KEY, Name
VARCHAR(255), Email VARCHAR(255) UNIQUE);

• ALTER: This command is used to modify the structure of an existing database object.
You can add, modify, or delete columns, constraints, or other object properties.
• Syntax: ALTER TABLE table_name ADD column_name datatype
constraint;

• Example: ALTER TABLE Students ADD Age INT;

• Syntax: ALTER TABLE table_name MODIFY column_name datatype;
• Example: ALTER TABLE Students MODIFY Age SMALLINT;
• Syntax: ALTER TABLE table_name DROP COLUMN column_name;
• Example: ALTER TABLE Students DROP COLUMN Email;

• DROP: This command is used to delete an existing database object. This action is

irreversible.
• Syntax: DROP TABLE table_name;
• Example: DROP TABLE Students;
• Syntax: DROP VIEW view_name;
• Example: DROP VIEW ActiveStudents;

• TRUNCATE: Removes all rows from a table, but the table structure remains.

• Syntax: TRUNCATE TABLE table_name;

• Example: TRUNCATE TABLE Students;

2. Data Manipulation Language (DML): DML commands are used to manipulate the data
stored within the database tables. They deal with inserting, updating, and deleting data.

• INSERT: This command is used to add new rows of data into a table.

• Syntax: INSERT INTO table_name (column1, column2, ...) VALUES

(value1, value2, ...);
• Example: INSERT INTO Students (StudentID, Name, Age) VALUES
(1, 'Alice', 20);

10
 • UPDATE: This command is used to modify existing data in a table. You specify which

rows to update using a WHERE clause.

• Syntax: UPDATE table_name SET column1 = value1, column2 =
value2, ... WHERE condition;
• Example: UPDATE Students SET Age = 21 WHERE StudentID = 1;

• DELETE: This command is used to remove rows from a table. You specify which rows

to delete using a WHERE clause. If no WHERE clause is provided, all rows are deleted.
• Syntax: DELETE FROM table_name WHERE condition;
• Example: DELETE FROM Students WHERE StudentID = 1;
• Example: DELETE FROM Students; (Deletes all rows)

3. Data Control Language (DCL): DCL commands are used to control access to the data in
the database. They deal with granting and revoking privileges.

• GRANT: This command is used to give users or roles specific privileges to access

database objects.
o Syntax: GRANT privilege_name ON object_name TO user_name;
o Example: GRANT SELECT, INSERT ON Students TO user1; (Grants
SELECT and INSERT privileges on the Students table to user1)
• REVOKE: This command is used to take back privileges that were previously granted.

o Syntax: REVOKE privilege_name ON object_name FROM user_name;

o Example: REVOKE SELECT ON Students FROM user1; (Revokes the
SELECT privilege on the Students table from user1)

4. Transaction Control Language (TCL): TCL commands are used to manage transactions,
which are logical units of work that may involve multiple SQL statements. TCL commands
ensure data consistency and integrity.

• COMMIT: This command is used to save all changes made during the current

transaction. The changes become permanent in the database.

o Syntax: COMMIT;

11
 • ROLLBACK: This command is used to undo all changes made during the current

transaction. The database is restored to its state before the transaction began.
o Syntax: ROLLBACK;
• SAVEPOINT: Creates a point within the current transaction to which you can later

rollback.
o Syntax: SAVEPOINT savepoint_name;
• Example:
o START TRANSACTION;
o INSERT INTO table1 (col) VALUES (1);
o SAVEPOINT A;
o INSERT INTO table2 (col) VALUES (2);
o ROLLBACK TO SAVEPOINT A; -- Undoes the insert into table2
o COMMIT; -- Only the insert into table1 is saved

• Data Query Language (DQL): DQL has one command, SELECT, which is used to retrieve
data from the database.
• SELECT: This command is used to query the database and retrieve data that matches

specified criteria.
o Syntax: SELECT column1, column2, ... FROM table_name WHERE
condition;
o Example: SELECT Name, Age FROM Students WHERE Age > 18;

12
 SQL Operators

An SQL operator is a reserved word, or a character used primarily in an

SQL statement's WHERE clause to perform operation(s).

SQL operators are reserved words or symbols that are used in SQL statements to perform
operations on data. They are commonly used in the WHERE clause to filter data based on specific
conditions, but they can also be used in other parts of a query, such as the SELECT clause, to
manipulate data.

Fig 4 SQL Operators

Detailed Explanation:

SQL operators can be categorized as follows:

• Arithmetic Operators: These operators perform mathematical calculations.

• + (Addition): Adds two values.

• Syntax: SELECT column1 + column2 FROM table_name;

• Example: SELECT Price + (Price * 0.1) AS IncreasedPrice FROM
Products;
- (Subtraction): Subtracts one value from another.
• Syntax: SELECT column1 - column2 FROM table_name;
• Example: SELECT Price - Discount AS NetPrice FROM Sales;
• (Multiplication): Multiplies two values.

13
 • Syntax: SELECT column1 * column2 FROM table_name;
• Example: SELECT Quantity * Price AS TotalPrice FROM OrderDetails;
• / (Division): Divides one value by another.

• Syntax: SELECT column1 / column2 FROM table_name;

• Example: SELECT Total / Count AS Average FROM MyTable;
• % (Modulo): Returns the remainder of a division.

• Syntax: SELECT column1 % column2 FROM table_name;

• Example: SELECT OrderID FROM Orders WHERE OrderID % 2 = 0; (Finds even
OrderIDs)

• Comparison Operators: These operators compare two values. The result of a comparison is
either TRUE, FALSE, or UNKNOWN (for NULL values).
• = (Equal to): Checks if two values are equal.

• Syntax: SELECT column1 FROM table_name WHERE column2 = value;

• Example: SELECT * FROM Customers WHERE City = 'New York';
➢ (Greater than): Checks if one value is greater than
another.
• Syntax: SELECT column1 FROM table_name WHERE column2 > value;
• Example: SELECT * FROM Products WHERE Price > 100;
• < (Less than): Checks if one value is less than another.

• Syntax: SELECT column1 FROM table_name WHERE column2 < value;

• Example: SELECT * FROM Orders WHERE OrderDate < '2024-01-01';
• >= (Greater than or equal to): Checks if one value is greater than or equal to another.

• Syntax: SELECT column1 FROM table_name WHERE column2 >= value;

• Example: SELECT * FROM Employees WHERE Age >= 18;
• <= (Less than or equal to): Checks if one value is less than or equal to another.

• Syntax: SELECT column1 FROM table_name WHERE column2 <= value;

• Example: SELECT * FROM Products WHERE Quantity <= 10;
➢ or != (Not equal to): Checks if two values are not

equal.
• Syntax: SELECT column1 FROM table_name WHERE column2 <> value;
• Example: SELECT * FROM Customers WHERE City <> 'New York';

14
• Logical Operators: These operators combine or negate conditions.
• AND: Returns TRUE if both conditions are TRUE.

• Syntax: SELECT column1 FROM table_name WHERE condition1 AND

condition2;
• Example: SELECT * FROM Customers WHERE City = 'New York' AND Age >
25;
• OR: Returns TRUE if either condition is TRUE.

• Syntax: SELECT column1 FROM table_name WHERE condition1 OR

condition2;
• Example: SELECT * FROM Products WHERE Category = 'Electronics' OR
Price < 50;
• NOT: Negates a condition. Returns TRUE if the condition is FALSE.

• Syntax: SELECT column1 FROM table_name WHERE NOT condition;

• Example: SELECT * FROM Customers WHERE NOT City = 'New York';

• Other Operators:
• BETWEEN: This operator selects values within a specified range (inclusive).

• Syntax: SELECT column1 FROM table_name WHERE column2 BETWEEN value1

AND value2;
• Example: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
• LIKE: This operator is used for pattern matching in string comparisons. It is used with

wildcard characters (% and _).

• Syntax: SELECT column1 FROM table_name WHERE column2 LIKE 'pattern';
• Example: SELECT * FROM Customers WHERE Name LIKE 'A%'; (Finds names
starting with 'A')
• IN: This operator specifies a list of values. It returns TRUE if the column's value

matches any value in the list.

• Syntax: SELECT column1 FROM table_name WHERE column2 IN (value1,
value2, ...);
• Example: SELECT * FROM Orders WHERE CustomerID IN (1, 2, 3);
• IS NULL: This operator checks if a value is NULL.

15
• Syntax: SELECT column1 FROM table_name WHERE column2 IS NULL;
• Example: SELECT * FROM Employees WHERE DepartmentID IS NULL;
• IS NOT NULL: This operator checks if a value is not NULL.

• Syntax: SELECT column1 FROM table_name WHERE column2 IS NOT NULL;

• Example: SELECT * FROM Employees WHERE DepartmentID IS NOT NULL;
• EXISTS: Checks for the existence of any row in a subquery.

• Syntax: SELECT column1 FROM table_name WHERE EXISTS (SELECT column2

FROM another_table WHERE condition);
• Example:
o SELECT CustomerName
o FROM Customers
o WHERE EXISTS (SELECT OrderID FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID);

16
 SQL Functions

For doing operations on data, SQL has many built-in functions. Explain the
following SQL functions with syntax and example.

SQL functions are built-in routines that perform specific operations on data. They can be used
to manipulate data, perform calculations, convert data types, and format output. SQL functions
enhance the power and flexibility of SQL queries.

Detailed Explanation:

SQL functions are often categorized as follows:

• SQL Server String Functions: These functions operate on string data.

Fig 5 SQL Server String Functions

• UPPER(string) or UPPERCASE(string): Converts a string to uppercase.

17
 o Syntax: SELECT UPPER(column_name) FROM table_name;
o Example: SELECT UPPER(Name) FROM Customers; (e.g., "john" becomes
"JOHN")
• LOWER(string) or LCASE(string): Converts a string to lowercase.

o Syntax: SELECT LOWER(column_name) FROM table_name;

o Example: SELECT LOWER(Name) FROM Customers; (e.g., "JOHN" becomes
"john")
• LENGTH(string) or LEN(string): Returns the length of a string (number of
characters).
o Syntax: SELECT LENGTH(column_name) FROM table_name;
o Example: SELECT LENGTH(Name) FROM Customers; (e.g., "John" returns 4)
• TRIM(string): Removes leading and trailing spaces from a string.

o Syntax: SELECT TRIM(column_name) FROM table_name;

o Example: SELECT TRIM(City) FROM Addresses;
• LTRIM(string): Removes leading spaces from a string.

o Syntax: SELECT LTRIM(column_name) FROM table_name;

o Example: SELECT LTRIM(Address) FROM Addresses;
• RTRIM(string): Removes trailing spaces from a string.

o Syntax: SELECT RTRIM(column_name) FROM table_name;

o Example: SELECT RTRIM(Address) FROM Addresses;
• CONCAT(string1, string2, ...): Concatenates (joins) two or more strings.

o Syntax: SELECT CONCAT(column1, column2, ...) FROM table_name;

o Example: SELECT CONCAT(FirstName, ' ', LastName) FROM

Employees; (e.g., "John" and "Doe" become "John Doe")

• SUBSTRING(string, start, length) (or SUBSTR() or MID()): Extracts a

substring from a string.

o Syntax: SELECT SUBSTRING(column_name, start, length) FROM
table_name;
o Example: SELECT SUBSTRING(Name, 1, 3) FROM Customers; (e.g., "John"
returns "Joh")
• REPLACE(string, old_substring, new_substring): Replaces all occurrences

of a substring with another substring.

18
 o Syntax: SELECT REPLACE(column_name, old_substring,
new_substring) FROM table_name;
o Example: SELECT REPLACE(ProductName, 'Widget', 'Gadget') FROM
Products;
• CHARINDEX(substring, string): Returns the starting position of a substring

within a string.
o Syntax: SELECT CHARINDEX(substring, column_name) FROM
table_name;
o Example: SELECT CHARINDEX(' ', FullName) FROM Employees;

• SQL Server Math/Numeric Functions: These functions perform mathematical calculations.

Fig 6 SQL Server Numeric Functions

• AVG(column): Returns the average value of a numeric column.

o Syntax: SELECT AVG(column_name) FROM table_name;
o Example: SELECT AVG(Salary) FROM Employees;
• SUM(column): Returns the sum of the values in a numeric column.

o Syntax: SELECT SUM(column_name) FROM table_name;

o Example: SELECT SUM(Quantity * Price) FROM OrderDetails;
• MAX(column): Returns the maximum value in a column.

o Syntax: SELECT MAX(column_name) FROM table_name;

o Example: SELECT MAX(Salary) FROM Employees;
• MIN(column): Returns the minimum value in a column.

19
 o Syntax: SELECT MIN(column_name) FROM table_name;
o Example: SELECT MIN(Salary) FROM Employees;
• COUNT(column): Returns the number of rows in a column (excluding NULLs).

COUNT(*) returns the total number of rows.

o Syntax: SELECT COUNT(column_name) FROM table_name;

o Example: SELECT COUNT(*) FROM Customers;
• ROUND(number, decimals): Rounds a number to a specified number of decimal

places.
o Syntax: SELECT ROUND(column_name, decimals) FROM table_name;
o Example: SELECT ROUND(234.567, 2); (Returns 234.57)
• ABS(number): Returns the absolute value of a number.

o Syntax: SELECT ABS(column_name) FROM table_name;

o Example: SELECT ABS(-10); (Returns 10)
• SQRT(number): Returns the square root of a number.

o Syntax: SELECT SQRT(column_name) FROM table_name;

o Example: SELECT SQRT(25); (Returns 5)
• POW(number, exponent): Returns the value of a number raised to a power.

o Syntax: SELECT POW(column_name, exponent) FROM table_name;

o Example: SELECT POW(2, 3); (Returns 8)

o SQL Server Date Functions: These functions operate on date and time value.

• NOW() or GETDATE(): Returns the current date and time.

o Syntax: SELECT NOW();

o Example: SELECT NOW(); (e.g., '2024-03-15 14:45:30')
• CURDATE() or CURRENT_DATE: Returns the current date.

o Syntax: SELECT CURDATE();

o Example: SELECT CURDATE(); (e.g., '2024-03-15')
• CURTIME() or CURRENT_TIME: Returns the current time.

o Syntax: SELECT CURTIME();

o Example: SELECT CURTIME(); (e.g., '14:45:30')
• DATE(datetime): Extracts the date part of a datetime value.

20
 o Syntax: SELECT DATE(column_name) FROM table_name;
o Example: SELECT DATE(OrderDateTime) FROM Orders;
• MONTH(date): Returns the month part of a date.

o Syntax: SELECT MONTH(column_name) FROM table_name;

o Example: SELECT MONTH(OrderDate) FROM Orders;
• YEAR(date): Returns the year part of a date.

o Syntax: SELECT YEAR(column_name) FROM table_name;

o Example: SELECT YEAR(OrderDate) FROM Orders;
• DAY(date): Returns the day of the month

o Syntax: SELECT DAY(column_name) FROM table_name;

o Example: SELECT DAY(OrderDate) FROM Orders;
• DATE_ADD(date, INTERVAL value unit) / DATEADD(): Adds a specified time

interval to a date.
o Syntax: SELECT DATE_ADD(column_name, INTERVAL value unit) FROM
table_name;
o Example: SELECT DATE_ADD(OrderDate, INTERVAL 7 DAY) FROM

Orders; (Adds 7 days)

• DATE_SUB(date, INTERVAL value unit) / DATESUB(): Subtracts a specified

time interval from a date.

o Syntax: SELECT DATE_SUB(column_name, INTERVAL value unit) FROM
table_name;
o Example: SELECT DATE_SUB(OrderDate, INTERVAL 1 MONTH) FROM
Orders;
• DATEDIFF(date1, date2): Returns the difference between two dates.

o Syntax: SELECT DATEDIFF(date1, date2) FROM table_name;

o Example: SELECT DATEDIFF(OrderDate, '2024-01-01') FROM Orders;

• SQL Server Advanced Functions: This is a broad category, and some functions might
overlap with other categories, depending on how they are classified.
• COALESCE(value1, value2, ...): Returns the first non-NULL value in a list.
o Syntax: SELECT COALESCE(column1, column2, 'N/A') FROM
table_name;

21
 o Example: If column1 is NULL and column2 is 'xyz', it returns 'xyz'. If both are
NULL, it returns 'N/A'.
• NULLIF(expression1, expression2): Returns NULL if expression1 equals
expression2; otherwise, it returns expression1.
o Syntax: SELECT NULLIF(column1, value) FROM table_name;
o Example: SELECT NULLIF(Quantity, 0) FROM Products; (Returns NULL
if Quantity is 0)
• CAST(expression AS data_type): Converts an expression to a specified data type.
o Syntax: SELECT CAST(column_name AS data_type) FROM table_name;
o Example: SELECT CAST(Price AS INT) FROM Products;
• CONVERT(data_type, expression, style): Converts an expression to a specified
data type, with optional style formatting (especially for dates).
o Syntax: SELECT CONVERT(data_type, column_name, style) FROM
table_name;
o Example: SELECT CONVERT(VARCHAR, OrderDate, 101) FROM Orders;
(Converts date to MM/DD/YYYY format)

Fig 7 Scalar & Aggregate Functions

• Scalar Functions: These functions operate on a single value and return a single value. Many
of the string, math, and date functions are scalar functions.
• Aggregate Functions: These functions operate on a set of rows and return a single value.

22
• COUNT(column): Returns the number of rows in a column (excluding NULLs).

COUNT(*) returns the total number of rows.

o Syntax: SELECT COUNT(column_name) FROM table_name;

o Example: SELECT COUNT(*) FROM Customers;
• SUM(column): Returns the sum of the values in a numeric column.

o Syntax: SELECT SUM(column_name) FROM table_name;

o Example: SELECT SUM(Salary) FROM Employees;
• AVG(column): Returns the average value of a numeric column.

o Syntax: SELECT AVG(column_name) FROM table_name;

o Example: SELECT AVG(Price) FROM Products;
• MAX(column): Returns the maximum value in a column.

o Syntax: SELECT MAX(column_name) FROM table_name;

o Example: SELECT MAX(OrderDate) FROM Orders;
• MIN(column): Returns the minimum value in a column.

o Syntax: SELECT MIN(column_name) FROM table_name;

o Example: SELECT MIN(Price) FROM Products;

23
 Example Programs

1. Consider the following schema for Orders Database:

SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)

• Create the tables for the above database with proper datatypes, constraints required.
• Count the customers with grades above Bangalore’s average.
• Demonstrate the DELETE operation by removing salesman with id 1000 All his
orders must also be deleted

SQL Queries:

-- Creating SALESMAN table

CREATE TABLE SALESMAN (
Salesman_id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
City VARCHAR(50),
Commission DECIMAL(5,2) CHECK (Commission >= 0 AND Commission <= 1)
);

-- Creating CUSTOMER table

CREATE TABLE CUSTOMER (
Customer_id INT PRIMARY KEY,
Cust_Name VARCHAR(50) NOT NULL,
City VARCHAR(50),
Grade INT,
Salesman_id INT,

24
 FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id) ON DELETE
SET NULL
);

-- Creating ORDERS table

CREATE TABLE ORDERS (
Ord_No INT PRIMARY KEY,
Purchase_Amt DECIMAL(10,2),
Ord_Date DATE,
Customer_id INT,
Salesman_id INT,
FOREIGN KEY (Customer_id) REFERENCES CUSTOMER(Customer_id) ON DELETE
CASCADE,
FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id) ON DELETE
CASCADE
);

-- Grades above average

SELECT COUNT(*) AS High_Grade_Customers
FROM CUSTOMER
WHERE Grade > (
SELECT AVG(Grade)
FROM CUSTOMER
WHERE City = 'Bangalore'
);
-- Deleting Salesman with id 1000
DELETE FROM SALESMAN
WHERE Salesman_id = 1000;

25
2. "Employees" in a company database. This table contains information about
employees, including their unique employee ID, name, email address, department ID, and
age.
EMPLOYEE(name, ssn, salary, sex, superssn, address, dno)
DEPARTMENT(Dname, Dnumber, Dloc ,MGRSSN)
Construct these tables by considering the various constraints to ensure data integrity and
enforce business rules using the SQL.
SQL Queries
-- Creating DEPARTMENT table
CREATE TABLE DEPARTMENT (
Dname VARCHAR(50) NOT NULL,
Dnumber INT PRIMARY KEY,
Dloc VARCHAR(100),
MGRSSN CHAR(9),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(ssn) ON DELETE SET NULL
);

-- Creating EMPLOYEE table

CREATE TABLE EMPLOYEE (
name VARCHAR(50) NOT NULL,
ssn CHAR(9) PRIMARY KEY,
salary DECIMAL(10,2) CHECK (salary > 0),
sex CHAR(1) CHECK (sex IN ('M', 'F')),
superssn CHAR(9),
address VARCHAR(100),
dno INT,
FOREIGN KEY (superssn) REFERENCES EMPLOYEE(ssn) ON DELETE SET NULL,
FOREIGN KEY (dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET
NULL
);

26
3. Specify the following queries on the Company relational database schema using the
relational algebra operators.
EMPLOYEE(name, ssn, salary, sex,superssn,address,dno)
DEPARTMENT(Dname, Dnumber, Dloc ,MGRSSN)PROJECT(Pname,
Pnumber,Plocation,Dnum)

WORKS_ON(ESSN,PNO, Dnum Hours)

DEPENDENT(ESSN, DEPE_Name, Address,Relationship,sex)

i) List female employees from Dumber=20 earning more than 5000.

ii) Retrieve the names of all employees in department 5 who work more than 10 hours on
the Product X project.

SQL Queries
i) πname (σsex='Female' AND dno=20 AND salary > 5000 (EMPLOYEE))
ii) π_name (
σ Hours > 10 ∧ Dnumber = 5 ∧ Pname = 'Product X'
(
EMPLOYEE ⨝ WORKS_ON ⨝ PROJECT
)
)

27
4. Specify the following queries on the Company relational database schema using the
relational algebra operators.

EMPLOYEE(name, ssn, salary, sex,superssn,address,dno)

DEPARTMENT(Dname, Dnumber, Dloc ,MGRSSN)

PROJECT(Pname, Pnumber,Plocation,Dnum)

WORKS_ON(ESSN,PNO, Dnum Hours)

DEPENDENT(ESSN, DEPE_Name, Address,Relationship,sex)

i) Find the names of all employees who are directly supervised by ‘Franklin Wong’.

ii) Retrieve the names of managers who do not have female dependents.

iii) For every project located in ‘MG ROAD’ list the project number, the controlling dept no
and the dept manager’s name and address.

iv) Retrieve the name and address of all employees who work for the 'Research' department.

SQL Queries
i) π E.name (
σ S.name = 'Franklin Wong' (
ρ S(EMPLOYEE) ⨝ S.ssn = E.superssn ρ E(EMPLOYEE)
)
)

ii) π name (
(Mgrs ⨝ EMPLOYEE) − (FemaleDeps ⨝ EMPLOYEE)
)

iii) π P.Pnumber, P.Dnum, E.name, E.address (

σ P.Plocation = 'MG ROAD' (
PROJECT P ⨝ P.Dnum = D.Dnumber DEPARTMENT D ⨝ D.MGRSSN = E.ssn
EMPLOYEE E

28
 )
)

iv) π E.name, E.address (

σ D.Dname = 'Research' (
EMPLOYEE E ⨝ E.dno = D.Dnumber DEPARTMENT D
)
)

29
5. Specify the following queries on the Flight database schema using SQL

FLIGHT(Flight no: integer, Flight from: string, Flight to: string, distance:
integer, departs: time, arrives: time, price: real)

AIRCRAFT (aid: integer, aname:string, cruisingrange:integer)

CERTIFIED (eid:integer, aid:integer)

EMPLOYEE (eid:integer, ename:string, salary: integer)

i)Find the names of pilots certified for some Boeing aircraft.

ii) For each aircraft identify the number of employees certified.

iii)Find the maximum, minimum and average salary of employees.

SQL Queries

i) SELECT DISTINCT E.ename

FROM EMPLOYEE E

JOIN CERTIFIED C ON E.eid = C.eid

JOIN AIRCRAFT A ON C.aid = A.aid

WHERE A.aname LIKE '%Boeing%';

ii) SELECT A.aid, A.aname, COUNT(C.eid) AS certified_employees

FROM AIRCRAFT A

LEFT JOIN CERTIFIED C ON A.aid = C.aid

GROUP BY A.aid, A.aname;

iii) SELECT

30
MAX(salary) AS Max_Salary,

MIN(salary) AS Min_Salary,

AVG(salary) AS Avg_Salary

FROM EMPLOYEE;

31
6. Specify the following queries on the Flight database schema using SQL

FLIGHT(Flight no: integer, Flight from: string, Flight to: string, distance:
integer, departs: time, arrives: time, price: real)

AIRCRAFT(aid: integer, aname:string, cruisingrange:integer)

CERTIFIED (eid:integer, aid:integer)

EMPLOYEE (eid:integer, ename:string, salary: integer)

i) For each pilot who is certified for more than three aircrafts, find the eid and the maximum
cruising range of the aircraft for which he/she is certified.

ii) Find the names of all pilots whose salary is less than the price of the cheapest route from
Bangalore to Frankfurt.

iii) For all aircrafts with cruising range over 1000 kms, find the name of the aircraft and the
average salary of all pilots certified for this aircraft

SQL Queries

i) SELECT C.eid, MAX(A.cruisingrange) AS max_cruising_range

FROM CERTIFIED C

JOIN AIRCRAFT A ON C.aid = A.aid

GROUP BY C.eid

HAVING COUNT(DISTINCT C.aid) > 3;

ii) SELECT E.ename

FROM EMPLOYEE E

JOIN CERTIFIED C ON E.eid = C.eid

WHERE E.salary < (

SELECT MIN(price)

32
 FROM FLIGHT

WHERE Flight_from = 'Bangalore' AND Flight_to = 'Frankfurt'

);

iii) SELECT A.aname, AVG(E.salary) AS avg_salary

FROM AIRCRAFT A

JOIN CERTIFIED C ON A.aid = C.aid

JOIN EMPLOYEE E ON C.eid = E.eid

WHERE A.cruisingrange > 1000

GROUP BY A.aname;

33
 Data Independence & Three – Schema Architecture

Demonstrate the three-schema architecture to separate the user application

and the physical database. Elaborate the different types of data
independence.

Three-Schema Architecture

Fig 8 Three – Schema Architecture

The three-schema architecture is a database framework that separates user applications from
the physical database, enhancing data management and flexibility. It consists of three levels:

• Internal Schema: The lowest level, detailing the physical storage of data (e.g., storage
devices, file organization).
• Conceptual Schema: Describes the overall logical structure of the database, including
entities, attributes, and relationships.
• External Schema: The highest level, providing different user views of the data, tailored to
specific needs.

34
 Data Independence

Fig 9 Levels of Data Independence

Data independence is the ability to modify one schema level without affecting a higher level.
There are two main types:

• Physical Data Independence: Changes to the internal schema (e.g., storage devices) do not
affect the conceptual schema.
• Logical Data Independence: Changes to the conceptual schema (e.g., adding entities) do not
affect the external schemas.

35
 SQL Joins

A JOIN clause is used to combine rows from two or more tables, based on a
related column between them. List and explain the various joins in SQL.

SQL joins are a fundamental feature that allows you to combine rows from two or more tables
based on a related column between them. Joins are essential for retrieving meaningful data
from a relational database, where data is often spread across multiple tables to reduce
redundancy and improve data integrity.

Fig 10 SQL Joins

Detailed Explanation:

A join clause is used in a SELECT statement to combine data from different tables. The join
condition specifies how the rows from the tables should be matched.

• INNER JOIN: This is the most common type of join. It returns only the rows that have
matching values in both tables. Rows with non-matching values are excluded from the result
set.

• Syntax:
• SELECT column_name(s)

36
 • FROM table1
• INNER JOIN table2

• ON table1.column_name = table2.column_name;

• Explanation: The INNER JOIN clause specifies the tables to be joined (table1
and table2). The ON clause specifies the join condition, indicating which
columns from the two tables should be compared. Only rows where the values
in these columns are equal are included in the result.
• Example:
• SELECT Orders.OrderID, Customers.CustomerName
• FROM Orders
• INNER JOIN Customers
• ON Orders.CustomerID = Customers.CustomerID;

• This query retrieves the order ID and customer name for each order, combining data
from the Orders and Customers tables based on the CustomerID column.

• LEFT JOIN (or LEFT OUTER JOIN): This join returns all rows from the left table (the
first table specified in the FROM clause), and the matched rows from the right table. If there is
no match in the right table for a row in the left table, the result will contain NULL values for
the columns from the right table.

o Syntax:

o SELECT column_name(s)
o FROM table1
o LEFT JOIN table2
o ON table1.column_name = table2.column_name;

o Explanation: All rows from table1 are included in the result. For each row in
table1, the join attempts to find matching rows in table2 based on the ON

condition. If a match is found, the columns from table2 are included. If no

match is found, the columns from table2 will be NULL.
o Example:

o SELECT Customers.CustomerName, Orders.OrderID

37
 o FROM Customers
o LEFT JOIN Orders
o ON Customers.CustomerID = Orders.CustomerID;

• This query returns all customers, even those who have not placed any orders. For
customers who have placed orders, the order ID is also displayed. For customers
without orders, the OrderID will be NULL.

• RIGHT JOIN (or RIGHT OUTER JOIN): This join is the opposite of a LEFT JOIN. It
returns all rows from the right table (the second table specified in the FROM clause), and the
matched rows from the left table. If there is no match in the left table for a row in the right
table, the result will contain NULL values for the columns from the left table.
• Syntax:
• SELECT column_name(s)
• FROM table1
• RIGHT JOIN table2
• ON table1.column_name = table2.column_name;

• Explanation: All rows from table2 are included. The join attempts to find matching
rows in table1.
• Example:
• SELECT Customers.CustomerName, Orders.OrderID
• FROM Customers
• RIGHT JOIN Orders
• ON Customers.CustomerID = Orders.CustomerID;

• FULL OUTER JOIN: This join returns all rows when there is a match in either the left or
right table. It combines the results of both LEFT and RIGHT joins. If there is no match in one
table for a row in the other table, the result will contain NULL values for the columns from the
table with no match.
• Syntax:
• SELECT column_name(s)
• FROM table1

38
 • FULL OUTER JOIN table2
• ON table1.column_name = table2.column_name;

• Explanation: Every row from both tables is included in the result.

• Example:
• SELECT Customers.CustomerName, Orders.OrderID
• FROM Customers
• FULL OUTER JOIN Orders
• ON Customers.CustomerID = Orders.CustomerID;

• CROSS JOIN: This join returns the Cartesian product of the rows from the joined tables. It
returns every possible combination of rows from the tables. It does not have an ON clause.
• Syntax:
• SELECT column_name(s)
• FROM table1
• CROSS JOIN table2;

• Example:
• SELECT Customers.CustomerName, Products.ProductName
• FROM Customers
• CROSS JOIN Products;

39
 SQL Views

Topic: SQL Views

In SQL, a view is a virtual table based on the result-set of an SQL statement. Unlike regular
tables, views do not store data physically. Instead, they provide a way to look at data from one
or more tables in a simplified or customized manner. Views are stored as SQL queries, and the
data is retrieved from the underlying tables each time the view is accessed.

Detailed Explanation:

Views offer several advantages:

• Data Abstraction: Views can hide the complexity of the underlying database structure.
Users can interact with a view as if it were a regular table, without needing to know the details
of the tables and joins involved in the query. This simplifies database access for users.
• Data Security: Views can restrict access to certain data. By creating a view that includes
only specific columns or rows from a table, you can grant users access to the view without
granting them access to the entire table. This enhances data security and protects sensitive
information.
• Simplified Queries: Views can simplify complex queries. If you frequently need to retrieve
data using a complex join or a lengthy WHERE clause, you can create a view that encapsulates
that query. Users can then query the view with a simple SELECT statement.
• Data Consistency: Views can help ensure data consistency. If you have a calculation or a
specific way of retrieving data that needs to be used in multiple queries, you can define it in a
view. This ensures that the calculation or data retrieval logic is consistent across all queries that
use the view.
• Renaming and Reorganization: Views allow you to rename columns and reorganize data
without changing the underlying table structure. This can be useful for providing a different
perspective on the data or for making the data more user-friendly.

Syntax:

The basic syntax for creating a view is:

CREATE VIEW view_name AS

40
 SELECT column1, column2, ...
FROM table_name
WHERE condition;

• CREATE VIEW: This clause initiates the creation of a new view.

• view_name: This is the name you assign to the view. View names must be unique

within the database.

• AS: This keyword introduces the SELECT statement that defines the view.

• SELECT column1, column2, ...: This SELECT statement specifies the columns

and data that will be included in the view. You can select columns from one or more
tables, use functions, and include complex expressions.
• FROM table_name: This specifies the table or tables from which the data for the

view is retrieved.
• WHERE condition: This optional clause specifies a condition that filters the data

included in the view.

Examples:

• Simple View:
• CREATE VIEW CustomerNames AS
• SELECT CustomerName
• FROM Customers;

This view creates a virtual table called CustomerNames that contains only the CustomerName
column from the Customers table. Users can query CustomerNames as if it were a regular
table: SELECT * FROM CustomerNames;

• View with a Join:

• CREATE VIEW OrderDetailsView AS
• SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate,
Orders.TotalAmount
• FROM Orders
• JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

41
 This view combines data from the Orders and Customers tables, showing order details along
with the customer name.

• View with a WHERE Clause:

• CREATE VIEW ActiveCustomersView AS
• SELECT CustomerName, City, Email
• FROM Customers
• WHERE IsActive = 1;

This view displays information only for active customers.

• View with Aggregated Data:

• CREATE VIEW SalesSummaryView AS
• SELECT CustomerID, SUM(TotalAmount) AS TotalSales
• FROM Orders
• GROUP BY CustomerID;

This view shows the total sales for each customer.

Fig 11 Views in SQL

42
 SQL Indexes

SQL Indexes are important components in relational databases. Define

index and explain with Syntax and examples.

SQL indexes are a crucial database object used to improve the speed of data retrieval
operations. They are similar to an index in a book, which allows you to quickly locate specific
information without having to read the entire book. In a database, an index helps the database
management system (DBMS) locate specific rows in a table more efficiently.

Fig 12 SQL Indexes

Detailed Explanation:

Without an index, when you execute a SELECT query, the DBMS has to perform a full table
scan, meaning it has to examine every row in the table to find the rows that match your query's
criteria. This can be very time-consuming, especially for large tables.

An index is a separate data structure that stores a subset of the data from a table, typically the
values of one or more columns, along with pointers to the corresponding rows in the table. The
index is organized in a way that allows for fast searching, such as a B-tree or a hash table.

43
 Key Points:

• Purpose of Indexes:
• Speed up SELECT queries: Indexes significantly improve the performance of queries
that retrieve data based on the indexed columns.
• Reduce disk I/O: By using an index, the DBMS can locate the required rows without
reading the entire table from disk, which reduces disk I/O and improves performance.

• How Indexes Work:

1. When you create an index on a column, the DBMS creates a separate data
structure (the index) that contains the values of that column and pointers to the
corresponding rows in the table.

2. When you execute a SELECT query that includes the indexed column in the
WHERE clause, the DBMS first consults the index to find the pointers to the rows
that match the query's criteria.
3. The DBMS then uses those pointers to retrieve the actual rows from the table.

• Types of Indexes:
o Clustered Index:
• Determines the physical order of the data rows in a table.
• A table can have only one clustered index.
• The data rows are stored in the order of the clustered index key.
• Clustered indexes are very efficient for range queries (e.g., WHERE age BETWEEN
20 AND 30).
• If a table has a primary key, the primary key is often used as the clustered index.

o Non-clustered Index:
• Does not determine the physical order of the data rows.
• A table can have multiple non-clustered indexes.
• The non-clustered index stores the indexed column values and pointers to the data rows.
• Non-clustered indexes are efficient for point queries (e.g., WHERE CustomerID =
123).

• Creating Indexes:
• Syntax:
• CREATE INDEX index_name

44
 • ON table_name (column1, column2, ...);

• CREATE INDEX: This clause initiates the creation of a new index.

• index_name: This is the name you assign to the index. Index names must be unique
within the database.
• ON table_name: This specifies the table on which the index is created.

• (column1, column2, ...): This specifies the column or columns that will be

included in the index. You can create an index on a single column or on multiple
columns (a composite index).
• Examples:
• Create a simple index on the Name column of the Customers table:
• CREATE INDEX idx_customer_name
• ON Customers (Name);

• Create a composite index on the City and Age columns of the Customers table:
• CREATE INDEX idx_customer_city_age
• ON Customers (City, Age);

• Primary Key and Indexes:

• When you define a primary key for a table, the DBMS automatically creates an index
on the primary key column(s). This index is usually a clustered index, but it depends
on the specific database system.

• Considerations:
• Indexes improve query performance but can slow down data modification operations
(INSERT, UPDATE, DELETE). When you modify data, the DBMS also has to update
the indexes, which takes time.
• Indexes consume storage space.
• You should create indexes on columns that are frequently used in WHERE clauses, JOIN
conditions, and ORDER BY clauses.
• Avoid creating too many indexes, as they can degrade performance.

45
 SQL Clauses

List and explain the various clauses in SQL with syntax and examples

SQL clauses are fundamental components of SQL statements. Each clause performs a specific
function within the statement, allowing you to define the data you want to retrieve, how you
want to filter it, how you want to sort it, and how you want to group it.

Fig 13 SQL Clauses

Detailed Explanation:

Here's a breakdown of common SQL clauses:

• SELECT: The SELECT clause is used to specify the columns that you want to retrieve from
one or more tables.
• Syntax:
• SELECT column1, column2, column3, ...
• FROM table_name;

o Explanation:

46
 • SELECT: The keyword that starts the clause.

• column1, column2, column3, ...: A comma-separated list of the columns you

want to retrieve. You can also use * to select all columns from the table.
• FROM table_name: Specifies the table from which the data will be retrieved.

• Example:
• SELECT CustomerName, City, Email
• FROM Customers;

This query retrieves the CustomerName, City, and Email columns from the Customers table.

• FROM: The FROM clause specifies the table or tables that contain the data you want to
retrieve. It is a mandatory clause in most SELECT statements.
• Syntax:
• SELECT column1, column2, ...
• FROM table_name;

o Explanation:

• FROM: The keyword that introduces the table name.

• table_name: The name of the table you want to query. If you are joining multiple

tables, you will specify them in the FROM clause along with JOIN clauses.
• Example:
• SELECT *
• FROM Employees;

This query retrieves all columns from the Employees table.

• WHERE: The WHERE clause is used to filter the rows that are retrieved by the SELECT
statement. It specifies a condition that must be met for a row to be included in the result set.
• Syntax:
• SELECT column1, column2, ...
• FROM table_name
• WHERE condition;

47
o Explanation:

• WHERE: The keyword that introduces the filtering condition.

• condition: A Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.

It can include comparison operators (=, <, >, <=, >=, <>), logical operators (AND, OR,
NOT), and other operators like LIKE, BETWEEN, and IN.

• Example:
• SELECT ProductName, Price
• FROM Products
• WHERE Price > 50;

This query retrieves the ProductName and Price from the Products table, but only for
products with a price greater than 50.

• GROUP BY: The GROUP BY clause is used to group rows that have the same values in one
or more columns into summary rows. It is often used with aggregate functions (e.g., COUNT(),
SUM(), AVG(), MAX(), MIN()) to calculate statistics for each group.

o Syntax:

• SELECT column1, aggregate_function(column2)

• FROM table_name
• GROUP BY column1;

o Explanation:

• GROUP BY column1: Specifies the column or columns by which the rows should be

grouped. All rows with the same value in column1 will be grouped together.
• Example:
• SELECT City, COUNT(CustomerID) AS NumberOfCustomers
• FROM Customers
• GROUP BY City;

This query groups customers by their city and counts the number of customers in each city.

48
• HAVING: The HAVING clause is used to filter groups created by the GROUP BY clause. It is
similar to the WHERE clause, but it filters groups rather than individual rows. The HAVING clause
can only be used in conjunction with the GROUP BY clause.
o Syntax:

• SELECT column1, aggregate_function(column2)

• FROM table_name
• GROUP BY column1
• HAVING condition;

o Explanation:

• HAVING condition: Specifies a condition that must be met by the groups to be

included in the result set. The condition can include aggregate functions.
• Example:
• SELECT City, COUNT(CustomerID) AS NumberOfCustomers
• FROM Customers
• GROUP BY City
• HAVING COUNT(CustomerID) > 5;

This query groups customers by city and counts the number of customers in each city, but it
only includes cities with more than 5 customers in the result.

• ORDER BY: The ORDER BY clause is used to sort the result set in ascending or descending
order based on the values of one or more columns.
o Syntax:

• SELECT column1, column2, ...

• FROM table_name
• ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

o Explanation:

• ORDER BY column1, column2, ...: Specifies the column or columns by which

the result set should be sorted. You can specify multiple columns to sort by.

49
 • ASC: Specifies ascending order (the default).

• DESC: Specifies descending order.

• Example:
• SELECT ProductName, Price
• FROM Products
• ORDER BY Price DESC, ProductName ASC;

This query retrieves the ProductName and Price from the Products table, sorts the result set
first by Price in descending order (highest price first), and then by ProductName in ascending
order for products with the same price.

• LIMIT / TOP: The LIMIT clause (in MySQL, PostgreSQL) or the TOP clause (in SQL
Server) is used to specify the maximum number of rows to return from the result set.
o Syntax (MySQL, PostgreSQL):

• SELECT column1, column2, ...

• FROM table_name
• LIMIT number;

o Syntax (SQL Server):

• SELECT TOP number column1, column2, ...

• FROM table_name;

o Explanation:

• LIMIT number: Specifies the maximum number of rows to return.

• TOP number: Specifies the maximum number of rows to return

• Example (MySQL):
• SELECT *
• FROM Customers
• LIMIT 10;

This query retrieves the first 10 rows from the Customers table.

50
o Example (SQL Server):

• SELECT TOP 10 * FROM Customers;

• OFFSET: The OFFSET clause is used in conjunction with the LIMIT clause to specify the
starting position of the rows to be returned. It allows you to retrieve a subset of rows from a
result set, starting from a specific position.
o Syntax(MySQL, PostgreSQL)

• SELECT column1, column2,...

• FROM table_name
• LIMIT number OFFSET start;

o Explanation:

• LIMIT number: Specifies the maximum number of rows to return

• OFFSET start: Specifies the starting position of the first row to be returned (starting
from 0).
• Example:
• SELECT *
• FROM Products
• ORDER BY ProductID
• LIMIT 10 OFFSET 20;

This query retrieves 10 rows from the Products table, starting from the 21st row (offset 20).
This is useful for implementing pagination.

51
 SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

List and explain the various wildcard characteristics with syntax and
examples.

SQL wildcard characters are special symbols that can be used in LIKE expressions to match
patterns in string values. They provide a flexible way to search for data when you don't know
the exact value you're looking for. Wildcards are essential for performing pattern matching in
SQL queries.

Fig 14 SQL Wildcard Characters

Detailed Explanation:

The following are the most commonly used wildcard characters in SQL:

• % (Percent Sign):
• The percent sign represents zero, one, or multiple characters.
• It can be used at the beginning, middle, or end of a search string.
• Syntax:
• SELECT column1
• FROM table_name
• WHERE column2 LIKE 'pattern%'; -- Matches values that start with
'pattern'
• ```sql

52
 • SELECT column1
• FROM table_name
• WHERE column2 LIKE '%pattern%'; -- Matches values that contain
'pattern'
• ```sql
• SELECT column1
• FROM table_name
• WHERE column2 LIKE '%pattern'; -- Matches values that end with
'pattern'

• Examples:
• SELECT * FROM Customers WHERE Name LIKE 'Sm%'; This query finds all

customers whose names start with "Sm" (e.g., "Smith", "Smyth", "Smothers").
• SELECT * FROM Products WHERE ProductName LIKE '%Widget%'; This query

finds all products whose names contain "Widget" (e.g., "SuperWidget", "WidgetPro",
"Widget 2000").
• SELECT * FROM Emails WHERE Email LIKE '%@example.com'; This query

finds all emails that end with "@example.com"

• _ (Underscore Sign):
• The underscore sign represents a single character.
• It can be used to match any single character at a specific position in a string.
• Syntax:
• SELECT column1
• FROM table_name
• WHERE column2 LIKE 'pattern_'; -- Matches values that start with
'pattern' and have one more character

• Examples:
• SELECT * FROM Products WHERE ProductCode LIKE 'P_01'; This query

finds all products whose product codes start with "P", followed by any single character,
and then "01" (e.g., "PA01", "PB01", "PX01").

53
 • SELECT * FROM PhoneNumbers WHERE Number LIKE '555-___-____'; This

query finds phone numbers that start with "555-", followed by any three characters, a
hyphen, and any four characters.

• [] (Square Brackets): (SQL Server Only)

• The square brackets are used to specify a set or range of characters to match.
• It matches any single character within the specified set.
• Syntax:
• SELECT column1
• FROM table_name
• WHERE column2 LIKE '[charlist]'; -- Matches any single character
in 'charlist'
• ```sql
• SELECT column1
• FROM table_name
• WHERE column2 LIKE '[start-end]'; -- Matches any single character
within the range 'start' to 'end'

o Examples:

• SELECT * FROM Customers WHERE Name LIKE '[ABC]%'; This query finds all
customers whose names start with "A", "B", or "C".
• SELECT * FROM Products WHERE ProductCode LIKE '[A-C]%'; This query

finds all products whose product code starts with A, B, or C.

• SELECT * FROM Employees WHERE LastName LIKE 'M[aeiou]%'; This query

finds last names starting with M, followed by a vowel.

• [^] or [! ] (Square Brackets with Negation): (SQL Server Only)

• The square brackets with a caret (^) or exclamation mark (!) are used to specify a set
or range of characters not to match.
• It matches any single character not in the specified set.
• Syntax:

54
 • SELECT column1
• FROM table_name
• WHERE column2 LIKE '[^charlist]'; -- Matches any single character
not in 'charlist'

o Examples:

• SELECT * FROM Customers WHERE Name LIKE '[^XYZ]%'; This query finds all

customers whose names do not start with "X", "Y", or "Z".

• SELECT * FROM Products WHERE ProductCode NOT LIKE '[0-9]%'; Finds

product codes not starting with a digit.

55
 References

• https://www.w3schools.com/sql/sql_datatypes.asp
• https://www.javatpoint.com/sql-commands
• https://www.tutorialspoint.com/sql/sql-commands.htm
• https://www.w3schools.com/sql/sql_operators.asp
• https://www.tutorialspoint.com/sql/sql-operators.htm
• https://www.w3schools.com/sql/sql_functions.asp
• https://www.tutorialspoint.com/sql/sql-functions.htm
• https://www.w3schools.com/sql/sql_join.asp
• https://www.javatpoint.com/sql-joins
• https://www.w3schools.com/sql/sql_view.asp
• https://www.javatpoint.com/sql-view
• https://www.javatpoint.com/sql-index
• https://www.tutorialspoint.com/sql/sql-indexes.htm
• https://www.w3schools.com/sql/sql_wildcards.asp
• https://www.tutorialspoint.com/sql/sql-wildcard-characters.htm

56