Getting your output to match the sample output does not guarantee you did the pr

Question

Getting your output to match the sample output does not guarantee you did the problem correctly. Below are some things you cannot do in this assignment. These may allow you to get matching output but that wouldn't be correct if you violated one of the items below.

Do not change the data in the Access database. Do not change the structure of the Access database.

Do not use the alternative JOIN ON syntax for any query. This is described on pages 168-176. Use the WHERE clause method described starting on page 163.

Do not write your SQL statements based on the data in the tables. For example, if the problem requires you find the oldest date don't look at the data in the table, find the oldest date and then write the SQL statement to select the row where date is equal to that date. Assume the data will be constantly changing and you must write the SQL statement that will ALWAYS find the oldest date.

The queries to complete are listed below.

1. Create a query that lists the rider's first name, the rider's last name, the rider's nationality, the rider's team name, the team 's registered nation and the number of pro wins for the rider. List only riders who have more than 50 pro wins. Display the output in order by the number of pro wins with the lowest number of wins listed first. Match the output in the sample results exactly.

2.Display the race name, the last name of the winner, the year, and the number of pro wins for the winner of each race. List only races in or after the year 2000. List the output with the oldest races list first. Match the output in the sample results exactly.

3. Display the bike name, bike cost, bike country of origin, team name, and team registered nation for each team that uses a bike made in Italy, Canada, or the USA. List the output by country of origin (Z to A). Match the output in the sample results exactly.

ANSWERS Results for Query 13

Results for Query 13

Results for Query 14

bike bike_number bike_name country_of_origin cost 10 Eddy Merckx Belgium 9000 20 Trek USA 7500 30 Cervelo Canada 8000 40 Colnago Italy 10500 50 Willier Italy 8500 60 Look France 9000 70 Gios Italy 9300 80 BMC Switzerland 10000 90 Scott Germany 8800 100 GT Canada 8900 110 Chumba USA 8800 120 Pinarello Mecurio So Italy 1092 130 Bianchi Specialissim Italy 5849.99 140 Pinarello Dogma Italy 9250

Explanation / Answer

1.

Select first_name,last_name,nationality,rider.team_name,registered_nation,num_pro_wins from rider inner join team on rider.team_name = team.team_name where num_pro_wins > 50 order by num_pro_wins;

inner join is used here to join the two tables on coomon column ie team_name. So to avoid ambiguity, rider.team_name is used

2.

Select race_name,last_name,race_year,num_pro_wins from winner inner join rider on winner.rider_number = rider.rider_number where race_year > 2000 order by race_year;

oldest year is year with less value so ascending order is used.

3.

Select bike_name,cost,country_of_origin,team_name,registered_nation from bike inner join team on bike.bike_number = team.bike_number where country_of_origin IN('Italy','Canada','USA') order by country_of_origin desc;

IN is used to select rows which match any of the values of country_of_origin in the list

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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