1

SQL:

Cycle-I: Aim: The marketing company wishes to computerize its operations by using the following
tables.
Table Name: Client Master
Description: Used to store client information
Column Name Data Type Size Attribute
Primary key
CLIENT_NO Varchar2 6
NAME Varchar2 20 Not null
ADDRESS1 Varchar2 30
ADDRESSS Varchar2 30
CITY Varchar2 15
PINCODE Varchar2 8
STATE Varchar2 15
BAL_DUE Number 10,2

Query for creating above table:

CREATE TABLE Client Master (
CLIENT_NO VARCHAR(6) PRIMARY KEY, -- Unique identifier for each client
NAME VARCHAR(20) NOT NULL, -- Client's name
CITY VARCHAR(15), -- Client's city
STATE VARCHAR(30),
PINCODE VARCHAR(8),
BAL_DUE DECIMAL(10, 2) DEFAULT 0 -- Balance due, defaulting to 0
);

Table Name: Product Master

Description: Used to store product information

Column Name Data Type Size Attribute

Primary key
PRODUCT_NO Varchar2 6
DESCRIPTION Varchar2 15 Not null
PROFIT _PERCENT Number 4,2 Not null
UNIT_MEASUE Varchar2 10
QTY_ON_ HAND Number 8
REORDER_LVL Number 8
SELL_PRICE Number 8,2 Not null, cannot be 0
COST _PRICE Number 8,2 Not null,cannot be 0
2

Query for creating above table:

CREATE TABLE Product_Master (
PRODUCT_NO VARCHAR(6) PRIMARY KEY, -- Product Number (Primary Key)
DESCRIPTION VARCHAR(15) NOT NULL, -- Product description
PROFIT_PERCENT DECIMAL(4,2) NOT NULL, -- Profit percentage (e.g., 15.25)
UNIT_MEASURE VARCHAR(10), -- Unit of measure (e.g., "kg", "litre")
QTY_ON_HAND DECIMAL(8), -- Quantity on hand
REORDER_LVL DECIMAL(8), -- Reorder level
SELL_PRICE DECIMAL(8,2) NOT NULL CHECK (SELL_PRICE <> 0), -- Selling price
COST_PRICE DECIMAL(8,2) NOT NULL CHECK (COST_PRICE <> 0) -- Cost price
);

Table Name: Salesman master

Description: Used to store salesman information working for the company.

Data
Column Name Attribute
Type Size
Primary key
SALESMAN_NO Varchar2 6
SALESMAN_NAME Varchar2 20 Not null
ADDRESS1 Varchar2
30
ADDRESS2 Varchar2
30
CITY Varchar2
20
PINCODE Number
8
STATE Vachar2
20
SAL_AMT Number Not null, cannotbe0
8,2
TGT_TO_GET Number Not null, cannotbe0
6,2
YTD_SALES Number Not null
6,2
REMARKS Varchar2
20
3

Query for creating above table:

CREATE TABLE Salesman (
SALES_MAN_NO VARCHAR(6) PRIMARY KEY, -- Salesman DECIMAL (Primary Key)
SALES_MAN_NAME VARCHAR(20) NOT NULL, -- Salesman Name (Cannot be Null)
ADDRESS1 VARCHAR(30), -- Address Line 1
ADDRESS2 VARCHAR(30), -- Address Line 2
CITY VARCHAR(20), -- City
PINCODE DECIMAL(8), -- Pincode
STATE VARCHAR(20), -- State
SAL_AMT DECIMAL(8,2) NOT NULL CHECK (SAL_AMT <> 0), -- Salary Amount (Cannot
be Zero)
TGT_TO_GET DECIMAL(6,2) NOT NULL CHECK (TGT_TO_GET <> 0), -- Target to
Achieve (Cannot be Zero)
YTD_SALES DECIMAL(6,2) NOT NULL, -- Year-to-Date Sales
REMARKS VARCHAR(20) -- Remarks
);

Table Name: SALES_ORDER

Description: Used to store client orders
Column Name Data Type Size Attribute
ORDER_NO Varchar2 6 Primary key
CLIENT_NO Varchar2 6 Foreign Key
ORDER _DATE Date

DELY_ADDRESS Varchar2 25
SALESMAN_NO Varchar2 6 Foreign Key
DELY_TYPE Char 1 Delivery:part(p)/full(f)anddefault‘
F’
BILL_YN Char 1
DELY_DATE Date Can’tbe lessthanorderdate
Varchar2 Values(“InProcess”,“Fulfilled”,
ORDER _STATUS 10
“Back Order”, “Cancelled)
4

Query for creating above table:

