Q. 1.

Consider the following set of requirements for a university database used to

maintain student grade reports (transcripts).

A. The university records each student's name, social security number (SSN),
address, phone, birthdate, major department, and degree program (B.A., B.S.,
etc.). An SSN uniquely designates a student.

B. Each department is described by a name, department code, office number,

office phone, and college. Name and code values each uniquely characterize a
department.

C. Each course has a course name, description, course number, number of

quarter hours credit, and offering department. The combination of a course
number and an offering department uniquely designates a course.

D. Each section has an instructor, semester, year, course and section number.
The section number distinguishes different sections of the same course that are
taught during the same quarter and year.

E. A grade report has a student, section, and grade (A,B,C, and D). The
combination of a student and section uniquely characterizes a grade report.

Draw the E-R diagram. Create the database in MySQL. Frame and execute the
SQL queries for the following:

1. List name of all students who have registered for courses in more than one
department.

2. List name and SSN of all students who have registered for more than one
course in a specified department.

3. List name of all students who have got at least B grade in all the courses
taken by him/her.

4. List name of all students who have got A in all the courses offered by CS
department.

5. List the highest grade obtained in each course by students majoring in each
of the departments.
Q.2) Define the schema for the following databases with specific data type and
constraints, the table nameand its fields name are to be taken from database
description which are given below:

A database is being constructed for storingsales information system. Aproductcan be

describedwith aunique product number,product name, selling price, manufacturer
name. The product can sale toa particularclientand each client have it ownunique
client number, client name, client addresses, city,pin code, state and total balance to
be required to paid. Each client order to buy product from thesalesman. In theorder, it
hasunique sales order number, sales order date, client number, salesmannumber
(unique), billed whole payment by the party or notandits delivery date.
Thesalesmanhave thename, addresses, city, pin code, state, salary of the sales man,
delivery date, total quantity ordered,product rate.

Write the SQL queries for the following –

a) List the various products available.

b) Find the names of all clients having ‘a’ as the second letter in their names.
c) List all the clients who are located in Kolhapur.
d) Find the products whose selling price is greater than 2000 and less than or
equal to 5000
e) Add a new column NEW_PRICE into the product_master table.
f) Rename the column product_rate of Sales_Order_Details to new_product_rate.
g) Display the order number and date on which the clients placed their order.
h) Change the delivery date of order number ON01008 to 16-08-08
i) Change the bal_due of client_no CN01003 to 1200
j) Find the product with description as ‘HDD1034’ and ‘DVDRW’
k) List the names, city and state of the clients not in the state of ‘ASSAM’
l) Write a cursor to List the products in sorted order of their description.
m) Write a procedure/function to Delete all the records having delivery date
before 25th August, 2008.
Q.3) Design the MySQL Database with following entities,
 SAILORS (SID: INTEGER, SNAME:STRING, RATING:INTEGER(Must be
in between 1 to 10), AGE:REAL)
 BOATS (BID: INTEGER, BNAME: STRING, COLOR: STRING)
 RESERVES (SID: INTEGER, BID: INTEGER, DAY: DATE)

Make appropriate tables and add required data for the above database. Frame and
execute the SQL queries for the following:

a) Find the names of sailors who have reserved a red or a green boat.
b) Find the names of sailor's who have reserved both a red and a green boat.
c) Find the sids of all sailor's who have reserved red boats but not green boats.
d) Find sailors whose rating is better than some sailor called Rajesh.
e) Find the sailor's with the highest rating using ALL.
f) Find the name and age of the oldest sailor.
g) Find the names of sailors who are older than the oldest sailor with a rating of
10.
h) Displays all the sailors according to rating (Lower Rating First), if rating is
same then sort according to age (Younger First).
i) Find the age of youngest sailor with age >= 18 for each rating with at least 2
such sailors.
j) Write a procedure to delete records from sailors table by reading SID from
Keyboard.
Q.4) Design the MySQL Database with following entities,

 SAILORS (SID: INTEGER, SNAME:STRING, RATING:INTEGER(Must be

in between 1 to 10), AGE:REAL)
 BOATS (BID: INTEGER, BNAME: STRING, COLOR: STRING)
 RESERVES (SID: INTEGER, BID: INTEGER, DAY: DATE)

Make appropriate tables and add required data for the above database. Frame and
execute the SQL queries for the following:

