SQL LANGUAGE TUTORIAL

1. SQL LANGUAGE TUTORIAL Prof: Dr. Shu-Ching Chen TA: Haiman Tian

2. Basic Syntax of SQL Language SELECT attribute name(s) FROM table name WHERE comparison predicate (Boolean expression) GROUP BY attribute name(s) HAVING comparison predicate ORDER BY attribute name(s)

3. Create Tables

4. SQL ALTER TABLE ADD COLUMN ALTER TABLE table name ADD column_name datatype;

5. SELECT ALL records

6. Formula • Like & DISTINCT

7. SELECT ALL with LIKE operator SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;

8. SELECT ALL with LIKE operator Example: WHERE column_name LIKE 'a%'

9. SELECT ALL with LIKE operator Example: WHERE column_name LIKE ’%a'

10. SELECT specific records with conditions

11. SELECT IN You use the IN operator in the WHERE clause to check if a value matches any value in a list of values. The syntax of the IN operator is as follows: SELECT column1, column2, … FROM table_name WHERE columnN IN (value1, value2, …); The expression returns true if the value matches any value in the list i.e., value1, value2, etc. The list of values is not limited to a list of numbers or strings but also a result set of a SELECT statement as shown in the following query: SELECT column1, column2, … FROM table_name WHERE columnN IN (SELECT value FROM tbl_name);

12. SELECT NOTIN You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list. SELECT column1, column2, … FROM table_name WHERE columnN NOTIN (value1, value2, …);

13. SELECT ANY (1) • The ANY operator must be preceded by one of the following operator =,<=,>,<,>,<> • The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false. • The subquery must return exactly one column. SELECT column1, column2, … FROM table_name WHERE columnN operator ANY (subquery);

14. SELECT ANY (2) • The = ANY is equivalent to IN operator. • Note that the <>ANY operator is different from NOT IN. • x<>ANY(a,b,c) is equivalent to • x<>a OR x<>b OR x<>c

15. SELECT from two TABLES (1) Products Categories

16. SELECT from two TABLES (2) • Query: List the name(s) of all products that are categorized as beverages.

17. SQL ORDER BY

18. SQL ORDER BY DESC

19. ASCII Value (1)

20. ASCII Value (2) • Example select grade, ascii(grade) from enroll order by ascii(grade)

21. SQL IN OPERATOR AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum

22. SQL Alias

23. SQL Joins • INNER JOIN: Return rows when there is at least one match in both tables • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table SELECT column_name(s) FROM table_name1 JOIN_TYPES table_name2 ON table_name1.column_name = table_name2.column_name

24. SQL INNER JOIN

25. SQL LEFT JOIN

26. SQL RIGHT JOIN

27. SQL UPDATE FIELD UPDATE table name SET column1 = value, column2 = value2,… WHERE comparison predicate (Boolean expression)

28. SQL UPDATE Join (1) Sometimes, you need to update data of a table based on values in another table. In this case, you can use the PostgreSQL UPDATE join syntax as follows: UPDATE A SET A.c1 = expression FROM B WHERE A.c2 = B.c2

29. SQL UPDATE Join (2) product_segment product • The product_segment table has the discount column that stores the discount percentage based on a specific segment. For example, grand luxury products have 5% discount while luxury and mass products have 6% and 10% discount respectively. • The product table has the foreign key column segment_id that links to the id of the segment table.

30. SQL UPDATE Join (3) product_segment product • The product_segment table has the discount column that stores the discount percentage based on a specific segment. For example, grand luxury products have 5% discount while luxury and mass products have 6% and 10% discount respectively. • The product table has the foreign key column segment_id that links to the id of the segment table.

31. SQL UPDATE Join (4) UPDATE product SETnet_price = price – price * discount FROM product_segment WHERE product.segment_id = product_segment.id; This statement joins the product table to the product_segment table. If there is a match in both tables, it gets the discount from the product_segment table, calculates the net price based on the following formula, and updates the net_pricecolumn. net_price = price – price * discount;

32. SQL UPDATE Join (5)

33. SQL GROUP BY Statement

34. SELECT GROUP BY (1) Products Categories

35. SELECT GROUP BY (2) • Query: Count the number of products in each category.

36. SELECT INNER JOIN • Query: Count the number of products in each category (using inner join).

37. SELECT GROUP BY (3) • Query: List the category names that contain greater-than-or-equal to 3 products (nesting the previous query)

38. SQL HAVING Clause

39. SELECT HAVING (1) Products Categories

40. SELECT HAVING (2) • Query: List the category names that contain greater-than-or-equal to 3 products (without nesting query)

41. SQL DATE Type

42. SQL DATE Type (1)

43. SQL DATE Type (2)

44. SQL DATE Type (3) • date_trunc: function truncates a TIMESTAMP value based on a specified date part (e.g. hour, week, month) date_trunc(‘datepart’, field)

45. SQL DATE Type (4) • date() function converts a string literal to a date value. The following is the syntax: date(‘DD/MM/YYYY’)

46. SQL DATE Type (5)

47. PosgreSQL Arrays (1)

48. PosgreSQL Arrays (2)

49. PosgreSQL Arrays (3)

50. PosgreSQL Composite Types