Turkish Statistical Institute SQL Fundamentals Overview
S
Q
L
F
U
N
D
E
M
A
N
T
A
L
S
3
1
.
0
3
.
2
0
1
4
(
M
u
s
c
a
t
,
O
m
a
n
)
O
U
T
L
I
N
E
•
Def
inition of SQL
•
Ma
i
n Categor
i
es
Of Sql
•
Bas
i
c Sql Commands
•
Nested Queries and Subqueries
•
Set Operators (minus, in, not in, all, some, intersect, exists)
•
Aggregate Funct
ions
•
Jo
in Operations
•
Using View and Matearilized View
•
Sequences
•
Triggers And The Purpose Of Triggers
S
Q
L
D
E
F
I
N
I
T
I
O
N
S
Q
L
(
S
t
r
u
c
t
u
r
e
d
Q
u
e
r
y
L
a
n
g
u
a
g
e
)
i
s
a special-purpose programming language
designed for managing data held in a RDBMS
First developed in the early 1970s at IBM
C
ommercially
released
by Relational Software Inc.
B
ecame a
standard
of the
American National Standards Institute (ANSI) in 1986
International Organization for Standards (ISO) in 1987
M
A
I
N
C
A
T
E
G
O
R
I
E
S
Sql Commands Overview
CREATE TABLE <name> ( <field> <domain>, … )
INSERT INTO <name> (<field names>)
VALUES (<field values>)
DELETE FROM <name>
WHERE <condition>
UPDATE <name>
SET <field name> = <value>
WHERE <condition>
SELECT <fields>
(distinct is usable here)
FROM <name>
WHERE <condition>
Some Examples
*Live Demo : install and use a DBMS for SQL s
E
x
a
m
p
l
e
:
s
e
l
e
c
t
d
i
s
t
i
n
c
t
s
n
a
m
e
,
a
g
e
f
r
o
m
s
a
i
l
o
r
s
Q1 : Find all sailors with a rating above 7.
Q2 : Find the sailors that rating>7
Q3 : Find the sailors that whose name starts with ‘B’ letter
Q4 : Find the names of the sailors who reserved at least one boat
E
x
a
m
p
l
e
s
o
f
B
a
s
i
c
S
Q
L
Q
u
e
r
i
e
s
Nested Queries and
Subqueries
A
subquery
is a select-from-where expression
that is nested within another query.
Example Query
:
Find courses offered in Fall 2009 and in Spring 2010
select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id in (select course_id
from section
where semester = ’Spring’ and year= 2010);
U
s
i
n
g
S
e
t
O
p
e
r
a
t
o
r
s
(
M
I
N
U
S
,
N
O
T
I
N
,
N
O
T
E
X
I
S
T
S
,
I
N
,
E
X
I
S
T
S
,
I
N
T
E
R
S
E
C
T
)
S
y
n
t
a
x
:
Q
u
e
r
y
1
<
S
e
t
O
p
e
r
a
t
o
r
>
Q
u
e
r
y
2
Example1: (difference)
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
M
i
n
u
s
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
2
;
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
w
h
e
r
e
s
t
a
f
f
_
n
u
m
b
e
r
n
o
t
i
n
(
s
e
l
e
c
t
s
t
a
f
f
_
n
u
m
b
e
r
f
r
o
m
s
t
a
f
f
_
2
)
;
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
a
w
h
e
r
e
n
o
t
e
x
i
s
t
s
(
s
e
l
e
c
t
*
s
t
a
f
f
_
2
b
w
h
e
r
e
a
.
s
i
d
=
b
.
s
i
d
)
;
Example2: (intersect)
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
i
n
t
e
r
s
e
c
t
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
2
;
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
w
h
e
r
e
s
t
a
f
f
_
n
u
m
b
e
r
i
n
(
s
e
l
e
c
t
s
t
a
f
f
_
n
u
m
b
e
r
f
r
o
m
s
t
a
f
f
_
2
)
;
S
e
l
e
c
t
*
f
r
o
m
s
t
a
f
f
_
1
a
w
h
e
r
e
e
x
i
s
t
s
(
s
e
l
e
c
t
*
s
t
a
f
f
_
2
b
w
h
e
r
e
a
.
s
i
d
=
b
.
s
i
d
)
;
E
x
e
r
c
i
s
e
s
:
Q5: Find the sailors that rating of the one equals 3 times of the other
(hint: result is Art, Bob, Horatio)
Q6:Find the names of sailors who have red or green boat
Q7: Find the names of sailors who have reserved a red and a green boat
Q8: Find the sname of sailors who have reserved red boats but not green boats
*Live Demo
A
n
s
w
e
r
s
:
Q
7
A
n
s
w
e
r
-
1
(
I
t
i
s
a
c
o
m
p
l
e
x
q
u
e
r
y
)
Select s.sname
from sailors s, reserves r 1, boats b1, r 2, boats b2
where s.sid = r1.sid and r1.bid=b1.bid
and s.sid = r2.sid and r2.bid=b2.bid
and b1.color=‘red’ and b2.color=‘green’;
Q
7
A
n
s
w
e
r
-
2
–
U
s
i
n
g
I
n
t
e
r
s
e
c
t
o
p
e
r
a
t
i
o
n
(
e
a
s
i
l
y
t
o
u
n
d
e
r
s
t
a
n
d
,
w
r
i
t
e
)
Select s.sname
from sailors s, reserves r , boats b
where s.sid = r.sid and r.bid=b.bid and b.color=‘red’
I
N
T
E
R
S
E
C
T
Select s2.sname
from sailors s2, reserves r 2, boats b2
where s2.sid = r2.sid and r2.bid=b2.bid and b2.color=‘red’ ;
Q
8
A
n
s
w
e
r
:
Find the sname of sailors who have reserved red boats but not green boats.
Select s.sname
from sailors s, reserves r , boats b
where s.sid = r.sid and r.bid=b.bid and b.color=‘red’
M
I
N
U
S
Select s2.sname
from sailors s2, reserves r2, boats b2
where s2.sid = r2.sid and r2.bid=b2.bid and b2.color=‘green’;
ALL, SOME,ANY
They are used to compare two datasets
E
x
a
m
p
l
e
:
Find names of instructors with salary greater than that of some (at least one)
instructor in the Biology department.
A
n
s
w
e
r
-
1
:
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ’Biology’;
S
a
m
e
q
u
e
r
y
u
s
i
n
g
s
o
m
e
c
l
a
u
s
e
select name
from instructor
w
h
e
r
e
s
a
l
a
r
y
>
s
o
m
e
(
s
e
l
e
c
t
s
a
l
a
r
y
from instructor
where dept_name = ’Biology’);
S
a
m
e
q
u
e
r
y
u
s
i
n
g
a
n
y
c
l
a
u
s
e
select name
from instructor
w
h
e
r
e
s
a
l
a
r
y
>
a
n
y
(
s
e
l
e
c
t
s
a
l
a
r
y
from instructor
where dept_name = ’Biology’);
Example Query
for All Clause
Find the names of all instructors whose salary is greater than
the salary of
all
instructors in the Biology department.
select
name
from
instructor
where
salary
>
all
(
select
salary
from
instructor
where
dept_name
= ’Biology’);
UNION vs UNION ALL
They concetane the result of two different SQLs.
They differ in the way they handle
duplicates
.
Selected columns need to be of the same data type
There is a performance hit when using UNION vs UNION ALL,
AGGREGATE FUNCT
I
ONS
avg:
average value
min:
minimum value
max:
maximum value
sum:
sum of values
count:
number of values
F
i
n
d
t
h
e
a
v
e
r
a
g
e
s
a
l
a
r
y
o
f
i
n
s
t
r
u
c
t
o
r
s
i
n
t
h
e
C
o
m
p
u
t
e
r
S
c
i
e
n
c
e
d
e
p
a
r
t
m
e
n
t
:
s
e
l
e
c
t
a
v
g
(
s
a
l
a
r
y
)
f
r
o
m
i
n
s
t
r
u
c
t
o
r
w
h
e
r
e
d
e
p
t
_
n
a
m
e
=
’
C
o
m
p
.
S
c
i
.
’
;
Find the total number of instructors who teach a course in the Spring
2010 semester
:
s
e
l
e
c
t
c
o
u
n
t
(
d
i
s
t
i
n
c
t
I
D
)
f
r
o
m
t
e
a
c
h
e
s
w
h
e
r
e
s
e
m
e
s
t
e
r
=
’
S
p
r
i
n
g
’
a
n
d
y
e
a
r
=
2
0
1
0
;
..GROUP BY
Attributes in select clause outside of aggregate functions must appear
in group by list
/* erroneous query */
select city, student_name, count(*), avg(average_note) note from student
group by city
SELECT
List the
columns
(and expressions) that should be returned from the query
FROM
Indicate the
table
(s) or view(s) from which data will be obtained
WHERE
Indicate the
conditions
under which a
row
will be included in the result
GROUP BY
Indicate
columns
to group the results
HAVING
Indicate the
conditions
under which a
group
will be included
ORDER BY
Sorts the result according to specified
columns
Used for queries on single or multiple tables
Clauses of the SELECT statement:
A
G
G
R
E
G
A
T
E
F
U
N
C
T
I
O
N
S
E
X
E
R
C
I
S
E
S
*Live Demo :
E
x
e
r
c
i
s
e
:
Using STUDENT table
S
T
U
D
E
N
T
(
s
t
u
d
e
n
t
_
n
a
m
e
,
l
e
s
s
o
n
_
n
a
m
e
,
n
o
t
e
)
Find:
a) The count of lessons for each student
b) The average note, and maximum note of each student
c) Find the students that have more than 1 record for the same lesson
S
o
l
u
t
i
o
n
s
o
f
t
h
e
E
x
e
r
c
i
s
e
:
select student_name, max(note), avg(note), count(lesson_name) from student
group by student_name
;
select student_name, lesson_name, count(*) from student
group by student_name, lesson_name
having count(*) > 1;
JOIN OPERATIONS
Join operations take two relations and return as a result another relation.
A join operation is a Cartesian product which requires that tuples in the
two relations match (under some condition).
It also specifies the attributes that are present in the result of the join
The join operations are typically used as subquery expressions
in the
from clause
JOIN EXAMPLES
Course
:
Department:
select t1.course_id, t1.title, t1.credits, t2.*
from COURSE t1, department t2
where t1.dept_id = t2.dept_id;
J
O
I
N
E
X
A
M
P
L
E
S
(
C
o
n
t
’
d
)
J
O
I
N
E
X
A
M
P
L
E
S
u
s
i
n
g
R
i
g
h
t
O
u
t
e
r
J
o
i
n
select t1.course_id, t1.title, t1.credits, t2.*
from COURSE t1, department t2
where t1.dept_id(+) = t2.dept_id;
select t1.course_id, t1.title, t1.credits, t2.*
f
r
o
m
C
O
U
R
S
E
t
1
r
i
g
h
t
o
u
t
e
r
j
o
i
n
d
e
p
a
r
t
m
e
n
t
t
2
on t1.dept_id = t2.dept_id;
J
O
I
N
E
X
A
M
P
L
E
S
u
s
i
n
g
L
e
f
t
O
u
t
e
r
J
o
i
n
select t1.course_id, t1.title, t1.credits, t2.*
from COURSE t1, department t2
where t1.dept_id = t2.dept_id(+);
select t1.course_id, t1.title, t1.credits, t2.*
f
r
o
m
C
O
U
R
S
E
t
1
l
e
f
t
o
u
t
e
r
j
o
i
n
d
e
p
a
r
t
m
e
n
t
t
2
on t1.dept_id = t2.dept_id;
J
O
I
N
E
X
A
M
P
L
E
S
u
s
i
n
g
F
u
l
l
O
u
t
e
r
J
o
i
n
select t1.course_id, t1.title, t1.credits, t2.*
from COURSE t1
full outer join
department t2
on t1.dept_id = t2.dept_id;
S
o
m
e
E
a
s
y
b
u
t
u
s
e
f
u
l
S
Q
L
s
t
a
t
e
m
e
n
t
s
Create table t1 as select * from t2;
Select * from t1 where rownum < 100;
D
e
l
e
t
i
n
g
d
u
p
l
i
c
a
t
e
r
e
c
o
r
d
s
e
x
a
m
p
l
e
:
for table Student(student_name, lesson_name, note)
delete from
STUDENT
A1 where exists
(Select 'x' from
STUDENT
A2
where A1.STUDENT_NAME = A2.STUDENT_NAME
and A1.LESSON_NAME = A2.LESSON_NAME
and A1.NOTE = A2.NOTE
and A1.ROWID > A2.ROWID);
Views and Materialized Views
A view is a result set of a query
By using views, showing only relevant data to the users is provided.
Create view statement :
create view
<
v
iew_name>
as < query expression >
•
Materializing a view: create a physical table containing all the tuples
in the result of the query defining the view
(refresh is required)
create
materialize
view
<m
v
iew_name>
as < query expression >
S
E
Q
U
E
N
C
E
S
A sequence is a database object that generates numbers in sequential order.
Mostly used to generate primary key for a table
E
x
a
m
p
l
e
:
To create the sequence:
CREATE SEQUENCE customer_seq INCREMENT BY 1 START WITH 100
To use the sequence to enter a record into the database:
INSERT INTO customer (cust_num, name, address)
VALUES (customer_seq.NEXTVAL, 'John Doe', '123 Main St.‘)
;
T
R
I
G
G
E
R
S
•
Trigger is a procedure that is automatically run by DBMS in response to
specified changes to the database, and performs desired events
•
T
r
i
g
g
e
r
h
a
s
t
h
r
e
e
p
a
r
t
s
:
E
v
e
n
t
,
C
o
n
d
i
t
i
o
n
,
A
c
t
i
o
n
Trigger Types Are :
D
M
L
t
r
i
g
g
e
r
s
f
o
r
I
n
s
e
r
t
,
U
p
d
a
t
e
,
D
e
l
e
t
e
D
D
L
t
r
i
g
g
e
r
s
(
i
n
s
t
e
a
d
o
f
)
f
o
r
C
r
e
a
t
e
,
A
l
t
e
r
,
D
r
o
p
,
e
t
c
.
.
T
r
i
g
g
e
r
s
c
a
n
’
t
b
e
d
e
f
i
n
e
d
f
o
r
S
e
l
e
c
t
s
t
a
t
e
m
e
n
t
<!> Transactions statements (commit, savepoint,rollback)
can not be used in Triggers
c
r
e
a
t
e
o
r
r
e
p
l
a
c
e
t
r
ı
g
g
e
r
d
e
l
e
t
e
_
f
o
r
b
i
d
d
e
n
before drop on database
begın
raıse_applıcatıon_error ( -20000,’dropping table is forbidden’);
end;
c
r
e
a
t
e
o
r
r
e
p
l
a
c
e
t
r
i
g
g
e
r
t
r
i
g
g
e
r
_
1
b
e
f
o
r
e
i
n
s
e
r
t
o
n
t
b
l
1
r
e
f
e
r
e
n
c
ı
n
g
n
e
w
a
s
n
e
w
o
l
d
a
s
o
l
d
f
o
r
e
a
c
h
r
o
w
b
e
g
i
n
:
n
e
w
.
r
e
f
e
r
e
n
c
e
d
_
d
a
t
e
:
=
s
y
s
d
a
t
e
;
end tr_1;
T
R
I
G
G
E
R
E
X
A
M
P
L
E
S
SUMMARY
Basic SQL query has a Select, From,Where
Use Distinct to avoid duplicates at queries
SQL provides set operations: Union, Intersect, Minus,
Queries that have subqueries are called Nested Queries.
In, Exists, Unique, Any, All, Some is used for Nested Queries
Aggregators operators are Count, Sum, Average, Max,Min
Group by and Having
SQL provides using of Sequences and Triggers
K
e
y
P
o
i
n
t
s
:
Be careful about recursive triggers !
Think about check constraints instead of triggers for database consistency.
The Turkish Statistical Institute provides an in-depth look at SQL fundamentals covering main categories, basic SQL commands, set operations, aggregate functions, join operations, and more. SQL, or Structured Query Language, is a special-purpose programming language designed for managing data in RDBMS. The overview includes definitions, main categories, SQL commands, examples of basic SQL queries, and a focus on using SQL in managing relational databases.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
TURKISH STATISTICAL INSTITUTE SQL FUNDEMANTALS 31.03.2014 (Muscat, Oman) 1 /34
TURKISH STATISTICAL INSTITUTE OUTLINE Definition of SQL Main Categories Of Sql Basic Sql Commands Nested Queries and Subqueries Set Operators (minus, in, not in, all, some, intersect, exists) Aggregate Functions Join Operations Using View and Matearilized View Sequences Triggers And The Purpose Of Triggers 2 /34
TURKISH STATISTICAL INSTITUTE SQL DEFINITION SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a RDBMS First developed in the early 1970s at IBM Commercially released by Relational Software Inc. Became a standard of the American National Standards Institute (ANSI) in 1986 International Organization for Standards (ISO) in 1987 3 /34
TURKISH STATISTICAL INSTITUTE MAIN CATEGORIES 4 /34
TURKISH STATISTICAL INSTITUTE Sql Commands Overview CREATE TABLE <name> ( <field> <domain>, ) INSERT INTO <name> (<field names>) VALUES (<field values>) DELETE FROM <name> WHERE <condition> UPDATE <name> SET <field name> = <value> WHERE <condition> SELECT <fields> (distinct is usable here) FROM <name> WHERE <condition> 5 /34
TURKISH STATISTICAL INSTITUTE Some Examples Sailors Reserves sid 22 29 31 32 58 64 71 74 85 95 sname Dustin Brutus Lubber Andy Rusty Horatio Zorba Horatio Art Bob rating age 45.0 33.0 55.5 25.5 35.0 35.0 16.0 35.0 25.5 63.5 sid 22 22 22 22 31 31 31 64 64 74 bid 101 102 103 104 102 103 104 101 102 103 day 7 1 8 8 10.10.1998 10.10.1998 10.08.1998 10.07.1998 11.10.1998 11.06.1998 11.12.1998 09.05.1998 09.08.1998 09.08.1998 10 7 10 9 3 3 Boats bid 101 102 103 104 bname Interlake Interlake Clipper Marine color blue red green red *Live Demo : install and use a DBMS for SQL s 6 /34
TURKISH STATISTICAL INSTITUTE Examples of Basic SQL Queries Example : select distinct sname, age from sailors Q1 : Find all sailors with a rating above 7. Q2 : Find the sailors that rating>7 Q3 : Find the sailors that whose name starts with B letter Q4 : Find the names of the sailors who reserved at least one boat 7 /34
TURKISH STATISTICAL INSTITUTE Nested Queries and Subqueries A subquery is a select-from-where expression that is nested within another query. Example Query: Find courses offered in Fall 2009 and in Spring 2010 select distinct course_id from section where semester = Fall and year= 2009 and course_id in (select course_id from section where semester = Spring and year= 2010); 8 /34
TURKISH STATISTICAL INSTITUTE Using Set Operators (MINUS, NOT IN, NOT EXISTS, IN, EXISTS, INTERSECT) Syntax : Query1 <Set Operator> Query2 Example1: (difference) Select * from staff_1 Minus Select * from staff2; Select * from staff_1 where staff_number not in (select staff_number from staff_2); Select * from staff_1 a where not exists (select * staff_2 b where a.sid=b.sid); Example2: (intersect) Select * from staff_1 intersect Select * from staff2; Select * from staff_1 where staff_number in (select staff_number from staff_2); Select * from staff_1 a where exists (select * staff_2 b where a.sid=b.sid); 9 /34
TURKISH STATISTICAL INSTITUTE Exercises: Q5: Find the sailors that rating of the one equals 3 times of the other (hint: result is Art, Bob, Horatio) Q6:Find the names of sailors who have red or green boat Q7: Find the names of sailors who have reserved a red and a green boat Q8: Find the sname of sailors who have reserved red boats but not green boats *Live Demo 10 /34
TURKISH STATISTICAL INSTITUTE Answers: Q7 Answer -1 ( It is a complex query ) Select s.sname from sailors s, reserves r 1, boats b1, r 2, boats b2 where s.sid = r1.sid and r1.bid=b1.bid and s.sid = r2.sid and r2.bid=b2.bid and b1.color= red and b2.color= green ; Q7 Answer -2 Using Intersect operation (easily to understand, write) Select s.sname from sailors s, reserves r , boats b where s.sid = r.sid and r.bid=b.bid and b.color= red INTERSECT Select s2.sname from sailors s2, reserves r 2, boats b2 where s2.sid = r2.sid and r2.bid=b2.bid and b2.color= red ; 11 /34
TURKISH STATISTICAL INSTITUTE Q8 Answer: Find the sname of sailors who have reserved red boats but not green boats. Select s.sname from sailors s, reserves r , boats b where s.sid = r.sid and r.bid=b.bid and b.color= red MINUS Select s2.sname from sailors s2, reserves r2, boats b2 where s2.sid = r2.sid and r2.bid=b2.bid and b2.color= green ; 12 /34
TURKISH STATISTICAL INSTITUTE ALL, SOME,ANY They are used to compare two datasets Example: Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department. Answer-1 : select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = Biology ; 13 /34
TURKISH STATISTICAL INSTITUTE Same query using some clause select name from instructor where salary > some (select salary from instructor where dept_name = Biology ); Same query using any clause select name from instructor where salary > any (select salary from instructor where dept_name = Biology ); 14 /34
TURKISH STATISTICAL INSTITUTE Example Query for All Clause Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department. select name from instructor where salary > all (select salary from instructor where dept_name = Biology ); 15 /34
TURKISH STATISTICAL INSTITUTE UNION vs UNION ALL They concetane the result of two different SQLs. They differ in the way they handle duplicates. Selected columns need to be of the same data type There is a performance hit when using UNION vs UNION ALL, 16 /34
TURKISH STATISTICAL INSTITUTE AGGREGATE FUNCTIONS avg: average value min: minimum value max: maximum value sum: sum of values count: number of values Find the average salary of instructors in the Computer Science department : select avg (salary) from instructor where dept_name= Comp. Sci. ; Find the total number of instructors who teach a course in the Spring 2010 semester: select count (distinct ID) from teaches where semester = Spring and year = 2010; 17 /34
TURKISH STATISTICAL INSTITUTE ..GROUP BY Attributes in select clause outside of aggregate functions must appear in group by list /* erroneous query */ select city, student_name, count(*), avg(average_note) note from student group by city 18 /34
TURKISH STATISTICAL INSTITUTE Used for queries on single or multiple tables Clauses of the SELECT statement: SELECT List the columns (and expressions) that should be returned from the query FROM Indicate the table(s) or view(s) from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate columns to group the results HAVING Indicate the conditions under which a group will be included ORDER BY Sorts the result according to specified columns 19 /34
TURKISH STATISTICAL INSTITUTE AGGREGATE FUNCTIONS EXERCISES *Live Demo : Exercise: Using STUDENT table STUDENT(student_name, lesson_name, note) Find: a) The count of lessons for each student b) The average note, and maximum note of each student c) Find the students that have more than 1 record for the same lesson 20 /34
TURKISH STATISTICAL INSTITUTE Solutions of the Exercise: select student_name, max(note), avg(note), count(lesson_name) from student group by student_name; select student_name, lesson_name, count(*) from student group by student_name, lesson_name having count(*) > 1; 21 /34
TURKISH STATISTICAL INSTITUTE JOIN OPERATIONS Join operations take two relations and return as a result another relation. A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join The join operations are typically used as subquery expressions in the from clause 22 /34
TURKISH STATISTICAL INSTITUTE JOIN EXAMPLES Course : Department: 23 /34
TURKISH STATISTICAL INSTITUTE JOIN EXAMPLES (Cont d) select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1, department t2 where t1.dept_id = t2.dept_id; 24 /34
TURKISH STATISTICAL INSTITUTE JOIN EXAMPLES using Right Outer Join select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1, department t2 where t1.dept_id(+) = t2.dept_id; select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1 right outer join department t2 on t1.dept_id = t2.dept_id; 25 /34
TURKISH STATISTICAL INSTITUTE JOIN EXAMPLES using Left Outer Join select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1, department t2 where t1.dept_id = t2.dept_id(+); select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1 left outer join department t2 on t1.dept_id = t2.dept_id; 26 /34
TURKISH STATISTICAL INSTITUTE JOIN EXAMPLES using Full Outer Join select t1.course_id, t1.title, t1.credits, t2.* from COURSE t1 full outer join department t2 on t1.dept_id = t2.dept_id; 27 /34
TURKISH STATISTICAL INSTITUTE Some Easy but useful SQL statements Create table t1 as select * from t2; Select * from t1 where rownum < 100; Deleting duplicate records example: for table Student(student_name, lesson_name, note) delete from STUDENT A1 where exists (Select 'x' from STUDENT A2 where A1.STUDENT_NAME = A2.STUDENT_NAME and A1.LESSON_NAME = A2.LESSON_NAME and A1.NOTE = A2.NOTE and A1.ROWID > A2.ROWID); 28 /34
TURKISH STATISTICAL INSTITUTE Views and Materialized Views A view is a result set of a query By using views, showing only relevant data to the users is provided. Create view statement : create view <view_name> as < query expression > Materializing a view: create a physical table containing all the tuples in the result of the query defining the view (refresh is required) create materialize view <mview_name> as < query expression > 29 /34
TURKISH STATISTICAL INSTITUTE SEQUENCES A sequence is a database object that generates numbers in sequential order. Mostly used to generate primary key for a table Example: To create the sequence: CREATE SEQUENCE customer_seq INCREMENT BY 1 START WITH 100 To use the sequence to enter a record into the database: INSERT INTO customer (cust_num, name, address) VALUES (customer_seq.NEXTVAL, 'John Doe', '123 Main St. ); 30 /34
TURKISH STATISTICAL INSTITUTE TRIGGERS Trigger is a procedure that is automatically run by DBMS in response to specified changes to the database, and performs desired events Trigger has three parts : Event, Condition, Action Trigger Types Are : DML triggers for Insert, Update, Delete DDL triggers (instead of) for Create, Alter, Drop, etc.. Triggers can t be defined for Select statement <!> Transactions statements (commit, savepoint,rollback) can not be used in Triggers 31 /34
TURKISH STATISTICAL INSTITUTE TRIGGER EXAMPLES create or replace tr gger delete_forbidden before drop on database beg n ra se_appl cat on_error ( -20000, dropping table is forbidden ); end; create or replace trigger trigger_1 before insert on tbl1 referenc ng new as new old as old for each row begin :new.referenced_date := sysdate; end tr_1; 32 /34
TURKISH STATISTICAL INSTITUTE SUMMARY Basic SQL query has a Select, From,Where Use Distinct to avoid duplicates at queries SQL provides set operations: Union, Intersect, Minus, Queries that have subqueries are called Nested Queries. In, Exists, Unique, Any, All, Some is used for Nested Queries Aggregators operators are Count, Sum, Average, Max,Min Group by and Having SQL provides using of Sequences and Triggers Key Points: Be careful about recursive triggers ! Think about check constraints instead of triggers for database consistency. 33 /34
