DATABASE MANAGEMENT SYSTEMS LABORATORY

LAB INTERNAL EXAMINATION

1. Create the following tables with the mapping given below.

a. stu_details (reg_no, stu_name, DOB, address, city)

b. mark_details (reg_no, mark1, mark2, mark3, total)

(i) Alter the table mark_details to add a column average with data type as long.

(ii) Display the months between the DOB and till date.

(iii) drop the column address from the table stu_details.

(iv) Write a trigger to find the sum &avg marks of all the student and insert them in the new table
called student_marks.

(v) retrieve the top 5 rows of the stu_details table

(vi)Apply all the aggregate functions on mark_details table

(vii) Display all details whose names begins with 'a'.

(viii)Write a trigger to calculate the grade of the students basing on the total marks obtained in the
mark_details.

2. Create the following tables with the mapping given below.

a. emp_details (emp_no, emp_name, DOB, address, doj, mobile_no, dept_no, salary).

b. dept_details (dept_no, dept_name, location).

(i) Display the months between the doj and till date.

(ii) Alter the table emp_details to add a primary key constraint on emp_no.

(iii)Display the name and salary of the employee who is getting the highest salary.

(iv)write the queries to illustrate all the join operations on the above tables.

(v) display the names of employees whose address begins and ends with ‘a’

(vi) create a trigger for delete operation on emp_details, the deleted data must be stored in
emp_backup table.

(vii)add 2 more attributes to dept_details in between dept_name and location attributes.

3. A)Create the following tables with the mapping given below.

a. stu_details (reg_no, stu_name, DOB, address, city)

b. mark_details (reg_no, mark1, mark2, mark3, total)

(i). Display only those rows whose total ranges between 250 and 300.

(ii). Drop the table mark_details.

(iii). Delete the row whose reg_no=161.

(iv). Display all details whose names begins with 'a'.

(v).Alter the table stu_details to add a primary key constraint on reg_no.

(vi).Write a trigger to calculate the grade of the students based on the total marks obtained in the
mark_details.

B) Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission,

deptno)

DEPARTMENT(deptno, dname, location)

i. Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.

ii. List the names of employees whose name contain substring ‘LA’.

iii. List the details of employees of salary are greater than or equal to the average salary of
employee table.

iv. Create a view which consists of details of all ‘SALESMAN’.

4.a. Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission,

deptno)

DEPARTMENT(deptno, dname, location)

i. Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.

ii. List the names of employees whose name contain substring ‘LA’.

iii. List the details of employees of salary are greater than or equal to the average salary of
employee table.

iv. Create a view which consists of details of all ‘SALESMAN’.

b. Consider the following tables.

SAILOR(sid, sname, rating, age)

BOATS(bid, bname, colour)

RESERVES(sid, bid, day)

i. Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.

ii. List the sailors in the descending order of their rating.

iii. List the sailors who have reserved for both ‘RED’ and ‘GREEN’ boats.

iv. display the names of sailors who is having higher rating than any other sailor.
5. a. Consider the following relations for order processing database application in a company.

CUSTOMER(custno, cname, city)

ORDER(orderno, odate, custno, ord_amt )

ORDER_ITEM(orderno, itemno, quantity)

ITEM(itemno, unitprice)

SHIPMENT(orderno, warehouseno, ship_date)

WAREHOUSE(warehouseno, city)

i. Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.

ii. Produce a listing: custname , No_of_orders , Avg_order_amount , where the middle column is the
total number of orders by the customer and the last column is the average order amount for that
customer.

iii. List the orderno for orders that were shipped from all the warehouses that the company has in a
specific city.

iv. Demonstrate the deletion of an item from the ITEM table.

6. Consider the following relations for a bus reservation system application:

BUS (ROUTENO, SOURCE, DESTINATION)

PASSENGER (PID, PNAME, DOB, GENDER)

BOOK_TICKET (PID, ROUTENO, JOURNEY_DATE, SEAT_NO)

a. The primary keys are underlined. Identify the foreign keys and draw schema diagram

b. Create the above mentioned tables and populate the tables

c. Include constraint that DOB of passenger should be after 2010

d. Display the passengers who had booked the journey from Bangalore to Chennai on 03-NOV-2014.

e. Create a view that displays the RouteNo, source, destination and journey_date which moves from
Chennai to Pune.

