Relational DBs and SQL Designing Your Web Database (Ch. 8)

1. Relational DBs and SQL • Designing Your Web Database (Ch. 8) • Creating and Working with a MySQL Database (Ch. 9, 10)

2. DML – Adding Table Rows • SQL requires the use of the INSERT command to enter data into a table. • INSERT command’s basic syntax: INSERT INTO tablename VALUES (value_1, value_2, … , value_n); • This version of INSERT: • adds one table row (tuple) at a time • requires a value to be specified for every column of the table; values are specified in the same order columns were defined in

3. DML – Adding Table Rows (cont) • Example: insert into customers values (1, "Julie Smith", "25 Oak Street", "Airport West"); insert into orders values (NULL, 1, 49.99, "2007-04-15"); • Notes: • The row contents are delimited by parentheses • Attribute entries are separated by commas • String and date values must be quoted (‘ or “) • Numerical entries are not enclosed in any special characters • Use NULL for: unknown values for columns that aren’t NOT NULL; auto-increment columns (filled automatically if set to null or no value provided)

4. DML – Adding Table Rows (cont) • If you want to fill in only some of the columns or if you want to specify them in a different order, list the specific columns in the INSERT statement: insert into customers (name, city) values ("Alan Wong", "Box Hill"); • Insert multiple rows into a table at once: • Each row in its own set of parentheses • Rows separated by commas insert into customers values (1, "Julie Smith", "25 Oak Street", "Airport West"), (2, "Alan Wong", "1/47 Haines Avenue", "Box Hill");

5. book_insert.sql • A script to populate the books database: • http://www.nku.edu/~frank/csc301/Examples/MySQL/book_insert.sql

6. DML – Updating Table Rows • Use the UPDATE command to modify data in a table. • UPDATE command’s syntax: UPDATE tablenameSET column_1 = expression_1 [, column_2 = expression_2 …][WHERE condition_list]; • expression = a simple value (76 or ‘Florida’), or a formula (price – 10) • condition_list = one or more conditional expressions connected by logical operators (and, or, not) • If more than one attribute is to be updated per tuple, separate modifications with commas

7. DML – Updating Table Rows (cont) • Examples: UPDATE books SET price = price * 1.1; • increase all the book prices by 10% UPDATE customers SET address = ‘250 Olsens Road’ WHERE customer_id = 4; • update a certain customer’s address – PK used to identify the customer • Notes: • The WHERE clause is optional • If a WHERE clause is not specified, all rows from the specified table will be modified.

8. DML – Deleting Table Rows • Use the DELETE command to delete data from a table. • DELETE command’s syntax: DELETE FROM tablename [WHERE condition_list ]; • Notes: • WHERE clause is optional • If a WHERE clause is not specified, all rows from the specified table will be deleted

9. DML – Deleting Table Rows (cont) • Examples: DELETE FROM customers WHERE customer_id = 4; • delete data about a customer who didn’t place orders for a long time DELETE FROM books WHERE price < 4; • delete all cheap books (none, one, or more tuples can satisfy the condition) DELETE FROM books; • delete all books; table is not deleted, but remains empty

10. DML – Listing Table Rows • Most database use is issuing queries to retrieve data from the db. • SELECT command is used to list the contents of a table (or more tables). • The simplest form (syntax) of a SELECT query is: SELECT column_list FROM tablename; • Column_list represents one or more attributes from tablename, separated by commas • Asterisk (*) can be used as a wildcard character to list all attributes for the selected rows (when column_list is *)

11. DML – Listing Table Rows (cont) • Example: SELECT name, city FROM customers;  the result contains all the rows and only the two specified columns of table customers. • The results of a query are presented in table form: • This behaves like a table (can be used in other queries for ex.), but isn’t stored as one.

12. DML – Listing Table Rows (cont) • Can limit data selected by placing conditional restrictionson the rows to be included in the output → with the WHERE clause of the SELECT statement. • Syntax: SELECT column_listFROM tablename[ WHERE condition_list ] ; • Will retrieve all the rows that match the conditions specified in the optional WHERE clause • If no rows match the specified criteria  result = an empty set of tuples (not an error!) condition_list = one or more conditional expressions connected by logical operators

13. DML – Listing Table Rows (cont) • WHERE clause example: SELECT title FROM books WHERE price > 40; • Conditional restrictions in the condition_list : column_name comparison_operator value expression comparison_operator expression expression – with columns and values (constants) as operands; • Comparisons include the usual “suspects” • =, !=, <, etc. • Also can use BETWEEN value1 AND value2 • If a data field is empty, can test with IS NULL operator • SELECT name FROM customers WHERE address IS NULL;  Finds records with no address specified

14. DML – Listing Table Rows (cont) • Can match patterns with LIKE • Pattern can be simple characters up to RE • % matches any number of characters, _ matches 1 character • Can match multiple conditions using AND and OR • Can negate a condition using NOT • Example: SELECT title FROM books WHERE price > 40 AND author like “Thomas%”;

