Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
Produce a script file with 15 queries: At least two using join At least three using where clause At least three using order by At least two using single-row functions At least two using group by and group functions at least one nested query At least 4 queries from chapter 1 - 6 (exclude Create Table, Insert queries) ------- TABLE 1 create TABLE ProCoaches (pro_co_ID INTEGER PRIMARY KEY, pro_coachName Varchar(50) NOT NULL, pro_coachLocation Varchar(20)); INSERT INTO ProCoaches VALUES (1, 'John', 'Missoula'); INSERT INTO ProCoaches VALUES (2, 'Joe', 'Bozeman'); INSERT INTO ProCoaches VALUES (3, 'Tara','Butte'); select * FROM ProCoaches; select LENGTH('Missoula') AS LengthOfLocation; select pro_coachName FROM ProCoaches where pro_coachLocation='Missoula'; select COUNT(pro_co_ID), pro_coachLocation FROM ProCoaches GROUP BY pro_coachLocation; TABLE 2 create TABLE ProCyclist (pro_cy_ID VARCHAR(10) PRIMARY KEY, pro_cyclist_Name VARCHAR(50) NOT NULL, pro_cyclist_Gender Varchar(20), pro_cyclist_DOB Varchar(20), pro_cyclist_AGE INTEGER, pro_cyclist_WEIGHT Varchar(20)); INSERT INTO ProCyclist VALUES ('C-1', 'Peter Sagan', 'Male', '2001/03/11',1,'65Kg'); INSERT INTO ProCyclist VALUES ('C-2', 'Nairo Quintana', 'Male', '2005/05/22',9,'70Kg'); INSERT INTO ProCyclist VALUES ('C-3', 'Cris Frome','Male', '2012/12/11',5,'60Kg'); INSERT INTO ProCyclist VALUES ('C-4', 'Mavi Garcia', 'Female','2003/10/5',2,'55Kg'); INSERT INTO ProCyclist VALUES ('C-5', 'Leah Thomas','Female','1999/10/3',3,'69Kg'); SELECT * FROM ProCyclist; SELECT pro_cyclist_Name,pro_cyclist_Weight FROM proCyclist ORDER BY pro_cyclist_AGE; SELECT LOWER(pro_cyclist_Name) FROM ProCyclist; TABLE 3 create TABLE Tested (t_date Varchar(20), t_fee INTEGER, pro_co_ID INTEGER, pro_cy_ID Varchar(20), FOREIGN KEY (pro_co_ID) REFERENCES ProCoaches(pro_co_ID), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID)); INSERT into Tested values('24Apr2001',5500,2,'C-1'); INSERT into Tested values('4July2002',1600,1,'C-2'); INSERT into Tested values('14July2001',12000,2,'C-4'); INSERT into Tested values('24May2001',1300,2,'C-3'); INSERT into Tested values('12Apr2001',1500,2,'C-5'); SELECT * FROM Tested; TABLE 4 create TABLE ProTeamOwner (pto_name Varchar(20) NOT NULL, pto_id INTEGER PRIMARY KEY, pro_cy_ID Varchar(50), pto_location Varchar(20), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID)); INSERT into ProTeamOwner values('Sky',101,'C-1','Sagan'); INSERT into ProTeamOwner values('BMC',102,'C-2','Quintana'); INSERT into ProTeamOwner values('Canels',103,'C-3','Frome'); INSERT into ProTeamOwner values('Specialized',104,'C-4','Gracia'); INSERT into ProTeamOwner values('Trek',105,'C-5','Martinez'); SELECT * FROM ProTeamOwner; SELECT pto_name FROM ProTeamOwner ORDER BY pto_Id, pto_location; TABLE 5 create TABLE ProRaces (pro_race_ID Varchar(20) PRIMARY KEY, pro_race_Name Varchar(50) NOT NULL); INSERT INTO ProRaces VALUES ('B_11','Tour De France'); INSERT INTO ProRaces VALUES ('B_12','Giro Di Italia'); INSERT INTO ProRaces VALUES ('B_13','La Vuelta'); INSERT INTO ProRaces VALUES ('B_14','Tour of Mexico'); INSERT INTO ProRaces VALUES ('B_15','Tour de Flanders'); INSERT INTO ProRaces VALUES ('B_16','Paris Robiax'); INSERT INTO ProRaces VALUES ('B_17','Strade Bianche'); SELECT * From ProRaces; TABLE 6 create TABLE ProRacesWon( pro_cy_ID Varchar(10), pro_race_ID Varchar(10), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID), FOREIGN KEY (pro_race_ID) REFERENCES ProRaces(pro_race_ID)); INSERT INTO ProRacesWon VALUES ('C-1','B_11'); INSERT INTO ProRacesWon VALUES ('C-2','B_12'); INSERT INTO ProRacesWon VALUES ('C-3','B_13'); INSERT INTO ProRacesWon VALUES ('C-4','B_14'); INSERT INTO ProRacesWon VALUES ('C-5','B_15'); SELECT * FROM ProRacesWon; Select pro_race_name from ProRaces; update ProRaces SET pro_race_Name='Niro Quintana' WHERE pro_race_ID='B_14'; SELECT * FROM ProRaces; SELECT ProTeamOwner.pto_Name,ProCyclist.Pro_cyclist_name FROM ProCyclist LEFT JOIN ProTeamOwner ON ProCyclist.pro_cy_Id = ProTeamOwner.pro_cy_Id; SELECT ProCoaches.pro_coachName, COUNT(Tested.pro_co_ID) AS NoOfTesters FROM tested LEFT JOIN ProCoaches ON Tested.pro_co_Id=ProCoaches.pro_co_ID;

