Transcript Slide 1

SQL in Action
Amit Bhawnani & Nimesh Shah
Basic Structure
•
•
SQL is based on set and relational operations with certain modifications and
enhancements
A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
–
Ais represent attributes
– ris represent relations
– P is a predicate.
•
This query is equivalent to the relational algebra expression.
•
A1, A2, ..., An(P (r1 x r2 x ... x rm))
The result of an SQL query is a relation.
SELECT
• What are the names and prices of all the
products we carry?
• Select product name, retail price from the
products table
• SELECT ProductName, RetailPrice FROM
Products
DISTINCT
• Which states do our customers come from ?
• Select the distinct state values from the
customers table
• Select DISTINCT custState FROM Customers
Tip: Use only when necessary
SELECT ALL
• Display all the information on our employees
• SELECT * FROM employees
Tip: explicitly specify columns instead of querying with *
ORDER BY
• Display a list of vendor names in ZIP Code
order.
• SELECT VendName, VendZipCode FROM
Vendors ORDER BY VendZipCode
ORDER BY
• Display the names of our employees, including
their phone number and ID number, and list
them by last name and first name.
• SELECT EmpLastName,
EmpFirstName,EmpPhoneNumber,
EmployeeID
FROM Employees
ORDER BY EmpLastName, EmpFirstName
Concatenation Expression
• Give me a list of employee full names and their
dates of birth.
• Select EmpFirstname || ‘ ‘ || EmpLastName
as EmployeeName, DOB AS DateOfBirth
FROM Employees
Mathematical Expression
• What is the inventory value of each product?
• SELECT ProductName, RetailPrice *
QuantityOnHand AS InventoryValue
FROM Products
Aggregate Functions - SUM
• What is the total amount we pay in salaries to
ALL our employees?
• SELECT SUM(Salary) AS TotalSalaryAmount
FROM Employees
Aggregate Functions - MIN
• What is the lowest price we charge for a
product?
• SELECT MIN(Price) AS LowestProductPrice
FROM Products
FILTER
• What are the names of our customers who live
in the state of washington (WA)?
• SELECT CustFirstName, CustLastName
FROM Customers
WHERE CustState = 'WA'
COUNT
• How many of our customers live in the state of
washington (WA)?
• SELECT count(*) as NumberOfCustomers
FROM Customers
WHERE CustState = 'WA'
FILTER - Inequality
• Display a list of vendor names and phone
numbers for all our vendors,with the exception
of those here in Bellevue.
• SELECT VendName, VendPhone
FROM Vendors
WHERE VendCity <> 'Bellevue'
Filter - Less Than and Greater Than
• Are there any orders where the ship date was
accidentally posted earlier than the order
date?
• SELECT OrderNumber
FROM Orders
WHERE ShipDate < OrderDate
LIKE
• Show me an alphabetical list of products with
names that begin with ‘Dog’
• SELECT ProductName
FROM Products
WHERE ProductName LIKE 'Dog%‘
ORDER BY ProductName
Multiple Filters
• Display customers with the last name
'Patterson’ and the customer should belong to
the state CA or the zip code should end in 9.
• SELECT CustFirstName, CustLastName,
CustState, CustZipCode
FROM Customers
WHERE CustLastName = 'Patterson‘ AND
(CustState = 'CA‘ OR CustZipCode LIKE '%9‘)
BETWEEN
• Display the list of Orders that were placed in
September 2007
• Select * from Orders
WHERE OrderDate BETWEEN '2007-09-01'
AND '2007-09-30‘
IS NULL
• Which vendors do we work with that don’t
have a Web site
• select vendorID,VendName
from vendors
where VendWebPage is null
IN
• Give me the names of all vendors based in
Ballard, Bellevue, and Redmond
• select vendorID,VendName
from vendors
where VendCity in (
'Ballard',
'Bellevue',
'Redmond' )
TOP
• Display the name and retail price of the 10
most expensive products
• Select top 10 productName,RetailPrice
from products
order by retailPrice desc
GROUP BY
• Display the numbers of customers from each
city in descending order of the number of
customers.
• SELECT CustCity, Count(*) as CustPerCity
FROM Customers
GROUP BY Customers.CustCity
ORDER by CustPerCity desc
GROUP BY
• Display product number and total sales for
each product
• SELECT productNumber,
SUM(QuotedPrice*QuantityOrdered) as
total_price
FROM Order_Details
GROUP BY productNumber
ORDER BY total_price desc
HAVING
• Display for the productnumber for all the products
where the total sales for the product is greater than
100000
• SELECT productNumber,
SUM(QuotedPrice*QuantityOrdered) as total_price
FROM Order_Details
GROUP BY productNumber
HAVING sum(QuotedPrice*QuantityOrdered) > 100000
order by total_price desc
Tip: Having clause is used to filter rows after all the rows are
selected. Do not try to use HAVING instead of WHERE
INNER Join
• Display all product names and their category
descriptions.
• SELECT Categories.CategoryDescription,
Products.ProductName
FROM Categories
INNER JOIN Products
ON Categories.CategoryID =
Products.CategoryID
More then two tables
• List of customer names who have ever ordered a helmet.
• SELECT DISTINCT Customers.CustFirstName,
Customers.CustLastName
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Order_Details
ON Orders.OrderNumber = Order_Details.OrderNumber
INNER JOIN Products
ON Products.ProductNumber =
Order_Details.ProductNumber
WHERE Products.ProductName LIKE '%Helmet%'
Left Outer Join
• What products have never been ordered ?
• SELECT Products.ProductNumber,
Products.ProductName FROM Products LEFT
OUTER JOIN Order_Details ON
Products.ProductNumber =
Order_Details.ProductNumber WHERE
Order_Details.OrderNumber IS NULL
LEFT OUTER JOIN
• List product number, Product name and the quantity
ordered for each product. Also include the products
that have not been ordered. Order in descending order
of products sold.
• SELECT Products.ProductNumber,
Products.ProductName, b.cnt
FROM Products LEFT OUTER JOIN
(
select productNumber,sum(quantityOrdered) as cnt
from Order_Details
group by productNumber
) as b
ON Products.ProductNumber = b.ProductNumber
Subqueries as Filters (IN)
• Find all the customers who have placed an
order on 2008-02-28
• select
customerId,custFirstName,CustLastName
from customers
where customerid in (select customerid from
orders where OrderDate = '2008-02-28')
Correlated subquery
• Find all the customers that have placed over
40 orders
• select
customerId,CustFirstName,CustLastName
from Customers c
where 40 < (select count(*) from orders o
where o.customerId = c.customerId)
EXISTS
• List of all employees who generated a sale on
the 2007-09-09
• select EmpFirstName,EmpLastName from
employees where exists (select 1 from Orders
where Orders.EmployeeId =
employees.EmployeeId and OrderDate =
'2007-09-09')
NOT EXISTS
• List of all employees who did not make any
sales on the 2007-09-09
• select EmpFirstName,EmpLastName from
employees where not exists (select 1 from
Orders where Orders.EmployeeId =
employees.EmployeeId and OrderDate =
'2007-09-09')
Subqueries as Column Expressions
• Display a list of customers and the last date on
which they placed an order
• SELECT Customers.CustFirstName,
Customers.CustLastName, (SELECT
MAX(OrderDate) FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID)
AS LastOrderDate
FROM Customers
Subqueries as Column Expressions
• List vendors and a count of the products they
sell to us
• Select VendName,(select
count(productNumber) from product_vendors
where vendorId = vendors.vendorid)
from vendors
EXCEPT
• Find all the customers who have placed an order on
2007-09-13 but NOT on 2007-09-22
• select customerId,custFirstName,CustLastName
from customers
where customerid in
(
select customerid from orders where OrderDate =
'2007-09-13‘
except
select customerid from orders where OrderDate =
'2007-09-22‘
)
UNION
• Give a list of customer full and vendor full names
that belong to the state of texas.
• SELECT CustFirstName + ' ' + CustLastName as
CustName
from customers
where custState = 'TX‘
union
select VendName
from vendors
where VendState = 'TX'
Tip: try to use UNION ALL in place of UNION.
Update
• Increase the retail price of all products by 10
percent.
• UPDATE Products SET Price = Price + (0.1 *
Price)
Update – with filters
• Modify products by increasing the retail price
by 4 percent for products that are clothing
• UPDATE Products
SET RetailPrice = RetailPrice * 1.04
WHERE CategoryID =
(SELECT CategoryID FROM Categories WHERE
CategoryDescription = 'Clothing')
Delete
• Delete all order details for orders placed
before January 1, 2008
• DELETE FROM Order_Details
WHERE OrderNumber IN (SELECT
OrderNumber FROM Orders WHERE
OrderDate < '2008-01-01')
Delete
• Delete vendors who do not provide any
products
• DELETE FROM Vendors
WHERE VendorID NOT IN (SELECT VendorID
FROM Product_Vendors)
Insert
• Insert into the employees table in the columns first
name,last name, street address, city, state, ZIP Code,
area code, and phone number the values
Susan, Metters, 16547 NE 132nd St, Woodinville,WA,
98072, 425, and 555-7825
• INSERT INTO Employees
(EmpFirstName, EmpLastName, EmpStreetAddress,
EmpCity, EmpState,EmpZipCode, EmpAreaCode,
EmpPhoneNumber)
VALUES ('Susan', 'Metters','16547 NE 132nd St',
'Woodinville', 'WA','98072', 425, '555-7825')
Insert data by using SELECT
• Copy to the Employees table the relevant columns in the
Customers table for customer David Smith.
• INSERT INTO Employees (EmpFirstName, EmpLastName,
EmpStreetAddress, EmpCity, EmpState, EmpZipCode,
EmpAreaCode, EmpPhoneNumber)
SELECT
Customers.CustFirstName,Customers.CustLastName,
Customers.CustStreetAddress, Customers.CustCity,
Customers.CustState, Customers.CustZipCode,
Customers.CustAreaCode, Customers.CustPhoneNumber
FROM Customers
WHERE Customers.CustFirstName = 'David'
AND Customers.CustLastName = 'Smith'
Create table
• Create a dept table with dept_id (PK), description, insert_date
• Modify the employees table and associate the department id
with the employees
• create table department
(
dept_id integer primary key,
description varchar(50) not null,
addition_date datetime default getdate()
)
• Alter table employees add dept_id integer foreign key
references department(dept_id)