15. DML – Listing Table Rows (cont) • Can sort results of query with ORDER BY: SELECT column_list FROM tablename [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC] …]; • Notes: • Although ORDER BY produces a sorted output, the actual table contents are unaffected by the ORDER BY clause! • ORDER BY clause must be listed last in SELECT (except for LIMIT) • Example: • SELECT title, price, author FROM books ORDER BY price, author;  all books info sorted by price and, for same price, ordered by the author default

16. DML – Joining DB Tables • The real power of a RDBMS resides in the ability to combine (join) tables – on common attributes. • A join is performed when data is retrieved from more than one table at a time. • Example: • which customers placed orders this month? •  the customers and orders tables are used to determine the answer

17. DML – Joining DB Tables (cont) • To create a natural join (= report only the pairs of rows in which the common attribute’s values match): • the tables to be joined are enumerated in the FROM clause of the SELECT command; • join conditions are specified in the WHERE clause. join condition = an equality comparison between the foreign key and the primary key of the two related tables. • If join conditions are not specified, the Cartesian product of all tables in the FROM clause is created = a row for each possible combination of rows from each of the tables listed in FROM

18. DML – Joining DB Tables (cont) • Example: SELECT orders.orderid, orders.amount, orders.date FROM customers, orders WHERE customers.name = 'Julie Smith' AND customers.customerid = orders.customerid; source tables to be joinedjoin condition • When same column name is defined in several of the joined tables • Must specify the source table of the column, in all SELECT clauses that use it: tablename.columnname • Otherwise  syntax error: ambiguous reference to an attribute.

19. DML – Joining DB Tables (cont) • All of the SELECT SQL clauses can be applied on the joined tables. • When joining 3+ tables, join conditions needs to be specified for pairs of tables. Generally, when N tables are listed in the SELECT clause, the number of join conditions is N-1. • Q: names of all customers who have placed at least one order that included a book about Java?

20. DML – Joining DB Tables (cont) • An alias can be used to identify a source table in a SELECT command. Aliases are alternative table names used to simplify commands. • Any legal table name can be used as an alias. • Syntax: FROM tablename AS alias, … • An alias is valid only in the SELECT command that defines it! • Example: SELECT o.orderid, o.amount, o.date FROM customers as c, orders as o WHERE c.name = 'Julie Smith' AND c.customerid = o.customerid;

21. DML – Grouping & Aggregating Data • SELECT can be used to perform various summaries of the data, by using one of the aggregate functions: • COUNT – the # of rows containing non-null values for a column • MINand MAX – the minimum, respectively maximum value encountered in a column • SUM – the sum of all values for a column • AVG – the arithmetic mean (average) of all values for a column • Syntax: SELECT aggregate_function([DISTINCT] column)… • Example: SELECT max(price) FROM books;

22. DML – Grouping & Aggregating Data • The aggregate functions yield only one value based on all of the values found in the table: a single maximum value, a single count, a single sum etc. • Aggregate functions can be used only in the column list of the SELECT command.

23. DML – Grouping & Aggregating Data • To compute aggregate functions based on groups of values use the GROUP BY and HAVING clauses of the SELECT SQL command. • SELECT column_list FROM table_list [ WHERE condition_list ] [GROUP BY g_column_list [HAVING g_condition_list] ] [ORDER BY column1 [ASC | DESC], …];

24. DML – Grouping & Aggregating Data • Example: how many books has each customer ordered? SELECT c.name, sum(i.quantity) as nr_items FROM customers as c, orders as o, order_items as i WHERE c.customerid = o.customerid and o.orderid = i.orderid GROUP BY c.customerid, c.name;

25. DML – Grouping & Aggregating Data • The GROUP BY clause is used in combination with aggregate functions • How it works: • The rows selected according to the SELECT, FROM and WHERE clauses are divided into groups that have the same value for all columns in g_column_list. • For each group, only one rowwill be reported in the result  • columnlist of the SELECT clause may contain only columns from g_column_list or aggregate functions applied on other columns  one value per group for each of the expressions in the SELECT clause.

26. DML – Grouping & Aggregating Data • The HAVING clause can be used only in combination with the GROUP BY clause: HAVING g_condition_list • g_condition_listspecifies qualification conditions for groups; • Only for groups that verify g_condition_list, a row will be included in the result. •  g_condition_listcan contain only expressions that have a unique value for a group = expressions formed with columns in from g_column_list or with aggregate functions applied on other columns. • NOTE: condition_list in the WHERE clause specifies qualification conditions for rows.

27. DML – Grouping & Aggregating Data • Example: how many books has each customer ordered; include only the customers that ordered 2 or more? SELECT c.name, sum(i.quantity) as nr_items FROM customers as c, orders as o, order_items as i WHERE c.customerid = o.customerid and o.orderid = i.orderid GROUP BY c.customerid, c.name HAVING nr_items >= 2;

28. DML – LIMIT clause • Non-standard clause! • Useful in Web applications • Allows to specify a range of rows to be returned, syntax: LIMIT start, end • Usually combined with ORDER BY • Example – two most expensive books: SELECT * FROM books ORDER BY price DESC LIMIT 2;