BODHINI

Introduction of DBMS (Database Management System)

A Database Management System (DBMS) is a software solution designed to
efficiently manage, organize, and retrieve data in a structured manner. It serves
as a critical component in modern computing, enabling organizations to
store, manipulate, and secure their data effectively. From small applications to
enterprise systems, DBMS plays a vital role in supporting data-driven decision-
making and operational efficiency.

DBMS:
A DBMS is a system that allows users to create, modify, and query databases
while ensuring data integrity, security, and efficient data access. Unlike
traditional file systems, DBMS minimizes data redundancy, prevents
inconsistencies, and simplifies data management with features like concurrent
access and backup mechanisms. It organizes data into tables, views, schemas,
and reports, providing a structured approach to data management.

Example:
A university database can store and manage student information, faculty
records, and administrative data, allowing seamless retrieval, insertion, and
deletion of information as required.

Database Languages
Database languages are specialized sets of commands and instructions used to
define, manipulate, and control data within a database. Each language type plays
a distinct role in database management, ensuring efficient storage, retrieval,
and security of data. The primary database languages include:

1. Data Definition Language (DDL)

DDL is the short name for Data Definition Language, which deals with database
schemas and descriptions, of how the data should reside in the database.
 CREATE: to create a database and its objects like (table, index, views, store
procedure, function, and triggers)
 ALTER: alters the structure of the existing database
 DROP: delete objects from the database

1
 BODHINI

 TRUNCATE: remove all records from a table, including all spaces allocated
for the records are removed

2. Data Manipulation Language (DML)

DML focuses on manipulating the data stored in the database, enabling users to
retrieve, add, update, and delete data.
 SELECT: retrieve data from a database
 INSERT: insert data into a table
 UPDATE: updates existing data within a table
 DELETE: Delete all records from a database table

3. Data Control Language (DCL)

DCL commands manage access permissions, ensuring data security by
controlling who can perform certain actions on the database.
 GRANT: Provides specific privileges to a user (e.g., SELECT, INSERT).
 REVOKE: Removes previously granted permissions from a user.

4. Transaction Control Language (TCL)

TCL commands oversee transactional data to maintain consistency, reliability,
and atomicity.
 ROLLBACK: Undoes changes made during a transaction.
 COMMIT: Saves all changes made during a transaction.
 SAVEPOINT: Sets a point within a transaction to which one can later roll
back.

2
 BODHINI

SQL INTRODUCTION
SQL stands for Structured Query Language. It is a standardized programming
language used to manage and manipulate relational databases. It enables users
to perform a variety of tasks such as querying data, creating and modifying
database structures, and managing access permissions. SQL is widely used
across various relational database management systems such
as MySQL, PostgreSQL, Oracle, and SQL Server.

What is SQL?
Data is at the core of every application, and SQL (Structured Query Language)
manages and interacts with this data. Whether we’re handling a small user
database or analyzing terabytes of sales records, SQL allows efficient
querying, updating, and management of relational databases.
When data needs to be retrieved from a database, SQL is used to construct and
send the request. The Database Management System (DBMS) processes the SQL
query, retrieves the requested data, and returns it to the user or application.
Instead of specifying step-by-step procedures, SQL statements describe what
data should be retrieved, organized, or modified, allowing the DBMS to handle
how the operations are executed efficiently.

SQL Data Types

SQL Data Types are very important in relational databases. It ensures that data
is stored efficiently and accurately. Data types define the type of value a column
can hold, such as numbers, text, or dates. Understanding SQL Data Types is
critical for database administrators, developers, and data analysts to design
robust databases and optimize performance.

What are SQL Data Types?

SQL developers must know what data type will be stored inside each column
while creating a table. SQL data types guide how the system processes data and
dictates the behavior of operations like sorting, searching, and calculations. The
main SQL data types include:

3
 BODHINI

 Numeric Data Types

 Character and String Data Types
 Date and Time Data Types
 Binary Data Types
 Boolean Data Types
 Special Data Types

Numeric Data Types:

Integer Types
These store whole numbers without decimal places. The main integer types
differ in storage size and range:
 TINYINT: The smallest integer type (1 byte). Stores values from -128 to
