Problem Set 10

1. Write a SQL query that returns the employee number, employee name, job, department number and location from the emp and dept tables.

Copy and paste your SQL code into this assignment.

2. Copy and modify the previous SQL query to use a right join to join the emp table to the dept table. What values appear in the result set now?

Copy and paste your SQL code along with your answer to the above question into this assignment.

3. Copy and paste the following SQL code into you ApEx SQL Command window:

insert into emp values(7935, 'EVE', 'MANAGER', 7839, SYSDATE, 2850, NULL, 40);

and click à Run

Re-run the SQL query created in part number 2 without making any changes to the query. How does the additional data in the table affect the result set of the query now?

Write your answer to the above question into this assignment.

4. Go to the SQL Scripts window in ApEx, which is under the SQL Workshop menu, and create a SQL file called "ps8a." Paste this SQL code into the code window and run it:

CREATE TABLE phone AS(select empno, deptno from emp);

ALTER table phone ADD(phoneNum NUMBER);

UPDATE phone set phoneNum = '8784444' where deptno = 10;

UPDATE phone set phoneNum = '8784445' where deptno = 20;

UPDATE phone set phoneNum = '8784446' where deptno = 30;

UPDATE phone set phoneNum = '8784447' where deptno = 40;

5. Create a SQL query in the SQL command window that will return the empno, enam, deptno and phone number from the emp and phone tables. You will have to join the two tables by first comparing them and decide which columns are in both tables. The phone table was created and populated with the commands above.

Copy and paste your SQL code along with your answer to the above question into this assignment.