CHAPTER 14

SQL Commands

Def -
SQL is a structured query language for storing, manipulating and retrieving data in relational
databases.
SQL is the standard language for Relational Database systems like Oracle, Sybase, MySQL, etc.
History –
1970 – Dr E.F.Codd of IBM known as father of relational database described a relational model for
databases.
1974 – Structured Query Language appeared.
1978 – IBM released a product named system/R
1986 – IBM developed first prototype of relational database, and was released by Relational
software it later became Oracle.
Two standard Organization ANSI (American National Standards Institute) and the ISO (International
standards Organization) currently promote SQL.
Characteristics of SQL-
1. It is very easy to learn and use.
2. Large volumes of data can be handled easily.
3. It is a non procedural language.
4. SQL can be linked to other high level languages.
5. SQL can change system security settings.
6. All user permissions on databases or tables.
Functions of SQL -
1. Allows users to describe the data.
2. Allows users to create and drop databases and tables.
3. Allows users to access data in RDBMS.
4. Allows users to define the data in database and manipulate the data.
5. Allows users to create views, stored procedures, etc.
6. Allows embedding within other languages using SQL modules.
7. Allows users to set permissions on tables, procedures and views.

SQL Commands –
Commands are instructions used to communicate with the database to perform specific task such
as creating table, add data to table, modify, delete, etc. The commands used are CREATE,
SELECT, DELETE, DROP, etc.

Commands in SQL are grouped as

DDL (Data Definition Language) – defines the conceptual schema providing a link between the
logical and the physical structure of the database.

Functions of DDL
1. DDL define the physical characteristics of each record.
2. DDL describes the schema and subschema.
3. DDL indicates the keys of the records.
4. DDL provides data security measures.
5. DDL provides means for associating related records or fields.
6. DDL provides for Logical and Physical data Independence.

Commands of DDL
CREATE – command creates a new table, a view of a table, etc.
ALTER – command modifies an existing database object such as a table.
DROP – command deletes an entire table.

DML (Data Manipulation Language) –

These SQL commands are used for storing, retrieving, updating and deleting the records in a table.

Functions –
1. DML provides data manipulation techniques like selection, insertion, deletion, update,
replacement, retrieval, sorting and display of data or records.
2. DML facilitates use of relationship between the records.
3. DML enables the user and application program to be independent of physical data structure.
4. DML provides for independence of Programming languages.

Commands of DML
INSERT – Command to Add new records (rows) of data in a table.
UPDATE - Command to Modify the existing records (rows) in a table.
DELETE - Command to Delete the existing records (rows) in a table.

DCL (Data Control Language) –

These SQL commands are used for providing security to database objects.
GRANT – Command gives a privilege to user.
REVOKE – Command takes back privileges granted from user.

DQL (Data Query Language) Command –

SELECT – Querying or retrieving data from one or more tables in the database.

Data Types supported by SQL – SQL Server offers different categories of data types
1. Exact numeric data types – Int, numeric
2. Floating Point numeric data types – Float, Real
3. Date and Time Data types – datetime, date, time
4. Character, strings data types – char(fixed length), varchar(Variable length)

Data Types supported by ORACLE

1. NUMBER – Used to store numeric value in a field or column. It may be decimal, integer or a real
value. Syntax – Number(n, d)
where n – specifies no of digits, d – specifies no of digits to the right of the decimal point.
Eg. marks Number(3), Price Number(10,2)
2. CHAR – used to store character data type in a column.
Syntax – CHAR(size) where size specifies number of characters
Eg. name CHAR(30)
values of this type must be enclosed in single quotes eg. ‘apple’
3. VARCHAR/VARCHAR2 – This type is used to store variable length alphanumeric data.
Syntax – VARCHAR(size) where size specifies number of characters Eg. address VARCHAR(60)
4. Date – Used to store dates in columns. Default format is DD-MON-YY.
5. TIME – used to store time format is HH-MM-SS.
Operators in SQL
Arithmetic operators + - * / %

Comparison Operators
= equal to != or <> not equal to
> < >= <=
!< not less than !> not greater than
Comparison Operators example:
Assume variable a = 10 and variable b = 20, then:
Logical Operators –
ALL Used to compare a value to all values in
another value set.
AND to combine multiple conditions
ANY Used to compare a value to any applicable
value
BETWEEN used to search values within a range
EXISTS used to search for the presence of a row in
a table
IN Used to compare a value to a list of values
that have been specified
LIKE Used to compare a value to similar values
NOT Reverses the meaning of logical operator
eg. NOT BETWEEN
OR Is used to combine multiple conditions
IS NULL Is used to compare a value with a NULL
value
UNIQUE Check for uniqueness (no duplicates)

