(please see the data model under the table before you copy and paste the answer
ID: 3575758 • Letter: #
Question
(please see the data model under the table before you copy and paste the answer from the other time this question was asked)
Flight
Shuttle
Launch date
Objective
Crew size
Commander
97
Endeavour
02-11-2000
Shuttle Radar Topographic Mission
6
Kregel
98
Atlantis
05-19-2000
ISS-2A.2 SpaceHab Double Cargo Module
7
Halsell
99
Atlantis
09-08-2000
ISS-ISS-2A.2B Outfiting ISS
7
Wilcutt
100
Discovery
10-11-2000
ISS-3A Integrated Truss Structure Z1, PMA-3, CMG's
7
Duffy
101
Endeavour
11-30-2000
ISS-4A ITS P6, Photovoltaic Module, Radiators
5
Jett
(make up a shuttlebuilddate and cmdfname, which is the commanders first name and a cmddob, commanders date of birth)
6. Create the database designed in answer to question 5. Create the table in the database you use and copy and paste the table creation script here.
7. Populate the database created in answer to question 6. Populate the table in the database you use and copy and paste the table insertion script here. Your data should include that provided in the chart above as well as any other necessary data to populate your tables.
8. Write five SQL queries from the above data demonstrating the following skills. I just need to see the SQL scripts, not the results. In all queries, the query needs to include more than one table.
a.Use of the Group by Statement
b.Use of the between statement
c.Use of the having statement
d.Use of the an order by statement
e.Use of a subquery (IN or EXISTS)
Flight
Shuttle
Launch date
Objective
Crew size
Commander
97
Endeavour
02-11-2000
Shuttle Radar Topographic Mission
6
Kregel
98
Atlantis
05-19-2000
ISS-2A.2 SpaceHab Double Cargo Module
7
Halsell
99
Atlantis
09-08-2000
ISS-ISS-2A.2B Outfiting ISS
7
Wilcutt
100
Discovery
10-11-2000
ISS-3A Integrated Truss Structure Z1, PMA-3, CMG's
7
Duffy
101
Endeavour
11-30-2000
ISS-4A ITS P6, Photovoltaic Module, Radiators
5
Jett
SHUTTLE tshuttlenarme shuttlebuilddate FUGHT num date fttobjective fltcrewsize DER tcmdid cmdfname cmd name cmd dobExplanation / Answer
5. Create the database designed in answer to question :
sql_Query: CREATE DATABASE agency;
6. Create the table in the database you use and copy and paste the table creation script here.
sql_query:
---creation of shuttle table
CREATE TABLE SHUTTLE(
shuttleno int,
shuttlename varchar(100),
shuttlebuilddate Date
);
----creation of flight table
CREATE TABLE FLIGHT(
fitnum int,
fitdate Date,
fitobjective varchar(350),
fitcrewsize int
);
----creation of commander
CREATE TABLE COMMANDER(
cmdid int,
cmdfname varchar(60),
cmdlname varchar(60),
cmddob Date
);
--------insert query for shuttle table
INSERT INTO SHUTTLE VALUES(97,"Endeavour", to_date('196000211','YYYYMMDD'));
INSERT INTO SHUTTLE VALUES(99,"Atlantis", to_date('19451130','YYYYMMDD'));
INSERT INTO SHUTTLE VALUES(100,"Discovery", to_date('19700310','YYYYMMDD'));
--------insert query for flight table
INSERT INTO flight VALUES(97, to_date('20000211','YYYYMMDD'), "Shuttle Radar Topographic Mission",6);
INSERT INTO flight VALUES(98, to_date('20000519','YYYYMMDD'), "ISS-2A.2 SpaceHab Double Cargo Module",7);
INSERT INTO flight VALUES(99, to_date('20000908','YYYYMMDD'), "ISS-ISS-2A.2B Outfighting ISS",7);
INSERT INTO flight VALUES(100, to_date('20001011','YYYYMMDD'), "ISS-3A Integrated Truss Structure Z1, PMA-3, CMG's",7);
INSERT INTO flight VALUES(101, to_date('20001130','YYYYMMDD'), "ISS-4A ITS P6, Photovolatic Module, Radiators",5);
--------insert query for commander table
INSERT INTO Commander VALUES("Cm-1", "James", "kregel",to_date('19700121','YYYYMMDD'));
INSERT INTO commander VALUES("Cm-2", "Corner", "Halshell",to_date('19800807','YYYYMMDD'));
INSERT INTO commander VALUES("Cm-3", "Adam", "Wilcutt",to_date('19700529','YYYYMMDD'));
INSERT INTO commander VALUES("Cm-4", "Edward", "Duffy",to_date('19700423','YYYYMMDD'));
INSERT INTO commander VALUES("Cm-5", "Mark", "Jett",to_date('19700303','YYYYMMDD'));
7. Populate the database created in answer to question 6. Populate the table in the database you use and copy and paste the table insertion script here.
Your data should include that provided in the chart above as well as any other necessary data to populate your tables.
sql_query:
----- populate data for table shuttle
SELECT * FROM shuttle;
------ populate data for table flight
SELECT * FROM flight;
------ populate data for table commander
SELECT * FROM commander;
8. Write five SQL queries from the above data demonstrating the following skills. I just need to see the SQL scripts, not the results.
In all queries, the query needs to include more than one table.
a.Use of the Group by Statement
sql_Query:
SELECT shuttle.shuttlename, flight.fitnum
FROM shuttle,flight
WHERE shuttle.shuttleno=flight.fitnum
GROUP BY shuttle.shuttleno
b.Use of the between statement
sql_query:
SELECT shuttle.shuttlename, flight.fitdate
FROM shuttle,flight
WHERE (flight.fitdate between to_date('20000519','YYYYMMDD') AND to_date('20001130','YYYYMMDD'))
GROUP BY shuttle.shuttleno
c.Use of the having statement
sql_query:
select shuttle.shuttlename, flight.fitdate,flight.fitobjective,flight.fitcrewsize
from shuttle.shuttlename, flight.fitdate
WHERE shuttle.shuttleno=flight.fitnum
order by shuttle.shuttleno, flight.fitdate;
having shuttle.shuttlename = "Endeavour";
d.Use of the an order by statement
sql_query:
select shuttle.shuttlename, flight.fitdate,flight.fitobjective,flight.fitcrewsize
from shuttle.shuttlename, flight.fitdate
WHERE shuttle.shuttleno=flight.fitnum
order by shuttle.shuttleno, flight.fitdate;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.