Internal Exam Queries PDF

Tables Customer_T(customer_id,customer_name,customer_address,city,state, postal_code) Order_T(order_id,order_date,customer_id) Product_T(product_id,product_description,product_finish,standard_price,product_line _id) Order_line_T(order_id,product_id,ordered_quantity)...

Tables Customer_T(customer_id,customer_name,customer_address,city,state, postal_code) Order_T(order_id,order_date,customer_id) Product_T(product_id,product_description,product_finish,standard_price,product_line _id) Order_line_T(order_id,product_id,ordered_quantity) emp(e_no, e_name, address, basic_salary, job_status) Projects(p_no,p_name,no_of_staff) Works_in(p_no, e_no, p_job) Create a table called subjects with the following schema: Subjects (subcodevarchar2(10),subname varchar2(15),version number(3,1), fulldur number(3), partdur number(3)) Supplier(sid,name,city) Parts(pid,pname,colour) Catalog(sid,pid,cost) Student(Student_id,Student_name) Faculty(Faculty_id,Faculty_name) Course(Course_id,Course_name) Is_registered (Student_id,Section_id,Semester) Section(Section_id,Course_id) Is_qualified(Faculty_id, Course_id, Data_qualified) Create a table called subjects with the following schema: Subjects (subcodevarchar2(10),subname varchar2(15),version number(3,1), fulldur number(3), partdur number(3)) 1. Create view “invoice” for a customer containing customer_id, customer_address,order_id,product_id,prouct_description,ordered_quantity? 2. What is the avg standard price for each product? 3. Which orders have been placed since 10/24/2000? 4. List product name, finish and unit price for all desks and tables that cost more than $300? 5. List all furniture products that have ever had a UNIT_PRICE over $300? 6. Write a query that returns empno,empname whose empno is either 7,5,10 or 12? 7. Write a nested query that displays the names of the employees who are working in the project “DBMS”. 8. Display names of the employees who are working in the same dept with P.Gupta. 9. Find the names of employees who are working for a particular project? 10. Find the project that has more than one coordinator? 11. Display names of the employees whose job_status begins with “office” using string handling function 12. Write a query to add one more column deptno to the table ‘emp’ and append the values to it. 13. Write a sql statement that displays names of those employees of the dept with deptno 3 who get more salary than the highest paid employee of the dept with deptno 5. 14. Write a query that determines for each department the number of employees who are having the same job_status. 15. Write a query that returns empno,empname whose empno is either 7,5,10 or 12? 16. Compute total and average salary of professors, research fellows and office assistants. 17. Create the following relation accounts(e_no,basic_sal,DA,gross)from the existing relation employees computing DA as 29% of the basic and gross as basic plus DA. 18. Increment DA from 29% to 30% of the basic of those employees whose basic salary is less than 3000? 19. Find out the departments in which there are at least one employee with a job_status=”professor”. 20. Write a nested query that displays the name of all the employees who do not work in the department where P.Sen is working. 21. Write a nested query that displays name, job_status and the basic_salary of those employees in A.sarkar’sdept who get the same salary as him. 22. Display the names of all the projects in which the employees are working. 23. Create the following relation accounts(e_no,basic_sal,DA,gross)from the existing relation employees computing DA as 29% of the basic and gross as basic plus DA. 24. Increment DA from 29% to 30% of the basic of those employees whose basic salary is less than 3000? 25. Find the names of employees who are located at the same place with a basic-salary greater than or equal to 5000? 26. Display the details of subjects whose subject code ends with letter ‘S’. 27. Display the details of the subjects whose subject code starts with letter ‘C’ and it consists of only two characters. 28. Display the details of subjects whose version is null 29. Delete the subject details whose full duration is 30. 30. Update full duration as 50 Hrs for subject code ‘CO’ 31. Display subject code, name, version, full duration for all subjects whose full duration is greater than 20 Hrs 32. Display subject code, name for subjects whose full duration is greater than 20 and part duration is less than or equal to 60 33. Display subject code, name and version for the subjects whose version is either 3.0 or 2.2 or 4.0. 4)Display subject code, name, and version for subjects whose subject code starts with letter ‘D’. 34. Display the details of subjects whose subject code contains letter ‘D’. 35. Create a view on the Employee database and perform DML statements