SQL 1

Oracle 9i Database Server
SQL
(Structured Query Language)
Oracle Database Management Tools
SQL (sequel) – Structured [English] Query Language
-Standard Language for Managing almost any RDBMS
-Supports with set of commands for managing RDBMS
-Developed by IBM
-Based on Dr. E F Codd’s Relational Model
PL/SQL – Procedural Language/SQL
-Procedural extension to SQL
-Provides with programming language features using which you can
develop application logic programs like procedures, functions, triggers
and packages which will be stored on database server.
Oracle Database Access Tools
SQL*Plus (most widely used tool)
- an interactive tool which takes SQL statements and PL/SQL
programs from you and sends to oracle for execution
- results and errors given by oracle will be presented in default
format or in specific format specified.
-supports many report formatting commands, environment
commands which are useful in presenting data in desired
format, setting the environment, saving statements and
PL/SQL programs in files, spooling etc.,
iSQL*Plus (web browser tool)
SQL*Plus worksheet
SQL command Groups
DQL - Data Query Language
SELECT : used to retrieve the data present in the database tables
DDL - Data Definition Language
CREATE : to create a new data structure.
ALTER : to change an existing data structure.
DROP : to remove an entire data structure.
TRUNCATE : to remove all rows permanently from table
RENAME : to rename existing table
DML - Data Manipulation Language
INSERT : to add records into the table
UPDATE : to change column value in the table
DELETE : to remove rows from the table
MERGE : to update & insert contents of one table to another table
based on condition
DCL - Data Control Language
GRANT :Allow access privileges to users
REVOKE :Revoke or cancel access privileges
TCL-Transaction Control Language.
COMMIT :Save or enable DML changes to the database.
ROLLBACK : To undo DML changes till in a transaction
SAVEPOINT: To divide a transaction
SELECT command query
Syntax : SELECT (column_list )

FROM (table_list )
WHERE (row restriction)
GROUP BY (attribute names)
HAVING (group restriction)
ORDER BY (attribute name or names)
Following topics will be covered online. Participants are
instructed to make note of the same
Tab table Handling NULL data
nvl(comm,0 )
Desc nvl2(comm, sal*12+comm,sal*12)
Sql buffer nullif(a,b )
COALESCE (expr1, expr2,…)
Arithmetic operators Sorting Data – order by clause
Alias column name Searching Data -where clause
Concatenate operator(||) Logical operators
Distinct clause SQL operators
Substitution variables
Rules of Precedence
Order Evaluated Operator

1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT logical condition
7 AND logical condition
8 OR logical condition
ACCEPT
-allows a variable to be created.
-value entered at runtime is stored in variable
-Supported in SQL*Plus
Syntax:
ACCEPT variable [number|char] [prompt|noprompt ‘Text’] [hide]
Examples:
sql>accept salary number prompt ‘Salary Figure :’
sql>accept password char prompt ‘Password :’ HIDE
sql>accept comm number NOPROMPT
Exercise:
2. List all details from salgrade table.
3. Display different job types without duplicating
4. List employees in department 10 and 30 in alphabetical order of
names
5. List clerks in department 20
6. List employees having ‘LL’ or ‘TH’ in their names
7. List employees who do not have manager.
8. List employees hired in year 81.
9. List salesmen having salary greater than commission. Sort on their
salaries in descending and names ascending.
10. List o/p as ‘SMITH works since 17-DEC-80 as CLERK’
11. List employees hired during any 2 dates (use substitution variable).
11. List all clerks earning less than 1000 and all managers
12. List employees having annual renumeration > 30000
DUAL table
-The DUAL table is owned by the user SYS and can
be accessed by all users.
-It contains one column, DUMMY, and one row
with the value X.
-The DUAL table is useful when you want to return
a value once only (for example, the value of a
constant, pseudocolumn, or expression that is not
derived from a table with user data).
-The DUAL table is generally used for SELECT
clause syntax completeness
Functions
Input Output
Function
arg 1 Function
performs action
arg 2
Result
value
arg n
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions functions
Operate on single Can manipulate groups of

rows only and return rows to give one result per
one result per row. group of rows (group
functions)
Single-Row Functions
Character
Accepts Single-row
functions Number
different types
Conversion Date
Character Functions
Character
functions
Case-manipulation Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
LTRIM | RTRIM
REPLACE
TRANSLATE
CHARACTER functions
LOWER (string) – returns data in lower case
UPPER(string) – returns data in upper case
INITCAP(string) – returns with first character in caps for each word
CONCAT(string1,string2) – concatenates two strings
SQL> SELECT ENAME,LOWER(ENAME),UPPER('OrACle'),

INITCAP(JOB),CONCAT(JOB,SAL) FROM EMP WHERE
DEPTNO=10;
CLARK clark ORACLE Manager MANAGER2450
KING king ORACLE President PRESIDENT5000

LPAD(string,width,padding character’s ) – Right justifies the string
within defined width and on LHS pads with padding char
RPAD(string,width,padding character’s ) – Left justifies the string
within the defined width and on LHS pads with padding char
SQL> SELECT DNAME,LPAD(DNAME,15,'.'),

