SQL: STRUCTURED QUERY LANGUAGE

 SQL is Structured Query Language which is a computer language for storing,

manipulating and retrieving data stored in relational database.

 SQL is the standard language for Relation Database System. All relational database
management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres
and SQL Server uses SQL as standard database language.

 Also, they are using different dialects, such as:

o MS SQL Server using T-SQL,
o Oracle using PL/SQL,
o MS Access version of SQLis called JET SQL (native format) etc.

SQL PROCESS:
 When you are executing an SQL command for any RDBMS, the system determines
the best way to carry out your request and SQL engine figures out how to interpret the
task.

 There are various components included in the process. These components are:
o Query Dispatcher
o Optimization engines
o Classic Query Engine
o SQL query engine etc.

 Classic query engine handles all non-SQL queries but SQL query engine won’t handle
logical files.

DBMS (CS) 1
 SQL Query

Query Language
Processor Parser + Optimizer

DBMS Engine File Manager

+
Transaction Manager

Physical Database

DBMS (CS) 2
BASIC DATA TYPES:-
Data type Syntax Explanation(if applicable)
numeric numeric (p, s) Where p is a precision value; s is a scale value.
For example, numeric (6,2) is a number that has 4
digits before the decimal and 2 digits after the
decimal.
float float (p) Where p is a precision value.
character char (x) Where x is the number of characters to store. This
data type is space padded to fill the number of
characters specified.
character varying varchar2 (x) Where x is the number of characters to store. This
data type does NOT space pad.
date date Stores year, month, and day values.
Ex: 2013-05-03
time time Stores the hour, minute, and second values.
Ex: 15:51:36
timestamp timestamp Stores year, month, day, hour, minute, and second
values.
Ex:2013-05-03 15:51:36
Integer Integer

 Char (Width): This data type is used to accept characters.

Note:
 Maximum characters allowed are 255.
 By default, if no width specified default one (1) is taken into consideration.
 It is a fixed length data type.
Ex:- create table employee (Name char (30));

 Varchar2 (Width): It is a varying length data type.

Note: Maximum 2000 characters can be stored.
Ex:- create table employee (Name varchar2 (30));

 Number (width, decimal or point): It provides support to all types of numeric values.
Note:
 Maximum 40 digits allow.
 By default, if no width is specified, it is taken as eight (8).
 By default, if no point is specified, zero (0) is taken into consideration.

DBMS (CS) 3
 Ex:- a) Number (7,2)
Output: 9999.99

b) Number (5.0) or Number (5)

Output: 99999

 SQL: SQL is fourth generation non-procedural language. The SQL is further divided
into three sub-languages for various purposes:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)

DDL (Data Definition Language)

Commands
CREATE: To create objects in the database
ALTER: Alters the structure of the database
DROP: Delete objects from the database
TRUNCATE: Remove all records from the table, including all spaces allocated for the
records are removed.

DML (Data Manipulation Language)

Commands
SELECT: Retrieve data from the database
INSERT: Insert data into a table
UPDATE: Updates existing data within a table
DELETE: Deletes all records from a table, the space for the records remains

DCL (Data Control Language)

Commands
COMMIT: Save work done
SAVEPOINT: Identify a point in a transaction to which you can later roll back
ROLLBACK: Restore database to original since the last COMMIT
SET TRANSACTION: Change transaction options like what roll back segment to use
GRANT: Gives user’s access privileges to database
REVOKE: Withdraw access privileges given with the GRANT command

DBMS (CS) 4
NOTE: The difference between delete and truncate is delete will delete rows from the table
and if rollback applied, data can be given back whereas in truncate, we can not rollback. If
any row deleted, it is deleted. No triggers are fired.
EXAMPLE
We will present a number of sample queries using the following table definitions:
Sailors (sid: integer, sname: string, rating: number, age: real)
Boats (bid: integer, bname: string, colour: string)
Reserves (sid: integer, bid: integer, day: date)

SAILORS TABLE
sid sname rating age
22 Dustin 7 45.0
29 Brutus 1 33.0
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35.0
71 Zorba 10 16.0
74 Horatio 9 35.0
85 Art 3 25.5
95 Bob 3 63.5