127 (signed) or 0 to 255 (unsigned). Useful for small ranges like age or
status flags.
 SMALLINT: 2-byte storage. Handles values from -32,768 to 32,767
(signed) or 0 to 65,535 (unsigned). Good for medium-sized counts.
 INT/INTEGER: The most commonly used integer type (4 bytes). Supports
values from -2.1 billion to 2.1 billion (signed) or 0 to 4.2 billion
(unsigned). Used for IDs, quantities, etc.
 BIGINT: 8-byte storage for very large numbers (±9.2 quintillion). Essential
for systems needing extremely large counters or unique identifiers.
Fixed-Point Numbers
These store exact numeric values with specified precision:
 DECIMAL(p,s): Stores exact numbers with 'p' total digits and 's' decimal
places. For example, DECIMAL(5,2) can store 123.45 but not 1234.56.
Critical for financial data where precision matters.
 NUMERIC(p,s): Functionally identical to DECIMAL in most databases.
Part of the SQL standard.
Floating-Point Numbers

4
 BODHINI

These store approximate numeric values and are useful for scientific
calculations:
 FLOAT: Single-precision (4 bytes), about 7 decimal digits of precision.
Good for most floating-point needs.
 DOUBLE: Double-precision (8 bytes), about 15 decimal digits. Used when
higher precision is needed.
 REAL: Typically the same as FLOAT in many databases.

Character and String Data Types:

Fixed-Length Strings
 CHAR(n): Always uses exactly 'n' characters of storage, padding with
spaces if needed. Best for data with consistent length like country codes
(CHAR(2) for 'US', 'IN').
Variable-Length Strings
 VARCHAR(n): Stores up to 'n' characters but only uses space for the
actual content. More efficient than CHAR for most text data like names
or addresses.
Large Text Types
 TEXT: Variable-length storage for large text (up to 65,535 characters).
Used for articles, descriptions.
 TINYTEXT: Up to 255 characters (similar to VARCHAR(255) but with
different handling).
 MEDIUMTEXT: Up to 16MB of text.
 LONGTEXT: Up to 4GB of text, suitable for very large documents.

Date and Time Data Type in SQL:

 DATE: Stores only the date component in YYYY-MM-DD format. Used for
birthdays, events.

5
 BODHINI

 TIME: Stores only the time component (HH:MM:SS). Useful for recording
specific times.
 DATETIME: Combines date and time (YYYY-MM-DD HH:MM:SS) without
timezone. Common for timestamps in applications.
 TIMESTAMP: Similar to DATETIME but includes timezone support.
Automatically converts to UTC. Often used for audit logs.
 YEAR: Stores just the year (2 or 4-digit format). Rarely used in modern
applications.

Binary Data Types in SQL:

 BINARY(n): Fixed-length binary data (like CHAR but for bytes). Used for
hashes or fixed-size binary data.
 VARBINARY(n): Variable-length binary data (like VARCHAR but for bytes).
Stores binary data up to 'n' bytes.
 BLOB: Binary Large Object for storing large binary files like images or
PDFs (up to 65KB).
 TINYBLOB, MEDIUMBLOB, LONGBLOB: Variants supporting up to 255
bytes, 16MB, and 4GB respectively.

Boolean Data Type in SQL

 The BOOLEAN data types are used to store logical values, Stores TRUE (1)
or FALSE (0) values. Some databases also accept 'YES'/'NO' or 'ON'/'OFF'.
Used for flags and binary states.

6
 BODHINI

Practical 1: Used of CREATE, ALTER, RENAME and DROP statement

in the database tables (relations).

1. CREATE TABLE Statement :

Definition: The CREATE TABLE statement is used to define a new table

and its structure including columns and their data types.

Syntax: CREATE TABLE table_name ( column1 datatype, column2

datatype, ... );

Example: CREATE TABLE employee(Id int PRIMARY KEY not null, Name
varchar(50), Age int);

OUTPUT:

2. ALTER TABLE Statement:

Definition: The ALTER TABLE statement is used to modify the structure of

an existing table, such as adding, deleting, or modifying columns.

Syntax: ALTER TABLE table_name ADD column_name datatype;

Example: ALTER TABLE employee ADD Address varchar(50);

