PS11

1 Log in to your Oracle ApEx account.

2. Create a new table called email with this command:

CREATE table email as (SELECT empno, ename||'.'||SUBSTR(JOB,1,2)||'@apex.com' as "EMAIL" from emp);

Click Run to create this table

3. Write a SQL query that JOINS the three tables emp,dept and email. This SQL query will return the empno, ename, job, sal, loc and email for each employee. Use the newer ANSI JOIN syntax rather than the dot notation to join the tables.

Copy and paste your SQL code for this query into this assignment.

Right now the emp and email tables have one-to-one relationship. There is exactly one email record for each employee.

4. Use the SQL ALTER command to add a column to the email table. The new column will be called email_type. Make it the varchar2 data type with a length of 4 characters.

Copy and paste your SQL code into this assignment.

Here is a good online reference for using the ALTER command:

https://www.techonthenet.com/oracle/tables/alter_table.php

5. Use the UPDATE command to set the value for the new email_type column to BUS for everyone since the only rows in the table are business email right now.

https://www.techonthenet.com/sql/update.php

6. Run the following script to insert new rows into the email table:

insert into email (SELECT empno, ename||'.'||SUBSTR(JOB,1,2)||,'PER' from emp where mgr = 7698)

Click Run to insert these rows.

Now there is a one-to-many relationship between the emp and email table, but only employees who have Blake as their manager have both business and personal email addresses.

7. Create a new SQL query that shows the empno, ename, job, sal, mgr from the emp table. Include a correlated subquery that uses the EXISTS keyword to search for the existence of employees with email addresses that have the PER email_type. The correlated sub-query will have correlate to the emp.empno. Were not joining any tables here, just looking for the existence of personal (PER) email addresses.

Copy and paste your SQL code into this assignment.