Assignment # 05
Bhavita (TCT-007)
Thar Institute of Engineering, Sciences &; Technology (TIEST)
CT-261: Database Management System
Engr. Abdul Hafeez
April 20, 2026
Lab Tasks
Task 1: Practice single data type
Create and run the example tables for these data types from the handout: TINYINT, SMALLINT, INT,
BIGINT,
DECIMAL, NUMERIC, FLOAT, CHAR, VARCHAR, NCHAR, NVARCHAR, DATE, TIME,
DATETIME, BIT, IDENTITY,
VARCHAR(MAX), and NVARCHAR(MAX).
For every example:
• Create the table exactly as shown in the handout.
• Insert the sample value.
• Run SELECT * FROM table_name; to verify the record.
• Observe what type of data the column stores.
CREATE TABLE demo_tinyint (age TINYINT);
INSERT INTO demo_tinyint VALUES (22);
SELECT * FROM demo_tinyint;
CREATE TABLE demo_smallint (room_no SMALLINT);
INSERT INTO demo_smallint VALUES (205);
SELECT * FROM demo_smallint;
CREATE TABLE demo_int (student_id INT);
INSERT INTO demo_int VALUES (1001);
SELECT * FROM demo_int;
CREATE TABLE demo_bigint (national_id BIGINT);
INSERT INTO demo_bigint VALUES (4210112345678);
SELECT * FROM demo_bigint;
CREATE TABLE demo_decimal (fee DECIMAL(10,2));
INSERT INTO demo_decimal VALUES (12500.75);
SELECT * FROM demo_decimal;
CREATE TABLE demo_numeric (cgpa NUMERIC(3,2));
INSERT INTO demo_numeric VALUES (3.75);
SELECT * FROM demo_numeric;
CREATE TABLE demo_numeric (cgpa NUMERIC(3,2));
INSERT INTO demo_numeric VALUES (3.75);
SELECT * FROM demo_numeric;
CREATE TABLE demo_char (gender CHAR(1));
INSERT INTO demo_char VALUES ('M');
SELECT * FROM demo_char;
CREATE TABLE demo_varchar (student_name VARCHAR(50));
INSERT INTO demo_varchar VALUES ('Ali Khan');
SELECT * FROM demo_varchar;
CREATE TABLE demo_nchar (grade NCHAR(2));
INSERT INTO demo_nchar VALUES ('A');
SELECT * FROM demo_nchar;
CREATE TABLE demo_nvarchar (address NVARCHAR(100));
INSERT INTO demo_nvarchar VALUES (N'Universite de Paris');
SELECT * FROM demo_nvarchar;
CREATE TABLE demo_date (admission_date DATE);
INSERT INTO demo_date VALUES ('2026-04-09');
SELECT * FROM demo_date;
CREATE TABLE demo_time (class_time TIME);
INSERT INTO demo_time VALUES ('09:30:00');
SELECT * FROM demo_time;
CREATE TABLE demo_datetime (created_at DATETIME);
INSERT INTO demo_datetime VALUES ('2026-04-09 10:30:00');
SELECT * FROM demo_datetime;
CREATE TABLE demo_bit (is_active BIT);
INSERT INTO demo_bit VALUES (1);
SELECT * FROM demo_bit;
CREATE TABLE demo_identity (
id INT IDENTITY(1,1) PRIMARY KEY,
student_name VARCHAR(50)
);
INSERT INTO demo_identity (student_name) VALUES ('Ali');
INSERT INTO demo_identity (student_name) VALUES ('Ayesha');
SELECT * FROM demo_identity;
CREATE TABLE demo_varchar_max (description VARCHAR(MAX));
INSERT INTO demo_varchar_max VALUES ('This is a long project description...');
SELECT * FROM demo_varchar_max;
CREATE TABLE demo_nvarchar_max (comments NVARCHAR(MAX));
INSERT INTO demo_nvarchar_max VALUES (N'Café résumé example');
SELECT * FROM demo_nvarchar_max;
Task 2: Create and run the datatype_demo table
Write and execute the datatype_demo table from the handout, then display the inserted row.
SELECT * FROM datatype_demo;
Write short answers after running the query:
• Which column stores whole numbers?
• Which column stores decimal values?
• Which column stores only date?
• Which column stores only time?
• Which column stores yes/no type value?
CREATE TABLE datatype_demo (
student_id INT,
student_name VARCHAR(50),
age TINYINT,
cgpa DECIMAL(3,2),
gender CHAR(1),
city VARCHAR(30),
admission_date DATE,
class_time TIME,
is_active BIT
);
INSERT INTO datatype_demo
VALUES (1001, 'Ali Khan', 21, 3.45, 'M', 'Peshawar', '2026-04-09', '09:00:00', 1);
SELECT * FROM datatype_demo;
Whole numbers: student_id, age
Decimal values: cgpa
Only date: admission_date
Only time: class_time
Yes/No value: is_active
Task 3: Create your own student table
Create a table named student_profile with these columns:
• student_id as INT
• student_name as VARCHAR(50)
• age as TINYINT
• gender as CHAR(1)
• city as VARCHAR(30)
• cgpa as DECIMAL(3,2)
• admission_date as DATE
• is_active as BIT
Insert at least 3 records of your own choice, then run:
CREATE TABLE student_profile (
student_id INT,
student_name VARCHAR(50),
age TINYINT,
gender CHAR(1),
city VARCHAR(30),
cgpa DECIMAL(3,2),
admission_date DATE,
is_active BIT
);
INSERT INTO student_profile VALUES
(101, 'Ali', 20, 'M', 'Karachi', 3.20, '2025-01-10', 1),
(102, 'Ayesha', 21, 'F', 'Hyderabad', 3.80, '2024-09-15', 1),
(103, 'Bilal', 22, 'M', 'Lahore', 2.95, '2023-08-20', 0);
SELECT * FROM student_profile;
SELECT * FROM student_profile;
Task 4: Create a teacher table with IDENTITY
Create a table named teacher_demo with these columns:
• teacher_id as INT IDENTITY(1,1) PRIMARY KEY
• teacher_name as VARCHAR(50)
• designation as VARCHAR(30)
• salary as DECIMAL(10,2)
• joining_date as DATE
• is_permanent as BIT
Insert 3 rows without writing teacher_id manually, then run:
SELECT * FROM teacher_demo;
Observation questions:
• What happened to teacher_id?
• Why did SQL Server generate it automatically?
CREATE TABLE teacher_demo (
teacher_id INT IDENTITY(1,1) PRIMARY KEY,
teacher_name VARCHAR(50),
designation VARCHAR(30),
salary DECIMAL(10,2),
joining_date DATE,
is_permanent BIT
);
INSERT INTO teacher_demo (teacher_name, designation, salary, joining_date, is_permanent) VALUES
('Ahmed', 'Lecturer', 45000, '2020-02-10', 1),
('Sara', 'Assistant Prof', 60000, '2019-05-15', 1),
('Usman', 'Lecturer', 40000, '2022-01-01', 0);
SELECT * FROM teacher_demo;
teacher_id is generated automatically.
SQL Server generates it because of IDENTITY, which auto-increments values.
Task 5: Create a course schedule table
Create a table named course_schedule with these columns:
• course_id as VARCHAR(10)
• course_name as VARCHAR(100)
• class_time as TIME
• room_no as SMALLINT
• is_lab as BIT
Insert 3 different courses and then run:
SELECT * FROM course_schedule;
CREATE TABLE course_schedule (
course_id VARCHAR(10),
course_name VARCHAR(100),
class_time TIME,
room_no SMALLINT,
is_lab BIT
);
INSERT INTO course_schedule VALUES
('CS101', 'Database Systems', '09:00:00', 101, 1),
('CS102', 'Programming', '11:00:00', 202, 0),
('CS103', 'Networks', '01:00:00', 105, 1);
SELECT * FROM course_schedule;
Task 6: Create a login record table
Create a table named login_record with these columns:
• record_id as INT IDENTITY(1,1) PRIMARY KEY
• user_name as VARCHAR(50)
• login_datetime as DATETIME
Insert 3 sample records and then run:
SELECT * FROM login_record;
Observation question: Why is DATETIME better here than only DATE or only TIME?
CREATE TABLE login_record (
record_id INT IDENTITY(1,1) PRIMARY KEY,
user_name VARCHAR(50),
login_datetime DATETIME
);
INSERT INTO login_record (user_name, login_datetime) VALUES
('Ali', '2026-04-10 09:15:00'),
('Ayesha', '2026-04-10 10:20:00'),
('Bilal', '2026-04-10 11:30:00');
SELECT * FROM login_record;
Observation:
DATETIME is better because it stores both date and time together.
Task 7: Large text practice
Create a table named project_notes with these columns:
• note_id as INT IDENTITY(1,1) PRIMARY KEY
Instructor: Engr. ABDUL HAFEEZ BABAR (hafeezbabar@[Link])
• title as VARCHAR(100)
• description as VARCHAR(MAX)
• comments as NVARCHAR(MAX)
Insert 2 long text records and then run:
SELECT * FROM project_notes;
CREATE TABLE project_notes (
note_id INT IDENTITY(1,1) PRIMARY KEY,
title VARCHAR(100),
description VARCHAR(MAX),
comments NVARCHAR(MAX)
);
INSERT INTO project_notes (title, description, comments) VALUES
('Project 1', 'This is a detailed description of project 1...', N'Good work'),
('Project 2', 'Another long description for project 2...', N'Needs improvement');
SELECT * FROM project_notes;
Task 8: Run the employee_demo task
Use the employee_demo table already given in the handout. Do the following:
• Create the employee_demo table exactly as provided.
• Insert the two rows given in the handout.
• Add one more employee record of your own.
• Display all records.
SELECT * FROM employee_demo;
CREATE TABLE employee_demo (
emp_id INT,
emp_name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employee_demo VALUES
(1, 'Ali', 30000),
(2, 'Sara', 40000),
(3, 'Usman', 50000);
SELECT * FROM employee_demo;
Task 9: Show selected columns only
From student_profile, display only student_name, cgpa, and city.
SELECT student_name, cgpa, city
FROM student_profile;
SELECT student_name, cgpa, city
FROM student_profile;
Task 10: Show active students only
Display only students whose is_active = 1.
SELECT *
FROM student_profile
WHERE is_active = 1;
SELECT *
FROM student_profile
WHERE is_active = 1;
Task 11: Show teachers with high salary
Display teachers whose salary is greater than 50000.
SELECT *
FROM teacher_demo
WHERE salary > 50000;
SELECT *
FROM teacher_demo
WHERE salary > 50000;
Task 12: Display courses in ascending room order
Display all courses in ascending order of room number.
SELECT *
FROM course_schedule
ORDER BY room_no ASC;
SELECT *
FROM course_schedule
ORDER BY room_no ASC;
Understanding Questions:
1. Difference between CHAR and VARCHAR
CHAR stores fixed-length data, while VARCHAR stores variable-length data.
2. Difference between DATE, TIME, DATETIME
DATE stores only date, TIME stores only time, DATETIME stores both.
3. Why DECIMAL for salary?
Because it stores exact values with decimals, unlike INT.
4. Purpose of BIT
It stores logical values like 0 or 1 (Yes/No).
5. Purpose of IDENTITY
It automatically generates unique values for each row.
6. Why VARCHAR(MAX)?
To store very long text like descriptions.
7. Difference between VARCHAR and NVARCHAR
VARCHAR stores normal text, NVARCHAR supports Unicode (multiple languages).