UNIT-3

BASIC SQL (STRUCTURED QUERY LANGUAGE)

SQL:
• SQL stands for Structured Query Language
• SQL is used to communicate with a database.
• According to ANSI (American National Standards Institute), it is the standard language
for relational database management systems.
• SQL statements are used to perform tasks such as update data on a database or retrieve
data from a database.
• Some common relational database management systems that use SQL are: Oracle,
MYSQL, Mongo DB, Sybase, Microsoft SQL Server, Access, Ingres, etc.
What Can SQL do:
1) SQL can execute queries against a database
2) SQL can retrieve data from a database
3) SQL can insert records in a database
4) SQL can update records in a database
5) SQL can delete records from a database
6) SQL can create new databases
7) SQL can create new tables in a database
8) SQL can create stored procedures in a database
9) SQL can create views in a database
10) SQL can set permissions on tables, procedures, and views
HISTORY OF SQL:
• In 1970 Dr. E. F. Codd developed the relational database model.
• In 1978 IBM developed the language called Structured English Query Language
(SEQUEL). This language was used on a database system called System/R, but neither the
system nor the query language was ever released.
• In 1979 Relational Software Inc. (later renamed Oracle) released the first relational
DBMS, Oracle.
• In 1982 IBM released their first relational database system, SQL/DS (SQL/Data
System).
• In 1985 IBM released DB2 (Database 2).
• In 1987 Microsoft released SQL Server.
• In 1989 The American National Standards Institute (ANSI) published the first set of
standards for a database query language, called ANSIISO SQL-89, or SQL 1.
• In 1992 ANSI published revised standards (ANSIIISO SQL-92, or SQL2) incorporated
many new features.
51
• In 1999 ANSI published SQL3 (ANSIISO SQL-99). These standards incorporated
new features, including support for objects.
• In 2003 ANSI published SQL:2003. These standards introduced XML-related
features, standardized sequences, and identity columns.
• In 2006 ANSI published SQL:2006, which defined how SQL can be used with XML.
• In 2008 ANSI published SQL:2008. These standards introduced INSTEAD OF
triggers and the TRUNCATE statement.

Characteristics of SQL:
• SQL is easy to learn.
• SQL is used to access data from relational database management systems.
• SQL can execute queries against the database.
• SQL is used to describe the data.
• SQL is used to define the data in the database and manipulate it when needed.
• SQL is used to create and drop the database and table.
• SQL is used to create a view, stored procedure, function in a database.
• SQL allows users to set permissions on tables, procedures, and views.

Advantages of SQL:
The following are various advantages of SQL
• Portable: SQL can run in mainframes, PCs, laptops, servers and even mobile phones. It
runs in local systems, intranet and internet. Databases using SQL can be moved from one
device to another without any problems.
• Used with any DBMS system with any vendor: SQL is used by all the vendors who
develop DBMS.
• Used for relational databases: SQL is widely used for relational databases.
• Easy to learn and understand: SQL mainly consists of English statements and it is
very easy to learn and understand a SQL query.
• Interactive language: SQL can be used to communicate with the databases and get
answers to complex questions in seconds.
• Both as programming language and interactive language: SQL can do both the jobs
of being a programming as well as an interactive language at the same time.
• Complete language for a database: SQL is used to create databases, manage security
of a database. It can also be used for updating, retrieving and sharing data with users.
• Multiple data views: By using SQL, different views of structure and content of a
database can be provided for different users.
• Client/Server language: SQL is used for linking front end computers and back end
databases. Thus, providing client server architecture.
52
 • Dynamic database language: By the use of SQL database structure can be changed in
a dynamic fashion even when the contents of the database are accessed by users at the
same time.
• Supports object-based programming: SQL supports the latest object-based
programming and is highly flexible.
• Supports enterprise applications: SQL is the database language which is used by
businesses and enterprises throughout the globe. For an enterprise application it is a
perfect language for a database.
• Integrates with Java: SQL integrates with Java by using an API known as JDBC (Java
Database Connectivity).
• Used in internet: SQL is used in three-tiered Internet architecture. The architecture
includes a client, application server and a database.
• Used by IBM and Microsoft: SQL is used by major players like IBM and Microsoft.
IBM used SQL in DB2; and Microsoft in ODBC (Open Database Connectivity), SQL
server and ADO (ActiveX Data Objects).

