UNIT 3

INTRODUCTION
TO SQL
INTRODUCTION TO SQL:

•Structured Query Language, popularly known as SQL is a

domain specific language used to manage data in
relational database management systems (RDBMS).

•It allows the user to perform operations such as create, delete etc.
on tables in database

•It also allows operations such as read, insert, update, delete etc. on
the data stored in the tables.

•All popular relational database management softwares such as

MySQL, PostgreSQL, Oracle, Microsoft Access, IDBM Db2,
SQLite etc. use SQL to manage the database.
Characteristics of SQL:
•Easy to Learn: SQL is user-friendly, english like language that makes it easy to
learn. Learning SQL doesn’t require prior knowledge.

•Portable language: SQL is a portable language, which means the software that
supports SQL can be moved to another machine without affecting the capability of
SQL interacting with the database on new machine.

•Supports wide variety of commands: SQL supports various useful commands

such as:
DDL (Data Definition Language) commands like CREATE, DROP, ALTER.
DML (Data Manipulation Language) commands like INSERT, DELETE,
UPDATE.
DCL (Data Control Language) commands like GRANT, REVOKE.
TCL (Transaction Control Language) commands like COMMIT,
ROLLBACK.
DQL (Data Query Language) commands like SELECT.
•Reusability: SQL promotes reusability by supporting stored
procedures. These stored procedures are stored SQL statements
that can be used to perform a specific task any number of times.
This makes it easier to write SQL statements for a re-occurring
task and reusing the saved stored procedure to perform the same
task without rewriting the same SQL statements again

•Performance: Better performance even if the database size if

huge.

•SQL is scalable and flexible.

•SQL is secure.
Advantages of SQL
1. Fast Response Time
You can quickly retrieve large amount of data from database using SQL. The
response time of a SQL query is very fast.

2. Requires No coding
Learning SQL is easy and doesn’t require any prior coding or programming
knowledge. The syntax of SQL is very simple and close to english so learning
curve is smooth.

3. Portable
SQL is portable, it supports various operating system and devices. SQL
statements can be stored as saved procedures and these procedures can be used
on a different machine to perform the same task without needing to rewrite the
statements again.
4. Standardised language
SQL is been used for over the years and has wide variety of well maintained
documentation. This language is so standardised that the same syntax can be
used on various different platforms.

5. Intergation
SQL server can connect to third party backends like Oracle, IDM Db2, MySQL
etc. using drivers. These drivers allow the smooth integration.

6. Secure
SQL allows to set permissions in tables, this makes it secure as a user with no
permission cannot read, write or modify the data in database. SQL also has the
concept of constraints that ensures what type of data can be inserted into the
tables. All these features make SQL a secure database language.

7. Scalable
It is easy to add or drop tables in the database using SQL. Also, the database size
doesn’t affect the performance of SQL that much and it works pretty great with
large databases as well. It is easy to add several new tables into the database as
well as drop multiple tables from database.
SQL Commands

1. DDL (Data Definition Language)

DDL commands are used to create, drop and alter the databases, aliases,
indexes, tables etc.
Once a DDL statement is executed, it takes effect immediately in the database.
Which means any changes done to the database using DDL command is
permanent.
Popular DDL commands are: CREATE, DROP, ALTER and TRUNCATE.
a) CREATE: This command is used to create database and tables inside a
database.
Syntax for creating database:
CREATE DATABASE database_name;

Syntax for creating table:

CREATE TABLE table_name
( column1 data type,
column2 data type,
column3 data type, .... );
b) DROP: This command is used to drop database and tables.
Syntax for dropping database:
DROP DATABASE database_name;
Syntax for dropping table:
DROP TABLE table_name;

c) ALTER: This command is used to add, delete, or modify columns in an

existing table.
Syntax: To add a new column in the existing table
ALTER TABLE table_name
ADD column_name datatype;

EX.
ALTER TABLE EMPLOYEES
ADD Address VARCHAR2(200);
d) TRUNCATE: This statement deletes all the rows from the table.
This is different from the DROP command, the DROP command deletes
the entire table along with the table schema, however TRUNCATE just
deletes all the rows and leaves an empty table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEES;

Deletes all the rows from the table Employees.

RENAME TABLE
In some situations, database administrators and users want to change the
name of the table in the SQL database because they want to give a more
relevant name to the table.

Syntax of RENAME
RENAME old_table _name To new_table_name ;
Ex. RENAME Employee To Coding_Employees ;
2.DML :
The DML commands in Structured Query Language change the data
present in the SQL database.
Following are the four main DML commands in SQL:
SELECT Command
INSERT Command
UPDATE Command
DELETE Command

1. SELECT DML Command

The SELECT command shows the records of the specified table. It also
shows the particular record of a particular column by using the WHERE
clause.
Syntax of SELECT command
SELECT column_Name_1, column_Name_2, ….., column_Name_N
FROM Name_of_table;
Here, column_Name_1, column_Name_2, ….., column_Name_N are
the names of those columns whose data we want to retrieve from the
table.

