Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Total Marks:
Obtained
Marks:
DATABASE SYSTEM LAB
LAB TASK
Submitted To: Maa’m Bushra
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Student Name: Fajr Syed
______________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Reg. Number: 2480177
______________________________________________________________________________________________________________________________________________________________________________________________________________________________________
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Question 1: Create a database named College and define its schema
by creating a table Student with attributes id, name, and age. Set id as
the primary key and apply a constraint so that age cannot be NULL.
Insert a few records (instances) into the table and try to insert a record
with NULL age to observe the constraint limitation.
1. CREATE DATABASE College;
2. USE College;
3. CREATE TABLE Student (
4. id INT PRIMARY KEY,
5. name VARCHAR(50),
6. age INT NOT NULL
7. );
8. INSERT INTO Student VALUES (1, 'Rahul', 20);
9. INSERT INTO Student VALUES (2, 'Anita', 22);
[Link] INTO Student VALUES (3, 'Vikram', 19);
11. INSERT INTO Student VALUES (4, mishhkat, 20); [Link]
INTO Student VALUES (5, arham, 22);
[Link] INTO Student VALUES (5, sudais, 19);
[Link] * FROM Student;
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Question 2: Insert at least 5 Student Records, then show the schema
vs instance. 1.
INSERT INTO Student VALUES (1, 'Rahul', 20);
2. INSERT INTO Student VALUES (2, 'Anita', 22);
3. INSERT INTO Student VALUES (3, 'Vikram', 19);
4. INSERT INTO Student VALUES (4, mishkat, 20);
5. INSERT INTO Student VALUES (5, arham, 22);
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Question 3: Create a database named University and design its
schema by creating a table Students with the following attributes:
roll_number, name, marks, grade, and city. Define roll_number as the
primary key to uniquely identify each student. Apply appropriate
constraints where necessary (for example, marks should not be
NULL). Insert at least three records (instances) into the table and test
any constraint limitations by attempting an invalid insertion
CREATE DATABASE University;
USE University;
CREATE TABLE Students (
roll_number INT PRIMARY KEY,
name VARCHAR(50),
marks INT NOT NULL,
grade CHAR(2),
city VARCHAR(50)
);
INSERT INTO Students VALUES (101, 'mubarrah', 85, 'A', 'isb');
INSERT INTO Students VALUES (102, 'arham', 78, 'B', 'lhr');
INSERT INTO Students VALUES (103, 'sudais', 92, 'A+', 'khi');
SELECT * FROM Students;
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Question 4: Write SQL SELECT queries on the Students table to
perform the following:
● Display all records from the Students table.
● Display the distinct cities of students (no repeated city names)
● Display the records of students whose marks are greater than 80.
● Observe and compare the results of each query.
1. SELECT * FROM Students;
2. SELECT DISTINCT city FROM Students;
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
3. SELECT * FROM Students WHERE marks > 80;
Question 5: Explain with an example how conceptual schema differs
from internal schema in the case of University Database.
1. Conceptual schema:
CREATE TABLE Students ( roll_number
INT PRIMARY KEY, name
VARCHAR(50),
marks INT NOT NULL,
grade CHAR(2),
city VARCHAR(50)
);
BS SE 4B SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
2. Internal schema
BS SE 4B SZABIST-ISB