Q) Explain DATA TYPES in SQL?

• SQL data types define the kind of data that can be stored in a database column.
• For example, in a particular column of a table, if we want to store a string type of data
then we will have to declare a string data type of this column.
• Data types mainly classified into following categories.

Numeric Data Types

1. INT: Stores integer values.

o Example: 123
o Usage: id INT
2. TINYINT: Stores very small integers.
o Example: 1
o Usage: age TINYINT
3. SMALLINT: Stores small integers.
o Example: 32000
o Usage: quantity SMALLINT
4. MEDIUMINT: Stores medium-sized integers.
o Example: 8388607
o Usage: views MEDIUMINT
5. BIGINT: Stores large integers.
o Example: 9223372036854775807
o Usage: total-sales BIGINT
53
6. DECIMAL (p, s): Stores fixed-point numbers with precision p and scale s.
o Example: 12345.67 (with p=10, s=2)
o Usage: price DECIMAL (10, 2)
7. NUMERIC (p, s): Similar to DECIMAL.
o Example: 123.45 (with p=5, s=2)
o Usage: balance NUMERIC (5, 2)
8. FLOAT: Stores floating-point numbers.
o Example: 36.6
o Usage: temperature FLOAT
9. DOUBLE: Stores double-precision floating-point numbers.
o Example: 12345.6789
o Usage: average DOUBLE
10. REAL: Stores single-precision floating-point numbers.
o Example: 1.2345
o Usage: ratio REAL

Character String Data Types

1. CHAR(n): Fixed-length character string of length n.

o Example: 'US' (with n=2)
o Usage: country code CHAR (2)
2. VARCHAR(n): Variable-length character string with a maximum length of n.
o Example: 'john doe' (with n=50)
o Usage: username VARCHAR (50)
3. TEXT: Large variable-length text.
o Example: 'This is a long text...
o Usage: description TEXT Variations:
4. TINYTEXT: Very short text.
o Example: 'Short text'
o Usage: note TINYTEXT
5. MEDIUMTEXT: Medium-length text.
o Example: 'Medium text...'
o Usage: comment MEDIUMTEXT
6. LONGTEXT: Very long text.
o Example: 'Long text...'
o Usage: article LONGTEXT

Binary Data Types

1. BINARY(n): Fixed-length binary data.

o Example: 0x1234 (with n=2)
o Usage: file_hash BINARY (16)

54
2. VARBINARY(n): Variable-length binary data with a maximum length of n.
o Example: 0x123456 (with n=3)
o Usage: image VARBINARY (255)
3. BLOB: Binary Large Object for large binary data.
o Example: Binary data of an image or file.
o Usage: document BLOB

Date and Time Data Types

1. DATE: Stores date values.

o Example: 2024-08-03
o Usage: event date DATE
2. TIME: Stores time values.
o Example: 13:45:30
o Usage: start time TIME
3. DATETIME: Stores date and time values.
o Example: 2024-08-03 13:45:30
o Usage: created at DATETIME

4. TIMESTAMP: Stores date and time with time zone.

o Example: 2024-08-03 13:45:30
o Usage: last login TIMESTAMP
5. YEAR: Stores year values.
o Example: 2024
o Usage: birthyear YEAR

Miscellaneous Data Types

1. BOOLEAN: Stores Boolean values (TRUE or FALSE).
o Example: TRUE
o Usage: is active BOOLEAN
2. ENUM: Stores one value from a set of predefined values.
o Example: 'small', 'medium', 'large'
o Usage: size ENUM ('small', 'medium', 'large')
3. SET: Stores multiple values from a set of predefined values.
o Example: 'a', 'b', 'c'
o Usage: options SET ('a', 'b', 'c')

55
 Q) Explain SQL Commands in detail? or
