Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
1. Write a SQL query to find all the tracks that have a length of 5,000,000 milliseconds or more. 2. Write a SQL query to find all the invoices whose total is between $5 and $15 dollars 3. Write a SQL query to find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

1. Write a SQL query to find all the tracks that have a length of 5,000,000 milliseconds or more. 2. Write a SQL query to find all the invoices whose total is between $5 and $15 dollars 3. Write a SQL query to find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

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

I need help with this exercise

SQLITE TUTORIAL media_types MediaTypeld: INTEGER Name: NVARCHAR(120) genres Genreld: INTEGER Name: NVARCHAR(120) 44 playlists Playlistid: INTEGER Name: NVARCHAR(120) playlist_track Playlistid: INTEGER Trackid: INTEGER tracks Trackid: INTEGER Name: NVARCHAR(200) Albumid: INTEGER MediaTypeld: INTEGER Genreld: INTEGER Composer: NVARCHAR(220) Milliseconds INTEGER Bytes: INTEGER UnitPrice: NUMERIC artists Artistid: INTEGER Name: NVARCHAR(120) invoices Invoiceld: INTEGER Customerld: INTEGER InvoiceDate: DATETIME BillingAddress: NVAR... BillingCity NVARCHA 4 more columns... invoice_items Invoiceltemid: INTEGER Invoiceld: INTEGER Trackid INTEGER UnitPrice: NUMERIC Quantity: INTEGER albums Albumid: INTEGER Title: NVARCHAR(160) Artistd: INTEGER customers Customerid INTEGER FirstName: NVARCHAR(40) LastName: NVARCHAR(20) Company: NVARCHAR(80) Address: NVARCHAR(70) City: NVARCHAR(40) State: NVARCHAR(40) Country: NVARCHAR(40) PostalCode: NVARCHAR(10) Phone: NVARCHAR(24) Fax: NVARCHAR(24) Email: NVARCHAR(60) SupportRepid: INTEGER employees Employeeld: INTEGER LastName: NVARCHAR(20) FirstName: NVARCHAR(20) Title: NVARCHAR(30) Reports To: INTEGER BirthDate: DATETIME HireDate: DATETIME Address: NVARCHAR(70) 7 more columns..... You have to run them in certain order in order to create the database (why?) 1. Write a SQL query to find all the tracks that have a length of 5,000,000 milliseconds or more. 2. Write a SQL query to find all the invoices whose total is between $5 and $15 dollars 3. Write a SQL query to find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY. 4. Write a SQL query to find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00. 5. Write a SQL query to find all the tracks whose name starts with 'All'. 6. Write a SQL query to find all the customer emails that start with "J" and are from gmail.com. 7. Write a SQL query to find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID. 8. Write a SQL query to 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. 9. Write a SQL query to find the albums with 12 or more tracks. 10. Write a SQL code to find how many albums does the artist Led Zeppelin have. 11. Create a list of album titles and the unit prices for the artist "Audioslave". 12. Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query? 13. Find the total price for each album.
Transcribed Image Text:SQLITE TUTORIAL media_types MediaTypeld: INTEGER Name: NVARCHAR(120) genres Genreld: INTEGER Name: NVARCHAR(120) 44 playlists Playlistid: INTEGER Name: NVARCHAR(120) playlist_track Playlistid: INTEGER Trackid: INTEGER tracks Trackid: INTEGER Name: NVARCHAR(200) Albumid: INTEGER MediaTypeld: INTEGER Genreld: INTEGER Composer: NVARCHAR(220) Milliseconds INTEGER Bytes: INTEGER UnitPrice: NUMERIC artists Artistid: INTEGER Name: NVARCHAR(120) invoices Invoiceld: INTEGER Customerld: INTEGER InvoiceDate: DATETIME BillingAddress: NVAR... BillingCity NVARCHA 4 more columns... invoice_items Invoiceltemid: INTEGER Invoiceld: INTEGER Trackid INTEGER UnitPrice: NUMERIC Quantity: INTEGER albums Albumid: INTEGER Title: NVARCHAR(160) Artistd: INTEGER customers Customerid INTEGER FirstName: NVARCHAR(40) LastName: NVARCHAR(20) Company: NVARCHAR(80) Address: NVARCHAR(70) City: NVARCHAR(40) State: NVARCHAR(40) Country: NVARCHAR(40) PostalCode: NVARCHAR(10) Phone: NVARCHAR(24) Fax: NVARCHAR(24) Email: NVARCHAR(60) SupportRepid: INTEGER employees Employeeld: INTEGER LastName: NVARCHAR(20) FirstName: NVARCHAR(20) Title: NVARCHAR(30) Reports To: INTEGER BirthDate: DATETIME HireDate: DATETIME Address: NVARCHAR(70) 7 more columns..... You have to run them in certain order in order to create the database (why?) 1. Write a SQL query to find all the tracks that have a length of 5,000,000 milliseconds or more. 2. Write a SQL query to find all the invoices whose total is between $5 and $15 dollars 3. Write a SQL query to find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY. 4. Write a SQL query to find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00. 5. Write a SQL query to find all the tracks whose name starts with 'All'. 6. Write a SQL query to find all the customer emails that start with "J" and are from gmail.com. 7. Write a SQL query to find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID. 8. Write a SQL query to 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. 9. Write a SQL query to find the albums with 12 or more tracks. 10. Write a SQL code to find how many albums does the artist Led Zeppelin have. 11. Create a list of album titles and the unit prices for the artist "Audioslave". 12. Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query? 13. Find the total price for each album.
14. How many records are created when you apply a Cartesian join (Cross join) to the invoice and invoice items table? 1. The following diagram is a depiction of what type of join? a. Inner Join b. Full outer join C. Left join d. Right join 16. Select which of the following statements are true regarding inner joins. (Select all that apply) a. There is no limit to the number of table you can join with an inner join. b. Performance will most likely worsen with the more joins you make Inner joins retrieve all matching and nonmatching rows from a table d. Inner joins are one of the most popular types of joins use 17. Which of the following is true regarding Aliases? (Select all that apply.) a. Aliases are often used to make column names more readable b. SQL aliases are used to give a table, or a column in a table, a temporary name. An alias only exists for the duration of the query. 18. What is wrong with the following query? SELECT Customers.CustomerName, Orders.OrderID FROM LEFT JOIN ON Customers.CustomerID = Orders.CustomerID FROM Orders AND Customers ORDER BY CustomerName; 19. Write a query to find the total number of invoices for each customer along with the customer's full name, city and email. 20. Write a query to retrieve the track name, album, artistID, and trackID for all the albums. C. table1 C. table2
Transcribed Image Text:14. How many records are created when you apply a Cartesian join (Cross join) to the invoice and invoice items table? 1. The following diagram is a depiction of what type of join? a. Inner Join b. Full outer join C. Left join d. Right join 16. Select which of the following statements are true regarding inner joins. (Select all that apply) a. There is no limit to the number of table you can join with an inner join. b. Performance will most likely worsen with the more joins you make Inner joins retrieve all matching and nonmatching rows from a table d. Inner joins are one of the most popular types of joins use 17. Which of the following is true regarding Aliases? (Select all that apply.) a. Aliases are often used to make column names more readable b. SQL aliases are used to give a table, or a column in a table, a temporary name. An alias only exists for the duration of the query. 18. What is wrong with the following query? SELECT Customers.CustomerName, Orders.OrderID FROM LEFT JOIN ON Customers.CustomerID = Orders.CustomerID FROM Orders AND Customers ORDER BY CustomerName; 19. Write a query to find the total number of invoices for each customer along with the customer's full name, city and email. 20. Write a query to retrieve the track name, album, artistID, and trackID for all the albums. C. table1 C. table2
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps

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