Produce a script file with 15 queries: At least two using join At least three using where clause At least three using order by At least two using single-row functions At least two using group by and group functions at least one nested query At least 4 queries from chapter 1 - 6 (exclude Create Table, Insert queries) ------- TABLE 1 create TABLE ProCoaches (pro_co_ID INTEGER PRIMARY KEY, pro_coachName Varchar(50) NOT NULL, pro_coachLocation Varchar(20)); INSERT INTO ProCoaches VALUES (1, 'John', 'Missoula'); INSERT INTO ProCoaches VALUES (2, 'Joe', 'Bozeman'); INSERT INTO ProCoaches VALUES (3, 'Tara','Butte'); select * FROM ProCoaches; select LENGTH('Missoula') AS LengthOfLocation; select pro_coachName FROM ProCoaches where pro_coachLocation='Missoula'; select COUNT(pro_co_ID), pro_coachLocation FROM ProCoaches GROUP BY pro_coachLocation; TABLE 2 create TABLE ProCyclist (pro_cy_ID VARCHAR(10) PRIMARY KEY, pro_cyclist_Name VARCHAR(50) NOT NULL, pro_cyclist_Gender Varchar(20), pro_cyclist_DOB Varchar(20), pro_cyclist_AGE INTEGER, pro_cyclist_WEIGHT Varchar(20)); INSERT INTO ProCyclist VALUES ('C-1', 'Peter Sagan', 'Male', '2001/03/11',1,'65Kg'); INSERT INTO ProCyclist VALUES ('C-2', 'Nairo Quintana', 'Male', '2005/05/22',9,'70Kg'); INSERT INTO ProCyclist VALUES ('C-3', 'Cris Frome','Male', '2012/12/11',5,'60Kg'); INSERT INTO ProCyclist VALUES ('C-4', 'Mavi Garcia', 'Female','2003/10/5',2,'55Kg'); INSERT INTO ProCyclist VALUES ('C-5', 'Leah Thomas','Female','1999/10/3',3,'69Kg'); SELECT * FROM ProCyclist; SELECT pro_cyclist_Name,pro_cyclist_Weight FROM proCyclist ORDER BY pro_cyclist_AGE; SELECT LOWER(pro_cyclist_Name) FROM ProCyclist; TABLE 3 create TABLE Tested (t_date Varchar(20), t_fee INTEGER, pro_co_ID INTEGER, pro_cy_ID Varchar(20), FOREIGN KEY (pro_co_ID) REFERENCES ProCoaches(pro_co_ID), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID)); INSERT into Tested values('24Apr2001',5500,2,'C-1'); INSERT into Tested values('4July2002',1600,1,'C-2'); INSERT into Tested values('14July2001',12000,2,'C-4'); INSERT into Tested values('24May2001',1300,2,'C-3'); INSERT into Tested values('12Apr2001',1500,2,'C-5'); SELECT * FROM Tested; TABLE 4 create TABLE ProTeamOwner (pto_name Varchar(20) NOT NULL, pto_id INTEGER PRIMARY KEY, pro_cy_ID Varchar(50), pto_location Varchar(20), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID)); INSERT into ProTeamOwner values('Sky',101,'C-1','Sagan'); INSERT into ProTeamOwner values('BMC',102,'C-2','Quintana'); INSERT into ProTeamOwner values('Canels',103,'C-3','Frome'); INSERT into ProTeamOwner values('Specialized',104,'C-4','Gracia'); INSERT into ProTeamOwner values('Trek',105,'C-5','Martinez'); SELECT * FROM ProTeamOwner; SELECT pto_name FROM ProTeamOwner ORDER BY pto_Id, pto_location; TABLE 5 create TABLE ProRaces (pro_race_ID Varchar(20) PRIMARY KEY, pro_race_Name Varchar(50) NOT NULL); INSERT INTO ProRaces VALUES ('B_11','Tour De France'); INSERT INTO ProRaces VALUES ('B_12','Giro Di Italia'); INSERT INTO ProRaces VALUES ('B_13','La Vuelta'); INSERT INTO ProRaces VALUES ('B_14','Tour of Mexico'); INSERT INTO ProRaces VALUES ('B_15','Tour de Flanders'); INSERT INTO ProRaces VALUES ('B_16','Paris Robiax'); INSERT INTO ProRaces VALUES ('B_17','Strade Bianche'); SELECT * From ProRaces; TABLE 6 create TABLE ProRacesWon( pro_cy_ID Varchar(10), pro_race_ID Varchar(10), FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID), FOREIGN KEY (pro_race_ID) REFERENCES ProRaces(pro_race_ID)); INSERT INTO ProRacesWon VALUES ('C-1','B_11'); INSERT INTO ProRacesWon VALUES ('C-2','B_12'); INSERT INTO ProRacesWon VALUES ('C-3','B_13'); INSERT INTO ProRacesWon VALUES ('C-4','B_14'); INSERT INTO ProRacesWon VALUES ('C-5','B_15'); SELECT * FROM ProRacesWon; Select pro_race_name from ProRaces; update ProRaces SET pro_race_Name='Niro Quintana' WHERE pro_race_ID='B_14'; SELECT * FROM ProRaces; SELECT ProTeamOwner.pto_Name,ProCyclist.Pro_cyclist_name FROM ProCyclist LEFT JOIN ProTeamOwner ON ProCyclist.pro_cy_Id = ProTeamOwner.pro_cy_Id; SELECT ProCoaches.pro_coachName, COUNT(Tested.pro_co_ID) AS NoOfTesters FROM tested LEFT JOIN ProCoaches ON Tested.pro_co_Id=ProCoaches.pro_co_ID;