Explain DDL, DML, TCL and DCL and DQL Commands in SQL?
• SQL commands are instructions used to communicate with the database to perform
specific task that work with data.
• SQL commands used to create tables, add data to tables, or modify data, drop the table,
set permissions for users etc.
• SQL commands are grouped into five major categories depending on their
functionality.
• DDL (Data Definition Language).
• DML (Data Manipulation Language).
• DCL (Data Control Language).
• TCL (Transaction Control Language)

Data Definition Language(DDL)commands:

• DDL commands are used to create, modify, and drop the structure of database objects like
table, view, procedure, indexes etc.
• All the command of DDL are auto-committed that means it permanently save all the
changes in the database.
• In this category we have CREATE, ALTER, DROP, TRUNCATE and RENAME
commands.
CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
ALTER: This is used to alter the structure of the database.
DROP: This command is used to delete objects from the database.
56
TRUNCATE: This is used to remove all records from a table, including all spaces
allocated for the records are removed.
RENAME: This is used to rename an object existing in the database

Data Manipulation Language (DML) Commands:

• DML commands are used to store, modify, and delete data from database tables.

• In this category we have INSERT, UPDATE, DELETE, LOCK and MERGE

commands.

• The commands of DML is not auto-committed that means it can't permanently save all
the changes in the database. They can be rollback.
SELECT: It is used to access the data from a database table.
INSERT: It is used to insert data into a table.
UPDATE: It is used to update existing data within a table.
DELETE: It is used to delete records from a database table.

Data Control Language(DCL) Commands:

• DCL Commands are used to control user access in a database.
• DCL commands are used to grant and revoke permissions from any database user.

Transaction Control Language (TCL) commands:

• Transaction Control Language (TCL) commands are used to manage transactions in the
database.
• These are used to manage the changes made to the data in a table by DML
statements.
• These commands can only use with DML commands like INSERT, DELETE and
UPDATE only.

57
Q) Explain DDL commands in detail?
• DDL is a Data Definition Language, which deals with database schemas and
descriptions.
• DDL statements are used to alter/modify a database or table structure and schema.
• All the commands of DDL is auto-committed that means it permanently save all the
changes in the database.
DDL Commands:
• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME
Q) Explain about tables in SQL?
OR
How to create Tables in SQL?
CREATE TABLE Command:
In Oracle, CREATE TABLE statement is used to create a new table in the database. Each
column has minimum of three attributes.
• Name
• Data type
• Size (column width)
Syntax: CREATE TABLE Tablename(column1 datatype, column2 datatype, column3
datatype, ...... columnN datatype, PRIMARY KEY (one or more columns));

Parameters:
Tablename: It specifies the name of the table which you want to create.
column1, column2, ... column n: It specifies the columns which you want to add in the
table. Every column must have a datatype. Every column should either be defined as
"NULL" or "NOT NULL". In the case, the value is left blank; it is treated as "NULL" as
default.
58
Primary key: A primary key is a column or set of columns in a table that uniquely
identifies rows in a table. It cannot accept null, duplicate values.
Example: If you want to create the Student table, the statement would be like,
SQL>CREATE TABLE Student (Sid number(5),
sname varchar(20),
class varchar(10),
age number(2),
fee number(10),
address varchar(10));
Table Created

Example 2: CREATE TABLE with primary key

SQL> CREATE TABLE Student (
Sid number(5),
sname varchar(20),
class varchar(10),
age number(2),
fee number(10),
address varchar(10), primary key(sid));
Table created.

Syntax2: SQL> CREATE TABLE Student (

Sid number(5) primary key, sname varchar(20),
class varchar(10),
age number(2),
fee number(10),
address varchar(10));
Table created.

Create Table Using Another Table:

• The CREATE TABLE AS statement is used to create a table from an existing table
by copying the columns of existing table.
• The new table gets the same column definitions.
• All columns or specific columns can be selected.
Syntax: CREATE TABLE new table AS (SELECT * FROM old table);
Ex: SQL>CREATE TABLE student1 AS (SELECT * FROM student);

