Database Systems – SQL

1. SQL SELECT - AGGREGATION FUNCTIONS It is far more efficient to aggregate data at the database server than to pull the raw data over the network and compute it in an application program. The following are a few, but not all of the aggregation functions. AVG MIN MAX SUM COUNT While they are traditionally used with the GROUP BY clause, they can be used without it. So first we will see how to use them before introducing the GROUP BY clause. If we want to determine the total number of items sold from a sales table, we could use the following query: SELECT SUM(quantity) AS total_items_sold FROM sales; Always rename your aggregation to a readable and relevant name. Database Systems – SQL

2. SQL SELECT - AGGREGATION FUNCTIONS All aggregate functions other than COUNT ignore values that are NULL. COUNT tallies the record whether the value is NULL or not. GROUP BY While being able to aggregate data across an entire table is useful, the true power of the aggregation functions is achieved by combining them in collections and aggregating each collection independently. This is accomplished with a GROUP BY clause which groups records and aggregates fields using the aggregation functions. The syntax for a GROUP BY clause is as follows: SELECT field-list-1 FROM tablename GROUP BY field-list-2; Each field in field-list-1 must either have the field name listed in field-list-2 or have a aggregation function applied to it when it is returned in the result set. Database Systems – SQL

3. SQL SELECT - AGGREGATION FUNCTIONS The syntax for a GROUP BY clause is as follows: SELECT field-list-1 FROM tablename GROUP BY field-list-2; For example, to select the total assets for all banks by city, you could use the following query: SELECT city, sum(assests) AS total_assets FROM branches GROUP BY city; Database Systems – SQL result set branches table • Branch_name is not included as that is the field that has no relation to the aggregated data. • City is listed without an aggregation function as it is listed in the GROUP BY clause. • Assets can only be listed by using an aggregation function and is renamed.

4. SQL SELECT - AGGREGATION FUNCTIONS We can also add an ordering to the result by using an ORDER BY clause after the GROUP BY clause. For example, to select the total assets for all banks by city and order the results by total_assets, you could use the following query: SELECT city, sum(assests) as total_assets FROM branches GROUP BY city ORDER BY total_assets; Database Systems – SQL result set branches table

5. SQL SELECT - AGGREGATION FUNCTIONS GROUP BY clauses can be added to the result of a JOIN. Observe the query to join the websites table with the hit_count table to return the total number of hit counts per website. SELECT website, SUM(hit_count) as total_hit_count FROM (websites INNER JOIN hit_count ON websites.id_website = hit_counts.id_website) GROUP BY website; Database Systems – SQL websites table(partial view) hit_counts table(partial view) result set

6. SQL SELECT - AGGREGATION FUNCTIONS COUNT(expr) - Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. Therefore, the following two statements could result in a different count if there are null values in the website field. SELECT COUNT(*) AS record_count FROM websites; SELECT COUNT(website) AS record_count FROM websites; Database Systems – SQL

7. SQL SELECT – HAVING CLAUSE It is very useful to be able to filter results based upon the results of the aggregated fields in the result record set. A HAVING clause accomplishes this and should not be confused with the WHERE clause which filters based upon the condition of rows in the table. A HAVING clause is used similarly to the WHERE clause the only difference in syntax is that the fields compared in the HAVING clause are the results of aggregation. Observe adding a HAVING clause to the previous query so that it now only returns the results of websites with a total hit count greater than 1,500. SELECT website, SUM(hit_count) As total_hit_count FROM (websites INNER JOIN hit_count ON websites.id_website = hit_count.id_website) GROUP BY websites.website HAVING total_hit_count > 1500; Database Systems – SQL Notice the racewalk.com record was removed from the result set. result set

8. SQL SELECT – HAVING CLAUSE In contrast, if we applied the same logic, but used a WHERE clause and comparing the hit_count field, the results would be quite different. SELECT website, SUM(hit_count) as total_hit_count FROM (websites INNER JOIN hit_count ON websites.id_website = hit_count.id_website) WHERE hit_count > 1500 GROUP BY websites.website; Database Systems – SQL websites table(partial view) hit_count table(partial view) result set With the WHERE clause used, only records containing a hit_count greater than 1,500, which is only one record.

9. SQL SELECT - UNION Sometimes it is necessary to combine the results of multiple queries together into a single result set. As long as the result sets domains are the same, they can be combined using the UNION command. The syntax follows: SELECT …. UNION SELECT … Imagine you have two tables, a non-sale and discount, that stores prices. Then imagine you wish to combine them into a single result set. The following query will accomplish this: SELECT product_name, retail_price AS price FROM non_sale_items UNION SELECT product_name, sales_price AS price FROM discount_items; By default, duplicate rows are removed from the result set. UNION ALL allows duplicate rows to be included. Database Systems – SQL