If we want to retrieve the data from all the columns of the table, we
have to use the following SELECT command:

SELECT * FROM table_name;

EX. SELECT * FROM Student;

SELECT Emp_Id, Emp_Salary FROM Employee;

If you want to access all the records of those students whose marks is
80 from the above table, then you have to write the following DML
command in SQL:
SELECT * FROM Student WHERE Stu_Marks = 80;
2.INSERT Command :

Insert command is used to insert data in database tables.

INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 ,

column_Name3 , .... column_NameN ) VALUES (value_1, value_2,
value_3, .... value_N ) ;

EX. INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age)

VALUES (104, Anmol, 89, 19);
UPDATE Command:

UPDATE command is used to allows users to update or modify the existing data in
database tables.

Syntax of UPDATE Command:

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N]

WHERE CONDITION;

Ex. UPDATE Product SET Product_Price = 80 WHERE Product_Id = 'P102' ;

DELETE Command:
DELETE command is used to remove single or multiple existing
records from the database tables.

Syntax of DELETE Command

DELETE FROM Table_Name WHERE condition;
Ex. DELETE FROM Product WHERE Product_Id = 'P202' ;
WHERE Clause:
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.

WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Ex.SELECT * FROM Customers WHERE Country='Mexico';

Note: The WHERE clause is not only used in SELECT statements, it

is also used in UPDATE, DELETE, etc.!
SQL Clauses

The following are the various SQL clauses:

ORDER BY:
The ORDER BY clause sorts the result-set in ascending or descending
order.
It sorts the records in ascending order by default. DESC keyword is used
to sort the records in descending order.
Syntax:
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1, column2... ASC|DESC;

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Ex.SELECT * FROM CUSTOMER ORDER BY NAME DESC;

2. GROUP BY
SQL GROUP BY statement is used to arrange identical data into groups.

The GROUP BY statement is used with the SQL SELECT statement.

The GROUP BY statement follows the WHERE clause in a SELECT

statement.

The GROUP BY statement is used with aggregation function.

Syntax
SELECT column FROM table_name WHERE conditions GROUP BY
Column;
Example:
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
3. HAVING
HAVING clause is used to specify a search condition for a group or an
aggregate.
Having is used in a GROUP BY clause. If you are not using GROUP BY
clause then you can use HAVING function like a WHERE clause.

Syntax:
SELECT column1, column2 FROM table_name WHERE conditions
GROUP BY column1, column2
HAVING conditions

Example:
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;
logical operators:
Logical operators are used to specify conditions in the structured query
language (SQL) statement.
They are also used to serve as conjunctions for multiple conditions in a
statement.
The different logical operators are shown below −

AND − Returns TRUE if both component conditions are TRUE.

Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
Syntax
SELECT column1, column2, FROM table_name
WHERE condition1 AND condition2 AND condition3;
For example,
select * from emp where job=’manager’ AND deptno=20;
OR − Return TRUE if either component condition is TRUE. Return
FALSE if both are FALSE. Otherwise, return UNKNOWN.
Syntax
SELECT column1, column2, FROM table_name
WHERE condition1 OR condition2 OR condition3;
For example,
select * from emp where job=’manager’ OR deptno=20;

IN − It is equivalent to any test. Equivalent to = ANY, The In operator is

used to compare a value to a list of literal values that have been specified.
For example,
select * from emp where ename IN (‘bhanu’,’ward’);

NOT − Returns TRUE if the condition is FALSE. Returns FALSE, if it is

TRUE. If it is UNKNOWN, it remains UNKNOWN.
For example,
select * from emp where NOT(salary between 2000 AND 5000);
BETWEEN − It is used to define range limits.
For example,
If we want to find all employees whose age is in between 40 and 50 the query is
as follows −
Select * from employee E where E.age between 40 AND 50;

LIKE − It is used to compare values to a list of literal values that are specified.
“%” character is used to match any substring and “_” character is used to match
any character. It expresses a pattern by using the ‘like’ comparison operator.
For example,
To display all names whose second letter is ‘b’, use the below mentioned
command −
select * from emp where ename LIKE ‘_b%’;

To display a person details whose first letter is ‘A’ and third letter is ‘d’, use the
command given below −
Select * from emp where ename LIKE ‘A_d_’;
String functions
are used to perform an operation on input string and return an output string.
Following are the string functions defined in SQL:

ASCII(): This function is used to find the ASCII value of a character.

Syntax: SELECT ascii('t');
Output: 116

CHAR_LENGTH(): . This function is used to find the length of a word.

Syntax: SELECT char_length('Hello!');
Output: 6

CHARACTER_LENGTH(): This function is used to find the length of a line.

Syntax: SELECT CHARACTER_LENGTH('geeks for geeks');
Output: 15

CONCAT(): This function is used to add two words or strings.

Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
Output: ‘GeeksforGeeks’
•LCASE(): This function is used to convert the given string into lower case.
Syntax: LCASE ("Geeks For Geeks To Learn");
Output: geeks for geeks to learn

