Serial Assignment Name Page No.
Signature
No.
Consider the following Relational Schema: 1-5
1
CUSTOMER (cust_id, cust_name, annual_revenue)
SHIPMENT (shipment_id, cust_id, weight, truck_id,
destination)
Foreign Key Destination references CITY
TRUCK (truck_id, driver_name)
CITY (city_name, population)
Create the database and insert sufficient no of records
Write SQL commands to do the following:
A.
1. Delete only those cities from the database whose
population is fewer than 5,000 and at the same time delete
them from the SHIPMENT table.
2. List the cities in the database having largest and smallest
populations.
3. List the names and populations of the cities that have
received shipments weights over 10 pounds.
B.
1. Convert the weight of every shipment in kilograms by
dividing the weight by 2.2.
2. List all the customers having over Rs. 250,000 in annual
revenue who have sent Shipments weighing less than
hundred pounds.
3. List all minimum weight of packets sent by the cities with
populations over 5,000.
Consider the following Relational schema: 6-8
2
Borrower (b_name, b_id);
Book (book_id, title, author, subject);
Borrows (book_id, b_id, date_of_issue, date_of_return);
Create tables through appropriate sql commands. Define all
integrity constraints and enter sufficient data .
Write SQL commands for the following queries:
a. Give all the books on physics and mathematics.
b. Find out all the titles and authors of all the books issued
by the borrower where b_id is B001.
c. Find out the names of the borrowers who have issued
books written by C.J.Date.
Consider the following Relational Schema: 9-11
3
STUDENT (student_id, student_name)
IS_QUALIFIED (faculty_id, course_id, date_qualified)
FACULTY (faculty_id, faculty_name)
SECTION (section_id, course_id)
COURSE (course_id, course_name)
IS_REGISTERED (student_id, section_id, semester)
Create the database and insert records. Define all integrity
constraints
Write SQL commands to do the following:
a. Display the course_id and course_name for all the courses
with an ISM prefix.
b. Display the student name for all students enrolled in
section 2714 of ISM 4212.
c. List all the students who weren’t enrolled in any course
during semester I-2008.
Consider the following Relational schema: 12-13
4
Sailors (s_id, s_name, rating)
Boats (b_id, b_name, color)
Reserves (s_id, b_id, day)
Bid must be between 10 to 30 and color must be RED,
GREEN, BLUE, YELLOW
Create the database and insert records. Define all integrity
constraints
Write SQL commands to do the following:
a. Find the name of the sailors who have reserved both a red
and green boat.
b. Find the colors of boats reserved by Rahim.
Consider the following Relational Schema: 14-16
5
EMPLOYEE (emp_id, emp_name, street, city)
WORKS (emp_id, company_name, salary) [Salary range-
10,000 to 1, 00,000]
COMPANY (company_name, city) [Company_name must
be ABC, PQR or XYZ]
MANAGER (mgremp_id, manager_name)
Create the database and insert records. Define all integrity
constraints
Write SQL commands to do the following:
a. Find the names, street address and cities of residence of
all employees who work for ABC company and earn more
than Rs 10,000.
b. Find all employees who do not work for ABC company.
Consider the following Relational schema: 17-19
6 Salesperson (ssn, name, startyear, dept_no)
Trip (ssn, fromcity, tocity, departure_date, return_date,
trip_id)
Expense (trip_id, account, amount)
Create the database through appropriate SQL command.
Define all integrity constraints
Insert sufficient number of records. Write SQL commands for
the following queries:
i) Give the details of trips that exited rupees 2000 in
expenses.
ii) Print the ssn of the salesman who trip from howrah.
iii) Print the total trip expenses in earned by the salesman
with ssn 145268547
Consider the following Relations about a computer firm. The 20-22
7 primary key of each relation is
underlined.
PRODUCT (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
LAPTOP (code, model, speed, ram, hd, screen, price)
PRINTER (code, model, color, type, price)
Create the database and insert sufficient records. Define all
integrity constraints.
Write SQL commands to do the following:
a. Find the model number, speed and hard drive capacity for
all the PCs with prices below rs.500.
b. Find the makers of the PCs that have speed not less than
450MHz.
c. Find the printers having the highest price.
Consider the following Relational schema: 23-24
8 Student (s_num, s_name, major, level, age)
Class (c_name, time_schedule, room, faculty_id)
Enrolled (s_num, c_name)
Faculty (faculty_id, ename, dept_id)
Create tables through appropriate sql commands. Define all
integrity constraints and enter
sufficient data.
Write SQL commands for the following queries:
a. Find the name of faculty members who teach in every
room in which some class is taught.
b. Print the level and the average age for that level.
Design a 'Cab allotment System' with following database:
9
Cab(cnu,model,colour,purchase-date)
driver(did,dname,phone,rating,age)
alloted-in(cnu,did.date1)
Now perform following operations using PHP:
a. Display all the records of table "Cab" through PHP.
b. insert a record in the "Cab" table through PHP.
Design a 'Stadium Booking System' that maintains the
10 following database:
stadium(sid,name,event,type,capacity,price)
organiser(oid,org-name,type-of-org)
booking(bid,sid,date-of-booking)
Create the above tables in MySQL with following and other
integrity constraints:
Stadium names must start with 'A', 'D' and 'G'.
Now perform the following operations in MySQL:
a. Display details of all stadiums which have been booked by
'ABC group of Institution' between
01/01/2022 to 15/03/2022(eliminate duplicate tuples).
b. List all organizers who have booked the highest capacity
stadiums at least once.
Now perform the following operation using PHP:
a. Display all the records from table stadium in tabular
format.
b. Delete the records from the "Stadium" whose price is
greater than 10,000.
Design a 'Sales Tracking System' with the following
11 database:
item(ino,iname,type,price,colour)
cust(cid,cname,phone,city)
sales(sales-id,ino,cid,sales-date)
Create the above tables in MySQL with the following and
other integrity constraints:
ino must be set to value 100 and it will be auto incremented
by 1.
Now perform the following operations using PHP:
a. Change the phone no of customer having cid = COO1
b. Display all customers from Kolkata and Durgapur who
have purchased a dish washer in SEPT, 22.
Design a 'ART GALLERY SYSTEM' with following database:
12
Gallery(gid,gname,capaciy,city)
artists(aid,aname,age.rank)
reserved(gid,aid,date,reverse)
Create the above tables in MySQL with the following and
other integrity constraints:
Default value for the field capacity in Gallery table 500.
Now perform the following operation using PHP:
Q. Design form to insert the record in the 'Artists' table and
insert the record in the table 'Artists' through the form.
Design a 'Flight Reservation System' with following
13 database:
flights(fl-no,from 1,to 1, distance price)
aircraft(a-id, a-name, cruising-range)
emp(e-id,e-name,salary)
certified(e-id,a-id)
Create the above tables in MySQL with the following and
other integrity constraints:
Salary is greater than 10000 and less than 40000
Now perform the following operations through PHP:
Design a login table to take user name and password. Show
all the emp tables for an authentic user. A user is
authenticated if the user name & password exists in the
MySQL database Login table.
Signature of Signature of Signature of
HOD Teacher Teacher
Signature of
HOD
