1
Practice 3 Solution
- Write a query that produces the following for each employee: <employee name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries. Sort the rows in descending of Dream Salary. Example, Ward earns 1250 monthly but wants 3750.
1)1 select ename||' earns '||sal||' monthly but wants '||sal*3 as "Dream salary"
2 from emp
3* order by sal DESC
Dream Salary
------
KING earns 5000 monthly but wants 15000
SCOTT earns 3000 monthly but wants 9000
FORD earns 3000 monthly but wants 9000
JONES earns 2975 monthly but wants 8925
BLAKE earns 2850 monthly but wants 8550
CLARK earns 2450 monthly but wants 7350
ALLEN earns 1600 monthly but wants 4800
TURNER earns 1500 monthly but wants 4500
MILLER earns 1300 monthly but wants 3900
WARD earns 1250 monthly but wants 3750
MARTIN earns 1250 monthly but wants 3750
ADAMS earns 1100 monthly but wants 3300
JAMES earns 950 monthly but wants 2850
SMITH earns 800 monthly but wants 2400
14 rows selected.
- Write a query that will display the employees’ names with the first letter capitalized and all other letters lowercase and the length of their name. Give each column an appropriate label.
2)1 select initcap(ename) as "Name", length(ename) as "Length of Name" from emp order by ename;
Name Length of Name
------
Adams 5
Allen 5
Blake 5
Clark 5
Ford 4
James 5
Jones 5
King 4
Martin 6
Miller 6
Scott 5
Smith 5
Turner 6
Ward 4
14 rows selected.
- Group functions work across many rows to produce one result per group. True/False
3)
True
- Group functions include nulls in calculations. True/False
4)
False
- The WHERE clause restricts rows prior to inclusion in a group calculation. True/False
5)
True
- Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Save your SQL statement in a file called p5q4.sql.
6)select Max(sal) as Maximum, Min(sal) as Minimum, Sum(sal) as Sum, avg(sal) as Average
2 from emp;
MAXIMUM MINIMUM SUM AVERAGE
------
5000 800 29025 2073.21429
SQL> save p5q4.sql
Created file p5q4.sql
- Modify p5q4.sql to display the minimum, maximum, sum, and average salary for each job type. Resave to a file called p5q5.sql. Rerun your query.
7)SQL> get d:\p5q4.sql
1 select Max(sal) as "Maximum", Min(sal) as "Minimum", Sum(sal) as "Sum", avg(sal) as "Average", Job as "Job Type"
2* from emp
SQL> edit
Wrote file afiedt.buf
1 select job as "Job Type", Max(sal) as "Maximum", Min(sal) as "Minimum", Sum(sal) as "Sum", avg(sal) as "Average"
2 from emp
3* group by job
SQL> /
Job Type Maximum Minimum Sum Average
------
ANALYST 3000 3000 6000 3000
CLERK 1300 800 4150 1037.5
MANAGER 2975 2450 8275 2758.33333
PRESIDENT 5000 5000 5000 5000
SALESMAN 1600 1250 5600 1400
SQL> save p5q5.sql
Created file p5q5.sql
- Write a query to display the number of people with the same job. (List the job and the number of people with that job.)
8)1 Select Job as "Job", Count(job) as "Number of people in that job"
2 from emp
3* group by job
SQL> /
Job Number of people in that job
------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
- Determine the number of managers without listing them. Label the column Number of Managers.
SQL> select count(DISTINCT MGR) AS "Number of Managers" FROM emp;
Number of Managers
------
6
- Write a query that will display the difference between the highest and lowest salaries. Label the column DIFFERENCE.
10)1 select Max(sal)-Min(sal) as "Salary Differences"
2* from emp
3 ;
Salary Differences
------
4200
To Put It All Together:
- Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $1000. Sort the output in descending order of salary.
SQL> SELECT mgr, MIN(sal)
2 FROM EMP
3 WHERE mgr IS NOT NULL
4 GROUP BY mgr
5 HAVING MIN(sal) > 1000
6 ORDER BY MIN(sal) DESC;
MGR MIN(SAL)
------
7566 3000
7839 2450
7782 1300
7788 1100