Familiarize with the ER diagram : UCDAVIS

 

Module 2 Coding Assignment

Latest Submission Grade 88.88%

1.

Question 1

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks that have a length of 5,000,000 milliseconds or more.

1

2

3

4

SELECT *

FROM Tracks

WHERE Milliseconds >= 5000000

 

+---------+-------------------------+---------+-------------+---------+----------+--------------+------------+-----------+
| TrackId | Name                    | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds |      Bytes | UnitPrice |
+---------+-------------------------+---------+-------------+---------+----------+--------------+------------+-----------+
|    2820 | Occupation / Precipice  |     227 |           3 |      19 |     None |      5286953 | 1054423946 |      1.99 |
|    3224 | Through a Looking Glass |     229 |           3 |      21 |     None |      5088838 | 1059546140 |      1.99 |
+---------+-------------------------+---------+-------------+---------+----------+--------------+------------+-----------+

How many tracks are returned?

1 / 1 point

2

Correct

2.

Question 2

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices whose total is between $5 and $15 dollars.

1

2

3

SELECT *

FROM Invoices

WHERE Total BETWEEN 5 AND 15

+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+
| InvoiceId | CustomerId | InvoiceDate         | BillingAddress          | BillingCity | BillingState | BillingCountry | BillingPostalCode | Total |
+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+
|         3 |          8 | 2009-01-03 00:00:00 | Grétrystraat 63         | Brussels    |         None | Belgium        | 1000              |  5.94 |
|         4 |         14 | 2009-01-06 00:00:00 | 8210 111 ST NW          | Edmonton    |           AB | Canada         | T6G 2C7           |  8.91 |
|         5 |         23 | 2009-01-11 00:00:00 | 69 Salem Street         | Boston      |           MA | USA            | 2113              | 13.86 |
|        10 |         46 | 2009-02-03 00:00:00 | 3 Chatham Street        | Dublin      |       Dublin | Ireland        | None              |  5.94 |
|        11 |         52 | 2009-02-06 00:00:00 | 202 Hoxton Street       | London      |         None | United Kingdom | N1 5LH            |  8.91 |
|        12 |          2 | 2009-02-11 00:00:00 | Theodor-Heuss-Straße 34 | Stuttgart   |         None | Germany        | 70174             | 13.86 |
|        17 |         25 | 2009-03-06 00:00:00 | 319 N. Frances Street   | Madison     |           WI | USA            | 53703             |  5.94 |
|        18 |         31 | 2009-03-09 00:00:00 | 194A Chain Lake Drive   | Halifax     |           NS | Canada         | B3S 1C5           |  8.91 |
|        19 |         40 | 2009-03-14 00:00:00 | 8, Rue Hanovre          | Paris       |         None | France         | 75002             | 13.86 |
|        24 |          4 | 2009-04-06 00:00:00 | Ullevålsveien 14        | Oslo        |         None | Norway         | 0171              |  5.94 |
+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+
(Output limit exceeded, 10 of 168 total rows shown)

While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are - enter that number below.

1 / 1 point

168

Correct

3.

Question 3

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

1

2

3

4

SELECT * 

FROM Customers

WHERE FirstName='Jack' AND LastName ='Smith'

 

+------------+-----------+----------+-----------------------+-----------------+---------+-------+---------+------------+-------------------+-------------------+-------------------------+--------------+
| CustomerId | FirstName | LastName | Company               | Address         | City    | State | Country | PostalCode | Phone             | Fax               | Email                   | SupportRepId |
+------------+-----------+----------+-----------------------+-----------------+---------+-------+---------+------------+-------------------+-------------------+-------------------------+--------------+
|         17 | Jack      | Smith    | Microsoft Corporation | 1 Microsoft Way | Redmond | WA    | USA     | 98052-8300 | +1 (425) 882-8080 | +1 (425) 882-8081 | jacksmith@microsoft.com |            5 |
+------------+-----------+----------+-----------------------+-----------------+---------+-------+---------+------------+-------------------+-------------------+-------------------------+--------------+

What company does Jack Smith work for?

1 / 1 point

Apple Inc.

Google Inc.

Microsoft Corp

Rogers Canada

Correct

4.

Question 4

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.

1

2

3

4

SELECT CustomerId, Total, InvoiceId,InvoiceDate

FROM Invoices

WHERE InvoiceId = 315

GROUP BY Total BETWEEN 1 AND 5 

+------------+-------+-----------+---------------------+
| CustomerId | Total | InvoiceId | InvoiceDate         |
+------------+-------+-----------+---------------------+
|         58 |  1.98 |       315 | 2012-10-27 00:00:00 |
+------------+-------+-----------+---------------------+

What was the invoice date for invoice ID 315?

1 / 1 point

6-12-2010

1-29-2013

10-27-2012

12-22-2013

Correct

5.

Question 5

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks whose name starts with 'All'.

1

2

3

SELECT Name, TrackId, AlbumId

FROM Tracks

WHERE Name LIKE 'All%'

+----------------------------------------+---------+---------+
| Name                                   | TrackId | AlbumId |
+----------------------------------------+---------+---------+
| All I Really Want                      |      38 |       6 |
| All For You                            |     134 |      14 |
| All Star                               |     385 |      33 |
| All My Life                            |    1009 |      81 |
| All My Love                            |    1608 |     130 |
| All Within My Hands                    |    1892 |     155 |
| All or None                            |    2192 |     180 |
| All Dead, All Dead                     |    2274 |     186 |
| All the Best Cowboys Have Daddy Issues |    2888 |     230 |
| All Because Of You                     |    2969 |     235 |
+----------------------------------------+---------+---------+
(Output limit exceeded, 10 of 15 total rows shown)

While only 10 records are shown, the query will indicate how many total records there are for this query - enter that number below.