1. Find the names of sailors who have reserved boat number 123.
2. Find names of the sailors who have reserved at least one boat.
3. Create a view for Expert Sailors ( A sailor is a Expert Sailor if his rating is
more than 7).
4. Find average age of Expert sailors.
5. Write the following queries on Expert Sailor View.
5.1 Find the Sailors with age > 25 and rating equal to 10.
5.2 Find the total number of Sailors in Expert Sailor view.
5.3 Find the number of Sailors at each rating level ( 8, 9, 10).
5.4 Find the age of Oldest as well as Youngest Expert Sailor.
6. Write cursor to display sailor records according to rating.
7. Write appropriate cursor to update rating of sailors by 2 if rating is less than 5,
by 1 if rating is >5 and doesn’t change the rating if it is equal to 10.
Q.5) Design the MySQL Database with following entities,
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer,item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold:
integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints.
b) Insert around 10 records in each of the tables
c) List all the bills for the current date with the customer names and item numbers
d) List the total Bill details with the quantity sold, price of the item and the final
amount.
e) List the details of the customer who have bought a product which has a
price>200.
f) Give a count of how many products have been bought by each customer
g) List the item details and count which are sold as of today.
h) Write a procedure to Give a list of products bought by a customer having
cust_id as 5
i) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price,
qty_sold, amount.
j) Create a view which lists the daily sales date wise for the last one week.
Q.6) Design the MySQL Database Schema for Student Library scenario
Student(Stud_no : integer,Stud_name: string)
Membership(Mem_no: integer,Stud_no: integer)
Book(book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the student names with their membership numbers
d) List all the issues for the current date with student and Book names
e) List the details of students who borrowed book whose author is CJDATE
f) Give a count of how many books have been bought by each student
g) Give a list of books taken by student with stud_no as 5
h) List the book details which are issued as of today
i) Create a view which lists out the iss_no, iss _date, stud_name, book name
j) Create a view which lists the daily issues-date wise for the last one week

Q.7) Design the MySQL Database Schema for a Employee-pay scenario

Employee(emp_id : integer,emp_name: string)
Department(dept_id: integer,dept_name:string)
Paydetails(emp_id : integer,dept_id: integer, basic: integer, deductions: integer,
additions: integer, DOJ: date)
Payroll(emp_id : integer, pay_date: date)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List the employee details department wise
d) List all the employee names who joined after particular date
e) List the details of employees whose basic salary is between 10,000 and
20,000
f) Give a count of how many employees are working in each department
g) Give a names of the employees whose netsalary>10,000
h) Write a cursor to List thepaydetailsfor all employee.
i) Create a view which lists out the emp_name, department, basic,
dedeuctions, netsalary
j) Create a view which lists the emp_name and his netsalary
Q.8) Design the MySQL Database Schema for Video Library scenario
Customer (cust_no: integer,cust_name: string)
Membership (Mem_no: integer, cust_no: integer)
Cassette (cass_no:integer, cass_name:string, Language: String)
Iss_rec (iss_no: integer, iss_date: date, mem_no: integer, cass_no: integer)
For the above schema, perform the following:-
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the customer names with their membership numbers
d) List all the issues for the current date with the customer names and cassette
names
e) List the details of the customer who has borrowed the cassette whose title is “
The Legend”
f) Give a count of how many cassettes have been borrowed by each customer
g) Give a list of book which has been taken by the student with mem_no as 5
h) Write a procedure to List the cassettes issues for today.
i) Write a trigger to delete a Customer record.
j) Create a view which lists outs the iss_no, iss_date, cust_name, cass_name
k) Create a view which lists issues-date wise for the last one week

Q.9) Design the MySQL Database Schema for student-Lab scenario

Student (stud_no: integer, stud_name: string, class: string)
Class (class: string,descrip: string)
Lab (machi_no: integer, Lab_no: integer, description: String)
Allotment (Stud_no: Integer, mach_no: integer, dayof week: string)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the machine allotments with the student names, lab and machine
numbers
d) List the total number of lab allotments day wise
e) Give a count of how many machines have been allocated to the ‘CSIT’ class
f) Give a machine allotment etails of the stud_no 5 with his personal and class
details
 g) Count for how many machines have been allocatedinLab_no 1 for the day of
the week as “Monday”
h) How many students class wise have allocated machines in the labs
i) Create a view which lists out the stud_no, stud_name, mach_no, lab_no,
dayofweek
j) Create a view which lists the machine allotment details for “Thursday”.
Q.10) ROADWAY TRAVELS “Roadway Travels” is in business since 1977 with
several buses connecting different places in India. Its main office is in Pune. The
company wants to computerize its operations in the following areas:
 Reservations
 Ticketing
 Cancellations

Reservations: Reservations are directly handled by booking office. Reservations can

be made 60 days in advance in either cash or credit. In case the ticket is not available,
a wait listed ticket is issued to the customer. This ticket is confirmed against the
cancellation.

Cancellation and modification: Cancellations are also directly handed at the booking
office. Cancellation charges will be charged. Wait listed tickets that do not get
confirmed are fully refunded.

Analyze the problem and following with the entities in it. Identify what Data must be
persisted in the databases.

1. Bus: - Bus No, Source, Destination, Couch Type

2. Reservation: - PNR NO, Journey date, Contact No, No-of-Seats, Bus No, Address

3. Ticket: - Ticket No, Journey date, Age, Dep-Time, Sex, Source, Destination, Bus
No

4. Passenger: - Ticket No, PNR NO, Age, Sex, Contact NO, Name

5. Cancellation: -PNR NO, Journey date, Seat No, Contact No

Design the suitable ER Diagram and execute following queries.

