Writing Basic SQL SELECT Statements Lecture
W
R
I
T
I
N
G
B
A
S
I
C
S
Q
L
S
E
L
E
C
T
S
T
A
T
E
M
E
N
T
S
L
e
c
t
u
r
e
7
-
1
1
O
u
t
l
i
n
e
s
SQL SELECT statement
Capabilities of SELECT statements
Basic SELECT statement
Selecting all columns
Selecting specific columns
Arithmetic Expressions
▪
Using Arithmetic operators
▪
Arithmetic operators precedence
▪
NULL values in arithmetic expression
Using column aliases
Concatenation operator
Literal Character String
Ghadah Al Hadba
2
C
a
p
a
b
i
l
i
t
i
e
s
o
f
S
Q
L
S
E
L
E
C
T
S
t
a
t
e
m
e
n
t
s
A
S
E
L
E
C
T
s
t
a
t
e
m
e
n
t
r
e
t
r
i
e
v
e
s
i
n
f
o
r
m
a
t
i
o
n
f
r
o
m
t
h
e
d
a
t
a
b
a
s
e
.
Using a SELECT statement, you can do the following:
o
P
r
o
j
e
c
t
i
o
n
:
Y
o
u
c
a
n
u
s
e
t
h
e
p
r
o
j
e
c
t
i
o
n
c
a
p
a
b
i
l
i
t
y
i
n
S
Q
L
t
o
c
h
o
o
s
e
t
h
e
c
o
l
u
m
n
s
i
n
a
t
a
b
l
e
t
h
a
t
y
o
u
w
a
n
t
r
e
t
u
r
n
e
d
b
y
y
o
u
r
q
u
e
r
y
.
Y
o
u
c
a
n
c
h
o
o
s
e
a
s
f
e
w
o
r
a
s
m
a
n
y
c
o
l
u
m
n
s
o
f
t
h
e
t
a
b
l
e
a
s
y
o
u
r
e
q
u
i
r
e
.
o
S
e
l
e
c
t
i
o
n
:
Y
o
u
c
a
n
u
s
e
t
h
e
s
e
l
e
c
t
i
o
n
c
a
p
a
b
i
l
i
t
y
i
n
S
Q
L
t
o
c
h
o
o
s
e
t
h
e
r
o
w
s
i
n
a
t
a
b
l
e
t
h
a
t
y
o
u
w
a
n
t
r
e
t
u
r
n
e
d
b
y
a
q
u
e
r
y
.
Y
o
u
c
a
n
u
s
e
v
a
r
i
o
u
s
c
r
i
t
e
r
i
a
t
o
r
e
s
t
r
i
c
t
t
h
e
r
o
w
s
t
h
a
t
y
o
u
s
e
e
.
o
J
o
i
n
i
n
g
:
Y
o
u
c
a
n
u
s
e
t
h
e
j
o
i
n
c
a
p
a
b
i
l
i
t
y
i
n
S
Q
L
t
o
b
r
i
n
g
t
o
g
e
t
h
e
r
d
a
t
a
t
h
a
t
i
s
s
t
o
r
e
d
i
n
d
i
f
f
e
r
e
n
t
t
a
b
l
e
s
b
y
c
r
e
a
t
i
n
g
a
l
i
n
k
b
e
t
w
e
e
n
t
h
e
m
.
Ghadah Al Hadba
3
C
a
p
a
b
i
l
i
t
i
e
s
o
f
S
Q
L
S
E
L
E
C
T
S
t
a
t
e
m
e
n
t
s
(
C
o
n
t
.
)
Ghadah Al Hadba
4
B
a
s
i
c
S
E
L
E
C
T
S
t
a
t
e
m
e
n
t
S
E
L
E
C
T
[
D
I
S
T
I
N
C
T
,
*
]
c
o
l
u
m
n
s
n
a
m
e
s
|
e
x
p
r
e
s
s
i
o
n
[
a
l
i
a
s
]
F
R
O
M
t
a
b
l
e
;
Ghadah Al Hadba
5
N
o
t
e
t
h
a
t
:
S
E
L
E
C
T
c
l
a
u
s
e
i
d
e
n
t
i
f
i
e
s
w
h
a
t
c
o
l
u
m
n
s
t
o
r
e
t
r
i
e
v
e
.
F
R
O
M
c
l
a
u
s
e
s
p
e
c
i
f
i
e
s
t
h
e
t
a
b
l
e
c
o
n
t
a
i
n
i
n
g
t
h
e
c
o
l
u
m
n
s
l
i
s
t
e
d
i
n
t
h
e
S
E
L
E
C
T
c
l
a
u
s
e
.
S
e
l
e
c
t
S
t
a
t
e
m
e
n
t
S
y
n
t
a
x
:
B
a
s
i
c
S
E
L
E
C
T
S
t
a
t
e
m
e
n
t
Ghadah Al Hadba
6
S
e
l
e
c
t
i
n
g
A
l
l
C
o
l
u
m
n
s
Ghadah Al Hadba
7
S
E
L
E
C
T
*
F
R
O
M
t
a
b
l
e
;
S
y
n
t
a
x
:
M
e
a
n
s
a
l
l
c
o
l
u
m
n
s
S
e
l
e
c
t
i
n
g
A
l
l
C
o
l
u
m
n
s
(
E
x
a
m
p
l
e
)
Ghadah Al Hadba
8
S
e
l
e
c
t
i
n
g
A
l
l
C
o
l
u
m
n
s
(
E
x
a
m
p
l
e
)
In the example on the slide, the department table contains four
columns:
DEPARTMENT_ID, DEPARTMENT_NAME,
MANAGER_ID, and LOCATION_ID
.
The table contains eight rows, one for each department.
N
o
t
e
T
h
a
t
:
Y
o
u
c
a
n
a
l
s
o
d
i
s
p
l
a
y
a
l
l
c
o
l
u
m
n
s
i
n
t
h
e
t
a
b
l
e
b
y
l
i
s
t
i
n
g
a
l
l
t
h
e
c
o
l
u
m
n
s
a
f
t
e
r
t
h
e
S
E
L
E
C
T
E
.
g
.
S
E
L
E
C
T
d
e
p
a
r
t
m
e
n
t
_
i
d
,
d
e
p
a
r
t
m
e
n
t
_
n
a
m
e
,
m
a
n
a
g
e
r
_
i
d
,
l
o
c
a
t
i
o
n
_
i
d
F
R
O
M
d
e
p
a
r
t
m
e
n
t
s
;
T
h
e
p
r
e
v
i
e
w
s
S
Q
L
s
t
a
t
e
m
e
n
t
,
l
i
k
e
t
h
e
e
x
a
m
p
l
e
o
n
t
h
e
s
l
i
d
e
,
w
i
l
l
d
i
s
p
l
a
y
a
l
l
c
o
l
u
m
n
s
a
n
d
a
l
l
r
o
w
s
o
f
t
h
e
D
E
P
A
R
T
M
E
N
T
S
t
a
b
l
e
:
Ghadah Al Hadba
9
S
e
l
e
c
t
i
n
g
S
p
e
c
i
f
i
c
C
o
l
u
m
n
s
Y
o
u
c
a
n
u
s
e
t
h
e
S
E
L
E
C
T
s
t
a
t
e
m
e
n
t
t
o
d
i
s
p
l
a
y
s
p
e
c
i
f
i
c
c
o
l
u
m
n
s
o
f
t
h
e
t
a
b
l
e
b
y
s
p
e
c
i
f
y
i
n
g
t
h
e
c
o
l
u
m
n
n
a
m
e
s
,
s
e
p
a
r
a
t
e
d
b
y
c
o
m
m
a
s
.
(
s
e
e
E
x
a
m
p
l
e
-
1
-
)
In the SELECT clause, specify the columns that you want
to display
in the order in which you want them to appear in
the output
. (
see Example -2-
)
Ghadah Al Hadba
10
10
E
x
a
m
p
l
e
-
1
-
Ghadah Al Hadba
11
11
E
x
a
m
p
l
e
-
2
-
Ghadah Al Hadba
12
12
.
.
A
r
i
t
h
m
e
t
i
c
E
x
p
r
e
s
s
i
o
n
s
•
C
r
e
a
t
e
e
x
p
r
e
s
s
i
o
n
s
w
i
t
h
n
u
m
b
e
r
a
n
d
d
a
t
e
d
a
t
a
,
i
.
e
.
c
o
l
u
m
n
n
a
m
e
s
t
h
a
t
c
o
n
t
a
i
n
s
o
n
l
y
n
u
m
e
r
i
c
o
r
d
a
t
e
d
a
t
a
,
b
y
u
s
i
n
g
a
r
i
t
h
m
e
t
i
c
o
p
e
r
a
t
o
r
s
Ghadah Al Hadba
13
13
U
s
i
n
g
A
r
i
t
h
m
e
t
i
c
O
p
e
r
a
t
o
r
s
Ghadah Al Hadba
14
14
U
s
i
n
g
A
r
i
t
h
m
e
t
i
c
O
p
e
r
a
t
o
r
s
(
C
o
n
t
.
)
The previews example uses the addition operator to
calculate a salary increase of $300 for all employees and
displays a new SALARY+300 column in the output.
N
o
t
e
t
h
a
t
t
h
e
r
e
s
u
l
t
a
n
t
c
a
l
c
u
l
a
t
e
d
c
o
l
u
m
n
S
A
L
A
R
Y
+
3
0
0
i
s
n
o
t
a
n
e
w
c
o
l
u
m
n
i
n
t
h
e
E
M
P
L
O
Y
E
E
S
t
a
b
l
e
;
i
t
i
s
f
o
r
d
i
s
p
l
a
y
o
n
l
y
.
W
h
e
r
e
b
y
d
e
f
a
u
l
t
,
t
h
e
n
a
m
e
o
f
a
n
e
w
c
o
l
u
m
n
c
o
m
e
s
f
r
o
m
t
h
e
c
a
l
c
u
l
a
t
i
o
n
t
h
a
t
g
e
n
e
r
a
t
e
d
i
t
—
i
n
t
h
i
s
c
a
s
e
,
(
s
a
l
a
r
y
+
3
0
0
)
.
Ghadah Al Hadba
15
15
O
p
e
r
a
t
o
r
P
r
e
c
e
d
e
n
c
e
*
/
+
_
M
u
l
t
i
p
l
i
c
a
t
i
o
n
a
n
d
d
i
v
i
s
i
o
n
t
a
k
e
p
r
i
o
r
i
t
y
o
v
e
r
a
d
d
i
t
i
o
n
a
n
d
s
u
b
t
r
a
c
t
i
o
n
.
O
p
e
r
a
t
o
r
s
o
f
t
h
e
s
a
m
e
p
r
i
o
r
i
t
y
a
r
e
e
v
a
l
u
a
t
e
d
f
r
o
m
l
e
f
t
t
o
r
i
g
h
t
.
P
a
r
e
n
t
h
e
s
e
s
a
r
e
u
s
e
d
t
o
f
o
r
c
e
p
r
i
o
r
i
t
i
z
e
d
e
v
a
l
u
a
t
i
o
n
a
n
d
t
o
c
l
a
r
i
f
y
s
t
a
t
e
m
e
n
t
s
.
Ghadah Al Hadba
16
16
O
p
e
r
a
t
o
r
P
r
e
c
e
d
e
n
c
e
S
E
L
E
C
T
l
a
s
t
_
n
a
m
e
,
s
a
l
a
r
y
,
1
2
*
s
a
l
a
r
y
+
1
0
0
F
R
O
M
e
m
p
l
o
y
e
e
s
;
S
E
L
E
C
T
l
a
s
t
_
n
a
m
e
,
s
a
l
a
r
y
,
1
2
*
(
s
a
l
a
r
y
+
1
0
0
)
F
R
O
M
e
m
p
l
o
y
e
e
s
;
Ghadah Al Hadba
17
17
1
2
2
1
W
h
y
?
N
u
l
l
V
a
l
u
e
s
i
n
A
r
i
t
h
m
e
t
i
c
E
x
p
r
e
s
s
i
o
n
s
A
r
i
t
h
m
e
t
i
c
e
x
p
r
e
s
s
i
o
n
s
c
o
n
t
a
i
n
i
n
g
a
n
u
l
l
v
a
l
u
e
e
v
a
l
u
a
t
e
t
o
n
u
l
l
.
If any column value in an arithmetic expression is null,
the result is null.
For example
, if you attempt to perform division with
zero, you get an error. However, if you divide a number
by null, the result is a null or unknown.
Ghadah Al Hadba
18
18
N
u
l
l
V
a
l
u
e
s
i
n
A
r
i
t
h
m
e
t
i
c
E
x
p
r
e
s
s
i
o
n
s
Ghadah Al Hadba
19
19
U
s
i
n
g
C
o
l
u
m
n
A
l
i
a
s
e
s
Rename a column heading
Is useful with calculations
I
m
m
e
d
i
a
t
e
l
y
f
o
l
l
o
w
s
t
h
e
c
o
l
u
m
n
n
a
m
e
,
a
n
d
t
h
e
r
e
a
l
s
o
c
a
n
b
e
t
h
e
o
p
t
i
o
n
a
l
A
S
k
e
y
w
o
r
d
b
e
t
w
e
e
n
t
h
e
c
o
l
u
m
n
a
n
d
t
h
e
a
l
i
a
s
Requires double quotation marks (
“ “
)if the alias:
Contains space
Contains a special characters (such as # or $)
Is a case sensitive
Ghadah Al Hadba
20
20
U
s
i
n
g
C
o
l
u
m
n
A
l
i
a
s
e
s
(
E
x
a
m
p
l
e
s
)
Ghadah Al Hadba
21
21
In this lecture, you will learn about the capabilities of SQL SELECT statements, including projection, selection, and joining data. The lecture covers topics such as selecting all columns, selecting specific columns, arithmetic expressions, using arithmetic operators, and handling NULL values in expressions. Gain insights into the syntax of SELECT statements, how to retrieve information from databases, and more.
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
1 WRITING BASIC SQL SELECT STATEMENTS Lecture 7 -1
2 Outlines SQL SELECT statement Capabilities of SELECT statements Basic SELECT statement Selecting all columns Selecting specific columns Arithmetic Expressions Using Arithmetic operators Arithmetic operators precedence NULL values in arithmetic expression Using column aliases Concatenation operator Literal Character String Ghadah Al Hadba
3 Capabilities of SQL SELECT Statements A SELECT statement retrieves information from the database. Using a SELECT statement, you can do the following: o Projection: You can use the projection capability in SQL to choose the columns in a table that you want returned by your query. You can choose as few or as many columns of the table as you require. o Selection: You can use the selection capability in SQL to choose the rows in a table that you want returned by a query. You can use various criteria to restrict the rows that you see. o Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them. Ghadah Al Hadba
4 Capabilities of SQL SELECT Statements (Cont.) Ghadah Al Hadba
5 Basic SELECT Statement Select Statement Syntax: SELECT [DISTINCT,*]columns names| expression [alias] FROM table; Note that: SELECT clause identifies what columns to retrieve. FROM clause specifies the table containing the columns listed in the SELECT clause. Ghadah Al Hadba
6 Basic SELECT Statement Ghadah Al Hadba
7 Selecting All Columns Syntax: SELECT * FROM table; Means all columns Ghadah Al Hadba
8 Selecting All Columns (Example) Ghadah Al Hadba
9 Selecting All Columns (Example) In the example on the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department. Note That : You can also display all columns in the table by listing all the columns after the SELECT E.g. SELECT department_id, department_name, manager_id, location_ id FROM departments; The previews SQL statement, like the example on the slide, will display all columns and all rows of the DEPARTMENTS table: Ghadah Al Hadba
10 Selecting Specific Columns You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas.(see Example -1-) In the SELECT clause, specify the columns that you want to display in the order in which you want them to appear in the output. (see Example -2-) Ghadah Al Hadba
11 Example-1- Ghadah Al Hadba
12 Example-2- . . Ghadah Al Hadba
13 Arithmetic Expressions Create expressions with number and date data, i.e. column names that contains only numeric or date data, by using arithmetic operators operator Description Add + Subtract - * / Multiply Divide Ghadah Al Hadba
14 Using Arithmetic Operators Ghadah Al Hadba
15 Using Arithmetic Operators (Cont.) The previews example uses the addition operator to calculate a salary increase of $300 for all employees and displays a new SALARY+300 column in the output. Note that the resultant calculated column SALARY+300 is not a new column in the EMPLOYEES table; it is for display only. Where by default, the name of a new column comes from the calculation that generated it in this case, (salary+300). Ghadah Al Hadba
16 Operator Precedence * / + _ Multiplication and division take priority over addition and subtraction. Operators of the same priority are evaluated from left to right. Parentheses are used to force prioritized evaluation and to clarify statements. Ghadah Al Hadba
17 Operator Precedence SELECT last_name, salary, 12*salary+100 FROM employees; 1 2 SELECT last_name, salary, 12*(salary+100) FROM employees; Why? 2 1 Ghadah Al Hadba
18 Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. If any column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division with zero, you get an error. However, if you divide a number by null, the result is a null or unknown. Ghadah Al Hadba
19 Null Values in Arithmetic Expressions Ghadah Al Hadba
20 Using Column Aliases Rename a column heading Is useful with calculations Immediately follows the column name, and there also can be the optional AS keyword between the column and the alias Requires double quotation marks ( )if the alias: Contains space Contains a special characters (such as # or $) Is a case sensitive Ghadah Al Hadba
21 Using Column Aliases (Examples) Ghadah Al Hadba
