SQL Review

Sections 1 - SQL and other basic statements

Marge Hohly 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.

Select command structure SELECT field1, field2, field3

FROM 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 5

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 music

VALUES (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 salary

FROM 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

Join

Marge Hohly 15

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 16

SELECTION SELECT *

FROM employeesWHERE department_id = 60;

SELECT * FROM employeesWHERE salary > 10000;

Marge Hohly 17

Projections

A subset of columns SELECT first_name, last_name, salary

FROM employees; SELECT id, title, artist

FROM d_songs;

Marge Hohly 18

Marge Hohly 19

Arithmetic Expressions Create expressions

with number and date data by using arithmetic operators. Operator Description

+ Add

- Subtract

* Multiply

/ Divide

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;

NAME SALARY Annual Salary

Whalen 4400 52800

Hartstein 13000 156000

Fay 6000 72000