SQL EXPERIMENTS:-
1. Aim: The marketing company wishes to computerize its operations by using the following tables.
producers:-
1.1. Create Table: `CLIENT_MASTER` and Insert Values
CREATE TABLE CLIENT_MASTER (
CLIENT_NO VARCHAR2(6) PRIMARY KEY,
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)
);
INSERT INTO CLIENT_MASTER VALUES ('C001', 'Rajesh', 'Street 1', 'Apartment 10', 'Mumbai', '400001', 'Maharashtra', 1500.50);
INSERT INTO CLIENT_MASTER VALUES ('C002', 'Amit', 'Sector 9', 'Block A', 'Bangalore', '560001', 'Karnataka', 500.00);
INSERT INTO CLIENT_MASTER VALUES ('C003', 'Priya', 'Main Road', 'House 22', 'Chennai', '600001', 'Tamil Nadu', 1200.75);
INSERT INTO CLIENT_MASTER VALUES ('C004', 'Anjali', 'Park Lane', 'Flat 3', 'Mumbai', '400002', 'Maharashtra', 950.00);
INSERT INTO CLIENT_MASTER VALUES ('C005', 'Ravi', 'Cross Road', 'Villa 5', 'Delhi', '110001', 'Delhi', 2000.00);
output:- 1.1 CLIENT_MASTER
|
CLIENT_NO |
NAME |
ADDRESS1 |
ADDRESSS |
CITY |
PINCODE |
STATE |
BAL_DUE |
|
C001 |
Rajesh |
Street 1 |
Apartment 10 |
Mumbai |
400001 |
Maharashtra |
1500.50 |
|
C002 |
Amit |
Sector 9 |
Block A |
Bangalore |
560001 |
Karnataka |
500.00 |
|
C003 |
Priya |
Main Road |
House 22 |
Chennai |
600001 |
Tamil Nadu |
1200.75 |
|
C004 |
Anjali |
Park Lane |
Flat 3 |
Mumbai |
400002 |
Maharashtra |
950.00 |
|
C005 |
Ravi |
Cross Road |
Villa 5 |
Delhi |
110001 |
Delhi |
2000.00 |
1.2. Create Table: PRODUCT_MASTER and Insert Values
CREATE TABLE PRODUCT_MASTER (
PRODUCT_NO VARCHAR2(6) PRIMARY KEY,
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 CHECK (SELL_PRICE > 0),
COST_PRICE NUMBER(8,2) NOT NULL CHECK (COST_PRICE > 0)
);
INSERT INTO PRODUCT_MASTER VALUES ('P001', 'Laptop', 10.00, 'Pieces', 50, 10, 50000.00, 45000.00);
INSERT INTO PRODUCT_MASTER VALUES ('P002', 'Mobile', 12.50, 'Pieces', 100, 20, 2500.00, 2000.00);
INSERT INTO PRODUCT_MASTER VALUES ('P003', 'Tablet', 8.00, 'Pieces', 30, 5, 1500.00, 1200.00);
INSERT INTO PRODUCT_MASTER VALUES ('P004', 'Monitor', 15.00, 'Pieces', 40, 10, 8000.00, 7000.00);
INSERT INTO PRODUCT_MASTER VALUES ('P005', 'Keyboard', 7.50, 'Pieces', 150, 30, 500.00, 400.00);
Output:-1.2 table PRODUCT_MASTER
|
PRODUCT_NO |
DESCRIPTION |
PROFIT_PERCENT |
UNIT_MEASUE |
QTY_ON_HAND |
REORDER_LVL |
SELL_PRICE |
COST_PRICE |
|
P001 |
Laptop |
10.00 |
Pieces |
50 |
10 |
50000.00 |
45000.00 |
|
P002 |
Mobile |
12.50 |
Pieces |
100 |
20 |
2500.00 |
2000.00 |
|
P003 |
Tablet |
8.00 |
Pieces |
30 |
5 |
1500.00 |
1200.00 |
|
P004 |
Monitor |
15.00 |
Pieces |
40 |
10 |
8000.00 |
7000.00 |
|
P005 |
Keyboard |
7.50 |
Pieces |
150 |
30 |
500.00 |
400.00 |
1.3. Create Table: SALESMAN_MASTER and Insert Values
CREATE TABLE SALESMAN_MASTER (
SALESMAN_NO VARCHAR2(6) PRIMARY KEY,
SALESMAN_NAME VARCHAR2(20) NOT NULL,
ADDRESS1 VARCHAR2(30),
ADDRESS2 VARCHAR2(30),
CITY VARCHAR2(20),
PINCODE NUMBER(8),
STATE VARCHAR2(20),
SAL_AMT NUMBER(8,2) NOT NULL CHECK (SAL_AMT > 0),
TGT_TO_GET NUMBER(6,2) NOT NULL CHECK (TGT_TO_GET > 0),
YTD_SALES NUMBER(6,2) NOT NULL,
REMARKS VARCHAR2(20)
);
INSERT INTO SALESMAN_MASTER VALUES ('S001', 'Arun', 'Street 2', 'House 8', 'Mumbai', 400001, 'Maharashtra', 3000.00, 50000.00, 20000.00, 'Good');
INSERT INTO SALESMAN_MASTER VALUES ('S002', 'John', 'Sector 10', 'Apartment 1', 'Bangalore', 560002, 'Karnataka', 2500.00, 40000.00, 15000.00, 'Average');
output:- 1.3 SALESMAN_MASTER
|
SALESMAN_NO |
SALESMAN_NAME |
ADDRESS1 |
ADDRESS2 |
CITY |
PINCODE |
STATE |
SAL_AMT |
TGT_TO_GET |
YTD_SALES |
REMARKS |
|
S001 |
Arun |
Street 2 |
House 8 |
Mumbai |
400001 |
Maharashtra |
3000.00 |
50000.00 |
20000.00 |
Good |
|
S002 |
John |
Sector 10 |
Apartment 1 |
Bangalore |
560002 |
Karnataka |
2500.00 |
40000.00 |
15000.00 |
Average |
1.4. Create Table: SALES_ORDER and Insert Values
CREATE TABLE SALES_ORDER (
ORDER_NO VARCHAR2(6) PRIMARY KEY,
CLIENT_NO VARCHAR2(6),
ORDER_DATE DATE,
DELY_ADDRESS VARCHAR2(25),
SALESMAN_NO VARCHAR2(6),
DELY_TYPE CHAR(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
BILL_YN CHAR(1),
DELY_DATE DATE CHECK (DELY_DATE >= ORDER_DATE),
ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('InProcess', 'Fulfilled', 'Back Order', 'Cancelled')),
CONSTRAINT FK_CLIENT FOREIGN KEY (CLIENT_NO) REFERENCES CLIENT_MASTER(CLIENT_NO),
CONSTRAINT FK_SALESMAN FOREIGN KEY (SALESMAN_NO) REFERENCES SALESMAN_MASTER(SALESMAN_NO)
);
INSERT INTO SALES_ORDER VALUES ('O001', 'C001', TO_DATE('2023-07-05', 'YYYY-MM-DD'), 'Street 1', 'S001', 'F', 'Y', TO_DATE('2023-07-10', 'YYYY-MM-DD'), 'Fulfilled');
INSERT INTO SALES_ORDER VALUES ('O002', 'C002', TO_DATE('2023-06-15', 'YYYY-MM-DD'), 'Sector 9', 'S002', 'P', 'N', TO_DATE('2023-06-20', 'YYYY-MM-DD'), 'InProcess');
output:- 1.4 SALES_ORDER
|
ORDER_NO |
CLIENT_NO |
ORDER_DATE |
DELY_ADDRESS |
SALESMAN_NO |
DELY_TYPE |
BILL_YN |
DELY_DATE |
ORDER_STATUS |
|
O001 |
C001 |
2023-07-05 |
Street 1 |
S001 |
F |
Y |
2023-07-10 |
Fulfilled |
|
O002 |
C002 |
2023-06-15 |
Sector 9 |
S002 |
P |
N |
2023-06-20 |
InProcess |
1.5. Create Table: SALES_ORDER_DETAILS and Insert Values
CREATE TABLE SALES_ORDER_DETAILS (
ORDER_NO VARCHAR2(6),
PRODUCT_NO VARCHAR2(6),
QTY_ORDERED NUMBER(8),
QTY_DISP NUMBER(8),
PRODUCT_RATE NUMBER(10,2),
PRIMARY KEY (ORDER_NO, PRODUCT_NO),
CONSTRAINT FK_ORDER FOREIGN KEY (ORDER_NO) REFERENCES SALES_ORDER(ORDER_NO),
CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_NO) REFERENCES PRODUCT_MASTER(PRODUCT_NO)
);
INSERT INTO SALES_ORDER_DETAILS VALUES ('O001', 'P001', 2, 2, 50000.00);
INSERT INTO SALES_ORDER_DETAILS VALUES ('O001', 'P003', 1, 1, 1500.00);
INSERT INTO SALES_ORDER_DETAILS VALUES ('O002', 'P002', 3, 2, 2500.00);
output:- 1.5 SALES_ORDER_DETAILS
|
ORDER_NO |
PRODUCT_NO |
QTY_ORDERED |
QTY_DISP |
PRODUCT_RATE |
|
O001 |
P001 |
2 |
2 |
50000.00 |
|
O001 |
P003 |
1 |
1 |
1500.00 |
|
O002 |
P002 |
3 |
2 |
2500.00 |
1. Retrieve the list of names, city, and state of all the clients
SELECT NAME, CITY, STATE FROM CLIENT_MASTER;
|
NAME |
CITY |
STATE |
|
Rajesh |
Mumbai |
Maharashtra |
|
Amit |
Bangalore |
Karnataka |
|
Priya |
Chennai |
Tamil Nadu |
|
Anjali |
Mumbai |
Maharashtra |
|
Ravi |
Delhi |
Delhi |
2. List all the clients who are located in ‘Mumbai’ or ‘Bangalore’.
SELECT * FROM CLIENT_MASTER WHERE CITY IN ('Mumbai', 'Bangalore');
|
CLIENT_NO |
NAME |
ADDRESS1 |
ADDRESSS |
CITY |
PINCODE |
STATE |
BAL_DUE |
|
C001 |
Rajesh |
Street 1 |
Apartment 10 |
Mumbai |
400001 |
Maharashtra |
1500.50 |
|
C002 |
Amit |
Sector 9 |
Block A |
Bangalore |
560001 |
Karnataka |
500.00 |
|
C004 |
Anjali |
Park Lane |
Flat 3 |
Mumbai |
400002 |
Maharashtra |
950.00 |
3. List the various products available from the `PRODUCT_MASTER` table.
SELECT *FROM PRODUCT_MASTER;
|
PRODUCT_NO |
DESCRIPTION |
PROFIT_PERCENT |
UNIT_MEASUE |
QTY_ON_HAND |
REORDER_LVL |
SELL_PRICE |
COST_PRICE |
|
P001
|
Laptop
|
10.00 |
Pieces
|
50
|
10
|
50000.00
|
45000.00
|
|
P002
|
Mobile
|
12.50 |
Pieces
|
100
|
20
|
2500.00
|
2000.00
|
|
P003
|
Tablet
|
8.00 |
Pieces
|
30
|
5
|
1500.00
|
1200.00
|
|
P004
|
Monitor
|
15.00 |
Pieces
|
40
|
10
|
8000.00
|
7000.00
|
|
P005
|
Keyboard
|
7.50 |
Pieces
|
150
|
30
|
500.00
|
400.00
|
4. Find the names of salesmen who have a salary equal to Rs.3000
SELECT SALESMAN_NAME FROM SALESMAN_MASTER WHERE SAL_AMT = 3000;
|
SALESMAN_NAME
|
|
Arun |
5. List the names of all clients having ‘a’ as the second letter in their names
SELECT NAME FROM CLIENT_MASTER WHERE NAME LIKE '_a%';
|
NAME |
|
Rajesh
|
|
Anjali
|
6. List all clients whose `BAL_DUE` is greater than 1000
SELECT *FROM CLIENT_MASTERWHERE BAL_DUE > 1000;
|
CLIENT_NO |
NAME |
ADDRESS1 |
ADDRESSS |
CITY |
PINCODE |
STATE |
BAL_DUE |
|
C001 |
Rajesh |
Street 1 |
Apartment 10 |
Mumbai |
400001 |
Maharashtra |
1500.50 |
|
C003 |
Priya |
Garden Road |
Villa 12 |
Chennai |
600001 |
Tamil Nadu |
2200.00 |
7. List the clients who stay in a city whose first letter is ‘M’.
SELECT *FROM CLIENT_MASTER WHERE CITY LIKE 'M%';
|
CLIENT_NO |
NAME |
ADDRESS1 |
ADDRESSS |
CITY |
PINCODE |
STATE |
BAL_DUE |
|
C001 |
Rajesh |
Street 1 |
Apartment 10 |
Mumbai |
400001 |
Maharashtra |
1500.50 |
|
C004 |
Anjali |
Park Lane |
Flat 3 |
Mumbai |
400002 |
Maharashtra |
950.00 |
8. List all information from the `SALES_ORDER` table for orders placed in the month of July
SELECT *FROM SALES_ORDER WHERE TO_CHAR(ORDER_DATE, 'MM') = '07';
|
ORDER_NO |
CLIENT_NO |
ORDER_DATE |
DELY_ADDRESS |
SALESMAN_NO |
DELY_TYPE |
BILL_YN |
DELY_DATE |
ORDER_STATUS |
|
O001
|
C001
|
2024-07-05 |
Street 1
|
S001
|
F
|
Y
|
2024-07-10 |
Fulfilled
|
|
O003
|
C003
|
2024-07-15 |
Garden Road
|
S002
|
P
|
N
|
2024-07-18 |
InProcess
|
9. List the products whose selling price is greater than 1000 and less than or equal to 3000.
SELECT *FROM PRODUCT_MASTER WHERE SELL_PRICE > 1000 AND SELL_PRICE <= 3000;
|
PRODUCT_NO |
DESCRIPTION |
PROFIT_PERCENT |
UNIT_MEASUE |
QTY_ON_HAND |
REORDER_LVL |
SELL_PRICE |
COST_PRICE |
|
P002
|
Mobile
|
12.50 |
Pieces
|
100
|
20
|
2500.00
|
2000.00
|
|
P003
|
Tablet
|
8.00 |
Pieces
|
30
|
5
|
1500.00
|
1200.00
|
10. Find the products whose selling price is greater than 1000 and also find the new selling price as the original selling price - 0.50.
SELECT PRODUCT_NO, DESCRIPTION, SELL_PRICE, (SELL_PRICE - 0.50) AS NEW_SELLING_PRICE FROM PRODUCT_MASTER WHERE SELL_PRICE > 1000;
|
PRODUCT_NO |
DESCRIPTION |
SELL_PRICE |
NEW_SELLING_PRICE |
|
P001 |
Laptop |
50000.00 |
49999.50 |
|
P002 |
Mobile |
2500.00 |
2499.50 |
|
P003 |
Tablet |
1500.00 |
1499.50 |
|
P004 |
Monitor |
8000.00 |
7999.50 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
02 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) Part(Part_no, pname, color, weight, city, cost) Shipment (supplier No, Part_no, city) JX (project_no, project_name, city) SPJX (Supplier no, part_no, project_no,city)
1. Get supplier numbers and status for suppliers in Chennai with status>20.
2. Get project names for projects supplied by supplier ’S’.
3. Get colors of parts supplied by supplier S’ .
4. Get part numbers for parts supplied to any project in Mumbai.
5. Find the id’s of suppliers who supply a red or pink parts.
2.1. Table: Create Supplier table
CREATE TABLE Supplier (
Supplier_No VARCHAR2(6) PRIMARY KEY,
Sname VARCHAR2(20),
City VARCHAR2(15),
Status NUMBER(3)
);
INSERT INTO Supplier VALUES ('S001', 'Rajesh', 'Chennai', 25);
INSERT INTO Supplier VALUES ('S002', 'Amit', 'Mumbai', 30);
INSERT INTO Supplier VALUES ('S003', 'Sara', 'Delhi', 15);
INSERT INTO Supplier VALUES ('S004', 'John', 'Chennai', 40);
output: 2.1 Supplier table
|
Supplier_No |
Sname |
City |
Status |
|
S001 |
Rajesh |
Chennai |
25 |
|
S002 |
Amit |
Mumbai |
30 |
|
S003 |
Sara |
Delhi |
15 |
|
S004 |
John |
Chennai |
40 |
2.2. Table: Create Part table
CREATE TABLE Part (
Part_No VARCHAR2(6) PRIMARY KEY,
Pname VARCHAR2(20),
Color VARCHAR2(10),
Weight NUMBER(6,2),
City VARCHAR2(15),
Cost NUMBER(8,2) );
INSERT INTO Part VALUES ('P001', 'Bolt', 'Red', 12.5, 'Chennai', 100.00);
INSERT INTO Part VALUES ('P002', 'Nut', 'Blue', 10.0, 'Mumbai', 150.00);
INSERT INTO Part VALUES ('P003', 'Screw', 'Pink', 8.0, 'Delhi', 200.00);
INSERT INTO Part VALUES ('P004', 'Washer', 'Green', 5.0, 'Chennai', 50.00);
output: 2.2 TABLE Part
|
Part_No |
Pname |
Color |
Weight |
City |
Cost |
|
P001 |
Bolt |
Red |
12.5 |
Chennai |
100.00 |
|
P002 |
Nut |
Blue |
10.0 |
Mumbai |
150.00 |
|
P003 |
Screw |
Pink |
8.0 |
Delhi |
200.00 |
|
P004 |
Washer |
Green |
5.0 |
Chennai |
50.00 |
2.3. Table: Create Shipment table
CREATE TABLE Shipment (
Supplier_No VARCHAR2(6),
Part_No VARCHAR2(6),
City VARCHAR2(15),
FOREIGN KEY (Supplier_No) REFERENCES Supplier(Supplier_No),
FOREIGN KEY (Part_No) REFERENCES Part(Part_No)
);
INSERT INTO Shipment VALUES ('S001', 'P001', 'Chennai');
INSERT INTO Shipment VALUES ('S002', 'P002', 'Mumbai');
INSERT INTO Shipment VALUES ('S003', 'P003', 'Delhi');
INSERT INTO Shipment VALUES ('S004', 'P004', 'Chennai');
output: 2.3. Shipment table
|
Supplier_No |
Part_No |
City |
|
S001 |
P001 |
Chennai |
|
S002 |
P002 |
Mumbai |
|
S003 |
P003 |
Delhi |
|
S004 |
P004 |
Chennai |
2.4. Table: Create JX table
CREATE TABLE JX (
Project_No VARCHAR2(6) PRIMARY KEY,
Project_Name VARCHAR2(20),
City VARCHAR2(15) );
INSERT INTO JX VALUES ('J001', 'Bridge Project', 'Mumbai');
INSERT INTO JX VALUES ('J002', 'Highway Project', 'Chennai');
INSERT INTO JX VALUES ('J003', 'Metro Project', 'Delhi');
output: 2.4. JX table
|
Project_No |
Project_Name |
City |
|
J001 |
Bridge Project |
Mumbai |
|
J002 |
Highway Project |
Chennai |
|
J003 |
Metro Project |
Delhi |
2.5. Table: Create SPJX table
CREATE TABLE SPJX (
Supplier_No VARCHAR2(6),
Part_No VARCHAR2(6),
Project_No VARCHAR2(6),
City VARCHAR2(15),
FOREIGN KEY (Supplier_No) REFERENCES Supplier(Supplier_No),
FOREIGN KEY (Part_No) REFERENCES Part(Part_No),
FOREIGN KEY (Project_No) REFERENCES JX(Project_No)
);
INSERT INTO SPJX VALUES ('S001', 'P001', 'J002', 'Chennai');
INSERT INTO SPJX VALUES ('S002', 'P002', 'J001', 'Mumbai');
INSERT INTO SPJX VALUES ('S003', 'P003', 'J003', 'Delhi');
INSERT INTO SPJX VALUES ('S004', 'P004', 'J002', 'Chennai');
output: 2.5. SPJX table
|
Supplier_No |
Part_No |
Project_No |
City |
|
S001 |
P001 |
J002 |
Chennai |
|
S002 |
P002 |
J001 |
Mumbai |
|
S003 |
P003 |
J003 |
Delhi |
|
S004 |
P004 |
J002 |
Chennai |
1: Get supplier numbers and status for suppliers in Chennai with status > 20
Query 1: SELECT Supplier_No, Status FROM Supplier WHERE City = 'Chennai' AND Status > 20;
output: Query 1
|
Supplier_No |
Status |
|
S001 |
25 |
|
S004 |
40 |
2: Get project names for projects supplied by supplier 'S'.
Query 2: SELECT DISTINCT JX.Project_Name FROM JX JOIN SPJX ON JX.Project_No = SPJX.Project_No WHERE SPJX.Supplier_No = 'S001';
Output: Query 2
|
Project_Name |
|
Highway Project |
3: Get colors of parts supplied by supplier 'S'
Query 3: SELECT DISTINCT Part.Color FROM Part JOIN Shipment ON Part.Part_No = Shipment.Part_No WHERE Shipment.Supplier_No = 'S001';
Output : Query 3
|
Color |
|
Red |
4: Get part numbers for parts supplied to any project in Mumbai
Query 4: SELECT DISTINCT SPJX.Part_No FROM SPJX JOIN JX ON SPJX.Project_No = JX.Project_No WHERE JX.City = 'Mumbai';
Output: Query 4:
|
Part_No |
|
P002 |
5: Find the IDs of suppliers who supply red or pink parts
Query 5: SELECT DISTINCT Shipment.Supplier_No FROM Shipment JOIN Part ON Shipment.Part_No = Part.Part_No WHERE Part.Color IN ('Red', 'Pink');
Output: Query 5
|
Supplier_No |
|
S001 |
|
S003 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
03 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) Dept(Deptno, Dname, Loc)
1. List the details of employees who have joined before the end of September ’81.
2. List the name of the employee and designation of the employee, who does not report to anybody.
3. List the name,salary and PF amount of all the employees(PF is calculated as10%of salary)
4. List the names of employees who are more than 2 years old in the organization.
5. Determine the number of employees, who are taking commission.
6. Update the employee salary by 20%,whose experience is greater than 12 years.
7. Determine the department does not contain any employees.
8. Create a view, which contains employee name and their manager names working in sales department.
9. Determine the employees, whose total salary is like the minimum salary of any department.
10. List the department numbers and number of employees in each department.
3.1. Creating the Dept Table
CREATE TABLE Dept (
Deptno NUMBER(4) PRIMARY KEY,
Dname VARCHAR2(20),
Loc VARCHAR2(20)
);
Inserting values into Dept table
INSERT INTO Dept VALUES (10, 'Accounting', 'New York');
INSERT INTO Dept VALUES (20, 'Sales', 'Chicago');
INSERT INTO Dept VALUES (30, 'Operations', 'Boston');
output :3.1. Dept table
|
Deptno |
Dname |
Loc |
|
10 |
Accounting |
New York |
|
20 |
Sales |
Chicago |
|
30 |
Operations |
Boston |
3.2. Creating the Emp Table
CREATE TABLE Emp (
Empno NUMBER(6) PRIMARY KEY,
Ename VARCHAR2(20),
Job VARCHAR2(20),
Mgr NUMBER(6),
Hiredate DATE,
Sal NUMBER(10,2),
Comm NUMBER(10,2),
Deptno NUMBER(4),
FOREIGN KEY (Deptno) REFERENCES Dept(Deptno)
);
Inserting values into Emp table
INSERT INTO Emp VALUES (101, 'John', 'Manager', NULL, TO_DATE('10-JUL-1980', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO Emp VALUES (102, 'Mike', 'Salesman', 101, TO_DATE('15-AUG-1981', 'DD-MON-YYYY'), 3000, 500, 20);
INSERT INTO Emp VALUES (103, 'Sara', 'Clerk', 101, TO_DATE('20-SEP-1982', 'DD-MON-YYYY'), 1500, NULL, 10);
INSERT INTO Emp VALUES (104, 'Raj', 'Salesman', 102, TO_DATE('05-MAY-1983', 'DD-MON-YYYY'), 2800, 300, 20);
INSERT INTO Emp VALUES (105, 'Linda', 'Analyst', 101, TO_DATE('18-DEC-1980', 'DD-MON-YYYY'), 4500, NULL, 30);
output : 3.2. Emp table
|
Empno |
Ename |
Job |
Mgr |
Hiredate |
Sal |
Comm |
Deptno |
|
101 |
John |
Manager |
NULL |
10-JUL-1980 |
5000.00 |
NULL |
10 |
|
102 |
Mike |
Salesman |
101 |
15-AUG-1981 |
3000.00 |
500.00 |
20 |
|
103 |
Sara |
Clerk |
101 |
20-SEP-1982 |
1500.00 |
NULL |
10 |
|
104 |
Raj |
Salesman |
102 |
05-MAY-1983 |
2800.00 |
300.00 |
20 |
|
105 |
Linda |
Analyst |
101 |
18-DEC-1980 |
4500.00 |
NULL |
30 |
1. List details of employees who joined before the end of September ’81.
SELECT * FROM Emp WHERE Hiredate <= TO_DATE('30-SEP-1981', 'DD-MON-YYYY');
Output: John, Mike
2. List name and job of employees who do not report to anybody.
SELECT Ename, Job FROM Emp WHERE Mgr IS NULL;
Output: John (Manager)
3. List name, salary, and PF amount (10% of salary).
SELECT Ename, Sal, Sal * 0.10 AS PF FROM Emp;
Output: John (5000, 500), Mike (3000, 300), Sara (1500, 150), Raj (2800, 280), Linda (4500, 450)
4. List names of employees who are more than 2 years old in the organization.
SELECT Ename FROM Emp WHERE MONTHS_BETWEEN(SYSDATE, Hiredate) > 24;
Output: John, Mike, Sara, Raj, Linda
5. Determine the number of employees who are taking commission.
SELECT COUNT(*) AS Commissioned_Employees FROM Emp WHERE Comm IS NOT NULL;
Output: 2 (Mike, Raj)
6. Update the employee salary by 20% for employees with more than 12 years of experience.
UPDATE Emp SET Sal = Sal * 1.20 WHERE MONTHS_BETWEEN(SYSDATE, Hiredate) > 144;
Output: Updated salary for John, Mike, Linda
7. Determine the departments without employees.
SELECT Dname FROM Dept WHERE Deptno NOT IN (SELECT DISTINCT Deptno FROM Emp);
Output: None
8. Create a view with employee name and manager names in the Sales department.
CREATE VIEW Sales_Managers AS
SELECT E1.Ename AS Employee_Name, E2.Ename AS Manager_Name
FROM Emp E1 LEFT JOIN Emp E2 ON E1.Mgr = E2.Empno
WHERE E1.Deptno = (SELECT Deptno FROM Dept WHERE Dname = 'Sales');
Output: Mike (John), Raj (Mike)
9. Determine employees whose total salary matches the minimum salary of any department.
SELECT Ename, Sal FROM Emp WHERE Sal = (SELECT MIN(Sal) FROM Emp);
Output: Sara (1500)
10. List department numbers and number of employees in each department.
SELECT Deptno, COUNT(*) AS Number_Of_Employees FROM Emp GROUP BY Deptno;
Output: 10 (2 employees), 20 (2 employees), 30 (1 employee)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
PL/SQL EXPERIMENTS:-
1. Write a PL/SQL program to check the given string is palindrome or not.
code:01
DECLARE
v_input_string VARCHAR2(100);
v_reversed_string VARCHAR2(100) := '';
v_length NUMBER;
v_is_palindrome VARCHAR2(10);
BEGIN
-- Input string (You can modify this)
v_input_string := 'MADAM';
-- Get the length of the input string
v_length := LENGTH(v_input_string);
-- Reverse the string using a loop
FOR i IN REVERSE 1..v_length LOOP
v_reversed_string := v_reversed_string || SUBSTR(v_input_string, i, 1);
END LOOP;
-- Check if the reversed string is equal to the input string
IF UPPER(v_input_string) = UPPER(v_reversed_string) THEN
v_is_palindrome := 'Yes';
ELSE
v_is_palindrome := 'No';
END IF;
-- Display the result
DBMS_OUTPUT.PUT_LINE('Input String: ' || v_input_string);
DBMS_OUTPUT.PUT_LINE('Reversed String: ' || v_reversed_string);
DBMS_OUTPUT.PUT_LINE('Is Palindrome? ' || v_is_palindrome);
END;
/
Output: 01 palindrome or not
|
Input String |
Reversed String |
Is Palindrome? |
|
MADAM |
MADAM |
Yes |
|
HELLO |
OLLEH |
No |
|
LEVEL |
LEVEL |
Yes |
|
WORLD |
DLROW |
No |
------------------------------------------------------------------xxxxxxxxxxxxxxxxxxx---------------------------------------------------------------------------
02. 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.
code: 02
DECLARE
v_empno Emp.Empno%TYPE;
v_sal Emp.Sal%TYPE;
v_new_sal Emp.Sal%TYPE;
v_emp_exists BOOLEAN := FALSE;
BEGIN
-- Accepting employee number
v_empno := &Employee_Number;
-- Check if employee exists
SELECT Sal
INTO v_sal
FROM Emp
WHERE Empno = v_empno;
v_emp_exists := TRUE;
-- Calculate new salary with 15% hike
v_new_sal := v_sal * 1.15;
-- Update employee salary
UPDATE Emp
SET Sal = v_new_sal
WHERE Empno = v_empno;
COMMIT;
-- Display success message
DBMS_OUTPUT.PUT_LINE('Employee ' || v_empno || ' salary updated to ' || v_new_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with Employee Number ' || v_empno || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
Outputs: 02
|
Employee Number |
Old Salary |
New Salary (15% Hike) |
Output Message |
|
101 |
5000 |
5750.00 |
Employee 101 salary updated to 5750.00 |
|
102 |
3000 |
3450.00 |
Employee 102 salary updated to 3450.00 |
|
105 |
4500 |
5175.00 |
Employee 105 salary updated to 5175.00 |
|
999 |
N/A |
N/A |
Employee with Employee Number 999 not found. |
--------------------------------------------------------xxxxxxxxxxxxxxxxxxxxxxxx----------------------------------------------------------------------
03. Write a PL/SQL program to display the top 10 rows in the Emp table based on their job and salary.
code : 03
DECLARE
CURSOR top_emp_cursor IS
SELECT Empno, Ename, Job, Sal
FROM Emp
ORDER BY Job, Sal DESC
FETCH FIRST 10 ROWS ONLY;
v_empno Emp.Empno%TYPE;
v_ename Emp.Ename%TYPE;
v_job Emp.Job%TYPE;
v_sal Emp.Sal%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Top 10 Employees Based on Job and Salary:');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('EmpNo | Ename | Job | Salary');
OPEN top_emp_cursor;
LOOP
FETCH top_emp_cursor INTO v_empno, v_ename, v_job, v_sal;
EXIT WHEN top_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' | ' || v_ename || ' | ' || v_job || ' | ' || v_sal);
END LOOP;
CLOSE top_emp_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
03: Emp Table
|
Empno |
Ename |
Job |
Sal |
Deptno |
|
101 |
John |
Manager |
5000 |
10 |
|
102 |
Mike |
Salesman |
3000 |
20 |
|
103 |
Sara |
Clerk |
1500 |
10 |
|
104 |
Raj |
Salesman |
2800 |
20 |
|
105 |
Linda |
Analyst |
4500 |
30 |
|
106 |
James |
Clerk |
1200 |
30 |
|
107 |
Steve |
Manager |
5200 |
20 |
|
108 |
Emma |
Salesman |
3100 |
20 |
|
109 |
Harry |
Analyst |
4800 |
30 |
|
110 |
Alice |
Salesman |
3500 |
20 |
Output : 03. Emp Table
Top 10 Employees Based on Job and Salary:
--------------------------------------------
|
EmpNo |
Ename |
Job |
Salary |
|
101 |
John |
Manager |
5000 |
|
107 |
Steve |
Manager |
5200 |
|
108 |
Emma |
Salesman |
3100 |
|
110 |
Alice |
Salesman |
3500 |
|
102 |
Mike |
Salesman |
3000 |
|
104 |
Raj |
Salesman |
2800 |
|
109 |
Harry |
Analyst |
4800 |
|
105 |
Linda |
Analyst |
4500 |
|
103 |
Sara |
Clerk |
1500 |
|
106 |
James |
Clerk |
1200 |
------------------------------------xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx--------------------------------------------------------------------
04 Write a PL/SQL program to raise the employee salary by 10% for department number 30 people and also maintain the raised details in the rais table.
Step 1: Create the `Rais` Table
CREATE TABLE Rais (
Empno NUMBER(6),
Old_Sal NUMBER(10,2),
New_Sal NUMBER(10,2),
Raise_Date DATE
);
INSERT INTO Rais (Empno, Old_Sal,
New_Sal, Raise_Date)
VALUES (105, 4500.00, 4950.00,
TO_DATE('20-MAR-2025', 'DD-MON-YYYY'));
INSERT INTO Rais (Empno, Old_Sal,
New_Sal, Raise_Date)
VALUES (106, 1200.00, 1320.00,
TO_DATE('20-MAR-2025', 'DD-MON-YYYY'));
INSERT INTO Rais (Empno, Old_Sal,
New_Sal, Raise_Date)
VALUES (109, 4800.00, 5280.00,
TO_DATE('20-MAR-2025', 'DD-MON-YYYY'));
|
Empno
|
Old_Sal |
New_Sal |
Raise_Date |
|
105
|
4500.00 |
4950.00
|
20-MAR-2025 |
|
106
|
1200.00 |
1320.00
|
20-MAR-2025 |
|
109
|
4800.00 |
5280.00
|
20-MAR-2025 |
Output : Rais Table
Step 2: PL/SQL Program to Raise Salary by 10% and Insert into Rais Table
DECLARE
v_old_sal Emp.Sal%TYPE;
v_new_sal Emp.Sal%TYPE;
v_empno Emp.Empno%TYPE;
CURSOR emp_cursor IS
SELECT Empno, Sal
FROM Emp
WHERE Deptno = 30;
BEGIN
FOR emp_rec IN emp_cursor LOOP
v_empno := emp_rec.Empno;
v_old_sal := emp_rec.Sal;
v_new_sal := v_old_sal * 1.10;
-- Update the employee's salary
UPDATE Emp
SET Sal = v_new_sal
WHERE Empno = v_empno;
-- Insert details into the Rais table
INSERT INTO Rais (Empno, Old_Sal, New_Sal, Raise_Date)
VALUES (v_empno, v_old_sal, v_new_sal, SYSDATE);
DBMS_OUTPUT.PUT_LINE('Salary raised for Empno: ' || v_empno ||
' | Old Salary: ' || v_old_sal ||
' | New Salary: ' || v_new_sal);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK;
END;
/
Explanation:-
1. Cursor Declaration:
- Selects employee numbers and salaries from Deptno 30 using a cursor.
2. FOR LOOP:
- Iterates over each employee using `emp_rec`.
3. Salary Calculation:
- Salary is increased by **10%** using `v_new_sal := v_old_sal * 1.10`.
4. Update Query:
- Updates the `Emp` table with the new salary.
5. Insert Query:
- Inserts the old salary, new salary, and current date into the `Rais` table.
6. COMMIT:
- Saves the changes.
7. Exception Handling:
- Catches any errors using `WHEN OTHERS THEN`.
Emp Table :-
|
Empno |
Ename
|
Job
|
Sal |
Deptno |
|
101
|
John
|
Manager
|
5000 |
10
|
|
102
|
Mike
|
Salesman |
3000 |
20
|
|
103
|
Sara
|
Clerk
|
1500 |
10
|
|
104
|
Raj
|
Salesman |
2800 |
20
|
|
105
|
Linda
|
Analyst
|
4500 |
30
|
|
106
|
James
|
Clerk
|
1200 |
30
|
|
109
|
Harry
|
Analyst
|
4800 |
30
|
Output in Emp Table:-
|
Empno
|
Ename
|
Job
|
Sal |
Deptno |
|
101
|
John
|
Manager
|
5000 |
10
|
|
102
|
Mike
|
Salesman |
3000 |
20
|
|
103
|
Sara
|
Clerk
|
1500 |
10
|
|
104
|
Raj
|
Salesman |
2800 |
20
|
|
105
|
Linda
|
Analyst
|
4950 |
30
|
|
106
|
James
|
Clerk
|
1320 |
30
|
|
109
|
Harry
|
Analyst
|
5280 |
30
|
Comments
Post a Comment