Transcript Download: SQL

SQL
Nitin Singh/AAO RTI
ALLAHABAD
1
OBJECTIVES







What is SQL?
Types of SQL commands and their function
Query
Index
Views
Synonyms
Database transactions
Nitin Singh/AAO RTI
ALLAHABAD
2
SQL
 Sequential Query Language
 Used for authenticating,accessing,
manipulating of data from database.
Nitin Singh/AAO RTI
ALLAHABAD
3
Types of SQL Commands






Data Definition Language(DDL)
Data Manipulation Language(DML)
Data Query Language(DQL)
Data Control Language(DCL)
Data Administration Command
Transactional Control Command
Nitin Singh/AAO RTI
ALLAHABAD
4
DDL






Create Table
Alter Table
Drop Table
Create Index
Alter Index
Drop Index
Nitin Singh/AAO RTI
ALLAHABAD
5
DML
 Insert
 Update
 Delete
Nitin Singh/AAO RTI
ALLAHABAD
6
DQL
 Select Statement: Used to select rows from
a table
 Select contains following main keywords
–
–
–
–
–
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Nitin Singh/AAO RTI
ALLAHABAD
7
Transactional Control Command
 Commit
 Rollback
 Savepoint
Nitin Singh/AAO RTI
ALLAHABAD
8
DCL




Alter Password
Grant
Revoke
Create Synonym
Nitin Singh/AAO RTI
ALLAHABAD
9
Data Manipulation Language
 Inserting into the table
INSERT INTO emp ( name , age , doj , phone_no)
VALUES(‘Anshuman’, 21 , 12/07/1999, ‘393751’) ;
 Updating Existing Data
UPDATE emp
SET name = ‘Sumit’
age = 31
WHERE name = ‘Anshuman’ ;
Nitin Singh/AAO RTI
ALLAHABAD
10
Data Manipulation Language
 Deleting data from table
DELETE FROM emp
WHERE name = ‘Anshuman’ ;
Nitin Singh/AAO RTI
ALLAHABAD
11
Managing Tables
 Creating A Table

CREATE TABLE emp
( name VARCHAR2(10) NOT NULL ,
age NUMBER(2)
NOT NULL ,
doj
DATE
NOT NULL )
STORAGE
(INITIAL 2K
NEXT 3K) ;
Continue……….
Nitin Singh/AAO RTI
ALLAHABAD
12
Managing Tables
 Modifying the table
ALTER TABLE emp
MODIFY (name VARCHAR2(12) NOT NULL)
ADD
phone_no VARCHAR2(10) ;
 Dropping the table
DROP TABLE emp CASCADE
Nitin Singh/AAO RTI
ALLAHABAD
13
DQL
 Selecting all the rows of table emp
– SELECT *
FROM emp
 Selecting phone no. & age of employee
Anshuman from table emp
– SELECT phone_no , age
FROM emp
WHERE name = ‘Anshuman’ ;
Nitin Singh/AAO RTI
ALLAHABAD
14
AGGREGATE FUNCTIONS
 Used to provide summarization information
for a SQL statement, such as
COUNT,TOTAL,AVERAGE .
 Aggregate functions to be covered are
–
–
–
–
–
COUNT
SUM
MAX
MIN
AVG
Nitin Singh/AAO RTI
ALLAHABAD
15
AGGREGATE FUNCTIONS
 COUNT
–
–
–
–
Used to return no. of rows .
Return a numeric value when used with a query.
Can be used with three options: */DISTINCT/ALL.
COUNT(*) counts all the rows of a table including
duplicates, whether a NULL value is contained in a
column or not.
– COUNT(ALL) counts all the rows including the
duplicates but excluding the rows containing NULL
values.
– COUNT(DISTINCT)counts all the rows excluding
the duplicate rows and the rows containing NULL
Nitin Singh/AAO RTI
16
values.
ALLAHABAD
AGGREGATE FUNCTIONS
 Usage:
– SELECT COUNT(*)
FROM emp ;
– SELECT COUNT(DISTINCT name)
FROM emp ;
– SELECT COUNT(ALL name)
FROM emp ;
Nitin Singh/AAO RTI
ALLAHABAD
17
AGGREGATE FUNCTIONS
 SUM
– Used to return a total on the values of a column
– DISTINCT can be used with SUM where only distinct column
values are to be added.
 Usage:
– SELECT SUM (age)
FROM emp ;
– SELECT SUM(DISTINCT age)
FROM emp ;
Nitin Singh/AAO RTI
ALLAHABAD
18
AGGREGATE FUNCTIONS
 AVG
– Used to find the averages for a group of rows.
– Can be used with DISTINCT command when average of
distinct rows is to be found out.
 Usage:
– SELECT AVG(age)
FROM emp ;
– SELECT AVG(DISTINCT age)
FROM emp ;
Nitin Singh/AAO RTI
ALLAHABAD
19
AGGREGATE FUNCTIONS
 MAX
– Used to return the maximum value of a column in a group
of rows.
– NULL values are ignored .
 Usage:
– SELECT MAX(age)
FROM emp ;
Nitin Singh/AAO RTI
ALLAHABAD
20
AGGREGATE FUNCTIONS
 MIN
– Used to return the minimum value of a column for a group
of rows.
– NULL values are ignored .
 Usage:
– SELECT MIN(age)
FROM emp ;
Nitin Singh/AAO RTI
ALLAHABAD
21
ACCESSING DATA FROM
MULTIPLE TABLES
 By joining the tables
 By using nested queries.
 By using compound queries
Nitin Singh/AAO RTI
ALLAHABAD
22
JOIN
 Join combines two or more tables in order
to retrieve data from multiple tables.
 Example
SELECT name , address
FROM student , stu
where student.stid = stu.stid ;
Nitin Singh/AAO RTI
ALLAHABAD
23
Sub Query
 Query within another query
 Sub query is used to return data that will be
used in the main query as a condition to
furthur restrict the data to be retrieved .
 Used with Select , Insert , Update , Delete
statements.
Nitin Singh/AAO RTI
ALLAHABAD
24
COMPOUND QUERY
 Includes two or more SELECT statements
 Formed by using
UNION,INTERSECT,EXCEPT operators.
Nitin Singh/AAO RTI
ALLAHABAD
25
INDEX
 Pointer to the database.
 When index is created , it records the location of values in
a table that are associated with the column that is indexed.
 Entries are added to the index when new data is added to
the table .
 When a query is executed against the database and a
condition is specified on a column in the WHERE clause
that is indexed, the index is first searched for the values
specified in the WHERE clause . If the value is found in
index , the index returns the exact location of the searched
data in the table.
Nitin Singh/AAO RTI
ALLAHABAD
26
INDEX
 Types of index
– Single column index
– Unique index
– Composite index

Single Column Index
– Based on only one table column

Creating Single Column Index
– CREATE INDEX name_idx
ON emp(name) ;
Nitin Singh/AAO RTI
ALLAHABAD
27
INDEX
 Unique Index
– Does not allow any duplicate values to be inserted into the
table
 Creating Unique Index
CREATE UNIQUE INDEX name1_idx
ON emp(name) ;
Nitin Singh/AAO RTI
ALLAHABAD
28
INDEX
 Composite Index
– An index on two or more columns
 Creating Composite Index
CREATE INDEX nam1_idx
ON emp(name, age) ;
Nitin Singh/AAO RTI
ALLAHABAD
29
INDEX
 Dropping the index
DROP INDEX name_idx ;
Nitin Singh/AAO RTI
ALLAHABAD
30
INDEX
 Conditions where index should be avoided
– Should not be used for small tables.
– Should not be used on the columns that contain high no. of
null values.
– Columns that are frequently manipulated should not be
indexed .
 Conditions where index should be considered
– Columns that are frequently referenced in the ORDER BY &
GROUP BY clauses should be considered for indexes.
– Indexes should be created on the columns with high no. of
unique values.
– Foreign keys are
candidates for index , as they are
Nitinexcellent
Singh/AAO RTI
31
often used to joinALLAHABAD
parent table.
VIEW




A virtual table.
Looks like a table and acts like a table.
Does not require physical storage space.
ORDER BY clause can’t be used in CREATE
VIEW but GROUP BY clause can be used
which has same effect as ORDER BY clause
Nitin Singh/AAO RTI
ALLAHABAD
32
VIEW
 Creating a view
– CREATE VIEW nam_vew AS
SELECT emp.name ,emp.age , detail.address ,
detail.status
FROM emp , detail
WITH CHECK OPTION ;
 Dropping a view
– DROP VIEW nam_vew ;
Nitin Singh/AAO RTI
ALLAHABAD
33
SYNONYM
 Another name for table or view
 Usually created so that a user can avoid
having to qualify another user’s table or
view to access the table or view.
 Creating a synonym
– CREATE SYNONYM employ FOR emp ;
 Dropping a synonym
– DROP SYNONYM employ ;
Nitin Singh/AAO RTI
ALLAHABAD
34
Managing Database Transactions
 Transaction: Sequence of work
accomplished in a logical manner.
 Commands to control transactions:
– COMMIT
– ROLLBACK
– SAVEPOINT
Nitin Singh/AAO RTI
ALLAHABAD
35
Managing Database Transactions
 Commit
• Used to save changes invoked by a transaction to the
database.
• Syntax: COMMIT[WORK] ;
 Rollback
• Used to undo transactions that have not already been
saved to the database.
• Syntax: ROLLBACK[WORK] ;
Nitin Singh/AAO RTI
ALLAHABAD
36
Managing Database Transactions
 Savepoint
• It is a point in a transaction that one can roll the
transaction back to without rolling back the entire
transaction.
• Syntax: SAVEPOINT s1 ;
 Example
SAVEPOINT s1 ;
DELETE FROM emp
WHERE name = ‘Anshuman’ ;
SAVEPOINT s2 ;
DELETE FROM emp
WHERE name = ‘Sumit’ ;
SAVEPOINT s3 ;
ROLLBACK s2;
Nitin Singh/AAO RTI
ALLAHABAD
37