DBMS LAB #2 - SOLUTIONS
Database Creation
1. Create Employee Database
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
2. Create Employee Table
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
3. Insert Sample Data into Employee Table
INSERT INTO employee (id, name, salary) VALUES
(1, 'Ali', 60000),
(2, 'Sara', 75000),
(3, 'John', 90000);
Example Queries and Outputs
1. SQL Arithmetic Operators Example
Query:
UPDATE employee SET salary = salary * 1.10;
Output: The salary column in the employee table will be updated with a 10% increment.
2. SQL Comparison Operators Example
Query:
SELECT id, name FROM employee WHERE salary >= 70000;
Output: List of employees with a salary of 70,000 or more.
3. SQL Logical Operators Example
Using AND Operator
sql
CopyEdit
SELECT * FROM department WHERE dname='sales' AND dept_location='lahore';
✅ Filters departments where name is 'Sales' and located in Lahore.
Output:
dept_id dname dept_location
101 Sales Lahore
Using OR Operator
sql
CopyEdit
SELECT * FROM employee WHERE name='Ali' OR salary > 80000;
✅ Fetches employees named Ali or earning more than 80,000.
Output:
id name salary
1 Ali 66000
3 John 99000
NOT Operator
sql
CopyEdit
SELECT * FROM employee WHERE NOT salary > 80000;
✅ Finds employees earning less than or equal to 80,000.
Output:
id name salary
1 Ali 66000
4. SQL Set Operators Example
Using UNION
sql
CopyEdit
SELECT id, name FROM employee_a
UNION
SELECT id, name FROM employee_b;
✅ Combines results from two tables, removing duplicates.
5. SQL Set Operators
Using INTERSECT
sql
CopyEdit
SELECT id, name FROM employee_a
INTERSECT
SELECT id, name FROM employee_b;
✅ Retrieves only the employees that exist in both employee_a and employee_b.
Using EXCEPT
sql
CopyEdit
SELECT id, name FROM employee_a
EXCEPT
SELECT id, name FROM employee_b;
✅ Retrieves employees that exist in employee_a but not in employee_b.
7. Sorting in SQL (ORDER BY Clause)
Sort Employees by Salary (Ascending)
sql
CopyEdit
SELECT * FROM employee ORDER BY salary ASC;
✅ Sorts employees in increasing order of salary.
Output:
id name salary
1 Ali 66000
2 Sara 82500
3 John 99000
Sort Employees by Name (Descending)
sql
CopyEdit
SELECT * FROM employee ORDER BY name DESC;
✅ Sorts employees in reverse alphabetical order (Z → A).
Output:
id name salary
3 John 99000
2 Sara 82500
1 Ali 66000
Sort Employees by Salary (Descending)
sql
CopyEdit
SELECT * FROM employee ORDER BY salary DESC;
✅ Sorts employees in decreasing order of salary.
Output:
id name salary
3 John 99000
2 Sara 82500
1 Ali 66000
Lab Tasks Solutions
1. Annual Bonus Calculation
SELECT name, salary, (salary * 0.20) AS Annual_Bonus FROM employee;
2. Salary Breakdown
SELECT
name,
salary AS Net_Salary,
(salary * 0.12) AS House_Rent,
(salary * 0.09) AS Travel_Allowance,
(salary - (salary * 0.12 + salary * 0.09)) AS Basic_Salary,
(salary * 1.10) AS Incremented_Salary
FROM employee;
3. Student Database with Admission Status
CREATE DATABASE StudentDB;
USE StudentDB;
CREATE TABLE student (
Reg_Id INT PRIMARY KEY,
Name VARCHAR(50),
Contact_No VARCHAR(15),
percentage DECIMAL(5,2),
test_marks DECIMAL(5,2),
aggregate DECIMAL(5,2)
);
ALTER TABLE student ADD admission_status VARCHAR(10);
UPDATE student
SET admission_status = CASE
WHEN aggregate > 60 THEN 'Pass'
ELSE 'Fail'
END;
4. Students with Percentage or Test Marks Above 50%
SELECT name FROM student WHERE percentage > 50 OR test_marks > 50;
5. Orders Database - Display Specific Order Status
CREATE DATABASE OrdersDB;
USE OrdersDB;
CREATE TABLE Local_Orders (
Order_No INT PRIMARY KEY,
Cust_Id INT,
Cust_name VARCHAR(50),
Product_ID INT,
Product_name VARCHAR(50),
Cust_address VARCHAR(100),
branch_address VARCHAR(100),
order_status VARCHAR(20)
);
SELECT * FROM Local_Orders WHERE order_status IN ('Shipped', 'Delivered');
6. Orders Not Shipped or Delivered
SELECT Order_No FROM Local_Orders WHERE order_status NOT IN ('Shipped',
'Delivered');
7. Display All Orders from Two Tables
CREATE TABLE International_Orders LIKE Local_Orders;
SELECT * FROM Local_Orders
UNION ALL
SELECT * FROM International_Orders;
8. Attendance Records Filtering
CREATE TABLE attendance_records (
employee_id INT,
time_in TIME,
time_out TIME,
total_time INT
);
SELECT employee_id FROM attendance_records WHERE employee_id IN (SELECT id
FROM employee);
9. Employees Ordered by Salary
SELECT * FROM employee ORDER BY salary ASC;
10. LIKE Operator Use Cases
Case 1: Employees whose names start with 'A'
SELECT * FROM employee WHERE name LIKE 'A%';
Case 2: Customers with email containing 'gmail'
SELECT * FROM customers WHERE email LIKE '%gmail%';
Lab Task Questions
1. What have you learned from the lab task?
Implementing arithmetic, comparison, logical, and set operators in SQL.
Using ORDER BY for sorting tables.
Applying conditions using WHERE, AND, OR, NOT.
Creating and modifying databases using SQL queries.
2. What was the most challenging task and how did you overcome it?
The most challenging task was implementing set operators (UNION, INTERSECT,
EXCEPT) since they require strict column matching.
Overcame by ensuring column names and data types matched before applying set
operations.