Workshop

Quiz

1.	Imagine that you had a database table named Albums that contained the following columns: AlbumID, Name, Artist, and DatePurchased. Write a SQL query to retrieve the name of the albums, ordered alphabetically.
2.	Write a SQL query to retrieve, in this order, the artist, name, and date the album was purchased, ordered alphabetically by the artist.
3.	Write a SQL query that retrieves the names of all of the albums by the artist Nirvana, ordered by the date the album was purchased, starting with the most recently purchased.
4.	True or False: The following two SQL queries would return the exact same data: SELECT AlbumID, Name, Artist, DatePurchased FROM Albums And: SELECT * FROM Albums
5.	Describe the steps you would take to add a data source control that returned the name and purchase date of albums whose AlbumID is greater than 5 that were recorded by the artist Pavement.

Answers

1.	The following SQL query would suffice: SELECT Name FROM Albums ORDER BY Name
2.	The following SQL query would suffice: SELECT Artist, Name, DatePurchased FROM Albums ORDER BY Artist
3.	The following SQL query would suffice: SELECT Name FROM Albums WHERE Artist = "Nirvana" ORDER BY DatePurchased DESC
4.	True.
5.	Start by dragging a SqlDataSource control onto the Design view of an ASP.NET page and click the Configure Data Source link. Next, specify the database's connection information. From the Configure Select Statement screen, choose the Albums table from the drop-down list and select the Name and DatePurchased columns. Next, click the WHERE button to bring up the Add WHERE Clause dialog box. In that dialog box, add two filter expressions. For the first, on AlbumID use the > operator with a Source of None and a Value of 5. The second filter expression would be on the Artist column, using the = operator, with a Source of None and a Value of Pavement.

Exercises

1. This exercise is intended to improve your proficiency with SQL. Open the query window by right-clicking on the database in the Database Explorer and choosing New Query.

   In the query window craft the SQL query to retrieve those books whose BookID is less than or equal to 3. (You can enter the SQL directly into the SQL pane or use the diagram or criteria panes if you prefer.) Note the list of books you see when testing the query. Now, run another query, this time retrieving those books that have a price greater than $10.00. (You are encouraged to experiment with the query window further.)