SQL plus chapter 7 Create a table called Secretary with the attributes “dcode CH

Question

SQL plus chapter 7

Create a table called Secretary with the attributes “dcode CHAR(4)” (for department code) and “name VARCHAR2(20)” (for the secretary name). Populate the table as follows:

Secretary dCode   name

ACCT Sally

COSC Chris

ENGL    Maria

a.) Create a query that lists the names of departments that have secretaries (use IN and the Secretary table in a subquery with the Department_to_major table in the outer query). Save this query as q77a.

b.)Create a query that lists the names of departments that do not have secretaries (use NOT.. IN). Save this query as q77b.

c.) Add one more row to the Secretary table that contains (This could be a situation in which we have hired Brenda but have not yet assigned her to a department.)

d.) Recall q77a and re-run it.

e.) Recall q77b and re-run it.

f.) Add the phrase “WHERE dcode IS NOT NULL” to the subquery in the IN and NOT..IN cases and run them again

Explanation / Answer

create table secretary
(dcode char(4),
name varchar2(20));

insert into secretary (dcode,name) values ("ACCT","Sally");
insert into secretary (dcode,name) values ("COSC","Chris");
insert into secretary (dcode,name) values ("ENGL","Maria");
Commit;

a)  select dept_name from Department_to_major
where dcode in (
select * from
from secretary where name is not null);

Note: Save it as q77a.sql through file and then save.

b) select dcode from secretary where dcode not in (
select dcode from secretary where name is not null);

Note: Save it as q77b.sql through file and then save.

c) insert into secretary (dcode,name) values (NULL,"Brenda");
Commit;

d) if you are using Toad, you can go to view and do a sql recall and then open the requires query and run it.

e) d. if you are using Toad, you can go to view and do a sql recall and then open the requires query and run it.

f) select dept_name from Department_to_major
where dcode in (
select * from
from secretary where dcode is not null and
name is not null);

/

select dcode from secretary where dcode not in (
select dcode from secretary where dcode is not null and
name is not null);

/

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.