10:40
vishal pandya
SQL
Introduction to SQL, Advantages of using SQL
Database concepts
SQL concepts and tools
The generic SQL Sentence Construct
Create Table
The Create Table Command
Creating a table from a table
Insertion of Data into tables
Inserting of data into a table
Inserting of data into a table from another table
Viewing data in the tables
View all rows and columns
Selected columns and all rows
Select rows and all columns
Selected columns and selected rows
Elimination of duplicates from the select statement
Sorting of data in a table
Delete Operations
Remove of all rows
Removal of a specified row(s)
Update Operations
Updating of all rows
Updating records conditionally
Modifying the structure of tables
Adding new columns
Modifying existing columns
Renaming Tables
Destroying Tables
Examining Objects created by a User
Arithmetic Operators
Logical Operators
Range Searching
Pattern Matching
Column Alias
Aggregate Functions
Scalar Functions
Date Conversion Functions
Data Constraints
Defining integrity constraints in the alter table command
Dropping integrity constraints in the alter table command
Default Value Concept
Grouping Data from tables
Using the WHERE clause with grouped data
Using the HAVING clause with grouped data
Comparison of WHERE and HAVING
Manipulating dates in SQL
To_char, To_Date, Special Date Formats using to_char functions
Subqueries
Joins
Inner Join, Equi Joins, Self Join, Outer Joins
Union, Intersect and Minus Clause
Index
View
Sequence
PL/SQL
Understanding the main features of PL/SQL, PLSQL Architecture, advantages of using
procedures
The Generic PL/SQL Block
PL/SQL
Character Set
Literals
Data Types
Constant
Branching and Loop statements
Operators
Comments
Displaying user messages on the screen
Cursor
Implicit Cursor
Explicit Cursor
Cursor For Loops
Parameterized Cursors
Select for Update Cursors
Cursor Variables
Error Handling
User-Named Exception Handlers
User –Defined Exception Handlers (for I/O validations)
User–Defined Exception Handlers (for Business Rule validations)
Procedures
Functions
Packages
Triggers
SQL QUERIES( Based on DDL statement, constraints, DML statement,
SELECT statement and Views.)
Note : In all schemas, Create the table with necessary constraints ( PK, FK,
Notnull, Unique and Check constraints) on SQL prompt and then solve the
given queries.
Question 1
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’
Question 2
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.
Question 3
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.
Question 4
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.
Question 5
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.
Question 6
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.
Question 7
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.
Question 8
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.
LIST of queries that fall under different catageries
Topics Question
DDL( create, alter
and drop )
5(1),7(1,2,3)
DML ( insert,
delete,update)
5(4),6(4,6),7(11),8(1),3(1),2(1),
FUNCTIONS 2(6),5(8),6(5), 7(9,10),8(6,8,9),3(3,10,13)
Simple SELECT 2(3,5,8), 5(5,9), 6(1,3)
Natural JOIN 2(4), 5(10,18), 6(8,9,10,13), 7(8,9), 8(4,7,11,12,13), 3(5)
Subquery
5(3,7,12),4(1), 6(6,9,11,12,14), 7(4,10,15), 8(3,5,6),
3(4,6,7,9,12,13)
Group by and Order 2(6), 3(3,10,13), 5(8), 6(5), 7(9,10), 8(6,8,9)
Join using SET
operators
8(2,10), 3(14)
Complex Joins 5(14), 4(5), 7(6,7), 8(14), 3(8,15)
Date Functions 2(9), 4(3,4), 6(1,2,6,7,13), 7(5,8,15), 8(6, 11 ,12,13), 3(2,11)
Views 5(2,11), 8(15
Introduction to SQL, Advantages of using SQL
Database concepts
SQL concepts and tools
The generic SQL Sentence Construct
Create Table
The Create Table Command
Creating a table from a table
Insertion of Data into tables
Inserting of data into a table
Inserting of data into a table from another table
Viewing data in the tables
View all rows and columns
Selected columns and all rows
Select rows and all columns
Selected columns and selected rows
Elimination of duplicates from the select statement
Sorting of data in a table
Delete Operations
Remove of all rows
Removal of a specified row(s)
Update Operations
Updating of all rows
Updating records conditionally
Modifying the structure of tables
Adding new columns
Modifying existing columns
Renaming Tables
Destroying Tables
Examining Objects created by a User
Arithmetic Operators
Logical Operators
Range Searching
Pattern Matching
Column Alias
Aggregate Functions
Scalar Functions
Date Conversion Functions
Data Constraints
Defining integrity constraints in the alter table command
Dropping integrity constraints in the alter table command
Default Value Concept
Grouping Data from tables
Using the WHERE clause with grouped data
Using the HAVING clause with grouped data
Comparison of WHERE and HAVING
Manipulating dates in SQL
To_char, To_Date, Special Date Formats using to_char functions
Subqueries
Joins
Inner Join, Equi Joins, Self Join, Outer Joins
Union, Intersect and Minus Clause
Index
View
Sequence
PL/SQL
Understanding the main features of PL/SQL, PLSQL Architecture, advantages of using
procedures
The Generic PL/SQL Block
PL/SQL
Character Set
Literals
Data Types
Constant
Branching and Loop statements
Operators
Comments
Displaying user messages on the screen
Cursor
Implicit Cursor
Explicit Cursor
Cursor For Loops
Parameterized Cursors
Select for Update Cursors
Cursor Variables
Error Handling
User-Named Exception Handlers
User –Defined Exception Handlers (for I/O validations)
User–Defined Exception Handlers (for Business Rule validations)
Procedures
Functions
Packages
Triggers
SQL QUERIES( Based on DDL statement, constraints, DML statement,
SELECT statement and Views.)
Note : In all schemas, Create the table with necessary constraints ( PK, FK,
Notnull, Unique and Check constraints) on SQL prompt and then solve the
given queries.
Question 1
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’
Question 2
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.
Question 3
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.
Question 4
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.
Question 5
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.
Question 6
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.
Question 7
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.
Question 8
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.
LIST of queries that fall under different catageries
Topics Question
DDL( create, alter
and drop )
5(1),7(1,2,3)
DML ( insert,
delete,update)
5(4),6(4,6),7(11),8(1),3(1),2(1),
FUNCTIONS 2(6),5(8),6(5), 7(9,10),8(6,8,9),3(3,10,13)
Simple SELECT 2(3,5,8), 5(5,9), 6(1,3)
Natural JOIN 2(4), 5(10,18), 6(8,9,10,13), 7(8,9), 8(4,7,11,12,13), 3(5)
Subquery
5(3,7,12),4(1), 6(6,9,11,12,14), 7(4,10,15), 8(3,5,6),
3(4,6,7,9,12,13)
Group by and Order 2(6), 3(3,10,13), 5(8), 6(5), 7(9,10), 8(6,8,9)
Join using SET
operators
8(2,10), 3(14)
Complex Joins 5(14), 4(5), 7(6,7), 8(14), 3(8,15)
Date Functions 2(9), 4(3,4), 6(1,2,6,7,13), 7(5,8,15), 8(6, 11 ,12,13), 3(2,11)
Views 5(2,11), 8(15
0 comments:
Post a Comment