7
 BODHINI

OUTPUT:

3. RENAME TABLE Statement:

Definition:
The RENAME TABLE statement is used to change the name of an existing
table in a database. This operation helps in modifying table names
without affecting the data inside the table.
Syntax:
RENAME TABLE old_table_name TO new_table_name;
Example:
RENAME TABLE employee TO IT_employee;
OUTPUT:

8
 BODHINI

4. DROP TABLE Statement:

Definition: The DROP TABLE statement is used to delete an existing table

and all of its data from the database.
Syntax: DROP TABLE table_name;
Example: DROP TABLE customers;
OUTPUT:

9
 BODHINI

Practical 2: Used of INSERT INTO, DELETE and UPDATE statement in

the database tables(relations).

1. INSERT INTO Statement:

Definition: The INSERT INTO statement is used to add new records
(rows) to a table. You can insert values into all columns or only specific
ones.
Syntax: INSERT INTO table_name (column1, column2, ...) VALUES
(value1, value2, ...);
Example: INSERT INTO it_employee VALUES(111,"Sahil",21,"Rajpura");
INSERT INTO it_employee VALUES(222,"Rahul",24,"Patiala");

OUTPUT:

2. DELETE Statement:

Definition: The DELETE statement removes one or more records from a

table based on a specified condition. If no condition is provided, all rows
are deleted.

Syntax: DELETE FROM table_name WHERE condition;

Example: DELETE FROM it_employee WHERE Age < 20;

10
 BODHINI

OUTPUT:

3. UPDATE Statement:
Definition: The UPDATE statement is used to modify existing records in a
table. You can update one or more columns based on a condition.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition;
Example: UPDATE it_employee SET Address="Mohali" WHERE Id=222;

11
 BODHINI

OUTPUT:

12
 BODHINI

Practical 3: Use of simple select statement.

SELECT Statement:
Definition: The SELECT statement is used to retrieve data from one or more
tables. It is the most commonly used SQL command.
Syntax: SELECT column1, column2 FROM table_name WHERE condition;
Example: SELECT Name, Address FROM it_employee;

OUTPUT:

13
 BODHINI

Practical 4: Use of Select query on two relations.

Definition:
The SELECT statement is used to retrieve data from one or more tables
(relations). When querying two tables, we typically use JOIN operations to
combine related data based on common columns.
Syntax (Using INNER JOIN):
SELECT table1.column1, table2.column2, ...
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Example:
SELECT [Link], [Link], departments.dept_name,
[Link]
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

Department Table:

Employees Table:

14
 BODHINI

OUTPUT:

15
 BODHINI

Practical 5: Use of nesting of queries.

Nested SELECT (Subquery):

Definition: A nested SELECT or subquery is a SELECT query placed inside another
query to retrieve data based on the result of the inner query.
Syntax: SELECT column FROM table
WHERE column IN (SELECT column FROM table WHERE condition);
Example:
SELECT name FROM it_employee
WHERE Id IN (SELECT Id FROM it_employee WHERE Id IN (111, 666));

OUTPUT:

16
 BODHINI

Practical 6: Use of Aggregate functions.

1. COUNT () Function:

Definition: Returns the number of records in a table or column.

Syntax: SELECT COUNT (column_name) FROM table_name;

Example: SELECT COUNT(Id) FROM it_employee;

OUTPUT:

2. SUM () Function:

Definition: Returns the total sum of a numeric column.

Syntax: SELECT SUM (column_name) FROM table_name;

Example: SELECT SUM(Salary) FROM it_employee;

OUTPUT:

3. AVG () Function:

Definition: Returns the average value of a numeric column.

Syntax: SELECT AVG (column_name) FROM table_name;

17
 BODHINI

Example: SELECT AVG(Salary) FROM it_employee;

OUTPUT:

4. MAX () Function:

Definition: Returns the highest value in a column.

Syntax: SELECT MAX (column_name) FROM table_name;

Example: SELECT MAX(Age) FROM it_employee;

OUTPUT:

5. MIN () Function:

Definition: Returns the lowest value in a column.

Syntax: SELECT MIN (column_name) FROM table_name;

Example: SELECT MIN(Age) FROM it_employee;

