Explination of the Window
At this point, you should have the 'SQL Query Analyzer' open. The query analyzer is basically a command prompt type of interface to SQL server. It allows a user to write in SQL queries to see if they are legal to write in MS SQL and also the result of the queries. The window is pretty easy to figure out. Here is a quick screen shot of it below to give you a reference to what I'm explaining.
Like most new windows applications, the menu buttons are self explanitory. In any case, there are only three major ones you'll need to know...
Check - The check is a way for you to check if you have written a legal SQL query.
Arrow - This is the execute command button. This will send the query to the server and the server will write back the result to you.
Square - This is the stop execution command. If you write a query that will take a long time to execute, you can cancel the request.
Writing, Checking, and Seeing the Result of a Query
To explain writing, checking and seeing the result of a query is best shown by an example. The example query we will be trying to find is 'What supplier has an ID of 2?'
To do this, just type directly into the panel given for you to write in. Note that SQL is NOT case sensitive. We will write the queries in the correct book style to make it easy for you to understand and see what is going on. The query is shown below...
As you can see, the check and arrow icons are now colored in because of the query that
was written.
Here's the result of clicking on the arrow with a valid SQL statement. The result is
always displayed in the bottom pane. This pane is shown/hidden by the icon that
looks like there are two panes.
This is the result of a bad query...
The error statement in the bottom pane should help you figure out what went wrong in
your query.
If you click on the execute query button (the green arrow) with the valid statement,
you'll get the following result.
As you can see, the result is 'Video Warehouse'. Pretty easy, right?
Word of caution: SQL Server does not support the following standard SQL statements
1. INTERSECT
2. EXCEPT
You can perform these same functions using different operations supported by SQL Server.
Exercise For Wednesday, April 21, 1999
Now that you're getting better at these queries, try these out and write us up a quick little document telling us the results...
Query 1
"Which suppliers did the store order the movie "Are You Listening?
1932" from?"
Note: You have to use DISTINCT to ensure that you have no duplicate information.
Query 1 Answer
SELECT DISTINCT SupplierName
FROM Suppliers, Orders, Movies
WHERE Movies.MovieID = Orders.MovieID AND
Orders.SupplierID = Suppliers.SupplierID AND
MovieName = "Are You Listening? 1932";
Query 2
"Who ever rented "My Marriage 1936"?
Query 2 Answer
SELECT FirstName + LastName AS Customer
FROM Customers, Rentals, Inventory, Movies
WHERE MovieName = "My Marriage 1936" AND
Movies.MovieID = Inventory.MovieID AND
Inventory.TapeID = Rentals.TapeID AND
Rentals.CustomerID = Customers.CustID;