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

3
Enter SQL command
4
Display the Table structure

• Enter the following command
• DESCRIBE MUSIC
• The structure of the table should be shown.

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

6
SQL DESCRIBE

• DESCRIBE lttable namegt
• DESCRIBE employeesTry the last statement.

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

8
Typical error messages

• The following statement has a spelling error
• SELCT FROM employeesThe error message is
        ORA-00900 invalid SQL statement
• The following statement incorrectly names the
  table employee instead of employees
• SELECT FROM employeeThe error message is
        ORA-00942 table or view does not exist
• Run the correct statement.

9
Subset of data WHERE clause

• SELECT ltcolumn name 1, column name 2, etc.gt FROM
  lttable namegtWHERE ltconditiongt
• SELECT first_name, last_name, salaryFROM
  employeesWHERE salary gt 5000

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

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.

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.

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.

14
Selection vs. Projection

• SELECT salaryFROM employeesWHERE last_name like
  Smith
• Selection (row) Projection (column)

ID First_name Last_name salary
10 John Doe 4000
20 Jane Jones 3000
30 Sylvia Smith 5000
40 Hai Nguyen 6000
15
Join
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

17
SELECTION

• SELECT FROM employeesWHERE department_id 60
• SELECT FROM employeesWHERE salary gt 10000

18
Projections

• A subset of columns
• SELECT first_name, last_name, salaryFROM
  employees
• SELECT id, title, artistFROM d_songs

19
Arithmetic Expressions

• Create expressions with number and date data by
  using arithmetic operators.

Operator Description
Add
- Subtract
Multiply
/ Divide
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

() / -
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.

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

23
Using Aliases

• SELECT last_name name, salary AS Salary,
  salary12 Annual SalaryFROM employees

NAME SALARY Annual Salary
Whalen 4400 52800
Hartstein 13000 156000
Fay 6000 72000