10. SQL SELECT - SUBQUERIES SQL allows you to nest one query within another. The most common subqueries are IN and NOT IN. I will show you these queries although you already know how to perform them using LEFT OUTER JOINs. While using an IN or NOT IN query is easier to read than a LEFT OUTER JOIN, they usually have poorer performance as the JOIN version relies on indexes with a properly designed table. The IN or NOT IN query relies on temporary results, which may or may not be optimized. Observe how we can combine queries to indicate the websites that have a daily hit count of at least 1,000 on any day. SELECT website FROM websites WHERE id_website IN (SELECT id_website FROM hit_count WHERE hit_count >= 1000); Database Systems – SQL

11. SQL SELECT - SUBQUERIES Observe how we can combine queries to indicate the websites that have a daily hit count of at least 1,000 on any day. SELECT website FROM websites WHERE id_website IN (SELECT ID_website FROM hit_count WHERE hit_count >= 1000); Database Systems – SQL websites table (partial view) result set of sub query hit_count table(partial view, records selected from the first query are highlighted in yellow) result set The sub query returns two records with only the id of each website, then the outer query selects the names of the websites corresponding to those IDs.

12. SQL SELECT - SUBQUERIES Observe how we can combine queries to indicate the websites that do not have a daily hit count of at least 1,000 on any day. SELECT website FROM websites WHERE id_website NOT IN (SELECT id_website FROM hit_count WHERE hit_count >= 1000); Database Systems – SQL result set of sub query websites table (partial view) hit_count table(partial view, records selected from the first query are highlighted in yellow) result set The sub query returns two records with only the id of each website, then the outer query selects the names of the websites not corresponding to those IDs.

13. SQL SELECT - SUBQUERIES Observe how we can combine queries to indicate the websites that do not have a daily hit count of at least 1,000 on any day. SELECT website FROM websites WHERE id_website NOT IN (SELECT id_website FROM hit_count WHERE hit_count > 1000); Database Systems – SQL result set of sub query websites table (partial view) hit_count table(partial view, records selected from the first query are highlighted in yellow) result set The sub query returns two records with only the id of each website, then the outer query selects the names of the websites not corresponding to those IDs.

14. SQL SELECT - SUBQUERIES Are the following two queries equivalent? SELECT website FROM websites WHERE id_website NOT IN (SELECT id_website FROM hit_count WHERE hit_count > 1000); and SELECT website FROM websites WHERE id_website IN (SELECT id_website FROM hit_count WHERE hit_count <= 1000); Database Systems – SQL

15. SQL SELECT - SUBQUERIES Are the following two queries equivalent? SELECT website FROM websites WHERE id_website IN (SELECT id_website FROM hit_count WHERE hit_count <= 1000); Database Systems – SQL result set of sub query websites table (partial view) hit_count table(partial view, records selected from the first query are highlighted in yellow) result set So, clearly they are not equivalent.

16. SQL SELECT - SUBQUERIES A better use of a sub-query is to compare the results of a query returning a single value and using that value as a scalar in a WHERE clause of another query. Let’s select the website(s) names that have the single greatest hit count on a single day. This requires data from both the website table and hit_counts table and requires a subquery to determine the largest hit count on a single day. SELECT website FROM (websites AS w INNER JOIN hit_count AS hc ON w.id_website = hc.id_website) WHERE hc.hit_count = (SELECT MAX(hit_count) as max_hit_count FROM hit_count); Database Systems – SQL result set websites table (partial view) hit_count table(partial view, records selected from the sub query are highlighted in yellow)

17. SQL SELECT - SUBQUERIES Another use of a subquery is when you want to aggregate an aggregated field. Suppose you wish to determine the average of the total hit counts for a website. You might want to do the following: SELECT AVG(SUM(hit_count)) FROM hit_counts GROUP BY id_website; However, you can not place one aggregate function within another (at least on most db severs). You must use a subquery as follows: SELECT AVG(sum_hit_count) FROM (SELECT SUM(hit_count) AS sum_hit_count FROM hit_count GROUP BY id_website); Database Systems – SQL

18. SQL SELECT - SUBQUERIES Here’s where things get interesting. Now imagine you wish to select the id’s of websites that have a total hit count greater than the average hit count of all websites. Note, that if one website has fewer days than another it’s hit count should not be proportionally larger than the average. First, you must compute the sums of each websites hit count. Second, you must compute the average hit count for each website. Finally, you must select those web sites that have a hit count higher than the average computed in the second part. The query follows: SELECT id_website FROM hit_count GROUP BY id_website HAVING SUM(hit_count) > (SELECT AVG(sum_hit_count) FROM (SELECT SUM(hit_count) AS sum_hit_count FROM hit_count GROUP BY id_website)); Database Systems – SQL

