SQL Review

1. SQL Review Sections 1 - SQL and other basic statements

2. Using APEX SQL editor • You may either type the command into the SQL editor or use the cut and paste option • If you are going to cut/paste the command copy the command from the word document into NotePad. This will drop out hidden characters. • Next copy the command from the NotePad into the editor Marge Hohly

3. Enter SQL command Marge Hohly

4. Display the Table structure • Enter the following command: • DESCRIBE MUSIC; • The structure of the table should be shown. Marge Hohly

5. Select command structure • SELECT field1, field2, field3FROM table_nameWHERE condition; • Try the following commandSELECT employee_id, first_name, last_name, department_idFROM employees; • SELECT employee_id, first_name, last_name, department_idFROM employeesWHERE department_id = 90; Marge Hohly

6. SQL DESCRIBE • DESCRIBE <table name>; • DESCRIBE employees;Try the last statement. Marge Hohly

7. Insert Data • Note the data types for each column • Inserting data into the table. • Since the table is empty all fields need to be populated, so column names can be omitted • INSERT INTO musicVALUES (10,'Marge Hohly','Folk'); • This will insert one record into the table • Next display the contents of the table to view the data • SELECT *FROM music; Marge Hohly

8. Typical error messages • The following statement has a spelling error: • SELCT *FROM employees;The error message is:       ORA-00900: invalid SQL statement • The following statement incorrectly names the table employee instead of employees: • SELECT *FROM employee;The error message is:       ORA-00942: table or view does not exist • Run the correct statement. Marge Hohly

9. Subset of data WHERE clause • SELECT <column name 1, column name 2, etc.> FROM <table name>WHERE <condition>; • SELECT first_name, last_name, salaryFROM employeesWHERE salary > 5000; Marge Hohly

10. Application Express SQL editor • The SQL course will use the three following sets of database tables for examples and practice exercises. • Oracle tables: COUNTRIES, REGIONS, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY AND JOB_GRADES • DJs on Demand database tables: D_CDS, D_PACKAGES, D_TYPES, D_THEMES, D_CLIENTS, D_VENUES, D_SONGS, D_TRACK_LISTINGS, D_PARTNERS, D_EVENTS, D_PLAY_LIST_ITEMS, D_JOB_ASSIGNMENTS • Global Fast Foods database tables: F_CUSTOMERS, F_REGULAR_MENUS, F_PROMOTIONAL_MENUS, F_SHIFTS, F_STAFFS, F_FOOD_ITEMS, F_ORDERS, F_ORDER_LINES, F_SHIFT_ASSIGNMENTS • Print out these tables for your reference when using the Application Express editor • These tables are available on the Student Resource web page for this class Marge Hohly

11. Review the tables • There are six properties of tables in a relational database: • Property 1: Entries in columns are single-valued. • Property 2: Entries in columns are of the same kind. • Property 3: Each row is unique. • Property 4: Sequence of columns is insignificant. • Property 5: Sequence of rows is insignificant. • Property 6: Each column has a unique name. Marge Hohly

12. Categories of SQL Statements • Data manipulation language (DML) statements • Begin with INSERT, UPDATE, DELETE, or MERGE • Used to modify the table by entering new rows, changing existing rows, or removing existing rows. • Data definition language (DDL) statements set up, change, and remove data structures from the database. • The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements. • Transaction control (TCL) statements are used to manage the changes made by DML statements. • Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT. TCL changes can be grouped together into logical transactions. • Data control language (DCL) • keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it. Marge Hohly

13. KEYWORD, CLAUSE, STATEMENT • Throughout this course, the words keyword, clause, and statement are used as follows: • A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. • A clause is a part of a SQL statement. SELECT employee_id, last_name, .... is a clause. • A statement is a combination of two or more clauses.SELECT *FROM employees; is a SQL statement. Marge Hohly

14. Selection vs. Projection • SELECT salaryFROM employeesWHERE last_name like ‘Smith’; • Selection (row) Projection (column) Marge Hohly

15. Join Marge Hohly

16. SELECT statement • SELECT statements can provide the same information depending on how they are written • Example: • SELECT *FROM d_songs; • SELECT id, title, duration, artist, type_codeFROM d_songs; Marge Hohly

17. SELECTION • SELECT *FROM employeesWHERE department_id = 60; • SELECT * FROM employeesWHERE salary > 10000; Marge Hohly

18. Projections • A subset of columns • SELECT first_name, last_name, salaryFROM employees; • SELECT id, title, artistFROM d_songs; Marge Hohly

19. Arithmetic Expressions • Create expressions with number and date data by using arithmetic operators. Marge Hohly

20. Operator Precedence • Operator Precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to force prioritized evaluation and to clarify statements. • Remember: Please excuse my dear aunt Sally Marge Hohly

21. What is null? • If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. • A null is not the same as zero. Zero is a number. • A null is not a space. Space is a character. Marge Hohly

22. Column Alias • Renames a column heading • Is useful in naming columns of derived values • Immediately follow the column name • Uses optional AS keyword between the column name and alias • Required double quotation marks if it contains spaces or special characters or is case sensitive Marge Hohly

23. Using Aliases • SELECT last_name name, salary AS Salary, salary*12 “Annual Salary”FROM employees; Marge Hohly