Lab Manual: Database Systems

University of Management and Technology,

Lahore Campus
Lab- 06 Manual

Lab Instructor: Riaz Ahmad

Department of Computer Science
Email: [email protected]

Lab: 06 Logical Operator

Logical Operator
The Logical Operator is nothing but which returns the result in one form, i.e., either it
will display the query is true, or the query is false. The results displayed to combine or
merge more than one true or false data.
The Logical Operators in SQL are as follows:
1. AND OPERATOR
2. OR OPERATOR
3. NOT OPERATOR
4. BETWEEN OPERATOR
5. IN OPERATOR
6. LIKE OPERATOR
Consider we have an employees table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Fatima Noor 50000 Lahore Project 2021-06-20 24
Manager
2 Ali Raza 75000 Karchi System 2019-12-24 23
Engineer
3 Umar Maqsood 40000 Islamabad Manager 2021-08-15 26
4 Umair Ali 90000 Rawalpindi Software 2021-06-13 24
Tester
5 Zaman 45000 Pattoki Project 2020-08-09 23
Manager
6 Abu huraira 60000 Okara Manager 2019-07-17 26
7 Kashif Ali 55000 Sialkot System 2021-10-10 24

Department of Computer Science, UMT, Lahore. 1 Riaz Ahmad

Lab Manual: Database Systems

Engineer
8 Husnain Raza 45000 Peshawar Software 2020-09-10 26
Engineer
9 Hira Saleem 50000 Multan Software 2021-01-01 25
Tester
10 Jawad Hassan 60000 DI Khan Project 2020-10-02 24
Manager
11 Bilal Hussain 45500 Pattoki HR 2019-01-02 26
12 Riaz Ahmad 50500 Pattoki Software 2016-09-10 25
Developer
13 Dur e Shahwar 50000 Lahore HR 2013-12-12 23
14 Mariyam Ali 40000 Islamabad Project 2017-11-10 25
Manager
15 Mahnoor 38000 Lahore Software 2019-03-05 20
Developer

Create Employees Table and insert data into the table:

CREATE TABLE Employee (
E_ID INT PRIMARY KEY,
Name VARCHAR(50),
Salary INT,
City VARCHAR(50),
Designation VARCHAR(50),
Date_of_Joining DATE,
Age INT
);

INSERT INTO Employee (E_ID, Name, Salary, City, Designation, Date_of_Joining,

Age) VALUES
(1, 'Fatima Noor', 50000, 'Lahore', 'Project Manager', '2021-06-20', 24),
(2, 'Ali Raza', 75000, 'Karachi', 'System Engineer', '2019-12-24', 23),
(3, 'Umar Maqsood', 40000, 'Islamabad', 'Manager', '2021-08-15', 26),
(4, 'Umair Ali', 90000, 'Rawalpindi', 'Software Tester', '2021-06-13', 24),
(5, 'Zaman', 45000, 'Pattoki', 'Project Manager', '2020-08-09', 23),
(6, 'Abu Huraira', 60000, 'Okara', 'Manager', '2019-07-17', 26),
(7, 'Kashif Ali', 55000, 'Sialkot', 'System Engineer', '2021-10-10', 24),
(8, 'Husnain Raza', 45000, 'Peshawar', 'Software Engineer', '2020-09-10', 26),
(9, 'Hira Saleem', 50000, 'Multan', 'Software Tester', '2021-01-01', 25),
(10, 'Jawad Hassan', 60000, 'DI Khan', 'Project Manager', '2020-10-02', 24),
(11, 'Bilal Hussain', 45500, 'Pattoki', 'HR', '2019-01-02', 26),
(12, 'Riaz Ahmad', 50500, 'Pattoki', 'Software Developer', '2016-09-10', 25),
Department of Computer Science, UMT, Lahore. 2 Riaz Ahmad
Lab Manual: Database Systems

(13, 'Dur e Shahwar', 50000, 'Lahore', 'HR', '2013-12-12', 23),

(14, 'Mariyam Ali', 40000, 'Islamabad', 'Project Manager', '2017-11-10', 25),
(15, 'Mahnoor', 38000, 'Lahore', 'Software Developer', '2019-03-05', 20);

1. AND Operator
The SQL AND operator is used with the where clause in the SQL Query. AND operator
in SQL returns only those records which satisfy both the conditions in the SQL query.

Example:
Write a query to retrieve only those records of employees from the employees table
where the designation is 'Project Manager' and the City to which the employee belongs
to is DI Khan.
Query:

SELECT * FROM employees WHERE

City = "DI Khan" AND Designation = "Project Manager";

Output:
E_ID Name Salary City Designation Date_of_Joining Age
10 Jawad 60000 DI Khan Project 2020-10-02 24
Hassan Manager

2. BETWEEN Operator
This operator displays the records which fall between the given ranges in the SQL
query.
Example:
Write a query to retrieve only those records of an employee from the employees table
where employee salary lies between 50000 to 90000.
Query:
SELECT * FROM employees WHERE Salary BETWEEN 50000 AND 90000;