Computer Networking: A Top-Down Approach (7th Edition)
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
See similar textbooks
icon
Related questions
Question
100%

Produce a script file with 15 queries:

  • At least two using join
  • At least three using where clause
  • At least three using order by
  • At least two using single-row functions
  • At least two using group by and group functions
  • at least one nested query
  • At least 4 queries from chapter 1 - 6 (exclude Create Table, Insert queries)

-------

TABLE 1

create TABLE ProCoaches
(pro_co_ID INTEGER PRIMARY KEY,
pro_coachName Varchar(50) NOT NULL,
pro_coachLocation Varchar(20));

INSERT INTO ProCoaches VALUES (1, 'John', 'Missoula');
INSERT INTO ProCoaches VALUES (2, 'Joe', 'Bozeman');
INSERT INTO ProCoaches VALUES (3, 'Tara','Butte');

select * FROM ProCoaches;
select LENGTH('Missoula') AS LengthOfLocation;
select pro_coachName FROM ProCoaches where pro_coachLocation='Missoula';
select COUNT(pro_co_ID), pro_coachLocation FROM ProCoaches GROUP BY pro_coachLocation;

TABLE 2

create TABLE ProCyclist
(pro_cy_ID VARCHAR(10) PRIMARY KEY,
pro_cyclist_Name VARCHAR(50) NOT NULL,
pro_cyclist_Gender Varchar(20),
pro_cyclist_DOB Varchar(20),
pro_cyclist_AGE INTEGER,
pro_cyclist_WEIGHT Varchar(20));

