LAB 1 – INTRODUCTION (TRADITIONAL FILE SYSTEM VS
DATABASES)
Exercise 1.1:
Compare the traditional file processing system with database management system with help of
an example.
Solution:
TRADITIONAL FILE SYSTEM DATABASE MANAGEMENT SYSTEM
It stores data in a group of files. Files data are Data is stored permanently. Data is
dependent on each other. independent. Multiple users can share data at
the same time.
Stores and arranges computer files Data dictionary management, modifies,
deletes and inserts data.
Each program defines and manages its data. Controls multi-user access management
Data redundancy ( each application has its Reduction of redundancy. Performance is
own data file so, same data may have to be good.
recorded and stored in many times).
LAB 2 – INTRODUCTION TO RDBMS
Exercise 2.2:
Write business rules from the following ER Diagrams
a)
Solution:
1. Each PROFESSOR must advises one or more STUDENTs. However, each STUDENT
must be advised by exactly one PROFESSOR.
2. Each PROFESSOR must teaches one or more CLASS. However, Each CLASS must
be taught by exactly one PROFESSOR.
b)
Solution:
1. A SALESREP must writes one or more INVOICE. However, each INVOICE must be
written by exactly one SALESREP.
2. Each INVOICE must generates exactly one CUSTOMER. However, A CUSTOMER
must be generated by one or more INVOICE.
3. Each SALESREP is assigned to one or more DEPARTMENTs. However, Each
DEPARTMENT must be assigned by exactly on SALESREP.
LAB 5 – DATA DEFINITION LANGUAGE (DDL)
EXERCISE 5.1
Consider the following Employee and Department tables:
DID Dname Location
11 CS East
12 EE West
13 HBS East
EmpId Fname Lname Salary Date-of- D-O-B DID
joining
1 Ali Murtaza 30000 12-01-2012 20-01-1984 11
2 Omer Kashif 20000 15-06-2012 19-03-1985 11
3 Ayesha Asif 30000 14-02-2011 10-06-1976 13
4 Amna Bilal 25000 07-03-2010 28-03-1988 12
Create above tables in SQL with following constraints:
Constraint No 1: EmpId > 0, DID > 0
Constraint No 2: Fname < 7 characters, Location < 6 characters
(HINT Declare as VarChar, and check (len(col name) < 7)
Constraint No 3: Salary > 0
Constraint No 4: EmpId PK, DID FK in employee table
Constraint No 5: DID pk in department table
Solution:
create database lab5
create table Employee
(
EmpId int CHECK(EmpId>0) Primary Key,
Fname varchar(50) CHECK(len(Fname)<7) Not Null,
Lname varchar(50) Not Null,
Salary int CHECK(Salary>0),
Date_Of_Joinning Date,
D_O_B DATE Not Null,
);
Alter table Employee
ADD DID int CHECK(DID>0);
Alter table Employee
ADD foreign key (DID) References Department (DID);
Create table Department
(
DID int CHECK(DID>0) Primary Key,
Dname varchar(100) Not Null,
Location varchar(50) CHECK(len(Location)<6) Not Null
);
EMPLOYEE TABLE:
DEPARTMENT TABLE:
LAB 6 – DATA MANIPULATION LANGUAGE (DML)
EXERCISE 6.1:
Write the following queries for above table:
Q1. List out the details of Ali
ANS: Select * from Employee where F_name='Ali';
Q2. List out the employee’s annual salary with their names only
ANS: Select F_name, Salary from Employee;
Q3. List out employee’s name who are earning salary between 30000 and 40000
ANS: Select F_name from Employee where Salary BETWEEN 30000 AND 40000;
Q4. List out employee’s name who is working in department 10 or 20
ANS: Select F_name from Employee where Dept_ID IN (10, 20);
Q5. List out employees name whose name start with ‘a’
ANS: Select F_name from Employee where F_name LIKE 'a%';
Q6. List out employee id last name in ascending order based on employee id
ANS: Select L_name from Employee Order by Employee_ID asc;
LAB 7 – SQL FUNCTIONS AND SUBQUERIES
EXERCISE 7.1
Write the following SQL queries:
Q1. How many employees who are working in different department wise in the organization.
ANS: Select count(*), deptname from Employee group by deptname;
Q2. List out department wise maximum annual salaries, minimum anuual salaries and average
annual salaries of employees.
ANS: Select Max (Salary), Min (Salary), and Avg (Salary), deptname from
Employee group by deptname;
EXERCISE 7.2:
Write the following SQL Queries using subqueries.
Q1. Display the employee details who got second highest salary
ANS: select * from Employee where Salary= (select MAX(Salary) from
Employee where Salary<>( select MAX(salary) from Employee));
Q2. List out the employees who earn more than the lowest salary in department 11
ANS: Select * from Employee where Salary> (Select Min (salary) from Employee
where deptid=11);
Q3. Find out the employees who earn greater than the average salary of their department.
ANS: Select * from Employee x where x.Salary>(Select Avg(salary) from
Employee where deptid=x.deptid);
Q4. Display the employee who got the maximum salary
ANS: Select * from Employee where Salary=(select MAX(salary) from Employee);
Q5. List out the employees who earn more than every employee in department 11
ANS: Select *from Employee where Salary >all (Select salary from Employee
where deptid=11);
LAB 9 – CASE STUDY
LIBRARY MANAGEMENT SYSTEM
EXERCISE 9.1:
1. Write down the name of entities you think are required.
Books
Members
Shelf
Staff
2. Draw complete E-R Diagram showing all the relationship between entities in part a.
Staff
Books Members
Shelf
3. Write Business Rules considering the E-R Diagram you made in part b.
Each member may borrow one or more book. However, a book
may be borrowed by one or more members.
Each book must be available on exactly one shelf. However, A
Shelf must be available for one or more book.
A Staff must maintain one or more book. However, each book
must be maintained by one staff.
A Staff may writes one or more member. However, each member
must be written by exactly one staff.
4. Resolve many to many relationships if any and draw new E-R diagram if different from part
b.
Book_Name Members_Name
Book_id Members_id
Issue_date Members_id
Book_id
Fine
Books Book_issue Members
Shelf_No Due_date
Staff_id
v
Author_Name Joinning_date
Return_date Contact
Shelf Staff
v
Staff_Name Staff_id
Shelf_No Block_no
SQL IMPLEMENTED ER-DIAGRAM:
5. Explain how your database solution is efficient as compared to existing system.
ANS: All librarians currently operate all its administrations using handwritten
forms or slips stored in drover (files). The handwritten information may also
cause some clashes in the records such as missing a particular slip or
maintaining the records in large librarian. So to avoid the problem arises, we
need an automated system that keeps a track of all the records & related
information. This library management system database provides some
following benefits points:
It provides “better & efficient” service to members.
Reduce the workload of employee.
Immediate retrieval of information.
Better storage capacity.
Quick sorting of information.
Accuracy of data that is no redundancy hence no inconsistency.
Easy to update the information
Complete management of records of book donor, vender, books, subject,
rack shelf etc.
LAB 10 – JOIN ALGORITHMS
EXCERCISE 10.1:
Write the following SQL queries:
1. List out employees with their manag er names.
ANS: Select t1.first_name as EmployeeName, t2.first_name as
ManagerName from Employee t1 inner join Employee t2 on
t1.Employee_ID=t2.Manager_ID;
2. How many employees who are working in different departments and display with
department name.
ANS: Select count(*) as TotalEmployee, Department.name from Employee
inner join Department on
Employee.Department_ID=Department.department_ID group by
Department.name;
3. Display employees with their designations.
ANS: Select employee.Firstname, Department.name, job.Job.Function
from Employee inner join Department on
Employee.Department_ID=Department.Department_ID inner join Job on
Employee.Job_Id=Job.Job_id;
4. Display all employees in sales or operation departments.
ANS: Select Employee.firstname, Department.name from Employee inner
join Department on Employee.Department_ID=Department.Department_ID where
Department.name in ('sales', 'operation');
5. Display all employees in sales or operation departments.
ANS: Select Employee.firstname, Department.name from Employee inner join
Department on Employee.Department_ID=Department.Department_ID where
Department.name in ('sales', 'operation');
LAB 11 – SQL VIEWS
EXERCISE 11.1:
1. Create views on any sample database, and update the contents through views.
CREATING VIEW;
Create View EmployeebySalary
as select * from employee where Salary=32000;
Select * from EmployeebySalary;
UPDATING VIEW;
Update EmployeebySalary set fname= 'Akram' where empid=1;
Select * from EmployeebySalary;
2. Create view using Joins for two or more than two tables, try to updates values using
views and analyze your results.
CREATING VIEW ON JOINS;
Create view emp_job_dep as
(Select employee.Fname, Department.Dname, job.Job_name from employee
inner join Department on Employee.DID=Department.DID inner join Job on
Employee.Job_id=Job.Job_id);
Select * from emp_job_dep;
UPDATING VIEW;
Update emp_job_dep set Job_name ='tester' where Fname='amna';
Select * from emp_job_dep;
Conclusion;
If a view based on multiple tables, and if we update the view it may not
update the underlying base tables correctly.
To correctly update a view that is based on multiple table instead of
triggers are used.
LAB 12 – SQL PROGRAMMING (STORED PROCEDURES)
EXERCISE 12.1:
Create tables as mentioned before questions as per given schema, Assign data types and
enter data as per your understanding
For Q No 1: St1(St_ID, St_Name, St_CourseCode, St_Grade)
Q NO 1: Create a stored procedure that takes name of the student as input and display the
grades of that student with course code.
CREATING PROCEDURE;
Create Procedure student_grades (@s_name varchar(50))
AS
BEGIN
Select student.St_id,student.St_Name,Study_info.Cr_Code as
St_CourseCode,Study_info.St_Grade from student inner join Study_info on
student.St_id=Study_info.St_id where St_Name=@s_name;
END
Execution;
exec student_grades'akram';
For Q No 2: St2(St_ID, St_Name, St_Marks, St_CGPA)
Q NO 2: Create a stored procedure that displays the names of students who got maximum and
minimum CGPA.
CREATING PROCEDURE;
Create Procedure max_minCGPA
AS
BEGIN
Select St_id,St_Name,St_marks,St_CGPA as MaximumCGPA from student where
St_CGPA =(Select Max(St_CGPA) from student);
Select St_id,St_Name,St_marks,St_CGPA as MinimumCGPA from student where
St_CGPA =(Select Min(St_CGPA) from student);
END
Execution;
exec max_minCGPA;
For Q No 3:
St3(St_ID, St_Name)
Course (Cr_Code, Cr_Name)
Reg (Reg_St_ID, Reg_Cr_Code, Reg_Marks)
Q NO 3: Write a stored procedure that takes student name as input and displays course names
along with marks for that student
CREATING PROCEDURE;
Create Procedure student_prog (@stu_name varchar(50))
AS
BEGIN
Select
student.St_id,student.St_Name,Study_info.Cr_Code,Course.Cr_Name,Study_
info.Cr_Marks from student inner join Study_info on
student.St_id=Study_info.St_id inner join Course on
Study_info.Cr_Code=Course.Cr_Code where St_Name=@stu_name;
END
Execution;
exec student_prog 'ahmed';
LAB 14 – SQL SERVER INDEX STRUCTURES
Lab# 14 EXCERCISE
Q1: write SQL query to create clustered index on employee table.
CREATING CLUSTERED;
Create UNIQUE CLUSTERED INDEX epm on employee(empid);
select * from Employee where empid=4;
Q2: write SQL query to create non-clustered index on employee table.
CREATING NON-CLUSTERED;
Create NONCLUSTERED INDEX r_salary on employee(Salary);
Select*from Employee WITH(INDEX(r_salary)) where Salary=35000;
LAB 16: BACKUP AND RESTORE
Exercise:
Create full back of already created database on one PC and use the backup created to restore
database on another PC.
FULL BACKUP;
RESTORE;
