Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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.

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.

Programming with Microsoft Visual Basic 2017
Programming with Microsoft Visual Basic 2017
8th Edition
ISBN:9781337102124
Author:Diane Zak
Publisher:Diane Zak
Chapter4: The Selection Structure
Section: Chapter Questions
Problem 7E
See similar textbooks
icon
Related questions
Question

Starting from question 4

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
steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Functions
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Programming with Microsoft Visual Basic 2017
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:
9781337102124
Author:
Diane Zak
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS