Scribd
Upload
13 views4 pages

Database Management System Lab

This document is a lab assignment for a Database Management System course, submitted by Aadeel ul Huq. It includes SQL commands to create a 'Staff' table, insert data, and perform various queries and updates on the staff records. The assignment demonstrates the use of SQL for managing staff information in a database at Bahria University, Islamabad.

Uploaded by

m.azibakram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views4 pages

Database Management System Lab

This document is a lab assignment for a Database Management System course, submitted by Aadeel ul Huq. It includes SQL commands to create a 'Staff' table, insert data, and perform various queries and updates on the staff records. The assignment demonstrates the use of SQL for managing staff information in a database at Bahria University, Islamabad.

Uploaded by

m.azibakram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Database Management System Lab

Assignment: 01
SUBMITTED TO:
Ms. Asifa Bibi

Name: Aadeel ul huq

Class: BSCS 4A
Enrollment no: 01-134232-225

DEPARTMENT OF COMPUTER
SCIENCE
BAHRIA UNIVERSITY | ISLAMABAD H- 11
CAMPUS
DROP TABLE IF EXISTS Staff;

CREATE TABLE Staff (


Staff_ID CHAR(9) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
MiddleName VARCHAR(10),
LastName VARCHAR(50) NOT NULL,
DOB DATE NOT NULL,
HomeAddress VARCHAR(100) NOT NULL,
Department VARCHAR(50),
ProjectLocation VARCHAR(100),
ProjectID INT,
ControlDeptID INT,
ManagerLastName VARCHAR(50),
SupervisorFirstName VARCHAR(50),
SupervisorLastName VARCHAR(50),
StaffSSN INT UNIQUE
);

INSERT INTO Staff (Staff_ID, FirstName, MiddleName, LastName, DOB, HomeAddress, StaffSSN)
VALUES
('101', 'Ayesha', 'M', 'Khan', '1990-02-14', '23 Maple St, NY', 983),
('102', 'Omar', 'T', 'Ali', '1985-09-30', '78 Birch St, CA', 726),
('103', 'Hamza', 'K', 'Raza', '1992-07-12', '456 Cedar St, TX', 512),
('104', 'Fatima', 'L', 'Hassan', '1988-03-25', '901 Oak St, FL', 348);
SELECT FirstName, MiddleName, LastName, HomeAddress FROM Staff WHERE Department =
'Finance';

UPDATE Staff SET ProjectLocation = 'Karachi', ProjectID = 201, ControlDeptID = 400,


ManagerLastName = 'Ahmad' WHERE Staff_ID = '101';
UPDATE Staff SET ProjectLocation = 'Karachi', ProjectID = 202, ControlDeptID = 401,
ManagerLastName = 'Hussain' WHERE Staff_ID = '102';

SELECT FirstName, MiddleName, LastName, HomeAddress FROM Staff WHERE Department =


'Finance';

UPDATE Staff SET ProjectLocation = 'Karachi', ProjectID = 201, ControlDeptID = 400,


ManagerLastName = 'Ahmad' WHERE Staff_ID = '101';
UPDATE Staff SET ProjectLocation = 'Karachi', ProjectID = 202, ControlDeptID = 401,
ManagerLastName = 'Hussain' WHERE Staff_ID = '102';
SELECT ProjectID, ControlDeptID, ManagerLastName, HomeAddress, DOB FROM Staff WHERE
ProjectLocation = 'Karachi';

UPDATE Staff SET SupervisorFirstName = 'Bilal', SupervisorLastName = 'Rehman' WHERE Staff_ID =


'101';
UPDATE Staff SET SupervisorFirstName = 'Zainab', SupervisorLastName = 'Tariq' WHERE Staff_ID =
'102';
UPDATE Staff SET SupervisorFirstName = 'Yasir', SupervisorLastName = 'Mehmood' WHERE Staff_ID =
'103';
UPDATE Staff SET SupervisorFirstName = 'Nadia', SupervisorLastName = 'Saeed' WHE Staff_ID = '102

UPDATE Staff SET SupervisorFirstName = 'Yasir', SupervisorLastName = 'Mehmood' WHERE Staff_ID =


'103';
UPDATE Staff SET SupervisorFirstName = 'Nadia', SupervisorLastName = 'Saeed' WHERE Staff_ID =
'104';

SELECT FirstName, LastName, SupervisorFirstName, SupervisorLastName FROM Staff;

SELECT StaffSSN FROM Staff;

SELECT StaffSSN, Department FROM Staff;

You might also like