Department of Computer Science & Engineering

Lab Internal-II

Set-1

Sub:Database Management Systems Lab Total Marks:25

1.Consider Student and Student Course tables

(a)Display the names and age of students enrolled in different courses.

A;
SELECT s.Name, s.Age, sc.CourseID
FROM Student s
JOIN Student_Course sc ON s.RollNo = sc.RollNo;

b)List all the students records who have enrolled in same courses.

A:
SELECT s1.Name, s2.Name, sc.CourseID
FROM Student_Course sc
JOIN Student s1 ON sc.RollNo = s1.RollNo
JOIN Student s2 ON sc.RollNo = s2.RollNo
WHERE s1.RollNo < s2.RollNo AND sc.CourseID IN (
SELECT CourseID
FROM Student_Course
GROUP BY CourseID
HAVING COUNT(DISTINCT RollNo) > 1
);

c)Print the average age of the students.

A:
SELECT AVG(Age) AS AverageAge
FROM Student;

d)Print all the records alphabetically.

A:

SELECT RollNo, Name, Address, Phone, Age

FROM Student
ORDER BY Name;

e)Display course _id and names of the students.

A:
SELECT sc.CourseID, s.Name
FROM Student_Course sc
JOIN Student s ON sc.RollNo = s.RollNo;
2. Suppose that your company has a business rule that no employee is allowed to have a salary greater than $200,000 unless the
authorizing manager’s employee ID has been entered with the transaction. Assume that a table, named employees, contains these
columns:

Column
Data type
name
EmployeeID Int
Salary Smallmoney
HireDate Smalldatetime
ApprovalID Int

A:
CREATE TRIGGER CheckSalary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
DECLARE v_EmployeeID INT;
DECLARE v_Salary DECIMAL(10, 2);
DECLARE v_ApprovalID INT;

SET v_EmployeeID = NEW.EmployeeID;

SET v_Salary = NEW.Salary;
SET v_ApprovalID = NEW.ApprovalID;

IF v_Salary > 200000 AND v_ApprovalID IS NULL THEN

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot exceed $200,000 without manager approval.';
END IF;
END;

3.Bills Consider the following relational schema that manages the telephone bills of a mobile phone company.
CUSTOMER ( SSN, Name, Surname, PhoneNum, Plan)
PRICINGPLAN ( Code, ConnectionFee, PricePerSecond )
PHONECALL ( SSN, Date, Time, CalledNum, Seconds) BILL ( SSN, Month, Year, amount )
Write a trigger that after each phone call updates the customer's bill.

A:
CREATE TRIGGER UpdateBillAfterPhoneCall
AFTER INSERT ON PHONECALL
FOR EACH ROW
BEGIN
DECLARE v_ConnectionFee DECIMAL(10, 2);
DECLARE v_PricePerSecond DECIMAL(10, 2);
DECLARE v_Seconds INT;
DECLARE v_Amount DECIMAL(10, 2);
DECLARE v_Month INT;
DECLARE v_Year INT;

SELECT ConnectionFee, PricePerSecond

INTO v_ConnectionFee, v_PricePerSecond
FROM PRICINGPLAN
WHERE Code = (SELECT Plan FROM CUSTOMER WHERE SSN = NEW.SSN);

SET v_Seconds = NEW.Seconds;

SET v_Amount = v_ConnectionFee + (v_PricePerSecond * v_Seconds);
SET v_Month = MONTH(NEW.Date);
SET v_Year = YEAR(NEW.Date);

INSERT INTO BILL (SSN, Month, Year, amount)

VALUES (NEW.SSN, v_Month, v_Year, v_Amount);
 END;

Department of Computer Science & Engineering

Lab Internal-II
Set-2
Sub:Database Management Systems Lab Total Marks:25

1.create the following table with suitable constraints.

Empid EmpName Department ContactNo EmailId EmpHeadId

101 Isha E-101 1234567890 isha@gmail.com 105
102 Priya E-104 1234567890 priya@yahoo.com 103
103 Neha E-101 1234567890 neha@gmail.com 101
104 Rahul E-102 1234567890 rahul@yahoo.com 105
105 Abhishek E-101 1234567890 abhishek@gmail.com 102

• Select the detail of the employee whose name start with P.

