EXPERIMENT 1
Create a table EMPLOYEE with following schema: (Emp_no, E_name, E_address, E_ph_no, Dept_no,
Dept_name,Job_id , Salary)
Below are the SQL queries to perform the requested tasks based on the EMPLOYEE table schema:
1. Create the EMPLOYEE Table
CREATE TABLE EMPLOYEE (
Emp_no INT PRIMARY KEY, -- Employee number (unique identifier)
E_name VARCHAR(50) NOT NULL, -- Employee name
E_address VARCHAR(100), -- Employee address
E_ph_no VARCHAR(15), -- Employee phone number
Dept_no VARCHAR(10) NOT NULL, -- Department number
Dept_name VARCHAR(50), -- Department name
Job_id VARCHAR(20), -- Job ID
Salary DECIMAL(10, 2) NOT NULL -- Employee salary
);
2. Insert at Least 5 Rows
INSERT INTO EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name, Job_id,
Salary)
VALUES
(1, 'Alice', '123 Main St', '123-456-7890', 'D10', 'HR', 'HR01', 55000.00),
(2, 'Bob', '456 Elm St', '987-654-3210', 'D20', 'IT', 'IT02', 75000.00),
(3, 'Charlie', '789 Oak St', '555-666-7777', 'D30', 'Finance', 'FIN01', 65000.00),
(4, 'David', '101 Pine St', '444-555-6666', 'D10', 'HR', 'HR02', 60000.00),
(5, 'James', '202 Birch St', '333-222-1111', 'D40', 'Sales', 'SLS01', 70000.00);
3. Display All Information in the EMPLOYEE Table
SELECT * FROM EMPLOYEE;
4. Display the Record of Employees Who Work in Department D10
SELECT * FROM EMPLOYEE
WHERE Dept_no = 'D10';
5. Update the City of Emp_no 12 to Nagpur
If the E_address column stores the city, the query is:
UPDATE EMPLOYEE
SET E_address = 'Nagpur'
WHERE Emp_no = 12;
(Ensure there's an employee with Emp_no = 12 in the table for this query to take effect.)
6. Display the Details of Employees Who Work in the MECH Department
SELECT * FROM EMPLOYEE
WHERE Dept_name = 'MECH';
7. Delete the Email ID of Employee James
Assuming the E_address or another column holds the email, replace its value with NULL:
UPDATE EMPLOYEE
SET E_address = NULL
WHERE E_name = 'James';
8. Display the Complete Record of Employees Working in the SALES Department
SELECT * FROM EMPLOYEE
WHERE Dept_name = 'Sales';
Experiment No.2
Create a table EMPLOYEE with following schema:
(Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
ANS
1. Create the EMPLOYEE Table
CREATE TABLE EMPLOYEE (
Emp_no INT PRIMARY KEY, -- Employee number (unique identifier)
E_name VARCHAR(50) NOT NULL, -- Employee name
E_address VARCHAR(100), -- Employee address (can include city)
E_ph_no VARCHAR(15), -- Employee phone number
Dept_no VARCHAR(10) NOT NULL, -- Department number
Dept_name VARCHAR(50), -- Department name
Job_id VARCHAR(20), -- Job ID
Salary DECIMAL(10, 2) NOT NULL -- Employee salary
);
2. Insert at Least 5 Rows
INSERT INTO EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name, Job_id,
Salary)
VALUES
(1, 'Alice', 'Mumbai', '123-456-7890', 'D10', 'HR', 'HR01', 55000.00),
(2, 'Bob', 'Pune', '987-654-3210', 'D20', 'IT', 'IT02', 75000.00),
(3, 'Charlie', 'Delhi', '555-666-7777', 'D30', 'Finance', 'FIN01', 65000.00),
(4, 'David', 'Bangalore', '444-555-6666', 'D10', 'HR', 'HR02', 60000.00),
(5, 'James', 'Hyderabad', '333-222-1111', 'D40', 'Sales', 'SLS01', 70000.00);
3. Display All Information in the EMPLOYEE Table
SELECT * FROM EMPLOYEE;
4. Display the Record of Each Employee Who Works in Department D10
SELECT * FROM EMPLOYEE
WHERE Dept_no = 'D10';
5. Update the City of Emp_no 12 to Nagpur
If the city is stored in the E_address column:
UPDATE EMPLOYEE
SET E_address = 'Nagpur'
WHERE Emp_no = 12;
If there is no employee with Emp_no = 12, ensure you add this record first.
6. Display the Details of Employees Who Work in Department MECH
SELECT * FROM EMPLOYEE
WHERE Dept_name = 'MECH';
7. Delete the Email ID of Employee James
Assuming E_address contains the email or city, and you want to delete this information:
UPDATE EMPLOYEE
SET E_address = NULL
WHERE E_name = 'James';
8. Display the Complete Record of Employees Working in the SALES Department
SELECT * FROM EMPLOYEE
WHERE Dept_name = 'Sales';
Experiment No: 3
Create a table EMPLOYEE with following schema:
(Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id, Designation , Salary)
ANS
Below are the SQL statements for the described queries based on the provided schema:
1. List the Emp_no, E_name, and Salary of all employees working as MANAGER.
SELECT Emp_no, E_name, Salary
FROM EMPLOYEE
WHERE Designation = 'MANAGER';
2. Display all the details of employees whose salary is more than the salary of any IT PROFF.
SELECT *
FROM EMPLOYEE
WHERE Salary > ANY (
SELECT Salary
FROM EMPLOYEE
WHERE Designation = 'IT PROFF'
);
3. List the employees in ascending order of Designation who joined after 1981.
Assuming there is a Join_date column in EMPLOYEE:
SELECT *
FROM EMPLOYEE
WHERE YEAR(STR_TO_DATE(Join_date, '%d-%b-%y')) > 1981
ORDER BY Designation ASC;
4. List the employees along with their Experience and Daily Salary.
Assuming Join_date exists and Experience is calculated as the difference between the current year
and the year of joining:
SELECT
E_name,
YEAR(CURDATE()) - YEAR(STR_TO_DATE(Join_date, '%d-%b-%y')) AS Experience,
Salary / 30 AS Daily_Salary
FROM EMPLOYEE;
5. List the employees who are either CLERK or ANALYST.
SELECT *
FROM EMPLOYEE
WHERE Designation IN ('CLERK', 'ANALYST');
6. List the employees who joined on specific dates (1-MAY-81, 3-DEC-81, 17-DEC-81, 19-JAN-80).
SELECT *
FROM EMPLOYEE
WHERE STR_TO_DATE(Join_date, '%d-%b-%y') IN (
'1981-05-01', '1981-12-03', '1981-12-17', '1980-01-19'
);
7. List the employees working for Dept_no 10 or 20.
SELECT *
FROM EMPLOYEE
WHERE Dept_no IN (10, 20);
8. List the E_name values starting with S.
SELECT E_name
FROM EMPLOYEE
WHERE E_name LIKE 'S%';
9. Display the name and the first five characters of names starting with H.
SELECT E_name, LEFT(E_name, 5) AS First_Five_Chars
FROM EMPLOYEE
WHERE E_name LIKE 'H%';
10. List all employees except PRESIDENT and MGR in ascending order of Salary.
SELECT *
FROM EMPLOYEE
WHERE Designation NOT IN ('PRESIDENT', 'MGR')
ORDER BY Salary ASC;
Experiment No: 4
ANS:
1. Display all the department numbers available with the DEPT and EMP tables, avoiding
duplicates.
Use the UNION operator to combine the department numbers from both tables and remove
duplicates:
SELECT Dept_no
FROM DEPT
UNION
SELECT Dept_no
FROM EMP;
2. Display all the department numbers available with the DEPT and EMP tables (including
duplicates).
Use the UNION ALL operator to combine the department numbers from both tables, including
duplicates:
SELECT Dept_no
FROM DEPT
UNION ALL
SELECT Dept_no
FROM EMP;
3. Display all department numbers available in EMP but not in DEPT, and vice versa.
(a) Department numbers in EMP but not in DEPT:
SELECT Dept_no
FROM EMP
WHERE Dept_no NOT IN (
SELECT Dept_no
FROM DEPT
);
(b) Department numbers in DEPT but not in EMP:
SELECT Dept_no
FROM DEPT
WHERE Dept_no NOT IN (
SELECT Dept_no
FROM EMP
);
(c) Combine both results to show all mismatches:
Use FULL OUTER JOIN if supported, or simulate it with UNION.
-- Simulating FULL OUTER JOIN for mismatches
SELECT Dept_no, 'In EMP Not in DEPT' AS Source
FROM EMP
WHERE Dept_no NOT IN (
SELECT Dept_no
FROM DEPT
UNION
SELECT Dept_no, 'In DEPT Not in EMP' AS Source
FROM DEPT
WHERE Dept_no NOT IN (
SELECT Dept_no
FROM EMP
);
Experiment No: 5
Consider the following schema:
Sailors (sid, sname, rating, age) Boats (bid, bname, color) Reserves (sid, bid, day(date))
ANS
1. Find all information of sailors who have reserved boat number 101.
SELECT *
FROM Sailors S
WHERE S.sid IN (
SELECT R.sid
FROM Reserves R
WHERE R.bid = 101
);
2. Find the name of the boat reserved by Bob.
SELECT B.bname
FROM Boats B
JOIN Reserves R ON B.bid = R.bid
JOIN Sailors S ON S.sid = R.sid
WHERE S.sname = 'Bob';
3. Find the names of sailors who have reserved a red boat, and list in the order of age.
SELECT DISTINCT S.sname
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid
JOIN Boats B ON R.bid = B.bid
WHERE B.color = 'red'
ORDER BY S.age;
4. Find the names of sailors who have reserved at least one boat.
SELECT DISTINCT S.sname
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid;
5. Find the IDs and names of sailors who have reserved two different boats on the same day.
SELECT S.sid, S.sname
FROM Sailors S
JOIN Reserves R1 ON S.sid = R1.sid
JOIN Reserves R2 ON S.sid = R2.sid
WHERE R1.bid <> R2.bid
AND R1.day = R2.day;
6. Find the IDs of sailors who have reserved a red boat or a green boat.
SELECT DISTINCT R.sid
FROM Reserves R
JOIN Boats B ON R.bid = B.bid
WHERE B.color IN ('red', 'green');
7. Find the name and the age of the youngest sailor.
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (
SELECT MIN(S1.age)
FROM Sailors S1
);
8. Count the number of different sailor names.
SELECT COUNT(DISTINCT S.sname) AS Sailor_Name_Count
FROM Sailors S;
9. Find the average age of sailors for each rating level.
SELECT S.rating, AVG(S.age) AS Avg_Age
FROM Sailors S
GROUP BY S.rating;
10. Find the average age of sailors for each rating level that has at least two sailors.
SELECT S.rating, AVG(S.age) AS Avg_Age
FROM Sailors S
GROUP BY S.rating
HAVING COUNT(S.sid) >= 2;
EXPERIMENT 6:
Create a relation and implement the following queries.
1. Display the total salary spent for each job category.
SELECT Job, SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY Job;
2. Display the lowest-paid employee details under each manager.
SELECT Manager_id, MIN(Salary) AS Lowest_Salary
FROM Employee
GROUP BY Manager_id;
To include full details of the lowest-paid employee under each manager:
WITH MinSalaryPerManager AS (
SELECT Manager_id, MIN(Salary) AS Lowest_Salary
FROM Employee
GROUP BY Manager_id
SELECT E.*
FROM Employee E
JOIN MinSalaryPerManager M
ON E.Manager_id = M.Manager_id AND E.Salary = M.Lowest_Salary;
3. Display the number of employees working in each department and their department name.
SELECT D.Dept_name, COUNT(E.Emp_id) AS Employee_Count
FROM Employee E
JOIN Department D ON E.Dept_id = D.Dept_id
GROUP BY D.Dept_name;
4. Display the details of employees, sorting the salary in increasing order.
SELECT *
FROM Employee
ORDER BY Salary ASC;
5. Show the record of employees earning a salary greater than 16000 in each department.
SELECT E.*
FROM Employee E
JOIN Department D ON E.Dept_id = D.Dept_id
WHERE E.Salary > 16000;
6. Write queries to implement and practice the above clauses.
Example Queries:
GROUP BY: Query 1 (GROUP BY Job) and Query 3 (GROUP BY Dept_name) demonstrate
grouping for aggregate operations.
ORDER BY: Query 4 demonstrates sorting (ORDER BY Salary ASC).
HAVING: Add conditions on grouped results (e.g., departments with more than 5
employees):
SELECT D.Dept_name, COUNT(E.Emp_id) AS Employee_Count
FROM Employee E
JOIN Department D ON E.Dept_id = D.Dept_id
GROUP BY D.Dept_name
HAVING COUNT(E.Emp_id) > 5;
JOINS: Queries 3 and 5 use JOIN to combine Employee and Department tables.
Experiment No: 7
Consider the following schema:
Sailors (sid, sname, rating, age) Boats (bid, bname, color) Reserves (sid, bid, day(date))
ANS:
1. Find all information of sailors who have reserved boat number 101.
SELECT *
FROM Sailors
WHERE sid IN (
SELECT sid
FROM Reserves
WHERE bid = 101
);
2. Find the name of the boat reserved by Bob.
SELECT bname
FROM Boats
WHERE bid IN (
SELECT bid
FROM Reserves
WHERE sid = (
SELECT sid
FROM Sailors
WHERE sname = 'Bob'
)
);
3. Find the names of sailors who have reserved a red boat, and list in the order of age.
SELECT sname
FROM Sailors
WHERE sid IN (
SELECT sid
FROM Reserves
WHERE bid IN (
SELECT bid
FROM Boats
WHERE color = 'red'
ORDER BY age;
4. Find the names of sailors who have reserved at least one boat.
SELECT sname
FROM Sailors
WHERE sid IN (
SELECT DISTINCT sid
FROM Reserves
);
5. Find the IDs and names of sailors who have reserved two different boats on the same day.
SELECT sid, sname
FROM Sailors
WHERE sid IN (
SELECT R1.sid
FROM Reserves R1, Reserves R2
WHERE R1.sid = R2.sid
AND R1.bid <> R2.bid
AND R1.day = R2.day
);
6. Find the IDs of sailors who have reserved a red boat or a green boat.
SELECT DISTINCT sid
FROM Reserves
WHERE bid IN (
SELECT bid
FROM Boats
WHERE color IN ('red', 'green')
);
7. Find the name and the age of the youngest sailor.
SELECT sname, age
FROM Sailors
WHERE age = (
SELECT MIN(age)
FROM Sailors
);
8. Count the number of different sailor names.
SELECT COUNT(DISTINCT sname) AS Sailor_Name_Count
FROM Sailors;
9. Find the average age of sailors for each rating level.
SELECT rating, AVG(age) AS Avg_Age
FROM Sailors
GROUP BY rating;
10. Find the average age of sailors for each rating level that has at least two sailors.
SELECT rating, AVG(age) AS Avg_Age
FROM Sailors
GROUP BY rating
HAVING COUNT(sid) >= 2;
Experiment No: 8
1. Create a table EMP with the specified structure.
CREATE TABLE EMP (
EMPNO NUMBER(6),
ENAME VARCHAR2(20) NOT NULL,
JOB VARCHAR2(10) NOT NULL,
DEPTNO NUMBER(3),
SAL NUMBER(7, 2),
CONSTRAINT chk_empno CHECK (EMPNO > 100), -- Constraint for EMPNO > 100
CONSTRAINT uniq_deptno UNIQUE (DEPTNO), -- Constraint for unique DEPTNO
CONSTRAINT pk_emp PRIMARY KEY (EMPNO) -- Primary key constraint for EMPNO
);
2. Add constraint to ensure EMPNO > 100.
If the constraint was not added during table creation, you can add it afterward:
ALTER TABLE EMP
ADD CONSTRAINT chk_empno CHECK (EMPNO > 100);
3. Define DEPTNO as unique.
If the unique constraint was not added during table creation:
ALTER TABLE EMP
ADD CONSTRAINT uniq_deptno UNIQUE (DEPTNO);
4. Create a primary key constraint for the table (EMPNO).
If the primary key constraint was not added during table creation:
ALTER TABLE EMP
ADD CONSTRAINT pk_emp PRIMARY KEY (EMPNO);
5. Queries to Practice Constraints
a) Insert a valid record.
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (101, 'John', 'Manager', 10, 50000.00);
b) Try inserting a record with EMPNO <= 100.
This will fail because of the chk_empno constraint.
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (99, 'Jane', 'Clerk', 20, 20000.00);
c) Try inserting a record with duplicate DEPTNO.
This will fail because of the uniq_deptno constraint.
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (102, 'Alice', 'Analyst', 10, 30000.00);
d) Try inserting a record with NULL in ENAME or JOB.
This will fail because both columns are defined as NOT NULL.
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (103, NULL, 'Sales', 30, 25000.00);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (104, 'Bob', NULL, 40, 15000.00);
e) Insert a valid record with a new department.
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (105, 'Eve', 'Engineer', 50, 40000.00);
EXPERIMRNT 9.
1. Implementing a Savepoint
A Savepoint allows you to set a point within a transaction to which you can later roll back if needed.
Example Query:
-- Begin the transaction
BEGIN;
-- Insert a record into the EMP table
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (201, 'Alice', 'Clerk', 30, 20000.00);
-- Create a Savepoint
SAVEPOINT sp1;
-- Insert another record
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (202, 'Bob', 'Manager', 40, 50000.00);
2. Implementing a Rollback
A Rollback undoes all the changes made after a specific savepoint or the start of a transaction.
Example Query:
-- Rollback to the Savepoint sp1
ROLLBACK TO sp1;
-- At this point, the record for Bob will be undone, but the record for Alice remains.
If no savepoint is specified, the rollback undoes the entire transaction:
-- Rollback the entire transaction
ROLLBACK;
3. Implementing a Commit
A Commit makes all changes made in the current transaction permanent in the database.
Example Query:
-- Commit the transaction
COMMIT;
-- At this point, the record for Alice is permanently saved in the database.
Example: Combined Workflow
BEGIN;
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (301, 'John', 'Analyst', 50, 45000.00);
SAVEPOINT sp1;
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO, SAL)
VALUES (302, 'Jane', 'Engineer', 60, 55000.00);
-- Rollback to Savepoint
ROLLBACK TO sp1;
-- Commit the remaining transaction
COMMIT;
EXPERIMENT 10
1. Create a User
To create a new user:
CREATE USER new_user IDENTIFIED BY 'password';
To grant basic connection privileges to the user:
GRANT CONNECT TO new_user;
2. Grant All Privileges of Emp Table to Dept Table
To allow the Dept table to have all privileges over the Emp table:
GRANT ALL ON Emp TO new_user;
3. Grant Some Privileges of Emp Table to Dept Table
To grant specific privileges such as SELECT and INSERT:
GRANT SELECT, INSERT ON Emp TO new_user;
4. Revoke All Privileges of Emp Table from Dept Table
To revoke all previously granted privileges:
REVOKE ALL ON Emp FROM new_user;
5. Revoke Some Privileges of Emp Table from Dept Table
To revoke specific privileges like INSERT:
REVOKE INSERT ON Emp FROM new_user;
Practical Workflow Example
-- Create a user
CREATE USER department_user IDENTIFIED BY 'dept123';
-- Grant privileges
GRANT SELECT, INSERT ON Emp TO department_user;
-- Revoke specific privilege
REVOKE INSERT ON Emp FROM department_user;
-- Revoke all privileges
REVOKE ALL ON Emp FROM department_user;
