■ DATABASE SYSTEMS LAB
Complete SQL Study Guide
UET Peshawar — BS(CS) 2nd Semester | Mid Term Spring 2026
■ What's Inside This Guide:
Topic Coverage
Task 1 Create Database & Tables (LibraryDB + UniversityDB)
Task 2 INSERT — Adding Records
Task 3 SELECT, WHERE, UPDATE, DELETE, ORDER BY
Task 4 Aggregate Functions — AVG, MIN, MAX, COUNT, SUM
Bonus Extra SQL concepts: LIKE, BETWEEN, GROUP BY, HAVING, JOINs
1. SQL BASICS — What Is SQL?
SQL (Structured Query Language) is used to communicate with databases. It lets you create, read,
update, and delete data — often called CRUD operations.
Command Purpose Example Use
CREATE Make a database or table CREATE DATABASE LibraryDB
USE Select a database to work in USE LibraryDB
INSERT INTO Add new records INSERT INTO Members VALUES(...)
SELECT Read/display data SELECT * FROM Members
UPDATE Modify existing data UPDATE Members SET age=22
DELETE Remove records DELETE FROM Members WHERE id=3
DROP Delete table/database DROP TABLE Members
2. DATA TYPES (Used in Your Exam)
When creating tables, each column needs a data type:
Data Type What It Stores Example
INT Whole numbers (no decimal) age, marks, books_issued
VARCHAR(n) Text up to n characters name, department, program
FLOAT / DECIMAL Numbers with decimals GPA, salary
DATE Date values dob, enrollment_date
CHAR(n) Fixed-length text grade ('A', 'B', 'C')
3. TASK 1 — Create Database & Table
■ Paper 1 — LibraryDB (Instructor: Sadiq Ur Rehman)
Step 1: Create the database
CREATE DATABASE LibraryDB;
Step 2: Select (use) the database
USE LibraryDB;
Step 3: Create the Members table
CREATE TABLE Members ( member_id INT PRIMARY KEY, member_name VARCHAR(50),
program VARCHAR(50), age INT, books_issued INT );
■ Paper 2 — UniversityDB (Instructor: Aisha Javed)
CREATE DATABASE UniversityDB; USE UniversityDB; CREATE TABLE Students (
student_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), age INT,
marks INT );
■ KEY POINT: PRIMARY KEY means this column uniquely identifies each row — no two rows can have the
same value, and it cannot be NULL.
4. TASK 2 — INSERT Records
Syntax:
INSERT INTO table_name (col1, col2, col3, ...) VALUES (val1, val2, val3, ...);
Paper 1 — 5 Records into Members:
INSERT INTO Members (member_id, member_name, program, age, books_issued) VALUES
(1, 'Ali Khan', 'BS IT', 20, 5), (2, 'Sara Ahmed', 'BS CS', 21, 2), (3, 'Usman
Raza', 'BS IT', 22, 7), (4, 'Hina Bibi', 'BBA', 20, 1), (5, 'Bilal Shah', 'BS
CS', 23, 4);
Paper 2 — 5 Records into Students:
INSERT INTO Students (student_id, name, department, age, marks) VALUES (1, 'Ahmed
Ali', 'Computer Science', 19, 85), (2, 'Fatima Noor', 'Electrical', 20, 72), (3,
'Zain Malik', 'Computer Science', 21, 91), (4, 'Sana Iqbal', 'Civil', 20, 60),
(5, 'Omar Farooq', 'Mechanical', 22, 78);
■ TIP: Text values go inside single quotes 'like this'. Numbers (INT) do NOT need quotes.
5. TASK 3 — SELECT, WHERE, UPDATE, DELETE, ORDER BY
a) Display ALL Records — SELECT *
Paper 1: SELECT * FROM Members;
Paper 2: SELECT * FROM Students;
b) Filter with WHERE Clause
Paper 1 — Members who issued MORE than 3 books: SELECT * FROM Members WHERE
books_issued > 3;
Paper 2 — Students with marks GREATER than 70: SELECT * FROM Students WHERE marks
> 70;
c) Display Specific Columns with WHERE
Paper 1 — Names of members in 'BS IT' program: SELECT member_name FROM Members
WHERE program = 'BS IT';
Paper 2 — Names of students from 'Computer Science': SELECT name FROM Students
WHERE department = 'Computer Science';
d) UPDATE — Modify Existing Data
Paper 1 — Increase books_issued by 1 for member_id = 1: UPDATE Members SET
books_issued = books_issued + 1 WHERE member_id = 1;
Paper 2 — Update marks of student_id = 1 to 85: UPDATE Students SET marks = 85
WHERE student_id = 1;
■■ ALWAYS use WHERE with UPDATE and DELETE. Without WHERE, ALL rows in the table will be
changed/deleted!
e) DELETE — Remove a Record
Paper 1 — Remove member with member_id = 4: DELETE FROM Members WHERE member_id =
4;
Paper 2 — Remove student with student_id = 3: DELETE FROM Students WHERE
student_id = 3;
f) ORDER BY — Sort Results
Paper 1 — All members sorted by books_issued (highest first = DESC): SELECT *
FROM Members ORDER BY books_issued DESC;
Paper 2 — Students sorted by marks in descending order: SELECT * FROM Students
ORDER BY marks DESC;
■ ASC = smallest first (A to Z, 0 to 9) | DESC = largest first (Z to A, 9 to 0) Default is ASC if you don't write
anything.
6. TASK 4 — Aggregate Functions
Aggregate functions perform calculations on a set of values and return ONE result.
Function What It Does Example
AVG(col) Calculate the average value AVG(marks) → 77.2
MIN(col) Find the smallest value MIN(books_issued) → 1
MAX(col) Find the largest value MAX(marks) → 91
COUNT(*) Count number of rows COUNT(*) → 5
SUM(col) Add up all values SUM(books_issued) → 19
Paper 1 Solutions:
-- a) Average number of books issued: SELECT AVG(books_issued) AS avg_books FROM
Members; -- b) Minimum number of books issued: SELECT MIN(books_issued) AS
min_books FROM Members;
Paper 2 Solutions:
-- a) Average marks of students: SELECT AVG(marks) AS average_marks FROM
Students; -- b) Highest marks in the table: SELECT MAX(marks) AS highest_marks
FROM Students;
■ AS is an alias — it gives a custom name to the result column. It's optional but makes your output look
cleaner.
7. BONUS — Extra SQL You Should Know
WHERE with Multiple Conditions (AND / OR)
-- AND: both conditions must be true SELECT * FROM Students WHERE marks > 70 AND
department = 'Computer Science'; -- OR: at least one condition must be true
SELECT * FROM Members WHERE program = 'BS IT' OR program = 'BS CS';
LIKE — Pattern Matching
-- Names starting with 'A' SELECT * FROM Students WHERE name LIKE 'A%'; -- Names
ending with 'n' SELECT * FROM Members WHERE member_name LIKE '%n';
% means ANY number of characters. _ means exactly ONE character.
BETWEEN — Range Filter
-- Students with marks between 60 and 80: SELECT * FROM Students WHERE marks
BETWEEN 60 AND 80;
COUNT — Count Rows
-- How many members are there? SELECT COUNT(*) AS total_members FROM Members; --
How many students scored above 70? SELECT COUNT(*) FROM Students WHERE marks >
70;
GROUP BY — Group Results
-- Count students per department: SELECT department, COUNT(*) AS total FROM
Students GROUP BY department;
HAVING — Filter After GROUP BY
-- Departments with more than 1 student: SELECT department, COUNT(*) AS total
FROM Students GROUP BY department HAVING COUNT(*) > 1;
■ Use WHERE to filter rows BEFORE grouping. Use HAVING to filter AFTER grouping.
8. EXAM QUICK REFERENCE CHEAT SHEET
All the SQL patterns you need for your exam at a glance:
Task SQL Template
Create DB CREATE DATABASE name;
Use DB USE name;
Create Table CREATE TABLE name ( col1 INT PRIMARY KEY, col2 VARCHAR(50) );
Insert Row INSERT INTO table (col1, col2) VALUES (val1, val2);
Show All SELECT * FROM table;
Filter Rows SELECT * FROM table WHERE col > value;
Specific Col SELECT col_name FROM table WHERE col = 'value';
Update UPDATE table SET col = new_val WHERE id = 1;
Increment UPDATE table SET col = col + 1 WHERE id = 1;
Delete DELETE FROM table WHERE id = 1;
Sort ASC SELECT * FROM table ORDER BY col ASC;
Sort DESC SELECT * FROM table ORDER BY col DESC;
Average SELECT AVG(col) FROM table;
Minimum SELECT MIN(col) FROM table;
Maximum SELECT MAX(col) FROM table;
Sum SELECT SUM(col) FROM table;
Count SELECT COUNT(*) FROM table;
■ EXAM TIPS: 1. Always end SQL statements with a semicolon (;) 2. Text/string values go in single quotes:
'BS IT' — numbers do NOT need quotes 3. Always USE the database before creating tables or inserting data
4. PRIMARY KEY = unique + not null — always one per table 5. SQL keywords are NOT case-sensitive
(SELECT = select = Select) but use UPPERCASE for clarity 6. ALWAYS use WHERE with UPDATE and
DELETE to avoid modifying all rows!
Good luck in your exam! You've got this! ■