Question 1
Suppose we wish to view the Order ID number, the Customer name, order date and freight charges for all of our orders in the Northwind database. What I want to know is why the following query is not a correct solution.

SELECT OrderID, CompanyName, OrderDate, Freight FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders. CustomerID) INNER JOIN [Order Details] ON Orders. OrderID = [Order Details).OrderID

Question 2
Write a query that finds the Earliest and Latest Dates of Hire for the employees. No grouping here.

Question 3
Write a query that displays the number of employees in each city in which there are at least 2 employees.

Question 4
How many rows are returned by this query? Note: the answer should be 22. If you are not getting that number then you need to drop your version of
Northwind and recreate a clean copy of it.

SELECT Orders.OrderID
Orders.ShippedDate
[Order Details].ProductID
[Order Details].UnitPrice

FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID

WHERE [Order Details].ProductID = 14
Order by ShippedDate

Question 5
How many rows does the following query return?
SELECT Products. ProductName
[Order Details].UnitPrice
Orders.ShippedDate
Customers.CompanyName

FROM Products INNER JOIN [Order Details] ON Products. ProductID = [Order Details].ProductID
INNER JOIN Orders ON [Order Details].OrderID : = Orders.OrderID
INNER JOIN Customers ON Orders. CustomerID = Customers.CustomerID
Where Orders.ShippedDate Between '4/1/2008' And '4/30/2008'
And products.product name = 'TOFU'
Order By CompanyName

Question 6
How many rows does the following query return?

SELECT Orders.OrderID
Orders.ShippedDate
[Order Details].ProductID
[Order Details].UnitPrice

FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderlD
WHERE [Order Details].ProductID=14
AND Orders.ShippedDate Between '4/1/2008' And '4/30/2008'

Question 7
How many rows with a ProductID of 14 are returned by this query? I did not add a WHERE clause here because I want you to search for the ProductIDs of 14 by scrolling thru the data. This is to make a point.

SELECT Orders. OrderID
Orders.ShippedDate
[Order Details].ProductID
[Order Details].UnitPrice

FROM Orders INNER JOIN [Order Details] ON Orders.OrderlD = [Order Details].OrderlD
WHERE Orders.ShippedDate Between '4/1/2008' And '4/30/2008'
Order by ProductID

Question 8
Insert a new employee into your database.
Create an inner join between the Employee table and the Orders table displaying the employee's LastName, EmployeelD, OrderDate, and the Freight cost. Order by the employee's LastName. Does your new employee show up in this query?

Question 9
SELECT Customers.CompanyName
Orders.ShippedDate
[Order Details].UnitPrice
Products.ProductName

FROM Products INNER JOIN Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders. OrderID = [Order Details].OrderlD

ON [Order Details).ProductID = [Order Details].ProductID
Where Orders.ShippedDate Between '4/1/2008' And '4/30/2008'
And products.product name = 'TOFU'
Order By Company! Name

Question 10
Refer back to the CROSS JOIN query below that is between Employees and Region.
SELECT Employees.EmployeeID,Employees.LastName,Region,RegionID, RegionDescription

FROM Employees CROSS JOIN Region

a. How many employees are there in the Employees table?
b. How many regions are there in the Region table?
c. What does number-of-employees times number-of-regions equal?
d. How many rows are returned in your cross join?
e. What is another term used for cross joins?

Question 11
Refer back to the INNER JOIN query between Employees and Orders tables.
Change the word INNER in your query to LEFT. Does your new employee show up in this query?
Change the word INNER in your query to RIGHT. Does your new employee show up in this query?
How does a LEFT Join differ from a RIGHT Join?
How are Left Joins different from an INNER Join and when should you use a LEFT Join vs an INNER Join? I am asking this question because
there are students in this class that seem to feel they should be using LEFT Joins over INNER Joins. There is a subtle difference, but important difference nonetheless between the two.

Question 12
Suppose we want know which employees are selling which products. Write a query that gives you the Employees (LastName, FirstName) and all the products (Product Name) that an employee has sold. This means you need information about the "orders" each employee was involved with. I only want to see the distinct rows.

Question 13
Go to the Views folder in SSMS. Look at the View called Summary of Sales by Quarter. What View does this view call.
Sales by Category
Invoices
Current Product List
Order Subtotals

Question 14
Look up the Delete statement. Write a Delete statement that would delete all Products where the ProductName = "Widget'.
DELETE FROM Products
DELETE FROM Products WHERE ProductName ="Widget"
DELETE FROM Products IF ProductName ="Widget'
DELETE FROM Products WHERE ProductName =Widget"

