LESSON 1
Writing Basic
SQL Select
Statements
Ø
A SELECT statement retrieves information from the database. Ø
Using a SELECT statement, you can do the following: Ø
in a table that you want returned by your query.
You can use the projection capability in SQL to choose the columns Ø
require.
You can choose as few or as many columns of the table as you Ø
table that you want returned by a query.
You can use the selection capability in SQL to choose the rows in a Ø
You can use various criteria to restrict the rows that you see. Ø
stored in different tables by creating a link between them. Ø
SELECT identifies What columns. Ø
FROM identifies Which table. 1) Select * from EMP;
2) Select distinct ename from EMP;
3) Select ename “Employee Name” from EMP; Arithmetic Expressions: Arithmetic expression can be created by applying the arithmetic
operators on NUMBER and DATE data types.
Arithmetic expressions containing a null values evaluates to null.
If any column value in an arithmetic expression is null, the result is
null.
Select ename, sal, sal+300 “Commision” from Emp;
Select ename,12*sal+
Ø
Renames a column heading. Ø
Is useful with calculations. Ø
column name and alias.
Immediately follows column name; optional AS keyword b/w Ø
characters or is case sensitive.
Requires double quotation marks if it contains spaces or special Ø
An Alias cannot be used in the where clause. 1) Select ename as NAME,sal SALARY,dname “Dept Name”;
NAME SALARY Dept Name
-------------------- ------------ ---------------------- OUTPUT:
Employee Details
-----------------------------------
King is a President
Blake is a Manager Eliminating Duplicates Rows: The default display of queries is all rows including duplicate rows.
select deptno from emp;
DEPTNO
----------------
10
30
10
20
To eliminate duplicate rows in the result, include
DISTINCT keywords in the SELECT clause immediately after the SELECT
keyword. select
OUTPUT:
DEPTNO
-----------------
10
20
30
We can specify multiple columns after the DISTINCT qualifier. The
distinct qualifier affects the selected columns, and the result represents
a distinct combination of the columns select
distinct deptno, job from emp; DEPTNO JOB
------------- ---------------
10 CLERK
10 MANAGER
20 ANALYSTdistinct deptno from emp; Using Column Aliases:
You can use the join capability in SQL to bring together data that isOUTPUT:
Example:
Example:
OUTPUT:
Example:
Ø
Literal Character String:
OUTPUT:
Example:
Example:
Ø
Examples:
BASIC SELECT STATEMENT:
3. Joining:
2. Selection:
1. Projection:
Capabilities of SQL SELECT Statements