19. SQL SELECT - SUBQUERIES SELECT id_website FROM hit_count GROUP BY id_website HAVING SUM(hit_count) > (SELECT AVG(sum_hit_count) AS avg_sum_hit_count FROM (SELECT SUM(hit_count) AS sum_hit_count FROM hit_count GROUP BY id_website)); Imagine you had the following data: Database Systems – SQL Results of 2nd SubQuery Results of 1st SubQuery Final Results hit_count table

20. SQL SELECT – MULTI VALUE SUB SELECTS SELECT fieldlist FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2); SELECT fieldlist FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2); The queries here are both TRUE if table t2 has a row where column1 = 1 and column2 = 2. NOTE, the sub query must return a single row or you get an error. The expressions (1,2) and ROW(1,2) are sometimes called row constructors. The two are equivalent. They are legal in other contexts as well. SELECT fieldlist FROM t1 WHERE (column1,column2) = (1,1); SELECT fieldlist FROM t1 WHERE column1 = 1 AND column2 = 1; The normal use of row constructors is for comparisons with subqueries that return two or more columns. For example, the following query answers the request, “find all rows in table t1 that also exist in table t2”: SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2); Database Systems – SQL

21. Database Systems – SQL SQL DELETE If you wish to remove records from a table, use the DELETE query. It is very similar to a SELECT query other than the first keyword. The syntax for a single table delete is as follows: DELETE FROM tablename WHERE predicate; NOTE IT APPEARS THAT DELETE IS CASE SENSITIVE ON THE TABLE NAME. NOT SURE WHY. If you do not specify a WHERE clause, all records are removed from the table. However, removing all the records without removing the structure is better accomplished using the TRUNCATE TABLE tablename command. To delete the hit counts with a website id of 1 from the hit_count table use the following query: DELETE FROM hit_count WHERE id_website = 1; Here are a few other examples of DELETE statements: DELETE FROM account WHERE branch=“Northeast Philly”; DELETE FROM ACCOUNT WHERE branch IN (SELECT branch FROM branches WHERE city = “Brooklyn”); DELETE FROM account WHERE balance < (SELECT AVG (balance) FROM account);