BOATS TABLE
bid bname colour
101 Interlake blue
102 Interlake red
103 Clipper green
104 Marine red
RESERVES TABLE
sid bid day
22 101 10/10/98
22 102 10/10/98
22 103 10/8/98
22 104 10/7/98
31 102 11/10/98
31 103 11/6/98
31 104 11/12/98
64 101 9/5/98
64 102 9/8/98
74 103 9/8/98

DBMS (CS) 5
 DDL COMMANDS
SQL: CREATE TABLE
Syntax: CREATE TABLE table-name (Column_name1 datatype (size) constraint,
Column_name1 datatype (size) constraint, …. );
Example:
CREATE TABLE sailors
{
sid NUMBER
sname VARCHAR2(20)
rating NUMBER
age NUMBER (3,1)
};

Result: Table created.

SQL: TO DESCRIBE ANY TABLE

 This is used to view the structure of the table.

Syntax: DESC <table_name>;

Example: desc sailors;
Result:
Name Null? Type
------------------------------------ ---------- -------------
SID NUMBER
SNAME VARCHAR2 (20)
RATING NUMBER
AGE NUMBER (3,1)

DBMS (CS) 6
 SQL: ALTER TABLE
 Alter command is used to:
1. Add a new column.
2. Modify the existing column definition.
3. To include or drop integrity constraint.

 Syntax: alter table table_name add/modify (attribute datatype (size));

 Example for adding new column:
Alter table sailors add (age number (3,1));
Result: Table altered.
 Example for delete column:
Alter table sailors drop (age);
Result: Table altered.
 Example for modify column:
 Before modify let us check the structure of our example sailors
SQL> desc sailors;
Result:
Name Null? Type
------------------------------------ ---------- -------------
SID NUMBER
SNAME VARCHAR2 (20)
RATING NUMBER
AGE NUMBER (3,1)

 Example: Alter table student modify (sid number primary key);

Result: Table altered.

DBMS (CS) 7
 SQL> desc sailors;
Result:

Name Null? Type

------------------------------------ ---------- -------------
SID NOT NULL NUMBER
SNAME VARCHAR2 (20)
RATING NUMBER
AGE NUMBER (3,1)
SQL: DROP TABLE
 It will delete the table structure provided the table should be empty.
 Syntax:
Drop table table_name;
 Example:
Drop table sailors;
Result: Table dropped.

SQL: TRUNCATING TABLE

 If there is no further use of records stored in a table and the structure has to be
retained then the records alone can be deleted.

Syntax: Truncate table <table_name>;

Ex: Truncate table sailors;

Result: Table truncated;

SQL: RENAMING TABLE

Syntax: Rename <old_table> to <new_table>;

Ex: rename sailors to s1;

Result: Table renamed.

 To rename a column in an existing table, the SQL ALTER TABLE syntax is:

Syntax: ALTER table table_name RENAME COLUMN old_name to new_name

DBMS (CS) 8
 DML COMMANDS
SQL: INSERT VALUES IN TABLE
 This is used to add one or more rows to a table. The values are separated by commas
and the data types char and date are enclosed in apostrophes.
 The values must be entered in the same order as they are defined.

 Inserting a single row into a table Syntax:

INSERT INTO table_name VALUES (value1, value2, value3, ….);

 Example: insert into sailors values (22, ‘Dustin’, 2, 45.0);

 Result: 1 row created.

 Inserting more than one record using a single insert commands Syntax:

INSERT INTO table_name VALUES (&column1, &column2, &column3, …...);

 Example:
SQL> insert into sailors values (&sid, &sname, &rating, &age);
Enter value for sid: 22
Enter value for sname: ‘Dustin’
Enter value for rating: 7
Enter value for age: 45.0
Old 1: insert into sailors value (&sid, &sname, &rating, &age)
New 1: insert into sailors value (22, ‘Dustin’, 7, 45.0)

 Result:
1 row created.

 Skipping the fields while inserting syntax:

Insert into <table_name (column names to which data is to be inserted) > values (list
of values);

DBMS (CS) 9
 SQL: SELECT VALUES FROM TABLE

 It is used to retrieve information from the table. It is generally referred to as querying

the table. We can either display all columns in a table or only specify column from the
table.

Syntax: select * from table_name;