OUTPUT:

18
 BODHINI

Practical 7: Use of Substring comparison.

SUBSTRING comparison:
Definition: Used to compare part of strings.
Syntax: SELECT * FROM table_name WHERE SUBSTRING(column_name) LIKE
'Data%';
Example: SELECT Address FROM it_employee WHERE Address LIKE '%a';

OUTPUT:

19
 BODHINI

Practical 8: Use of order by statement.

ORDER BY Statement:
Definition: The ORDER BY clause is used to sort the result set in ascending or
descending order based on one or more columns.
Syntax: SELECT column1, column2, ... FROM table_name ORDER BY
column_name [ASC|DESC];
Example: SELECT * FROM it_employee ORDER BY Age ASC;

OUTPUT:

20
 BODHINI

Practical 9: Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS
(Book_id, Author_Name) PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, No-of_Copies) BOOK_LENDING
(Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH (Branch_id, Branch_Name, Address) Write SQL
queries to
1. Retrieve details of all books in the library_id, title, name of
publisher, authors, number of copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3
books between Jan 2018 to Jun 2018
3. Delete a book in BOOK table. Update the contents of other tables
to reflect this data manipulation operation.
4. Partition the BOOK table based on year of publication.
Demonstrate its working with a simple query.
5. Create a view of all books and its number of copies that are
currently available in the library.

1. BOOK Table :

CREATE TABLE BOOK (

Book_id INT PRIMARY KEY,
Title VARCHAR(255),
Publisher_Name VARCHAR(100),
Pub_Year YEAR
);

INSERT INTO BOOK (Book_id, Title, Publisher_Name, Pub_Year) VALUES

(1, 'Database Systems', 'Pearson', 2015),
(2, 'Operating Systems', 'McGraw-Hill', 2018),
(3, 'Machine Learning', 'Oxford', 2020);

21
 BODHINI

OUTPUT:

2. BOOK_AUTHORS Table:

CREATE TABLE BOOK_AUTHORS (

Book_id INT,
Author_Name VARCHAR(100),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id) ON DELETE
CASCADE
);

INSERT INTO BOOK_AUTHORS (Book_id, Author_Name) VALUES

(1, 'Raghu Ramakrishnan'),
(1, 'Johannes Gehrke'),
(2, 'Abraham Silberschatz'),
(2, 'Peter Baer Galvin'),
(3, 'Tom Mitchell');

OUTPUT:

22
 BODHINI

3. PUBLISHER Table:

CREATE TABLE PUBLISHER (

Name VARCHAR(100) PRIMARY KEY,
Address VARCHAR(255),
Phone VARCHAR(15)
);

INSERT INTO PUBLISHER (Name, Address, Phone) VALUES

('Pearson', 'New York, USA', '1234567890'),
('McGraw-Hill', 'Boston, USA', '9876543210'),
('Oxford', 'London, UK', '5678901234');

OUTPUT:

4. BOOK_COPIES Table:

CREATE TABLE BOOK_COPIES (

Book_id INT,
Branch_id INT,
No_of_Copies INT,
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
);

INSERT INTO BOOK_COPIES (Book_id, Branch_id, No_of_Copies) VALUES

23
 BODHINI

(1, 101, 5),

(1, 102, 3),
(2, 101, 2),
(3, 102, 4);

OUTPUT:

5. BOOK_LENDING Table:

CREATE TABLE BOOK_LENDING (

Book_id INT,
Branch_id INT,
Card_No INT,
Date_Out DATE,
Due_Date DATE,
PRIMARY KEY (Book_id, Branch_id, Card_No, Date_Out),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id),
FOREIGN KEY (Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id)
);

INSERT INTO BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out,

Due_Date) VALUES
(1, 101, 1001, '2018-02-01', '2018-02-15'),
(2, 101, 1002, '2018-03-10', '2018-03-24'),
(3, 102, 1003, '2018-05-05', '2018-05-19'),
(1, 102, 1001, '2018-06-01', '2018-06-15'),
(2, 101, 1001, '2018-06-10', '2018-06-24'),
(3, 102, 1001, '2018-06-15', '2018-06-29');

24
 BODHINI

OUTPUT:

