Question

CIS 411w spring 2017 Problem Set 11   

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. We’re not joining any tables here, just looking for the existence of personal (‘PER’) email addresses.

Copy and paste your SQL code into this assignment.

Answer 1

3.

Select emp.empno, ename, job, sal, loc and email from emp inner join dept using(deptno) inner join email using(empno);

4.

Alter email add email_type varchar2(4);

5.

Update email set email_type = 'BUS';

7.

Select empno, ename, job, sal, mgr from emp e where EXISTS ( Select empno from email em where e.empno = em.empno and em.email_type = 'PER');