•
• Select the detail of employee whose emailId is in gmail.
• Select the details of the employee who work either for department E-104 or E-102.
• What is the department name for DeptID E-102?
• select the name of the employee whose name's 3rd charactor is 'h'.

### Creating table for above data:

mysql>create table emp(empid int,empname varchar(200),department varchar(200),contact int,emailid varchar(200),emailheadid

varchar(200));

### Inserting data in to table:

mysql> insert into emp values(101,'Isha','E-101',1234567890,'isha@gmail.com','105'),(102,'Priya','E-

104',1234567890,'priya@yahoo.com','103'),(103,'Neha','E-101',1234567890,'neha@gmail.com','101'),(104,'Rahul','E-
102',1234567890,'rahul@yahoo.com','105'),(105,'Abhishek','E-101',1234567890,'abhishek@gmail.com','102');

⦁Select the detail of the employee whose name start with P.

A:
mysql> select * from emp where empname like"p%";
+-------+---------+------------+------------+-----------------+-------------+
| empid | empname | department | contact | emailid | emailheadid |
+-------+---------+------------+------------+-----------------+-------------+
| 102 | Priya | E-104 | 1234567890 | [priya@yahoo.com](mailto:priya@yahoo.com) | 103 |
+-------+---------+------------+------------+-----------------+-------------+

⦁Select the detail of employee whose emailId is in gmail.

A:
mysql> select * from emp where emailid like"%@gmail.com%";
+-------+----------+------------+------------+--------------------+-------------+
| empid | empname | department | contact | emailid | emailheadid |
+-------+----------+------------+------------+--------------------+-------------+
| 101 | Isha | E-101 | 1234567890 | [isha@gmail.com](mailto:isha@gmail.com) | 105 |
| 103 | Neha | E-101 | 1234567890 | [neha@gmail.com](mailto:neha@gmail.com) | 101 |
| 105 | Abhishek | E-101 | 1234567890 | [abhishek@gmail.com](mailto:abhishek@gmail.com) | 102 |
+-------+----------+------------+------------+--------------------+-------------+

⦁Select the details of the employee who work either for department E-104 or E-102.
A:
mysql> select * from emp where department in("E-104","E-102");
+-------+---------+------------+------------+-----------------+-------------+
| empid | empname | department | contact | emailid | emailheadid |
+-------+---------+------------+------------+-----------------+-------------+
| 102 | Priya | E-104 | 1234567890 | [priya@yahoo.com](mailto:priya@yahoo.com) | 103 |
| 104 | Rahul | E-102 | 1234567890 | [rahul@yahoo.com](mailto:rahul@yahoo.com) | 105 |
+-------+---------+------------+------------+-----------------+-------------+

⦁What is the department name for DeptID E-102?

A:
mysql> select empname from emp where department in("E-102");
+---------+
| empname |
+---------+
| Rahul |
+---------+

⦁select the name of the employee whose name's 3rd charactor is 'h'.
A:
mysql> SELECT empname
-> FROM emp
-> WHERE SUBSTRING(empname, 3, 1) = 'h';
+----------+
| empname |
+----------+
| Isha |
| Neha |
| Rahul |
| Abhishek |
+----------+
2.Update the missing data in the following table:

### Creating table:

mysql> CREATE TABLE resident ( roll_no INT PRIMARY KEY, name VARCHAR(255) NOT NULL, address
VARCHAR(255), phone VARCHAR(20), age INT);
### Inserintg values:
mysql> INSERT INTO resident (roll_no, name, address, phone, age)
-> VALUES
-> (1, 'Harish', 'Delhi', 'XXXXXXXXXX', 18),
-> (2, 'Prathik', 'Bihar', 'XXXXXXXXXX', 19),
-> (3, 'Riyanka', 'Siliguri', 'XXXXXXXXXX', 20),
-> (4, 'Deep', 'Ramnagar', 'XXXXXXXXXX', 18),
-> (5, 'Saptarhi', 'Kolkata', 'XXXXXXXXXX', 19),
-> (6, 'Dhanraj', 'Barabajar', 'XXXXXXXXXX', 20),
-> (7, 'Rohit', 'Balurghat', 'XXXXXXXXXX', 18),
-> (8, 'Niraj', 'Alipur', 'XXXXXXXXXX', 19);
mysql> update resident set phone=1234567890 where roll_no=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update resident set phone=1234567890 where roll_no=8;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from resident;
+---------+----------+-----------+------------+------+
| roll_no | name | address | phone | age |
+---------+----------+-----------+------------+------+
| 1 | Harish | Delhi | 1234567890 | 18 |
| 2 | Prathik | Bihar | 1234567890 | 19 |
| 3 | Riyanka | Siliguri | 1234567890 | 20 |
| 4 | Deep | Ramnagar | 1234567890 | 18 |
| 5 | Saptarhi | Kolkata | 1234567890 | 19 |
| 6 | Dhanraj | Barabajar | 1234567890 | 20 |
| 7 | Rohit | Balurghat | 1234567890 | 18 |
| 8 | Niraj | Alipur | 1234567890 | 19 |
+---------+----------+-----------+------------+------+
8 rows in set (0.00 sec)
3.Write a procedure to display the information about the people who are resident of Delhi.
DELIMITER //
CREATE PROCEDURE GetResidentsOfDelhi()
BEGIN
SELECT roll_no, name, address, phone, age
FROM resident
WHERE address LIKE '%Delhi%';
END //
DELIMITER ;
 Department of Computer Science & Engineering

Lab Internal-II

Set-3

Sub:Database Management Systems Lab Total Marks:25

1.Create a table with following specifications:

Hotel (Hotel_No, Name, Address)
Room (Room_No, Hotel_No, Type, Price)
Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No)
Guest (Guest_No, Name, Address)
• List full details of all hotels.
• List full details of all hotels in London.
• List the names and addresses of all guests in London, alphabetically ordered by name.
• List all double or family rooms with a price below £40.00 per night, in ascending order of price.
• List the bookings for which no date_to has been specified

