SQL Query Examples and Operators
SQL 3
Where Clause Predicates
•
SQL includes a
between
comparison operator
•
Example: Find the names of all instructors with salary between $90,000 and $100,000 (that is,
$90,000 and
$100,000)
•
select
name
from
instructor
where
salary
between
90000
and
100000
Example
:
Find the Customer_id, amount and orders_id of all order with
amount between 25 and 45 on 1/5/2022
Aggregate Functions Examples
•
Find the total number of instructors who teach a course in the Spring 2018 semester
•
select count
(
distinct
ID
)
from
teaches
where
semester
= 'Spring'
and
year
= 2018;
Example: Find the total
number of customer
who made an orders on
‘2022/05/01’
Aggregate Functions – Having Clause
•
Find the names and average salaries of all departments whose average salary is
greater than 42000
•
Note: predicates in the
having
clause are applied after the formation of groups
whereas predicates in the
where
clause are applied before forming groups
s
e
l
e
c
t
d
e
p
t
_
n
a
m
e
,
a
v
g
(
s
a
l
a
r
y
)
a
s
a
v
g
_
s
a
l
a
r
y
f
r
o
m
i
n
s
t
r
u
c
t
o
r
g
r
o
u
p
b
y
d
e
p
t
_
n
a
m
e
h
a
v
i
n
g
a
v
g
(
s
a
l
a
r
y
)
>
4
2
0
0
0
;
Nested Subqueries
•
SQL provides a mechanism for the nesting of subqueries. A
subquery
is a
select-from-where
expression that is
nested within another query.
•
The nesting can be done in the following SQL query
select
A
1
,
A
2
, ...,
A
n
from
r
1
,
r
2
, ...,
r
m
where
P
as follows:
•
From clause:
r
i
can be replaced by any valid subquery
•
Where clause:
P
can be replaced with an expression of the form:
B
<operation> (subquery)
B
is an attribute and <operation> to be defined later.
•
Select clause:
A
i
can be replaced be a subquery that generates a single value
.
Set Membership
•
Find courses offered in Fall 2017 and in Spring 2018
•
Find courses offered in Fall 2017 but not in Spring 2018
s
e
l
e
c
t
d
i
s
t
i
n
c
t
c
o
u
r
s
e
_
i
d
f
r
o
m
s
e
c
t
i
o
n
w
h
e
r
e
s
e
m
e
s
t
e
r
=
'
F
a
l
l
'
a
n
d
y
e
a
r
=
2
0
1
7
a
n
d
c
o
u
r
s
e
_
i
d
i
n
(
s
e
l
e
c
t
c
o
u
r
s
e
_
i
d
f
r
o
m
s
e
c
t
i
o
n
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
8
)
;
s
e
l
e
c
t
d
i
s
t
i
n
c
t
c
o
u
r
s
e
_
i
d
f
r
o
m
s
e
c
t
i
o
n
w
h
e
r
e
s
e
m
e
s
t
e
r
=
'
F
a
l
l
'
a
n
d
y
e
a
r
=
2
0
1
7
a
n
d
c
o
u
r
s
e
_
i
d
n
o
t
i
n
(
s
e
l
e
c
t
c
o
u
r
s
e
_
i
d
f
r
o
m
s
e
c
t
i
o
n
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
8
)
;
U
p
d
a
t
e
s
•
Give a 5% salary raise to all instructors
update
instructor
set
salary
=
salary
* 1.05
•
Give a 5% salary raise to those instructors who
earn
less than 70000
update
instructor
set
salary
=
salary
* 1.05
where
salary
< 70000;
•
Give a 5% salary raise to instructors whose salary is less than average
update
instructor
set
salary
=
salary
* 1.05
where
salary
< (
select avg
(salary)
from
instructor
);
Updates (Cont.)
•
Increase salaries of instructors whose salary is over $100,000 by 3%, and all others
by a 5%
•
Write two
update
statements:
update
instructor
set
salary
=
salary
* 1.03
where
salary
> 100000;
update
instructor
set
salary
=
salary
* 1.05
where
salary
<= 100000;
•
The order is important
•
Can be done better using
the
case
statement
(next slide)
CASE Syntax
•
CASE
WHEN
condition1
THEN
result1
WHEN
condition2
THEN
result2
WHEN
conditionN
THEN
resultN
ELSE
result
END
;
Case Statement for Conditional Updates
•
Same query as before but with case statement
update
instructor
set
salary
=
case
when
salary
<= 100000
then
salary
* 1.05
else
salary
* 1.03
end
Case Statement for Conditional Updates
•
Write the SQL statement by using case to
•
The company want to give 10% discount for all orders amount with
value greater than 30 JD and 5 % on amount under 30 JD only on
2/5/2022
•
Check by yourself how to make the discount first
Case Statement for
SELECT
SELECT orders_id, amount,
CASE
WHEN amount > 24 THEN 'The amount is greater than 25'
WHEN amount = 22 THEN 'The amount is 22'
ELSE 'The amount is under 22'
END
AS amountText
FROM ORDERS;
Example
•
Write the SQL statement show that
•
If employee earn over 5000 as salary then s\he is a boss
•
If employee earn less than 5000 and over 4000 as salary then s\he is a
manager
•
If employee earn between 2000 and 1500 as salary then s\he is a
worker
•
Else he is trainee
This comprehensive guide provides examples of SQL queries utilizing various operators such as the BETWEEN comparison operator, aggregate functions, having clause, nested subqueries, and set membership. It covers practical examples including finding instructors within a salary range, counting customers and orders, calculating average salaries, and examining course offerings in different semesters.
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. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Where Clause Predicates SQL includes a between comparison operator Example: Find the names of all instructors with salary between $90,000 and $100,000 (that is, $90,000 and $100,000) select name from instructor where salary between 90000 and 100000 Example: Find the Customer_id, amount and orders_id of all order with amount between 25 and 45 on 1/5/2022
Aggregate Functions Examples Find the total number of instructors who teach a course in the Spring 2018 semester select count (distinct ID) from teaches where semester = 'Spring' and year = 2018; Example: Find the total number of customer who made an orders on 2022/05/01
Aggregate Functions Having Clause Find the names and average salaries of all departments whose average salary is greater than 42000 select dept_name, avg (salary) asavg_salary from instructor group by dept_name having avg (salary) > 42000; Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups
Nested Subqueries SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another query. The nesting can be done in the following SQL query select A1, A2, ..., An fromr1, r2, ..., rm where P as follows: From clause: ri can be replaced by any valid subquery Where clause: P can be replaced with an expression of the form: B <operation> (subquery) B is an attribute and <operation> to be defined later. Select clause: Ai can be replaced be a subquery that generates a single value.
Set Membership Find courses offered in Fall 2017 and in Spring 2018 select distinct course_id from section where semester = 'Fall' and year= 2017 and course_id in (select course_id from section where semester = 'Spring' and year= 2018); Find courses offered in Fall 2017 but not in Spring 2018 select distinct course_id from section where semester = 'Fall' and year= 2017 and course_id not in (select course_id from section where semester = 'Spring' and year= 2018);
Updates Updates Give a 5% salary raise to all instructors update instructor set salary = salary * 1.05 Give a 5% salary raise to those instructors who earn less than 70000 update instructor set salary = salary * 1.05 where salary < 70000; Give a 5% salary raise to instructors whose salary is less than average update instructor set salary = salary * 1.05 where salary < (select avg (salary) from instructor);
Updates (Cont.) Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5% Write two update statements: update instructor set salary = salary * 1.03 where salary > 100000; update instructor set salary = salary * 1.05 where salary <= 100000; The order is important Can be done better using the case statement (next slide)
CASE Syntax CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
Case Statement for Conditional Updates Same query as before but with case statement update instructor set salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03 end
Case Statement for Conditional Updates Write the SQL statement by using case to The company want to give 10% discount for all orders amount with value greater than 30 JD and 5 % on amount under 30 JD only on 2/5/2022 Check by yourself how to make the discount first
Case Statement for SELECT SELECT orders_id, amount, CASE WHEN amount > 24 THEN 'The amount is greater than 25' WHEN amount = 22 THEN 'The amount is 22' ELSE 'The amount is under 22' END AS amountText FROM ORDERS;
Example Write the SQL statement show that If employee earn over 5000 as salary then s\he is a boss If employee earn less than 5000 and over 4000 as salary then s\he is a manager If employee earn between 2000 and 1500 as salary then s\he is a worker Else he is trainee