INSERT INTO ProCyclist VALUES ('C-1', 'Peter Sagan', 'Male', '2001/03/11',1,'65Kg');
INSERT INTO ProCyclist VALUES ('C-2', 'Nairo Quintana', 'Male', '2005/05/22',9,'70Kg');
INSERT INTO ProCyclist VALUES ('C-3', 'Cris Frome','Male', '2012/12/11',5,'60Kg');
INSERT INTO ProCyclist VALUES ('C-4', 'Mavi Garcia', 'Female','2003/10/5',2,'55Kg');
INSERT INTO ProCyclist VALUES ('C-5', 'Leah Thomas','Female','1999/10/3',3,'69Kg');

SELECT * FROM ProCyclist;
SELECT pro_cyclist_Name,pro_cyclist_Weight
FROM proCyclist ORDER BY pro_cyclist_AGE;
SELECT LOWER(pro_cyclist_Name)
FROM ProCyclist;


TABLE 3

create TABLE Tested
(t_date Varchar(20),
t_fee INTEGER,
pro_co_ID INTEGER,
pro_cy_ID Varchar(20),
FOREIGN KEY (pro_co_ID) REFERENCES ProCoaches(pro_co_ID),
FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID));

INSERT into Tested values('24Apr2001',5500,2,'C-1');
INSERT into Tested values('4July2002',1600,1,'C-2');
INSERT into Tested values('14July2001',12000,2,'C-4');
INSERT into Tested values('24May2001',1300,2,'C-3');
INSERT into Tested values('12Apr2001',1500,2,'C-5');

SELECT * FROM Tested;

TABLE 4

create TABLE ProTeamOwner
(pto_name Varchar(20) NOT NULL,
pto_id INTEGER PRIMARY KEY,
pro_cy_ID Varchar(50),
pto_location Varchar(20),
FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID));

INSERT into ProTeamOwner values('Sky',101,'C-1','Sagan');
INSERT into ProTeamOwner values('BMC',102,'C-2','Quintana');
INSERT into ProTeamOwner values('Canels',103,'C-3','Frome');
INSERT into ProTeamOwner values('Specialized',104,'C-4','Gracia');
INSERT into ProTeamOwner values('Trek',105,'C-5','Martinez');

SELECT * FROM ProTeamOwner;
SELECT pto_name
FROM ProTeamOwner
ORDER BY pto_Id, pto_location;

TABLE 5

create TABLE ProRaces
(pro_race_ID Varchar(20) PRIMARY KEY,
pro_race_Name Varchar(50) NOT NULL);

INSERT INTO ProRaces VALUES ('B_11','Tour De France');
INSERT INTO ProRaces VALUES ('B_12','Giro Di Italia');
INSERT INTO ProRaces VALUES ('B_13','La Vuelta');
INSERT INTO ProRaces VALUES ('B_14','Tour of Mexico');
INSERT INTO ProRaces VALUES ('B_15','Tour de Flanders');
INSERT INTO ProRaces VALUES ('B_16','Paris Robiax');
INSERT INTO ProRaces VALUES ('B_17','Strade Bianche');

SELECT * From ProRaces;


TABLE 6

create TABLE ProRacesWon(
pro_cy_ID Varchar(10),
pro_race_ID Varchar(10),
FOREIGN KEY (pro_cy_ID) REFERENCES ProCyclist(pro_cy_ID),
FOREIGN KEY (pro_race_ID) REFERENCES ProRaces(pro_race_ID));

INSERT INTO ProRacesWon VALUES ('C-1','B_11');
INSERT INTO ProRacesWon VALUES ('C-2','B_12');
INSERT INTO ProRacesWon VALUES ('C-3','B_13');
INSERT INTO ProRacesWon VALUES ('C-4','B_14');
INSERT INTO ProRacesWon VALUES ('C-5','B_15');

SELECT * FROM ProRacesWon;

Select pro_race_name from ProRaces;
update ProRaces SET pro_race_Name='Niro Quintana' WHERE pro_race_ID='B_14';

SELECT * FROM ProRaces;
SELECT ProTeamOwner.pto_Name,ProCyclist.Pro_cyclist_name
FROM ProCyclist LEFT JOIN ProTeamOwner ON ProCyclist.pro_cy_Id = ProTeamOwner.pro_cy_Id;
SELECT ProCoaches.pro_coachName, COUNT(Tested.pro_co_ID) AS NoOfTesters
FROM tested LEFT JOIN ProCoaches ON Tested.pro_co_Id=ProCoaches.pro_co_ID;

 

Expert Solution
steps

Step by step

Solved in 9 steps with 17 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY
SEE MORE TEXTBOOKS