CREATE TABLE Hotel (

Hotel_No INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Address VARCHAR(255)
);

CREATE TABLE Room (

Room_No INT PRIMARY KEY,
Hotel_No INT,
Type VARCHAR(50),
Price DECIMAL(10, 2),
FOREIGN KEY (Hotel_No) REFERENCES Hotel(Hotel_No)
);

CREATE TABLE Booking (

Hotel_No INT,
Guest_No INT,
Date_From DATE,
Date_To DATE,
Room_No INT,
FOREIGN KEY (Hotel_No) REFERENCES Hotel(Hotel_No),
FOREIGN KEY (Guest_No) REFERENCES Guest(Guest_No),
FOREIGN KEY (Room_No) REFERENCES Room(Room_No)
);

CREATE TABLE Guest (

Guest_No INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Address VARCHAR(255)
);

### Inserting values:

mysql> INSERT INTO Hotel (Hotel_No, Name, Address)

-> VALUES
-> (1, 'Grand Hotel', '123 Main Street, City'),
-> (2, 'Luxury Resort', '456 Beachfront Road, Resort Town');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Room (Room_No, Hotel_No, Type, Price)

-> VALUES
-> (101, 1, 'Standard', 150.00),
-> (102, 1, 'Deluxe', 250.00),
-> (201, 2, 'Suite', 400.00);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Guest (Guest_No, Name, Address)

-> VALUES
-> (1, 'John Smith', '789 Oak Avenue, Suburb'),
-> (2, 'Jane Doe', '567 Pine Street, Town');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No)

-> VALUES
-> (1, 1, '2023-08-10', '2023-08-15', 101),
-> (1, 2, '2023-09-05', '2023-09-10', 102),
-> (2, 1, '2023-08-20', '2023-08-25', 201);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

⦁List full details of all hotels.

A:
mysql> select * from hotel;
+----------+---------------+----------------------------------+
| Hotel_No | Name | Address |
+----------+---------------+----------------------------------+
| 1 | Grand Hotel | 123 Main Street, City |
| 2 | Luxury Resort | 456 Beachfront Road, Resort Town |
+----------+---------------+----------------------------------+
2 rows in set (0.00 sec)

⦁List full details of all hotels in 123 Main Street, City

A:
mysql> SELECT *
-> FROM Hotel
-> WHERE Address = '123 Main Street, City';
+----------+-------------+-----------------------+
| Hotel_No | Name | Address |
+----------+-------------+-----------------------+
| 1 | Grand Hotel | 123 Main Street, City |
+----------+-------------+-----------------------+
1 row in set (0.00 sec)

