1.1. Exercise
Data Retrieval
Part I
1.
See table
structure of Designation_Master, Department_Master, Student_Master,
Student_Marks, Staff_Master, Book_Master and Book_Transaction table.
2.
Query
Student_Code, Student_Name and Department_Code of every Student. Retrieve
detail of every Student and Staff.
3.
Display
Student_Code, Subjects and Total_Marks for every student. Total_Marks will
calculate as Subject1 + Subject2 + Subject3 from Student_Marks.
4.
. Display
Student_Code, Student_Year, Subjects and Percentage of all student and heading
for the columns would be Student#, Join_Year, Subject1, Subject2, Subject3,
Total, and Percentage from Student_Marks.
5.
Create a
query to display unique Department_Code from Student_Master table.
6.
. Create
a query which will display all students’ data those who are passing (getting
pass Subject1 + Subject2 + Subject3 > 180. And save the query
7.
. Create
a query which displays Student_Code, Join_Year and Subject 1 of all students
who scored more than 75 in Subject1. And save the query
8.
Modify the
query to display student detail of those who getting marks in Subject1 between
50 and 75. And save the query .
9.
Write a
query which will display Student_Name, Departmnet_Code and DOB of all students
who born between January 1, 1981 and March 31, 1983. Order the query in
ascending order of DOB.
10.
. Write a
query which will display Student_Name, Department_Code of all students’ studies
in departments 10 and 30, in alphabetical order by name.
11.
Select
Student_Name and Department_Code of all students those who have born in 1982.
12.
. Display
Book_Details for all books which have not being returned.
13.
. Display
Book_Details for all books which have not being returned and expected returned
was last Monday.
14.
. Select
all students who have completed 25 years of age and their name starts with ‘S’;
15.
. Display
Student_Code & Year, of all students who is getting more than 60 in each
subject and aggregate of marks is greater then 190 for year 2006 or 2007.
16.
. Display
Student_Code, Subject1, Subject2, Subject3, Total_Marks, and Percentage as
whole number for current year and percentage should be greater that 75.
17.
Modify
the query to add a column that will find the difference between Total_Marks and
Maximum_Marks. Label the column as “Diff_Marks”.
18.
Write a
query which displays Student_Name in upper case, Student_Name first letter
capitalized and all other letters lower case and length of the Student_Name for
all students whose name starts with ‘S’
or ‘J’.
19.
. Create
a Query which displays the Student_Name and Department_Code. If student does
not belong to any department, put “No Department”. Label the column as
“Department”.
20.
. Create
a Query which display the Student_Name and Department_Code and Total_Marks and
order it on Total_Marks descending and Student_Name ascending.
21.
. List
the Book details issued to Students for current month and last month.
22.
. List
all Table Name contains ‘MASTER’ in their names.
23.
. Display
the Staff_Name Staff_Salary and the Salary in X. Each X represents a 1000 in
Salary.
Sample
Output.
KING 10000 XXXXXXXXXX
FORD 12000
XXXXXXXXXXXX
24. Display
Student_Name and Date of birth where DOB must be displayed in the format
similar to “January, 12 1981” for those who born on Saturday or Sunday.
25. Display
Staff_Name, Hiredate and review date of all staff. Review date is first Monday
after six months of Service. Label the Column REVIEW
26. Display
each Staff_Name and number of months he worked for the organization. Label the
column as ‘MONTHS WORKED’. Order your result by number of months employed.
Round the number of months to closest whole number.
27. Write
a query that produces the following for each staff :
28. <Staff_Name>
earns <Salary> monthly but wants <2 times salary. Label the column as
DREAM SALARY. Format the dream salary as Rs. 99,999.00.
29. Create
a query which will display Staff_Name, Salary of each staff. Format the salary
to be 15 character long and left padded with ‘$’.
30. Display
the Staff_Name, Hiredate and day of the week on which staff was hired. Label
the column as DAY. Order the result by the day of the week staring with Monday.
31. Write
a query that displays Staff_Name, Salary, and Grade of all staff. Grade depends
on the following table.
Salary
|
Grade
|
Salary
>=50000
|
A
|
Salary
>= 25000 < 50000
|
B
|
Salary>=10000
< 25000
|
C
|
OTHERS
|
D
|
32. Write
a query to find the pay date for the month. Pay date is the last Friday of the
month. Display the date in the format “Twenty Eighth of January, 2002”. Label
the heading as PAY DATE.
33. Display
the Highest, Lowest, Total & Average salary of all staff. Label the columns
Maximum, Minimum, Total and Average respectively. Round the result to nearest
whole number. Save the query as E2Q20.
34. Edit
the above query and display the same for each Department Name.
35. Write
a query to display number of people in each Department. Output should display
Department_Code, Department_Name, and Number of People.
36. Determine
the number of managers without listing them. Label the column as ‘Total Number
of Managers’.
37. Display
Manager_Code, Manager_Name and salary of lowest paid staff in that manager’s
team. Exclude any group where minimum salary is less than 10000. Order you
result on descending order of salary.
38. Display
the Manager_Name and the total strength of his/her team.
39. Create
a query which will give the following output ignore the lines.
Dept_Code
|
1980
|
1981
|
1982
|
1983
|
Total
|
10
|
2
|
1
|
3
|
||
20
|
1
|
2
|
1
|
1
|
5
|
30
|
6
|
6
|
Part
II
40. Write
a query which displays Staff_Name, Department_Name and Department_Code Salary
for all staff who earns more than 20000.
41. Write
a query to display Staff_Name, Department_Code, and Department_Name for all
staff who do not works in Department code 10 and have ‘A’ in their name.
42. Display
Staff_Code, Staff_Name Department_Name, and his manager’s number and name.
Label the columns Staff#, Staff_Name, Mgr#, Manager.
43. Create
a query that will display Student_Code, Sudent_Name, Department_Name, Subjec1,
Subject2, and Subject3 for all students who are getting more than 60 in each
subject from department 10 and 20.
44. Create
a query which will display Manager_Name, Staff_Name, Salary, Staff Hiredate,
Manager Hiredate of all staff hired before their manager.
45. Create
a query that will display Student_Code, Sudent_Name, Department_Name,
Book_Code, Book_Name for all students whose expected book return date is today.
46. Create
a query that will display Staff_Code, Staff_Name, Department_Name, Designation,
Book_Code, Book_Name, Issue_Date. For only those staff who have taken any book
in last 30 days.
47. Generate
a report which contains the following information.
Staff Code Staff Name Designation Department Name Department Head
For all staff excluding HOD (List
should not contain the details of Department head).
48. Generate
a report which contains the following information
Student Code Student Name Department
Name Total Marks HOD Name
Sort the output on Department Name and
Total Marks.
49. Generate
a report which contains the following information.
Staff Code, Staff Name, Designation,
Department, Book Code, Book Name, Author, Fine
For the staff who have not return the book. Fine will be
calculated as Rs. 5 per day.
Fine = 5 * (No. of days = Current Date
– Expected return date).
50. List
Staff_Code, Staff_Name, and Salary for those who are getting less than the
average salary of organization.
51. List
the Staff_Code, Staff_Name who are not Manager.
52. Display
Author_Name, Book_Name for those authors who wrote more than one book.
53. Display
Staff_Code, Staff_Name, Department Name for those who have taken more than one
book.
54. Display
top ten students for a specified department.
Details are:
Student_Code, Student_Name, Department_Name, Subject1,
Subject2, Subject3, Total.
55. Display
the Staff_Name, Department_Name, and Salary for those staff who are getting
less then average salary in their own department
56. Create
a query that will display the Staff_Name, Department_Name, and all the staff
that work in the same department as a given staff. Give the column as
appropriate label.
57. List
the Student_Code, Student_Name for that student who got highest marks in all
three subjects in Computer Science department for current year.
58. Display
the Student_Code, Student_Name, and Department_Name for that department in
which there are maximum number of student are studying.
59. Display Staff_Code, Staff_Name,
Department_Name, and Designation for those who have joined most recently.
60. List
the total staff and the number of staff hired in each Department
Department No No. of Staff
10 30
20 40
30 25
Total 95
61. Display
the Manager_Name, Staff_Name, Salary (The highest and the lowest paid staff in
his team).
Manager Name Staff_Name Salary
Allen Amit 10000
Allen Raju 20000
Raj Ajay 30000
62. Write
a query which will display Staff_Name, and Department_Name, Designation of all
staff. Also display the department name where there is no staff. Display”NO
STAFF” where there is no staff in the department.
63. List
the books that are returned on expected date, before expected date and after
expected date. Calculate the fine for books returned after due date as Rs. 5
per day. Output should be in following format.
Book Code Book Name Author Status Fine
100001 Java
Complete Reference Schield Expected date 0
100002 C++
Complete Reference Schield Before date 0
100001 Oracle
Complete Reference Scott After date 15
Total
15
64. Accept
a specific book code from user and find the details of the students/staff who
have borrowed that book and has not returned the same. The following details
should be displayed
Student/Staff Code Student/Staff Name Issue Date Expected
Return Date
65. Accept
the student code and display only faculty details of that department. Display
Details for only (HOD/Professor/Reader/Lecturer).Use Department Master &
Designation Master.
Output should be in following format.
Faculty
Code Faculty Name Designation Department Name
66. List
the department Name which has borrowed maximum number of time (Any book).
67. .List
the faculties of the department in which the student obtaining the maximum
total marks for current year
68. For
current year in which month maximum numbers of books are borrowed by which
department?
69. Send a report to every department in the
following format:
Dept
Name, Total No: of students yet to return the book
70. List
the details of the book which has not been borrowed so far.
71. List the student detail, who has borrowed
maximum number of books.
72. Generate
the report that displays details for all books.
Book
Code Book Name Author Number of Times Issues
73. Generate
a report which contains the following information.
Book
Code Book Name Staff Code Staff
Name Designation Department Name Number of
Times Issued
For current year only.
DML & DDL Statements
Q1. Add
a new staff with Staff_Code: 8888, Name: HARRIS, Mgr_Code: 7566, Hiredate:
08-APR-1985, Sal: 25000, Dept_Code: 20.
Q2. Change
the Miller’s department from 10 to 20.
Q3. Give
all the staff in department 30 a salary hike of 10%.
Q4. Delete
all staff from department 20.
Q5. Create
a table Dept_Master with the following columns
DeptNo – Primary Key
DeptName -- It should not be null
Q6. Create
a table Staff_Master_Dup which contains the following columns:
Staff_Code -- Primary key should generate numbers
automatically
Staff_Name – Should not be null
Designation –Should always be one of them HOD / Reader/
Professor/ Lecturer.
Q7. Salary
– Should be always >7000
Q8. HireDate
–Default should be current date
Q9. Dept_Code
– Should refer to the DEPT_Code in Dept_Master
Q10.
Insert the values.
OTHER OBJECTS
Q1. Create
a view consisting of names, code, designation, salaries and departments of all
staff in department 10.
Q2. From
the above view list the average annual salary for all the people in department 10
who makes less than 5000.
Q3. Create
a view FINANCE with data from the Staff_Master which gives their total salary
and average salary.
Q4. Create
a view Staff_VU from Staff_Master, Designation_Master & Dept_Master to make
it possible to see where some one works.
Q5. Display
the structure of the view Staff_VU.
Q6. Display
the contents of the STAFF_VU.
Q7. Select
view name and text from USER_VIEWS.
Q8. Create
a view name DEPT20 that contains the staff code, staff name and department
number for all staff in department 20. Label the view column Staff_Code,
Staff_Name and Dept_Code. Do not allow a staff to be reassigned to another
department through this view.
Q9. Display
the structure of the DEPT20 view.
Q10.
Assign SMITH to department 30. What
happened?
Q11.
Create a view called SALARY_VU based on
the Staff_Name, Department_Name, Salary and salary grade for all staff. Label
the columns staff, Department, Salary and Grade respectively.
Q12.
Create an index on salary column of
Staff_Master table.
Q13.
Confirm the same from data dictionary
that the index is created.
Q14.
Create a table called TEST with one
column as Primary key.
Q15.
Confirm that an index is created
implicitly.
Q16.
Drop the table TEST and check that the
corresponding index is also dropped.
Q17.
Create an index on salary of
Staff_Master table.
Q18.
Create an index on Department_Code and
Designation_Code of Staff_Master table.
Procedures
, Function, triggers
Q1.Write a sql script to calculate bonus of staff for a given Staff_Code. Bonus is twice
of salary. Handle exception if Staff_Code is not present or if it returns for
more than one staff information.
Q2. Write a sql script to retrieve all staff under specific
department number and display the result. (Note: The Department_Code will be
accepted from user. Display the error message if the wrong department code has
entered.)
Q3. Write a sql script block to increase
the salary by 30 % or 5000 whichever minimum for a given Department_Code.
Display the error message if wrong department code has entered.
Q4. Write a sql script that generated the following report for a
given Department code
Student_Code Sudent_Name
Subject1 Subject2 Subject3
Total Percentage Grade
Note: Display suitable error massage if
wrong department code has entered and if there is no student in the given
department.
For Grade:
Student should pass in each subject
individually (pass marks 60).
Percent >= 80 then grade= A
Percent >= 70 and < 80 then
grade= B
Percent >= 60 and < 70 then
grade= C
Else D
Q1. Write a function to compute area of
circle. [Formula: PI * Radius**2].
Q2. Write a function to compute age. The
function should accept a date and return age in years.
Q3. Write a procedure that accept staff
code and update staff name to Upper case. If the staff name is null raise a
user defined exception.
Q4. Write a procedure to find the manager
of a staff. Screen should display the following data – Staff_Code, Staff_Name,
Dept_Code and Manager Name.
Q5. Write a function to compute the
following. Function should take Staff_Code and return the cost to company.
DA = 15% Salary, HRA= 20% of Salary,
TA= 8% of Salary.
Special Allowance will be decided based
on the service in the company.
< 1 Year Nil
>=1 Year< 2 Year 10% of Salary
>=2 Year< 4 Year 20% of Salary
>4 Year 30% of Salary
Q6. Write a procedure that displays the
following information of all staff
Staff_Name Department Name Designation Salary Status
Note: - Status will be (Greater, Lesser
or Equal) respective to average salary of their own department. Display an
error message Staff_Master table is empty of if there is no matching record.
Q7. Write a procedure that accept
Staff_Code and update the salary and store the old salary details in
Staff_Master_Back (Staff_Master_Back has the same structure without any
constraint) table.
Exp < 2 then no Update
Exp > 2 and < 5 then 20% of
salary
Exp > 5 then 25% of salary
Q8. Create a procedure that accepts the
book code as parameter from the user. Display the details of the students/staff
that have borrowed that book and has not returned the same. The following
details should be displayed
Student/Staff Code Student/Staff Name Issue Date Designation
Expected Ret_Date
Q9.Write the following
Function: This function will return years of
experience for a staff. This function will take the hiredate of the staff as an input parameter.
The output will be rounded to the nearest year (1.4 year will be considered as
1 year and 1.5 year will be considered as 2 year).
Procedure:
Capture
the value returned by the above function to calculate the additional allowance
for the staff based on the experience.
Additional Allowance = Year of
experience x 3000
Calculate the additional allowance and store Staff_Code, Date of Joining, and
Experience in years and additional allowance in Staff_Allowance table.
Q10. Generate a report which
displays the following information.
Student Code Student Name Department
Name Total Marks HOD Name
For only top three students from each department and for
current year.
Q11. Generate a report which
displays the following information.
Book
Code Book Name Issue Date (Stu/Staff)
Code (Stu/Staff) Name Designation
10001 C++ 11-Jan-07 1001 Allan HOD
10005 Java 15-Jan-07 10011 Ravi Student
10009 Oracle 18-Jan-07 1005 Arvind Lecturer
Q12. Generate a report Using which
displays the following information.
Department
Name:
Computer Science
Book
Code Book Name Issue Date
Student_Code Student
Name
10001 C++ 11-Jan-07 10012 Raju
10005 Java 15-Jan-07 10011 Ravi
Department
Name: Robotics
Book
Code Book Name Issue Date
Student_Code Student
Name
10009 Oracle 18-Jan-07 10015 Arvind
No comments:
Post a Comment