There are nine records in the employees table whose salary falls between 50000 to
90000.

3. SQL OR Operator
The SQL OR operator is used with the where clause in an SQL Query. AND operator in
SQL returns only those records that satisfy any of the conditions in the SQL query.
Example:
Write a query to retrieve only those records of employees from the employees table
where the employee's designation is “Software Developer” or the city to which the
employee belongs is Lahore.
Department of Computer Science, UMT, Lahore. 3 Riaz Ahmad
Lab Manual: Database Systems

Query:
SELECT * FROM employees
WHERE Designation = "Software Developer" OR City = " Lahore ";

4. SQL IN Operator
When we want to check for one or more than one value in a single SQL query, we use
IN operator with the WHERE clause in a SELECT query.
Example:
Write a query to retrieve only those records of employees from the employees table
where the city to which the employee belongs to is either Lahore, Karachi, or DI Khan.

Query:
SELECT * FROM employees WHERE City IN ("Lahore", "Karachi", "DI Khan");

5. SQL NOT Operator

NOT operator in SQL shows those records from the table where the criteria is not met.
NOT operator is used with where clause in a SELECT query.

Example:
Write a query to retrieve only those records of employees from the employees table
where the employee's designation is not Project Manager.
Query:
SELECT * FROM employees WHERE NOT Designation = "Project Manager";

6. The SQL LIKE Operator:

LIKE Operator in SQL displays only those data from the table which matches the
pattern specified in the query. Percentage (%) and underscore (_) are the two wildcard
operators used with LIKE Operator to perform pattern matching tasks.
Example:
Write a query to retrieve only those records of employees from the employees table
whose salary starts with the digit 5.
Query:
SELECT * FROM employees WHERE Salary LIKE "5%";

Department of Computer Science, UMT, Lahore. 4 Riaz Ahmad

Lab Manual: Database Systems
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any
position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second
position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at
least 2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at
least 3 characters in length
WHERE ContactName LIKE 'a%o%' Finds any values that start with "a" and end
with "o"

Graded Lab Exercises

Customer Table:
customeri name city country amount
d
1 Ali Khan Lahore Pakistan 1200
2 Sarah Ali Karachi Pakistan 900
3 John Doe New York USA 750
4 Ayesha R. Islamabad Pakistan 2000
5 Mike Ross London UK 3000
6 Rehan M. Kasur Pakistan 850
7 Zara Tariq Multan Pakistan 650
8 Usman R. Lahore Pakistan 500
9 Hassan I. Faisalabad Pakistan 1200
10 Sofia A. Quetta Pakistan 1100
11 Adam Smith Sydney Australia 1400
12 Noor J. Karachi Pakistan 800
13 Bilal Z. Peshawar Pakistan 1500
14 David Lee Toronto Canada 950
15 Hira Q. Hyderabad Pakistan 400
16 Hamza K. Lahore Pakistan 3000
17 Maria S. Islamabad Pakistan 1750
18 Ahmed Z. Kasur Pakistan 1000
19 Fatima Y. Sialkot Pakistan 2100
20 Kevin J. Berlin German 600

Department of Computer Science, UMT, Lahore. 5 Riaz Ahmad

Lab Manual: Database Systems
y

Create Customers table and insert data into the table:

CREATE TABLE Customers (
customerid INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50),
amount INT
);

INSERT INTO Customers (customerid, name, city, country, amount) VALUES

(1, 'Ali Khan', 'Lahore', 'Pakistan', 1200),
(2, 'Sarah Ali', 'Karachi', 'Pakistan', 900),
(3, 'John Doe', 'New York', 'USA', 750),
(4, 'Ayesha R.', 'Islamabad', 'Pakistan', 2000),
(5, 'Mike Ross', 'London', 'UK', 3000),
(6, 'Rehan M.', 'Kasur', 'Pakistan', 850),
(7, 'Zara Tariq', 'Multan', 'Pakistan', 650),
(8, 'Usman R.', 'Lahore', 'Pakistan', 500),
(9, 'Hassan I.', 'Faisalabad', 'Pakistan', 1200),
(10, 'Sofia A.', 'Quetta', 'Pakistan', 1100),
(11, 'Adam Smith', 'Sydney', 'Australia', 1400),
(12, 'Noor J.', 'Karachi', 'Pakistan', 800),
(13, 'Bilal Z.', 'Peshawar', 'Pakistan', 1500),
(14, 'David Lee', 'Toronto', 'Canada', 950),
(15, 'Hira Q.', 'Hyderabad', 'Pakistan', 400),
(16, 'Hamza K.', 'Lahore', 'Pakistan', 3000),
(17, 'Maria S.', 'Islamabad', 'Pakistan', 1750),
(18, 'Ahmed Z.', 'Kasur', 'Pakistan', 1000),
(19, 'Fatima Y.', 'Sialkot', 'Pakistan', 2100),
(20, 'Kevin J.', 'Berlin', 'Germany', 600);

Department of Computer Science, UMT, Lahore. 6 Riaz Ahmad