⦁List the names and addresses of all guests in 123 Main Street, City, alphabetically ordered by name.
A:
mysql> SELECT Name, Address
-> FROM Guest
-> WHERE Address = '123 Main Street, City'
-> ORDER BY Name;
Empty set (0.00 sec)

⦁List all double or family rooms with a price below £40.00 per night, in ascending order of price.
A:
mysql> SELECT *
-> FROM Room
-> WHERE Type IN ('Double', 'Family') AND Price < 40.00
-> ORDER BY Price ASC;
Empty set (0.00 sec)

⦁List the bookings for which no date_to has been specified

A:
mysql> SELECT *
-> FROM Booking
-> WHERE Date_To IS NULL;
Empty set (0.00 sec)

2. Customers:
Field Type Null Key Default Extra
CustomerID varchar(5) NO PRI NULL
CompanyName varchar(40) NO MUL NULL
ContactName varchar(30) YES NULL
ContactTitle varchar(30) YES NULL
Address varchar(60) YES NULL
City varchar(15) YES MUL NULL
Region varchar(15) YES MUL NULL
PostalCode varchar(10) YES MUL NULL
Country varchar(15) YES NULL
Phone varchar(24) YES NULL
Fax varchar(24) YES NULL

a.Write an SQL query to display CustomerID, CompnayName of Customers whose City is ‘Berlin@ or
‘London’
b. Write an SQL query to select all CustomerID, CompnayName and City from the "Customers" table in
the DESCENDING order by the "Country" column
c. Write an SQL query to display the contactname,title and city from the Customers table where city
name starts with 'Lo'.
d. Write an SQL query to display the customerid, name and city from the Customers table where city
starts with p and ends with d.
e. Write an SQL query to lists the number of customers and countryname in each country in descending
order where the number of customers are more than 5

### Creating table:

mysql> CREATE TABLE Customers5 (

-> CustomerID VARCHAR(5) NOT NULL PRIMARY KEY,
-> CompanyName VARCHAR(40) NOT NULL,
-> ContactName VARCHAR(30),
-> ContactTitle VARCHAR(30),
-> Address VARCHAR(60),
-> City VARCHAR(15),
-> Region VARCHAR(15),
-> PostalCode VARCHAR(10),
-> Country VARCHAR(15),
-> Phone VARCHAR(24),
-> Fax VARCHAR(24)
-> );
Query OK, 0 rows affected (0.02 sec)

### Inserting values:

mysql> INSERT INTO Customers5 (CustomerID, CompanyName, ContactName, City, Country, Phone)
-> VALUES
-> ('C001', 'Berlin Company', 'Max Mustermann', 'Berlin', 'Germany', '123-456-7890');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Customers5 (CustomerID, CompanyName, ContactName, City, Country, Phone)
-> VALUES
-> ('C002', 'London Ltd.', 'John Smith', 'London', 'United Kingdom', '987-654-3210');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Customers5 (CustomerID, CompanyName, ContactName, City, Country, Phone)
-> VALUES
-> ('C003', 'Paris Enterprises', 'Sophie Martin', 'Paris', 'France', '555-123-4567'),
-> ('C004', 'Rome Corporation', 'Luigi Rossi', 'Rome', 'Italy', '333-999-8888'),
-> ('C005', 'New York Industries', 'Emily Johnson', 'New York', 'USA', '111-222-3333');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

1.Write an SQL query to display CustomerID, CompnayName of Customers whose City is ‘Berlin@ or
‘London’
A:
mysql> SELECT CustomerID, CompanyName
-> FROM Customers5
-> WHERE City IN ('Berlin', 'London');
+------------+----------------+
| CustomerID | CompanyName |
+------------+----------------+
| C001 | Berlin Company |
| C002 | London Ltd. |
+------------+----------------+
2 rows in set (0.00 sec)

b. Write an SQL query to select all CustomerID, CompnayName and City from the "Customers" table in
the DESCENDING order by the "Country" column
A:
mysql> SELECT CustomerID, CompanyName, City
-> FROM Customers5
-> ORDER BY Country DESC;
+------------+---------------------+----------+
| CustomerID | CompanyName | City |
+------------+---------------------+----------+
| C005 | New York Industries | New York |
| C002 | London Ltd. | London |
| C004 | Rome Corporation | Rome |
| C001 | Berlin Company | Berlin |
| C003 | Paris Enterprises | Paris |
+------------+---------------------+----------+
5 rows in set (0.00 sec)