1 / 1 point

15

Correct

6.

Question 6

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customer emails that start with "J" and are from gmail.com.

1

2

3

4

SELECT *

 

FROM Customers

WHERE Email LIKE 'j%gmail.com'

+------------+-----------+----------+---------+-------------+----------------+-------+---------+------------+-------------------+------+---------------------+--------------+
| CustomerId | FirstName | LastName | Company | Address     | City           | State | Country | PostalCode | Phone             |  Fax | Email               | SupportRepId |
+------------+-----------+----------+---------+-------------+----------------+-------+---------+------------+-------------------+------+---------------------+--------------+
|         28 | Julia     | Barnett  |    None | 302 S 700 E | Salt Lake City | UT    | USA     | 84102      | +1 (801) 531-7272 | None | jubarnett@gmail.com |            5 |
+------------+-----------+----------+---------+-------------+----------------+-------+---------+------------+-------------------+------+---------------------+--------------+

Enter the one email address returned (you will likely need to scroll to the right) below.

1 / 1 point

jubarnett@gmail.com

Correct

7.

Question 7

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.

1

2

4

5

3

6

SELECT *

FROM Invoices

ORDER BY  InvoiceId DESC

 

WHERE BillingCity LIKE 'Bras%' 

 

+-----------+------------+---------------------+----------------+-------------+--------------+----------------+-------------------+-------+
| InvoiceId | CustomerId | InvoiceDate         | BillingAddress | BillingCity | BillingState | BillingCountry | BillingPostalCode | Total |
+-----------+------------+---------------------+----------------+-------------+--------------+----------------+-------------------+-------+
|       319 |         13 | 2012-11-01 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  8.91 |
|       264 |         13 | 2012-03-03 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         | 13.86 |
|       253 |         13 | 2012-01-22 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  1.98 |
|       132 |         13 | 2010-07-31 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  0.99 |
|        80 |         13 | 2009-12-10 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  5.94 |
|        58 |         13 | 2009-09-07 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  3.96 |
|        35 |         13 | 2009-06-05 00:00:00 | Qe 7 Bloco G   | Brasília    | DF           | Brazil         | 71020-677         |  1.98 |
+-----------+------------+---------------------+----------------+-------------+--------------+----------------+-------------------+-------+

What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer.

1 / 1 point

13.86

Correct

8.

Question 8

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

1

2

3

SELECT *

FROM Invoices

ORDER BY InvoiceID DESC

+-----------+------------+---------------------+------------------------------------------+---------------+--------------+----------------+-------------------+-------+
| InvoiceId | CustomerId | InvoiceDate         | BillingAddress                           | BillingCity   | BillingState | BillingCountry | BillingPostalCode | Total |
+-----------+------------+---------------------+------------------------------------------+---------------+--------------+----------------+-------------------+-------+
|       412 |         58 | 2013-12-22 00:00:00 | 12,Community Centre                      | Delhi         |         None | India          | 110017            |  1.99 |
|       411 |         44 | 2013-12-14 00:00:00 | Porthaninkatu 9                          | Helsinki      |         None | Finland        | 00530             | 13.86 |
|       410 |         35 | 2013-12-09 00:00:00 | Rua dos Campeões Europeus de Viena, 4350 | Porto         |         None | Portugal       | None              |  8.91 |
|       409 |         29 | 2013-12-06 00:00:00 | 796 Dundas Street West                   | Toronto       |           ON | Canada         | M6J 1V1           |  5.94 |
|       408 |         25 | 2013-12-05 00:00:00 | 319 N. Frances Street                    | Madison       |           WI | USA            | 53703             |  3.96 |
|       407 |         23 | 2013-12-04 00:00:00 | 69 Salem Street                          | Boston        |           MA | USA            | 2113              |  1.98 |
|       406 |         21 | 2013-12-04 00:00:00 | 801 W 4th Street                         | Reno          |           NV | USA            | 89503             |  1.98 |
|       405 |         20 | 2013-11-21 00:00:00 | 541 Del Medio Avenue                     | Mountain View |           CA | USA            | 94040-111         |  0.99 |
|       404 |          6 | 2013-11-13 00:00:00 | Rilská 3174/6                            | Prague        |         None | Czech Republic | 14300             | 25.86 |
|       403 |         56 | 2013-11-08 00:00:00 | 307 Macacha Güemes                       | Buenos Aires  |         None | Argentina      | 1106              |  8.91 |
+-----------+------------+---------------------+------------------------------------------+---------------+--------------+----------------+-------------------+-------+
(Output limit exceeded, 10 of 412 total rows shown)

What is the number of items placed for the 8th person on this list? Enter that number below.

1 / 1 point

7

Correct

9.

Question 9

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find the albums with 12 or more tracks.

1

2

3

4

SELECT *

FROM Albums

WHERE AlbumId >=12

 

+---------+--------------------------------------------------+----------+
| AlbumId | Title                                            | ArtistId |
+---------+--------------------------------------------------+----------+
|      12 | BackBeat Soundtrack                              |        9 |
|      13 | The Best Of Billy Cobham                         |       10 |
|      14 | Alcohol Fueled Brewtality Live! [Disc 1]         |       11 |
|      15 | Alcohol Fueled Brewtality Live! [Disc 2]         |       11 |
|      16 | Black Sabbath                                    |       12 |
|      17 | Black Sabbath Vol. 4 (Remaster)                  |       12 |
|      18 | Body Count                                       |       13 |
|      19 | Chemical Wedding                                 |       14 |
|      20 | The Best Of Buddy Guy - The Millenium Collection |       15 |
|      21 | Prenda Minha                                     |       16 |
+---------+--------------------------------------------------+----------+
(Output limit exceeded, 10 of 336 total rows shown)

While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.

0 / 1 point

336

Incorrect