Using SQL developer to assist in questions.

This is to create the data

set pagesize 20;

DROP TABLE MISSION; DROP TABLE ASSIGNMENT; DROP TABLE TEAM; DROP TABLE AGENT;

CREATE TABLE AGENT ( AID CHAR(2) PRIMARY KEY, AName VARCHAR2(20), Gender CHAR(1), Salary NUMBER(9,2) );

CREATE TABLE TEAM ( TID NUMBER(2,0) PRIMARY KEY, TName VARCHAR2(24), LeaderID CHAR(2) REFERENCES AGENT(AID) );

CREATE TABLE ASSIGNMENT ( AgentID CHAR(2) REFERENCES AGENT(AID), TeamID NUMBER(2,0) REFERENCES TEAM(TID), Joined NUMBER(4) CHECK (Joined >= 1990), Hours NUMBER(3) CHECK (Hours > 0), CONSTRAINT PK_ASSIGNMENT PRIMARY KEY (AgentID,TeamID) );

CREATE TABLE MISSION ( MissionID CHAR(3) PRIMARY KEY, Location VARCHAR2(24), TeamID NUMBER(2,0) REFERENCES TEAM(TID) );

INSERT INTO AGENT VALUES ('01', 'Jim Phelps', 'M', 100000.00); INSERT INTO AGENT VALUES ('02', 'Ethan Hunt', 'M', 500000.00); INSERT INTO AGENT VALUES ('03', 'Claire Phelps', 'F', 750000.00); INSERT INTO AGENT VALUES ('04', 'Luther Stickell', 'M', 300000.00); INSERT INTO AGENT VALUES ('05', 'Benjie Dunn', 'M', 150000.00); INSERT INTO AGENT VALUES ('06', 'Jane Carter', 'F', 250000.00); INSERT INTO AGENT VALUES ('07', 'William Brandt', 'M', 125000.00); INSERT INTO AGENT VALUES ('08', 'August Walker', 'M', 175000.00); INSERT INTO AGENT VALUES ('09', 'Julia Meade', 'F', 200000.00); INSERT INTO AGENT VALUES ('10', 'Alan Hunley', 'M', 750000.00); INSERT INTO TEAM VALUES (0, 'Prequel', '01'); INSERT INTO TEAM VALUES (1, 'Original', '01'); INSERT INTO TEAM VALUES (4, 'Ghost', '02'); INSERT INTO TEAM VALUES (6, 'Fallout', '02'); INSERT INTO TEAM VALUES (8, 'Fake', '05');

INSERT INTO ASSIGNMENT VALUES ('01', 0, 1993, 10); INSERT INTO ASSIGNMENT VALUES ('03', 0, 1993, 120); INSERT INTO ASSIGNMENT VALUES ('01', 1, 1995, 10); INSERT INTO ASSIGNMENT VALUES ('02', 1, 1995, 100); INSERT INTO ASSIGNMENT VALUES ('03', 1, 1995, 120); INSERT INTO ASSIGNMENT VALUES ('04', 1, 1996, 40); INSERT INTO ASSIGNMENT VALUES ('02', 4, 2010, 90); INSERT INTO ASSIGNMENT VALUES ('05', 4, 2010, 80); INSERT INTO ASSIGNMENT VALUES ('06', 4, 2010, 80); INSERT INTO ASSIGNMENT VALUES ('07', 4, 2011, 50); INSERT INTO ASSIGNMENT VALUES ('02', 6, 2017, 150); INSERT INTO ASSIGNMENT VALUES ('04', 6, 2017, 90); INSERT INTO ASSIGNMENT VALUES ('07', 6, 2017, 100); INSERT INTO ASSIGNMENT VALUES ('08', 6, 2018, 40);

INSERT INTO MISSION VALUES ('C01', 'Chicago', 0); INSERT INTO MISSION VALUES ('L01', 'London', 1); INSERT INTO MISSION VALUES ('P01', 'Prague', 1); INSERT INTO MISSION VALUES ('B01', 'Berlin', 4); INSERT INTO MISSION VALUES ('D01', 'Dubai', 4); INSERT INTO MISSION VALUES ('M01', 'Moscow', 4); INSERT INTO MISSION VALUES ('M02', 'Mumbai', 4); INSERT INTO MISSION VALUES ('P02', 'Paris', 6); INSERT INTO MISSION VALUES ('L02', 'London', 6); INSERT INTO MISSION VALUES ('K02', 'Kashmir',6); INSERT INTO MISSION VALUES ('L03', 'London', 6);

SELECT * FROM AGENT; SELECT * FROM TEAM; SELECT * FROM ASSIGNMENT; SELECT * FROM MISSION;

1. Give the ID and name of all agents assigned to team 'Ghost', ordered by the agent ID. 2. Give an alphabetical list of the names of all teams led by Jim Phelps'. 3. For each agent, give the agent's ID and name, and how many teams they have been assigned to. (Do not include agents who have not been assigned to any teams.) Order the output from the agent who has been on the fewest teams to the one who has been on the most. 4. Give a list of all locations where the 'Fallout' team has performed at least one mission. 5. For each team, give the ID, name, and the total number of hours that agents have been assigned to that team. (Your output should include teams to that have no agents assigned to them - you may display either zero or NULL for their total hours.) Order the teams from the one with the most total hours to the one with the fewest. 6. For each mission, give the ID of the mission and the name of the leader of the team that performed the mission. 7. Give the name and salary of every agent who has never been the leader of a team. Order the output from the lowest salary to the highest. 8. Give the number of missions that were performed by a team that agent 'William Brandt' was assigned to. (Note: Even thought the answer will be a single number, you will need all four tables to solve this problem!)