SQL (DML)
Schema Example / HR Schema Oracle
The SQL UPDATE Statement
• The UPDATE statement is used to modify the
existing records in a table.
• UPDATE table_name
SET column1 = value1, column2 = value2, ..
.
WHERE condition;
• Example:
• update employees set first_name='Ali',
last_name='Jaber', email='a.jaber@ptuk.edu.ps'
where employee_id=199;
The SQL DELETE Statement
• The DELETE statement is used to delete
existing records in a table.
• DELETE FROM table_name
WHERE condition;
• Example:
– delete from employees where employee_id=199;
ROWNUM Clause
• The ROWNUM Clause is used to specify the
number of records to return.
• The ROWNUM Clause is useful on large tables
with thousands of records. Returning a large
number of records can impact on performance.
• SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
ROWNUM Clause Examples
• select * from employees where
rownum<=10;
• select * from (select e.*, rownum r
from employees e) where r>10 and
r<=20;
• select salary from (select
salary,rownum r from (select
distinct(salary) from employees
order by salary asc)) where r=10;
The SQL MIN() and MAX() Functions
• The MIN() function returns the smallest value
of the selected column.
• The MAX() function returns the largest value
of the selected column.
• SELECT MIN(column_name)
FROM table_name
WHERE condition;
MIN() & MAX Examples
• select min(salary) as minimum_salary from employees;
MINIMUM_SALARY
--------------
2100
• select max(salary) as maximum_salary from employees;
MAXIMUM_SALARY
--------------
24000
• select min(salary) as min_salary, max(salary) as
max_salary from employees;
MIN_SALARY MAX_SALARY
---------- ----------
2100 24000
COUNT(), AVG() and SUM() Functions
• The COUNT() function returns the number of
rows that matches a specified criteria.
• The AVG() function returns the average value
of a numeric column.
• The SUM() function returns the total sum of a
numeric column.
COUNT() Example
• SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SQL> select count(*) from employees
where salary=2600;
COUNT(*)
----------
4
The SQL GROUP BY Statement
• The GROUP BY statement is often used with aggregate
functions (COUNT, MAX, MIN, SUM, AVG) to group the
result-set by one or more columns.
• select avg(salary),department_id from employees group
by(department_id);
AVG(SALARY) DEPARTMENT_ID
----------- -------------
8601.33333 100
4150 30
7000
19333.3333 90
9500 20
10000 70
10154 110
3475.55556 50
8955.88235 80
6500 40
5760 60
4400 10
• select count(*), extract( year from hire_date) from employees
group by extract(year from hire_date);
COUNT(*) EXTRACT(YEAR FROM HIRE_DATE)
---------- --------------------------
29 2005
1 2001
24 2006
19 2007
6 2003
10 2004
7 2002
11 2008
The SQL IN Operator
• The IN operator allows you to specify multiple values in a
WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...
);
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
The SQL BETWEEN Operator
• The BETWEEN operator selects values within a
given range. The values can be numbers, text,
or dates.
• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1
AND value2;
SQL Aliases
• SQL aliases are used to give a table, or a column in a table, a
temporary name.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of the query.
• SELECT column_name AS alias_name
FROM table_name;
• SELECT column_name(s)
FROM table_name AS alias_name;
User Prompt
You can write any thing else here but it should not contain spaces and & is required here
This value (102) was entered by the user
