Basics of SELECT Statement - Oracle SQL

A short course on "SQL Fundamentals - Oracle 11g". All practicals are performed in the embedded video. Video can be separately found at given link
https://youtu.be/1OxaHBEVubg

1. SQL Fundamentals Oracle 11g
MUHAMMAD WAHEED
ORACLE DATABASE DEVELOPER
EMAIL:[email protected]
Lecture#5
Basics of SELECT Statement

2. Transaction Properties
•All database transactions are ACID.
Atomicity
Consistency
Isolation
Durability
2

3. Transaction Properties(cont’d)
•All database transactions are ACID.
Atomicity: The entire sequence of actions must be either
completed or aborted. The transaction cannot be partially
successful.
Consistency: The transaction takes the resources from one
consistent state to another.
Isolation:A transaction's effect is not visible to other
transactions until the transaction is committed.
Durability:Changes made by the committed transaction are
permanent and must survive system failure.
3

4. SELECT Statement
•SELECT identifies what columns.
•FROM identifies which table.
4

5. Arithmetic Expressions
•Create expressions with number and date by using following
arithmetic operators:
Divide ( / )
Multiply ( * )
Add ( + )
Subtract ( -)
•You can use arithmetic operators in any SQL clause except FROM
clause.
•Operator precedence is applied by default. We need to use
parenthesis for custom precedence.
5

6. Arithmetic Expressions(cont’d)
•Example:
SELECT std_name,std_age+ 30
FROM student;
•Example:
SELECT std_name,std_marks+ 10/100
FROM student;
*enforcement of custom operator precedence is (std_marks+ 10)/100.
6

7. Aliases
•Oracle ALIASES can be used to create a temporary name for
columns or tables.
•It has two types: column alias, table alias
•COLUMN ALIASES are used to make column headings in
your result set easier to read.
•TABLE ALIASES are used to shorten your SQL to make it
easier to read or when you are listing the same table more
than once in the FROM clause.
7

8. Aliases(cont’d)
•Syntax:
Column Alias : <column_name> AS <alias_name>
Table Alias : <table_name> <alias_name>
•Alias name can not contain special characters but if it
is desired then use double-quotes i.e.
“<alias_name>”.
•*Remember: ‘AS’ is only used with column not table.
8

9. Column Aliases(cont’d)
•Example:
SELECT std_idAS ID , std_nameAS NAME
FROM student;
•To have special character in alias name follow following example:
SELECT std_idAS “Student ID” , std_nameAS “Student Name”
FROM student;
or
SELECT std_idAS id, std_nameAS "Student Name" FROM student;
9

10. Table Aliases(cont’d)
•Example:
SELECT s.std_id, s.std_name
FROM student s;
•To have special character in alias name follow following
example:
SELECT s.std_idAS “Student ID” , s.std_nameAS “Student
Name”
FROM student s;
10

11. Aliases(cont’d)
11

12. Concatenation Operator
12
•Concatenates columns or character strings to other
column.
•It is represented by vertical bars ( || ).

13. Concatenation(cont’d)
13
•Example:
SELECT std_id||std_nameFROM student;
•Example:
SELECT std_id||std_nameAS “name”FROMstudent;
•Example:
SELECT std_id||std_name||std_ageFROM student;

14. Concatenation(cont’d)
14
•Using Literal character string.
•It is neither column name nor a column alias rather it
is a string enclosed in single quotes.
•Example:
SELECT std_name|| ‘is’ || age || ‘years old’ AS
“Student Age”
FROM student;

15. Duplicate Records
15
•By default SELECT selects all rows from a table
including duplicate records.
•Duplicate records can be eliminated by using
keyword “DISTINCT”.
•Example:
SELECT DISTINCT std_nameFROM student;

16. View Table Structure
16
•Syntax:
DESC[CRIBE] <table_name>;
bracket’s enclosed part is optional.

