Oracle SQL
[Link] Basic Select Statement
Eng:- Ahmed Ramadan , Eng:- Marwan Elkordey
Chapter01:Writing Basic Select Statement
Introduction
What is Database ? Collection of related data
Employees Departments
Employee number Employee name Department No Department NO Department name Loc
7788 SCOTT 10 10 Accounting Alex
7900 ALEN 20 20 HR Alex
7892 Ward
80 30 Inventory Cairo
40 Purchasing Cairo
8888 Turner 40
What is Table ?
Two dimensional structure consists of rows and columns and used to store data
Chapter01:Writing Basic Select Statement
Introduction
SQL ( Structure Query Language ) Commands
[Link] retrieval Language ( DRL ) Select writing basic select statement
using oracle single row function
using group functions
joining tables
sub-queries
2. Data Manipulation Language ( DML ) - insert
Update
Delete
Merge
3. Data Definition language (DDL) Create
Alter
Drop , Truncate , Rename …
4. Data Control language ( DCL ) Grant , Revoke , Role , Change Password
Chapter01:Writing Basic Select Statement
Chapter Content :
Part1 : Writing Basic Select Statement
Writing Basic Select Statement
Using Arithmetic Operators (Writing Expressions)
Using Alias
Using Concatenation
Using Distinct
Part2: Restricting Rows (Using Where Clause )
Using Comparison Operators
Using In Operator
Using Between and Operator
Using Like Operator
Using Not Operator
Using Multiple Conditions
Part3: Sorting Data
Sorting data by one column
Sorting Data By More Than one Column
Using Alias and Column position in Order By Clause
Chapter01:Writing Basic Select Statement
Chapter Content :
Part1 : Writing Basic Select Statement
Writing Basic Select Statement
Using Arithmetic Operators (Writing Expressions)
Using Alias
Using Concatenation
Using Distinct
Chapter01:Writing Basic Select Statement
Writing Basic Select Statement
Syntax:
Select Select_List
From Table_name ;
Select : Identifies which columns
From : Identifies from which table
Guide lines
• SQL statements are not case sensitive.
• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or split across lines.
• Clauses are usually placed on separate lines.
• Tabs and indents are used to enhance readability.
Introduction to Oracle ERP
Select all columns from certain table
Example :
select *
from emp;
To display all columns in this
table without listing the column
names
Introduction to Oracle ERP
Select Certain Columns
Example :
select empno , ename , sal
from emp;
Introduction to Oracle ERP
Display Tables in Current account
Select * from tab
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Desc Table_name
to get table definition
Example :
Desc emp
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Writing Expressions
Follow the math rules
1) ()
2) * /
3) + -
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Writing Expressions
Use Math Priorities
1) ()
2) * /
3) - +
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Writing Expressions
Violate Math Rules
Use ()
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Writing Expressions
More than one operator
Evaluate the Expr from
left to right
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Using Alias : you can rename the column in your select st.
May be one word
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Using Alias
More than one word
You can include the alias between “ “
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Using Alias : you can Alias database columns
As key word is optional
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Concatenation Operator
Use literals with column names
To display data in form of statements
Introduction to Oracle ERP
Part1 : Writing Basic Select Statement
Using Distinct Key Word
Suppress Duplications in Rows
Introduction to Oracle ERP
Practices
1. Write a query to display all employees in emp table
2. Write a query to display these columns from emp table ( empno , ename , sal , hiredate )
3. Write a query to display the salary column and the annual salary of the employee name the column annual _salary
4. Write a query to display the total salary of the employee given that the total is the sum of sal and comm columns
5. Write a query to display employee data in this form : Employee No. 7788 works as ANALYST
6. Write a query to display different jobs in emp table
7. Write a query to display different jobs in each department
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
Using Comparison Operators
Using In Operator
Using Between and Operator
Using Like Operator
Using Not Operator
Using Multiple Conditions
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Comparison Operators
= > < >= <= <> !=
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Comparison Operators
= > < >= <= <> !=
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Comparison Operators
= > < >= <= <> !=
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Comparison Operators
= > < >= <= <> !=
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
In Operator
Value list : Search for Group of Values
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Between and Operator
Search for range
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Like Operator
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Not
Select *
From emp
Where sal not in ( 800 , 1300 , 3000 ) ;
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Not
Select *
From emp
Where sal not between 2000 and 3000
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Not
Select *
From emp
Where ename not like ‘A%’
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
More Than one Condition
And ( both conditions must be true)
Or ( any of them may be true )
Select *
From emp
Where (deptno = 30 ) and (sal > 2000)
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
More Than one Condition
And ( both conditions must be true)
Or ( any of them may be true )
Select *
From emp
Where (deptno = 30 ) OR (sal > 2000)
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Nulls
Introduction to Oracle ERP
Part2: Restricting Rows (Using Where Clause )
To filter Rows you will use Where Clause
Select select_list
From table_name
Where Condition
Condition : Colum_name operator value
Using Nulls
Introduction to Oracle ERP
Part3: Sorting Data
Sorting data by one column
Sorting Data By More Than one Column
Using Alias and Column position in Order By Clause
Introduction to Oracle ERP
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Introduction to Oracle ERP
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Writing Basic Select St
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Writing Basic Select St
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Sort By More than one Column
Writing Basic Select St
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Sorting By Column position
Writing Basic Select St
Sorting Data
Syntax
Select select_list
From table_name
[Where Condition ]
Order by order_by_list
Sorting By Column Alias
Writing Basic Select St
Practices
1. Write a query to Display Employees Numbers , Names , salaries from emp table
select empno , ename, sal from emp;
2. Write a query to Display Employees with salaries greater than 2000
select * from emp where sal > 2000
3. Write a query to display Employees whose their names start with A
select * from emp where ename like ‘A%’ ;
4. Write a query to display Employees in department 20 , sort your data by salary
select * from emp where deptno = 20 order by deptno ;
5. Write a query to display Employees with these salaries ( 800 , 1300 , 3000 ) sort your data by salary
select * from emp where sal in ( 800 , 1300 , 3000)
6. Write a query to display Employees with Null Commissions sort you data by employee name
Select * from emp where comm is null order by ename
7. Write a query to display Employees work as ANALYST sort your data by deptno
Select * from emp where job =‘ANALYST’ order by deptno ;
8. Write a query to display Employee Hired Before 1990
select * from emp where hiredate < ’01-jan-90’
9. Write a query to Display employees jobs without duplication
select distinct job from emp ;
10. Write a query to display employees monthly salary and annual salary name the annual salary column with suitable name
select empno , ename , sal , sal * 12 “annual_salary” from emp ;
11. Write a query to display employees ( number , name , deptno , sal ) , sort the salary in each department in desc order
select empno , ename, deptno , sal from emp order by deptno ,sal desc
Writing Basic Select St
Practices
1. Write a query to Display Employees Numbers , Names , salaries from emp table
2. Write a query to Display Employees with salaries greater than 2000
3. Write a query to display Employees whose their names start with A
4. Write a query to display Employees in department 20 , sort your data by salary
5. Write a query to display Employees with these salaries ( 800 , 1300 , 3000 ) sort your data by salary
6. Write a query to display Employees with Null Commissions sort you data by employee name
7. Write a query to display Employees work as ANALYST sort your data by deptno
8. Write a query to display Employee Hired Before 1990
9. Write a query to Display employees jobs without duplication
10. Write a query to display employees monthly salary and annual salary name the annual salary column with suitable name
11. Write a query to display employees ( number , name , deptno , sal ) , sort the salary in each department in desc order