SQL Expressions –
SQL Expressions are like formulas and written in query language.
TYPES –
1. SQL – Boolean Expression – fetches the data on the basis of matching single
value.
Syntax : SELECT col1, col2 from tablename where single value matches expr;
Eg : SELECT * from Employees where age>=35;

2. Numeric Expression – used to perform any mathematical operation in any query.

Eg : SELECT (15+6) as Addition
SELECT count(*) as Record from Employees;

3. Date Expression – Return current system date and time values.

Eg : SELECT current_timestamp;

SQL Constraints –
Constraints are the rules enforced on data columns of table. They ensure the accuracy and
reliability of data in the database.
Constraints can be Column Level or Table Level – commonly used constraints are primary key,
foreign key, Not NULL, unique constraint, etc.

Constraints can be defined in 2 ways

1. Constraints can be specified immediately after column definition. This is called column level
definition.
2. Constraints can be specified after all the columns are defined. This is called table level definition.

SQL Primary key Constraint –

This constraint defines a column or combination of columns which uniquely identifies each row in
the table.
Syntax to define a Primary key at column level:
column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define a Primary key at table level:
[CONSTRAINT constraint_name] PRIMARY KEY(column_name1,column_name2,..)
Where column_name1, column_name2 are the names of the columns which define
the primary Key.
In the syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
Eg : CREATE TABLE Employee (id number(3) primary key, name char(20));

SQL Foreign key Constraint –

