ORACLE SQL TRAINING
NAME OF TRAINER :
[Link] PATEL & M. YASIR
2
Name of Students
SANA JHANGIR (SEMSTER 8TH )
IFRA AZIZ (SEMSTER 8TH )
SALMAN AHMED SIDDQUI(SEMSTER 8TH )
WALEED (SEMSTER 7TH )
HAMZA
ANWAR ALI (SEMSTER 7TH )
UMAIR HANIF (SEMSTER 7TH )
SHAZIL (SEMSTER 8TH )
[Link] HASHMI (SEMSTER 8TH )
SUMIYA
SARWAN JAWAID (7TH SEMESTER)
3
Scope
To Teach Beginner Level SQL to all
participants of this Course (of this
Project)
4
GOAL objective:
Discuss the theoretical and physical aspect of a
relation database.
Describe the oracle implementation of RDBMS and
ORDBMS.
Understand the goals of the course.
5
GOAL of the course
After the completing the course you should be able
to do the following.
Identify the major structure components of oracle
database 10g/11g.
Retrieve row and column data from tables with the
“SELECT” statement.
6
Objectives
Explore basic commands and functions of SQL
How to use SQL for data manipulation (to add,
modify, delete, and retrieve data)
How to use SQL to query a database to extract
useful information
How to use SQL for data administration (to create
tables, indexes, and views)
7
Introduction to SQL
SQL functions fit into two broad categories:
Data definition language
SQL includes commands to:
Create database objects, such as tables, indexes, and views
Define access rights to those database objects
Data manipulation language
Includes commands to insert, update, delete, and retrieve
data within database tables
8
Introduction to SQL
(continued)
SQL is relatively easy to learn
Basic command set has vocabulary of less than 100
words
Nonprocedural language
American National Standards Institute (ANSI)
prescribes a standard SQL
Several SQL dialects exist
9
Single Table Query Basics
The main element in a SQL query is the SELECT
statement.
A properly written SELECT statement will always
produce a result in the form of one or more rows of
output.
The SELECT statement chooses (selects) rows from
one or more tables according to specific criteria.
10
SELECT STATMENT
Selecting All Columns
SELECT *
FROM departments;
11
SELECT STATEMENT
The following SELECT statement produces an identical output as
mentioned in previous slide.
SELECT DEPARTMENT_ID,
DEPARTMENT_NAME,MANAGER_ID,LOACTION_ID FROM DEPARTMENTS;
12
Clauses of the SELECT
statement
SELECT
List the columns (and expressions) to 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 categorization of results
HAVING
Indicate the conditions under which a category (group) will be
included
ORDER BY
Sorts the result according to specified criteria
13
Selecting Specific Columns
14
Selecting Specific Columns
Specify the column names to be displayed in the result set by
typing the exact, complete column names.
Separate each column name with a comma (,).
Specify the name of the table after the FROM clause.
Terminate the query with a semi-colon (;).
SELECT emp_ssn, emp_last_name, emp_first_name FROM
employee;
15
Common Errors
There are syntactical rules that must be followed or
Oracle gives an error message instead of the
desired result table.
Oracle communicates errors in SELECT statements
by providing unique error numbers and
accompanying error descriptions.
16
FROM Keyword Missing
The next SELECT statement is missing the FROM
clause so that no table name has been specified.
Without a table name, the database management
system does not know which table to query.
SELECT emp_ssn;
ERROR at line 1:
ORA-00923: FROM keyword not found where
expected
17
Arithmetic Expressions
Create expressions with number and date data by using
arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
18
Using Arithmetic Operators
…
19
Selecting from the DUAL Table
DUAL is a table automatically created by Oracle Database along
with the data dictionary. DUAL is in the schema of the user SYS but
is accessible by the name DUAL to all users. It has one column,
DUMMY, defined to be VARCHAR2(1), and contains one row with
a value X. Selecting from the DUAL table is useful for computing a
constant expression with the SELECT statement. Because DUAL has
only one row, the constant is as there are rows in the table
returned only once. Alternatively, you can select a constant,
pseudo-column, or expression from any table, but the value will
be returned as many times
20
Selecting from the DUAL Table
21
DUAL STATMENTS
SQL> select 1+1 from dual;
SQL> select 1+1 from dual;
SQL> select 2*1+3*4 from dual;
SQL> select 2*((1+3)*4) from dual;
22
Defining a Column Alias
A column alias:
Renames a column heading
Is useful with calculations
Immediately follows the column name (There can
also be the optional AS keyword between the
column name and alias.)
Using Column Aliases
SELECT last_name AS name, commission_pct comm
FROM employees;
SELECT last_name "Name" , salary*12 "Annual Salary"
FROM employees;
…
24
Concatenation Operator
A concatenation operator:
Links columns or character strings to other columns
Is represented by two vertical bars (||)
Creates a resultant column that is a character
expression
SELECT last_name||job_id AS "Employees"
FROM employees;
…
25
Literal Character Strings
A literal is a character, a number, or a date that is
included in the SELECT statement.
Date and character literal values must be enclosed
by single quotation marks.
Each character string is output once for each
row returned.
26
Using Literal Character Strings