Structured Query Language (1) (SQL) Eng. Mohammed Alokshiya

Islamic University of Gaza
Faculty of Engineering
Computer Engineering Dept.
Database Lab (ECOM 4113)
Lab 2
Structured Query Language (1)
(SQL)
Eng. Mohammed Alokshiya
October 14, 2014
SQL is a standard language for accessing and manipulating
databases.
SQL commands are instructions, coded into SQL statements,
which are used to communicate with the database to perform
specific tasks, work, functions and queries with data.
SQL commands can be used not only for searching the database
but also to perform various other functions like, for example, you
can create tables, add data to tables, or modify data, drop the
table, set permissions for users. SQL commands are grouped into
major categories depending on their functionality:
 Data Manipulation Language (DML) - These SQL commands are used
for storing, retrieving, modifying, and deleting data. These Data
Manipulation Language commands are CALL, DELETE, EXPLAIN,
INSERT, LOCK TABLE, MERGE, SELECT and UPDATE.
 Data Definition Language (DDL) - These SQL commands are used for
creating, modifying, and dropping the structure of database objects.
The commands are ALTER, ANALYZE, AUDIT, COMMENT, CREATE,
DROP, FLASHBACK, GRANT, PURGE, RENAME, REVOKE and TRUNCATE.
 Transaction Control Language (TCL) - These SQL commands are used
for managing changes affecting the data. These commands are
COMMIT, ROLLBACK, and SAVEPOINT.
 Etc.
In this lab, we will use the well-known HR sample schema (which
included in Oracle XE).
2
INSERT Statement
INSERT statement is used to add new rows into a table in the
database. The syntax of SQL INSERT statement is:
Syntax of SQL INSERT Statement
INSERT INTO table-name (column_list)
VALUES (values-list);
Example:
Using INSERT Statement
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
VALUES ('PS', 'Palestine', 4);
You can also insert new rows without specifying column names, by
typing: “INSERT INTO table-name VALUES (value-list)”. In this case,
you MUST order values in the same order of its corresponding
columns.
Example:
Using INSERT Statement
INSERT INTO COUNTRIES VALUES ('PS', 'Palestine', 4);
Note: use “DESC[RIBE] table-name” statement to see the columns
order in table definition.
3
DELETE Statement
DELETE statement is the opposite of INSERT statement. It is
used to delete rows from a table according to a specified condition.
The syntax of SQL DELETE statement is:
Syntax of SQL DELETE Statement
DELETE FROM table-name
WHERE condition;
Example:
Using DELETE Statement
DELETE FROM COUNTRIES
WHERE COUNTRY_ID = 'IL';
It also can be used to delete all rows from a table by not specifying
any conditions. If you want to empty a table, you just have to issue
this command: “DELETE FROM table-name”. You should be aware
when using this form.
UPDATE Statement
UPDATE statement is used to modify existing rows values in a
table. The syntax of SQL UPDATE statement is:
Syntax of SQL UPDATE Statement
UPDATE table-name
SET column_name_1 = new_value1 , column_name_2 = new_value2 -- etc
WHERE condition;
Example:
Using UPDATE Statement
UPDATE JOBS
SET MIN_SALARY = 30000, MAX_SALARY = 50000
WHERE JOB_TITLE = 'President';
4
SELECT Statement
The most commonly used SQL command is SELECT statement.
SQL SELECT statement is used to query or retrieve data from a table
in the database. A query may retrieve information from specified
columns or from all of the columns in the table.
To create a simple SQL SELECT Statement, you must specify the
column(s) name and the table name. The whole query is called SQL
SELECT Statement.
Syntax of SQL SELECT Statement
SELECT * | {[DISTINCT] column_list | expression [alias],...}
FROM table-name
[WHERE condition]
[GROUP BY columns]
[HAVING group-selection-condition]
[ORDER BY column-names || aliases || column-numbers];
- table-name is the name of the table from which the information is
retrieved.
- column_list includes one or more columns from which data is retrieved.
 Selecting All Columns
Selecting All Columns
SELECT *
FROM EMPLOYEES;
5
Press F9 to run the statement:
 Selecting Specific Columns
Selecting Specific Columns
SELECT FIRST_NAME, LAST_NAME, PHONE_NUMBER, SALARY
FROM EMPLOYEES;
 Arithmetic Expressions
