Topic 07

Introduction to SQL – Part 2

Nebojša Bačanin Džakula, PhD

nbacanin@singidunum.ac.rs
Faculty of Technical Sciences, Singidunum University
Database Systems
2019/20
 2

Notation used in presentations

▪ Each presentation represents one topic, or part of the topic.

▪ Materials within each topic are divided into Sections (for example 3),
which are further divided into Subsections (for example 3.4).

▪ Important definitions and terms are given in this type of

textbox.

▪ SQL code is given in this type of textbox.

Note

▪ Notes and remarks are given in callouts like this

 Contents
1. SQL DML Statements
2. SQL DDL Statements

3
1. SQL DML Statements

4
 1.1 Relational model and environment #1
• Relational model that will be used for learning purposes is given in on this
slide.

5
 1.1 Relational model and environment #2
• We will use SQL WorkShop – SQL Commands component of the APEX to
create and to execute SQL commands.

6
 1.2 SQL DML Statements
• Basic SQL DML Statements are: SELECT, UPDATE, DELETE, INSERT and
MERGE.
• In the following slides, we will take a deeper look into the syntax and
examples of using these statements.
• All SQL statements must end with semicolon (;)

7
 1.3 SQL INSERT INTO #1
• The INSERT INTO statement is used to insert new records in a table.
• It is possible to write the INSERT INTO statement in two ways.
• The first way specifies both the column names and the values to be
inserted:

