nitin singh/aao rti allahabad 1 sql nitin singh/aao rti allahabad 2 objectives §what is sql?...
TRANSCRIPT
![Page 1: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/1.jpg)
Nitin Singh/AAO RTI ALLAHABAD
1
SQL
![Page 2: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/2.jpg)
Nitin Singh/AAO RTI ALLAHABAD
2
OBJECTIVES
What is SQL?Types of SQL commands and their functionQueryIndexViewsSynonymsDatabase transactions
![Page 3: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/3.jpg)
Nitin Singh/AAO RTI ALLAHABAD
3
SQL
Sequential Query LanguageUsed for authenticating,accessing,
manipulating of data from database.
![Page 4: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/4.jpg)
Nitin Singh/AAO RTI ALLAHABAD
4
Types of SQL Commands
Data Definition Language(DDL)Data Manipulation Language(DML)Data Query Language(DQL)Data Control Language(DCL)Data Administration CommandTransactional Control Command
![Page 5: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/5.jpg)
Nitin Singh/AAO RTI ALLAHABAD
5
DDL
Create TableAlter TableDrop TableCreate IndexAlter IndexDrop Index
![Page 6: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/6.jpg)
Nitin Singh/AAO RTI ALLAHABAD
6
DML
Insert UpdateDelete
![Page 7: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/7.jpg)
Nitin Singh/AAO RTI ALLAHABAD
7
DQL
Select Statement: Used to select rows from a table
Select contains following main keywords– SELECT
– FROM
– WHERE
– GROUP BY
– ORDER BY
![Page 8: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/8.jpg)
Nitin Singh/AAO RTI ALLAHABAD
8
Transactional Control Command
CommitRollbackSavepoint
![Page 9: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/9.jpg)
Nitin Singh/AAO RTI ALLAHABAD
9
DCLAlter PasswordGrantRevokeCreate Synonym
![Page 10: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/10.jpg)
Nitin Singh/AAO RTI ALLAHABAD
10
Data Manipulation Language
Inserting into the table INSERT INTO emp ( name , age , doj , phone_no)
VALUES(‘Anshuman’, 21 , 12/07/1999, ‘393751’) ;
Updating Existing DataUPDATE emp
SET name = ‘Sumit’
age = 31
WHERE name = ‘Anshuman’ ;
![Page 11: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/11.jpg)
Nitin Singh/AAO RTI ALLAHABAD
11
Data Manipulation Language
Deleting data from table DELETE FROM emp
WHERE name = ‘Anshuman’ ;
![Page 12: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/12.jpg)
Nitin Singh/AAO RTI ALLAHABAD
12
Managing TablesCreating 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……….
![Page 13: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/13.jpg)
Nitin Singh/AAO RTI ALLAHABAD
13
Managing Tables
Modifying the tableALTER TABLE emp
MODIFY (name VARCHAR2(12) NOT NULL)
ADD phone_no VARCHAR2(10) ;
Dropping the table DROP TABLE emp CASCADE
![Page 14: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/14.jpg)
Nitin Singh/AAO RTI ALLAHABAD
14
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’ ;
![Page 15: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/15.jpg)
Nitin Singh/AAO RTI ALLAHABAD
15
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
![Page 16: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/16.jpg)
Nitin Singh/AAO RTI ALLAHABAD
16
AGGREGATE FUNCTIONSCOUNT
– 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 values.
![Page 17: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/17.jpg)
Nitin Singh/AAO RTI ALLAHABAD
17
AGGREGATE FUNCTIONS
Usage:– SELECT COUNT(*)
FROM emp ;
– SELECT COUNT(DISTINCT name)
FROM emp ;
– SELECT COUNT(ALL name)
FROM emp ;
![Page 18: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/18.jpg)
Nitin Singh/AAO RTI ALLAHABAD
18
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 ;
![Page 19: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/19.jpg)
Nitin Singh/AAO RTI ALLAHABAD
19
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 ;
![Page 20: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/20.jpg)
Nitin Singh/AAO RTI ALLAHABAD
20
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 ;
![Page 21: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/21.jpg)
Nitin Singh/AAO RTI ALLAHABAD
21
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 ;
![Page 22: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/22.jpg)
Nitin Singh/AAO RTI ALLAHABAD
22
ACCESSING DATA FROM MULTIPLE TABLES
By joining the tablesBy using nested queries.By using compound queries
![Page 23: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/23.jpg)
Nitin Singh/AAO RTI ALLAHABAD
23
JOIN
Join combines two or more tables in order to retrieve data from multiple tables.
ExampleSELECT name , address
FROM student , stu
where student.stid = stu.stid ;
![Page 24: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/24.jpg)
Nitin Singh/AAO RTI ALLAHABAD
24
Sub Query
Query within another querySub 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.
![Page 25: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/25.jpg)
Nitin Singh/AAO RTI ALLAHABAD
25
COMPOUND QUERY
Includes two or more SELECT statementsFormed by using
UNION,INTERSECT,EXCEPT operators.
![Page 26: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/26.jpg)
Nitin Singh/AAO RTI ALLAHABAD
26
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.
![Page 27: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/27.jpg)
Nitin Singh/AAO RTI ALLAHABAD
27
INDEXTypes 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) ;
![Page 28: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/28.jpg)
Nitin Singh/AAO RTI ALLAHABAD
28
INDEX
Unique Index– Does not allow any duplicate values to be inserted into the
table
Creating Unique IndexCREATE UNIQUE INDEX name1_idx
ON emp(name) ;
![Page 29: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/29.jpg)
Nitin Singh/AAO RTI ALLAHABAD
29
INDEX
Composite Index– An index on two or more columns
Creating Composite IndexCREATE INDEX nam1_idx
ON emp(name, age) ;
![Page 30: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/30.jpg)
Nitin Singh/AAO RTI ALLAHABAD
30
INDEX
Dropping the indexDROP INDEX name_idx ;
![Page 31: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/31.jpg)
Nitin Singh/AAO RTI ALLAHABAD
31
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 excellent candidates for index , as they are often used to join parent table.
![Page 32: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/32.jpg)
Nitin Singh/AAO RTI ALLAHABAD
32
VIEWA 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
![Page 33: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/33.jpg)
Nitin Singh/AAO RTI ALLAHABAD
33
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 ;
![Page 34: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/34.jpg)
Nitin Singh/AAO RTI ALLAHABAD
34
SYNONYM
Another name for table or viewUsually 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 ;
![Page 35: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/35.jpg)
Nitin Singh/AAO RTI ALLAHABAD
35
Managing Database Transactions
Transaction: Sequence of work accomplished in a logical manner.
Commands to control transactions:– COMMIT
– ROLLBACK
– SAVEPOINT
![Page 36: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/36.jpg)
Nitin Singh/AAO RTI ALLAHABAD
36
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] ;
![Page 37: Nitin Singh/AAO RTI ALLAHABAD 1 SQL Nitin Singh/AAO RTI ALLAHABAD 2 OBJECTIVES §What is SQL? §Types of SQL commands and their function §Query §Index](https://reader035.vdocuments.us/reader035/viewer/2022062720/56649efd5503460f94c118d5/html5/thumbnails/37.jpg)
Nitin Singh/AAO RTI ALLAHABAD
37
Managing Database TransactionsSavepoint
• It is a point in a transaction that one can roll the transaction back to without rolling back the entire transaction.
• Syntax: SAVEPOINT s1 ;
ExampleSAVEPOINT s1 ;
DELETE FROM emp
WHERE name = ‘Anshuman’ ;
SAVEPOINT s2 ;
DELETE FROM emp
WHERE name = ‘Sumit’ ;
SAVEPOINT s3 ;
ROLLBACK s2;