This constraint identifies any column referencing the PRIMARY KEY in another table. It
establishes a relationship between two columns of different tables.
Syntax to define a Foreign key at column level:
[CONSTRAINT constraint_name] foreign key REFERENCES
Referenced_Table_name(column_name)
Syntax to define a Foreign key at table level:
[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES
referenced_table_name(column_name);
Eg : CREATE TABLE Product (pid number(3) foreign key references master(pid), pname
char(20));
NOT NULL constraint –
This constraint ensures all rows in the table contain a definite value for the
column which is specified as not null.
Syntax to define a Not Null constraint:
[CONSTRAINT constraint name] NOT NULL
Eg : CREATE TABLE Employee (id number(3) primary key, name char(20) NOT NULL );
Unique Key constraint –
This constraint ensures that a column or a group of columns in each row have a distinct value.
A column can have a null value but the values cannot be duplicated.
Syntax to define a Unique key:
[CONSTRAINT constraint_name] UNIQUE
Eg : CREATE TABLE Employee (rollno number(3) unique)
Check Constraint –
This constraint defines a business rule on a column. All the rows must satisfy this rule. The
constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:
[CONSTRAINT constraint_name] CHECK (condition)

Implementation of SQL Commands

CREATE TABLE command – used to create a new table.
Syntax –
CREATE TABLE table_name(colname1 datatype, colname2 datatype, ……, \ colnameN
datatype,);
Eg. CREATE TABLE student(regno NUMBER(6), name VARCHAR2(25), dob DATE, fees
NUMBER(10,2));

ALTER TABLE command – Used to MODIFY or change the existing table.

Eg. ALTER TABLE employee modify salary number(12,2);
ALTER TABLE student add(total number(3));

DROP TABLE command – used to remove an existing table from the database.
Syntax – DROP TABLE table_name;
Eg : DROP TABLE employees;
DML commands –
INSERT command- used to add new rows of data in the table.
Syntax – INSERT INTO tablename [(col1, col2,… colN)] VALUES ( value1, value2,……..
valueN);
Here col1, col2 are the names of the columns in the table into which data is to be inserted (to
add data to all the columns in the table it is not necessary to mention the column names)
The [ ] bracket indicates that the items inside the bracket are optional.
INSERT INTO command performs
1. Creates a new row in the table.
2. Loads the values passed into the column specified

Eg. INSERT INTO student (regno, name, dob, fees) values

(1402,’John’,’04-nov-96’,23000);
OR
INSERT INTO student values (1402,’John’,’04-nov-96’,23000);

SELECT command – Statement is used to fetch the data from database table.
Query – is a request for some information from the database.
SQL uses SELECT command for data retrieval. This command can perform selection as well
as projection operations of Relational Algebra.

Syntax – SELECT columnlist FROM tablename [WHERE CONDITION(S)]

Examples.
Selecting all columns and all rows in a table. Command is SELECT * FROM student;
Selecting a set of columns from a table. Command is SELECT regno, name FROM student;
Removing duplicate data (DISTINCT) SELECT DISTINCT city FROM student;

Extracting specific rows from a table using WHERE clause

SELECT columnlist FROM tablename [WHERE CONDITION(S)]
the WHERE clause is used to extract only those records that fulfill a specified condition.

Eg. SELECT * FROM student where marks >= 85;

Or SELECT regno,name FROM student where regno = 104;

Logical operators –
AND operator –allows to combine multiple conditions in a SQL statement where clause
SELECT col1,col2,…,coln FROM tablename where [condition 1] AND [condition 2];
Eg. SELECT id,name FROM employees where (salary>30000) AND (salary < 40000);

OR operator –allows to combine multiple conditions in a SQL statement where clause

SELECT col1,col2,…,coln FROM tablename where [condition 1] OR [condition 2];
Eg. SELECT id,name FROM employees where (salary>30000) OR (age > 50);

UPDATE command – used to change data or modify the existing data in a table.
Syntax – UPDATE tablename SET colname = value [WHERE condition]
Eg.
i. To change fees of all students to 30000 UPDATE student SET fees = 30000;
ii. To reduce the fees of students by 500 UPDATE student SET fees = fees - 500;
DELETE command –
used to remove row or rows from a table, where condition can be used.
Syntax –DELETE FROM tablename [where condition]
Eg. DELETE FROM student where fees >=40000;

ORDER BY –
It is used to sort the data in ascending or descending order based on one or more columns.
Syntax – SELECT columnlist FROM tablename [WHERE CONDITION(S)] [ORDERBY
colname(s)] [ASC|DESC]
Eg: SELECT * FROM student ORDER BY rno;
Eg. SELECT regno, name, marks, combi FROM student order by name;

GROUP BY –
It is used with the select statement to arrange identical data into groups.
GROUP BY clause follows the where clause in SELECT statement and precedes the ORDER
BY clause.
Syntax – SELECT columnlist FROM tablename [WHERE CONDITION(S)]
[GROUP BY colname(s)] [ORDER BY colname(s)] [ASC|DESC]
SELECT regno,name,marks,combi FROM student groupby combi orderby name

GROUP Functions –
are built in SQL functions that operate on group of rows and return a single value for the entire
group.
Functions are –
Count( ) - returns the number of rows in the table that satisfies the condition.
Eg. SELECT count(*) from employees;
Max() - returns maximum value from a column.
Eg. SELECT max(salary) from employees;
Min() - returns minimum value from a column.
Eg. SELECT min(salary) from employees;
Avg() – returns average value from a numeric column.
Eg. SELECT avg(salary) from employees;
Sum() – returns sum of a numeric column.
Eg. SELECT sum(salary) from employees;
DISTINCT – is used with SELECT to eliminate all the duplicate records and fetch only
unique records.
Syntax : SELECT Distinct colname from tablename;
Eg: SELECT Distinct name from employees;

JOINS – are used to fetch data from two or more tables based on a join condition which is
specified in the WHERE clause.
Types of JOINS
1. INNER JOIN –
Returns rows when there is a match in both tables also called EQUI JOIN.
Syntax – SELECT table1.colname, table2.colname…. FROM table1 INNER JOIN table2 ON
table1.commonfield = table2.commonfield
Eg. SELECT id, name, amount FROM customers INNER JOIN orders ON customers.id =
orders.cid
OR
SELECT id, name, amount FROM customers, orders WHERE customers.id = orders.cid
2. LEFT JOIN –
Returns all rows from the left table even if there are no matches in the right table.
3. RIGHT JOIN –
Returns all rows from the right table even if there are no matches in the left table.
4. FULL JOIN –
Returns rows when there is a match in one of the tables.
5. SELF JOIN –
It is used to join a table to itself as if the table were two tables, temporarily renaming at least
one table in the SQL statement.
6. CARTESIAN JOIN –
Returns the Cartesian product of the sets of rows from the two or more joined tables.
Syntax – SELECT table1.col1, table2.col2 …. FROM table1, table2….;
Eg. SELECT * FROM customers, orders;
In above query there are two tables customer and orders. There are 5 records in customers
and 4 records in orders. In Cartesian product each row of customers is concatenated with each
row of orders. The total number of records after Cartesian product is 20(5 X 4).

NULL – It is a term used to represent a missing value.

Eg. SELECT id, name from employees where salary IS NOT NULL;

Creating views –
Views can be created from a single table, multiple table or another view.
CREATE VIEW viewname AS SELECT name,age from employees;

COMMIT command –
It is used to save changes invoked by a transaction to the database.
Syntax : COMMIT;

DCL commands –
Are used to enforce database security in a multiple user database environment.
GRANT –
This command is used to provide access or privilege on the database object to the user.
Syntax: GRANT privilege_name on object_name to User_name
Privilege_name is the access right granted to the user, it can be All, Execute, Select, etc.
Object_name can be table, view, etc.
User_name is to whom access is being granted.
Eg. Grant Select on employee to user1;
Revoke –
This Command removes user access rights or privileges to the database objects.
Syntax: REVOKE privilege_name on object_name from User_name
Eg. Revoke Select on employee from user1;
DUAL table in Oracle –
DUAL is a single row and single column dummy table provided by Oracle. This is used to
perform mathematical calculations without using a table.
Eg. SELECT 3*2 from dual;
Output – 3*2
6
Privileges and Roles
Priveleges
It defines the access rights provided to a user on a database object.
There are 2 types of Privileges
1. System Privileges –
This allows the user to create, alter or drop database objects.
2. Object Privileges –
This allows the user to execute select, insert, update, or delete data from database objects to
which the privileges apply.
Roles –
These are set of privileges grouped together. Eg. System role.
Connect – Privileges granted to this role are create table, create view, etc.
Resource – Create procedures, create trigger, etc.
DBA – All system privileges.

SQL built-in functions –

There are 2 types of SQL functions in Oracle version of SQL
1. Group Functions –
These functions group the rows of data based on the values returned by the query. Group
functions are used to calculate aggregate values like sum, average, count, etc.

2. Single Row functions or scalar functions -

It returns a value for every row that is processed in the query.

Types of Single Row Functions

Numeric functions –
Accept numeric input and return numeric values.

FunctionName Return value Example

Abs(x) Absolute value of number(x) Abs(1.2)=1
Abs(-1) = 1
Ceil(x) Integer value that is greater Ceil(2.83) = 3
than or equal to the number Ceil(2.49) = 3
(x)
Floor(x) Integer value that is less than floor(2.83) = 2
or equal to the number (x) floor(2.49) = 2
Trunc(x,y) Truncates value of number Trunc(5.7) = 5
(x) upto ‘y’ decimal places Trunc(142,-1) = 140
Round(x,y) Rounded off value of the Round(123.456,1) = 123.5
number ’x’ upto ‘y’ decimal Round(124.456,-1) = 120
places.

These functions can be used on database columns

e.g. Select Round(unit_price) from product;
Character or Text functions –
These are used to manipulate text strings.
FunctionName Return value Example
Lower(string) all the letters in string is Lower(‘GOOD’) = good
converted to lowercase
Upper(string) all the letters in string is Upper(‘good’)=GOOD
converted to uppercase
Initcap(string) All the word first character will Initcap(‘good
have capital letters. morning’)=Good Morning
Ltrim(string,trim_text) All occurrence of trim text is Ltrim(‘Good
removed from left of string Morning’,’Good’) = Morning
Rtrim(string,trim_text) All occurrence of trim text is Rtrim(‘Good
removed from right of string Morning’,’Good’) = Good
Substr(string,m,n) Returns ‘n’ number of characters substr(‘Good Morn’,6,4)=
from string starting from the ‘m’ Morn
position.
Length(string) Returns number of characters in length(‘Good Morn’)= 9
string

Date functions –
Take values that are date type as input and return values of date type, except for the
Months_between function.

FunctionName Return value Example

Add_months(date,n) Returns a date value after Add_months(‘8-Sep-2020’,2)
adding ‘n’ months to the date Ans. 8-Nov-2020
Months_between(x1,x2) Returns no. of months Months_between(‘8-Sep-
between x1 and x2 dates 2020’, ’8-Nov-2020)
Ans. -2
Next_day Returns the next date of the Next_day(’17-Jul-2020’,
week_day on or after the ’thursday’)
date ‘x’ occurs Ans. 23-Jul-2020
Last_day(x) It is used to determine the Last_day(‘8-Sep-2020’)
last date of a month from the Ans. 30-Sep-2020
date ‘x’ specified.

Conversion Functions :
These functions help to convert a value in one form to another form. i.e a value from one
datatype to another datatype.

to_char(x,[y]) converts numeric or date values to

character string
To_date(x,[date format]) Converts valid numeric or character
values to a date value.
NVL(x, y) If ‘x’ is NULL replaces with ‘y’