INSERT INTO table_name (column1,

column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO jobs

(job_id,job_title,min_salary,max_salary) VALUES
('IT_ADMIN','IT Administrator',30000,50000);

8
• If you are adding values for all the columns of the table, you do not need
to specify the column names in the SQL query.
• However, make sure the order of the values is in the same order as the
columns in the table.
• The INSERT INTO syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

INSERT INTO jobs VALUES (‘IT_DB',’IT Database

administrator',30000,50000);

9
 1.4 SQL UPDATE
• The UPDATE statement is used to modify the existing records in a table.
• Be careful when updating records in a table!
• Notice the WHERE clause in the UPDATE statement.
• The WHERE clause specifies which record(s) that should be updated. If
you omit the WHERE clause, all records in the table will be updated!

UPDATE table_name
SET column1 = value1, column2 =
value2, ...
WHERE condition;

UPDATE jobs SET min_salary=35000,max_salary=55000 WHERE

job_id='IT_ADMIN';

10
 1.5 SQL DELETE
• The DELETE statement is used to delete existing records in a table.
• Be careful when deleting records in a table!
• Notice the WHERE clause in the DELETE statement.
• The WHERE clause specifies which record(s) should be deleted. If you omit
the WHERE clause, all records in the table will be deleted!

DELETE FROM table_name

WHERE condition;

DELETE FROM jobs WHERE job_id='IT_ADMIN';

11
• If you want to delete all the data from the particular table, then you will
create SQL script with the following syntax:

DELETE FROM jobs;

12
 1.6 SQL SELECT TOP
• The SELECT TOP clause is used to specify the number of records to return.
• The SELECT TOP clause is useful on large tables with thousands of records.
Returning a large number of records can impact on performance.
• Not all database systems support the SELECT TOP clause. MySQL supports
the LIMIT clause to select a limited number of records, while Oracle uses
ROWNUM.
• Syntax for MS Access / MS SQL Server

SELECT TOP number|percent column_name(s)

FROM table_name
WHERE condition;

13
• Syntax for MySQL RDBMS:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

• Syntax for Oracle Databases:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

14
• In the following example, we use Oracle APEX for creating SQL script that
selects first 50 records from employees table.

SELECT * FROM employees WHERE ROWNUM<=50;

• In the second example, we select first 50 records from employees table,

but only for employees that work as “Sales Representatives”.

SELECT * FROM employees WHERE ROWNUM<=50 AND

job_id='SA_REP';

15
 1.7 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.
• MIN() syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

• MAX() syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

16
• In the following example, we show minimal salary from the employees
table:

SELECT MIN(salary) AS "Minimal Salary" FROM employees;

• In the following example, we show maximum salary from the employees

table:

SELECT MAX(salary) AS "Minimal Salary" FROM employees;

17
 1.8 SQL SUM(), COUNT() and AVG() 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.
• The COUNT() syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• AVG() syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;

18
• SUM() syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

• In the following example, we select sum of all salaries in the table:

SELECT SUM(salary) AS "SUM OF SALARIES" FROM employees;

19
• In the following example, we count number of employees in the
employees table:

SELECT COUNT(employee_id) AS "Number of employees" FROM

employees;

• These functions are often used with subqueries.

• With SQL queries with subqueries, the subquery is always executed first.

20
• In the following example, we select data for only those employees whose
salary is greater than the average salary:

SELECT * FROM employees WHERE salary>(SELECT AVG(salary)

FROM employees);

• In the following example, we select data for only those employees whose
salary is greater than the average salary and who were hired after 1st
January 2004.

SELECT * FROM employees WHERE salary>(SELECT AVG(salary)

FROM employees) AND hire_date>'01/01/2004';

21
 1.9 SQL LIKE operator
• The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column.
• There are two wildcards used in conjunction with the LIKE operator:
1. % - The percent sign represents zero, one, or multiple characters
2. _ - The underscore represents a single character
• MS Access uses a question mark (?) instead of the underscore (_).

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

22
• In the following example, we select all employees whose first name starts
with letter A.
• Note that the strings are case-sensitive.

SELECT * FROM employees WHERE first_name LIKE 'A%';

• In the following example, we select all employees whose first name ends
with letter b, and are exactly five letters in length.
SELECT * FROM employees WHERE first_name LIKE '______b';

• The next example shows employees whose first name starts with letter A,
and ends up with letter b.

SELECT * FROM employees WHERE first_name LIKE ‘a%b’;

23
 1.10 SQL wild carts
• A wildcard character is used to substitute any other character(s) in a
string.
• Wildcard characters are used with the SQL LIKE operator.
• The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column.
• There are two wildcards used in conjunction with the LIKE operator:
1. % - The percent sign represents zero, one, or multiple characters
2. _ - The underscore represents a single character
3. In the following example, we show all employees whose last name starts
with letter K, followed by any character, followed by n.

SELECT * FROM employees WHERE last_name LIKE 'K_n%';

24
 1.11 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, ...);

• Or:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

25
• The following example selects data for all employees whose job id is not
‘AD_PRES’ and ‘AD_VP’;

SELECT * FROM jobs WHERE job_id NOT IN

('AD_PRES','AD_VP');

• The following example demonstrates the usage of subqueries with SQL IN

operator:

SELECT * FROM employees WHERE job_id IN (SELECT job_id

FROM jobs);

26
 1.12 SQL BETWEEN Operator
• The BETWEEN operator selects values within a given range. The values can
be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are included.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• The following example select employees whose salary is within the range
[20000,30000].

SELECT * FROM employees WHERE salary BETWEEN 20000 AND

30000;

27
 1.13 SQL GROUP BY
• 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 column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

28
• The following example shows the average salary for all job categories in
from the employees table:
SELECT AVG(salary),job_id FROM employees GROUP BY
job_id;

• The following example shows the maximum salary for all job categories in
from the employees table, sorted by maximum salary:

SELECT MAX(salary),job_id FROM employees GROUP BY job_id

ORDER BY MAX(salary);

29
 1.14 SQL HAVING
• The HAVING clause was added to SQL because the WHERE keyword could
not be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

• The following example selects average salary from employees grouped by

job categories, but shows only results where aveage salary is greater than
20.000:
SELECT AVG(salary), job_id FROM employees GROUP BY
job_id HAVING AVG(salary)>20000;
30
2. SQL DDL Statements

31
 2.1 SQL CREATE DATABASE, DROP DATABASE
• The CREATE DATABASE statement is used to create a new SQL database.
CREATE DATABASE databasename;

• Unfortunalely, in Oracle APEX, we are not able to create new database, so

the example with this command won’t be presented.
• The DROP DATABASE statement is used to drop existing SQL database.

DROP DATABASE databasename;

32
 2.2 SQL CREATE TABLE
• The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
);

• The column parameters specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold
(e.g. varchar, integer, date, etc.).

33
• The following example creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons (

PersonID int,
LastName varchar2(255),
FirstName varchar2(255),
Address varchar2(255),
City varchar(255)
);

• The PersonID column is of type int and will hold an integer.

• The LastName, FirstName, Address, and City columns are of type varchar
and will hold characters, and the maximum length for these fields is 255
characters.

34
• A copy of an existing table can be created using a combination of the
CREATE TABLE statement and the SELECT statement.
• The new table gets the same column definitions. All columns or specific
columns can be selected.
• If you create a new table using an existing table, the new table will be
filled with the existing values from the old table.

CREATE TABLE persons_backup AS SELECT * FROM persons;