Table created.
59
 Create Table copying selected columns of another table:
Syntax: CREATE TABLE new table AS (SELECT column1, column2...columnN FROM
old table);
Ex: SQL> CREATE TABLE student2 AS (SELECT Sid, sname FROM Student);

Create Table Example: copying selected columns from multiple tables:

Syntax: CREATE TABLE new table AS (SELECT column1, column2, ... columnN
FROM old table1, old table2, ... old table N);
Ex:CREATE TABLE empdept as(SELECT empno, ename, job, dname from emp, dept);

Q) Explain ALTER Command in SQL?

Explain about table Modification Commands?

• The SQL ALTER TABLE command is used to add, delete or modify columns in an
existing table.
• The ALTER TABLE statement is also used to add and drop various constraints on an
existing table.
ALTER command is used to perform the following functions:
• To add a column to existing table
• To rename any existing column
• To change datatype of any column or to modify its size.
• To drop a column from the table.
ALTER Command: Add a new Column

Using ALTER command, we can add a column to any existing table.

Syntax: ALTER TABLE table_name ADD(column_name datatype);
Ex: ALTER TABLE student ADD(address VARCHAR(200));
The above command will add a new column address to the table student, which will
hold data of type varchar which is nothing but string, of length 200.

ALTER Command: Add multiple new Columns

Using ALTER command, we can even add multiple new columns to any existing table.
Syntax: ALTER TABLE table name ADD(column-name1 datatype1, column-name2
datatype2);

60
Ex: ALTER TABLE student ADD(fathername VARCHAR(60), mothername
VARCHAR(60), dob DATE);
The above command will add three new columns to the student table

ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a default value too. The
default value is used when no value is inserted in the column.
Syntax: ALTER TABLE tablename ADD(column-name1 datatype1 DEFAULT some
value);
Ex: ALTER TABLE student ADD(dob DATE DEFAULT '01-Jan-99');
The above command will add a new column with a preset default value to the table
student.

ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing column. Syntax:
ALTER TABLE table_name modify(column_name datatype);
Ex: ALTER TABLE student MODIFY(address varchar(300));
The above command will modify the address column of the student table, to hold up to
300 characters.

ALTER Command: Rename a Column

Using ALTER command, we can rename an existing column.
Syntax: ALTER TABLE tablename RENAME old_columnname TO new_columnname;
Ex: ALTER TABLE student RENAME address TO location;
The above command will rename address column to location.

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns.

Syntax: ALTER TABLE tablename DROP column( column_name);
Ex: ALTER TABLE student DROP column (address);
The above command will drop the address column from the table student.

DROP Command:
• The DROP TABLE statement is used to drop an existing table in a database.
• Once a table is dropped we cannot get it back, so be careful while using DROP
command.
Syntax: DROP TABLE tablename;
Ex: Drop Table Students;
61
TRUNCATE Command:

The TRUNCATE command is used to delete the data inside a table, but not the table
Structure.
Syntax: TRUNCATE TABLE tablename;
Ex: TRUNCATE TABLE student;
The above query will delete all the records from the table student.
RENAME Command:
RENAME command is used to set a new name for any existing table. Following is the
syntax,
Syntax: RENAME TABLE old_tablename to new_tablename;
Ex: RENAME TABLE student to studentinfo;

DESC Command (Describe Table)

• SQL DESC Command used for describe the list of column definitions for
specified table.
• You can use either DESC or DESCRIBE statement.
DESCRIBE statement to get following information:
• Column Name
• Column allow NULL or NOT NULL
• Datatype of the Column
• Size of the column
Syntax: DESC tablename;
Example SQL> DESC student;

Name Null? Type

NO NOT NULL NUMBER (3)
NAME VARCHAR2(30)
ADDRESS VARCHAR2(70)
CONTACT_NO VARCHAR2(12)

Q) Explain Data Manipulation Commands? (or)

Explain DML Commands in SQL?
• Data manipulation language commands are used to manipulate the data.
• They are used for storing, retrieving, modifying, and deleting data in the database table.
• DML commands are not auto-committed. It means changes made by DML command
are not permanent to database, it can be rolled back.