UCASE(): This function is used to make the string in upper case.

Syntax: UCASE ("GeeksForGeeks");
Output: GEEKSFORGEEKS

LEFT(): This function is used to SELECT a sub string from the left of given size or characters.
Syntax: SELECT LEFT('geeksforgeeks.org', 5);
Output: geeks

LENGTH(): This function is used to find the length of a word.

Syntax: LENGTH('GeeksForGeeks');
Output: 13

LOWER(): This function is used to convert the upper case string into lower case.
Syntax: SELECT LOWER('GEEKSFORGEEKS.ORG');
Output: geeksforgeeks.org
RPAD(): This function is used to make the given string as long as the given size by
adding the given symbol on the right.
Syntax: RPAD('geeks', 8, '0');
Output: ‘geeks000’

LPAD(): This function is used to make the given string of the given size by adding
the given symbol.
Syntax: LPAD('geeks', 8, '0');
Output: 000geeks

REVERSE(): This function is used to reverse a string.

Syntax: SELECT REVERSE('geeksforgeeks.org');
Output: ‘gro.skeegrofskeeg’

CONCAT(): This function is used to add two words or strings.

Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
Output: ‘GeeksforGeeks’
Numeric Functions are used to perform operations on numbers and return numbers.
Following are the numeric functions defined in SQL:
ABS(): It returns the absolute value of a number.
Syntax: SELECT ABS(-243.5);
Output: 243.5

COS(): It returns the cosine of a number, in radians.

Syntax: SELECT COS(30);
Output: 0.15425144988758405

COT(): It returns the cotangent of a number, in radians.

Syntax: SELECT COT(6);
Output: -3.436353004180128

EXP(X)
This function returns the value of e (the base of the natural logarithm) raised to the
power of X.
SQL>SELECT EXP(3);
FLOOR(X)
This function returns the largest integer value that is not greater than X.
SQL>SELECT FLOOR(7.55);
FLOOR(7.55)
7
MOD(N,M)
This function returns the remainder of N divided by M. Consider the following
example −
SQL>SELECT MOD(29,3);

PI()
This function simply returns the value of pi. SQL internally stores the full double-
precision value of pi.
SQL>SELECT PI();

POWER(X,Y)
These two functions return the value of X raised to the power of Y.
SQL> SELECT POWER(3,3); 27
SQRT(X)
This function returns the non-negative square root of X. Consider the following
example −
SQL Aggregate Functions
SQL aggregation function is used to perform the calculations on multiple
rows of a single column of a table.
It returns a single value.
It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION
•COUNT function is used to Count the number of rows in a database table.

•It can work on both numeric and non-numeric data types

•COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table.
•COUNT(*) considers duplicate and Null.
Ex.SELECT COUNT(*) FROM PRODUCT_MAST;

2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on
numeric fields only.
Ex.SELECT SUM(COST) FROM PRODUCT_MAST;

3. AVG function
The AVG function is used to calculate the average value of the numeric type. AVG
function returns the average of all non-Null values.
Ex. SELECT AVG(COST) FROM PRODUCT_MAST;
4. MAX Function
MAX function is used to find the maximum value of a certain column. This
function determines the largest value of all selected values of a column.
Ex.SELECT MAX(RATE) FROM PRODUCT_MAST;

5. MIN Function
MIN function is used to find the minimum value of a certain column. This
function determines the smallest value of all selected values of a column.
Ex. SELECT MIN(RATE) FROM PRODUCT_MAST;
DATE & TIME function

1. Sysdate:
The SYSDATE() function returns the current date and time.
Ex. select sysdate from dual;

2.round(sysdate): The ROUND() function is used to get the date rounded to the
unit specified by the format model.
Syntax:
ROUND(date [, fmt ])

The Round(date) is an Oracle Date/Time function. This function returns the date
rounded by the given measure.
Ex. select ROUND(TO_DATE ('25-NOV-18'),'YEAR') from dual;
select ROUND(TO_DATE ('25-NOV-18'),‘MONTH') from dual;

3.add_months:ADD_MONTHS is one of the vital Date/Time functions of Oracle. It

is used to get a date with a specified number of months added.
Syntax:
ADD_MONTHS ( date, number_months )
Ex.select add_months(sysdate,3)from dual;

last_day(sysdate): LAST_DAY returns the date of the last day of the month that
contains date. The return type is always DATE, regardless of the data type of the date
argument.
Syntax:
LAST_DAY( Date );
Ex.select last_day(sysdate)from dual;

Next_day(sysdate):NEXT_DAY returns the date of the first instance of the specified

day that is later than the given date.
If the day value is the same day of the week as given_date, the next occurrence of
that day is returned.
Ex.select next_day('2014-08-20','Tuesday');
The following example returns the date of the first Tuesday after 8/20/2014.
SET Operators in SQL:

SET operators are special type of operators which are used to combine the result
of two queries.
SET operators are:
UNION
UNION ALL
INTERSECT
MINUS