Last updated date: 04/10/2016

COT-313 (Database Systems)

IT-216 (Database Management Systems)
Lab Practical

Instructions: Type below string in UNIX terminal

$mysql u nituser p nituser (ENTER) // for Log in into MYSQL

mysql> mysql dump u nituser p nituser COMPANY> xyz.sql // backup or dump
$mysql u nituser p nituser <xyz.sql // extract dump in MYSQL

Experiment No.-01
Installation of MYSQL/ ORACLE (Refer Video Tutorial)
http://www.spoken-tutorial.org/
http://www.spoken-tutorial.org/keyword-search/?q=SQL
(Show Video)

Experiment No.-02

Focus Area: Table Creation and various key constraints (PK, Not Null, FK etc.)

Create a relational database schema for below given database, described by the
following relations and Insert tuples.

(a). STUDENT(Roll_no, Name, DOB, Address)

DEPARTMENT (Dept_name, Dept_no, Location, Phone)
FACULTY(Faculty_name, Faculty_no, Address, Contact_no)

(b). SUPPLIER(Sno, Sname)

PART(Pno, Pname)
PROJECT(Jno, Jname)
SUPPLY(Sno, Pno, Jno)

(c). EMPLOYEE (Fname, Lname, Emp_no, Street, City)

COMPANY (Name, Company_no, City)
WORKS (Emp_no, Comp_no, Salary, Start_date)
MANAGES (Emp_no, Manager_no)

1
 Experiment No.-03

Focus Area: Database & Table Creation, various Key Constraints, update and delete
query.

Create a relational database schema for a Minor-Project, described by the

following relations.

STUDENT (Rollno, Name, Semester, Degree, Contact no, email_id, Guide_No)

GUIDE (Guide_name, Guide_No, Guide_reserach_domain, Email_id)
PROJECT (ProjectNo, Project_title, Project_Area, Start_dt, Guide_No)
GROUP (Group_Code, Roll_No )
PROJECT_GROUP (Group_Code, Project_No, No_of_students)

Specify the following queries in SQL on the database schema Minor_Project.

(1). Change the data type of attribute Rollo (StudentTable), from Number (10) to
Varchar2 (10).
(2). Add a Check constraint on Gender attribute(StudentTable) such that this attribute
accept only (M, F, m, f) values.
(3). Add a constraint on Degree (Studenttable), such that no null values be inserted.
(4). Find the list of guide, who are guiding more than two student groups.
(5). Find the list of project no, project name & name of guide, in domain of Database.
(6). Update the project start_date with 01 July 2015 for student, who enrolled in 7th
semester.
(7). Update guide details of a roll no 110011, new guide is Ram Mohan & id
112200.
(8). Remove the Guide details, guide no is 112211 and assign guide no 133113 to
all respective students project group.

2
 Experiment No.-04

Focus Area: Database & Table Creation, update, retrieval.

Create a relational database schema for a University, described by the following

relations and Insert tuples.

CLASSROOM (building, room_number, room_type, capacity)

DEPARTMENT(dept name, dept_no, building,room_number, budget)
COURSE(course id, title, dept_no, credits)
INSTRUCTER(I_ID, name, dept_no, salary)
SECTION(course id, sec id, semester, year, building, room number, time slot id)
TEACHES(I_ID, course id, sec id, semester, year)
STUDENT(S_ID, name, bdate, email_id, total_credit, deptno)
TAKES(I_ID, course_id, sec_id, semester, year, grade)
ADVISOR(S_ID,I_ID)
TIME SLOT(Time_slot_id, day, start time, end time)
PREREQ(Course_id, Prereq_id)

Specify the following queries in SQL on the database schema University.

(1). Update, Salary of each instructor, who is working in Department EC.

(2). Update the Room no for Course scheduled on 12.30-01.25 from LHC-204 to
LHC-102
(3). Retrieve the names of all instructors, along with their department names and
department building name.
(4). Find the names of all instructors in the Computer Science department who have
salary greater than $70,000.
(5). For all instructors in the university who have taught some course, find their
names and the course ID of all courses they taught
(6). Find the titles of courses in the Comp. Sci. department that have 3 credits.
(7). Find the course name, credits running in semester 4.
(8). List the section name, course name whose class are engaged in room no LHC-
102.
(9). Find the pairs of Student ID, Supervisor ID where supervisor is teacher and
advisor of the student, also retrieve the name department.
(10). Retrieve the list of Rooms where the entire all the classes are scheduled before
12.00 am.
(11). Retrieve the list of room number & time slot where all classes of Computer Sci.
Semester 7th are scheduled.
(12). Retrieve the Course Name, Course no, Semester, Room no, time slot, taught by
Prof ABC.

3
 Experiment No.-05

