Database Lab

Manual
Solution

Name: Muhammad Awais

Zia

Reg No :149

Section: C
Exercise 1.1

Compare the traditional file processing system with the database

management system with the help of an example.

Traditional File System Database Management System

File System is a general, easy- The database management

to-use system to store system is used when
general files which require security constraints are
less security and constraints. high.

Security is low in File Security is high in the

Management System. Database Management
System.

Data Redundancy is more in Data Redundancy is less in a

a file management system. database management
system.

Data Inconsistency is more in Data Inconsistency is less in

the file system. a database management
system.

Centralization is hard to get Centralization is achieved in

when it comes to File Database Management
Management System. System.

Exercise 2.1
a)
1. Each student must be advised by exactly one Professor. However, each
Professor must be advised by one or more Students.

2. Each Professor must teach at least one Class. However, each class must
be taught by exactly one Professor.

b)

1. Each Sales rep must be written one or more Invoice. However, each
Invoice must be written by exactly one Sales rep.

2. Each Invoice must generate exactly one Customer. However, each

customer must be generated by at least one Invoice.

3. Each Department must be assigned by exactly one Sales rep. However,

each Sales rep must be assigned to at least one Department.

Exercise 5.1

CREATE TABLE DEPARTMENT

DID int PRIMARY KEY CHECK(DID>0),

Dname varchar(255) NOT NULL,

Location varchar(255) CHECK(len(Location)<6)

);
CREATE TABLE Employee

EmpId int PRIMARY KEY CHECK(EmpId>0),

Fname varchar(255) NOT NULL CHECK(len(Fname)<6),

Lname varchar(255) NOT NULL,

Salary int CHECK(Salary>0),

Date_of_joining date,

D_O_B date ,

DID int Foreign key references DEPARTMENT(DID),

);

Output:

Exercise 6.1

1. SELECT * FROM Employee where F_name ='ali';

2. SELECT F_Name, L_name, Salary FROM Employee;

3. SELECT F_Name, L_name FROM Employee where salary BETWEEN 30000 and
40000

4. SELECT F_Name FROM Employee where Dep_Id in(10,20)

5. SELECT F_Name FROM Employee where F_Name LIKE 'a%'

6. SELECT Employee_ID, F_Name FROM Employee order By Employee_ID asc

Records used for Exercise 7

Exercise 7.1

Q1: select count(EmpId)as employe,DID from Employee group by(DID)

Output:
Q2: select max(salary)as salary, DID from Employee group by DID

Output

Exercise 7.2

Q1: select max(salary) from Employee where Salary<>(select max(salary)from employee)

Output
Q2: select min(salary) from Employee where salary<>(select min(salary) from Employee)

Output

Q3: select Fname from Employee where salary>(select avg(salary) from employee)

Output

Q4: select Fname from Employee where salary=(select max(salary) from employee)

Output

Q5: select fname,Lname from employee where salary>(select min(salary) from Employee
where DID=11)
Output

Exercise 8.1
Already solved in the lab manual.

Exercise 9.1

Q1:

⚫ Books

⚫ Authors
⚫ Staff

⚫ Members

⚫ Shelves

⚫ Block

Q2:

Q3:
1. Each track must contain exactly one book. However, each book must have exactly one
track.

2. Each book may have at least one author. However, each author may have one or more
books.
3. Each book must be issued to at least one member. However, each member may issue one
or more books.

4. Each member must be an issue by exactly one staff. However, each staff may issue at least
one member.

Q:4

book_data books
book_id b_ID
author_ID Book_name

author
author_ID
author_name

book_issue
staff_ID member
mem_ID m_ID
book_ID m_name
issue_date mem_date
due_date contact_no
retrn_date

staff
staff_ID
staff_name

Exercise 10.1

Q:4

select e1.emp_id,e1.First_name,e2.First_name as manager from Employee e1

inner join Employee e2 on e1.Mangager=e2.emp_id

Q:2
2

Q:3

select e1.emp_id,e1.First_name,e1.Last_name,j.Job_Id,j.Function_type from Employee e1

left outer join Job j on e1.Job_id=j.Job_Id

Output

Q:4

select e1.emp_id,e1.First_name,e1.Last_name,dep.Name from Employee e1

left outer join Department dep on e1.Department_id=dep.Dep_Id where dep.name

in('Sales','Operations')

Output
:

Exercise 11.1

Question 1:

Query to create view

select * from Employee where Department_id=30

Output

Query to update data through a view

Output

update vw_employee set First_name='jhonny' where First_name='kevin'

'

Question 2:

Query to create view using join

create view vw_employee as select emp1.*,dep.Name as Department_Name from Employee

emp1 left join Department dep on emp1.Department_id=dep.Dep_Id

where(dep.Name='Sales')

Output
Query to update data through the view

update vw_employee set emp_id=1000 where First_name='kevin'

Output

Exercise 12.1

Question 1:

Create procedure get_grades(@name varchar(60))

as

begin

select stu.reg_No,stu.Name,s.cour_id as CourseCode ,s.grade as Grades from student_info stu

inner join study s on stu.reg_No=s.stu_id where stu.Name=@name

end

get_grades 'ali'

Output
Question 2:

Procedure to get minimum

CREATE procedure get_min

as

begin

select Name,CGPA as Minmumgpa from student_info where CGPA=(select min(CGPA) from

student_info)

end

Output

Procedure to get Maximum

CREATE procedure get_min

as

begin

select Name,CGPA as Maximum from student_info where CGPA=(select max(CGPA) from

student_info)

End

Output

Question 3:

create procedure courses_info(@name varchar(60))

as

begin

select i.Name,c.courseid,c.coursename,i.CGPA from student_info i inner join study s on

i.reg_No=s.stu_id

inner join course c on c.courseid=s.cour_id where i.Name='ali'

End

exec courses_info 'ali'

Output
Question 4:

alter procedure get_info(@id int)

as

begin

Declare @avg float

Declare @cgpa float

select @avg= avg(CGPA) from student_info

select @cgpa= CGPA from student_info where reg_No=@id

if @avg=@cgpa

print 'on avg'

else if @cgpa>@avg

print 'above avg'

else

print 'below avg'

end
get_info 174

Output