CSC 3084: Web Development and Programming

1. CSC 3084:Web Development and Programming Chapter 18: How to Use SQL to Work with a MySQL Database

2. SELECT Statement Examples SELECT productID, productName, listPrice FROM products ORDER BY listPrice SELECT productID, productName, listPrice FROM products WHERE listPrice < 450 ORDER BY listPrice

3. SELECT Statement Examples SELECT productID, productName AS name, listPrice AS price FROM products WHERE listPrice < 450 ORDER BY listPrice • The AS keyword lets you create a column alias, which will be used in place of the original column name when generating the result set

4. SELECT Statement Examples • Column aliases can be created with the following syntax, which omits AS, but it’s not as readable SELECT productID, productName name, listPriceprice FROM products WHERE listPrice < 450 ORDER BY listPrice

5. SELECT Statement Examples • The LIMIT keyword limits the number of rows that are included in the result set. SELECT productID, productName FROM products LIMIT 3 • Retrieve three rows starting at the eighth row: SELECT productID, productName FROM products LIMIT 7, 3

6. WHERE Clause Examples WHERE categoryID = 2 WHERE productName = 'Gibson Les Paul' WHERE listPrice < 499.99 WHERE listPrice >= 499.99 WHERE productName < 'G' (name starts with A to F) WHERE dateAdded < '2010-01-31' WHERE dateAdded >= '2010-01-31' WHERE discountPercent <> 30 (inequality check) WHERE categoryID = 1 AND discount = 30 WHERE categoryID = 1 OR discount = 30

7. LIKE Clause Examples • The LIKE operator is used to retrieve rows that match a string pattern called a mask • All rows that start with “Fender”:WHERE productName LIKE 'Fender%' • All rows that include the substring “cast”:WHERE productName LIKE '%cast%' • All rows that have a zip code that begins with 076 followed by any two characters:WHERE zipCode LIKE '076__' • All rows that have an order date in June of 2010:WHERE orderDate LIKE '2010-06-__%'

8. ORDER BY Clause Examples SELECT productName, listPrice, discountPercent FROM products WHERE listPrice < 500 ORDER BY productName SELECT productName, listPrice, discountPercent FROM products WHERE listPrice < 500 ORDER BY listPrice DESC

9. ORDER BY Clause Examples SELECT productName, listPrice, discountPercent FROM products WHERE categoryID = 1 ORDER BY discountPercent, listPriceDESC

10. INNER JOIN Clause Examples SELECT firstName, lastName, orderDate FROM customers INNER JOIN orders ON customers.customerID = orders.customerID ORDER BY orderDate • Using table and column aliases: SELECT firstName, lastName, orderDate FROM customers c INNER JOIN orders o ON c.customerID = o.customerID ORDER BY orderDate

11. INNER JOIN Clause Examples SELECT firstName, lastName, orderID, productName, itemPrice, quantity FROM customers c INNER JOIN orders o ON c.customerID = o.customerID INNER JOIN orderItemsoi ON o.orderID = oi.orderID INNER JOIN products p ON oi.productID = p.productID ORDER BY o.orderID

12. The Syntax of the Aggregate Functions • Aggregate functions perform calculations on the value in a set of selected rows. • An SQL expression can also be a calculated value that operates on the values in two or more columns. • AVG(expression) • SUM(expression) • MIN(expression) • MAX(expression) • COUNT(expression) • COUNT(*)

13. Examples of Aggregate Functions • Count all products: SELECT COUNT(*) AS productCount FROM products • Count all orders and shipped orders: • SELECT COUNT(*) AS totalCount, COUNT(shipDate) AS shippedCountFROM orders

14. Find Lowest, Highest, and Average Prices SELECT MIN(listPrice) AS lowestPrice, MAX(listPrice) AS highestPrice, AVG(listPrice) AS averagePrice FROM products

15. Get the Total of the Calculated Values for All Orders SELECT SUM(itemPrice * quantity – discountAmount) AS ordersTotal FROM orderItems