35
 2.3 Oracle SQL datatypes
• There are many Oracle SQL data types.
• In this section, the most important types will be briefly presented.
• The CHAR datatype stores fixed-length character strings.
• When you create a table with a CHAR column, you must specify a string
length (in bytes or characters) between 1 and 2000 bytes for the CHAR
column width.
• The default is 1 byte.
• Oracle then guarantees that:
➢ When you insert or update a row in the table, the value for the CHAR column has the
fixed length.
➢ If you give a shorter value, then the value is blank-padded to the fixed length.
➢ If a value is too large, Oracle Database returns an error.
➢ Oracle Database compares CHAR values using blank-padded comparison semantics.

36
• The VARCHAR2 datatype stores variable-length character strings.
• When you create a table with a VARCHAR2 column, you specify a
maximum string length (in bytes or characters) between 1 and 4000 bytes
for the VARCHAR2 column.
• For each row, Oracle Database stores each value in the column as a
variable-length field unless a value exceeds the column's maximum
length, in which case Oracle Database returns an error.
• Using VARCHAR2 and VARCHAR saves on space used by the table.
• For example, assume you declare a column VARCHAR2 with a maximum
size of 50 characters.
• In a single-byte character set, if only 10 characters are given for the
VARCHAR2 column value in a particular row, the column in the row's row
piece stores only the 10 characters (10 bytes), not 50.
• Oracle Database compares VARCHAR2 values using nonpadded
comparison semantics.
37
• NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode
character data.
• The character set of NCHAR and NVARCHAR2 datatypes can only be either
AL16UTF16 or UTF8 and is specified at database creation time as the
national character set.
• AL16UTF16 and UTF8 are both Unicode encoding.
• The NCHAR datatype stores fixed-length character strings that correspond
to the national character set.
• The NVARCHAR2 datatype stores variable length character strings.
• When you create a table with an NCHAR or NVARCHAR2 column, the
maximum size specified is always in character length semantics.
• Character length semantics is the default and only length semantics for
NCHAR or NVARCHAR2.

38
• Columns defined as LONG can store variable-length character data
containing up to 2 gigabytes of information.
• LONG data is text data that is to be appropriately converted when moving
among different systems.
• LONG datatype columns are used in the data dictionary to store the text
of view definitions.
• You can use LONG columns in SELECT lists, SET clauses of UPDATE
statements, and VALUES clauses of INSERT statements.

39
• The NUMBER datatype stores fixed and floating-point numbers.
• Numbers of virtually any magnitude can be stored and are guaranteed
portable among different systems operating Oracle Database, up to 38
digits of precision.

• The following numbers can be stored in a NUMBER column:

➢ Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
➢ Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits
➢ Zero
➢ Positive and negative infinity (generated only by importing from an Oracle Database,
Version 5)

40
• Oracle Database provides two numeric datatypes exclusively for floating-
point numbers: BINARY_FLOAT and BINARY_DOUBLE.
• They support all of the basic functionality provided by the NUMBER
datatype.
• However, while NUMBER uses decimal precision, BINARY_FLOAT and
BINARY_DOUBLE use binary precision.
• This enables faster arithmetic calculations and usually reduces storage
requirements.
• BINARY_FLOAT and BINARY_DOUBLE are approximate numeric datatypes.
They store approximate representations of decimal values, rather than
exact representations.
• For example, the value 0.1 cannot be exactly represented by either
BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific
computations. Their behavior is similar to the datatypes FLOAT and
DOUBLE in Java and XMLSchema.
41
• The DATE datatype stores point-in-time values (dates and times) in a
table.
• The DATE datatype stores the year (including the century), the month, the
day, the hours, the minutes, and the seconds (after midnight).
• Oracle Database can store dates in the Julian era, ranging from January 1,
4712 BCE through December 31, 9999 CE (Common Era, or 'AD'). Unless
BCE ('BC' in the format mask) is specifically used, CE date entries are the
default.
• Oracle Database uses its own internal format to store dates. Date data is
stored in fixed-length fields of seven bytes each, corresponding to
century, year, month, day, hour, minute, and second.

42
• There are many date functions available.
• Consider the following example:

SELECT TO_DATE('23 January 2018','DD MONTH YYYY') AS

"Date example" FROM DUAL;

• Function TO_DATE() converts string representation of the date to date

format.
• The first parameter of the function is date string, while second is the date
syntax.
• In this example, DD is two digits date, MONTH is full string name of the
month, while YYYY is year stored in four digits format.
• DUAL is special table that is available in Oracle APEX that is used for
testing purposes.

