Assignment 3
Consider the following table
1) Student (stud_lib_card_no, stud_name, class)
2) Book (Book_id, category, title, author, price, status)
3) Book_issue (Book_id, stud_lib_card_no, issue_Date, return_date,fine_charged,fine_paid)
Solve the following queries based on above tables
1. Create above three tables with proper constraints (primary key, foreign key etc)
2. insert appropriate data in the above tables( Add minimum 5 records in each table)
3. Display all books written by author LEE.
4. List all books with costs more than Rs 150/- and are issued to students of MCM class.
5. Delete all books having status LOST.
6. Count the books under the category ‘SYSTEM’.
7. Find the student who has paid maximum fine.
8. Increase the fine charged by 10% for all students whose return date is 15 days overdue.
Assignment 4
Consider the following table
1) Patient (Patient_no,patient_name, p_add,p_birth_date,p_phone)
2) Room (Room_id, Room_type room_rate)
3) Doctor (doc_id,doc_name,doc_add,doc_type,doc_mobile)
3) Patient_transaction_table (patient_no,doc_id,room_id,patient_admit_date,patient_dischage_date,
total_room_charges,treatment_name,tretmenat_charge,total_bill_amout)
Solve the following queries based on above tables
Create above four tables with proper constraints (primary key, foreign key etc)
insert appropriate data in the above tables( Add minimum 5 records in each table)
Display all patient details who are taking the treatment under the doctor LELE.
List all the doctors who are visiting.
Display all patient details whose total_bill_amout is greather than 20000 Rs.
Count total doctors those who are visiting.
Display all patient details those who are more than 10 days in hospital.
List all patient who are admitted today.
__________
Assignment 2
Oracle queries
1.
Display names and job of the employee who is clerk.
2.
Display names and department number of the employee whose job is analyst or clerk.
3.
Display the list of employee sorted on name by descending.
4.
Display different kind of job available.
5.
Display list of employee whose deptno is 30.
6.
Retrive all employees whose names are 5 letters long.
7.
Display all employees whose names ending with R.
8.
Display deptno and department name whose deptno is greather than 20
9.
Display jobs,deptno,name of employee whose name start with ‘B’ or ’M’ and display it in ascending order of deptno.
10.
Display name, salary and commission of emp whose commission is more than 5% of salary.
11.
Display all employee hired in the month of DECEMBER.
12.
Display all employee hired after 1981.
13.
How many months has the president work for the company.
14.
If new person ‘AMOL’ joined the company in place of turner on 5th sep-2006 with empno 7999 make the necessary changes in the emp table.
15.
Pramote james to manager of deptno 10 with pay hike Rs 1000 make necessary changes in emp table.
16.
Display employee name and jobs in the following format
Smith-----------------Clerk
17.
List the name and hiredate of the emp in the deptno 20 display in the following format.12/03/2004
18.
Find the day of week on which smith joins.
19.
How many months ADAMS working in the company.
20.
Find out the average annual salary per job in each department.
21.
Count number of people in deptno 30.who receives salary and number of people who receives commission.
22.
Calculate average salary,minimum salary,maximum salary of those group of employee whose job is clerk or manager.
23.
Display the deptno of dept which have more than one clerk.
24
Display the employee whose commission is null and salary does not exceed Rs 3000;
25
List the year of joining of all employee’s in words.
26.
Show the length of enames in emp table eliminate duplicate names does not show names.
27.
How many years has president worked for the company?
28.
Display the name of employee,salary whose dept location is CHICAGO.
29.
Find out department where maximum employees are working
30.
Find out difference between maximum salary of deptno 10 and minimum salary of deptno 30.
31.
Find out employee’s whose salary is less than average salary of deptno 20.
32.
Display name of employee who are reporting to BLAKE.
33.
List all employee’s who hired earliest and latest
34.
Display the names of all employees where the third letter of the name is an A.
35.
Display the names of all employees who have and A and N in their name.
36.
Display the name ,job, salary for all employees whose job is SALESMAN or CLERK and whose salary is not equal to 1500, 1300.
37.
Write a query to display the current date, Label the column Date.
38.
Write query for each employee number,name,salary, and salary increased by 15% and expressed as a whole number.Label the column New salary.
39.
Write a query to display the employees names with the first letter capitalized and all other letters lowercase and the length of the name for all employees whose name starts with A or B or J. and sort the results by employees name.
40.
Write a query that produces the following for each employee.
JAMES Earns $950. Monthly but wants $2,850.00.
41.
Write a query to display the name, department number and department name for all employees.
42.
Write a query to display the number of people with the same job.
43.
Write a query to count the number of managers without listing them.Label the column “Number of Managers”
44.
Write a query to displays the difference between the highest and lowest salaries. Label the column DIFFERENCE
45.
Write a query to displays the name and hiredate of any employee in the same department as