Create expressions with number and date data by using
arithmetic operators
Using Arithmetic Operators
SELECT FIRST_NAME, LAST_NAME, SALARY, SALARY + 300
FROM EMPLOYEES;
6
 Null Values
 A null is a value that is unavailable, unassigned, unknown,
or inapplicable.
 It is NOT the same as a zero or a blank space.
Null Values
SELECT LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT
FROM EMPLOYEES;
 You cannot use equal operator (=) to compare two null
values! Instead, use (IS) key word to check if a value is null
or not.
Example: retrieve all employees who have not manager
Null Values
SELECT FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;
7
The result of any arithmetic expressions containing a null
value is a null value
o null + 5 is null
o null * 12 is null
Null Values in Arithmetic Expressions
SELECT LAST_NAME, SALARY,COMMISSION_PCT, SALARY + SALARY * COMMISSION_PCT
FROM EMPLOYEES;
o To overcome this problem, use NVL function, which
converts a Null value into an actual specified value
Using NVL Function
SELECT LAST_NAME, SALARY, COMMISSION_PCT, SALARY + SALARY * NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;
8
 A 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 if it is case-sensitive
Using Aliases
SELECT FIRST_NAME FName, LAST_NAME AS LName, SALARY * 12 "Annual Salary"
FROM EMPLOYEES;
 A Concatenation Operator
 Links columns or character strings to other columns
 Is represented by two vertical bars (||)
 Creates a resultant column that is a character expression
9
Concatenation Operator
SELECT FIRST_NAME || ' ' || LAST_NAME AS "Full Name"
FROM EMPLOYEES;
 Duplicate Rows
 The default display of queries is all rows, including
duplicate rows, but you can display only distinct values
without duplication by using “DISTINCT” keyword.
SELECT DISTINCT
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES;
10
Restricting Data
In most cases, we query the database to get some specific
data from a table and not the whole table; therefore, we need a
technique to restrict the data retrieved by SELECT statement.
Restricting the rows that are returned by a query can be done
by using the optional [WHERE clause].
Syntax of SQL SELECT Statement
SELECT * | {[DISTINCT] column_list | expression [alias],...}
FROM table-name
[WHERE condition]
Example: retrieving all employees who are working in depart 30.
All Employees in Deptartment 30
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;
 Notes:
 When you deal with character strings or date values, you
must enclosed them by single quotation marks (' ').
 Character values are case-sensitive, and date values are
format-sensitive.
 The default date format is DD-MON-RR.
11
Example: retrieving all employees whose first name is “David”.
All David’s
SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'David';
Example: retrieving all employees who are hired on 7/6/2002.
All Employees with Hire Date: 7/6/2002
SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE = '7-JUN-02';
 LIKE Condition
 Use the LIKE condition to perform wildcard searches of
valid search string values.
 Search conditions can contain either literal characters or
