all questions are for Oracle practice

Write down the SQL statements for the following.

1. select all information from emp;
2. list all employee who have salary between 2000 and 5500.
3. List department numbers and names in department name order;
4. Display all the different job types.
5. List the details of the employees in departments 10 and 20 in alphabetical order of name.
6. List names and jobs of all clerks in department 20.
7. Display all employees names which have TH or LL in them.
8. List all employees who have a manager.
9. Display name and total remuneration for all employee.
10. Display all employee who were hired during 1981.
11. Find the minimum salary of all employees.
12. Find the minimum, maximum and average salaries of all employees of department 20.
13. List the minimum and maximum salary for each job type.
14. Find out how many managers there are without listing them.
15. Find the average salary and average total remuneration for each job type. Remember salesmen earn commission.
16. Find out the difference between highest and lowest salaries.
17. Find all departments which have more than 3 employees.
18. List lowest paid employees working for each manager. Exclude any groups where the minimum salary is less then 1000. Sort the output by salary.
19. Display all employee names and their department name, in department name order.
20. Display all employee names, department number and names.
21. Display the name, location and department of employees whose salary is more than 1500 a month.
22. Produce a list showing employees salary grades.
23. Show only employees on Grade 3.
24. Show all employees in ‘Dallas’.
25. List the employee name, job, salary, grade and department name for everyone in the company except clerks. Sort on salary, displaying the highest salary first.
26. List ename, job, annual_sal, deptno, dname, grade from emp, dept and salgrade table who earn 36000 a year or who are ‘clerks’.
27. Display the department that has not employees.
28. List all employees by name and number along with their manager’s name and number.
29. Modify solution to question 2 to display IND who has no manager.
30. Find all employees who joined the company before their manager.
31. Find the job that was filled in the first half of 1983, and the same job that was filled during the same period in 1984.

Question colection of database part iii

Brief Answer Questions: [1.5x10=15]

1. List out the three major disadvantages of File Processing System.
2. Define strong entity and weak entity.
3. What do you mean by Integrity Constraints?
4. Describe one-to-many relationship with suitable example.
5. Give syntax for ‘group by’ and ‘order by’ clause.
6. What is specialization and generalization?
7. Differentiate between Cartesian product operation and Natural join operation in relation algebra.
8. Define the difference between HAVING and WHERE clause with example.
9. Define Domain with example.
10. Give different symbols of ER Diagram with name.

Short Answer Questions: [5x3=15]

11. Explain Data Model in DBMS.
12. Explain the major advantages of DBMS over file processing system.
13. Draw an ER Diagram for Library Management System with at least four entity sets and assume required attributes.

Question collection of database part 2

1. Match the following terms and definitions:

……… Data ………..Repository ……….Metadata

……… Database Application ..……… Data Warehouse ……….Constraint

………. Database …………Information … ……User view

……….. Data independence

1. data placed in a context or summarized
2. application program(s)
3. facts, text, graphics, images, etc
4. integrated decision support database
5. organized collection of related data
6. includes data definitions and constraints
7. centralized storehouse for all data definitions
8. separation of data descriptions from programs
9. a rule that cannot be violated
10. logical description of portion of database

2. List any four significant differences between a file-processing system and a DBMS.

3. What are five main functions of a DBA.

4. Define Data Model with its types.

5. Explain the difference between physical and logical data independence.

Question collection of database part 1

1. Is there any difference between Database and Database Management System? Explain.
2. Explain Network Model and Hierarchical Model with diagram.
3. Design a ER Diagram for Hospital Management System.
4. Write short notes on (Any TWO)
1. Primary Key
2. Mapping Constraints
3. Instances and Schema
5. Write down SQL statements for following (Any THREE)
1. Display all employee whose name is starting from letter ‘R’ and ending with letter ‘H’.
2. Display all employees no, name, annual salary of department 20.
3. Display all employees whose salary is greater than their commission.
4. Display all employees working under department no 10,20,30.

6. Who is Database Administrator (DBA)? Explain their task and dutie