c. Write an SQL query to display the contactname,title and city from the Customers table where city
name starts with 'Lo'.
A:
mysql> SELECT ContactName, ContactTitle, City
-> FROM Customers5
-> WHERE City LIKE 'Lo%';
+-------------+--------------+--------+
| ContactName | ContactTitle | City |
+-------------+--------------+--------+
| John Smith | NULL | London |
+-------------+--------------+--------+
1 row in set (0.00 sec)

d. Write an SQL query to display the customerid, name and city from the Customers table where city
starts with p and ends with d.
A:
mysql> SELECT CustomerID, CompanyName, City
-> FROM Customers5
-> WHERE City LIKE 'p%d';
Empty set (0.00 sec)

e. Write an SQL query to lists the number of customers and countryname in each country in descending
order where the number of customers are more than 5
A:
mysql> SELECT COUNT(CustomerID) AS NumberOfCustomers, Country
-> FROM Customers5
-> GROUP BY Country
-> HAVING NumberOfCustomers > 5
-> ORDER BY NumberOfCustomers DESC;
Empty set (0.00 sec)

3. Create table with following attributes:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL
Write a trigger when commission is above 10000.
### Creating table:

mysql> CREATE TABLE Employee2 (EMPNO INT PRIMARY KEY, ENAME VARCHAR(50), JOB VARCHAR(50),
MGR INT, HIREDATE DATE, SAL DECIMAL(10, 2), COMM DECIMAL(10, 2), DEPTNO INT, AGE INT, ESAL
DECIMAL(10, 2));
Query OK, 0 rows affected (0.01 sec)

### Inserting values:

mysql> INSERT INTO Employee2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, AGE,
ESAL)
-> VALUES
-> (101, 'John Doe', 'Manager', NULL, '2023-01-15', 50000.00, 15000.00, 10, 35, 65000.00),
-> (102, 'Jane Smith', 'Analyst', 101, '2023-02-20', 40000.00, NULL, 20, 28, 45000.00),
-> (103, 'Michael Johnson', 'Developer', 101, '2023-03-10', 35000.00, 12000.00, 10, 30, 40000.00),
-> (104, 'Emily Williams', 'Manager', NULL, '2023-04-05', 55000.00, 18000.00, 30, 38, 70000.00),
-> (105, 'Robert Brown', 'Designer', 104, '2023-05-25', 42000.00, NULL, 30, 29, 48000.00);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

### Trigger:
A:
mysql> DELIMITER //
mysql> CREATE TRIGGER CheckCommission
-> BEFORE INSERT ON Employee2
-> FOR EACH ROW
-> BEGIN
-> IF NEW.COMM > 10000 THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT = 'Commission above 10000 is not allowed';
-> END IF;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
Department of Computer Science & Engineering

Lab Internal-II

Set-4

Sub:Database Management Systems Lab Total Marks:25

1.Create table with following attributes:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

Creating table:

CREATE TABLE Employee (

EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(10, 2),
COMM DECIMAL(10, 2),
DEPTNO INT,
AGE INT,
ESAL DECIMAL(10, 2)
);

Inserting values:

INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, AGE, ESAL)
VALUES
(104, 'Emily Brown', 'Analyst', 102, '2022-01-10', 3200.00, 300.00, 20, 26, 3500.00),
(105, 'David Lee', 'Clerk', 103, '2023-04-05', 1800.00, NULL, 10, 23, 2000.00),
(106, 'Sarah Miller', 'Manager', NULL, '2019-08-25', 5200.00, NULL, 30, 40, 6000.00),
(107, 'Robert Wilson', 'Analyst', 106, '2020-11-12', 3500.00, 400.00, 30, 32, 4200.00),
(108, 'Linda Davis', 'Clerk', 106, '2023-02-18', 1900.00, NULL, 30, 22, 2100.00);
a. List all employee names and their salaries, whose salary lies between 1500/- and 3500/- both
inclusive.

A:
SELECT ENAME, SAL
FROM Employee
WHERE SAL >= 1500 AND SAL <= 3500;