62
The DML Commands are
• Select
• Insert
• Update
• Delete

SELECT Command:
• The ‘Select’ command is used to retrieve the data from a Table or a View.
• A select query may retrieve information from specified columns or from all of the
columns in the table.

Student Table:

SID SNAME COURSE FEE

1 BHASKAR MPCS 10000
2 RAMESH MSCS 20000
3 SURESH MECS 15000
4 VENKAT MSCS 20000
Syntax: To select all columns
SQL>SELECT * FROM table-name;
Ex: SQL> SELECT *from student;
Syntax: To select specific columns
SQL>SELECT column1, column2, ... FROM tablename;
Ex: SQL> SELECT sid, sname from student;

Syntax: TO Select a particular record based on a condition

SQL>SELECT column1, column2, ... FROM tablename where condition;
Ex: SQL> SELECT sid, sname from student WHERE FEE>10000;
INSERT Command:
• The SQL INSERT statement is used to insert single or multiple data in a table.
• In SQL, we can insert the data in two ways:
• Without specifying column name
• By specifying column name

Syntax1: Inserting single row

SQL>insert into <table name> values (value1, value2, value3…);
Ex: SQL>insert into student values(101,’abc’,75);

63
Syntax2: Inserting Multiple rows
SQL>insert into <table name> values (&value1, &value2, &value3…);
SQL>insert into student values(&rollno,’&studname’, &marks’);
After giving this command the following output will come,
Enter value for rollno:101
Enter value for name: xyz
Enter value for marks: 80
1 row created.
The statement ‘1 row created’ assures that the record has been inserted into the table.
To run the same command just press ‘/ ‘on SQL prompt as follows.
SQL> /
Syntax3: Insert value into only specific columns
• We can use the INSERT command to insert values for only some specific columns of a
row.
• We can specify the column names along with the values to be inserted like this,
Syntax: SQL> Insert into table(column1, column2) values(value1,value2);
EX: SQL>INSERT INTO student(id, name) values(102, 'mahesh');
The above SQL query will only insert id and name values in the newly inserted record.

Syntax4: Insert with Select

We can use the SELECT statement with INSERT INTO statement to copy rows from one
table and insert them into another table.
Syntax: SQL> INSERT INTO firsttable(names_of_columns1) SELECT names of columns
FROM secondtable.
EX:SQL>insert into student1(sid,sname)select sid,sname from student2;

Syntax4: Insert NULL value to a column:

The SQL INSERT statement can also be used to insert NULL value for a column.
Ex: insert into student(1,’mahesh’, null);

Update Command:
• UPDATE command is used to update any record of data in a table.
• UPDATE can update one or more records in a table.
• In the Update statement, WHERE clause identifies the rows that get affected. If you do
not include the WHERE clause, column values for all the rows get affected

Syntax: SQL>UPDATE tablename SET column1=value1, column2=value2,... WHERE

condition;
EX: SQL>UPDATE student SET sname=’chandu’ where sid=1;
64
DELETE Command:
• The DELETE Statement is used to delete rows from a table.
• We can use the WHERE clause with a DELETE Command to delete the selected rows,
otherwise all the records would be deleted.

Syntax: SQL>DELETE from tablename WHERE [condition];

EX: SQL>DELETE from student WHERE sid=1;
If we want to DELETE all the records from the student table, we do not need to use the
WHERE clause.
Ex: SQL>Delete from student;

DCL Commands:
• DCL stands for Data Control Language.
• DCL commands are used to control user access in a database.
• Using DCL command, it allows or restricts the user from accessing data in database
schema.

DCL commands are as follows,

1. GRANT
2. REVOKE

Grant Command:

• GRANT command gives user's access privileges to the database.

• This command allows specified users to perform specific tasks.

Syntax: GRANT <privilege list> ON<table name or view name> TO <user >;
EX: For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE
privileges on a table called employees to a user name smith, you would run the following
GRANT statement:

EX: GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO mahesh;

