Unit 1.2 SQL Review

1. Unit 1.2SQL Review

2. Learning Objectives

3. AND/OR • These logical operators join two or more conditions in which a WHERE clause is used. • AND requires that both conditions are true for the data to be qualified. • OR requires any one of the conditions to resolve as true for the data to be qualified. • When both AND & OR appear in an SQL statement, AND takes precedence over OR.

4. AND Operator Product Find all current products that cost over $20. SELECTProductName FROM Product WHEREUnitPrice > 20 AND Discontinued = 0 ; Solution:

5. OR Operator Product Show products whose price is less than $20 or greater than or equal to $60. SELECTProductName FROM Product WHEREUnitPrice< 20 ORUnitPrice>= 60 ; Solution:

6. OR followed by AND LineItem Find all Products (use IDs) that have sold because they are a good deal. i.e. Find cheap products (<=$20) or costly(>$20) products with discounts. SELECT Distinct(ProductID) FROMLineItem WHEREUnitPrice <= 20 ORUnitPrice > 20 AND Discount <> 0 ; Solution:

7. LIKE • LIKE is an operator that must be used when: • Only a part of the value is known • A percent sign (i.e. %) needs to be used for the unknown portion. • For example, “… WHERE State LIKE ‘New%’ qualifies New York, New Jersey, New Mexico & New Hampshire.

8. LIKE Customer Select customers whose phone number contains “4112”. SELECTContactName FROM Customer WHERE Phone like ‘%4112’; Solution:

9. BETWEEN • BETWEEN is used when qualifying for a certain range of values held by a field. • For example, a child care service may want to accept children between the ages of four and eight.

10. BETWEEN Find product (by name) whose price is between $15 and $75. SELECT ProductName FROM Product WHERE UnitPrice between 15 and 75 ;

11. IN / NOT IN • Math operators (=, >, >=, <, <=, <>) assume a comparison between a field and a single value (e.g., disk = 80) • When comparing a field to a set of values, use the IN operator (e.g., disk in (80, 120) ) • When choosing a value not in the set of values, use the NOT IN operator (e.g., NOT disk IN (80, 120))

12. IN Customer Find all company’s in USA and Canada. SELECT CompanyName FROM Customer WHERE Country in (‘USA', ‘Canada') ; Solution:

13. NOT IN Customer Find all company’s not in USA and Canada. SELECT CompanyName FROM Customer WHERE not Country in (‘USA', ‘Canada') ; Solution:

14. Functions in SQL • The following functions are available in SQL Count( ), Sum( ), Avg( ), Min( ), Max( ), Distinct( ) • Functions appear on the same line as the column list • Nested functions are sometimes used in combination with a group by clause SELECT sum(sale_price * quantity) FROMlineitem GROUP BYxorder$id ;

15. Functions • Sum: returns the total sum of values of a field • Count: returns the number of selected records • Group by: is used to order a selected field for which an aggregate function (like SUM or COUNT) is being used. • Group by ... Having: is used when “Group By” is needed in conjunction with a condition that needs a “where” clause • Order by: is used to specify how the selected data must be sorted • Distinct: used when selecting non-unique fields.

16. Sum (x) LineItem What is the total number of product items ordered. In other words, make a total of all quantities for all products. SELECT sum(Quantity) FROM LineItem ; Solution:

17. Count (x) Product How many total products do we have? SELECT Count(ProductID) FROM Product ; Solution:

18. Group by LineItem How many products did each order contain? SELECTOrderID, Count(ProductID) FROMLineItem GROUP BYOrderID ; Solution:

19. Group by … Having LineItem How many orders have more than one product? SELECT OrderID, Count(ProductID) FROMLineItem GROUP BY OrderID HAVING Count(ProductID) > 1 ; Solution:

20. Order by Product Produce a product list sorted alphabetically by name. SELECTProductName FROM Product ORDER BYProductName; Solution:

21. Distinct Orders Which customers, by email address, have placed an order? SELECT Distinct(EmailAddress) FROM Orders; Count would count duplicate emails. Distinct cannot be used with count. To get the total you must count the rows in Access. can be used with Count in SQL Server e.g. COUNT(DISTINCT CustomerID) AS uniqueCustomers Solution:

22. Join • When two or more tables appear in the FROM clause, SQL assumes that you want to compare records between the tables. • Comparing records between tables is called a JOIN. • Through joins, data that you carefully separated during the design phase can now be reintegrated to provide useful information. • You must instruct SQL to compare the records by looking for matches between primary and foreign keys (PK of table 1 = FK of table 2).

23. 2-Table Join Customer Orders Which customers, by name, have placed an order? SELECT Distinct(Customer.ContactName) FROM Customer, Orders WHERECustomer.EmailAddress= Orders.EmailAddress; Note: VWDE will translate this into … Solution:

24. 3 - Table Join Customer Orders How many customers, by name, ordered products with a discount? SELECT Distinct (Customer.ContactName) FROM Customer, Orders, LineItem WHERECustomer.EmailAddress = Orders.EmailAddress ANDOrders.OrderID = LineItem.OrderID AND Discount > 0; LineItem In VWDE… Solution:

25. Which customers have ordered discontinued products? 4 - TableJoin Customer Orders SELECTCustomer.ContactName, FROMCustomer, Orders, LineItem, Product WHERECustomer.EmailAddress = Orders.EmailAddress ANDOrders.OrderID = LineItem.OrderID ANDLineItem.ProductID = Product.ProductID; ANDProduct.Discontinued = 1 LineItem Products Solution:

26. Subqueries • Subqueries are queries contained inside of other queries • Problems such as comparing an average value with a group of records can only be solved using a subquery • The inner subquery always executes first and then the results of the inner query are compared with the outer query. • When debugging you should try running the inner query by itself to get a feel for what is going on. • If the inner query returns a single value (e.g., select avg(x)) use arithmetic operators (=, <, >, <>, >=, <=) for the comparison • If the inner query returns multiple values (e.g., select x) use IN or NOT IN for the comparison.

27. Subquery: Compare records with average LineItem Which products cost above average? SELECTProductID FROMLineItem WHEREUnitPrice >= ( select avg(UnitPrice) from LineItem ) ; Solution:

28. Insert Insert statements are used to add a new record. • Include all fields that are required • Exclude all fields that are set to ReadOnly • Make sure that data types match • Make sure that the variables and their values are in the same order • Multiple table insert statements begin with parent table inserts before child table inserts

29. Insert: Example Insert the following new customer into the database. INSERT INTO Customer (EmailAddress, Password, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, Gender) VALUES (‘hoops@gmail.com’, ‘basketball’, ‘Hoops R Us’, ‘John Balls’, ‘Owner’, ‘218 Center Street’, ‘Columbus’, ‘OH’, ‘44060’; ‘USA’; ‘(440)255-8969’; ‘(440)255-7456’, ‘Male’); EmailAddress: hoops@gmail.com Password: basketball CompanyName: Hoops R Us ContactName: John Balls ContactTitle: Owner Address: 218 Center Street City: Columbus Region: OH PostalCode: 44060 Country: USA Phone: (440)255-8969 Fax: (440)255-7456

30. Update Update statements are used to edit records. • Specify which records must be updated • One statement can update multiple records simultaneously

31. Update: Example UPDATE Customer SET Country = ‘CA’ WHERE Country = ‘Canada’ Update all the records in the customer table for Canada to be CA. CA