2. List employees name, job, and salary that is a manager and has a salary > $1,000
select ename, job, sal from emp where job = 'MANAGER' and sal > 1000
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
3. Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
select ename, job, sal from emp where job 'MANAGER' or sal > 1000
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
14 rows selected.
4. Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
SQL> select ename, sal 2 from emp 3 where sal between 1000 and 2000
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
6 rows selected.
5. Select all employees that are in department 10 and 30. Use the IN operator.
SQL> select * from emp 2 where deptno in (10, 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10
9 rows selected.
6. Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
select ename from emp where ename like 'A%';
ENAME
----------
ALLEN
ADAMS
7. Select all employees with an “A” as the second character of their name. Use a wildcard.
select * from emp where ename like '_A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7900 JAMES CLERK 7698 03-DEC-81 950 30
3 rows selected.
8. List the employee names in alphabetical sequence.
SQL> select ename from emp 2 order by ename;
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
14 rows selected.
9. List the job, salary, and employee name in job order and then salary in descending order.
SQL> select job, sal, ename 2 from emp 3 order by job, sal desc
JOB SAL ENAME
--------- ---------- ----------
ANALYST 3000 FORD
ANALYST 3000 SCOTT
CLERK 1300 MILLER
CLERK 1100 ADAMS
CLERK 950 JAMES
CLERK 800 SMITH
MANAGER 2975 JONES
MANAGER 2850 BLAKE
MANAGER 2450 CLARK
PRESIDENT 5000 KING
SALESMAN 1600 ALLEN
SALESMAN 1500 TURNER
SALESMAN 1250 MARTIN
SALESMAN 1250 WARD
14 rows selected.
10. Show a list of different jobs. Eliminate repeating values.
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
11. Show employee names, salary that has not earned a commission yet.
SQL> select ename, sal from emp 2 where comm = 0 or comm is null;
ENAME SAL
---------- ----------
SMITH 800
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
11 rows selected.
12. Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
SELECT ename AS NAME, sal AS SALARY, comm AS COMMISSION, (sal + NVL(comm,0)) AS TOTAL_SALARY FROM emp;
NAME SALARY COMMISSION TOTAL_SALARY
---------- ---------- ----------------- -------------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
Chapter 2
13. Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
SELECT course_no, description, cost, prerequisite, created_by
FROM course
WHERE cost = 1195 AND description NOT LIKE 'Intro%'
ORDER BY NVL(prerequisite,0) DESC;
14. Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
select student_id, first_name, last_name, zip, phone, employer from student where last_name between 'A' and 'T' and employer = 'Competrol Real Estate' order by last_name;
STD_ID FIRST_NA LAST_NAM ZIP PHONE EMPLOYER
------ -------- -------- ----- --------------- ------------------------------------------------ 147 Judy Cahouet 11219 718-555-5555 Competrol Real Estate 337 Preston Cross 11413 718-555-5555 Competrol Real Estate 148 D. Orent 07011 201-555-5555 Competrol Real Estate 149 Judith Prochask 07078 201-555-5555 Competrol Real Estate
Chapter 3
15. Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.
SELECT student_id, last_name
FROM student
WHERE last_name BETWEEN 'a' AND 'z' ;
16. Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format.
select last_name, phone from instructor where translate(phone, '0123456789', '#########') '(###)###-####'
22. Show the employee name with the maximum salary.
SELECT ENAME
FROM emp
WHERE SAL = (SELECT MAX (SAL) FROM emp);
ENAME
----------
KIRK
23. Show the average salary for all employees that are not managers.
SELECT AVG(SAL) FROM EMP WHERE JOB 'MANAGER';
AVG(SAL)
----------
2229.16667
24. What is the difference between the highest and lowest salary?
SELECT MAX(SAL) - MIN(SAL)
FROM EMP;
MAX(SAL)-MIN(SAL)
-----------------
5200
25. Select employee number, name for all employees with salaries above the average salary. Use a subquery.
SELECT EMPNO, ENAME FROM EMP WHERE SAL > (SELECT AVG (SAL) FROM EMP);
EMPNO ENAME
--------- ---------- 7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7902 FORD 9999 KIRK
Using the ZIPCODES table from the text schema, write a SQL query that displays the following:
26. Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table. (2 points)
SELECT STATE, COUNT(*)
FROM ZIPCODE
GROUP BY STATE
ORDER BY COUNT(*) DESC
ST COUNT(*)
-- ----------
NY 123
NJ 74
CT 19
MA 5
MI 1
GA 1
FL 1
PR 1
WV 1
OH 1
10 rows selected.
27. Which city has the most zip codes assigned to it? Use a TOP-N query. (2 points)
select * from (select city, count(*) from zipcode group by city order by count(*) desc) where rownum < 2
CITY COUNT(*)
------------------------- ----------
Brooklyn 36
28. Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all cities having less than 5 zip codes. (2 points)
select state, count(*) from zipcode group by state having count(*) > 4 order by count(*) desc ST COUNT(*)
-- ----------
NJ 74
MA 5
NY 123
CT 19
Using the text schema, write a SQL query that displays the following:
29. Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view. (2 points)
Select s.student_id, last_name, enroll_cnt.tot
From student s,
(select student_id, count(*) tot From enrollment Group by student_id ) enroll_cnt Where s.student_id = enroll_cnt.student_id and tot > 2
30. Show the number of students enrolled for each state and zip code for New York and where the city begins with ‘W’. Use a Scalar subquery. (2 points)
Select city, state, zip, (select count(*) NY_count From student s Where s.zip = z.zip) as student_count From zipcode z Where state = 'NY' and city like 'W%'
CITY ST ZIP STUDENT_COUNT
------------------------- -- ----- -------------
West Islip NY 11295 0
Whitestone NY 11357 2
Woodside NY 11377 4
West Hempstead NY 11552 1
Woodmere NY 11598 0
31. Display the course number and description of courses with no enrollment. Also include courses which have no section assigned. (2 points)
SELECT course_no, description FROM course c WHERE NOT EXISTS (SELECT 'X' FROM enrollment e, section s WHERE c.course_no = s.course_no and s.section_id = e.section_id); COURSE_NO DESCRIPTION
---------- ------------------------------------ 80 Structured Programming Techniques 144 Database Design 210 Oracle Tools 220 PL/SQL Programming 430 JDeveloper Techniques