EX: To Allow a User to create Table
SQL>grant create table to username;
EX: To grant all privileges to user:
SQL>Grant all privileges to Mahesh;

65
REVOKE COMMAND:
• REVOKE command is used to cancel previously granted or denied permissions.
• This command withdraws access privileges given with the GRANT command.
• It takes back permissions from user.

Syntax: REVOKE <privilege list>ON <table name or view name>FROM <user name>;

Ex: SQL>REVOKE CREATE TABLE FROM username;

Ex: if we want to revoke DELETE privileges on a table called employees from a user
name mahesh, we would run the following REVOKE statement:
Ex: REVOKE DELETE ON employees FROM mahesh;
If you want to revoke ALL permissions from mahesh
Ex: REVOKE ALL ON employees FROM mahesh;

Q) TCL Commands:
• Transaction Control Language(TCL) commands are used to manage transactions in the
database.
• These are used to manage the changes made to the data in a table by DML
statements
• It also allows statements to be grouped together into logical transactions.

COMMIT command:
• COMMIT command is used to permanently save any transaction into the database.
• When we use any DML command like INSERT, UPDATE or DELETE, the changes
made by these commands are not permanent, until the current session is closed, the
changes made by these commands can be rolled back.
• To avoid that, we use the COMMIT command to mark the changes as permanent.

Syntax: SQL>COMMIT;

ROLLBACK command
• This command restores the database to last committed state.
• This command can only be used to undo transactions since the last COMMIT or
ROLLBACK command was issued
Syntax: SQL: ROLLBACK;
Syntax1:SQL>Rollback to savepoint-name;

66
SAVEPOINT Command:
• A SAVEPOINT is a point in a transaction in which you can roll the transaction back to
a certain point without rolling back the entire transaction.
• SAVEPOINT command is used to temporarily save a transaction so that you can roll
back to that point whenever required.
• Syntax: SAVEPOINT savepointname;
• ROLLBACK TO SAVEPOINT_NAME;
EX: SAVEPOINT SP1;
//Savepoint created.
DELETE FROM Student WHERE AGE = 20;
//deleted SAVEPOINT SP2;
//Savepoint created. ROLLBACK TO SP1;
//Rollback completed.
Distinct Command:

• Distinct removes all the duplicate records while retrieving records from any table in the
database.
Syntax: SELECT DISTINCT column-name FROM table-name;
EX: SQL> SELECT DISTINCT SAL FROM STUDENT;

Q) Explain SQL OPERATORS:

• The symbols which are used to perform logical and mathematical operations in SQL are
called SQL operators.
• SQL statements generally contain some reserved words or characters that are used to
perform operations such as comparison and arithmetical operations etc. These reserved
words or characters are known as operators.
TYPES:
• SQL Arithmetic Operators
• SQL Comparison Operators
• SQL Logical Operators

SQL Arithmetic Operators:

Let's assume two variables "a" and "b". Here "a" is valued 50 and "b" valued 100.

67
Example:

Operators Descriptions Examples

+ It is used to add containing values of a+b will give 150

both operands

- It subtracts l e f t hand op e r an d a-b will give -50

f r o m right hand operand

* It multiplies both operand's values a*b will give 5000

/ It divides left hand operand by right b/a will give 2

hand operand

% It divides left hand operand by b%a will give 0

right hand operand and returns
reminder

SQL Comparison Operators:

Comparison operators are used to compare the column data with specific values in a
condition. Let us take two variables "a" and "b" that are valued 50 and 100.

Operator Description Example

= Examine both operands value that are equal or not, (a=b) is not true
if yes condition become true.

!= This is used to check the value of both operands (a!=b) is true

equal or not, if not condition become true.

> Examine the left operand value is greater than (a>b) is not true
right Operand, if yes condition becomes true

< Examines the left operand value is less than right (a<b)is true
Operand, if yes condition becomes true
68
 >= Examines that the value of left operand is greater (a>=b) is not true
than or equal to the value of right operand or not,
if yes condition become true

<= Examines that the value of left operand is less (a<=b) is true
than or equal to the value of right operand or not,
if yes condition becomes true

!< Examines that the left operand value is not less (a!<b> is not true
than the right operand value

!> Examines that the value of left operand is not (a!>b) is true
greater than the value of right operand

SQL Logical Operators:

Logical operators in SQL are used to perform logical operations on the given expressions
in SQL statements. There are many operators in SQL which are used in SQL statements in
the WHERE clause.

Operator Description
AND Returns TRUE if all conditions are true.

OR Returns TRUE if any condition is true.

NOT Negates a condition (returns TRUE if false).

BETWEEN Checks if a value is within a range.

IN Checks if a value matches a list of values.
LIKE Searches for a pattern in data.
IS NULL Checks if a value is NULL.

XOR Returns TRUE if only one condition is true.

69
Q) Where clause:

• SQLWHERE clause is used to filter the records from the table based on the given
condition.
• A WHERE clause in SQL is a data manipulation language statement.

The WHERE clause can be used with the following types of SQL statements:
• SELECT
• UPDATE
• DELETE
A WHERE clause with a SELECT statement:
SELECT column-names FROM table-name WHERE condition;
A WHERE clause with an UPDATE statement:
UPDATE table-name SET column-name = value WHERE condition;
A WHERE clause with a DELETE statement:
DELETE table-name WHERE condition;
WHERE clause uses some conditional selection: <, >, <=, >=, !=, =
Ex: EMP TABLE

70
Ex: SQL>select *from emp where sal=3000;
SQL> select *from emp where SAL>1000;
SQL>select *from emp where sal>=1000;
SQL>select *from emp where sal<3000;
SQL>select *from emp where sal<=3000;
SQL>select *from emp where sal!=3000;
SQL>select *from emp where sal<>3000;
SQL>update emp set sal=sal+2000 where sal=1100;
SQL>Delete emp where empno=1234;

AND operator:
• SQL ‘ AND’ operator is used when we want to combine multiple conditions as part
of the WHERE clause. The result set will be filtered based on the condition.
• if both the conditions are true then only the result will be filtered.
• To combine multiple conditions, we can use more than one AND as part of the WHERE
clause.
syntax: SELECT column(s) FROM table_name WHERE condition1 AND condition2;

EX:SQL> Select * from Emp where JOB=” CLERK”AND JOB=” SALESMAN”;

OR Operator
• OR operator is used when we want to combine multiple conditions as part of the
WHERE clause.
• The result set will be filtered based on satisfaction of at least one of the
conditions.
• So, if at least one of the conditions are true than only the result will be filtered.

Syntax: SELECT column(s) FROM table_name WHERE condition1 OR condition2;

EX:SQL> Select * from Emp where JOB=” CLERK” OR JOB=” SALESMAN”;

NOT Operator
• SQL NOT operator is used when we want to filter result set when the condition
is not satisfied in the WHERE clause.
Syntax: SELECT column(s) FROM table_name WHERE NOT condition;

EX: SQL> SELECT * FROM emp WHERE NOT job= “SALESMAN";

71
BETWEEN Operator:
• The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN
value1 AND value2;

Ex: SQL> SELECT *FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
EX: SELECT Fname, Lname FROM Employee where DOB BETWEEN '1985-01-01'
AND '1990-12-30';

IN operator:
• The IN operator checks a value within a set of values separated by commas and retrieve
the rows from the table which are matching.
• The IN returns 1 when the search value present within the range otherwise returns 0.

Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN

(list_of_values);

EX: SQL>SELECT Ename,JOB FROM Emp WHERE Deptno IN (10,20,30);

EX: SELECT Fname, Lname FROM Employee WHERE Salary IN (30000, 40000,
25000);

Like Operator:
• The LIKE operator is used to list all rows in a table whose column values match a
specified pattern.
• It is useful when you want to search rows to match a specific pattern, or when you do
not know the entire value.
• For this purpose, we use a wildcard character '%'
o The percent sign (%)
o The underscore (_)
• The percent sign represents zero, one or multiple characters. The underscore represents
a single number or character. These symbols can be used in combinations.
Syntax: SELECT * FROM table_name WHERE column LIKE 'XXXX%'
Ex: SQL> select * from emp where ename like ‘J%’;
SQL> select * from emp where ename like ‘J_O%’;
SQL> select * from emp where ename like ‘_L_%’;