Focus Area: Data Retrieval based on conditions, Aggregation functions.

Specify the following queries in SQL on the database schema University

(experiment no 04)

(1). Find the names of all departments whose building name includes the substring
Watson.
(2). Find the names of instructors with salary amounts between $90,000 and
$100,000.
(3). To list in alphabetic order all instructors in the Physics department.
(4). Retrieve the list of students, who are born on 1990.
(5). Retrieve the list of courses taught by teacher whose name consist R as 4rth letter.
(6). Find the IDs of all students who were taught by an instructor named Einstein
makes sure that, there are no duplicates in the result.
(7). Find the average salary of instructors in the Computer Science department.
(8). Find the total number of course in University.
(9). Find the total number of instructors who teach a course in the Spring 2010
semester.
(10). Find the average salary of instructors in each department.
(11). Find all instructors earning the highest salary (there may be more than one with
the same salary).
(12). Find the number of instructors in each department who teach a course in the
spring 2010 semester.

4
 Experiment No.-06

Focus Area: Aggregation (Average, count, max, min), and condition based retrieval.

Specify the following queries in SQL on the database schema University

(experiment no 04)

(1). Find the average salary of instructors in those departments where the average
salary is more than $42,000.
(2). Find the average salary of instructors in the university.
(3). Find the total number of student enrolled in each of department in university.
(4). Find the maximum enrolment, across all sections, in autumn 2009.
(5). For each course section offered in 2009, find the average total credits (tot cred) of
all students enrolled in the section, if the section had at least 2 students.
(6). Find the total number of students with each grade.
(7). Find the lowest, across all departments, of the per-department maximum salary
computed by the preceding query.
(8). For each department, find the maximum salary of instructors in that department.
You may assume that every department has at least one instructor.
(9). Retrieve the total count of student in each department who earned credit more
than 8 overall.
(10). Retrieve the name of student from Computer Science & EC who earn total credits
more than 7.
(11). Find the name of classrooms shared by EC dept & Computer Science dept day
wise.
(12). List the name of Instructor, Course & total no of student in where student more
than 20% students earn 9 credits.
(13). List the name of faculty, dept of faculty, course name who are teaching a subject
of other department.

5
 Experiment No.-07

Focus Area: Condition based Data retrieval.

Specify the following queries in SQL on the database schema University

(experiment no 04)

(1). Retrieve the name department which has 5000 rs of budget; also retrieve the
number of classrooms in dept and capacity of classroom.
(2). List the name computer science faculties, whose classes are slotted after 12.35 pm
in Computer Science dept.
(3). List the name computer science faculties, whose classes are slotted after 12.35 pm
in Computer Science dept and EC dept of 6th and 8th Semester.
(4). Find the IDs and names of all students who have not taken any course offering
before Spring 2009.
(5). Find the names of all students who have taken at least one Comp. Sci. course;
make sure there are no duplicate names in the result.
(6). Find the names of course on which more than 20 student earned less than credits
5 on 3rd Year.
(7). List the name of instructors who are not teaching any 8th semester course.
(8). List of the all students whose advisor belongs to other department.
(9). Retrieve the name of student, subject name taught by an instructor, who is not an
adviser to any of the students.

6
 Experiment No.-08

Focus Area: Create database and insert tuples, retrieval, orderedby, substring
matching.

Create a relational database schema for a Company, described by the following

relations,

EMPLOYEE (Fname, Lname, Emp_id, Bdate, Address, Gender, Salary, Super_ssn, D_no)
DEPT (D_name, D_no, Mgr_ssn, Mgr_start_date )
DEPT_LOCATION(D_no, D_location )
PROJECT (P_name, P_number, P_location, D_no )
WORKS_ON (Essn, P_no, No_of_Hours )
DEPENDENT (Essn, Dependent_name, Gender, Bdate, Relationship)

And Insert tuples as shown in below database state.

7
Specify the following queries in SQL on the database schema Company

(1). Company decided to give a raiseon salaries of every employee, working on the
ProductX project by 10 percent.
(2). List the name and address of all employees who work for the Research
department.
(3). Find the names and addresses of all employees who work on same department.
(4). Retrieve the name & employee no. of employees, whose salary is between
$30,000 and $40,000.
(5). Find the names of all employees who are directly supervised by Franklin.
(6). Retrieve the list of female employee working in the department number 4 and
supervised by Frankline.
(7). List the names of all employees who have a dependent with the same first name
as themselves.
(8). Retrieve the names of all employees in department 5 who work more than 10
hours per week on XYZ Project.
(9). Find the list of employees who lives in the same city as their supervisor.
(10). Retrieve a list of employees and the projects they are working on, ordered by
department and within each department, ordered alphabetically by last name and
then by first name.
(11). List the names of employees who works on all project controlled by department
number 4.