a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables.
c) Display all the names of male passengers.
d) Find the ticket numbers of the passengers whose name start with ‘r’ and ends
with ‘h’.
e) Find the names of passengers whose age is between 30 and 45.
f) Display the sorted list of passenger’s names.
g) Find the number of tickets booked by a passenger where the number of seats is
greater than 1.
h) Display the Total number of Reservation per bus number.
i) Write a cursor to display the ticket numbers and names of all the passengers.
Q.11) A student is described by a unique Roll Number, Name Adress, and
Semester. Each student enrolls himself in an Academic programme offered by a
Department. Academic programmes have programme name(unique), duration, a
programme code(unique) and a list of courses (both core and elective course)
while the departments have department code (unique), department name
(unique), HoD who is a Teacher and list of courses offered by it. Each teacher is
described by employee code (unique), name, department and designation. A
student registers some courses in a semester. A course is described by a unique
course number, title of the course, credit allotted for the course and offering
department. Database stores the grades obtained by different student in different
courses registered by him/her in different semesters. Database also stores
information about the courses offered by a department in a semester, the
corresponding teacher(s) for each course.
Write the SQL queries for the following –
a) List the available courses from the CSE department.
b) List the all students in a particular semester.
c) List the students who earned CGPA greater than or equal to 8.5
d) How much subjects are registered by a student in each semester.
e) List the common students who are allotted the same courses of both the
programme MCA and M.Tech.
f) List the total number of student enrolled in the subject DBMS.
g) Retrieve the semester of the student under DBMS subject.
h) Retrieve all the student name and arrange into ascending order.
i) Modify a student address Kolhapur to Pune where sdt_id=’CSI08002’.
j) Find the total credit point of student required to complete for a course
like MCA.
k) Retrieve all the students located at ‘Pune’.
l) Find the total number of department in our database.
m) Write a Cursor to List all the courses which are related to computer
science.
n) Write a trigger to delete all corresponding records from assigned table if
student deleted.
Q. 12:Bank Database:
A bank database keeps record of the details of customers, accounts, loans
and transactions such as deposits or withdraws. Customer record should
include customer id, customer name, address, age, contact number, email
id etc., accounts details involves account number, account type(fixed
account, savings account, monthly account etc), date of creation of the
account, balance. Transaction detail keeps information about amount
deposited or withdrawn to/from a particular account and the date of
transaction. The database should also store record of loans which include
loan amount, loan date and the account number to which the loan is
granted.
Make appropriate tables for the above database and try to find out the
following queries: (Apply appropriate triggers whenever required)
a) List the details of account holders who have a ‘savings’ account.
b) List the Name and address of account holders with loan amount more
than 50,000.
c) Change the name of the customer to ‘ABC’ whose account number is
’TU001’
d) List the account number with total deposit more than 80,000.
e) List the number of fixed deposit accounts in the bank.
f) Display the details of customers who created their accounts between
’20-jan-08’ to ’20-aug-08’.
g) Display the detailed transactions on 28th Aug, 2008.
h) Display the total amount deposited and withdrawn on 29th Aug, 2008.
i) List the details of customers who have a loan.
j) Write a cursors to display Savings and Loan information of all customers.
k) Write a trigger to delete the customer.
Q.13) Hospital information system:
Patients - indoor/outdoor, medicines/lab tests(including results) prescribed to
patients, information if a patient if referred to other expert/hospital. Doctors -
specialization, patients attended etc. Different wards/beds and patients alloted to
them etc.
Patient registration form should include Registration number, Patient name,
Address, Gender, Bed number, date of registration, refer doctor id etc.
Doctor information should include Doctor code, Doctor Name, Specialization
etc.
Lab test information should include Test name, test number, test date, results
and referred doctor’s code.
Bed information should include bed number, ward number and status(whether
allotted or not).
Write suitable Queries for following statements:
1. Display the details of patients admitted between ‘20-jul-02’ and ’20-
aug-08’.
2. Change the name of the patient to ‘Ram’ whose patient id=’PT011’
3. Display the names of the patients and lab test results performed on
‘20-jul-08’.
4. Display the number of patients taking treatment under doctor
=’ABC’.
5. Retrieve the name of doctor who is taking care of maximum
number of patients.
6. Change the bed number of the patient to 456 where patient
id=’PT023’
7. Change the status of bed with bed number 123 with ‘not allotted’.
8. List the bed details which are free in ward number 10.
9. List the name of male patients in ward no 13 taking treatment
under doctor ‘XYZ’
10. List the details of patients with age more than 50 taking treatment
under a doctor, whose name like ‘das’.
11. Write a cursor to display details of all patients from allocated
doctors to allocated beds.
Q.14) Consider the following relational schema. An employee can work in more
than one department.
Emp(eid: integer, ename: string, salary: real)
Works(eid: integer, did: integer)
Dept(did: integer, dname: string, managerid: integer, floornum: integer)
Write the following Queries:
1. Print the names of all employees who work on the 10th floor and make
less than Rs. 50,000.
2. Print the names of all managers who manage three or more departments
on the same floor.
3. Write a procedure to Give every employee who works in the toy
department a 10 percent raise.
4. Print the names and salaries of employees who work in both the toy
department and the Music department.
5. Print the names of employees who earn a salary that is either less than
Rs. 10,000 or more than Rs. 100,000.
6. Print all of the attributes for employees who work in some department
that employee Abhishek also works in.
7. Print the names of employees who make more than Rs. 20,000 and work
in either the video department or the toy department.
8. Print the name of each employee who earns more than the manager of
the department that he or she works in.
9. Print the name of each department that has a manager whose last name is
pantamabe and who is neither the highest-paid nor the lowest-paid
employee in the department.
10. Write a Procedure to Print the names of all employees who work on the
floor(s) where Amar Arora works.
11. Write a trigger to update department id from department, corresponding
table must be reflected accordingly.
Q.19) Our toy-store database has the following schema:
Product(pid:integer, name: varchar(20), min_age: integer)
Manufacturer(mid:integer, name: varchar(20), address: varchar(50))
Supplier(sid:integer, name: varchar(20), address: varchar(50))
Inventory(pid:integer, stock: integer)
Manufactures(mid:integer, pid: integer)
Supplies(sid: integer, pid: integer)
The Product relation contains information about all the toys sold by the store.
The Product.pid column is the primary key for the relation. Product.name is the
name of each toy, and Product.min_age indicates the minimum age recommended to
play with the toy.
Manufacturer and Supplier list the names and addresses of all toy manufacturers and
suppliers respectively. Manufacturer.mid and Supplier.sid are the primary keys for
these relations.
Inventory indicates the number of toys in stock. Inventory.pid is a foreign key
referring to Product.pid. Inventory.stock indicates the number of toys of the given
type available in stock.
Manufactures and Suppliers associate products with their manufacturers and
suppliers respectively. Note that there can be only one manufacturer for a product,
but there can be many suppliers.
Write SQL queries for the following:
1) List the ids and names of all products whose inventory is below 5.
2) List the ids and names of all suppliers for products manufactured by
"manufacturer_2". The id and name of each supplier should appear only once.
3) List the ids, names, and number in stock of all products in inventory. Order the
list by decreasing number in stock and decreasing product ids.
4) List the ids and names of all products for whom there is only one supplier.
5) Find the ids and names of the products with the lowest inventory. Do NOT
assume these are always products with an inventory of zero.
6) List the id and name of each supplier along with the total number of products it
supplies.
7) Find the id and name of the manufacturer who produces toys on average for the
youngest children.
8) Create a view that shows only toys in the Product table for children over age 6
9) Create a view that shows the id and names of toys along with the ids and names of
their manufacturers.
10) Write a cursor to display Average age of each products per Manufacturer
Q.20)Shops sell items at varying prices. Customers buy items from shops. This is
described by the following relations:
Shops (ShopId, name, address)
Items (ItemId, name, description)
Sells (ShopId, ItemId, price)
Customers (CustomerId, name, address)
Sales (SaleId, CustomerId, ItemId, ShopId, date)
a) Draw an E/R diagram that describes the database.
b) Create the table Sells. Invent suitable types for the attributes (itemId shall have the
type char(10)) and indicate reasonable integrity constraints.
c) Print the name and address of all customers who haven’t bought any item.
d) Print the number of shops that sell items with id’s starting with 'EF'.
e) Print the name and address of the shop(s) that sell the item with id = 'EF123-A' at
the lowest price.
f) For all customers that have bought at least one item: print the customer id and the
total sum of all purchases.
g) Display total price per customer.
h) Display the name of Highest buyer.
i) Write a procedure to display total items per customer.
j) Write a trigger to delete item, corresponding tables must have reflected according.
Q.21) Create a relational database that contains the following tables and
insert the following data into these tables.