72
ORDER BY Clause:
• The SQL ORDER BY clause is used for sorting data in ascending and descending order
based on one or more columns.
• Some databases sort query results in ascending order by default
Sort according to one column: To sort in ascending or descending order we can use the
keywords ASC or DESC respectively.
Syntax: SELECT * FROM table_name ORDER BY column_name ASC/DESC.;
Sort according to multiple columns: To sort in ascending or descending order we can
use the keywords ASC or DESC respectively. To sort according to multiple columns,
separate the names of columns by (,) operator.

Syntax: SELECT * FROM table_name ORDER BY column1 ASC/DESC, column2

ASC/DESC
Ex: SELECT * FROM emp ORDER BY SAL DESC;

GROUP BY Clause:
The Group By statement is used for organizing similar data into groups Using some
functions.
Important Points:
GROUP BY clause is used with the SELECT statement.
In the query, GROUP BY clause is placed after the WHERE clause.
In the query, GROUP BY clause is placed before ORDER BY clause if used any.

Syntax: SELECT column1, function_name(column2) FROM table_name WHERE

condition GROUP BY column1, column2 ORDER BY column1, column2;

EX: CUSTOMERS
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
|1 |Ramesh |32 |Ahmedabad |2000.00|
|2 |Khilan |25 |Delhi |1500.00|
|3 |Kaushik |23 |Kota |2000.00|
|4 |Chaitali |25 |Mumbai |6500.00|
|5 |Hardik |27 |Bhopal |8500.00|
|6 |Komal |22 | MP |4500.00|
|7 |Muffy |24 |Indore |10000.00

Ex: SELECT ENAME, SUM(SALARY) FROM Employee GROUP BY deptno;

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;

73
Q)Aggregate Functions in SQL:
• SQL aggregation functions are 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.

1. Avg ():
• The Avg function is used to calculate the average value of the numeric type.
• Avg function returns the average of all non-Null values.
Syntax: SQL>SELECT Avg (column_name) FROM table_name WHERE condition;
Example: SQL> Select Avg(salary)from emp;

2. COUNT ()
• 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.
Syntax: SQL>SELECT COUNT(column_name) FROM table_name WHERE condition;
Ex: emp table
Id Name salary
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F NULL

74
Example: SQL> select count(id)from emp;
SQL> Select count(id)from emp where sal>60;
3. count (*):
The COUNT (*) function returns the number of rows in a table including the rows that
contain the NULL values.
Syntax: SQL>SELECT COUNT (*) FROM table_name WHERE condition;
Example: SQL> Select count(*)from emp;
4. FIRST ():
The FIRST () function returns the first value of the selected column.
Syntax:
SELECT FIRST(Column_name) FROM table_name;
Ex: SELECT FIRST(sal) FROM emp;
5. LAST ()
The LAST () function returns the last value of the selected column.
Syntax
SELECT LAST(column_name) FROM table_name;
Ex: SELECT LAST(sal) FROM emp;
6. max ():
• 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
Syntax: SQL>SELECT MAX (column_name) FROM table_name WHERE condition;
Example: SQL> Select MAX(salary)from emp;
7. MIN ():
• 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.
Syntax: SQL>SELECT min(column_name) FROM table_name WHERE condition;
Example: SQL> Select min(salary)from emp;
8. SUM ():
• Sum function is used to calculate the sum of all selected columns. It works on numeric
fields only.
• Null values are excluded from the result returned.
Syntax: SQL>SELECT sum(column_name) FROM table_name WHERE condition;
Example: SQL>Select sum(salary)from emp;
9. SQRT(): SQRT function is used to find out the square root of any number. You can
Use SELECT statement to find out square root of any number as follows.
Syntax: SQL>SELECT SQRT(Value) FROM DUAL;
Example: SQL>SELECT SQRT(16) FROM DUAL;

75