SQL II:
Lab: Application of Selects and
Joins
As last time:
Download presentation from CULearn to
ensure faster access to queries and ability to
read (especially if youre in the back).
Useful starting point
SELECT count(*)
FROM customers
SELECT count(*)
FROM orders
-- Paste this whole screen into SQL Server
and execute (including this line)
From Last Class:
Who bought Chai?
Develop your queries one step at a time.
That way, you will know where the errors
may be found
Again: Who bought Chai?
SELECT *
FROM orders
SELECT *
FROM orders
inner join [order details]
ON [Link] = [order details].orderid
SELECT *
FROM orders
inner join [order details]
ON [Link] = [order details].orderid
inner join products
ON [order details].productid = [Link]
SELECT *
FROM orders
inner join [order details]
ON [Link] = [order details].orderid
inner join products
ON [order details].productid = [Link]
WHERE [Link] = 'chai
NOW: Change this query so that you see only the Company Names of those who bought Chai!
NOW: Change this query so that you can see the Company Names, date, quantities, unitprice, and discount of those who
bought Chai! ORDER by company name
NOW: Change this query so that you know the total price paid for the Chai on each order.
New Knowledge: Aliases
Sometimes it gets boring to write in names
over and over (and over) again.
SQL has a solution to that called aliases
Two uses:
Replace table name with a shorter name
Give aggregate functions column names
Aliases - example
SELECT
[Link]
,[Link]
,[Link]
,[Link] AS ShippedToName
FROM
orders o
INNER JOIN
customers c
ON
[Link] = [Link]
New Knowledge: Views
You can also create virtual tables that dont actually contain any data,
but which you can query as if they were real tables
Example:
CREATE VIEW viewname AS
SELECT [Link], [Link], [Link], [Link]
FROM orders o
FULL OUTER JOIN customers c
ON [Link] = [Link]
Now, lets examine an existing view. Go to the views tab under the Northwind
database. Right-click on the view called [Link] Sales for 1997. Select top
1000 rows. Then do the same and select design.
New Knowledge: Inline Views
Rather than joining with a table, you can create a query
to replace it.
-- Example 1 (no inline view):
SELECT *
FROM
customers
-- Example 2 (inline view in the FROM table)
SELECT *
FROM
(SELECT *
FROM customers
WHERE country LIKE 'Germany') AS CustomersTable
-- Example 3 (inline view in JOIN)
SELECT *
FROM orders
INNER JOIN
(SELECT *
FROM customers
WHERE country LIKE 'Germany') AS CustomersTable
ON [Link] = [Link]
New Knowledge: ORDER BY
The result set can be ordered by one or
several columns.
The ORDER BY statement always comes:
After the WHERE (if this exists)
After the GROUP BY (if this exists)
SELECT
[Link]
,[Link]
FROM
Customers
ORDER BY [Link]
Test 1
In Northwind, create a query that outputs
the following three columns:
ProductId
ProductName
Average ordersize
You should get 77 rows with the average
quantity for each order of that item.
Test 2
Find out how many items Northwind has
sold of each product along with the total
sum for each
Test 2 Answer
SELECT productname,
SUM(quantity) AS NumberOfSales,
SUM(([Link] * [Link])*
(1 - [Link])) AS TotalSum
FROM products
INNER JOIN [order details] od
ON [Link] = [Link]
GROUP BY productname
Test 3
Now take the query from Test 2 and
replace the product name with a category
name.
It can be done with two relative simple
changes and an additional join
Test 3 Answer
SELECT
categoryname,
SUM(([Link] * [Link])* (1 - [Link])) AS TotalSum,
COUNT(*) AS NumberOfSales
FROM products
INNER JOIN [order details] od
ON [Link] = [Link]
INNER JOIN categories
ON [Link] = [Link]
GROUP BY categoryname
Homework for next class
At [Link], do the following exercises:
4 (Select within Select) (individual homework)
5 (Sum and Count) (individual homework)
14
15
6 (Joins) (individual homework)
1-5
Note that they dont specify inner, left, right, outer join (which defaults to inner). They also use slightly
different formatting, but youll be fine with what youve learned in class.
7 (Joins) (individual homework)
1-8
7 (Joins) (Teamwork, turn in one copy for whole team)
9-11
Print the pages with all your result sets showing
Preferred approach: do screenshots into MS Word and crop off everything but the query and the result.