numbers:
o % denotes zero or many characters.
o _ denotes one character
Example: retrieve all employees whose first name starts with “A”
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'A%';
12
Example: retrieve all employees whose first name starts with “A”
and the third letter is “e”
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'A_e%';
13
Comparison operators:
Operator
=
>
>=
<
<=
<>
BETWEEN ... AND ...
LIKE
IS NULL
IN(set)
ANY(set)
ALL(set)
Meaning
Equal to
Greater than
Greater than or equal to
Less than
Less than or equal to
Not equal to
Between two values (inclusive)
Match a character pattern
Is a null value
Match any of a list of values
Compare to any value in a set
Compare to all values in a set
Rules of Precedence
Operator
Parentheses
()
*, /
+, ||
>, <, >=,
<=, =, <>
Arithmetic operators
Concatenation operator
Comparison conditions
IS [NOT] NULL, LIKE, [NOT] IN
[NOT] BETWEEN
Not equal to
NOT logical condition
AND logical condition
OR logical condition
14
Priority
1
2
3
4
5
6
7
8
9
10
11
Example: retrieving names of all employees whose salary between
5000 and 6000.
Retrieve All Employees with Salary Between 5000 and 6000
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 5000 AND SALARY <= 6000;
Another way to retrieve them:
Retrieve All Employees with Salary Between 5000 and 6000
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 5000 AND 6000;
Example: retrieving all employees who are working in
departments: 60, 90, or 100.
All Employees in Departments: 60, 90, and 100
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90, 100);
15
Example: retrieving all employees who are working in departments
100 AND their salary is greater than ALL employees in department
60.
First solution: retrieve targeted employees with two steps; firstly,
retrieve salaries of all employees in dept. 60 (without duplication),
then, use the retrieve values in another query. The first query:
First Query
SELECT DISTINCT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
Then you will get four values:
Use them in a new Query:
Second Query
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
AND SALARY > ALL(9000, 4800, 4200, 6000);
And you will get the targeted employees:
The previous solution is not wrong. However, it is not a practical
solution since you apply it with many steps and each step needs
a human to do them (cannot applied by machine since the
retrieve values are dynamic and can be changed in any time.
A better solution is to embedded the first query in the second
query in “All’s condition parentheses”, which is called: inner
query:
All Employees in Departments: 60, 90, and 100
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
AND SALARY > ALL(
SELECT DISTINCT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60
);
16
Sorting Data
You can sort rows retrieved by a query using the optional
[ORDER BY clause].
Syntax of SQL SELECT Statement
SELECT * | {[DISTINCT] column_list | expression [alias],...}
FROM table-name
[WHERE condition]
[ORDER BY column-names || expression || aliases || (column-alias)number];
Example: retrieving all employees’ records sorted by first name.
Using “Order By” Clause
SELECT FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
ORDER BY FIRST_NAME;
By default, “ORDER BY” Clause sorts the retrieved rows in
ascending order. To reverse the ordering, use “DESC” keyword
after column-name.
Example: retrieve all employees’ records sorted by their hire date.
However, the newest employee should come first and the old one
come last.
Descending Order
SELECT FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;
17
You can also sort rows according to expression result; in this case,
you have to use an alias instead of column name.
Example: retrieve all employees sorted by their total salary.
Total salary = salary + salary * commission_pct
Descending Order
SELECT FIRST_NAME, LAST_NAME, SALARY + SALARY * NVL(COMMISSION_PCT, 0) "Total Salary"
FROM EMPLOYEES
ORDER BY "Total Salary" DESC;
18
You can sort rows according to more than one column.
Example: retrieve all employees sorted by their total salary, if there
are two employees have the same total salary, then sort them by
first name in ascending order, then by their last name.
Sorting According to More Than One Column
SELECT FIRST_NAME, LAST_NAME, SALARY + SALARY * NVL(COMMISSION_PCT, 0) "Total Salary"
FROM EMPLOYEES
ORDER BY "Total Salary" DESC, FIRST_NAME, LAST_NAME;
Note: in “ORDER BY” clause, you can type (column | alias) numeric
position instead of name. For example in the previous query,
FIRST_NAME column comes first in the query, so its number is 1.
LAST_NAME comes second, so its number is 2. Finally, “Total
Salary” alias comes third, so its number is 3. Therefore, the
previous query can be wrote in another way:
Sorting According to More Than One Column
SELECT FIRST_NAME, LAST_NAME, SALARY + SALARY * NVL(COMMISSION_PCT, 0) "Total Salary"
FROM EMPLOYEES
ORDER BY 3 DESC, 1, 2;
19
Exercises:
1. Write SQL statement to insert a new record into JOBS table with
the following information:
JOB_ID = C_ENG
JOB_TITLE = Computer Engineer
MIN_SALARY = 20000
MAX_SALARY = 50000
2. Write SQL statement to modify all employees’ JOB_IDs to
C_ENG, if there salary is between 20000 and 50000.
3. Write SQL statement to delete all employees who are not
working in any departments, and all employees who are working in
department 80, but their TOTAL salary is less than 7000.
 Note: total salary = salary + salary * commission_pct
4. Write SQL statement to retrieve the last name and salary for all
employees whose salary is not in the range 5000 through 12000.
5. Write SQL statement to retrieve the last names of all employees
who have both an “a” and an “e” in their last name.
6. Write SQL statement to retrieve the last name, salary, and
commission for all employees who earn commissions. Sort data in
descending order of salary and commissions. Use the column’s
numeric position in the ORDER BY clause.
7. Write SQL statement to retrieve all employee last names in
which the third letter of the name is “a”.
20

Download Report