SQL has one basic statement for retrieving information from the database: The SELECT statement. SQL also provides Three other DML statements to modify the database. These statements are: update, delete and insert.
The basic form of the SELECT statement is formed of three clauses SELECT, FROM, and WHERE, having the following form:
SELECT < attribute list >
FROM < table list >
WHERE < condition >
In this form,
- < attribute list > is the list of attribute names whose values are to be retrieved by the query.
- < table list > is the list of relation names required to process the query.
- < condition > is a conditional expression that identifies the tuples to be retrieved by the query.
The following examples show the working of SELECT statement with different options. The INSERT, UPDATE and DELETE statements are also described in the following subsections.
1. Select Statement
Select statement is used to retrieve information from table.
Syntax : SELECT < column list >
FROM < table name >.
Example 1 : To display all department ID and the Department name, the query is
SELECT DID, DName
FROM Dept ;
Example 2 : To select all columns use “*”.
SELECT *
FROM Dept;
- Column Alias : You can give name to columns of your choice by using keyword “As” (gives column name in upper-case letter) or by using “ ” (gives column name as specified in query).
Example 3 : SELECT DID As Department_ID, DName
FROM Dept ;
- Concatenation Operator (II) : It is used to concatenate two or more
Example 4 : SELECT DName || Loc As department
FROM Dept ;
- Literal Character Strings : A literal is a number, character or date that can be included in Character and date literals must be enclosed with single quotation marks (‘ ‘).
Example 5 : SELECT DName || branch is situated at ’ || Loc As department
FROM Dept ;
- Eliminating Duplicate Rows : To eliminate duplicate rows, the keyword ‘DISTINCT’ is used.
Example 6 : SELECT salary SELECT DISTINCT salary
- Arithmetic Operators and NULL Values : SQL provides arithmetic operators to perform Arithmetic operators with their precedence are
A null value is unknown value and it is different from zero. Any arithmetic operation with null value gives null results.
Example 7 : Suppose you want to increase salary of each employee by 500.
SELECT EID, salary + 500 “New Salary”
FROM Emp;
- Where Clause : WHERE clause is used to select particular rows from
Syntax : SELECT <column list>
FROM <table name>
WHERE <condition>.
Example 8 : List the name of employees having salary ` 9000.
SELECT name
FROM emp
WHERE salary = 9000;
- Comparison or relational operators : The relational operators provided by SQL are as
Example 9 : List the name of employees having salary not equal to 
9000.
SELECT name
FROM emp
WHERE salary <> 9000;
- Special operators : Special operators provided by SQL are as follows :
Example 10 : List name and EID of employees having salary ` 8000 or ` 9500.
SELECT EID, name
FROM Emp
WHERE salary IN (8000, 9500);
Example 11 : List the EID and names of employees who were hired by company from 5–Feb–2001 to 1–Jan–2006.
SELECT EID, Name
FROM Emp
WHERE Hire_Date in (5-Feb-2001, 1-Jan-2006);
Example 12 : List the EID and names of employees having MID equal to null.
SELECT EID, name
FROM Emp
WHERE MID IS NULL;
Two symbols with LIKE operator can be used:
(i) % → It represents any sequence of zero or more characters.
(ii) _ (underscore) → It represents any single character.
Example 13 : List the names of employees ending with ‘it’.
SELECT name
FROM Emp
WHERE name LIKE ‘% it’;
Example 14 : List the names of employees having second alphabet of their names is ‘a’.
SELECT name
FROM Emp
WHERE name LIKE ‘_ a %’;
- Logical operators : Logical operators are used to combine two Following are the logical operators provided by SQL.
Example 15 : List name of employees having salary less than ` 8500 and MID is 707.
SELECT name
FROM Emp
WHERE salary <= 8500
AND MID = 707;
Example 16 : List name of employees having salary greater than ` 9200 or first alphabet of his name is ‘D’.
SELECT name
FROM Emp
WHERE salary > 9200
OR name LIKE ‘D%’;
Example 17 : List name of employees having MID is not equal to 707.
SELECT name
FROM Emp
WHERE MID NOT 707;
Note: Vishal is not included because NOT operator with NULL value gives Null result.
- Order by Clause : Order by clause is used to sort rows in both ascending and descending order.
- ASC : To sort rows in ascending order (By default).
- DESC : To sort rows in descending
Syntax : SELECT <column list>
FROM <table name>
WHERE <condition>
ORDER BY <column list> <ASC/DESC>;
Example 18 : List name of employees in ascending order.
SELECT name
FROM Emp
ORDER BY name ;
Example 19 : List name of employees in descending order according to their hire date and having salary greater than 7500.
SELECT name
FROM Emp
WHERE salary > 7500
ORDER BY Hire_date DESC;
2. Functions in SQL
Functions : Functions are used to manipulate data but these are more powerful than simple queries.
Types of Functions:
- Single row functions
- Group
Single row functions are further divided into:
- Character Functions
- Arithmetic Functions
- Date Functions
- Conversion Functions
- General
Dual table : Dual table is used to explain single row functions. It has one column name Dummy and one row having value x.
- Character Functions:
Example 20 :
SELECT LENGTH(‘Vivek’) “Output”
FROM dual;
SELECT LOWER(‘ADITYA’) “Output”
FROM dual;
SELECT UPPER(‘dinesh’) “Output”
FROM dual;
SELECT INITCAP(‘shivi’) “Output”
FROM dual;
SELECT CHR(103) “Output”
FROM dual;
SELECT CONCAT(‘IN’, ‘DIA’) “Output”
FROM dual;
SELECT REPLACE(‘Amit and Sumit’, ‘mit’, ‘zi’) “Output”
FROM dual;
SELECT SUBSTR(‘equivalent’, 3, 8) “Output”
FROM dual;
SELECT INSTR(‘aeroplane’, ‘p’) “Output”
FROM dual;
SELECT LPAD(‘cat’, 5, ‘*’) “Output”
FROM dual;
SELECT RPAD(‘cat’, 5, ‘%’) “Output”
FROM dual;
SELECT LTRIM(‘J’ FROM ‘Jack’) “Output”
FROM dual;
SELECT RTRIM(‘k’ FROM ‘Jack’) “Output”
FROM dual;
Example 21 : SELECT RPAD (name, 10, ’*’) “Emp_name”, LENGTH (JOB)
FROM Emp;
- Number Functions : Number functions are also known as arithmetic They accept numeric data and returns numeric values.
*x may be any numeric value or name of column.
Example 22 :
SELECT CEIL(77.7)
FROM dual;
SELECT FLOOR(69.2) “Output”
FROM dual;
SELECT ABS(–19) “Output”
FROM dual;
SELECT Power(7, 2) “Output”
FROM dual;
SELECT MOD(79, 10) “Output”
FROM dual;
SELECT SIGN(–9), SIGN(8)
FROM dual;
SELECT ROUND(55.438, 1) “Output”
FROM dual;
SELECT Exp(4) “Output”
FROM dual;
SELECT SQRT(64) “Output”
FROM dual;
SELECT TRUNC(79.128, 2) “Output”
FROM dual;
Example 23 :
SELECT MOD (salary, 100) “Output”
FROM Emp;
- Date Functions : Date functions accept Date data type input and returns Date data type except MONTHS_BETWEEN By default, date format in oracle is DD-MON-RR (12-Nov-81).
Example 24 :
SELECT SYSDATE
FROM dual;
SELECT NEXT_DAY(‘23-FEB-06’, ‘SATURDAY’)
FROM dual;
SELECT LAST_DAY(‘8-Nov-05’)
FROM dual;
SELECT ADD_MONTHS(‘5-AUG-05’, 2)
FROM dual;
SELECT MONTHS_BETWEEN(‘3-Jan-05’, ‘3-Feb-06’)
FROM dual;
SELECT ROUND(‘26-NOV-05’, ‘YEAR’)
FROM dual;
SELECT TRUNC(‘26-NOV-05’, ‘MONTH’)
FROM dual;
Example 25 : Display the EID, number of months employed of employees having salary more than ` 8500. Suppose system date is 01-Jan-06.
SELECT EID, MONTHS_BETWEEN(SYSDATE, Hire_date) “Time”
FROM Emp
WHERE salary > 8500;
- Conversion Functions : Conversion functions are used to convert one data type into other data type.
Example 26 :
SELECT TO_CHAR(‘15-Nov-1988’, ‘MONTH’)
FROM Dual;
SELECT TO_DATE(‘DECEMBER’, ‘MM’)
FROM dual;
- General Functions : General functions can accept any data type as input and pertain to the use of NULL values.
Example 27 :
SELECT USER
FROM dual;
SELECT UID
FROM dual;
Example 28 : Display EID and salary of all employees and convert salary equal to zero if NULL by using NVL function.
SELECT EID, NVL(salary, 0) “Salary”
FROM Emp;
Example 29 : Repeat Ex. 28 with the help of NVL2 function.
SELECT EID, NVL2(salary, 0, salary) “New Salary”
FROM Emp;
Example 30 :
SELECT NULLIF(99, 199)
FROM dual;
SELECT NULLIF(87, 87)
FROM dual;
Example 31 :
SELECT EID, COALESCE(Salary, MID, Job) “Star”
FROM Emp;
3. Conditional Statements
Conditional statements are look like conditional statements in procedural languages like C, C++.
- DECODE Function : DECODE function is the substitute of IF-THEN-ELSE statement.
Syntax : DECODE ( Expression,
expr 1, result 1,
expr 2, result 2,
———————
———————
expr N, result N,
default )
- CASE Function : CASE is the substitute of SWITCH CASE statement in procedural languages.
Syntax : CASE expression WHEN expr 1 THEN result 1
WHEN expr 2 THEN result 2
————————————————-
————————————————-
WHEN expr N THEN result N
ELSE default
END
Example 32 : Display EID and salary of all employees with an increment of ` 1000 in salary for employees working as Analyst and ` 2000 for employees working as Manager. Use DECODE function.
SELECT EID, DECODE(Job,
‘Analyst’, Salary + 1000,
‘Manager’, Salary + 2000, Salary) “New Salary”
FROM Emp;
Example 33 : Repeat Ex. 32 by use of CASE expression.
SELECT EID, CASE Job WHEN ‘Analyst’ THEN Salary + 1000
WHEN ‘Manager’ THEN Salary + 2000
ELSE Salary
END
FROM Emp;
4. Joining of Tables
If we need information from more than one table then we use joins. To join tables the condition need to be specified.
- Cartesian Product : In Cartesian product there is no join It returns all possible combinations of rows.
Example 34 :
SELECT EID, LOC
FROM Emp, Dept;
Note: Syntax of Join : The following is the common syntax for all types of JOIN.
SELECT table 1.columns, table 2.columns
FROM table 1, table 2.
WHERE table 1.column N = table 2.column M;
- Equijoin : When two or more tables are joined by equality of values in one or more columns then it is called
*More than two tables can be joined by using logical operators.
Example 35 : Display EID and DName of all employees by joining over DID.
SELECT Emp.EID, Dept.DName
FROM Emp, Dept
WHERE Emp.DID = Dept.DID
- Table Aliases : Alias names can be given to the It is specified in FROM clause. They are helpful to simplify queries.
Example 36 : Repeat Ex. 35 with table alias
- Non-Equijoin : When tables are joined by any other operator except the equality operator in condition, then it is known as Non-Equijoin.
Example 37 : Display EID and DName of employees having MID 705 or 707
SELECT e.EID, d.DName
FROM Emp e, Dept d
WHERE e.MID IN (d.MID = 705, d.MID = 707);
- Outer Join : The outer join operator is ‘(+)’. During simple joining some rows are missing due to null To display these rows use outer join operator towards defficient side.
Example 38 : Display EID and DName of employees by joining over MID.
- Self Join : A table can be joined to itself by using self joins.
Example 39 : Display the name of employees and name of their managers.
SELECT e.Name “Employee”, m.Name “Manager”
FROM Emp e, Emp m
WHERE e.MID = m.MID;
- Cross Join : It is same as cartesian product.
Example 40 : Repeat ex. 35 by cross join
SELECT e.EID, d.DName
FROM Emp e, Dept d
CROSS JOIN DID;
- Natural Join : It is used to join two tables having same column names and data It select rows from two tables having equal values.
Syntax : SELECT <column names>
FROM <table_names>
NATURAL JOIN (table name);
- Using clause : It is almost impossible that two tables having same column names and data So, modify Natural join and use USING clause in which we specify the column which is used for joining.
Syntax : SELECT <column names>
FROM <table names>
USING (column name);
- On clause : ON clause is used to specify join condition instead of where clause.
Example 41 : Repeat ex. 35 using On clause
SELECT e.EID, d.DName
FROM Emp e, Dept d
ON e.DID = d.DID
- Left Outer Join : To display all the rows of table left of the join condition, use LEFT OUTER This keyword is used instead of outer join operator ‘(+)’.
- Right Outer Join : To display all the rows of table right of the join condition, use RIGHT OUTER This keyword is used instead of outer join operator ‘(+)’.
- Full Outer Join : To display all the rows of both of the tables, use keyword FULL OUTER JOIN.
Example 42 : Display EID and DName of employees by joining over MID. (By using left outer join, Right outer join and Full outer join).
5. Group Functions
Group functions are those functions that operate on a group of rows and returns a single result per group. Group may be entire table or a part of table. All the group functions ignore null values.
Example 43 :
SELECT MAX(salary)
FROM Emp;
SELECT MIN(Name)
FROM Emp;
SELECT AVG(Salary), AVG(DISTINCT Salary)
FROM mp;
SELECT STDDEV(Salary)
FROM Emp;
SELECT SUM(Salary), SUM(DISTINCT Salary)
FROM Emp;
SELECT COUNT(*), COUNT(DISTINCT Job)
FROM Emp;
SELECT VARIANCE(Salary)
FROM Emp;
- Group by Clause : The GROUP BY clause is used to divide table into A group may contain whole of the table or a collection of rows.
Syntax :
SELECT <column name>
FROM <table name>
WHERE <condition>
GROUP BY <column name>;
Column alias cannot be used with group by clause.
Example 44 : Display job and average salary paid by company for a particular job.
SELECT Job, AVG(salary)
FROM emp
GROUP BY Job;
- Rows are sorted by ascending order according to the column specified in GROUP BY clause (By default).
(In above example rows are sorted according to Job)
- To display rows in order according to the user, ORDER BY clause can be
- HAVING Clause : The HAVING clause to apply restrictions on Like Where clause is used to restrict single rows, Having clause is used to restrict group, (collection of rows).
Syntax : SELECT <column name>
FROM <table name>
WHERE <condition>
GROUP BY <column name>
HAVING <group condition>
Example 45 : Display job and average salary paid by company for a particular job in descending order according to their average salary and average salary must be greater than 7500.
SELECT Job, AVG(Salary)
FROM Emp
GROUP BY Job
HAVING AVG(Salary) > 7500
ORDER BY AVG(Salary) DESC;
*Group conditions cannot be used in where clause.
6. Subquery
A subquery is a select statement which is nested with another select statement. First subquery is executed and returns result to outer query then outer query executes.
Place of Subquery : Subquery can be placed at the following places:
(i) Where clause, (ii) From clause, (iii) Having clause
Need of Subquery : When a condition depends on the data in the table itself then subquery is required.
Syntax: SELECT <column name>
FROM <table name>
WHERE expr operator
(SELECT <column name>
FROM <table name>
WHERE <condition>);
Example 46 : Display Name of those employees having same DID as that of Sumit.
It is required to find out DID of Sumit to solve this query. (Single Row Subquery).
SELECT Name
FROM Emp
WHERE DID =
Main problem with nested queries is that if inner query returns NULL value than outer query also returns NULL value.
Example 47 : List name and salary of only those employees having salary more than any of the employee working as Analyst.
SELECT name, salary
FROM emp
WHERE salary > ANY
*ANY works like OR operator
Example 48 : List name and salary of only those employees having salary more than every employee working as Analyst.
SELECT name, salary
FROM emp
WHERE salary > All
*AND works like AND operator
7. Insert Statement
Insert statement is used to insert or add new rows in table.
Syntax : INSERT INTO <table name> (column 1, column 2,……, column n)
VALUES (value 1, value 2,……, value n);
*Only a single row is inserted at a time
*Name of columns can be given in any order.
8. Update Statement
Update statement is used to modify the values of existing rows.
Syntax : UPDATE <table name>
SET <(column 1 = value 1), (column 2 = value 2),……, (column n = value n)>
WHERE <condition>;
*All rows in the table satisfies the condition are updated.
9. Delete Statement
Delete statement is used to remove existing rows from table.
Syntax : DELETE FROM <table name>
WHERE <condition>;
Example 49 : Consider table Dept. (Figure 7.3). Suppose it is empty.
49 (a) Insert new rows in Dept. table.
Insert INTO Dept (DID, DName, Loc, MID)
VALUES (10; ‘Accounts’; ‘Bangalore’, 708);
Insert INTO Dept (DName, Loc, DID, MID)
VALUES (‘Accounts’, Hyderabad; 40, NULL);
Insert INTO Dept (DID, DName, MID, Loc)
VALUES (50, ‘Testing’, 709, ‘Delhi’);
49 (b) Update the MID of DName Accounts to 702
UPDATE Dept
SET MID = 702
WHERE DName = ‘Accounts’;
49 (c) Delete row from Dept having DName = Testing
DELETE FROM Dept
WHERE DName = ‘Testing’;
49 (d) SELECT *
FROM Dept;
10. Merge Statement
Merge statement is a combination of Insert and Update statements. By using merge statement, conditional update or insert can be performed on a row.
Syntax : MERGE INTO <table1 name> <table1 alias>
USING <table2 name> <table2 alias>
ON (<join condition>)
WHEN MATCHED THEN
UPDATE SET
<Column1> = <val1>
<Column2> = <val2>
– – – – – – – – – – – –
– – – – – – – – – – – –
<Column n> = <val n>
WHEN NOT MATCHED THEN
INSERT <column list>
VALUES <column list>;
If condition is matched, rows in table 1 are updated otherwise they are inserted into table 1.
Example 50 : Create a table Twin-dept same as table Dept. Now merge these tables
MERGE INTO Twin-dept t
USING Dept d
ON (t.DID = d.DID)
WHEN MATCHED THEN
UPDATE SET
t.DName = d.DName
t.LOC = d.Loc
t.MID = d.MID
When NOT MATHCED THEN
INSERT (d.DID, d.DName, d.LOC, d.MID);
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)