6. LIBRARY_BRANCH:

CREATE TABLE LIBRARY_BRANCH (

Branch_id INT PRIMARY KEY,
Branch_Name VARCHAR(100),
Address VARCHAR(255)
);

INSERT INTO LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

VALUES
(101, 'Central Library', '123 Main St, New York'),
(102, 'West Side Library', '456 West St, Boston');

OUTPUT:

25
 BODHINI

Query 1: Retrieve full book details (with authors, publisher, copies).

SELECT B.Book_id, [Link], [Link] AS Publisher_Name,

GROUP_CONCAT(A.Author_Name SEPARATOR ', ') AS Authors,
C.Branch_id, C.No_of_Copies
FROM BOOK B
JOIN PUBLISHER P ON B.Publisher_Name = [Link]
JOIN BOOK_AUTHORS A ON B.Book_id = A.Book_id
JOIN BOOK_COPIES C ON B.Book_id = C.Book_id
GROUP BY B.Book_id, C.Branch_id;

OUTPUT:

Query 2: Borrowers who borrowed more than 3 books (Jan–Jun

2018).

SELECT L.Card_No, COUNT(L.Book_id) AS Books_Borrowed

FROM BOOK_LENDING L
WHERE L.Date_Out BETWEEN '2018-01-01' AND '2018-06-30'
GROUP BY L.Card_No
HAVING COUNT(L.Book_id) > 3;

26
 BODHINI

OUTPUT:

Query 3: Delete a book and update other tables.

DELETE FROM BOOK_AUTHORS WHERE Book_id = 1;

DELETE FROM BOOK_COPIES WHERE Book_id = 1;
DELETE FROM BOOK_LENDING WHERE Book_id = 1;
DELETE FROM BOOK WHERE Book_id = 1;

OUTPUT:

Query 4: Partition BOOK table by publication year.

ALTER TABLE book

PARTITION BY RANGE (Pub_Year) (
PARTITION p_before_2000 VALUES LESS THAN (2000),

27
 BODHINI

PARTITION p_2000_2010 VALUES LESS THAN (2010),

PARTITION p_2010_2020 VALUES LESS THAN (2020),
PARTITION p_2020_onwards VALUES LESS THAN MAXVALUE
);

SELECT * FROM book PARTITION(p_2010_2020);

OUTPUT:

Query 5: Create a view of all books with the number of available

copies.

CREATE VIEW Available_Books AS

SELECT B.Book_id, [Link], SUM(C.No_of_Copies) AS Total_Copies
FROM BOOK B
JOIN BOOK_COPIES C ON B.Book_id = C.Book_id
GROUP BY B.Book_id;

OUTPUT:

28
 BODHINI

Practical 10: Consider the following schema for Order 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)
Write SQL queries to
1. Count the customers with grades above Amritsar’s average.
2. Find the name and numbers of all salesmen who had more than
one customer.
3. List all salesmen and indicate those who have and don’t have
customers in their cities (Use UNION operation.)
4. Create a view that finds the salesman who has the customer with
the highest order of a day.
5. Demonstrate the DELETE operation by removing salesman with id
1000. All his orders must also be deleted.

Order Database Queries:

1. SALESMAN Table:

CREATE TABLE SALESMAN (

Salesman_id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
City VARCHAR(50) NOT NULL,
Commission DECIMAL(5,2) NOT NULL
);

INSERT INTO SALESMAN VALUES

(1000, 'John Doe', 'Amritsar', 0.15),
(1001, 'Jane Smith', 'Delhi', 0.12),

29
 BODHINI

(1002, 'Robert Johnson', 'Mumbai', 0.10),

(1003, 'Alice Brown', 'Amritsar', 0.11),
(1004, 'David Wilson', 'Chennai', 0.14),
(1005, 'Emily Davis', 'Delhi', 0.13);

OUTPUT:

2. CUSTOMER Table:

CREATE TABLE CUSTOMER (

Customer_id INT PRIMARY KEY,
Cust_Name VARCHAR(50) NOT NULL,
City VARCHAR(50) NOT NULL,
Grade INT,
Salesman_id INT,
FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id)
);

INSERT INTO CUSTOMER VALUES