17. View Table SQL Structure
17
•Syntax:
SELECT
DBMS_METADATA.GET_DDL(‘TABLE’,‘<table_name>'[,‘<user
_name/schema>']) from DUAL;
bracket’s enclosed part is optional.

18. WHERE Clause
18
•Restricts the records to be displayed.
•Character string and date are enclosed in single
quotes.
•Character values are case sensitive while date values
are format sensitive.
•Default date format is ‘DD-MON-YY’.

19. Comparison Conditions/Operators
19
•There are following conditions:
=
>=
<
<=
<> (not equal to)
•Example:
SELECT * FROM student WHERE std_id<>1;

20. Comparison
Conditions/Operators(cont’d)
20
•Few other operators/conditions are:
> BETWEEN <lower_limit> AND <upper_limit>
> IN/MEMBERSHIP (set/list of values)
> LIKE
> IS NULL

21. BETWEEN … AND … Condition
21
•Use the BETWEEN condition to display rows based on
a range of values.
•Example:
SELECT * FROM student
WHERE std_ageBETWEEN 17 AND 21;

22. IN/ MEMBERSHIP Condition
22
•Use to test/compare values from list of available
values.
•Example: let we have library defaulters with IDs
102,140,450 etc.
SELECT * FROM student
WHERE std_idIN ( 102,140,450);

23. Wildcard
23
•Databases often treat % and _ as wildcard characters.
•Pattern-match queries in the SQL repository (such as
CONTAINS, STARTS WITH, or ENDS WITH), assume
that a query that includes % or _ is intended as a
literal search including those characters and is not
intended to include wildcard characters.

24. LIKE Condition/Operator
24
•The Oracle LIKE condition allows wildcards to be
used in the WHERE clause of a SELECT, INSERT,
UPDATE, or DELETE statement. This allows you to
perform pattern matching.

25. LIKE Condition/Operator(cont’d)
25
•The SQL LIKE clause is used to compare a value to the
existing records in the database records
partially/fully.
•There are two wildcards used in conjunction with the
LIKE operator:
percent sign (%)
underscore (_)

26. LIKE Criteria Example(cont’d)
26

27. NULL Condition
27
•Compare records to find NULL values by using ‘IS
NULL’.
•Example: all students having no contact info
SELECT * FROM student
WHERE std_contactIS NULL;

28. LOGICAL Conditions
•A logical condition combines the result of two columns or inverts the value of a single column.
•There are following logical operators:
AND
OR
NOT
28

29. AND -Logical Conditions(cont’d)
•AND requires both conditions to be true.
•Example:
SELECT * FROM student WHERE std_id=1 AND age=21;
•Example:
SELECT * FROM student WHERE std_id<=5 AND std_nameLIKE ‘%a%’;
29

30. OR -Logical Conditions(cont’d)
•OR requires both conditions to be true.
•Example:
SELECT * FROM student WHERE std_id=1 OR age=21;
•Example:
SELECT * FROM student WHERE std_id<=5 OR std_nameLIKE ‘%a%’;
30

31. NOT -Logical Conditions(cont’d)
•NOT inverses the resulting value.
•NOT is used with other SQL operators e.gBETWEEN,IN,LIKE.
•Examples:
SELECT * FROM student
WHERE std_idNOT BETWEEN 1 AND 5;
WHERE std_idNOT IN (101,140,450);
WHERE std_nameNOT LIKE ‘%A%’;
WHERE std_contactIS NOT NULL;
•Example:
SELECT * FROM student WHERE std_id<=5 OR std_nameLIKE ‘%a%’;
31

32. Rules of Precedence
32

33. Rules of Precedence(cont’d)
•Example:
SELECT * FROM student
WHERE tch_nameLIKE ‘%a%’
OR tch_id>5
AND tch_salary>=20000;
*it evaluates as “select all columns if tch_idis greater than 5 and tch_salaryis greater or equal
to 20000, or if the tch_namecontaining ‘a’.
** to perform the OR operation first apply parenthesis.
33

34. ORDER BY Clause
•Sort the resulting rows in following orders:
ASC: ascending order(by default)
DESC: descending order
•It is used with select statement.
34

35. ORDER BY Clause(cont’d)
•Example:
SELECT * FROM student
ORDER BY dob;
or
SELECT * FROM student
ORDER BY dobDESC;
•ORDER BY on multiple columns:
SELECT * FROM student
ORDER BY dob,std_nameDESC;
35

36. Motivational Speaking
36

37. Feedback/Suggestions?
Give your feedback at: [email protected]