b. Get the description of the table.

A:

DESC Employee;

c. List all employee names and their and their manager whose manager is 7902 or 7566 0r 7789.

A:
SELECT E.ENAME AS EmployeeName,
M.ENAME AS ManagerName
FROM Employee E, Employee M
WHERE E.MGR = M.EMPNO
AND M.EMPNO IN (7902, 7566, 7789);

d. List all employees which starts with either Jor T.

A:

SELECT *
FROM Employee
WHERE ENAME LIKE 'J%' OR ENAME LIKE 'T%';

e. .List all employee names and jobs, whose job title includes M or P.
A:
SELECT ENAME, JOB
FROM Employee
WHERE JOB LIKE '%M%' OR JOB LIKE '%P%';

2.Create a procedure for displaying name and occupation of an employee

where working hours >12 for the following structure of a table
Employee ( name , occupation ,working_date ,working_hours ).

Creating table:

CREATE TABLE Employee (

name VARCHAR(100),
occupation VARCHAR(50),
working_date DATE,
 working_hours INT
);

Inserting values:

INSERT INTO Employee (name, occupation, working_date, working_hours)

VALUES
('John Doe', 'Manager', '2023-08-14', 8),
('Jane Smith', 'Analyst', '2023-08-14', 17),
('Michael Johnson', 'Clerk', '2023-08-14', 6),
('Emily Brown', 'Analyst', '2023-08-14', 16),
('David Lee', 'Clerk', '2023-08-14', 8);

Procedure:

DELIMITER //
CREATE PROCEDURE DisplayEmployeeInfo()
BEGIN
SELECT name, occupation
FROM Employee
WHERE working_hours > 12;
END //
DELIMITER ;

Calling:
CALL DisplayEmployeeInfo();

3. Demonstrate the lock mechanism on a table of your choice.

A:
BEGIN TRANSACTION;
UPDATE Employee SET SAL = SAL + 100 WHERE EMPNO = 101;
COMMIT;
 Department of Computer Science & Engineering

Lab Internal-II

Set-5

Sub:Database Management Systems Lab Total Marks:25

1.Create table with following attributes:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

Creating table:
CREATE TABLE Employee (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50),
JOB VARCHAR(30),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(10, 2),
COMM DECIMAL(10, 2),
DEPTNO INT,
AGE INT,
ESAL DECIMAL(10, 2)
);

Inserting values:
INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
AGE, ESAL)
VALUES
(1, 'John Doe', 'Manager', NULL, '2022-01-15', 6000.00, NULL, 10, 40, 7000.00),
(2, 'Jane Smith', 'Analyst', 1, '2022-03-20', 4000.00, 500.00, 20, 28, 4500.00),
(3, 'Mike Johnson', 'Clerk', 2, '2022-06-10', 2500.00, 100.00, 10, 22, 2700.00),
(4, 'Emily Brown', 'Developer', 1, '2021-11-05', 5500.00, 1000.00, 30, 32, 6500.00),
(5, 'David Lee', 'Manager', NULL, '2023-02-28', 7000.00, NULL, 20, 38, 8000.00);

• List all jobs available in employee table.

A:
 SELECT DISTINCT JOB FROM Employee;

-- Output the list of jobs

+-----------+

| JOB |

+-----------+

| Manager |

| Analyst |

| Clerk |

| Developer |

+-----------+

• List all employees who belongs to the department 10 or 20.

A:

SELECT * FROM Employee

WHERE DEPTNO IN (10, 20);

Output:

+-------+--------------+----------+------+------------+---------+---------+--------+-----+--------+

| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE | ESAL |

+-------+--------------+----------+------+------------+---------+---------+--------+-----+--------+

|1 | John Doe | Manager | NULL | 2022-01-15 | 6000.00 | NULL | 10 | 40 | 7000.00|

|2 | Jane Smith | Analyst | 1 | 2022-03-20 | 4000.00 | 500.00 | 20 | 28 | 4500.00|

|3 | Mike Johnson | Clerk | 2 | 2022-06-10 | 2500.00 | 100.00 | 10 | 22 | 2700.00|

|5 | David Lee | Manager | NULL | 2023-02-28 | 7000.00 | NULL | 20 | 38 | 8000.00|

