Lecture 8 : SQL-Based Database Queries

1. Lecture 8: SQL-Based Database Queries November 7th

2. Introduction • 2 types of query languages: • Form-based query language: uses a GUI panel for the creation of the query e.g., MS Access • Command-based query language: queries are written as text commands e.g., SQL

3. Requesting Information from a Database • To request information stored in a RDBMS, you use a query language, e.g.: SELECT first_name, last_name, title, loc_num FROM books_info

4. What is SQL? • SQL is a query language • It is the standard language for RDBMSs • The programmer tells the computer that the requested data must meet specified criteria

5. Defining Components • Components are comprised of related statements • Statements are instructions that you give to a relational database

6. SQL Statement Categories • Data Query Language (DQL) – Statements that query data but change nothing – SELECT statement • Data Manipulation Language (DML) – Statements that modify data values but not data structures (tables, views, etc.) – INSERT, UPDATE, DELETE statements

7. SQL Statement Categories • Data Definition Language (DDL) – Statements that add/change/delete database objects, but not the data within them (the containers but not the contents) – CREATE, ALTER, DROP statements • Data Control Language (DCL) – Statements that manage privileges users have regarding database objects – GRANT, REVOKE statements

8. Querying Tables using SQL • Retrieving data • Ordering query results • Limiting query results • Grouping query results

9. SELECT Statement Components • SELECT clause • Identifies the columns to be displayed • FROM clause • Identifies the table that contains the columns specified in the SELECT clause

10. Sample SELECT Statement SELECT tag_number, file_number FROM new_files;

11. Write SQL …. • Provide a list with the id numbers and names of all products available at your company • Use the SELECT statement …. SELECT id, name FROM products Table attributes Table

12. Form-Based …. SELECT id, name, owner_id, birth_date FROM horse;

13. DISTINCT Keyword • DISTINCT keyword indicates that duplicate rows are not to be displayed • Used in the SELECT statement …. SELECT DISTINCT name FROM ca_dept;

14. DISTINCT Keyword • With DISTINCT NAME Administration Finance Sales … • Without DISTINCT NAME Administration Finance Finance Finance Sales …

15. Column Aliases • The heading in a query result is typically derived from the name of selected column • Adding arithmetic operators to a SQL statement can make columns ambiguous or meaningless • A column alias is used to avoid this situation

16. Column Aliases SELECT sales_person, monthly_sales*3 FROM ca_emp; SALES_PERSON David Mary Ron ____________ 30 90 60

17. Using AS Keyword SELECT sales_person, monthly_sales*3 AS commission FROM ca_emp SALES_PERSON David Mary Ron COMMISSION 30 90 60 **By default, column aliases are displayed in uppercase, unless in quotation marks

18. Form-based …. SELECT id, name, …, CCur(current_balance…) AS net_balance FROM customer;

19. Concatenation Operator • At times, it is necessary to join 2 columns, arithmetic expressions, or constant values to create a single output expression • One method of joining columns is by using the concatenation operator • || represents a concatenation operator • Columns on either side of the concatenation operator are joined to make a single output column

20. Concatenation Operator SELECT first_name || last_name AS staff FROM records; STAFF DavidWong MaryPeterson RonFloyd

21. Write the SQL Code for this Query SELECT id, name, …, CCur(current_balance…) AS net_balance FROM customer;

22. ORDER BY Clause • SQL does not define a specific order for the rows it displays in query results • To display rows in a particular order, use the ORDER BY clause • This clause in placed at the end of the SQL statement • Sorts are performed in ascending order by defaults

23. ORDER BY Clause SELECT id, city, state, country FROM s_warehouse ORDER BY country; ID 201 10501 301 101 401 City Sao Paolo Bratislava Lagos Seattle Hong Kong State WA Country Brazil Czechoslovakia Nigeria USA

24. Form-Based ….. SELECT id, name, dam, sire FROM horse_linage ORDER BY name;

25. DESC Keyword • The rows displayed in a query result can be changed from ascending order to descending order using the DESC keyword with the ORDER BY clause • DESC keyword is placed after the column name or position you want reversed

