1 / 62

SQL Database Programming Lesson Review

Learn about SQL, structured query language, and how to create, modify, and query tables in a database. Understand the basic structure of SQL queries and learn about comparison operators, aliases, concatenation, logical conditions, and functions. Practice writing SQL queries with examples.

gsparling
Download Presentation

SQL Database Programming Lesson Review

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Structured Query Language (結構化查詢語言) SQL

  2. CREATE TABLE … ALTER TABLE… ADD/DROP/MODIFY (…) DESC … DROP TABLE … RENAME … TO … INSERT INTO … VALUES (…) SELECT * FROM … Database Programming Lesson Review:

  3. UPDATE TableName SET Column1 = NewValue1, Column2 = NewValue2 WHERE Condition(s) The WHERE clause is optional. Updating Data in a Table

  4. DELETE FROM TableName WHERE Condition(s) The WHERE clause is optional. Delete Records from a Table

  5. What is SQL? • Structured Query Language(SQL) (結構化查詢語言) Database Management System • Most ___________________________ (DBMS) support SQL. e.g. Oracle • When a user wants to get some information from a database file, he can issue a _______. query • A query is a user–request to retrieve data or information with a certain ___________. condition

  6. Basic structure of an SQL query SELECT * / Column1, Column2, … FROM TableNames

  7. Retrieve data with specified selection criteria SELECTColumn1, Column2, … FROM TableNames WHERE Conditions

  8. Comparison operators Examples WHERE event_date = ' 01-JAN-04' WHERE rental_fee >=2000 WHERE cd_title = ' White Rose' You may also see the <> (not equal to) symbol written as != or ^ = In the example shown from the DJ on Demand database, which rows will be selected? Will salaries of 3000 be included in the results set? SELECT last_name, salary FROM employees WHERE salary <= 3000

  9. Aliases – Column Headings table

  10. Concatenation – Link columns

  11. Retrieve value(s) without Duplication SELECT DISTINCT Column1, Column2, … FROM TableNames WHERE Conditions

  12. Comparison Operators BETWEEN…AND SELECT title, year FROM d_cds WHERE year BETWEEN '1999' AND '2001‘

  13. IN SELECT title, type_code FROM d_songs WHERE type_code IN ( 77, 12 )

  14. LIKE The % symbol is used to represent any sequence of zero or more characters. The underscore (_ ) symbol is used to represent a single character. In the example shown below, all employees with last names beginning with any letter followed by an "o" and then followed by any other number of letters will be returned. SELECT last_name FROM employees WHERE last_name LIKE '_o%' Which of the following last names could have been returned from the above query? 1. Sommersmith 2. Oog 3. Fong 4. Mo If you said 1, 2, 3, and 4, you're correct!

  15. IS NULL, IS NOT NULL The IS NULL condition tests for unavailable, unassigned, or unknown data. IS NOT NULL tests for data that is present in the database. In this example, the WHERE clause is written to retrieve all the last names and manager IDs of those employees who do not have a manager. SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL Read the following and explain what you expect will be returned: SELECT first_name, last_name, auth_expense_amt FROM d_partners WHERE auth_expense_amt IS NOT NULL

  16. LOGICAL CONDITIONS Logical conditions combine the result of two component conditions to produce a single result based on them. For example, to attend a rock concert, you need to buy a ticket AND have transportation to get there. If both conditions are met, you go to the concert. AND In the query below, the results returned will be rows that satisfy BOTH conditions specified in the WHERE clause. SELECT id, title, duration, type_code FROM d_songs WHERE id > 40 AND type_code = 77

  17. OR If the WHERE clause uses the OR condition, the results returned from a query will be rows that satisfy either one of the OR conditions. In other words, all rows returned have an ID greater than 40 OR they have a type_code equal to 77. SELECT id, title, duration, type_code FROM d_songs WHERE id > 40 OR type_code = 77

  18. NOT SELECT title, type_code FROM d_songs WHERE type_code NOT IN 77

  19. ORDER BY SELECT title, year FROM d_cds ORDER BY year

  20. e.g. ROUND(45.926, 2) -> 45.93 TRUNC (45.926, 2) -> 45.92 MOD( 1600 / 300) -> 100

  21. Tutorial Exercise Example:Database (stud.dbf)

  22. I General Structure List the names and ages (1 d.p.) of 1B girls. 1B Girls ?

  23. I General Structure List the names and ages (1 d.p.) of 1B girls. Condition for "1B Girls": 1) class = "1B" 2) sex = "F" 3) Both ( AND operator)

  24. I General Structure List the names and ages (1 d.p.) of 1B girls. What is "age"?

  25. I General Structure List the names and ages (1 d.p.) of 1B girls. Functions: # days : SYSDATE – dob # years :(SYSDATE – dob) / 365 1 d.p.: ROUND(__ , 1)

  26. I General Structure List the names and ages (1 d.p.) of 1B girls. SELECT class, sex, name, ROUND((SYSDATE-dob)/365,1) AS "age" FROM stud WHERE class='1B' AND sex='F'

  27. Tutorial Exercise Time

  28. SELECT last_name, specialty, auth_expense_amt FROM d_partners WHERE specialty ='All Types' OR specialty IS NULL AND auth_expense_amt = 300000 SELECT last_name, specialty, auth_expense_amt FROM d_partners WHERE (specialty ='All Types' OR specialty IS NULL) AND auth_expense_amt = 300000

  29. II Comparison expr IN ( value1, value2, value3) expr BETWEEN value1 AND value2 expr LIKE "%_"

  30. II Result Comparison eg. 6List the 1A students whose Math test score is between 80 and 90 (incl.) SELECT name, mtest FROM student ; WHERE class="1A" AND ; mtest BETWEEN 80 AND 90

  31. II Result Comparison eg. 7List the students whose names start with "T". SELECT name, class FROM student ; WHERE name LIKE "T%"

  32. II Result Comparison eg. 8List the Red house members whose names contain "a" as the 2nd letter. SELECT name, class, hcode FROM student ; WHERE name LIKE "_a%" AND hcode="R"

  33. III Grouping SELECT ...... FROM ...... WHERE condition ; GROUP BY groupexpr [HAVING requirement] Group functions: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) – groupexprspecifies the related rows to be grouped as one entry. Usually it is a column. – WHERE condition specifies the condition of individual rows before the rows are group. HAVING requirement specifies the condition involving the whole group.

  34. III Grouping eg. 11List the number of students of each class.

  35. Group By Class class 1A 1A 1A COUNT( ) 1A 1A 1B 1B 1B 1B 1B COUNT( ) 1B 1B 1B 1C 1C 1C COUNT( ) 1C 1C Student

  36. III Result Grouping eg. 11List the number of students of each class. SELECT class, COUNT(*) FROM student ; GROUP BY class

  37. III Grouping eg. 12List the average Math test score of each class.

  38. Group By Class class 1A 1A AVG( ) 1A 1A 1B 1B AVG( ) 1B 1B 1B 1B 1B 1C AVG( ) 1C 1C 1C Student

  39. III Result Grouping eg. 12List the average Math test score of each class. SELECT class, AVG(mtest) FROM student ; GROUP BY class

  40. III Result Grouping eg. 13List the number of girls of each district. SELECT dcode, COUNT(*) FROM student ; WHERE sex="F" GROUP BY dcode

  41. III Result Grouping eg. 14List the max. and min. test score of Form 1 students of each district. SELECT MAX(mtest), MIN(mtest), dcode ; FROM student ; WHERE class LIKE "1_" GROUP BY dcode

  42. III Result Grouping eg. 15List the average Math test score of the boys in each class. The list should not contain class with less than 3 boys. SELECT AVG(mtest), class FROM student ; WHERE sex="M" GROUP BY class ; HAVING COUNT(*) >= 3

  43. IV Display Order SELECT ...... FROM ...... WHERE ...... GROUP BY ..... ; ORDER BY colname ASC / DESC

  44. IV Result ORDER BY dcode Display Order eg. 16List the boys of class 1A, order by their names. SELECT name, id FROM student ; WHERE sex="M" AND class="1A" ORDER BY name

More Related