$1.$Using the MINUS condition, write a query that retrieves the name of the department, the name of the project, and the location of projects that Franklin does not work in$.$

$2.$Using the INTERSECT condition, write a query that retrieves the name of the department, the name of the project, and the location of the projects that Franklin works in$.($Hint: Query #$1$ and Query #$2$ differ by the keyword$)$

$3.$Write a query that retrieves the number of projects each employee works on$.$ Show each employee's first and last names alongside the number of projects each employee works on$.$ Present the list ordered alphabetically by the first name.

CREATE$$TABLE$$SHOW$($

$$SHOW$\_$ID$$INT$$PRIMARY$$KEY$,$

$$SHOW$\_$NAME$$VARCHAR$(20)$NOT$$NULL$,$

$$SHOW$\_$GENRE$$VARCHAR$(20)$

$)$;

CREATE$$TABLE$$ANCHOR$($

$$ANCHOR$\_$ID$$INT$$PRIMARY$$KEY$,$

$$FIRST$\_$NAME$$VARCHAR$(20)$NOT$$NULL$,$

$$LAST$\_$NAME$$VARCHAR$(20)$NOT$$NULL$,$

$$EMAIL$\_$ADDRESS$$VARCHAR$(100),$

$$PHONE$\_$NUMBER$$VARCHAR$(12)$

$)$;

CREATE$$TABLE$$STUDIO$($

$$STUDIO$\_$ID$$INT$$PRIMARY$$KEY$,$

$$STUDIO$\_$CITY$$VARCHAR$(20),$

$$STUDIO$\_$NAME$$VARCHAR$(20)$

$)$;

CREATE$$TABLE$$BROADCAST$($

$$BROADCAST$\_$ID$$INT$$PRIMARY$$KEY$,$

$$BROADCAST$\_$TITLE$$VARCHAR$(255),$

$$BROADCAST$\_$DATE$$DATE$$NOT$$NULL$,$

$$START$\_$TIME$$TIMESTAMP$$NOT$$NULL$,$

$$END$\_$TIME$$TIMESTAMP$$NOT$$NULL$,$

$$STUDIO$\_$ID$$INT$$NOT$$NULL$,$

$$SHOW$\_$ID$$INT$$NOT$$NULL$,$

$$CONSTRAINT$$BR$\_$SH$\_$FK$$FOREIGN$$KEY$($SHOW$\_$ID$)$REFERENCES$$SHOW$($SHOW$\_$ID$),$

$$CONSTRAINT$$BR$\_$ST$\_$FK$$FOREIGN$$KEY$($STUDIO$\_$ID$)$REFERENCES$$STUDIO$($STUDIO$\_$ID$)$

$)$;

CREATE$$TABLE$$ANCHOR$\_$ASSIGNMENTS$($

$$ANCHOR$\_$ID$$INT$$NOT$$NULL$,$

$$BROADCAST$\_$ID$$INT$$NOT$$NULL$,$

$$CONSTRAINT$$AA$\_$PK$$PRIMARY$$KEY$($ANCHOR$\_$ID$,$BROADCAST$\_$ID$),$

$$CONSTRAINT$$AA$\_$A$\_$FK$$FOREIGN$$KEY$($ANCHOR$\_$ID$)$REFERENCES$$ANCHOR$($ANCHOR$\_$ID$),$

$$CONSTRAINT$$AA$\_$BC$\_$FK$$FOREIGN$$KEY$($BROADCAST$\_$ID$)$REFERENCES$$BROADCAST$($BROADCAST$\_$ID$)$

$)$;

$--$Populate$$ANCHOR$$table

INSERT$$INTO$$ANCHOR$($ANCHOR$\_$ID$,$FIRST$\_$NAME,$$LAST$\_$NAME,$$EMAIL$\_$ADDRESS,$$PHONE$\_$NUMBER$)$

VALUES$$

$(13,\text{'}$KURT$\text{'},\text{'}$WARNER$\text{'},\text{'}$kurt$.$warner@nfl$.$com',$\text{'}890-013-7890\text{'}),$

$(112,\text{'}$MARSHAWN$\text{'},\text{'}$LYNCH$\text{'},\text{'}$marshawn$.$lynch@nfl$.$com',$\text{'}890-456-7890\text{'})$;

$--$Populate$$SHOW$$table

INSERT$$INTO$$SHOW$($SHOW$\_$ID$,$SHOW$\_$NAME,$$SHOW$\_$GENRE$)$

VALUES$(10,\text{'}$NFL$$NETWORK$\text{'},\text{'}$SPORTS$\text{'})$;

$--$Populate$$STUDIO$$table

INSERT$$INTO$$STUDIO<

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!

Q:

APPLICATION PROBLEM SET 3 Problem 3.1 Creating an Access Database Using an Excel File Each worksheet in the Company.xls file represents a different table in a simple company database. You are to use...

Q:

APPLICATION PROBLEM SET 3 Problem 3.1 Creating an Access Database Using an Excel File Each worksheet in the Company.xls file represents a different table in a simple company database. You are to use...

Q:

This task has five parts. IMPORTANT: If you do not complete this step, your work will not be graded at all. You will get a zero for the project. Create a company database named as COMP _ your first...

Q:

This is the picture of the information to start the assignment. Need a lot of help please and need the workbook for excel once done. This is the picture of the information to start the assignment....

Q:

This is the picture of the information to start the assignment. Need a lot of help please and need the excel worksheet when done. Please need lots of help. This problem is out of the book Succeeding...

Q:

Please help me with this excel problem! Level 2 Calculating Travel Costs at America Travels As a new sales associate at America Travels travel agency, you assist travel agents in finding the best...

Q:

Hello need some assistance with the following Oracle SQL statements 7. Create a query that uses a natural join to join the departments table and the employees table by the department_number column....

Q:

Using the database(SQL), write the queries for all the following questions. More information is provided at the end EMPNO ENAME JOB MGR HIREDATE SALCOMM DEPTNO 1 7369 SMITH CLERK 7902 17/12/80 800...

Q:

a +a2aaab Amadeus Corporation is considering the issue of a new product to be added to its product mix. They hired you, a recent business graduate from MacEwan, for conducting the analysis. The...

Q:

Using MySQL and the Company schema. 1. Using the Company schema, provide a query that lists all employees along with any dependents. Include employees without dependents in the result set. List the...

Q:

Value a risky corporate bond, assuming that the risk-free interest rate is 4 percent per period, where a period is defined as six months. Does the corporate bond have a face value of $100 pay?

Q:

Prove that the Z ratio given in Equation 9.2.1 has a standard normal distribution.

Q:

uestion 2 1 of 7 5 . thich taxpayer owns a capital asset? Aidan. He has a property that he rents to others. Brayden. He purchased jewelry for himself to wear. Madison. She keeps a supply of cosmetics...

Q:

In addition to restructuring Siemens into three sectors and 17 regional clusters, Siemens replaced about 50 percent of the companys top managers in an effort to increase accountability and manageri

Previous Question Next Question