g. Create an index on PID in the passenger table.

7. Consider the following relations for a transport management system application:

DRIVER (DCODE, DNAME, DOB, GENDER) CITY (CCODE, CNAME) TRUCK (TRUCKCODE, TTYPE)

TTYPE can take two values (‘L’,’H’) L-Light H- Heavy

Each truck is assigned a unique truck code.

There can be many trucks belonging to the same truck type.

DRIVE_TRUCK (TRUCKCODE, DCODE, DOT, CCODE)

DOT – Date of Trip

a. Identify the primary and foreign keys and draw schema diagram

b. Create the above mentioned tables and populate the tables

c. Include the constraint as mentioned above and the gender of driver is always 'male'.

d. Develop a SQL query to list the details of each driver and the number of trips traveled.

e. Create an index on truck_code in Drive_truck table

f. Create a view that displays the Driver details and also the city in which he drives a truck

8.Consider the following relational schema for a Loan database application:

Customer (Custid, Custname, Age, phno)

HLoan (HLoanid, Amount, Custid)

VLoan (VLoanid, Amount, Custid)

Where HLoan is Housing loan and VLoan is a Vehicle loan.

a. Identify the primary and foreign keys and draw schema diagram

b. Create the above mentioned tables and populate the tables

c. Display the number of VLoan taken by a particular customer id

d. Display the list of the customerids and total HLoan amount taken.

f. Create a view that keeps track of customer details who have taken both HLoan and VLoan.

g. Create a sequence named Customer_Sequence that gets auto incremented and use it for
inserting Custid values in the Customer table.
9.i)Create the following table with the mapping given below.

book(book_name,author_name,price,quantity).

(i) write a query to update the quantity by double in the table book.

(ii)List all the book_name whose price is greater than Rs.400.

(iii)Retrieve the list of author_name whose first letter is ’a’ along with the book_name and price.

ii) Phone_book(ph_no,name,door_no,street,place).

(i)List the price of the book which one is between the price of 400 & 500.

(ii)Creating Triggers

To Insert Values To Another Table.

10. a)Create the tables as given relational schema below with suitable Data types and Primary,
Foreign key relationships in MySQL Database, insert the data and execute the following queries.
Emp(eno : integer, ename : string, job : string, mgr : integer, hiredate : date, sal : integer, comm. :
integer, deptno : integer)
Dept(deptno : integer, dname : string, location : string)

1. Display all the employee names which starts with B and ends with E
2. Update the salary of all the employees in Research Department to 8000
3. Display all the employees who are working in CHICAGO
4. Display the employee name who is paid highest salary
5. Display the all the employees who are CLERK’s and working in DALLAS
6. Display the annual salary of the employees in descending order
7. Display the employee name whose salary is higher than average salary of all employees
8. Delete all the employees who are located in NEWYORK
9. Display the number of employees working in each department
11.a) Create the tables as given relational schema below with suitable Data types and Primary,
Foreign key relationships in MySQL Database, insert the data and execute the following queries.

Car( CarNo : string , model : string , Year : integer, color : string price : integer)

Owner (OwnerID : integer, OwnerName : string, Mobile : integer, Driving License : string)

CarOwner( OwnerID : integer, CarNo : string )

1. Display all the owners of Sports Car

2. Display all the owners whose car cost is greater than 50000

3. Display the Model , cost of each car owned by Pentu Saheb

4. Display the owner names who have owned a red color car

5. Display the driving license of the owner who has owned sports car

6. Display the owner name who has “Reddy” in the name

12.a) Create the tables as given relational schema below with suitable Data types and Primary,
Foreign key relationships in MySQL Database, insert the data and execute the following queries.

DVD Rentals

Member ( MemberID : integer, MemberName : string , Mobile : integer)

DVD(DVDID : integer, Title : String, Category : string, Cost : integer)

DVDIssue( DVDID : integer, MemberID : integer, IssueDate : Date)

1. List the titles of Action movies

2. List the customer name who have been issued Love Story category DVD

3. List all the titles with atleast 6 characters and belongs to action category

4. List how many DVD’s have been taken by each customer

5. List all the titles who cost is greater than 250

6. List all the customer to whom DVD was issued after January 2010

7. List the customers who have been issued Horror movies

8. List all the customer who have been issued DVD after year December 2011