22. Database Systems – SQL SQL DELETE A simple form of a multi table delete can be achieved using the IN operator Observe how to delete all records in the hit count table that contain the id of the website for www.racewalk.com: DELETE FROM hit_count WHERE id_website IN (SELECT id_website FROM websites WHERE website = “www.racewalk.com”; Observe how to delete all records in the hit count table that contain hit counts lower than the average hit count: DELETE FROM hit_count WHERE hit_count < (SELECT AVG (hit_count) FROM hit_count);

23. Database Systems – SQL MULTI TABLE DELETE QUERY Sometimes you wish to delete records from one or more tables with a query spanning multiple tables. DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching: Or: These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2. The preceding examples show inner joins that use the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

24. Database Systems – SQL test1 table test2 table MULTI TABLE DELETE QUERY Assume you have the following two tables: What do you think the tables look like after the following query is executed: DELETE FROM test1 USING test1, test2 WHERE test1.C1=test2.C1;

25. Database Systems – SQL test1 table test2 table MULTI TABLE DELETE QUERY DELETE FROM test1 USING test1, test2 WHERE test1.C1=test2.C1;

26. Database Systems – SQL test1 table test2 table MULTI TABLE DELETE QUERY Assume you have the following two tables: What do you think the tables look like after the following query is executed: DELETE FROM test1, test2 USING test1, test2 WHERE test1.C1=test2.C1;

27. Database Systems – SQL test1 table test2 table MULTI TABLE DELETE QUERY DELETE FROM test1, test2 USING test1, test2 WHERE test1.C1=test2.C1;

28. Database Systems – SQL SQL INSERT To insert data into a table using explicitly specified values use the following syntax: INSERT INTO tablename (columnlist) VALUES (valuelist); Therefore, to insert values into the websites table, use the following command: INSERT INTO websites (website, organization, first_year, category) VALUES ("www.yankees.com", "NY Yankees", 1990, "Sports"); The id_website field is filled in automatically because it has an auto increment qualifier. It is allowable to use the following format, but I do not recommend it as the table structure may change and then your code may break: INSERT INTO tablename VALUES (valuelist); Therefore, to insert values into the websites table, use the following command: INSERT INTO websites VALUES ("www.yankees.com", "NY Yankees", 1990, "Sports");

29. Database Systems – SQL SQL INSERT - FROM OTHER TABLES To insert data into a table using other tables as a source use the following syntax: INSERT INTO tablename (columnlist) query; Therefore, to insert values into the websites table from a table called old_websites, use the following command: INSERT INTO websites (website, organization, first_year, category) SELECT web_site, organ, f_year, cat from old_websites; Notice that I purposely picked different field names. Without the columns listed I believe, but am not sure, that it will work as long as the fields selected to be inserted are type compatible with those defined in the same order of the table and all fields of the insertion table are provided.

30. Database Systems – SQL SQL UPDATE – SINGLE TABLE To update data within a table using explicitly specified values use the following syntax: UPDATE tablename SET COL=Val WHERE condition;

31. Database Systems – SQL SQL UPDATE – SINGLE TABLE Individual values can be changed by using the UPDATE command The syntax for updating values explicitly in a single table as follows: UPDATE tablename SET field = value; If we wanted to update the retail price of all products in the products table by 5% we could use the following command: UPDATE products SET retail_price = retail_price * 1.05; In addition, if we want to limit the update to only certain rows, you could use the following syntax: UPDATE tablename SET field = value WHERE condition; If we wanted to update the retail_price of all products in the products table by 5% for all prices that are less than $29.99 we could use the following command: UPDATE products SET retail_price = retail_price * 1.05 WHERE retail_price < 29.99;

32. Database Systems – SQL SQL UPDATE – SINGLE TABLE What if we wanted to update all prices of products under $29.99 by 6% and all prices greater than or equal to $29.99 by 5%. You could issue two UPDATE commands as follows: UPDATE products SET retail_price = retail_price * 1.06 WHERE retail_price < 29.99; UPDATE products SET retail_price = retail_price * 1.05 WHERE retail_price >= 29.99; Will this work?

33. Database Systems – SQL SQL UPDATE – SINGLE TABLE What if we wanted to update all prices of products under $29.99 by 6% and all prices greater than or equal to $29.99 by 5%. You could issue two UPDATE commands as follows: UPDATE products SET retail_price = retail_price * 1.06 WHERE retail_price < 29.99; UPDATE products SET retail_price = retail_price * 1.05 WHERE retail_price >= 29.99; Will this work? It will not, because values within 5% of 29.99 will be updated twice. So we could update them in the reverse order or use a CASE statement within the UPDATE as follows: UPDATE products SET retail_price = CASE WHEN retail_price >= 29.99 then retail_price * 1.05 ELSE retail_price * 1.06 END;

34. Database Systems – SQL SQL UPDATE – WITH QUERY TABLE When issuing an update command you can use a sub query in the WHERE clause just as you would in a SELECT query. Observe how you can update the retail_price in the products table by 5% wherever the retail price is less than the average retail price: UPDATE products SET retail_price = retail_price * 1.05 WHERE retail_price < (SELECT AVG(retail_price));

35. Database Systems – SQL SQL UPDATE – WITH MULTIPLE TABLES Similar to updating a single table, you can use multiple tables and update one table from another. UPDATE table references SET col1=value1 WHERE condition; Imagine you wanted to update a the products table with a list of sale prices. The sales prices could be stored in another table called discount_products and then you could update the products table using the following: UPDATE products, discount_products SET products.retail_price = discount_products.sale_price WHERE products.id_product = discount_products.id_product;

36. Database Systems – SQL SQL - CREATE TABLE Command CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_option ...] There are some restrictions on the characters that may appear in identifiers: No identifier can contain ASCII 0 (0x00) or a byte with a value of 255. The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible. Database, table, and column names should not end with space characters. Database names cannot contain "/", "\", ".", or characters that are not allowed in a directory name. Table names cannot contain "/", "\", ".", or characters that are not allowed in a filename. The length of the identifier is in bytes, not characters. If you use multi-byte characters in your identifier names, then the maximum length will depend on the byte count of all the characters used.

37. Database Systems – SQL SQL CREATE TABLE CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_namedata_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

38. Database Systems – SQL SQL CREATE TABLE Numeric Data Types Character Types

39. Database Systems – SQL SQL CREATE TABLE Time/Date Data Types

40. Database Systems – SQL SQL CREATE TABLE Simple table declaration example: CREATE TABLE websites (website CHAR (50), organization CHAR (30), first_year SMALLINT, category CHAR (20));