RPAD(DNAME,15,'*') FROM DEPT;
ACCOUNTING .....ACCOUNTING ACCOUNTING*****

RESEARCH .......RESEARCH RESEARCH*******
SALES ..........SALES SALES**********
OPERATIONS .....OPERATIONS OPERATIONS*****
SUBSTR(string,start pos,length )- returns part of the string
INSTR( string, char’s, start pos, nth occurance) – returns index of the
char’s in the given string
SQL> SELECT DNAME,

SUBSTR(DNAME,3,4),SUBSTR(DNAME,4),
INSTR(DNAME,'C',1),INSTR(DNAME,'C',1,2)
FROM DEPT;
ACCOUNTING COUN OUNTING 2 3
RESEARCH SEAR EARCH 7 0
SALES LES ES 0 0
OPERATIONS ERAT RATIONS 0 0

LTRIM(string,char’s) – returns string after trimming with specified
chars from left hand side of the string
RTRIM(string,char’s) – returns string after trimming with specified
chars from right hand side of the string
SQL> SELECT DNAME,LTRIM(DNAME,'ASOP'),

RTRIM(DNAME,'NGES') FROM DEPT;
ACCOUNTING CCOUNTING ACCOUNTI
RESEARCH RESEARCH RESEARCH
SALES LES SAL
OPERATIONS ERATIONS OPERATIO

LENGTH(string)- returns length of the string
TRANSLATE(string,source,target ) – overwrites source chars with
target chars
SQL> SELECT DNAME,LENGTH(DNAME),

TRANSLATE(DNAME,'A','X'),
TRANSLATE(DNAME,'AS','XY')FROM DEPT;
ACCOUNTING 10 XCCOUNTING XCCOUNTING
RESEARCH 8 RESEXRCH REYEXRCH
SALES 5 SXLES YXLEY
OPERATIONS 10 OPERXTIONS OPERXTIONY

REPLACE(string,source,target) – replaces source string with target
string
SQL> SELECT JOB,

REPLACE(JOB,'SALESMAN','MARKETING')
FROM EMP WHERE DEPTNO=30;
SALESMAN MARKETING
SALESMAN MARKETING
SALESMAN MARKETING
MANAGER MANAGER
SALESMAN MARKETING
CLERK CLERK
CHR(number) – returns char equi of ascii value

NUMBER functions
ROUND (40.44,1) 40.4 TRUNC(40.41,1) 40.4
“ (40.45,1) 40.5 “ (40.49,1) 40.4
“ (40.4) 40 “ (40.1) 40
“ (40.5) 41 “ (40.9) 40
“ (44,-1) 40 “ (41,-1) 40
“ (45,-1) 50 “ (49,-1) 40
“ (149,-2) 100 “ (111,-2) 100
“ (150,-2) 200 “ (199,-2) 100
CEIL (11.1) 12 FLOOR (11.9) 11
“ (-11.9) -11 “ (-11.1) -12
POWER(X,Y) XY SQRT (25) 5
MOD (20,3) 2 SIGN (10-5) 1
ABS (-45.67) 45.67 “ (5-10) -1
ASCII (‘B’) 66 “ (5-5) 0
Exercise :
• Write a case insensitive query which accepts name and return data
• Display o/p ‘SMITH works as CLERK’ using concat()
• Write a query which returns employees with char ‘L’ in 3rd position
• Find number of ‘E’ occurances in each row of dname column
• Find employee earning sal > comm using other than ‘>’ operator.
• List employee names and salary increased by 15% and expressed as a
whole number.
• List employees having odd empno.

SQL 1

Uploaded by

Copyright:

Available Formats

SQL 1

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL 1

Uploaded by

Copyright:

Available Formats

Oracle 9i Database Server

Syntax : SELECT (column_list )

Order Evaluated Operator

Operate on single Can manipulate groups of

SQL> SELECT ENAME,LOWER(ENAME),UPPER('OrACle'),

CLARK clark ORACLE Manager MANAGER2450

KING king ORACLE President PRESIDENT5000

SQL> SELECT DNAME,LPAD(DNAME,15,'.'),

ACCOUNTING .....ACCOUNTING ACCOUNTING*****

SQL> SELECT DNAME,

ACCOUNTING COUN OUNTING 2 3

RESEARCH SEAR EARCH 7 0

OPERATIONS ERAT RATIONS 0 0

SQL> SELECT DNAME,LTRIM(DNAME,'ASOP'),

ACCOUNTING CCOUNTING ACCOUNTI

RESEARCH RESEARCH RESEARCH

SALES LES SAL

OPERATIONS ERATIONS OPERATIO

SQL> SELECT DNAME,LENGTH(DNAME),

ACCOUNTING 10 XCCOUNTING XCCOUNTING

RESEARCH 8 RESEXRCH REYEXRCH

SALES 5 SXLES YXLEY

OPERATIONS 10 OPERXTIONS OPERXTIONY

SQL> SELECT JOB,

CHR(number) – returns char equi of ascii value

You might also like