Example: select * from sailors;

Result:

SID SNAME RATING AGE

------- --------------------------- ------------- -----------
22 Dustin 7 45
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35
64 Horatio 7 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5

10 rows selected.

The retrieval of specific columns from a table:

Syntax: Select column_name1, ……, column_namen from table_name;
Example: Select sid, sname from sailors;
Result:
SID SNAME
------- ---------------------------
22 Dustin
29 Brutus
31 Lubber
32 Andy
58 Rusty
64 Horatio
71 Zorba

DBMS (CS) 10
 74 Horatio
85 Art
95 Bob
10 rows selected.

Elimination of duplicates from the select clause:

 It prevents retrieving the duplicated values.
 Distinct keyword is to be used.

Syntax: Select DISTINCT col1, col2 from table_name;

Example: Select DISTINCT sname from sailors;

Result:

SNAME
---------------------------
Dustin
Brutus
Lubber
Andy
Rusty
Horatio
Zorba
Art
Bob
9 rows selected.

Select command with where clause:

 To select specific rows from a table we include ‘where’ clause in the select command.
It can appear only after the ‘from’ clause.

Syntax: Select column_name1,…..., column_namen from table_name where condition;

Example: Select sname, rating from sailors where rating > 7;
Result:
SNAME RATING
--------------------------- -------------
Dustin 7
Brutus 1

DBMS (CS) 11
 Lubber 8
Andy 8
Rusty 10
Horatio 7
Zorba 10
Horatio 9
Art 3
Bob 3
Select command with order by clause:
Syntax: Select column_name1, ……, column_namen from table name where condition
orderby column_name;
Example: Select sid, sname from sailors order by rating;
Result:
SID SNAME
-------- ---------------------------------
29 Brutus
85 Art
95 Bob
22 Dustin
64 Horatio
31 Lubber
32 Andy
74 Horatio
58 Rusty
71 Zorba

10 rows selected.

Select command to create a table:

 Used to create new table with the same values as of existing table
Syntax: create table table_name as select * from existing_table_name;
Example: create table sailors1 as select * from sailors;
Result: Table created.

DBMS (CS) 12
Select command to insert records:
 Used to insert the same values as of the existing table

Syntax: insert into table_name (select columns from existing_table_name);

Example: insert into sailors1 (select * from sailors);
Result: 10 rows created.

SQL: Update Command

 It is used to alter the column values in a table. A single column may be updated or
more than one column could be updated.

Syntax: update table_name set field=values where condition;

Example: update sailors set rating=10 where rating=1;

Result: 1 row updated.

SQL> select * from sailors;

SID SNAME RATING AGE

------- --------------------------- ------------- -----------
22 Dustin 7 45
29 Brutus 10 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35
64 Horatio 7 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5

10 rows selected.

DBMS (CS) 13
 SQL: Delete command
 After inserting row in a table, we can also delete them if required. The delete
command consists of a clause from clause followed by an optional where clause.

Syntax: Delete from table where conditions;

Example: delete from sailors where sid=58;

Result: 1 row deleted.

 To delete all the records in the table

Example: delete from sailors;

Result: 9 rows deleted.

DCL commands
SQL: commit command
 This will commit (write to database) the transactions done by the DML.
 After inserting, updating or deleting the transactions the user does not want to commit
the changes, then the user can rollback the transaction using the command:

Syntax: SQL> commit;

Result: Commit complete.
SQL: Rollback command
 It will rollback the transaction and will not commit the changes to the database.
 SQL> rollback;
Rollback complete.

SQL: save point command

 It will save the transactions under the name s1.
 SQL> savepoint s1;
Result: Savepoint s1 created.

DBMS (CS) 14
 SQL: GRANT command

 The grant statement provides various types of access to database objects such as
tables, views and sequences and so on.
 It is used to create users and grant access to the database.
 It requires database administrator (DBA) privilege, except that a user can change their
password.
 A user can grant access to their database to their database objects to other users.
Syntax:
GRANT <object privilege> ON <object_name> TO <username> [WITH GRANT
OPTION];

SQL: Revoke command

 The REVOKE statement is used to deny the Grant given on an object.

Syntax: REVOKE <object privilege> ON FROM <username>;

DBMS (CS) 15