41. Database Systems – SQL SQL CREATE TABLE - PRIMARY KEYS A primary key is a unique identifier for the table. When you declare a primary key, the database will not allow null values in the field nor will it allow duplicates to be inserted. To add a primary key to the database, just add the key words PRIMARY KEY and then a list of the fields you want as primary keys. This can be a single field or a series of keys (known as a compound primary key). The following is the declaration of the websites table with the website field defined as a primary key: CREATE TABLE websites (website CHAR (50), organization CHAR (30), first_year SMALLINT, category CHAR (20), PRIMARY KEY (website));

42. Database Systems – SQL SQL CREATE TABLE - PRIMARY KEYS The following is the declaration of a depositor table with the customer_name and account_number fields defined as a compound primary key: CREATE TABLE depositor (customer_name CHAR (20), account_number CHAR (10), PRIMARY KEY (customer_name, account_number));

43. Database Systems – SQL SQL CREATE TABLE - PRIMARY KEYS While it is OK to make the primary key a field like website, it is better to use a unique number to represent the record so that it can be referenced from other tables. The best way to do this is to create an ID field that contains a non null integer that automatically increments so that you are ensured a unique number of each record inserted into the table. Therefore, observe the new websites table definition: CREATE TABLE websites (id_website SERIAL, website CHAR(50), organization CHAR(30), first_year SMALLINT, category CHAR (20), PRIMARY KEY (id_website)); Note many texts use ID fields named just ID. I find this confusing as you can’t identify what the ID stands for from the name. Always use ID_identifier as the field name.

44. Database Systems – SQL SQL CREATE TABLE - INDEXES One complication of using a unique ID as the primary key is that you have extra work to ensure that fields like website, which should not be repeated, do not have duplicates (Other than NULL, if NULL is allowed). To combat this, we must create a unique index on the website field. The syntax for creating unique index is: CREATE UNIQUE INDEX index_name ON TABLE NAME (field list); Therefore, to create a unique index on the websites table on the website field: CREATE UNIQUE INDEX website_index ON websites (website);

45. Database Systems – SQL SQL CREATE TABLE – INDEXES Indexes are not only created to prevent duplicates, but more commonly are created to speed searches. If you wish to create an index that is not unique, use the following syntax: INDEX index_name (field list) If we wished to add an index to the category field of the websites table, we would define the index as follows: CREATE INDEX category_indexON websites (category);

46. Database Systems – SQL SQL CREATE TABLE – INDEXES The decision of when to create an index requires some knowledge of how the data in the table is going to be accessed. As a general rule of thumb, create each table with a primary key. If a row in that table is referenced from another table, create a unique ID field to represent the row and assign that as the primary key. If a field in a table is commonly searched on, create an index on the field. If a field in a table should not contain duplicates, create an index on the field. So why not create indexes on every field?

47. Database Systems – SQL SQL CREATE TABLE – INDEXES So why not create indexes on every field? Indexing fields speeds searches when the index field is included in the WHERE clause. However, when a row is inserted, each field that is indexed will slow the insertion of the record. This is why many websites/applications do not show inserted values immediately. The same issue exists for deletes and updates. An index, like a primary key may be created on a single field or multiple fields.

48. Database Systems – SQL SQL CREATE TABLE – FOREIGN KEYS Whenever a field is defined in another table and referenced in another table, a good database developer creates a foreign key relationship from the child table to the parent table. This forces the parent value to exist, before it may be entered into the child table. The syntax for the Foreign Key constraint is as follows: FOREIGN KEY (child table field) REFERENCES parent table(field list)

49. Database Systems – SQL SQL CREATE TABLE – FOREIGN KEYS Let’s create the hitcounts table that references the id_website field created in the websites table. CREATE TABLE hitcounts (id_website INTEGER , hit_date DATE, hit_count INTEGER, PRIMARY KEY (id_website, hit_date), FOREIGN KEY (id_website) REFERENCES websites(id_website)); NOTE, the table being referenced must already be created in order for the foreign key to be allowed. Let’s also create the customer table: CREATE TABLE Customer (id_customer SERIAL, first_name CHAR(20), last_name CHAR(20), primary key (id_customer)); CREATE UNIQUE INDEX name_indexON Customer (last_name, first_name);

50. Database Systems – SQL SQL CREATE TABLE – FOREIGN KEYS Now let’s define a products table although there is nothing particularly new about it, we will need it for another table we wish to create. We’ll keep the products table simple and only record the item name, price, and an identifier for it. CREATE TABLE products (id_product SERIAL, product_name CHAR(40), retail_price DECIMAL, primary key (id_product)); CREATE UNIQUE INDEX product_name_indexON products (product_name); Next, we will create a sales table that contains the website that the sale was purchased from, the customer, the product, quantity bought, and the date. What should the primary key be? What should the foreign key be? What, if any, indexes should you create?