SQL in Action

1. SQL in Action Amit Bhawnani & Nimesh Shah

2. 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, ..., Anfromr1, r2, ..., rmwhere P • Aisrepresent attributes • risrepresent 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.

3. 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

4. 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

5. SELECT ALL • Display all the information on our employees • SELECT * FROM employees Tip: explicitly specify columns instead of querying with *

6. ORDER BY • Display a list of vendor names in ZIP Code order. • SELECT VendName, VendZipCode FROM Vendors ORDER BY VendZipCode

7. 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, EmployeeIDFROM Employees ORDER BY EmpLastName, EmpFirstName

8. Concatenation Expression • Give me a list of employee full names and their dates of birth. • Select EmpFirstname || ‘ ‘ || EmpLastName as EmployeeName, DOB AS DateOfBirthFROM Employees

9. Mathematical Expression • What is the inventory value of each product? • SELECT ProductName, RetailPrice * QuantityOnHand AS InventoryValueFROM Products

10. Aggregate Functions - SUM • What is the total amount we pay in salaries to ALL our employees? • SELECT SUM(Salary) AS TotalSalaryAmountFROM Employees

11. Aggregate Functions - MIN • What is the lowest price we charge for a product? • SELECT MIN(Price) AS LowestProductPriceFROM Products

12. FILTER • What are the names of our customers who live in the state of washington (WA)? • SELECT CustFirstName, CustLastNameFROM Customers WHERE CustState = 'WA'

13. COUNT • How many of our customers live in the state of washington (WA)? • SELECT count(*) as NumberOfCustomersFROM Customers WHERE CustState = 'WA'

14. 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, VendPhoneFROM Vendors WHERE VendCity <> 'Bellevue'

15. Filter - Less Than and Greater Than • Are there any orders where the ship date was accidentally posted earlier than the order date? • SELECT OrderNumberFROM Orders WHERE ShipDate < OrderDate

16. LIKE • Show me an alphabetical list of products with names that begin with ‘Dog’ • SELECT ProductNameFROM Products WHERE ProductName LIKE 'Dog%‘ ORDER BY ProductName

17. 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, CustZipCodeFROM Customers WHERE CustLastName = 'Patterson‘ AND (CustState = 'CA‘ OR CustZipCode LIKE '%9‘)

18. 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‘

19. IS NULL • Which vendors do we work with that don’t have a Web site • select vendorID,VendNamefrom vendors where VendWebPage is null

20. IN • Give me the names of all vendors based in Ballard, Bellevue, and Redmond • select vendorID,VendNamefrom vendors where VendCity in ('Ballard','Bellevue','Redmond' )

21. TOP • Display the name and retail price of the 10 most expensive products • Select top 10 productName,RetailPricefrom products order by retailPricedesc

22. 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.CustCityORDER by CustPerCitydesc

23. GROUP BY • Display product number and total sales for each product • SELECT productNumber, SUM(QuotedPrice*QuantityOrdered) as total_priceFROM Order_DetailsGROUP BY productNumberORDER BY total_pricedesc

24. 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_priceFROM Order_DetailsGROUP BY productNumberHAVING sum(QuotedPrice*QuantityOrdered) > 100000 order by total_pricedesc Tip: Having clause is used to filter rows after all the rows are selected. Do not try to use HAVING instead of WHERE

25. INNER Join • Display all product names and their category descriptions. • SELECT Categories.CategoryDescription, Products.ProductNameFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

26. More then two tables • List of customer names who have ever ordered a helmet. • SELECT DISTINCT Customers.CustFirstName, Customers.CustLastNameFROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDINNER JOIN Order_DetailsON Orders.OrderNumber = Order_Details.OrderNumberINNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumberWHERE Products.ProductName LIKE '%Helmet%'

27. 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

28. 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_Detailsgroup by productNumber) as b ON Products.ProductNumber = b.ProductNumber

29. Subqueries as Filters (IN) • Find all the customers who have placed an order on 2008-02-28 • select customerId,custFirstName,CustLastNamefrom customers where customerid in (select customerid from orders where OrderDate = '2008-02-28')

30. Correlated subquery • Find all the customers that have placed over 40 orders • select customerId,CustFirstName,CustLastNamefrom Customers c where 40 < (select count(*) from orders o where o.customerId = c.customerId)

31. 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')

32. 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')

33. 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 LastOrderDateFROM Customers

34. 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

35. EXCEPT • Find all the customers who have placed an order on 2007-09-13 but NOT on 2007-09-22 • select customerId,custFirstName,CustLastNamefrom customers where customerid in(select customerid from orders where OrderDate = '2007-09-13‘ except select customerid from orders where OrderDate = '2007-09-22‘)

36. UNION • Give a list of customer full and vendor full names that belong to the state of texas. • SELECT CustFirstName + ' ' + CustLastName as CustNamefrom customers where custState = 'TX‘unionselect VendNamefrom vendors where VendState = 'TX' Tip: try to use UNION ALL in place of UNION.

37. Update • Increase the retail price of all products by 10 percent. • UPDATE Products SET Price = Price + (0.1 * Price)

38. Update – with filters • Modify products by increasing the retail price by 4 percent for products that are clothing • UPDATE Products SET RetailPrice = RetailPrice * 1.04WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryDescription = 'Clothing')

39. Delete • Delete all order details for orders placed before January 1, 2008 • DELETE FROM Order_DetailsWHERE OrderNumber IN (SELECT OrderNumber FROM Orders WHERE OrderDate < '2008-01-01')

40. Delete • Delete vendors who do not provide any products • DELETE FROM Vendors WHERE VendorID NOT IN (SELECT VendorID FROM Product_Vendors)

41. 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')

42. 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.CustPhoneNumberFROM Customers WHERE Customers.CustFirstName = 'David'AND Customers.CustLastName = 'Smith'

43. 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_datedatetime default getdate()) • Alter table employees add dept_id integer foreign key references department(dept_id)