Question 15
Look up the Update statement. Write an Update statement that would increases the Pay of all employees who live in Cincinnati by 10%. That is same as multiplying by 1.10.
UPDATE [Employees] SET Pay = Pay *1.10 WHERE City ='Cincinnati'
UPDATE [Employees] LET Pay = Pay *1.10 WHERE City ='Cincinnati"
WHERE City ='CincinnatiÂ³ UPDATE Pay - Pay *1.10 FOR [Employees]
UPDATE [Employees] SET Pay *1.10 WHERE City =Cincinnati'

Question 16
Add in a new column to your Employee table called Pay. Make it a floating point numeric data type.
ADD Pay FLOAT to Employees
ALTER Employees ADD Pay FLOAT
ALTER Employees ADD Pay datatype FLOAT
ALTER TABLE [Employees] ADD Pay FLOAT

Question 17
Look up the Insert statement. Write an Insert statement that would insert a row into the [Order Details] table.

INSERT INTO [Northwind].[dbo].[Order Details]
([OrderlD]
[ProductID]
[UnitPrice]
[Quantity]
[Discount])
VALUES
(10248,50,2,41,2,null)

INSERT INTO [Northwind].[dbo].[Order Details]
([OrderlD]
[ProductID]
[UnitPrice]
[Quantity]
[Discount])
VALUES
(10248,500,2,41,2,5)

INSERT INTO [Northwind].[dbo].[Order Details]
([OrderlD]
[ProductID]
[UnitPrice]
[Quantity]
[Discount])
VALUES
(10248,50,2,41,2,5)

INSERT INTO [Northwind].[dbo].[Order Details]
([OrderlD]
[ProductID]
[UnitPrice]
[Quantity]
[Discount])
VALUES
(10248',50,2,41,2,5)

Question 18
Go to Programmability folder under the Northwind database and right click on the Ten Most Expensive Products stored procedure. How are the top 10 products picked? Is there another way of doing this?
SET TOP10 or you can use ROWCOUNT 10 within the SELECT statement.
SET ROWCOUNT 10 or you can use TOP 10 within the WHERE statement.
SET ROWCOUNT 10 or you can use TOP 10 within the SELECT statement.
RETURN FIRST 10 ROWS or you can use TOP 10 within the SELECT statement.

Question 19
If you wanted to display a report showing all the Customers and the Products that they bought what tables would you have to navigate through in order to get all the information.
Can't be done.
Go from Customers straight to Products
Go from Customers to [Order Details] to Products
Go from Customers to Orders to [Order Details] to Products

Question 20
Use the Northwind database diagram. Suppose we no longer are going to carry condiments (which has a CategoryID of 2). Delete all condiment records from the products table.

DELETE
FROM Products
WHERE Products.CategoryID =2

DELETE
FROM Products
WHERE Products.CategoryID =2;

DELETE Products.CategorylD =2
FROM Products

FROM Products
DELETE Products.CategoryID
WHERE Products.CategoryID =2;

Question 21
Use the Northwind database diagram. List countries with more than 5 customers and show the number of customers in that country.

SELECT country, COUNT(*) AS (customer cnt]
FROM Customers
GROUP BY country
HAVING COUNT(*) > 5

SELECT country, COUNT(*) AS (customer cnt)
FROM Customers
WHERE COUNT(*) > 5
GROUP BY country

SELECT COUNT AS (customer ent)
FROM Customers
GROUP BY country
WHERE COUNT(*) > 5

SELECT COUNT(country) AS (customer ant]
FROM Customers
GROUP BY country
HAVING COUNT(*) > 5

Question 22
Retrieve all the names of customers who purchased tofu. Pick the best query for this.

SELECT Customers.ContactName
FROM Customers CROSS JOIN Products
WHERE Products.ProductName= Tofu'

SELECT Customers.ContactName
FROM Customers, Products
WHERE Products.ProductName = Tofu'

SELECT Customers.ContactName
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders. CustomerID
INNER JOIN [Order Details] ON Orders. OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
WHERE Products.ProductName= "Tofu'

SELECT Customers.CompanyName
FROM Customers INNER JOIN Orders ON Customers. CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders. OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName = "Tofu'

Question 23
Bring back all employee information for employees whose regions is null.

SELECT
FROM Employees
WHERE Region is Null

SELECT
FROM Employees
WHERE Region = Null

SELECT
FROM Employees
WHERE Region"

SELECT
FROM Employees
WHERE Region isNull

Question 24
Use the following table:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

If a row is inserted into the table and no date is supplied, the database will automatically insert the current date.
True
False

Question 1 Suppose we wish to view the Order ID number, the Custom...