structured query language s q l. what is sql it is a database programming language developed by ibm...
TRANSCRIPT
What is SQL
It is a database programming language developed by IBM in the early 1970’s.
It is used for managing and retrieving data stored in database system
Mostly all DMMS ‘s support SQL
What can SQL do !!!
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new database
SQL can create tables in a database
SQL can create views in a database
SQL can set permissions on tables , procedures and tables
Advantages of using SQL
SQL commands consists of English language words such as create, insert , delete etc, so it is easy to learn
SQL is a non procedural language. We simply specify what information is to be retrieved rather than how to retrieve it
SQL is a keyword based language . Each command of SQL begins with a unique keyword
SQL is a free format language , ie parts of SQL statements do not have to be typed at particular locations on the screen
SQL is a portable language . If we follows standard syntax we can move SQL commands from one DBMS to another
SQL can be used by variety of users from DBA(Database Administrators) , application developers to end users.
SQL statements
1. Data Definition Language (DDL) : CREATE,ALTER,RENAME, DROP
2. Data retrieval : SELECT
3. Data Manipulation Language (DML) : UPDATE,DELETE,INSERT
4. Transaction Control Language (TCL) : COMMIT,ROLLBACK,SAVEPOINT
5. Data Control Language : GRANT,REVOKE
Data Definition Language (DDL)
DDL commands are subset of SQL commands .These commands are used to perform three main function on database objects.(tables).They are
1. Defining or creating database objects through CREATE statement
2. Modifying database objects through ALTER statement
Removing definition of database object through DROP statement
Definition of all database objects is stored in permanent table called data dictionary.
Data dictionary stores data about data called metadata
Data Manipulation Language (DML)
Subset of SQL commands used to query and manipulate the database.
INSERT : Inserting new records in to a table or relation.
UPDATE : Modifying existing records.
DELETE : Deleting unwanted records.
SELECT statement can be considered in the category of DML.
It is used to retrieve existing records from the database
Types of DML
1. Procedural DML : It not only require a user to specify what data is needed, but also the procedure of how to retrieve the data.
2. Non-Procedural DML : It require only to specify what data is needed without giving details of procedure.
Eg : INSERT,UPDATE,DELETE,SELECT
Transaction Control Language (TCL)
When we make changes on a table suing insert,delete or update,these are temporary changes.
COMMIT is used to make theses changes permanent. After applying COMMIT transaction is successfully completed.
It is managed by TCL commands
COMMIT : used to make the changes permanent
ROLLBACK : used to undo all changes of a transaction
SAVEPOINT : Identify a point or mark in a transaction since COMMIT.
Incase of failure u can undo last transaction , ie rollback up to that save point
Data Control Language (DCL)
It controls type of data access to the database
GRANT : is used to assign user’s access privileges to the database.
REVOKE : used to withdraw the access privileges given by GRANT
Rules for SQL commands
1. SQL commands can be typed in lower or uppercase . It is not case sensitive
2. It can be typed in a single line or more than one line
3. A semi colon is used to terminate SQL command.
4. A comma (,) can be used to separate parameter
5. Characters and date constraints or literals must be enclosed in single quotes (‘A’)
Reserved words can’t be used as identifier’s name . Some of the words are
ADD,SET,DATE,CHAR,DROP,ELSE,INTO,LIKE,CREATE,DELETE,VARCHAR,AS,OR,ON,FLOAT,ALTER,CHECK etc
A command can be typed either in full or first four characters
Database objects
1. Relation or table : used to ……………..
2. View : it is a logical table , part of original table . Different views of database are assigned to different users.
3. Sequence : it is used to identify the records in a table by generating the primary key value to them
4. Index : it improve response time of queries
5. Synonym : Used to give alias name or alternative names to objects
Naming conventions
1. Name must be 1 to 30 characters long. Name of the database can be 8 characters long
2. Names of objects are not case sensitive
3. Names should not contain quotation marks
4. First character of the name must be an alphabet.
5. Names of alphabets can have alphanumeric values
6. Reserved words cannot be used for naming objects
SQL data types
1. NUMBER
It may contain a number with or without decimal point and a sign. An attribute of a relation can have numbers in three forms
a). NUMBER : Here we don’t specify the width or precision . Default one ‘ll be taken
b). NUMBER (P) : Here we specify the width or precision without any decimal point.
It is mainly used for EmpNo, Age
c). NUMBER (P,S) : Here we specifies both precision P and scale S .
P is total number of decimal digits and
Scale S is total number of digits to the right of the decimal point.
2. CHARACTER
It contain fixed length character data . Abbreviated as CHAR , specified as
CHARACTER [(Size)] or CHAR [(Size)].
It is used for Item_code , the length of which remains same for all items
3). VARCHAR2
It contain varying length character data and is specified as
VARCHAR2 (Size)
Maximum size must be given .Attributes such as EName , DName uses this data type in which length varies frequently.
4). DATE
Used to store date and time value . It is defined simply as
DATE
It is stored in standard format as
DD- MON-RR
DD : Day
MON : Month
RR : Year
SQL Commands
Points to remember :
1. Every SQL command must be terminated with a semicolon (;)
2. SQL is a case insensitive language
Presentation style
1. All SQL key words are written in capital letters
2. Table names are written with their first letter capital
3. All SQL commands are indented properly to provide readability
DDL commands
1. CREATE TABLE :
Each table in the database is created by using CREATE TABLE command
Syntax :
CREATE TABLE <table name>
(<column name> <data type> [size],
< column name> <data type> [size]…..);
Table name and column name are given by the user.
When a table is created it must have at least one column.
Table is empty , it has no data. Only the definition of the table is stored in the database.
This definition is called schema of the table
Eg :::@@ IMP @@:::
1. Create a table EMPLOYEE which has the following columns
Column Name Data Type Description of column
EmpNoEnameHire_dateJobSalaryDeptNo
Number (3)Varchar2(30)DateVarchar2(15)Number (7,2)Number (2)
Employee Identity NumberEmployee NameDate of joining of EmployeeJob title of employeeBasic salary of employeeDepartment number of employee
CREATE table EMPLOYEE (EmpNo Number (3), EName Varchar2(30) , Hire_date Date , Job Varchar2(15) , Salary Number (7,2) , DeptNo Number (2)) ;
Table created
Including constraints
Constraints :
Constraints are the mean by which you can prevent invalid data entry in to the table.
Eg : salary must not exceed 30,000.00
Constraints are stored in data dictionary . if we don’t specify the name of the constraint Oracle automatically gives a name. (SYS_Cn)
We can view constraint in User_constraint in data dictionary,
Defining constraint
Various constraints which can be defined on database object are
1. NOT NULL
2. UNIQUE KEY
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
You can create constraint either
1. At the time when the table is created using CREATE TABLE or
2. After the table has been created using ALTER TABLE.
constraint can be defined at two levels when table is created
Column level : It is used when it is to be applied on a single column
Table level : It is used when it is to be applied on more than one column.
All the constraints except NOT NULL can be defined at table level
Detail about different types of constraints
NOT NULL
Null ???
It is a value which is unknown ,unavailable , unassigned or inapplicable.
It is neither a zero or a blank space, because zero is a numeric value and space is a character.
Column of any data type can contain NULLS
NOT NULL ensures that the column cannot contain a null value.
SQL > Create Table Student
( Name Varchar2 (25) Not Null ,
Class Number (2) ) ;
Explain !!!
Here constraint name is unnamed. We can provide name by the command
Create Table Student
( Name Varchar2 (25) constraint Name_st Not Null ,
Class Number (2) ) ;
Here Name_st is the constraint name given by us
UNIQUE
It specifies a column or combination of columns whose value must be unique for all rows in the table.
No two rows can have duplicate values.
It can be defined at the column level or table level.
Column level : It references a single column
SQL > Create Table Student
(Rollno Number (3) Unique,
Name Varchar2(25) Not Null,
Class Number (2) ) ;
Here no two records can have same roll number
Table level :
It references one or more columns
SQL > Create Table Student
(Rollno Number (3),
Name Varchar2(25) Not Null,
Class Number (2),
Unique (Rollno) ) ;
Column included in unique constraint is known as UNIQUE KEY
PRIMARY KEY
One limitation of candidate key is that it allows null values in the column.
Primary key constraint allow two things
Unique identification of each row in the table
No column that is a part of primary key can contain a null value
It can be defined at column or table level
Column level :
SQL > Create Table Student
(Rollno Number (3) PRIMARY KEY,
Name Varchar2(25) ,
Class Number (2) ) ;
Null value and duplicate for Rollno is not allowed
Table level :
SQL > Create Table Student
(Rollno Number (3),
Name Varchar2(25) ,
Class Number (2)
PRIMARY KEY (Name , Class) ) ;
FOREIGN KEY
It designates a column or combination of columns as a foreign key and establishes it’s relationship with a primary key or unique key in different table.
Eg : EMPLOYEE and DEPARTMENT
Column level :
SQL > CREATE TABLE Employee
(EmpNo Number (3),
………………….
salary Number (7,2),
DeptNo Number(2) REFERENCES Department (DeptNo) ) ;
Table level :
SQL > CREATE TABLE Employee
(EmpNo Number (3),
………………….
salary Number (7,2),
DeptNo Number(2)
FOREIGN KEY (DeptNo) REFERENCES Department (DeptNo) ) ;
CHECK
Values in some of the columns are to be within a certain range or they must satisfy certain condition.
Given to column so that all value must satisfy the condition.
SQL > CREATE TABLE Employee
(EmpNo Number (3),
Ename Varvhar2(30),
Salary Number (8 , 2) CHECK (salary > 0) , …………………… );
We can apply it for table level also
DEFAULT
It is used to assign a default value to a column.
It prevents null entry in a row.
SQL > Create Table Student
(Rollno Number (3) UNIQUE,
Name Varchar2(25) NOT NULL,
Class Number (2)
Adm_date DATE DEAFULT SYSDATE );
Adding a constraint
Syntax :
ALTER TABLE Tablename ADD [constraint type] (col) ;
Eg : ALTER TABLE Employee ADD PRIMARY KEY (EmpNo) ;
Dropping a constraint
Syntax :
ALTER TABLE Tablename
DROP CONSTRAINT Constraint name ;
Eg : ALTER TABLE Employee
DROP PRIMARY KEY ;
OR
ALTER TABLE Employee
DROP CONSTRAINT EmpName ;
Disabling constraints
Syntax :
ALTER TABLE Tablename DISABLE CONSTRAINT constraint_name ;
Eg : ALTER TABLE Employee
DISABLE CONSTRAINT EmpNumber ;
Enabling constraints
Syntax same as that of previous one
E g :
ALTER TABLE Employee
ENABLE CONSTRAINT EmpNumber ;
Viewing constraints
E g : SELECT constraint_name , column_name
from user_cons_columns
where tablename = ‘Employee’;
Drop table
Dropping a table not only deletes the data contained in the table but also removes the definition of it’s structure from the database.
Syntax : DROP TABLE <Table name> ;
E g: To drop employee table
DROP TABLE Employee ;
Alter table
The structure of a table can be altered by applying the alter table statement.
Table can be altered in one of the three ways
1. By adding a column to an existing table
2. By changing a column definition (data type)
3. By dropping a column of a table.
Adding a new column in a table
Syntax : ALTER TABLE <table name>
ADD <column name> <data type> [constraint definition] ;
E g : ALTER TABLE Student
ADD grade char(1);
Modifying existing column
It changes the data type or size of an existing column. Here we uses MODIFY clause.
Syntax : ALTER TABLE <table name>
MODIFY (<column name> <data type> [size]) ;
Here
1. Width of the column can be increased at any time.
2. Number of decimal place in a NUMBER column can be increased or decreased at any time
3. Data type of a column can only be changed if the column contains null values
Eg : NUMBER (7 , 2) to NUMBER (8 , 2)
ALTER TABLE Employee
MODIFY (Salary Number (8,2));
Dropping a column
A column can be dropped from a table if it is no longer required.
Syntax :
ALTER TABLE <Table name>
DROP COLUMN <column name> ;
We can drop more than one columns at a time
ALTER TABLE <Table name>
DROP COLUMN (<column 1> , <column 2>) ;
To drop grade from student table sql command is
SQL > ALTER TABLE Student
DROP COLUMN grade ;
It will drop the column’s definition from the database
Renaming an object
We can change the name of an existing table by using RENAME command.
Syntax : RENAME <table name> to <new table name>
Eg: RENAME Employee To Empl ;
Truncating a table
TRUNCATE command deletes all the rows of a table. It empties a table completely
Syntax : TRUNCATE TABLE <Table name> ;
E g: TRUNCATE TABLE Employee ;
Adding comment to a table
Syntax for adding a comment to a column of a table is
COMMENT ON COLUMN <table name> . <column name > IS ‘COMMENT’;
Eg : COMMENT ON COLUMN Employee.Job IS ‘Job Title Of Employee’;
Dropping a comment
Syntax : COMMENT ON COLUMN <Table name> . <Column name > IS ‘ ’ ;
E g :
COMMENT ON TABLE Employee IS ‘ ’;
COMMENT ON COLUMN Employee.Job IS ‘’;
Data manipulation language(DML)
These are used to manipulate the existing object of a database such as tables.
1. INSERT statement
It is used to store data in a table.
Syntax :
INSERT INTO <table name>[<column list>] VALUES (<list of values>) ;
SQL > INSERT INTO Employee Values
(111,’Lucky’,’10-June-2003’,’Clerk’) ;
We can view the table by
SQL > SELECT * from Employee ;
Listing columns in the INSERT clause
When we do not want to supply values for all the columns we ‘ll use it.
INSERT INTO Employee (Empno,Ename,Hire_date , salary) Values (112,’Mansi’,’05-Sep-2011’,1300) ;
Inserting new row with null values
INSERT INTO Employee values
(112,’Mansi’, ’05-Sep-2011’,NULL ,1300,Null);
Use of substitution variables to insert
values
SQL > INSERT INTO Employee values
(&Empno , &Empname , &Job , &salary);
Copying rows from another table
We can insert the rows from another table by INSERT command.
Here we insert values in Employee1 from Employee table
SQL > INSERT INTO Employee1
Select * from Employee
where Empno BETWEEN 103 and 105 ;
UPDATE statement
It changes or modifies the value of specified column in a table.
Syntax : UPDATE <table name>
SET <column name >=<value>
where <condition>;
Updating multiple rows and multiple
columns
UPDATE Employee1
SET Salary = Salary + 1500 , Job = ‘Manager’
where Deptno IN (11,12,13) ;
Updating rows based on another table
UPDATE Employee1
SET job = ‘Manager ‘
WHERE Empno = (Select EmpNo From
Employee where Hire_Date = ’05-Nov-1999’ ) ;
DELETE Statement
It does exactly opposite to that of an INSERT statement.
It is used to DELETE rows from a table
Syntax : DELETE [FROM] <table name>
[WHERE <condition>];
Where clause is used to restrict the number of rows to be deleted
DELETE with WHERE clause
Syntax : DELETE [FROM] <table name>
WHERE <condition> ;
SQL > DELETE from Employee
where Salary < 25000 ;
DELETE without WHERE clause
Syntax : DELETE FROM <table name> ;
SQL > DELETE FROM Employee1 ;
OR
DELETE * FROM Employee1 ;
Deleting rows based on conditions from another table
SQL > DELETE FROM Deptt
WHERE DeptNo Not In
(Select DeptNo from teacher) ;
Transaction control commands
A transaction is a unit of work that contains logically related statements . A transaction has a beginning and an end.
Commit and rollback statements are used to control transaction.
Commit (Making data manipulation
permanent)
Commit is used to finish the current transaction by making the changes permanent.
After applying the commit we cannot retain the previous state of the data.
Rollback (Undo data manipulation
changes)
ROLLBACK is used to cancel or undo all the changes in the current transaction.
SQL >UPDATE Teacher Set Salary = 18000
WHERE Tno=103 ;
SQL >ROLLBACK ;
SQL > Select * from teacher ;
Making simple queries (select)
It is used to retrieve information from a table . it can be used to access a row , a column , a group of rows , a group of columns or all the rows and columns in a table.
Syntax : SELECT <column1>,[<column2>…]
FROM <table name> ;
EMPNO ENAME HIRE_DATE JOB SALARY DEPTNO
101 Sunil 01-Jul-01 President 27000 11
102 Pavitra 11-Jan-02 Clerk 18000 12
103 Seema 18-Feb-95 Manager 20000 13
104 Sanjay 28-Aug-97 President 23000 14
105 Deepa 05-Nov-99 Operator 13000 11
106 Sanker 09-Mar-2006
Manager 22000 11
107 Surekha 29-Apr-03 President 25000 13
Selecting columns
Selecting all columns
Syntax : SELECT * FROM <table name> ;
• is used for displaying all the columns from a table.
To retrieve all columns from Employee
SQL > SELECT * FROM Employee ;
Selecting specific columns
Syntax : SELECT <column1>, <column2>,….
FROM <table name> ;
Display Empno , Ename and job from Employee
SQL > SELECT Empno , Ename , Job
FROM Employee ;
LIKE Operator
Powerful feature of SQL is pattern matching using LIKE .
Two symbols are used to construct the search string
1. % Represent any sequence of zero or more characters
2. _ (underscore) Represent any single character
To list all the employees whose names begin with letter ‘S’ in Employee table.
SQL > SELECT * FROM Employee
WHERE Ename LIKE ‘S%’ ;
To list the details of employee having the second character of their name as ‘a ‘ in the table Employee
SQL > SELECT * FROM Employee
WHERE Ename LIKE ‘_a%’ ;
Display name job and department number of employees whose name if of seven characters long from Employee table
SQL > SELECT Ename , Job , DeptNo
FROM Employee
WHERE Ename LIKE ‘_ _ _ _ _ _ _ ’;
IS NULL Operator
It is for testing null values in a table
To list class and name of students who have not been assigned any grade .
SQL > SELECT Name , Class FEOM SPORTS WHERE Grade IS NULL ;
Set III : Logical operators
1. OR
List the rollno , name , game ,grade of all students with game either cricket or tennis
SQL > SELECT rollno , name , game , grade from SPORTS
WHERE game = ‘Tennis’ OR game = ‘Cricket’ ;
2. AND
List the name game grade of all the students in table SPORTS with game = tennis and grade = A is
SQL > SELECT name , game , grade from SPORTS WHERE game= ‘tennis’ AND grade =‘A’;