• stands for structural query language • accepted as standard
DESCRIPTION
presentation bookTRANSCRIPT
SQL
• Stands for Structural Query Language
• Accepted as Standard Language(ISO,ANSI) for Relational database
• Non Procedural Language
(what should be done/not how it should be done)
• Interface used to access data from database
SQL StatementsSELECT INSERTUPDATEDELETEMERGE
CREATEALTERDROPRENAMETRUNCATE
COMMITROLLBACKSAVEPOINT
GRANTREVOKE
Data retrieval
Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)
DDL commands
• 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 from table
– RENAME : to rename existing table
DML commands
• 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
• DCL-Data Control Language
– GRANT :Allow access privileges to users
– REVOKE:Revoke or cancel access privileges
TCL
• 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)
Commands1. To Create Table Create Table Tablename(field Name Type (Size))
2. To Display The Content Of Table Select * From Tablename;3. To Display Structure Of Table Desc Tablename;4.To Display All Tables (For Current User) Select * From Tab;
The different Datatypes available are:
CHAR :To store character type of data
VARCHAR2 :Similar to CHAR but can store variable number of characters
NUMBER :Stores fixed and floating point numbers
DATE : Stores point-in-time values (i,e. date and time) in a table
Oracle datatypes
LONG : Can store upto 2 GB of characters. Similar to MEMO fields in FoxPro
RAW : Used to store binary data such as graphics, sound etc.
LONGRAW : Contains raw binary data otherwise the same as a LONG column.
The values entered must be in hex notation.
CLOB : Character Large Objects
BLOB : Binary Large Objects
NCLOB : National Language Support Character Large Object
BFILE : Binary File
SQL FUNCTIONS
– Functions are a very powerful feature of SQL and can be used to do the following:
• Perform calculations on data• Modify individual data items• Manipulate output for groups of rows• Format dates and numbers for display• Convert column data types
– SQL functions sometimes take arguments and always return a value.
TWO TYPES OF SQL FUNCTIONS
FunctionsFunctions
Single-row Single-row functionsfunctions
Multiple-rowMultiple-rowfunctionsfunctions
Operate on single rows only and return one result per row.
Can manipulate groups of rows to give one result per group of rows (group functions)
Single-Row Functions
ConversionConversion
CharacterCharacter
NumberNumber
DateDate
Accepts Accepts different typesdifferent types Single-row Single-row
functionsfunctions
Character Functions
CharacterCharacterfunctionsfunctions
LOWERUPPERINITCAP
CONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACETRANSLATE
Case-manipulation Case-manipulation functionsfunctions
Character-manipulationCharacter-manipulationfunctionsfunctions
Function Result
Case Manipulation Functions
These functions convert case for character strings.
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP(‘sql course')
sql course
SQL COURSE
Sql Course
CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM('H' FROM 'HelloWorld')
REPLACE(‘HELLO’,’EL’,’12’)
TRANSLATE(‘HELLO’,’EL’,’12’)
HelloWorld
Hello
10
6
*****24000
24000*****
elloWorld
H12LO
H122O
Function Result
Character-Manipulation Functions
These functions manipulate character strings:
Pattern Matching
COMMAND DESCRIPTION
S% BEGIN WITH S
%S END WITH S
_S%SECOND LETTER STARTING WITH S
_____ EXACTLY 5 LETTERS
PRACTICE SESSIONS1. Display the total content of dept table & salgrade
table.2. List the employees whose names start with "S"
(not "s"): 3. List the employee names ending with an "S" : 4. List the names of employees whose names have
exactly 6 characters: 5. List the employee names having "I" as the
second character: (Write minimum 2 queries)6. Display o/p ‘SMITH works as CLERK’ using
concat()7. Find number of ‘A’ occurrences in each row of
dname column
PRACTICE SESSIONS8. Write a query to perform trim operation on ename column
of emp table.9. Write a query to perform ‘replace’ function on ename for
replacing first 3 characters of ename by numbers.10. Left justify sal column of emp table.(use #)11. Right justify job column of emp table (use $)12. Write a query to find 3rd character in ename.13. Create a query to display all the data from the
EMPLOYEES table. Separate each column by acomma. Name the column THE_OUTPUT.
14. Write a query to find last character in ename.15. Display all enames with only 3rd character replaced by
numbers.16. Display all enames with last character being different
case.17. Display only those names where last character is getting
repeated.
SM2THAL3ENWA7DJO5ESMA7TINBL1KECL1RKSC6TTKI5GTU7NERAD1MSJA4ESFO7DMI3LER
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
18. Display only those names where second character is getting repeated In 3rd position.
Number Functions
• ROUND: Rounds value to specified decimal
ROUND(45.926, 2) 45.93
• TRUNC: Truncates value to specified decimal
TRUNC(45.926, 2) 45.92
• MOD: Returns remainder of division
MOD(1600, 300) 100
NUMBER functionsROUND( )
TRUNC( )
CEIL( )
FLOOR( )
POWER()
SQRT( )
SIGN( )
MOD( )
ABS( )
Date Functions
Number of monthsbetween two dates
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Add calendar months to date
Next day of the date specified
Last day of the month
Round date
Truncate date
Function Description
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Using Date Functions
• ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-FEB-95')
19.6774194
'11-JUL-94'
'08-SEP-95'
'28-FEB-95'
• ROUND(TO_DATE('01-JUL-05'),’YYYY’)
‘01-JAN-06'
• TRUNC(TO_DATE('01-JUL-05'),’YYYY’)‘01-JAN-05'
FORMAT DESCRIPTION EXAMPLE
MM Number of month 12
RM Roman Numerical month XII
MON Three letter abbreviation DEC
MONTH Month fully spelt out DECEMBER
DDD Number of days since Jan 1 347
DD Number of the day of month 13
D Number of days in week 2
DY Three-letter abbreviation of day WED
DAY Day fully spelt out WEDNESDAY
YYYY Full 4 digit year 1995
SYYYY Signed Year 1000,BC=1000
YYY Last three digits of year 995
YY Last 2 digits of year 95
Y Last digit of year 5
YEAR Year fully spelt out NINTEEN-NINTY-FIVE
Useful formats while Using To_char Functions
1. Select To-day’s date.2. Find out the experience of each employee and arrange in the
increasing order of experience.3. Select a query to return next Sunday.4. Write a query to find how old are you?( express in years and
months)5. List employees hired on Monday.6. Show all employees who were hired in the first half of the month
(before the 16th of the month).7. Write a Query to find out the last day of any given year.8. Add 1 year to each employee and display as a separate column.9. Write a query to find first Sunday of hiredate. (first Sunday in the
joining month)10. Create an anniversary overview based on the hire date of the
employees. Sort the anniversaries in ascending order.
PRACTICE SESSIONS
The aggregate functions produce a single value for an entire group or table
COUNT determines the number of rows or non NULL column values
SUM determines the sum of all selected columns
MAX : determines the largest of all selected values of a column
MIN : determines the smallest of all selected values of a column
AVG : determines the average of all selected values of a column
In all the above functions, NULLs are ignored
Aggregate Functions
1. List the number of employees working with the company
2. List the number of jobs available in the emp table:
3. List the total salaries payable to employees 4. List the maximum salary of employee working
as a salesman: 5. List the minimum salary from emp table 6. List the average salary and number of
employees working in the department 20:
PRACTICE SESSIONS
Conditional Retrieval of Rows
• The WHERE clause is used along with the SELECT statement to specify the condition, based on which the rows will be extracted from a table with SELECT
• Operators used to specify conditions
Relational operators Logical Operators
= :Equal to AND :Logical And
>= : Greater than or equal to OR :Logical or
<= : Less than or equal to NOT : Logical not
<>,!= : Not equal to
1. List the employees belonging to the department 20: 2. List the name and salary of the employees whose
salary is more than 1000: 3. List the employee number and name of managers: 4. List the names of the clerks working in the
department 20: 5. List the names of analysts and salesmen 6. List the details of the employees who have joined
before the end of September- 81:
7. List the names of employees who are not managers:
8. List the name of the employees whose employee numbers are 7369, 7521, 7839 7934, 7788:
PRACTICE SESSIONS
Examples
List the employees belonging to the department 20:
SELECT * FROM emp WHERE deptno = 20;
Special operators
IN Checking value in a set
BETWEEN Checking value with a range
NOT IN Checking value not in set
1. List the name of the employees whose employee numbers are 7369, 7521, 7839 7934, 7788:
2. List the employee details not belonging to the department 10,30 and 40:
3. List the employee name and salary, whose salary is between 1000 and 2000:
4. List employee names, who have joined before 30th June-81 and after December-81:
PRACTICE SESSIONS
GREATEST(expr1 [,expr2]...)
LEAST(expr1 [, expr2] ...)
NVL(col, value)
DECODE(C,V1,S1,V2,S2,...,D)
General Functions
SQL uses the ORDER BY clause to impose an order on the result of a query.
ORDER BY clause is used with SELECT statement
The syntax is
SELECT[DISTINCT] <column list> | <expr>FROM <table>[,<table>] [WHERE condition][ORDER BY <columns>] [ASC|DESC]
One or more columns and/or expressions can be specified in ORDER BY clause.
Examples: List the empno, ename, sal in ascending order of salary:
SELECT empno, ename, sal FROM empORDER BY sal;
Order by Clause
1. List the empno, ename, sal in ascending order of salary:
2. List the employee name and hiredate in descending order of hiredate:
3. List the employee name, salary, Job and Department no
descending order of Department No and salary: 4. List the employee details in ascending order of salary:
(using column nos.,)5. List the employee name, salary, PF, HRA, DA and
gross; order the result in ascending order of gross. HRA is 50% of salary and DA is 30 % of salary.
PRACTICE SESSIONS
NULL values are not 0 or a blank.
It represents an unknown or inapplicable value
It cannot be compared using the relational and/or logical operators
The special operator 'IS' is used with the keyword 'NULL' to locate NULL values
Examples:
List the employee names, who are not eligible for commission:
SELECT ename FROM empWHERE comm IS NULL;
Working with NULL values
1. List the employee names, who are not eligible for commission:
2. List the name of the employee and designation (job)of the employee, who does not report to anybody (mangers is NULL)
3. List the employees who are eligible for commission:
4. List the details of employees, whose salary is greater than 2000 and commission is null
PRACTICE SESSIONS
The GROUP BY clause is used to divide the rows in a table into smaller groups
The GROUP BY clause is used with SELECT clause
SQL groups the result after it retrieves the rows from a table
Conditional retrieval of rows from a grouped result is possible with the HAVING clause
The syntax for GROUP BY clause is
SELECT[DISTINCT] <column list> | <expr>FROM <table>[,<table>] [WHERE condition]GROUP BY <col | expr>[HAVING <cond>]
ORDER BY clause can be used to order the final result
Group by and Having Clause
It chooses rows based on the where clause
It groups those rows together based on the group by.
It calculates the results of the group functions for each group.
It chooses and eliminates groups based on the having clause
It orders the groups based on the results of the group functions in the order by. The order by must use either a group function or a column in the group by clause.
Order of Execution
PRACTICE SESSIONS1. List the department numbers and number of employees in each department:
2. List the department number and the total salary payable in each department:3. List the jobs and the number of employees in each job. The result should be
in descending order of the number of employees: 4. List the total salary, maximum and minimum salary and the average salary of
employees job wise:
5. List the average salary for each job excluding managers 6. List the average monthly salary for each job type within department.
7. List average salary for all departments employing more than five people. 8. List jobs of all the employees where maximum salary is greater than or equal
to 5000. 9. List the total salary, maximum and minimum salary and the average salary of
the employees job wise, for department number 20 and display only those rows having average salary greater than 1000:
10. List the employee numbers, names, department numbers and the department name:
11. List workers and their respective managers.12. List all employees who joined the company before their manager. 13. Write a query to list enames with job having maximum no., of employees
JOINS
• Joins are used to combine columns from different tables
• The connection between tables is established through the WHERE clause
TYPES OF JOINS
ORACLE PROPRIETARY
JOINS( ORACLE 8I & PRIOR)
SQL COMPLAINT JOINS
• Equi Join • Cross joins
• Non equijoin • Natural joins
• Outer join • Using clause
• Self join• Full or two sided outer
joins
• Arbitrary join conditions
for outer joins
The outer join symbol ( + ) can not be on both the sides.
We can not "outer join" the same table to more than one other table in a single SELECT statement.
A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.
Rules to place (+) operator
PRACTICE SESSIONS1. List the employee numbers, names, department numbers
and the department name2. List all employees who joined the company before their
manager.3. Select employees name with reporting manager.4. List all employees who are earning more than manager’s.5. List the employee name along with reporting manager6. Write a query to display employee name,managername and manager’s department name.
JOINING of Tables - Retrieving data from multiple tables
1. EQUI-JOIN : JOINING of EMP and DEPT tables Using DEPTNO in WHERE clause.
2. NON-EQUI JOIN : JOINING of EMP and SALGRADE tables Using WHERE clause
EQUI-JOINSQL> SELECT EMP.ENAME,EMP.JOB,EMP.DEPTNO,
DEPT.DNAME,DEPT.LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
ENAME JOB DEPTNO DNAME LOC SMITH CLERK 20 RESEARCH DALLAS ALLEN SALESMAN 30 SALES CHICAGO
SQL> SELECT E.ENAME,E.JOB,E.DEPTNO,D.DNAME,D.LOC 2 FROM EMP E,DEPT D 3 WHERE E.DEPTNO=D.DEPTNO;SQL> SELECT ENAME,JOB,E.DEPTNO,DNAME,LOC 2 FROM EMP E,DEPT D 3 WHERE E.DEPTNO=D.DEPTNO;
NON-EQUI JOINSQL> SELECT ENAME,JOB,SAL,GRADE 2 FROM EMP E,SALGRADE S 3 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME JOB SAL GRADE ---------- --------- ---------- ---------- SMITH CLERK 800 1 ADAMS CLERK 1100 1 JAMES CLERK 950 1 WARD SALESMAN 1250 2 MARTIN SALESMAN 1250 2 MILLER CLERK 1300 2 ALLEN SALESMAN 1600 3 TURNER SALESMAN 1500 3 JONES MANAGER 2975 4 BLAKE MANAGER 2850 4
JOINING of EMP,DEPT & SALGRADE tables
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC,GRADE 2 FROM EMP E,DEPT D,SALGRADE S 3 WHERE E.DEPTNO=D.DEPTNO AND 4 E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME SAL DEPTNO DNAME LOC GRADE
SMITH 800 20 RESEARCH DALLAS 1 ADAMS 1100 20 RESEARCH DALLAS 1 JAMES 950 30 SALES CHICAGO 1 WARD 1250 30 SALES CHICAGO 2 MARTIN 1250 30 SALES CHICAGO 2
JOINING a table to IT-SELF SQL> SELECT E.ENAME EMP_NAME, 2 M.ENAME MGR_NAME 3 FROM EMP E,EMP M 4 WHERE E.MGR=M.EMPNO;
EMP_NAME MGR_NAME ---------- ---------- SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING
Cross Joins (Cartesian product )
SQL > SELECT * FROM EMP CROSS JOIN DEPT;
SQL > SELECT EMPNO,ENAME,JOB,EMP.DEPTNO,DNAME,LOC FROM EMP CROSS JOIN DEPT
Cartesian Products
• A Cartesian product is formed when:– A join condition is omitted– A join condition is invalid– All rows in the first table are joined to all
rows in the second table
• To avoid a Cartesian product, always include a valid join condition in a WHERE clause.
Generating a Cartesian Product
Cartesianproduct:
20x8=160 rows
EMPLOYEES (20 rows) DEPARTMENTS (8 rows)
What Is a Subquery?
A subquery is a SELECT statement embedded in a clause of another SQL statement.
SELECT ... FROM ...WHERE ...
(SELECT ... FROM ... WHERE ...)
MainMainqueryquery
SubquerySubquery
Subqueries
• The subquery (inner query) executes once before the main query.
• The result of the subquery is used by the main query (outer query).
SELECT select_listFROM tableWHERE expr operator (SELECT select_list
FROM table);
SELECT enameFROM empWHERE sal > (SELECT sal FROM emp WHERE ename=‘JONES’);
Using a Subquery
10500
ENAME----------SCOTTKINGFORD
NESTED QUERIES (SUBQUERIES)
A SUBQUERY is a SELECT statement that is nested within another SELECT statement and which returns intermediate results
Syntax:
SELECT col1,col2,... FROM table_name
WHERE clause operator (SELECT col FROM
table WHERE clause)
Single row subqueriesLet us take an example
SQL> SELECT MIN(SAL) FROM EMP;
MIN(SAL) ---------- 800 In the above case minimum sal is retrieved. Now let us try to retrieve the employee name who has this minimum salary.
SQL> SELECT ENAME,MIN(SAL) FROM EMP;SELECT ENAME,MIN(SAL) FROM EMP *ERROR at line 1:ORA-00937: not a single-group group function
SQL> SELECT ENAME,SAL FROM EMP 2 WHERE SAL=(SELECT MIN(SAL) FROM EMP);
ENAME SAL ---------- ---------- SMITH 800
The following example retrieves the employees who have the same job as that of BLAKESQL> SELECT ENAME,JOB 2 FROM EMP 3 WHERE JOB=(SELECT JOB FROM EMP WHERE
ENAME='BLAKE');ENAME JOB ---------- --------- JONES MANAGER BLAKE MANAGER CLARK MANAGER
To find details of employees whose salary is greater than maximum salary of sales department.
SQL> SELECT ENAME,JOB,HIREDATE,SAL FROM EMP 2 WHERE SAL > (SELECT MAX(SAL) FROM EMP 3 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE
DNAME='SALES'));ENAME JOB HIREDATE SAL ---------- --------- --------- ---------- JONES MANAGER 02-APR-81 2975 SCOTT ANALYST 19-APR-87 3000 KING PRESIDENT 17-NOV-81 5000 FORD ANALYST 03-DEC-81 3000
Subqueries returning multiple rows
To retrieve ename,salary,deptno of employees who draw minimum salary in each department.
SQL> SELECT ENAME,SAL,DEPTNO FROM EMP 2 WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP
BY DEPTNO);
ENAME SAL DEPTNO ---------- ---------- ---------- SMITH 800 20 JAMES 950 30 MILLER 1300 10
To query the department which has the avg(sal) greater to that of department 30.
SQL> SELECT DEPTNO,AVG(SAL) FROM EMP 2 HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP 3 WHERE DEPTNO=30) 4 GROUP BY DEPTNO;
DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667 20 2175
To retrieve the maximum average salary for JOB
SQL> SELECT JOB,AVG(SAL) 2 FROM EMP GROUP BY JOB 3 HAVING AVG(SAL)=(SELECT MAX(AVG(SAL)) FROM
EMP GROUP BY JOB);
JOB AVG(SAL) --------- ---------- PRESIDENT 5000 Show all employees who were hired on the day of the week on which the highest
number of employees has been hired.
Select ename from (SELECT DY,CNT FROM (SELECT TO_CHAR(HIREDATE,‘DY') DAY,COUNT(1) CNT FROM EMP GROUP BY TO_CHAR(HIREDATE,‘DY') ORDER BY CNT DESC) WHERE ROWNUM<=1)
SET Operators are used to combine information of similar type from one or more than one table
Datatype of corresponding columns must be the same
The types of SET operators in ORACLE are :
UNION : Rows of first query plus rows of second query, less duplicate rows
INTERSECT : Common rows from all the queries
MINUS : Rows unique to the first query
The SET Operators
The SET OperatorsA B
UNION/UNION ALL
A B
A B
INTERSECT
A B
MINUS
UNION SELECT ENAME,SAL FROM EMP WHERE DEPTNO=10
UNION SELECT ENAME,SAL FROM EMP WHERE DEPTNO=20
INTERSECTSELECT JOB FROM EMP WHERE DEPTNO=10
INTERSECTSELECT JOB FROM EMP WHERE DEPTNO=20
MINUSSELECT JOB FROM EMP WHERE DEPTNO=10
MINUSSELECT JOB FROM EMP WHERE DEPTNO=20
MINUSSELECT JOB FROM EMP WHERE DEPTNO=30
1. Display the different designations in department 20 and 30:
2. Use order by clause for the above query3. List the jobs common to department 20 and 30: 4. List the jobs unique to department 20: 5. List the employees belonging to the department of MILLER: 6. List the names of the employee drawing the highest salary:
7. List the names of the employees, who earn lowest salary in each
department: 8. List employee details who earn salary greater than the average
salary for their department. 9. List the job with highest average salary.10. List the names of the employees, who earn lowest salary in each
department:
PRACTICE SESSIONS
• Create the table.
• Confirm creation of the table.
Creating Tables
CREATE TABLE dept1(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13));
Table created.Table created.
DESCRIBE dept1
What Are Constraints?
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid:– NOT NULL– UNIQUE – PRIMARY KEY– FOREIGN KEY– CHECK
Alter,Modify,Drop
ALTER TABLE
Add clause –Add new columns–Add constraints
Modify clause–Add not null constraint–Change column data type–Modify columnsize(width)
Drop clause–Drop Columns–Drop Constraints
Adding a Column
DEPT80
Add a new column to the DEPT80 table.
DEPT80
New column
Adding a Column• Use the ADD clause to add columns.
• The new column becomes the last column.
ALTER TABLE dept80ADD (job_id VARCHAR2(9));Table altered.Table altered.
Modifying a Column• You can change a column’s data
type, size, and default value.
• A change to the default value affects only subsequent insertions to the table.
ALTER TABLE dept80MODIFY (last_name VARCHAR2(30));Table altered.Table altered.
Dropping a Column
Use the DROP COLUMN clause to drop columns you no longer need from thetable.
ALTER TABLE dept80DROP COLUMN job_id; Table altered.Table altered.
alter table e2 add constraint p primary key ( empno)Alter table e2 drop constraint p;
Dropping a Table
• All data and structure in the table is deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• You cannot roll back the DROP TABLE statement.
DROP TABLE dept80;Table dropped.Table dropped.
Changing the Name of an Object• To change the name of a table, view,
sequence, or synonym, execute the RENAME statement.
• You must be the owner of the object.
RENAME dept TO detail_dept;Table renamed.Table renamed.
Truncating a Table• The TRUNCATE TABLE statement:
– Removes all rows from a table– Releases the storage space used by
that table
• You cannot roll back row removal when using TRUNCATE.
• Alternatively, you can remove rows by using the DELETE statement.
TRUNCATE TABLE detail_dept;Table truncated.Table truncated.
STATEMENT DESCRIPTION
INSERT Adds a new row to the table
UPDATE Modifies existing rows in the table
DELETE Removes all/particular existing rows from the table
COMMIT Makes all changes permanent
SAVEPOINT Used to divide the transactions
ROLLBACK Discards all pending data changes
STATEMENT DESCRIPTION
CREATE TABLE Creates a table
ALTER TABLE Modifies table structures
DROP TABLE Removes the rows & table structure
RENAME Changes the name of object (table, view etc)
TRUNCATE Removes all rows from table
GRANT & REVOKE
GRANT IS USED FOR GIVING PRIVILEGES TO USERS
REVOKE IS USED TO WITHDRAW THE PRIVILEGES FROM USERS
TYPES OF PRIVILEGES
•SYSTEMCreate session, table etc
•OBJECT select, insert, delete,update
What Is a Sequence?
A sequence:
• Automatically generates unique numbers
• Is a sharable object
• Is typically used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memory
The CREATE SEQUENCE Statement Syntax
Define a sequence to generate sequential numbers automatically.
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
Creating a Sequence• Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table.
• Do not use the CYCLE option.CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;Sequence created.Sequence created.
CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;Sequence created.Sequence created.
Confirming Sequences
• Verify your sequence values in the USER_SEQUENCES data dictionary table.
• The LAST_NUMBER column displays the next available sequence number if NOCACHE is specified.
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
NEXTVAL and CURRVAL Pseudocolumns
• NEXTVAL returns the next available sequence value.It returns a unique value every time it is
referenced,
even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.
Using a Sequence
• Insert a new department named “Support” in location ID 2500.
• View the current value for the DEPT_DEPTID_SEQ sequence.
INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);1 row created.1 row created.
INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);1 row created.1 row created.
SELECT dept_deptid_seq.CURRVALFROM dual;
SELECT dept_deptid_seq.CURRVALFROM dual;
Using a Sequence
• Caching sequence values in memory gives faster access to those values.
• Gaps in sequence values can occur when:– A rollback occurs– The system crashes– A sequence is used in another table
• If the sequence was created with NOCACHE, view the next available value, by querying the USER_SEQUENCES table.
Removing a Sequence
• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.
DROP SEQUENCE dept_deptid_seq;Sequence dropped.Sequence dropped.
DROP SEQUENCE dept_deptid_seq;Sequence dropped.Sequence dropped.
SEQUENCES
1.Create a sequence to be used with the primary key column of the DEPT table. The sequence should start at 200 and have a maximum value of 1000. Have your sequence increment by ten numbers. Name the sequence DEPT_ID_SEQ.
2.Write a query in a script to display the following information about your sequences:
sequence name, maximum value, increment size, and last number.
3 Write a script to insert two rows into the DEPT table. Be sure to use the sequence
that you created for the ID column. Add two departments named Education and
Administration. Confirm your additions.