Experiment No.-09

Focus Area: Data Retrieval with substrings, Aggregate functions (count, average)

8
Specify the following queries in SQL on the database schema Company

(Experiment no. 08)

(1). Retrieve the employee details, whose first name start with A.
(2). Retrieve the employee details, whose third character of first name S.
(3). Retrieve all employees, whose address is in Houston, Texas.
(4). Retrieve the employees, whose supervisor city name start name NEW.
(5). Retrieve the employee name, Employee id, dept no, project no, whose were born
during 1980 and working in a project located at Houston.
(6). Retrieve the total number of employees in company.
(7). Retrieve the total number of Supervisor in organization.
(8). Retrieve the total number projects currently in company.
(9). Retrieve the total no of Female employees in organization, whose salary is more
than 25000.
(10). Find the total number of projects, in which an employee is working.
(11). Find the total number of projects controlled by each of the department.
(12). Retrieve the total no male & female employee in the Organization.
(13). Retrieve the average salary of all female employees in each department.
(14). Retrieve the employee id & no. of projects, an employee is working.
(15). Retrieve project name, total no. of employee and, total no. of hours working.
(16). Retrieve the total no of employee working, for each of the department.
(17). Retrieve the list of supervisor and number of employees supervised by each of the
supervisors.
(18). For each project controlled by department no 5, find the average number of hours
an employee is working.

Experiment No.-10

Focus Area: Queries of aggregate (sum, min, max, and average), Conditions based
9
Specify the following queries in SQL on the database schema Company

(Experiment no. 08)

(1). Find the sum, the maximum, the minimum and the average salary in the
company.
(2). For each department, retrieve the department name and the average salary of all
employees working in that department.
(3). For each project, retrieve the project number, the project name and the number of
employees who work on that project
(4). For each project, list the project name and total no of hours per week (by all
employees) spent on the project.
(5). Retrieve the Project name & no of employee, in which least number of employees
are working.
(6). Retrieve the name of department name & department No, number of projects,
who are controlling highest no of projects.
(7). For each project in which more than two employees work, retrieve the project
number, the project name, and the number of employees working in the project.
(8). For each project, retrieve the project number, the project name, and the number of
employees from department 1 who work on the project.
(9). For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than $30,000.
(10). Retrieve the names of employees who work on all projects controlled by
department No 04.
(11). Find the names of all employees who earn more than average salary of company.
(12). Make a list of project numbers for projects that involve an employee whose last
name is Smith, either as a worker or as a manager of the department that
controls the project.
(13). List of employee, who earns 10000 more than the average salary of company.
(14). List of employees, on each of department who earn 5000 more than minimum
salary of each department salary.

Experiment No.-11
Focus Area: Exist, All, Any, View

10
Specify the following queries in SQL on the database schema Company

(Experiment no. 08)

(1). Retrieve the names of the employee who do not have supervisors.
(2). Retrieve the names of all employees who work on every project.
(3). List the last name of all department managers who have no dependent.
(4). List the names of managers who have at least one dependent.
(5). Retrieve the names of employees who do not work on any project
(6). Retrieve the name of department name & department No, number of employees,
Number of projects, who is controlling highest no of projects.
(7). List the name of each employee who works on some project controlled by
department number 5.
(8). Retrieve the name of department name & department No, number of employees,
Number of projects, who is controlling highest no of projects.
(9). Retrieve the department name and department number which is controlling at
least one project and at least 3 numbers of employees are working in it.
(10). List of employees for each project, who are working highest number of hours.
(11). Find the pair of employee and supervisor names, who are working in different
department in company.
(12). A view that has the department name, manager name and manager salary for
every department.
(13). A view that has the project name, controlling department, number of employees,
and total hours worked per week for each project.
(14). A view that has the project name, controlling department name, number of
employees, total hours per week on the project for each project with more than
one employee working on it.

Assignment DB Design

11
Focus Area: Creation of database, Aggregation, Conditional query

A database is being constructed to keep track of the teams and games of a sports league.
A team has a number of players, not all of whom participate in each game. It is desired
to keep track of the players participating in each game for each team, the positions they
played in that game, and the result of the game. Design an ER schema diagram for this
application, stating any assumptions you make. Choose your favourite sport (e.g.,
soccer, baseball, football).

(1). Find the name of Team, who won the maximum number of matches /games.
(2). Retrieve the name of players of each participating team, who played highest
number of games (for a team).
(3). Retrieve the name of players of each team, who played on every match in which
the team is winning team.
(4). Retrieve the name of venues in which each team won at least a game/match.
(5). Find the name of players for each team in tournament that played at least in three
different positions on different games/matches for each team.
(6). Retrieve the Team Name, Player Name, opponent team, player position for all
matches where team is losing side.

12