7/30/2019 SQL 1 [basic]

1/41

SQL Part 1

kal@ittelkom.ac.id

7/30/2019 SQL 1 [basic]

2/41

Outline

SQL History

Basic SQL

Select Statement Arithmetic Operators

Where Clause

Sorting

7/30/2019 SQL 1 [basic]

3/41

SQL History

The first version of SQL was developed at IBMby Andrew Richardson, Donald C. Messerly andRaymond F. Boyce in the early 1970s.

This version, initially called SEQUEL, wasdesigned to manipulate and retrieve datastored in IBM's original relational databaseproduct, System R.

IBM patented their version of SQL in 1985, whilethe SQL language was not formally standardizeduntil 1986 by the American National StandardsInstitute (ANSI) as SQL-86.

7/30/2019 SQL 1 [basic]

4/41

SQL Statements

A SELECT statement retrieves information fromthe database. Using a SELECT statement, youcan do the following:

Projection: You can use the projection capability in SQL to

choose the columns in a table that you want returned byyour query. You can choose as few or as many columns ofthe table as you require.

Selection: You can use the selection capability in SQL tochoose the rows in a table that you want returned by a

query. You can use various criteria to restrict the rows thatyou see.

Joining: You can use the join capability in SQL to bringtogether data that is stored in different tables by creating alink between them. You learn more about joins in a laterlesson.

7/30/2019 SQL 1 [basic]

5/41

Aljabar Relational Review

SelectionProjection

Table 1 Table 2

Table 1Table 1

Join

7/30/2019 SQL 1 [basic]

6/41

Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

SELECT identifies what columns

FROM identifies which table

7/30/2019 SQL 1 [basic]

7/41

SELECT *

FROM departments;

Selecting All Columns

7/30/2019 SQL 1 [basic]

8/41

Selecting Specific Columns

SELECT department_id, location_id

FROM departments;

7/30/2019 SQL 1 [basic]

9/41

Arithmetic Expressions

Create expressions with number and date

data by using arithmetic operators.

Operator

+

-

*

/

Description

Add

Subtract

Multiply

Divide

7/30/2019 SQL 1 [basic]

10/41

Using Arithmetic Operators

SELECT last_name, salary, salary + 300

FROM employees;

7/30/2019 SQL 1 [basic]

11/41

Operator Precedence

Multiplication and division take

priority over addition andsubtraction.

Operators of the same priority are

evaluated from left to right. Parentheses are used to force

prioritized evaluation and to clarify

statements.

* / + _

7/30/2019 SQL 1 [basic]

12/41

Operator Precedence

SELECT last_name, salary, 12*salary+100

FROM employees;

7/30/2019 SQL 1 [basic]

13/41

Using Parentheses

SELECT last_name, salary, 12*(salary+100)

FROM employees;

7/30/2019 SQL 1 [basic]

14/41

Defining a Column AliasA column alias:

Renames a column heading

Is useful with calculations

Immediately follows the column name -there can also be the optional AS

keyword between the column name and

alias

Requires double quotation marks if it

contains spaces or special characters or

is case sensitive

7/30/2019 SQL 1 [basic]

15/41

Using Column Aliases

SELECT last_name "Name", salary*12 "Annual Salary"

FROM employees;

SELECT last_name AS name, commission_pct commFROM employees;

7/30/2019 SQL 1 [basic]

16/41

Concatenation Operator

A concatenation operator:

Concatenates columns or character

strings to other columns Is represented by two vertical bars

(||)

Creates a resultant column that is acharacter expression

7/30/2019 SQL 1 [basic]

17/41

Using the Concatenation

OperatorSELECT last_name||job_id AS "Employees"

FROM employees;

7/30/2019 SQL 1 [basic]

18/41

Literal Character Strings

A literal is a character, a number, or

a date included in the SELECT list.

Date and character literal valuesmust be enclosed within single

quotation marks.

Each character string is output oncefor each

row returned.

7/30/2019 SQL 1 [basic]

19/41

Using Literal Character

StringsSELECT last_name ||' is a '||job_id

AS "Employee Details"

FROM employees;

7/30/2019 SQL 1 [basic]

20/41

Duplicate Rows

The default display of queries is all rows,including duplicate rows.

SELECT department_id

FROM employees;

7/30/2019 SQL 1 [basic]

21/41

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCTkeyword in the SELECT clause.

SELECT DISTINCT department_id

FROM employees;

7/30/2019 SQL 1 [basic]

22/41

Limiting Rows Using a Selection

retrieve allemployees

in department 90

EMPLOYEES

7/30/2019 SQL 1 [basic]

23/41

Limiting the Rows Selected

Restrict the rows returned by usingthe WHERE clause.

The WHERE clause follows the FROM

clause.

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)];

7/30/2019 SQL 1 [basic]

24/41

Using the WHERE Clause

SELECT employee_id, last_name, job_id, department_id

FROM employees

WHERE department_id = 90 ;

7/30/2019 SQL 1 [basic]

25/41

Comparison Conditions

Operator

=

>

>=

=10000

AND job_id LIKE '%MAN%';

7/30/2019 SQL 1 [basic]

35/41

Using the OR Operator

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000OR job_id LIKE '%MAN%';

7/30/2019 SQL 1 [basic]

36/41

SELECT last_name, job_idFROM employees

WHERE job_id

NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

Using the NOT Operator

7/30/2019 SQL 1 [basic]

37/41

Rules of Precedence

Override rules of precedence by using parentheses.

Order Evaluated Operator

1 Arithmetic operators

2 Concatenation operator

3 Comparison conditions4 IS[NOT]NULL, LIKE, [NOT]IN

5 [NOT] BETWEEN

6 NOT logical condition

7 AND logical condition8 ORlogical condition

7/30/2019 SQL 1 [basic]

38/41

SELECT last_name, job_id, salary

FROM employeesWHERE job_id = 'SA_REP'

OR job_id = 'AD_PRES'

AND salary > 15000;

Rules of Precedence

ORDER BY Cl

7/30/2019 SQL 1 [basic]

39/41

SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date ;

ORDER BY Clause

Sort rows with the ORDER BY clause

ASC: ascending order, default

DESC: descending order

The ORDER BY clause comes last in the SELECT

statement.

7/30/2019 SQL 1 [basic]

40/41

The order of ORDER BY list is the order of sort.

You can sort by a column that is not in the

SELECT list.

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

Sorting by Multiple Columns

7/30/2019 SQL 1 [basic]

41/41

Review

SQL History

Basic SQL

Select Statement Arithmetic Operators

Where Clause

Sorting