26. ORDER BY with DESC (Column) SELECT first_name, last_name, dept_id FROM records ORDER BY dept_id DESC; FIRST_NAME David Mary Ron LAST_NAME Wong Peterson Floyd DEPT_ID 4 2 1

27. ORDER BY with DESC (Position) SELECT first_name, last_name, dept_id FROM records ORDER BY 3 DESC; FIRST_NAME David Mary Ron LAST_NAME Wong Peterson Floyd DEPT_ID 4 2 1

28. Form-Based ….. SELECT id, name, owner_id, birth_date, gender FROM horse ORDER BY birth_date DESC;

29. Write the SQL Code for this Query SELECT id, name, dam, sire FROM horse_linage ORDER BY name;

30. WHERE Clause • Sometimes it is necessary to limit the rows displayed when retrieving data • One method of limiting query results is using the WHERE clause • Enables the addition of search criteria to SQL statements

31. WHERE Clause SELECT author, title, dec_num FROM book_info WHERE author=“Wong”; SELECT author, title, dec_num FROM book_info WHERE dec_num=.041;

32. Relationship Operators in a WHERE Clause • = equal to • > greater than • < less than • >= greater than or equal to • <= less than or equal to • <> not equal to

33. Form-Based …. SELECT id, name, owner_id, birth_date, gender FROM horse WHERE gender = ‘M’ ORDER BY birth_date DESC;

34. SQL Operators in a WHERE Clause • BETWEEN • IN • LIKE • IS NULL

35. SQL Operators in a WHERE Clause - BETWEEN • Displays rows based on a range of values SELECT art_name, title, sales_total FROM gallery WHERE sales_total BETWEEN 100000 and 300000;

36. SQL Operators in a WHERE Clause - IN • Tests for values in a list SELECT id, last_name, dept_num FROM emp WHERE dept_num IN (5,6);

37. Form-Based ….. SELECT id, name, owner_id FROM horse WHERE owner_id IN (‘00011’, ‘0850’, ‘02001’, ‘02003’);

38. SQL Operators in a WHERE Clause - LIKE • Is used for searches where the exact value for the condition is uncertain • Constructed using the • * any sequence of 0 or more characters • _ any single character SELECT titles FROM book_info WHERE title LIKE ‘S*’;

39. Form-Based …. SELECT track_id, race_number, ………. FROM race_result WHERE horse_id LIKE ‘KY*’ AND …..;

40. SQL Operators in a WHERE Clause – IS NULL • Null is tested for with the IS NULL operator • Tests for a value that is unavailable, unassigned, unknown, or inapplicable

41. AND and OR Operators SELECT first_name, last_name, title, loc_num FROM books_info WHERE last_name = ‘smith’ AND loc_num = 25; SELECT first_name, last_name, title, loc_num FROM books_info WHERE last_name = ‘smith’ OR loc_num = 25;

42. Form-Based …… SELECT id, name, owner_id, birth_date, gender FROM horse WHERE gender = ‘M’ OR birth_date > ‘1/1/85’ ORDER BY birth_date DESC;

43. Write the SQL Code for this Query SELECT id, name, owner_id, birth_date, gender FROM horse WHERE gender = ‘M’ ORDER BY birth_date DESC;

44. GROUP BY Clause • Rows in a table can be divided into smaller groups using the GROUP BY clause • Creating these smaller groups enables you to use the group functions to return summary information for each group

45. GROUP BY Clause SELECT title, dept_id FROM s_emp GROUP BY title; TITLE Sales Representative Sales Representative Stock Clerk DEPT_ID 34 35 34

46. Form-Based SELECT invoice_id, quantity*unit_price AS amount FROM lineitem GROUP BY invoice_id;

47. HAVING Clause • To display specific groups in a query result, use the HAVING clause • Groups matching a criteria are displayed

48. HAVING Clause SELECT dept_id FROM s_emp GROUP BY dept_id HAVING SUM(salary) > 4000; DEPT_ID 41 50

49. Querying Tables • Retrieving data – SELECT.. FROM, DISTINCT, AS, || • Ordering query results – ORDER BY, DESC • Limiting query results – WHERE, BETWEEN, IN, LIKE, IS NULL, AND, OR • Grouping query results – GROUP BY, HAVING

50. Write the SQL Code for this Query