Solved the Following SQL Queries:

1. Display the first name and contact number of all students.
2. Display Roll no,First name, dept id and Dept name.
3. Display the First name, Surname , contact no and Dept name whose first
name contain ‘a’ character at any place but not studying in Civil or
Mechanical department .
4. Give the name and roll no of all students of information technology who are
male.
5. Display the name of departments of students who are in 1st semester.
6. Display name of departments for which no students are in 3rdsemester.
7. Count the Total no of Female Students in each department.
8. Count the Total no of Female and Male Students and display the maximum
output.
9. Count the Total no of Students in each department and display only those
department which having more than 3 students.
10. Count the Total no of Students in each department arrange that calculate
students in descending order.
11. Display data of student firstname members in alphabetical order.
12. Count the Total no of Students semester wise.
13. Display data of student who are currently in semester ‘1’ but not a gender
female.
14. Write a cursor to Count the Total no of Students in each department.
15. Write a procedure to display total no of students in corresponding dept (Dept
name should be taken from user)
Q.22) Consider following relational schema
TABLE :DEPT
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--TABLE: EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 2-Apr-81 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850 30
7782 CLARK MANAGER 7839 9-Jun-81 2450 10
7788 SCOTT ANALYST 7566 9-Dec-82 3000 20
7839 KING PRESIDENT 17-Nov-81 5000 10
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-83 1100 20
7900 JAMES CLERK 7698 3-Dec-81 950 30
7902 FORD ANALYST 7566 3-Dec-81 3000 20
7934 MILLER CLERK 7782 23-Jan-82 1300 10
Solve following queries
1) Display all the ANALYSTs whose name doesn’t ends with ‘S’
2) Display all the employees who are earning more than all the managers.
3) Display all the managers working in 20 & 30 department.
4) Display all the managers who don’t have a manager
5) Display the second maximum salary.
6) Display the departments that are having more than 3 employees under it.
7) Display job-wise average salaries for the employees whose employee number is
not from 7788 to 7790.
8) Display department-wise total salaries for all the Managers and Analysts, only
if the average salaries for the same is greater than or equal to 3000.
9) Display the third maximum salary.
10)Display all the managers & clerk who work in Accounting and Marketing
(SALES) departments.
11)Display all the managers & clerks who work in Accounts and Marketing
departments.
12)Display all the employees who have joined before their managers.
13) Select all the employees name along with their manager names, and if an
employee does not have a manager, display him as “CEO”.
14) Write a trigger to delete department, dependent table must be reflected
accordingly.
15)Write a cursor to Display job-wise maximum salary.
Q.23) Consider following relational schema
TABLE :DEPT
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--TABLE: EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 2-Apr-81 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850 30
7782 CLARK MANAGER 7839 9-Jun-81 2450 10
7788 SCOTT ANALYST 7566 9-Dec-82 3000 20
7839 KING PRESIDENT 17-Nov-81 5000 10
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-83 1100 20
7900 JAMES CLERK 7698 3-Dec-81 950 30
7902 FORD ANALYST 7566 3-Dec-81 3000 20
7934 MILLER CLERK 7782 23-Jan-82 1300 10
Solve following queries
1) Display all the employees whose naming is having letter ‘E’ as the last but one
character
2) Display all the employees who total salary is more than 2000.
3) Display name of all the employee who joined after 30th Feb 81 with department
name.
4) Display all the employees who are getting some commission in department 20
& 30.
5) Select all the employees who work in DALLAS.
6) Select employee number, job & salaries of all the Analysts who are earning
more than any of the managers.
7) Select department name & location of all the employees working for CLARK.
8) Display all the salesmen who are not located at DALLAS.
9) Display all the departmental information for all the existing employees and if a
department has no employees display it as “No employees”.
10)Get all the matching & non-matching records from both the tables.
11)List all the employees whose salaries are greater than their respective
departmental average salary.
12)Write a trigger to delete department, dependent table must be reflected
accordingly.
13)Write a cursor to Display Department-wise maximum salary.
Q.24) Create the table DISTRIBUTOR with the fields (DNO, DNAME,
DADDRESS, DPHONE)
Constraints on table DISTRIBUTOR: dno primary key, dname not null.
Create the table ITEM1 with the fields (ITEMNO, ITEMNAME, COLOR, WEIGHT)
Constraints on table ITEM1: itemno primary key, itemname not null, check for
weight>0
Create the table DIST_ITEM with the fields (DNO, ITEMNO, QTY): Constraints of
table DIST_ITEM: dno references DISTRIBUTOR table, itemno references ITEM
table.
1. Create a view LONDON_DIST on DIST_ITEM which contains only those
records where distributors are from London. Make sure that this condition is
checked for every DML against this view.
2. Display detail of all those item that have never been supplied.
3. Count the number of items having the same color but not having weight
between 20 and 100
4. Display all those distributors who have supplied more than 1000 parts of the
same type.
5. Display the average weight of items of same colour provided at least one items
have that colour.
6. Display the position where a distributor name has an 'OH' in its spelling
somewhere after the forth character.
7. Count the number of distributors who have a phone connection and are
supplying item number 'I100
8. Create a view on the table in such a way that the view contains the distributor
name, item name and the quantity supplied.
9. List the name, address and phone number of distributors who have the same
three digits in their number as 'Mr. Talkative'.
10. List all distributor names who supply either item I01 or I07 and the quantity
supplied is more than 100.
11. Display the data of the top three heaviest ITEMS.
12. Count the total quantity group by itemno.
13. Delete all those items that have been sulpplied only once.
14. List the names of distributors who have an 'A' and also a 'B' somewhere in their
names.
Q.25)
Create the table WORKER with the fields (worker_id, name,
wage_per_hour, specialized_in, manager_id) Constraints on table WORKER:
worker_id primary key, name not null, manager_id primary key, check for
wage_per_hour>=0.
Create the table JOB with the fields (job_id, type_of_job, status):
Create the table JOB_ASSIGNED with the fields (worker_id, job_id,
starting_date, number_of_days)
Constraints on table JOB_ASSIGNED: worker_id references WORKER table, job_id
references JOB table.
1. Display the date on which each worker is going to end his presently assigned
job.
2. Display how many days remain for each worker to finish his job.
3. Display the STARTING_DATE in the following format - 'The fifth day of
month of October, 2004'.
4. Change the status to 'Complete' for all those jobs, which started in year 2008.
5. What is total number of days allocated for printing on the goods for all the
workers together.
6. Which workers receive higher than average wage per hour.
7. Display details of workers who are working on more than one job.
8. Which workers having specialization in polishing start their job in
September?
9. Display details of workers who are specialized in the same field as that of
Mr.Cacophonix or have a wage per hour more than any of the workers.
10. Display job details of all those jobs where at least 25 workers are
working.
11. Display all those jobs that are already incompleted.
12. Find all the jobs, which begin within the next two weeks.
13. List all workers who have their wage per hour ten times greater than the wage
of their managers.
14. List the names of workers who have been assigned the job of Packing.
15. Find the names of the workers who are getting more then 50 Rs. as wages per
hour.
16. Find the jobs which are assigned after 31-DEC-2008.