Free Essay

Is562 – Database Applications and Programming

In: Computers and Technology

Submitted By bustamanteax
Words 1973
Pages 8
IS562 – Database Applications and Programming
4 points for the first 19 - 2 points for the questions 20 through 31
Chapter 1

1. List all employee information in department 30.

select * from emp where deptno = 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 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.

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;

COURSE_NO DESCRIPTION COST PREREQUISITE CREATED_BY ---------- -------------------------------------------------- ---------- ------------ ------------ 10 DP Overview 1195 DSCHERER 310 Operating Systems 1195 DSCHERER 146 Java for C/C++ Programmers 1195 DSCHERER 144 Database Design 1195 420 DSCHERER 430 JDeveloper Techniques 1195 350 DSCHERER 145 Internet Protocols 1195 310 DSCHERER 210 Oracle Tools 1195 220 DSCHERER 134 Advanced Unix Admin 1195 132 DSCHERER 330 Network Administration 1195 130 DSCHERER 132 Basics of Unix Admin 1195 130 DSCHERER 350 JDeveloper Lab 1195 125 DSCHERER 125 JDeveloper 1195 122 DSCHERER 124 Advanced Java Programming 1195 122 DSCHERER 122 Intermediate Java Programming 1195 120 DSCHERER 220 PL/SQL Programming 1195 80 DSCHERER 420 Database System Principles 1195 25 DSCHERER 142 Project Management 1195 20 DSCHERER 140 Structured Analysis 1195 20 DSCHERER 100 Hands-On Windows 1195 20 DSCHERER 147 GUI Programming 1195 20 DSCHERER

20 rows selected.

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' ;

STUDENT_ID LAST_NAME
---------- ------------------------- 206 annunziato

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', '#########') '(###)###-####'

LAST_NAME PHONE
------------------------- ---------------
Hanks 2125551212
Wojick 2125551212
Schorin 2125551212
Pertez 2125551212
Morris 2125551212
Smythe 2125551212
Frantzen 2125551212
Lowry 2125551212
Chow 2125551212
Willig 2125551212

10 rows selected.

Chapter 4
17. Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.

SELECT course_no, description, cost FROM course WHERE cost = CAST(cost AS VARCHAR(6));

COURSE_NO DESCRIPTION COST
---------- -------------------------------------------------- ---------- 10 DP Overview 1195 20 Intro to Computers 1195 25 Intro to Programming 1195 80 Structured Programming Techniques 1595 100 Hands-On Windows 1195 120 Intro to Java Programming 1195 122 Intermediate Java Programming 1195 124 Advanced Java Programming 1195 125 JDeveloper 1195 130 Intro to Unix 1195 132 Basics of Unix Admin 1195

18. Write a SQL statement that converts a date datatype to a char datatype.

SELECT TO_DATE('January 15, 2002, 11:00 A.M.', - 'Month dd, YYYY, HH:MI A.M.') AS New_Date FROM dual;

NEW_DATE
---------
15-JAN-02

19. Write a SQL statement that convert a number value to a character.

SQL> SELECT TO_CHAR(1013.50, '$999,999.99') AS New_Value 2 FROM dual;

NEW_VALUE
------------
$1,013.50

Part 2

20. Show a list of different jobs. Eliminate repeating values.

SELECT DISTINCT JOB
FROM EMP;

JOB
---------
ANALYST
CAPTAIN
CLERK
MANAGER
PRESIDENT
SALESMAN

6 rows selected.

21. How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?

SELECT COUNT (*), AVG(SAL), SUM(SAL), JOB, DEPTNO
FROM EMP
GROUP BY JOB, DEPTNO;

COUNT(*) AVG(SAL) SUM(SAL) JOB DEPTNO
---------- ---------- ---------- --------- ---------- 1 1300 1300 CLERK 10 2 950 1900 CLERK 20 1 950 950 CLERK 30 2 3000 6000 ANALYST 20 1 6000 6000 CAPTAIN 50 1 2450 2450 MANAGER 10 1 2975 2975 MANAGER 20 1 2850 2850 MANAGER 30 4 1400 5600 SALESMAN 30 1 5000 5000 PRESIDENT 10

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

STUDENT_ID LAST_NAME TOT
---------- ------------------------- ---------- 124 Wicelinski 4 184 Zuckerberg 3 214 Williams 4 215 Chatman 3 232 Jung 3 238 Snow 3 250 Fielding 3

7 rows selected.

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

Similar Documents