16. The GROUP BY and HAVING Clauses • The GROUP BY clause groups the rows of a result set based on one or more columns or expressions. It’s typically used in SELECT statements that include aggregate functions. • The HAVING clause specifies a search condition for a group or an aggregate. MySQL applies this condition after it groups the rows that satisfy the condition in the WHERE clause.

17. GROUP BY and HAVING Clauses • General syntax: SELECT select_list FROM table_source [WHERE search_condition] [GROUP BY group_by_list] [HAVING search_condition] [ORDER BY order_by_list]

18. GROUP BY and HAVING Clauses • Calculate the average list price by category: SELECT categoryID, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice FROM products GROUP BY categoryID ORDER BY productCount

19. GROUP BY and HAVING Clauses • Use columns from multiple tables: SELECT categoryName, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice FROM products p JOIN categories c ON p.categoryID = c.categoryID GROUP BY categoryName HAVING averageListPrice > 400

20. GROUP BY and HAVING Clauses • Use a WHERE clause to filter rows before grouping them: SELECT categoryName, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice FROM products p JOIN categories c ON p.categoryID = c.categoryID WHERE listPrice > 400 GROUP BY categoryName

21. Subqueries • A subquery is a SELECT statement that’s coded within another SQL statement • Four ways to introduce a subqueryin a SELECT statement: • In a WHERE clause as a search condition • In a HAVING clause as a search condition • In the FROM clause as a table specification • In the SELECT clause as a column specification

22. Use a Subqueryin the WHERE Clause SELECT productName, listPrice FROM products WHERE listPrice > (SELECT AVG(listPrice) FROM products) ORDER BY listPrice DESC • The value returned by the subquery: 841.895

23. Use Another Subquery in the WHERE Clause SELECT productName, listPrice FROM products WHERE categoryID = (SELECT categoryID FROM categories WHERE categoryName = 'Basses')

24. The EXISTS Operator • The EXISTS operator can be used to test that one or more rows are returned by a subquery. • Get all customers that don’t have any orders: SELECT c.customerID, firstName, lastName FROM customers c WHERE NOT EXISTS (SELECT * FROM orders o WHERE c.customerID = o.customerID)

25. INSERT Statement Examples • Add a single row without using a column list: INSERT INTO products VALUES (DEFAULT, 1, 'tele', 'Fender Telecaster', 'NA', '949.99', DEFAULT, NOW()) • Add a single row using a column list: INSERT INTO products (categoryID, productCode, productName, description, listPrice, dateAdded) VALUES (1, 'tele', 'Fender Telecaster', 'NA', '949.99', NOW())

26. INSERT Statement Examples • Add multiple rows: INSERT INTO categories (categoryID, categoryName) VALUES (4, 'Keyboards'), (5, 'Brass'), (6, 'Woodwind')

27. UPDATE Statement Examples • Update one column of one row: UPDATE products SET discountPercent = '10.00' WHERE productName = 'Fender Telecaster' • Update multiple columns of one row: UPDATE products SET discountPercent = '25.00', description = 'This guitar has great tone and smooth playability.' WHERE productName = 'Fender Telecaster'

28. UPDATE Statement Examples • Update one column of multiple rows: UPDATE products SET discountPercent = '15.00' WHERE categoryID = 2 • Update one column of all rows in the table UPDATE products SET discountPercent = '15.00'

29. DELETE Statement Examples • Delete one row: DELETE FROM products WHERE productID = 6 • Delete multiple rows: DELETE FROM products WHERE categoryID = 3 • Delete multiple rows: DELETE FROM categories WHERE categoryID > 3

30. UPDATE/DELETE with Subqueries UPDATE orders SET shipAmount = 0 WHERE customerID IN (SELECT customerID FROM customers WHERE lastName = 'Sherwood') DELETE FROM orderItems WHERE orderID IN (SELECT orderID FROM orders WHERE customerID = 1)