Practice Problems (SQL)
Problem# 1 (Introduction to SQL) 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 1.10 1.11 1.12
1.13
Select all information from SALGRADE table. Select all information from EMP table. List all employees who who have a salary salary between 1000 and and 2000. List department department numbers numbers and and names in department department name name order. order. Display all the different job types. List the details details of the employees in departments 10 and 20 in alphabetical alphabetical order of names. List names names and and jobs of all clerks in department department 20. Display all employees names names which which have TH or LL in them. List details for all all employees who have a manager. manager. Display names and total remuneration for all employees. Display all employees who were hired during 1983. Display name, annual salary and commission of all salespeople whose monthly salary salary is great greater er than than their their commis commissio sion. n. The output output should should be ordere ordered d by salary, in descending order. Display all employee information in ascending order of manager number. King must be last.
Problem# 2 (Using Functions) This exercise covers functions not just in the SELECT but also in WHERE and ORDER BY clauses. Note the column alias, which have been used. 2.1 2.2 2.3
List the employee name and salary increased increased by 15% and expressed expressed as a whole number of dollars. Display each each employee’s employee’s name name and and hiredate hiredate from dept. 20. Display Display each employee employee name with hiredate, hiredate, and salary review review date. Assume Assume review date is one year after hiredate. Order the output in ascending review date order. Problem# 3 (Group Functions)
3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8
Find the minimum salary of all employees. Find the minimum, maximum maximum and average salaries of all employees. List the minimum and maximum salary for for each job type. Find out how many many managers managers there there are without listing listing them. Find the average average salary and and average total total remuneratio remuneration n for each job type. Remember salesmen earn commission. Find out the difference difference between between highest highest and lowest salaries. salaries. Find all departments which have more than than 3 employees. List lowest paid paid employees working working for each manager. manager. Exclude Exclude any groups where the minimum salary is less than 1000. Sort the output by salary. Problem# 4 (Joins)
4.1
Display Display all employee employee names and their their department department name, in departmen departmentt name order.
4.2 4.3
Display all employee names, department number and name. Display the name, location and department department of employees employees whose salary is more than 1500 a month. 4.4 Produce a list showing showing employees’ salary grades. 4.5 Show only employees on grade 3. 4.6 Show all employees in Dallas. 4.7 List the employee employee name, job salary, grade and department name name for everyone everyone in the company except clerks. Sort on salary, displaying the highest salary first. 4.8 Display the department that has no employees. 4.9 List all employees employees by name name and number number along with their manager’s manager’s name name and number. 4.10 Modify solution to question 10 to display KING who has no manager. 4.11 Find all employees who joined the company before their manager. Problem# 5 (Sub queries) 5.1
Write a query to display the the employee name name and hiredate hiredate for all employees employees in the same department as Blake. Exclude Blake. 5.2 Create Create a query to display display the employee employee number and name name for all employees employees who earn more than the average salary. Sort the results in descending order of salary. 5.3 Write a query query that will display display employee employee number number and name for for all employees employees who work in a department with any employee whose name contains a T. 5.4 5.4 Disp Displa lay y the the empl employ oyee ee name name,, depa depart rtme ment nt numb number er,, and and job job title title for for all all employees whose department location is Dallas. 5.5 Display the employee and salary of all employees who who report to King. 5.6 Display the department number, number, name, and and job for all employees in the Sales Sales department. 5.7 Write Write a query query to displa display y the name, name, departme department nt number, number, and salary salary of any employee whose department number and salary match the department number and salary of any employee who earns a commission. 5.8 Display the name, department department name, and salary salary of any employee whose whose salary and commission match the salary and commission of any employee located in Dallas. 5.9 Create a query query to display display the name, hiredate, and salary for all employees who who have the same salary and commission as Scott. 5.10 Create a query to display the employees that earn a salary that is higher than the salary of all of the clerks. Sort the results on salary from highest to lowest.