Lab Manual: Database Systems

Products Table:
productid productname category price
1 iPhone 15 Mobile 1200
2 Samsung Galaxy S Mobile 1000
3 HP Laptop Laptop 1500
4 Dell Inspiron Laptop 1400
5 Apple MacBook Laptop 2500
6 Lenovo ThinkPad Laptop 1300
7 Xiaomi Redmi 12 Mobile 800
8 Oppo Reno 10 Mobile 900
9 Sony Headphones Accessories 200
10 JBL Speaker Accessories 250
11 Canon EOS 90D Camera 2200
12 Nikon D750 Camera 2300
13 Samsung Tablet Tablet 950
14 Apple iPad Tablet 1100
15 ASUS ROG Laptop Laptop 2700
16 Dell XPS 13 Laptop 1800
17 Bose Earbuds Accessories 300
18 Huawei Tablet Tablet 700
19 Google Pixel 7 Mobile 1100
20 Microsoft Surface Laptop 2000
Create Products table and insert data into the table:
CREATE TABLE Products (
productid INT PRIMARY KEY,
productname VARCHAR(100),
category VARCHAR(50),
price INT
);
INSERT INTO Products (productid, productname, category, price) VALUES
(1, 'iPhone 15', 'Mobile', 1200),
(2, 'Samsung Galaxy S', 'Mobile', 1000),
(3, 'HP Laptop', 'Laptop', 1500),
(4, 'Dell Inspiron', 'Laptop', 1400),
(5, 'Apple MacBook', 'Laptop', 2500),
(6, 'Lenovo ThinkPad', 'Laptop', 1300),
(7, 'Xiaomi Redmi 12', 'Mobile', 800),
(8, 'Oppo Reno 10', 'Mobile', 900),

Department of Computer Science, UMT, Lahore. 7 Riaz Ahmad

Lab Manual: Database Systems
(9, 'Sony Headphones', 'Accessories', 200),
(10, 'JBL Speaker', 'Accessories', 250),
(11, 'Canon EOS 90D', 'Camera', 2200),
(12, 'Nikon D750', 'Camera', 2300),
(13, 'Samsung Tablet', 'Tablet', 950),
(14, 'Apple iPad', 'Tablet', 1100),
(15, 'ASUS ROG Laptop', 'Laptop', 2700),
(16, 'Dell XPS 13', 'Laptop', 1800),
(17, 'Bose Earbuds', 'Accessories', 300),
(18, 'Huawei Tablet', 'Tablet', 700),
(19, 'Google Pixel 7', 'Mobile', 1100),
(20, 'Microsoft Surface', 'Laptop', 2000);
AND, OR, and NOT Operators
1. Write a SQL query to display all customers from the Customer table where the amount is
between 500 and 1000, and customerid is greater than 5.
2. Write a SQL query to fetch customerid, name, city, and amount where amount is between 500
and 1000, and customerid is greater than 5 but less than 10.
3. Write a SQL query to retrieve customers where amount is between 500 and 1000, and
customerid is greater than 5 or less than 10.
4. Write a SQL query to show customer records where the country is Pakistan and the amount is
not 1000 or where the customerid is 2.
5. Write a SQL query to update the country field to 'Pak' for customers where customerid is 1 or
where customerid is greater than 7 and the city is not Lahore.
6. Write a SQL query to select all fields from the Customer table where the country is Pakistan
and the city is either Lahore or Kasur.
LIKE Operator
7. Write a SQL query to retrieve all customers whose CustomerName starts with "A".
8. Write a SQL query to fetch all customers whose CustomerName ends with "r".
9. Write a SQL query to get all customers where CustomerName contains the letter "r" at any
position.
10. Write a SQL query to fetch customers where CustomerName has "a" in the second position.
11. Write a SQL query to retrieve all customers whose CustomerName starts with "B" and ends
with "n".
12. Write a SQL query to display all products from the Products table where the ProductName
contains the word "Laptop" anywhere in the name.
13. Write a SQL query to select all products where ProductName starts with "D" and has "o" in
the fourth position.
14. Write a SQL query to fetch all customers whose CustomerName contains exactly 6 characters.
15. Write a SQL query to find all records where CustomerName starts with "M" and the second
character is not "a".
Additional Queries (Based on Sample 20-Record Table)
16. Write a SQL query to fetch all customer records where the city starts with "K" and the amount
is more than 2000.
17. Write a SQL query to find all customers where CustomerName starts with "A" and the amount
is exactly 1500.
18. Write a SQL query to display all customer records where city ends with "bad" (e.g.,
Islamabad, Hyderabad).

Department of Computer Science, UMT, Lahore. 8 Riaz Ahmad

Lab Manual: Database Systems
19. Write a SQL query to fetch all customers whose name contains an underscore (_).
20. Write a SQL query to display all products where the ProductName contains either "Phone" or
"Tablet".
Happy Learning! May Allah Enhance your knowledge😊

Department of Computer Science, UMT, Lahore. 9 Riaz Ahmad