+-------+--------------+----------+------+------------+---------+---------+--------+-----+--------+

• List all employee names , salary and 15% rise in salary.

 A:

SELECT ENAME, SAL, SAL * 1.15 AS NEW_SALARY FROM Employee;

Output:

+--------------+---------+------------+

| ENAME | SAL | NEW_SALARY |

+--------------+---------+------------+

| John Doe | 6000.00 | 6900.00 |

| Jane Smith | 4000.00 | 4600.00 |

| Mike Johnson | 2500.00 | 2875.00 |

| Emily Brown | 5500.00 | 6325.00 |

| David Lee | 7000.00 | 8050.00 |

+--------------+---------+------------+

• List minimum , maximum , average salaries of employee.

A:

SELECT

MIN(SAL) AS MinSalary,

MAX(SAL) AS MaxSalary,

AVG(SAL) AS AvgSalary

FROM Employee;

Output:

+-----------+-----------+-----------+

| MinSalary | MaxSalary | AvgSalary |

+-----------+-----------+-----------+

| 2500.00 | 7000.00 | 5000.00 |

+-----------+-----------+-----------+
 • Find how many job titles are available in employee table.

A:

SELECT COUNT(DISTINCT JOB) AS NumberOfJobTitles FROM Employee;

Output:

+-------------------+

| NumberOfJobTitles |

+-------------------+

|4 |

+-------------------+

2. Write a trigger to increment scount of sailorcount table by one for each insertion into sailors table.

A:

DELIMITER //

CREATE TRIGGER IncrementSailorCount

AFTER INSERT ON sailors

FOR EACH ROW

BEGIN

UPDATE sailorcount

SET scount = scount + 1;

END;

//

DELIMITER ;

3. Create a procedure for displaying only names of the employees

for the following structure of a table
Employee ( name , occupation ,working_date ,working_hours ).

A:
DELIMITER //
CREATE PROCEDURE DisplayEmployeeNames()
BEGIN
 SELECT name FROM Employee;
END;
//
DELIMITER ;

Calling:
CALL DisplayEmployeeNames();

Department of Computer Science & Engineering

Lab Internal-II

Set-6

Sub:Database Management Systems Lab Total Marks:25

1.Create a trigger invoked automatically when insert the working_hours <0 on following structure of a
table.
Table(name NOT NULL, occupation NOT NULL, working_date ,working_hours).

A:
Creating table:
CREATE TABLE Employee (
 name VARCHAR(50) NOT NULL,
occupation VARCHAR(50) NOT NULL,
working_date DATE,
working_hours DECIMAL(5, 2)
);

Inserting values:

INSERT INTO Employee (name, occupation, working_date, working_hours)

VALUES
('John Doe', 'Manager', '2023-08-14', 8.5),
('Jane Smith', 'Analyst', '2023-08-14', 7.5),
('Mike Johnson', 'Clerk', '2023-08-14', 6.0),
('Emily Brown', 'Developer', '2023-08-14', 9.0),
('David Lee', 'Manager', '2023-08-14', 7.0);

Trigger:
DELIMITER //
CREATE TRIGGER CheckWorkingHours
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
IF NEW.working_hours < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Working hours cannot be negative';
END IF;
END;
//
DELIMITER ;

-- Try to insert a record with negative working_hours

INSERT INTO Employee (name, occupation, working_date, working_hours)
VALUES ('Sarah Turner', 'Designer', '2023-08-14', -5.5);

Error Code: 1644. Working hours cannot be negative

2. Write a procedure for validating age and rating fields of sailors.

SQL> select * from sailors;
+------+--------+--------+------+
| sid |sname | rating | age |
|22 | ramu | 6 | 22 |
| 101 | asdf | 8 | 34 |
| 25 | suresh | 7 | 25 |
| 26 | suresh | 7 | 25 |
| 27 | suresh | 7 | 25 |
| 23 | kiran | -1 | 26 |
| 24 | karan | 4 | 101|
A:
Procedure:
DELIMITER //
CREATE PROCEDURE ValidateSailorFields()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cur_sid INT;
DECLARE cur_sname VARCHAR(50);
DECLARE cur_rating INT;
DECLARE cur_age INT;
DECLARE cur_cursor CURSOR FOR
SELECT sid, sname, rating, age FROM sailors;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_cursor;
read_loop: LOOP
FETCH cur_cursor INTO cur_sid, cur_sname, cur_rating, cur_age;
IF done THEN
LEAVE read_loop;
END IF;
IF cur_rating < 1 OR cur_rating > 10 THEN
SELECT CONCAT('Invalid rating for sailor ', cur_sname) AS ErrorMessage;
END IF;
IF cur_age <= 0 OR cur_age >= 100 THEN
SELECT CONCAT('Invalid age for sailor ', cur_sname) AS ErrorMessage;
END IF;
END LOOP;
CLOSE cur_cursor;
END;
//
DELIMITER ;

Calling:
CALL ValidateSailorFields();

3.Create the following tables with suitable constraints :

Emp(name ,street,city)
company(company_name,city)
works(employee_name,company_name ,salary)

Creating Tables:

CREATE TABLE Emp (

name VARCHAR(50),
street VARCHAR(50),
city VARCHAR(50)
);

CREATE TABLE company (

 company_name VARCHAR(50),
city VARCHAR(50)
);

CREATE TABLE works (

employee_name VARCHAR(50),
company_name VARCHAR(50),
salary DECIMAL(10, 2)
);

Inserting data:

-- Insert data into Emp table

INSERT INTO Emp (name, street, city)
VALUES
('John Doe', '123 Main St', 'New York'),
('Jane Smith', '456 Elm St', 'Los Angeles'),
('Mike Johnson', '789 Oak St', 'Chicago'),
('Emily Brown', '234 Maple St', 'Houston'),
('David Lee', '567 Pine St', 'Miami');

-- Insert data into company table

INSERT INTO company (company_name, city)
VALUES
('First Bank Corporation', 'New York'),
('Second Tech Inc', 'San Francisco'),
('Third Services Ltd', 'Chicago');

-- Insert data into works table

INSERT INTO works (employee_name, company_name, salary)
VALUES
('John Doe', 'First Bank Corporation', 12000.00),
('Jane Smith', 'Second Tech Inc', 9000.00),
('Mike Johnson', 'First Bank Corporation', 15000.00),
('Emily Brown', 'Third Services Ltd', 8000.00),
('David Lee', 'Second Tech Inc', 11000.00);

• Find the names and cities of residence of all employee who works of "First Bank Corporation".

A:
SELECT Emp.name, Emp.city
FROM Emp
JOIN works ON Emp.name = works.employee_name
WHERE works.company_name = 'First Bank Corporation';

Output:
+-----------+----------+
| name | city |
+------------+----------+
| John Doe | New York |
| Mike Johnson | Chicago |
+------------+----------+

• Find the names,street addresses and cities of residence of all employees who works for "First
Bank Corporation" and earns more than $10,000.

A:
SELECT Emp.name, Emp.street, Emp.city
FROM Emp
JOIN works ON Emp.name = works.employee_name
WHERE works.company_name = 'First Bank Corporation' AND works.salary > 10000.00;

Output:
+------------+-------------+----------+
| name | street | city |
+------------+-------------+----------+
| Mike Johnson | 789 Oak St | Chicago |
+------------+-------------+----------+

• Find all employees in the database who do not work for "First Bank Corporation”

A:
SELECT Emp.name
FROM Emp
WHERE Emp.name NOT IN (
SELECT DISTINCT works.employee_name
FROM works
WHERE works.company_name = 'First Bank Corporation'
);

Output:

+------------+
| name |
+------------+
| Jane Smith |
| Emily Brown |
| David Lee |
+------------+
• Find all employees in the database who earn more than each employee of "Small Bank
Corporation".

A:
 SELECT Emp.name
FROM Emp
WHERE NOT EXISTS (
SELECT 1
FROM works
WHERE works.company_name = 'Small Bank Corporation'
AND works.salary >= Emp.salary
);

Output:
+------------+
| name |
+------------+
| John Doe |
| Mike Johnson |
| David Lee |
+------------+
• Find the company that has the most employees.

A:
SELECT company_name, COUNT(employee_name) AS employee_count
FROM works
GROUP BY company_name
ORDER BY employee_count DESC
LIMIT 1;

Output:
+--------------------+----------------+
| company_name | employee_count |
+--------------------+----------------+
| Second Tech Inc | 2 |
+--------------------+----------------+