Customer Item schema queries which fall in all the categories mentioned above.
CUST(Custno, cname, state, phone)
ITEM(itemno, Itemname, Itemprice, Qty_hand)
INVOICE(Invno, invDate, Custno)
INVITEM(Invno, Itemno, Qty)
1. Create four table along with necessary constraints( PK,FK,notnull, Unique and
Check constraints)
2. Write a Insert script for insertion of rows with substitution variables.
3. Add a column to the Item table, which will allow us to store Item color field.
4. Write SELECT statement for the given queries.
a. Display Item name, Price in sentence form using concatenation
b. Find total value of each item based on quantity on hand
c. Find customers who are from state of Gujarat.
d. Display items with unit price of at least Rs. 100
e. List items whose range lies between Rs. 200 and Rs. 500
f. Which customers are from lalbaug area of Ahmedabad, Baroda and Patan.
g. Find all customers whose name start with Letter ‘P’.
h. Find name of items with ‘W’ in their name.
i. Sort all customers alphabetically
j. Sort all items in descending order by their prices.
k. Display all customers from M.P alphabetically
l. Display invoices dates in ‘September 05, 2007’ format.
m. Find total, average, highest and lowest unit price
n. Count number of items ordered in each invoice
o. Find invoices in which three or more items are ordered.
p. Find all possible combination of customers and items ( use Cartesian product)
q. Display all item quantity and item price for invoices ( natural join)
r. Find total price amount for each invoice.
s. Use outer join to display items ordered as well as not ordered so far.
t. Find invoices with ‘Gear’ in their item name.
u. Display name of items ordered in invoice number 1001
v. Find the items that are cheaper than ‘Bullet’.
w. Create a table ( namely guj_cust) for all Gujarat customer based on existing customer table
x. Copy all M.P customers to the table with Gujarat customers
y. Rename Guj_cust table to MP_cust table.
z. Find the customers who are not in Gujarat or M.P
aa. Delete rows from customer table that are also in MP_cust table
bb. Find the items with top three prices
cc. Find two items with lowest quantity on hand
dd. Create a simple view with item names and item price only
ee. Create a sequence that can be used to enter new items into item table
ff. Add a new item into item table with sequence just created.
gg. Create a index file to speed up a search based on customer name
hh. Lock customer Mr. Shah record to update the state and phone no.
ii. Give everybody select and insert rights on your item table
jj. Revoke the insert option on item table from user ‘Roshi’
CUST(Custno, cname, state, phone)
ITEM(itemno, Itemname, Itemprice, Qty_hand)
INVOICE(Invno, invDate, Custno)
INVITEM(Invno, Itemno, Qty)
1. Create four table along with necessary constraints( PK,FK,notnull, Unique and
Check constraints)
2. Write a Insert script for insertion of rows with substitution variables.
3. Add a column to the Item table, which will allow us to store Item color field.
4. Write SELECT statement for the given queries.
a. Display Item name, Price in sentence form using concatenation
b. Find total value of each item based on quantity on hand
c. Find customers who are from state of Gujarat.
d. Display items with unit price of at least Rs. 100
e. List items whose range lies between Rs. 200 and Rs. 500
f. Which customers are from lalbaug area of Ahmedabad, Baroda and Patan.
g. Find all customers whose name start with Letter ‘P’.
h. Find name of items with ‘W’ in their name.
i. Sort all customers alphabetically
j. Sort all items in descending order by their prices.
k. Display all customers from M.P alphabetically
l. Display invoices dates in ‘September 05, 2007’ format.
m. Find total, average, highest and lowest unit price
n. Count number of items ordered in each invoice
o. Find invoices in which three or more items are ordered.
p. Find all possible combination of customers and items ( use Cartesian product)
q. Display all item quantity and item price for invoices ( natural join)
r. Find total price amount for each invoice.
s. Use outer join to display items ordered as well as not ordered so far.
t. Find invoices with ‘Gear’ in their item name.
u. Display name of items ordered in invoice number 1001
v. Find the items that are cheaper than ‘Bullet’.
w. Create a table ( namely guj_cust) for all Gujarat customer based on existing customer table
x. Copy all M.P customers to the table with Gujarat customers
y. Rename Guj_cust table to MP_cust table.
z. Find the customers who are not in Gujarat or M.P
aa. Delete rows from customer table that are also in MP_cust table
bb. Find the items with top three prices
cc. Find two items with lowest quantity on hand
dd. Create a simple view with item names and item price only
ee. Create a sequence that can be used to enter new items into item table
ff. Add a new item into item table with sequence just created.
gg. Create a index file to speed up a search based on customer name
hh. Lock customer Mr. Shah record to update the state and phone no.
ii. Give everybody select and insert rights on your item table
jj. Revoke the insert option on item table from user ‘Roshi’
STUDENT(rollno,name,class,birthdate)
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)
1. Add constraint that marks entered are between 0 to 100 only.
2. While creating COURSE table, primary key constraint was forgotten. Add the primary key now.
3. Display details of student where course is ‘Data Base Management System’.
4. Select student names who have scored more than 70% in Computer Networks and have not failed in any subject.
5. Select names and class of students whose names begin with ‘A’ or ‘B’.
6. Display average marks obtained by each student.
7. Select all course where passing marks are more than 30% of average maximum marks.
8. Select the course where second and third characters are ‘AT’.
9. Display details of students born in 1975 or 1976.
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)
1. Add constraint that marks entered are between 0 to 100 only.
2. While creating COURSE table, primary key constraint was forgotten. Add the primary key now.
3. Display details of student where course is ‘Data Base Management System’.
4. Select student names who have scored more than 70% in Computer Networks and have not failed in any subject.
5. Select names and class of students whose names begin with ‘A’ or ‘B’.
6. Display average marks obtained by each student.
7. Select all course where passing marks are more than 30% of average maximum marks.
8. Select the course where second and third characters are ‘AT’.
9. Display details of students born in 1975 or 1976.
HOSTEL (H#, hname, haddr, total_capacity, warden)
ROOM (h#, r#, rtype, location, no_of_students, status)
CHARGES (h#, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, h#, r#)
FEES (sid, fdate, famount)
The STATUS field tells us whether the room is occupied or vacant. The charges
represent the term fees to be paid half yearly. A student can pay either the annual fees at one time or the half yearly fees twice a year.
1. Add a check constraint to the room table so that the room type allows the following values only – ‘s’ for single, ‘d’ for double, ‘t’ for triple and ‘f’ for four-seater.
2. Display the total number of rooms that are presently vacant.
3. Display number of students of each faculty and department wise staying in each hostel.
4. Display hostels, which have at least one single-seated room.
5. Display the warden name and hostel address of students of Computer Science department.
6. Display those hostel details where single seated or double-seated rooms are vacant.
7. Display details of hostels occupied by medical students.
8. Display hostels, which are totally occupied to its fullest capacity.
9. List details about students who are staying in the double-seated rooms of Chanakya Hostel.
10. Display the total number of students staying in each room type of each hostel.
11. Display details about students who have paid fees in the month of Nov. 2003.
12. For those hostels where total capacity is more than 300, display details of students studying in Science faculty.
13. Display hostel details where there are at least 10 vacant rooms.
14. Display details of students who have still not paid fees.
15. Display those hostels where single-seated room is the costliest.
ROOM (h#, r#, rtype, location, no_of_students, status)
CHARGES (h#, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, h#, r#)
FEES (sid, fdate, famount)
The STATUS field tells us whether the room is occupied or vacant. The charges
represent the term fees to be paid half yearly. A student can pay either the annual fees at one time or the half yearly fees twice a year.
1. Add a check constraint to the room table so that the room type allows the following values only – ‘s’ for single, ‘d’ for double, ‘t’ for triple and ‘f’ for four-seater.
2. Display the total number of rooms that are presently vacant.
3. Display number of students of each faculty and department wise staying in each hostel.
4. Display hostels, which have at least one single-seated room.
5. Display the warden name and hostel address of students of Computer Science department.
6. Display those hostel details where single seated or double-seated rooms are vacant.
7. Display details of hostels occupied by medical students.
8. Display hostels, which are totally occupied to its fullest capacity.
9. List details about students who are staying in the double-seated rooms of Chanakya Hostel.
10. Display the total number of students staying in each room type of each hostel.
11. Display details about students who have paid fees in the month of Nov. 2003.
12. For those hostels where total capacity is more than 300, display details of students studying in Science faculty.
13. Display hostel details where there are at least 10 vacant rooms.
14. Display details of students who have still not paid fees.
15. Display those hostels where single-seated room is the costliest.
Screen(screen_id,location ,seating_cap)
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must be less than date_of_closure.
Solve the following queries based on the above schema:
1. Get the name of movie which has run the longest in the multiplex so far.
2. Get the average duration of a movie on screen number ‘S4’.
3. Get the details of movie that closed on date 24-november-2004.
4. Movie ‘star wars III ‘ was released in the 7th week of 2005. Find out the date of its release considering that a movie releases only on Friday.
5. Get the full outer join of the relations screen and current.
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must be less than date_of_closure.
Solve the following queries based on the above schema:
1. Get the name of movie which has run the longest in the multiplex so far.
2. Get the average duration of a movie on screen number ‘S4’.
3. Get the details of movie that closed on date 24-november-2004.
4. Movie ‘star wars III ‘ was released in the 7th week of 2005. Find out the date of its release considering that a movie releases only on Friday.
5. Get the full outer join of the relations screen and current.
DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)
1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.
2. 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.
3. Display details of all those items that have never been supplied.
4. Delete all those items that have been supplied only once.
5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
6. Count the number of items having the same colour but not having weight between 20 and 100.
7. Display all those distributors who have supplied more than 1000 parts of the same type.
8. Display the average weight of items of same colour provided at least three items have that colour.
9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after the fourth character.
10. Count the number of distributors who have a phone connection and are supplying item number ‘I100’.
11. Create a view on the tables in such a way that the view contains the distributor name, item name and the quantity supplied.
12. List the name, address and phone number of distributors who have the same three digits in their number as ‘Mr. Talkative’.
13. List all distributor names who supply either item I1 or I7 and the quantity supplied is more than 100.
14. Display the data of the top three heaviest ITEMS.
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)
1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.
2. 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.
3. Display details of all those items that have never been supplied.
4. Delete all those items that have been supplied only once.
5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
6. Count the number of items having the same colour but not having weight between 20 and 100.
7. Display all those distributors who have supplied more than 1000 parts of the same type.
8. Display the average weight of items of same colour provided at least three items have that colour.
9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after the fourth character.
10. Count the number of distributors who have a phone connection and are supplying item number ‘I100’.
11. Create a view on the tables in such a way that the view contains the distributor name, item name and the quantity supplied.
12. List the name, address and phone number of distributors who have the same three digits in their number as ‘Mr. Talkative’.
13. List all distributor names who supply either item I1 or I7 and the quantity supplied is more than 100.
14. Display the data of the top three heaviest ITEMS.
WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)
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 the month of October, 2004’.
4. Change the status to ‘Complete’ for all those jobs, which started in year 2000.
5. Display job details of all those jobs where at least 25 workers are working.
6. Display all those jobs that are already completed.
7. Find all the jobs, which will begin within the next two weeks.
8. List all workers who have their wage per hour ten times greater than the wage of their managers.
9. List the names of workers who have been assigned the job of molding.
10. What is the total number of days allocated for packaging the goods for all the workers together.
11. Which workers receive higher than average wage per hour.
12. Display details of workers who are working on more than one job.
13. Which workers having specialization in polishing start their job in December?
14. 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.
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)
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 the month of October, 2004’.
4. Change the status to ‘Complete’ for all those jobs, which started in year 2000.
5. Display job details of all those jobs where at least 25 workers are working.
6. Display all those jobs that are already completed.
7. Find all the jobs, which will begin within the next two weeks.
8. List all workers who have their wage per hour ten times greater than the wage of their managers.
9. List the names of workers who have been assigned the job of molding.
10. What is the total number of days allocated for packaging the goods for all the workers together.
11. Which workers receive higher than average wage per hour.
12. Display details of workers who are working on more than one job.
13. Which workers having specialization in polishing start their job in December?
14. 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.
PUBLISHER(publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)
ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)
In the above tables duration is in years and it stores the membership duration for that
category.
Change the table design of ISSUE table to add a constraint, which will allow only ‘I’ or
‘R’ to be entered in the ISSUE_RET column, which stores the action whether the book is
being issued or returned.
2. Add a column to the MEMBER table, which will allow us to store the address of the member.
3. Create a table LIBRARY_USERS which has a structure similar to that of the MEMBER table but with no records.
4. Give details about members who have issued books, which contain ‘DATA’ somewhere in their titles.
5. Display the books that have been issued at the most three times in the year 2003.
6. Display which books of publisher PHI that are issued right now.
7. Display details about books whose all copies are issued.
8. Display the book details and members for books, which have been issued between 1st Oct 2005 and 15th Nov 2005.
9. Display all staff members who have issued at least two books.
10. Display details about those publishers whose more than 100 books are available in the library.
11. Delete all those members whose membership has expired.
14How many members registered in the last three months ?
15Display since how many months has each staff member registered.
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)
ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)
In the above tables duration is in years and it stores the membership duration for that
category.
Change the table design of ISSUE table to add a constraint, which will allow only ‘I’ or
‘R’ to be entered in the ISSUE_RET column, which stores the action whether the book is
being issued or returned.
2. Add a column to the MEMBER table, which will allow us to store the address of the member.
3. Create a table LIBRARY_USERS which has a structure similar to that of the MEMBER table but with no records.
4. Give details about members who have issued books, which contain ‘DATA’ somewhere in their titles.
5. Display the books that have been issued at the most three times in the year 2003.
6. Display which books of publisher PHI that are issued right now.
7. Display details about books whose all copies are issued.
8. Display the book details and members for books, which have been issued between 1st Oct 2005 and 15th Nov 2005.
9. Display all staff members who have issued at least two books.
10. Display details about those publishers whose more than 100 books are available in the library.
11. Delete all those members whose membership has expired.
14How many members registered in the last three months ?
15Display since how many months has each staff member registered.
APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)
(This database is for a common entrance test which is being conducted at a number of
centers and can be taken by an applicant on any day except holidays)
1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. E.g. if value is ‘1123’, it should become ‘A1123’.
2. Display test center details where no tests were conducted.
3. Display details about applicants who have the same score as that of Jaydev in ‘ORACLE FUNDAMENTALS’.
4. Display details of applicants who appeared for all tests.
5. Display those tests where no applicant has failed.
6. Display details of entrance test centers which had full attendance between 1st Oct 05 and 15th Oct 05.
7. Display details of the applicants who scored more than the cut score in the tests they appeared in.
8. Display average and maximum score test wise of tests conducted at Mumbai.
9. Display the number of applicants who have appeared for each test, test center wise.
10. Display details about test centers where no tests have been conducted.
11. For tests, which have been conducted between 2-3-04 and 23-4-04, show details of the tests as well as the test centres.
12. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of February ?
13. Display details about applicants who appeared for tests in the same month as the month in which they were born.
14. Display the details about APPLICANTS who have scored the highest in each test,test centre wise.
15. Design a read only view, which has details about applicants and the tests that he has appeared for.
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)
(This database is for a common entrance test which is being conducted at a number of
centers and can be taken by an applicant on any day except holidays)
1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. E.g. if value is ‘1123’, it should become ‘A1123’.
2. Display test center details where no tests were conducted.
3. Display details about applicants who have the same score as that of Jaydev in ‘ORACLE FUNDAMENTALS’.
4. Display details of applicants who appeared for all tests.
5. Display those tests where no applicant has failed.
6. Display details of entrance test centers which had full attendance between 1st Oct 05 and 15th Oct 05.
7. Display details of the applicants who scored more than the cut score in the tests they appeared in.
8. Display average and maximum score test wise of tests conducted at Mumbai.
9. Display the number of applicants who have appeared for each test, test center wise.
10. Display details about test centers where no tests have been conducted.
11. For tests, which have been conducted between 2-3-04 and 23-4-04, show details of the tests as well as the test centres.
12. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of February ?
13. Display details about applicants who appeared for tests in the same month as the month in which they were born.
14. Display the details about APPLICANTS who have scored the highest in each test,test centre wise.
15. Design a read only view, which has details about applicants and the tests that he has appeared for.
functions in plsql