43
 2.4 SQL DROP TABLE
• The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

• In the following example, we drop table Persons:

DROP TABLE Persons;

44
 2.5 SQL TRUNCATE TABLE
• The TRUNCATE TABLE statement is used to delete the data inside a table,
but not the table itself.
TRUNCATE TABLE table_name;
• In the following example, we truncate (erase all the data) from table
Persons:
TRUNCATE TABLE Persons;

• If we want to delete all the data from the particular table, it is more
efficient to use TRUNCATE TABLE than DELETE FROM SQL statement.

45
 2.6 SQL ALTER TABLE
• The ALTER TABLE statement is used to add, delete, or modify columns in
an existing table.
• The ALTER TABLE statement is also used to add and drop various
constraints on an existing table.
• To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype;

• In the following example, we add a new column to the table Persons:

ALTER TABLE Persons ADD email VARCHAR2(255);

46
• To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

MODIFY column_name datatype;

ALTER TABLE Persons MODIFY email VARCHAR2(100);

47
 2.7 SQL Constraints
• SQL constraints are used to specify rules for data in a table.
• Constraints can be specified when the table is created with the CREATE
TABLE statement, or after the table is created with the ALTER TABLE
statement.

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

48
• SQL constraints are used to specify rules for the data in a table.
• Constraints are used to limit the type of data that can go into a table. This
ensures the accuracy and reliability of the data in the table. If there is any
violation between the constraint and the data action, the action is
aborted.
• Constraints can be column level or table level. Column level constraints
apply to a column, and table level constraints apply to the whole table.
• The following constraints are commonly used in SQL:
1. NOT NULL - Ensures that a column cannot have a NULL value
2. UNIQUE - Ensures that all values in a column are different
3. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table

49
4. FOREIGN KEY - Uniquely identifies a row/record in another table
5. CHECK - Ensures that all values in a column satisfies a specific condition
6. DEFAULT - Sets a default value for a column when no value is specified
7. INDEX - Used to create and retrieve data from the database very quickly

50
• By default, a column can hold NULL values.
• The NOT NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you
cannot insert a new record, or update a record without adding a value to
this field.
• The following SQL ensures that the "ID", "LastName", and "FirstName"
columns will NOT accept NULL values:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

51
• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.
• The following example uses two ways to add unique constraint with the
CREATE TABLE statement:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar2(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
52
• The PRIMARY KEY constraint uniquely identifies each record in a database
table.
• Primary keys must contain UNIQUE values, and cannot contain NULL
values.
• A table can have only one primary key, which may consist of single or
multiple fields.

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

53
• The CHECK constraint is used to limit the value range that can be placed in
a column.
• If you define a CHECK constraint on a single column it allows only certain
values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND
City='Sandnes')
);
54
• The DEFAULT constraint is used to provide a default value for a column.
• The default value will be added to all new records IF no other value is
specified.

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

55
 References
1. Oracle Academy courseware (Database Foundations and Database
Design and Programming with SQL), 2015.
2. Burleson, K. D., Physical Database Design Using Oracle (Foundations of
Database Design), 1st Edition, Auerebach Publications, 2006, p. 247.

56
 IMPORTANT NOTICE!
Ova prezentacija je nekomercijalna.
Slajdovi mogu da sadrže materijale preuzete sa Interneta, stručne i naučne
građe, koji su zaštićeni Zakonom o autorskim i srodnim pravima. Ova
prezentacija se može koristiti samo privremeno tokom usmenog izlaganja
nastavnika u cilju informisanja i upućivanja studenata na dalji stručni,
istraživački i naučni rad i u druge svrhe se ne sme koristiti –
Član 44 - Dozvoljeno je bez dozvole autora i bez plaćanja autorske naknade za nekomercijalne svrhe nastave:
(1) javno izvođenje ili predstavljanje objavljenih dela u obliku neposrednog poučavanja na nastavi;
- ZAKON O AUTORSKOM I SRODNIM PRAVIMA ("Sl. glasnik RS", br. 104/2009 i 99/2011)
.................................................................................................................................................................................
Nebojša Bačanin Džakula
nbacanin@singidunum.ac.rs

57
 Topic 07
Introduction to SQL – Part 2

Thank you for attention!

Nebojša Bačanin Džakula, PhD
nbacanin@singidunum.ac.rs
Faculty of Technical Sciences, Singidunum University
Database Systems
2019/20