WHERE Clause Syntax Homework

Using the Chinook database and SQL Server Management Studio (SSMS), write SQL queries for the following questions. Submit your answers in a single SQL file to the Blackboard site. Use SQLs commenting syntax to include your name at the top of the file. Also include the question number as a comment before each answer. The answer key to the questions will be released after the deadline to submit homework has passed.

1. Display FirstName and LastName from the Employee table where the ReportsTo column isn't null. (7 rows)

2. Display all customers in the Customer table that do not live in the state California. (56 rows)

3. Display all records in the Invoice table that have an InvoiceDate between April 1st 2010 and May 1st 2010. (7 rows)

4. Display the Title and AlbumId from the Album table where the title starts with the word "The". (30 rows)

5. Find all records from the Album table where the title doesn't start with the letters A through Z. (3 rows)

6. Display CustomerId, BillingCity, BillingCountry and InvoiceDate from the Invoice table where the BillingCountry is equal to Canada, Germany, France, Spain or India. Order the records by InvoiceDate in descending order. (139 rows)

7. Display all records in the Album table where the artist associated with the album has a name containing the word "Black". Hint: You will need to use the Artist table. (6 rows)

8. Display all records in the Track table that do not have an invoice. Hint: You will need to use the InvoiceLine table. (1519 rows)

9. Display all records from the Track table where the MediaTypeId equals 5 and the GenreId is not equal to 1, or the Composer is Gene Simmons. (16 rows)

10. Display all records from the Track table where the AlbumId equals 237 and the Composer column contains the name Dylan or Hendrix. (3 rows)