CREATE TABLE Sales_Orders (

ORDER_NO VARCHAR(6) PRIMARY KEY, -- Unique order DECIMAL

CLIENT_NO VARCHAR(6) REFERENCES client_master(CLIENT_NO), -- Client DECIMAL

(foreign key)

ORDER_DATE DATE NOT NULL, -- Date of the order

DELY_ADDRESS VARCHAR(25), -- Delivery address

SALESMAN_NO VARCHAR(6) REFERENCES Salesman(SALES_MAN_NO), -- Salesman

DECIMAL (foreign key)

DELY_TYPE CHAR(1) DEFAULT 'F', -- Delivery type ('F' for Full, 'P' for Part)

BILL_YN CHAR(1), -- Billing indicator

DELY_DATE DATE CHECK (DELY_DATE >= ORDER_DATE), -- Delivery date (must be later than
or equal to order date)

ORDER_STATUS VARCHAR(10) CHECK (ORDER_STATUS IN ('InProcess', 'Fulfilled', 'Back

Order', 'Cancelled')) -- Order status

);

Table Name: ORDER_DETAILS

Description:Used to store client’s order with details of each product ordered.

ColumnName Data Type Size Attribute

ORDER_NO Varchar2 6 Primary key references
SALES_ORDER table
PRODUCT_NO Varchar2 6 Foreign Key references
SALES_ORDER_table
5

QTY_ ORDERED Number 8

QTY_ DISP Number 8
PRODUCT_RATE Number 10,2 Foreign Key

Query for creating above table:

CREATE TABLE OrderDetails (
ORDER_NO VARCHAR(6) PRIMARY KEY REFERENCES
SALES_ORDERS(ORDER_NO), -- References the SALES_ORDER table
PRODUCT_NO VARCHAR(6) REFERENCES Product_Master(PRODUCT_NO), --
References the SALES_ORDER table
QTY_ORDERED DECIMAL(8), -- Quantity ordered
QTY_DISP DECIMAL(8), -- Quantity dispatched
PRODUCT_RATE DECIMAL(10,2) -- Product rate
);

1. Retrieve the list of names, city, and the state of all the clients.
6

select * from client_master;

2. List all the clients who are located in ‘Mumbai’ or ‘Bangalore’.

select * from client_master where city='Mumbai';

3.List the various products available from the

product_mastertable.
7

select * from Product_Master;

4. Find the names of salesmen who have a salary equal to Rs.3000.

5. List the names o fall clients having ‘a’ as the second letter in their
names.
8

SELECT NAME FROM client_master WHERE NAME LIKE '_a%';

6. List all clients whose Baldue is greater than value 1000.

select * from client_master where bal_due>1000;

7. List the clients who stay in a city whose first letter is ‘M’.
9

SELECT NAME FROM client_master WHERE city LIKE 'M%';

8. List all information from the sales-order table for orders placed in
the month of March.
SELECT * FROM sales_orderS WHERE EXTRACT(MONTH FROM ORDER_DATE) = 3;

9. List the products whose selling price is greater than 1000 and less
than or equal to 3000.
10

SELECT PRODUCT_NO, DESCRIPTION, SELL_PRICE

FROM Product_Master
WHERE SELL_PRICE > 1000 AND SELL_PRICE <= 3000;

10. Find the products whose selling price is greater than 1000 and
also find the new selling price as the original selling price of 0.50.

SELECT PRODUCT_NO, DESCRIPTION, SELL_PRICE,

(SELL_PRICE * 0.50) AS NEW_SELL_PRICE
FROM Product_Master
WHERE SELL_PRICE > 1000;

Cycle-II Supplier
11

Aim: A manufacturing company deals with various parts and various suppliers supply these parts. It
consists of three tables to record its entire information. Those are as follows.

Supplier (Supplier_No, Sname, City, status)

Query for creating Supplier Table

CREATE TABLE Supplier (
Supplier_No VARCHAR(6) PRIMARY KEY, -- Unique supplier identifier
Sname VARCHAR(50) NOT NULL, -- Supplier name
City VARCHAR(30), -- Supplier's city
Status VARCHAR(10) -- Supplier status
);

Part(Part_no, pname, color, weight, city, cost)

Query for creating Part Table
CREATE TABLE Part (
Part_No VARCHAR(6) PRIMARY KEY, -- Unique part identifier
Pname VARCHAR(50) NOT NULL, -- Part name
Color VARCHAR(20), -- Part color
Weight DECIMAL(8, 2), -- Part weight
City VARCHAR(30), -- Manufacturing city
Cost DECIMAL(10, 2) -- Part cost
);

Query for creating Shipment Table

12

Shipment (supplier No, Part_no, city)

CREATE TABLE Shipment (
Supplier_No VARCHAR(6), -- Foreign key from Supplier table
Part_No VARCHAR(6), -- Foreign key from Part table
City VARCHAR(30), -- Shipment city
PRIMARY KEY (Supplier_No, Part_No),
FOREIGN KEY (Supplier_No) REFERENCES Supplier(Supplier_No),
FOREIGN KEY (Part_No) REFERENCES Part(Part_No)
);

Query for creating JX Table

JX (project_no, project_name, city)

CREATE TABLE JX (
Project_No VARCHAR(6) PRIMARY KEY, -- Unique project identifier
Project_Name VARCHAR(50) NOT NULL,-- Project name
City VARCHAR(30) -- Project city
);
13

Query for creating SPJX Table

SPJX (Supplier no, part_no, project_no,city)

CREATE TABLE SPJX (

Supplier_No VARCHAR(6), -- Foreign key from Supplier table
Part_No VARCHAR(6), -- Foreign key from Part table
Project_No VARCHAR(6), -- Foreign key from JX table
City VARCHAR(30), -- City associated with SPJX
PRIMARY KEY (Supplier_No, Part_No, Project_No),
FOREIGN KEY (Supplier_No) REFERENCES Supplier(Supplier_No),
FOREIGN KEY (Part_No) REFERENCES Part(Part_No),
FOREIGN KEY (Project_No) REFERENCES JX(Project_No)
);
14

1. Get supplier numbers and status for suppliers in Jaipurwith status

Active.
SELECT Supplier_No, Status
FROM Supplier
WHERE City = ' Jaipur' AND Status =’Active’;

2. Get project names for projects supplied by supplier ’S’.

SELECT JX.Project_Name
FROM JX
JOIN SPJX ON JX.Project_No = SPJX.Project_No
WHERE SPJX.Supplier_No LIKE 'S%';
15

3.Get colors of parts supplied by supplier S’.

SELECT DISTINCT Part.Color
FROM Part
JOIN SPJX ON Part.Part_No = SPJX.Part_No
WHERE SPJX.Supplier_No LIKE 'S%';

4. Get part numbers for parts supplied to any project in Mumbai.

SELECT DISTINCT SPJX.Part_No

FROM SPJX
JOIN JX ON SPJX.Project_No = JX.Project_No
WHERE JX.City = 'Mumbai';
16

5. Find the id’s of suppliers who supply a red or pink parts.

SELECT DISTINCT SPJX.Supplier_No
FROM SPJX
JOIN Part ON SPJX.Part_No = Part.Part_No
WHERE Part.Color IN ('Red', 'Pink');
17

Cycle–III Employee Database

Aim: An enterprise wishes to maintain a database to automate its operations. Enterprise is divided into
certain departments and each department consists of employees. The following two tables describe the
automation schemas.

Emp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)

1. Employee Table (Emp):

CREATE TABLE Emp (

Empno INT PRIMARY KEY, -- Employee number (unique identifier)
Ename VARCHAR(50) NOT NULL, -- Employee name
Job VARCHAR(30), -- Job title
Mgr INT, -- Manager ID (references another Empno)
Hiredate DATE NOT NULL, -- Date of hiring
Sal DECIMAL(10, 2) NOT NULL, -- Salary
Comm DECIMAL(10, 2), -- Commission (optional)
Deptno INT, -- Department number (foreign key)
FOREIGN KEY (Deptno) REFERENCES Dept(Deptno)
);

2. Department Table (Dept):

CREATE TABLE Dept (

Deptno INT PRIMARY KEY, -- Department number (unique identifier)
Dname VARCHAR(50) NOT NULL, -- Department name
Loc VARCHAR(50) -- Location of the department
);
18

1. List the details of employees who have joined before the end of
September ’23.

SELECT *
FROM Emp
WHERE Hiredate < '2023-09-30';

2. List the name of the employee and designation of the employee,

who does not report to anybody.
SELECT Ename, Job
FROM Emp
WHERE Mgr IS NULL;

3. List the name,salary and PF amount of all the employees(PF is

calculated as10%of salary)
SELECT Ename, Sal, (Sal * 0.10) AS PF_Amount FROM Emp;

4. List the names of employees who are more than 2 years old in the
organization.
19

SELECT Ename
FROM Emp
WHERE Hiredate <= CURRENT_DATE - INTERVAL '2' YEAR;

5. Determine the number of employees, who are taking commission.

SELECT COUNT(*) AS NumberOfEmployeesWithCommission
FROM Emp
WHERE Comm IS NOT NULL;

6. Update the employee salary by 20%, whose experience is greater

than 12 years.

UPDATE Emp
SET Sal = Sal * 1.20
WHERE Hiredate <= CURRENT_DATE - INTERVAL '12' YEAR;
select * from Emp;

7. Determine the department does not contain any employees.

20

SELECT D.Deptno, D.Dname

FROM Dept D
LEFT JOIN Emp E ON D.Deptno = E.Deptno
WHERE E.Empno IS NULL;

8. Create a view, which contains employee name and their manager

names working in sales department.
CREATE VIEW Emp_Manager_Sales AS
SELECT
E.Ename AS Employee_Name,
M.Ename AS Manager_Name
FROM Emp E
LEFT JOIN Emp M ON E.Mgr = M.Empno
INNER JOIN Dept D ON E.Deptno = D.Deptno
WHERE D.Dname = 'Sales';
select *from Emp_Manager_Sales;

9. Determine the employees, whose total salary is like the minimum

21

salary of any department.

SELECT E.Ename, D.Dname, E.Sal, E.Comm, (E.Sal + COALESCE(E.Comm, 0)) AS Total_Salary
FROM Emp E
JOIN Dept D ON E.Deptno = D.Deptno
WHERE (E.Sal + COALESCE(E.Comm, 0)) IN (
SELECT MIN(Sal + COALESCE(Comm, 0))
FROM Emp
GROUP BY Deptno
);

10. List the department numbers and number of employees in each

department.
SELECT Deptno, COUNT(*) AS Number_of_Employees
FROM Emp
GROUP BY Deptno;

PL/SQL PROGRAMS
22

1. Writea PL/SQL program to check the given string is palindrome or not.

DECLARE
input_string VARCHAR2(100); -- Input string
reversed_string VARCHAR2(100); -- Reversed string
length_of_string INTEGER; -- Length of input string
is_palindrome BOOLEAN; -- Flag for palindrome check
BEGIN
-- Assign input string here
input_string := 'LEVEL'; -- Replace 'LEVEL' with your desired string

length_of_string := LENGTH(input_string); -- Get length of the string

reversed_string := ''; -- Initialize reversed string

-- Reverse the string

FOR i IN REVERSE 1..length_of_string LOOP
reversed_string := reversed_string || SUBSTR(input_string, i, 1);
END LOOP;

-- Check if input_string equals reversed_string

IF input_string = reversed_string THEN
is_palindrome := TRUE;
ELSE
is_palindrome := FALSE;
END IF;

-- Display the result

IF is_palindrome THEN
DBMS_OUTPUT.PUT_LINE(input_string || ' is a palindrome.');
ELSE
DBMS_OUTPUT.PUT_LINE(input_string || ' is not a palindrome.');
END IF;
END;

Output:

LEVEL is a palindrome.
23

2. The HRD manager has decided to raise the employee salary by 15% write a PL/SQL block to
accept the employee number and update the salary of that employee. Display appropriate
messages based on the existence of the record in the Emp table.

DECLARE
v_empno NUMBER; -- Variable to hold the employee number
v_salary NUMBER; -- Variable to hold the employee's current salary
v_count NUMBER; -- Variable to check if the employee exists
BEGIN
-- Accept the employee number
v_empno := &empno; -- Replace &empno with the input employee number while running

-- Check if the employee exists

SELECT COUNT(*)
INTO v_count
FROM Emp
WHERE Empno = v_empno;
IF v_count = 0 THEN
-- Employee record not found
DBMS_OUTPUT.PUT_LINE('Employee with Employee Number ' || v_empno || ' does not
exist.');
ELSE
-- Fetch the current salary
SELECT Salary
INTO v_salary
FROM Emp
WHERE Empno = v_empno;
-- Update the salary
UPDATE Emp
SET Salary = Salary * 1.15
WHERE Empno = v_empno;

DBMS_OUTPUT.PUT_LINE('Salary updated successfully for Employee Number ' || v_empno

||
'. Previous Salary: ' || v_salary ||
', New Salary: ' || (v_salary * 1.15));
END IF;
-- Commit the changes
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
24

3. Write a PL/SQL program to display the top 10 rows in the Emp table based on their job and
salary.

SET SERVEROUTPUT ON;

DECLARE
-- Cursor to fetch top 10 employees ordered by Job and Salary
CURSOR emp_cursor IS
SELECT Empno, Ename, Job, Sal
FROM Emp
ORDER BY Job, Sal DESC
FETCH FIRST 10 ROWS ONLY; -- For Oracle 12c and above

BEGIN
-- Loop through the cursor
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Empno: ' || emp_record.Empno ||
', Name: ' || emp_record.Ename ||
', Job: ' || emp_record.Job ||
', Salary: ' || emp_record.Sal);
END LOOP;
END;