(2001, 'Customer A', 'Amritsar', 2, 1000),
(2002, 'Customer B', 'Delhi', 1, 1001),
(2003, 'Customer C', 'Mumbai', 3, 1002),
(2004, 'Customer D', 'Amritsar', 2, 1000),
(2005, 'Customer E', 'Chennai', 1, 1004),

30
 BODHINI

(2006, 'Customer F', 'Delhi', 2, 1001),

(2007, 'Customer G', 'Mumbai', 3, 1002),
(2008, 'Customer H', 'Amritsar', 1, 1003);

OUTPUT:

3. ORDERS Table:

CREATE TABLE ORDERS (

Ord_No INT PRIMARY KEY,
Purchase_Amt DECIMAL(10,2) NOT NULL,
Ord_Date DATE NOT NULL,
Customer_id INT,
Salesman_id INT,
FOREIGN KEY (Customer_id) REFERENCES CUSTOMER(Customer_id),
FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id)
);

INSERT INTO ORDERS VALUES

(3001, 1500.00, '2023-01-15', 2001, 1000),
(3002, 2500.00, '2023-01-15', 2002, 1001),

31
 BODHINI

(3003, 1800.00, '2023-01-16', 2003, 1002),

(3004, 3500.00, '2023-01-16', 2004, 1000),
(3005, 1200.00, '2023-01-17', 2005, 1004),
(3006, 2800.00, '2023-01-17', 2006, 1001),
(3007, 4200.00, '2023-01-18', 2007, 1002),
(3008, 1900.00, '2023-01-18', 2008, 1003),
(3009, 3100.00, '2023-01-19', 2001, 1000),
(3010, 2200.00, '2023-01-19', 2003, 1002);

OUTPUT:

Query 1: Count the customers with grades above Amritsar's

average.

SELECT COUNT(*) AS Customer_Count

FROM CUSTOMER
WHERE Grade > (

32
 BODHINI

SELECT AVG(Grade)
FROM CUSTOMER
WHERE City = 'Amritsar'
);

OUTPUT:

Query 2: Find the name and numbers of all salesmen who had more
than one customer.

SELECT s.Salesman_id, [Link], COUNT(c.Customer_id) AS Customer_Count

FROM SALESMAN s
JOIN CUSTOMER c ON s.Salesman_id = c.Salesman_id
GROUP BY s.Salesman_id, [Link]
HAVING COUNT(c.Customer_id) > 1;

OUTPUT:

33
 BODHINI

Query 3: List all salesmen and indicate those who have and don't
have customers in their cities (Use UNION operation)
SELECT s.Salesman_id, [Link], 'Has customers in city' AS Status
FROM SALESMAN s
WHERE EXISTS (
SELECT 1
FROM CUSTOMER c
WHERE c.Salesman_id = s.Salesman_id AND [Link] = [Link]
)
UNION
SELECT s.Salesman_id, [Link], 'No customers in city' AS Status
FROM SALESMAN s
WHERE NOT EXISTS (
SELECT 1
FROM CUSTOMER c
WHERE c.Salesman_id = s.Salesman_id AND [Link] = [Link]
);

OUTPUT:

34
 BODHINI

Query 4: Create a view that finds the salesman who has the
customer with the highest order of a day.

CREATE VIEW Highest_Order_Salesman AS

SELECT o.Ord_Date, s.Salesman_id, [Link], MAX(o.Purchase_Amt) AS
Highest_Order
FROM ORDERS o
JOIN SALESMAN s ON o.Salesman_id = s.Salesman_id
GROUP BY o.Ord_Date, s.Salesman_id, [Link]
HAVING MAX(o.Purchase_Amt) = (
SELECT MAX(Purchase_Amt)
FROM ORDERS
WHERE Ord_Date = o.Ord_Date
);

OUTPUT:

Query 5: Demonstrate the DELETE operation by removing salesman

with id 1000. All his orders must also be deleted.

DELETE FROM ORDERS

WHERE Salesman_id = 1000;

35
 BODHINI

DELETE FROM CUSTOMER

WHERE Salesman_id = 1000;
DELETE FROM SALESMAN
WHERE Salesman_id = 1000;

OUTPUT:

36
 BODHINI

Practical 11: Write a PL/SQL code to add two numbers and display
the result. Read the numbers during run time.

CREATE TABLE Add_Numbers ( num1 INT, num2 INT );

INSERT INTO Add_Numbers VALUES (10, 20);
INSERT INTO Add_Numbers VALUES (10, 20);
INSERT INTO Add_Numbers VALUES (14, 50);
INSERT INTO Add_Numbers VALUES (78, 37);
INSERT INTO Add_Numbers VALUES (76, 2);
INSERT INTO Add_Numbers VALUES (19, 75);
SELECT num1, num2, (num1 + num2) AS sum_result FROM
Add_Numbers;

OUTPUT:

37
 BODHINI

Practical 12: Write a PL/SQL code to find sum of first 10 natural

numbers using while and for loop.
Using WHILE Loop:
DELIMITER //
CREATE PROCEDURE sum_first_10_natural()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;

-- Using WHILE loop

WHILE i <= 10 DO
SET total = total + i;
SET i = i + 1;
END WHILE;
SELECT CONCAT('Sum (WHILE loop): ', total) AS Result;
END //
DELIMITER ;
CALL sum_first_10_natural();

OUTPUT:

Using FOR Loop:

DELIMITER //
CREATE PROCEDURE sum_with_repeat()

38
 BODHINI

BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
REPEAT
SET total = total + i;
SET i = i + 1;
UNTIL i > 10 END REPEAT;
SELECT CONCAT('Sum (FOR loop): ', total) AS Result;
END //
DELIMITER ;
CALL sum_with_repeat();

OUTPUT:

39
 BODHINI

Practical 13: Write a program to create a trigger which will convert

the name of a student to upper case before inserting or updating
the name column of student table.

CREATE TABLE student (

student_id INT PRIMARY KEY,
name VARCHAR(100),
other_columns VARCHAR(100)
);
-- Create the trigger for INSERT
DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
SET [Link] = UPPER([Link]);
END//
DELIMITER ;
-- Create the trigger for UPDATE
DELIMITER //
CREATE TRIGGER before_student_update
BEFORE UPDATE ON student
FOR EACH ROW
BEGIN
SET [Link] = UPPER([Link]);
END//
DELIMITER ;

40
 BODHINI

INSERT INTO student (student_id, name) VALUES (1, 'jane smith');

INSERT INTO student (student_id, name) VALUES (2, 'john doe');
INSERT INTO student (student_id, name) VALUES (3, 'sam altman');

OUTPUT:

41
 BODHINI

Practical 14: Write a PL/SQL block to count the number of rows

affected by an update statement using SQL%ROWCOUNT.

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10,2),
department_id INT
);

INSERT INTO employees VALUES

(1, 'John', 'Doe', 5000, 10),
(2, 'Jane', 'Smith', 6000, 20),
(3, 'Bob', 'Johnson', 5500, 10);

DELIMITER //
CREATE PROCEDURE update_employees_with_count()
BEGIN
DECLARE rows_affected INT;
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;
SET rows_affected = ROW_COUNT();
SELECT CONCAT('Rows affected: ', rows_affected) AS result
COMMIT;

42
 BODHINI

END //
DELIMITER ;
CALL update_employees_with_count();

OUTPUT:

43
 BODHINI

Practical 15: Write a PL/SQL block to increase the salary of all

doctors by 1000.

CREATE TABLE doctors (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
specialization VARCHAR(100),
salary DECIMAL(10,2) NOT NULL
);

INSERT INTO doctors (name, specialization, salary) VALUES

('Dr. Smith', 'Cardiology', 8000.00),
('Dr. Johnson', 'Pediatrics', 7500.00),
('Dr. Williams', 'Neurology', 9000.00);

DELIMITER //
CREATE PROCEDURE increase_doctor_salaries()
BEGIN
DECLARE rows_affected INT;
UPDATE doctors
SET salary = salary + 1000;
SET rows_affected = ROW_COUNT();
SELECT CONCAT('Successfully increased salary for ', rows_affected, ' doctors')
AS result;
END //
DELIMITER ;
CALL increase_doctor_salaries();

44
 BODHINI

OUTPUT:

45