1

LAB SESSION

SQL LANGUAGE
 Introduction
 Structured Query Language (SQL) is a query language that is
standardized for RDBMS.
 SQL Statements (commonly referred to as 'queries') are run to
retrieve or modify the requested information from the database
 SQL supports:
 Data Definition Language (DDL), and
 Data Manipulation Language (DML)
 DQL (Data Query Language)
 DCL (Data Control Language)
 TCL (Transaction Control Language)
2
 Why SQL?
3

 Allows users to access data in relational database management

systems.

 Allows users to describe the data.

 Allows users to define the data in database and manipulate that

data.

 Allows to embed within other languages using SQL modules,

libraries & pre-compilers.

 Allows users to create and drop databases and tables.

4 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 Query Dispatcher, Optimization

Engines, Classic Query Engine and SQL Query Engine, etc.
Classic query engine handles all non-SQL queries, but SQL
query engine won't handle logical files.
5 Following is a simple diagram showing SQL Architecture
 SQL Commands
6
 Data Definition Language (DDL)

 Part of the SQL language that permits database tables and

constraints to be created, modified or deleted.
 CREATE TABLE - creates a new database table.

 ALTER TABLE - alters (changes) a database table.

 DROP TABLE - deletes a database table.

 CREATE INDEX - creates an index (search key).

 DROP INDEX - deletes an index.

 DDL statements are used for schema definition of a relational 7

database.
 Table Creation and Modification
 Creating a basic table involves naming the table and defining
its columns and each column's data type:

CREATE TABLE <table_name> (

<column 1_name> <data_type> {size},

<column 2_name> <data_type> {size},

…………………………………..

<column N_name> <data_type> {size},

8
PRIMARY KEY( one or more columns )):
 Example
 Let us create a table to store data of Subjects, so the table
name is Subject, Columns are Sub_ID, Sub_Name

 Creating the tables

CREATE TABLE subject (

sub_id int,

Sub_name varchar(20)

)
9
 ALTER TABLE – ADD Column
10

 ADD is used to add columns to the existing table.

 Syntax:

 ALTER TABLE table_name ADD (Columnname_1 datatype,

Columnname_2 datatype, …Columnname_n datatype);

Example:

ALTER TABLE STUDENT

ADD(AGE)
11 ALTER TABLE – DROP
 DROP COLUMN is used to drop columns in a table.
Deleting the unwanted columns from the table.

 Syntax:

 ALTER TABLE table_name

 DROP COLUMN column_name;

12 ALTER TABLE-MODIFY

 It is used to modify the existing columns in a table. Multiple

columns can also be modified at once.

 Syntax:

 ALTER TABLE table_name

 MODIFY column column_name column_type;

13 Truncate Command

 It is used to delete the entities inside the table while holding the
structure of the table. It free up the space from the table whereas
the column names are left resulting to store new data in the
table

 Syntax:

 TRUNCATE TABLE table_name;

14 Drop Command
 It is used to completely delete a table from the database.
the table along with the structure is deleted and can't be
found again in the database.
 Syntax :
 DROP TABLE table_name;
 Data Manipulation Language (DML)

 It is part of the SQL syntax for executing queries to insert,

retrieve, update, or delete records.
 INSERT INTO - inserts new data into a database table.
 UPDATE - updates data in a database table.
 DELETE - deletes data from a database table.
 The four most common commands are also known as SQL CRUD
statements.
 Create, Read, Update, Delete

15
 INSERT INTO Statement
16
 The INSERT INTO statement of SQL is used to insert a new
row/record in a table. There are two ways of using the INSERT
INTO statement for inserting rows.

 Only Values

 The first method is to specify only the value of data to be inserted

without the column names.

 INSERT INTO table_name VALUES (value1, value2, value3);

 Column Names And Values Both

 INSERT INTO table_name (column1, column2, column3)

VALUES ( value1, value2, value3);

 UPDATE Statement
17
 The UPDATE statement in SQL is used to update the data of an
existing table in database. We can update single columns as
well as multiple columns using UPDATE statement as per our
requirement.

 Syntax:

UPDATE table_name SET column1=value1,column2=value2,

…..where condition;

 Omitting WHERE clause: update table_name set

column=value
18 DELETE Statement

 Existing records in a table can be deleted using the SQL

DELETE Statement. We can delete a single record or multiple
records depending on the condition we specify in the WHERE
clause.

 Syntax:

 DELETE FROM table_name WHERE some_condition;

19 SELECT Query

 Select is the most commonly used statement in SQL.

 The SELECT Statement in SQL is used to retrieve or fetch

data from a database

 Syntax:

 SELECT column1, column2 FROM table_name

20 Example (Student table)
 SQL Constraints
21  Constraints are the rules enforced on data columns on table.
 These are used to limit the type of data that can go into a table.
 This ensures the accuracy and reliability of the data in the
database.
 Constraints could be column level or table level.
 Column level constraints are applied only to one column,
whereas, table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL:
 NOT NULL Constraint: Ensures that a column cannot have
NULL value.
 DEFAULT Constraint: Provides a default value for a column
when none is specified.
 UNIQUE Constraint: Ensures that all values in a column are
different.
 Cont’s
22

 PRIMARY Key: Uniquely identified each rows/records in a

database table.

 FOREIGN Key: Uniquely identified a rows/records in any

another database table.

 CHECK Constraint: The CHECK constraint ensures that all

values in a column satisfy certain conditions.

 INDEX: Use to create and retrieve data from the database very
quickly.
 NOT NULL Constraint:
23

 By default, a column can hold NULL values. If you do not want

a column to have a NULL value, then you need to define such
constraint on this column specifying that NULL is now not
allowed for that column.

 A NULL is not the same as no data, rather, it represents

unknown data.
 Example
24
 For example, the following SQL creates a new table called
CUSTOMERS and adds five columns, three of which, ID and
NAME and AGE, specify not to accept NULLs:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

25 DEFAULT Constraint
 The DEFAULT constraint provides a default value to a column
when the INSERT INTO statement does not provide a specific
value.

 For example, the following SQL creates a new table called

CUSTOMERS and adds five columns. Here, SALARY column
is set to 5000.00 by default, so in case INSERT INTO
statement does not provide a value for this column. then by
default this column would be set to 5000.00.
26 Example
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2) DEFAULT 5000.00,
PRIMARY KEY (ID) );
27 UNIQUE Constraint
 The UNIQUE Constraint prevents two records from having
identical values in a particular column. In the CUSTOMERS
table, for example, you might want to prevent two or more
people from having identical age.

 For example, the following SQL creates a new table called

CUSTOMERS and adds five columns. Here, AGE column is
set to UNIQUE, so that you can not have two records with
same age
 Example
28
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2) DEFAULT 5000.00,
PRIMARY KEY (ID) );
If CUSTOMERS table has already been created, then to add a
UNIQUE constraint to AGE column, you would write a
statement similar to the following:
ALTER TABLE CUSTOMERS
MODIFY AGE INT NOT NULL UNIQUE;
 PRIMARY Key
29
 A primary key is a field in a table which uniquely identifies each row/record in
a database table.

 Primary keys must contain unique values. A primary key column cannot have
NULL values.

 A table can have only one primary key, which may consist of single or
multiple fields.

 When multiple fields are used as a primary key, they are called a composite
key. If a table has a primary key defined on any field(s), then you can not have
two records having the same value of that field(s).

 Note: You would use these concepts while creating database tables.
30 EXAMPLE
CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID) );

To create a PRIMARY KEY constraint on the "ID" column when

CUSTOMERS table already exists, use the following SQL syntax:

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

31 CONT’S…
 For defining a PRIMARY KEY constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID,NAME) );

 FOREIGN Key
32
 A foreign key is a key used to link two tables together. This is
sometimes called a referencing key.

 Foreign Key is a column or a combination of columns whose

values match a Primary Key in a different table.

 The relationship between 2 tables matches the Primary Key in

one of the tables with a Foreign Key in the second table.

 If a table has a primary key defined on any field(s), then you

can not have two records having the same value of that
field(s).
 Example:
33 Consider the structure of the two tables as follows:

 CUSTOMERS table

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID) );

34 CONTS’…

ORDERS table:

CREATE TABLE ORDERS (

ID INT NOT NULL,

DATE DATETIME,

CUSTOMER_ID INT references CUSTOMERS(ID),

AMOUNT double,

PRIMARY KEY (ID) );

35 CHECK Constraint
 The CHECK Constraint enables a condition to check the value
being entered into a record. If the condition evaluates to false,
the record violates the constraint and isn’t entered into the
table.

 For example, the following SQL creates a new table called

CUSTOMERS and adds five columns. Here, we add a CHECK
with AGE column, so that you can not have any CUSTOMER
below 18 years:
36 Example:
CREATE TABLE CUSTOMERS(

ID INT NOT NULL ,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULLCHECK(AGE>=18),

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID) );

 INDEX
37

 The INDEX is used to create and retrieve data from the

database very quickly.

 Index can be created by using single or group of columns in a

table. When index is created, it is assigned a ROWID for each
row before it sorts out the data.

 Proper indexes are good for performance in large databases,

but you need to be careful while creating index. Selection of
fields depends on what you are using in your SQL queries.
38
Example:

 For example, the following SQL creates a new table called

CUSTOMERS and adds five columns:
CREATE TABLE CUSTOMERS(

ID INT NOT NULL ,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID) );

 CONT’S
39

 Now, you can create index on single or multiple columns using

the following syntax:

CREATE INDEX index name

ON table_name ( column1, column2.....);

To create an INDEX on AGE column, to optimize the search on

customers for a particular age, following is the SQL syntax:

CREATE INDEX idx_age

ON CUSTOMERS ( AGE );
40 SORTING
 Sorting (ascending, descending) using ORDER BY, DESC
and ASC.
 SQL ORDER BY Clause:
SELECT column1, column2....column
FROM table_name
ORDER BY column_name {ASC|DESC};
41 SQL Joins

 SQL Joins clause is used to combine records from two or more

tables in a database.

 A JOIN is a means for combining fields from two tables by

using values common to each.
 SQL Join Types:
42
 There are different types of joins available in SQL:

 INNER JOIN: returns rows when there is a match in both

tables.

 LEFT JOIN: returns all rows from the left table, even if there
are no matches in the right table.

 RIGHT JOIN: returns all rows from the right table, even if
there are no matches in the left table.

 FULL JOIN: returns rows when there is a match in one of the

tables.
 INNER JOIN
43
 The most frequently used and important of the joins is the
INNER JOIN. They are also referred to as an EQUIJOIN.

 The INNER JOIN creates a new result table by combining

column values of two tables (table1 and table2) based upon
the join-predicate.
 Syntax
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
 LEFT JOIN
44

 The SQL LEFT JOIN returns all rows from the left table, even if
there are no matches in the right table. This means that if the ON
clause matches 0 (zero) records in right table, the join will still
return a row in the result, but with NULL in each column from
right table.
 Syntax :
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_filed = table2.common_field;
 RIGHT JOIN
45

 The SQL RIGHT JOIN returns all rows from the right table,
even if there are no matches in the left table. This means that if
the ON clause matches 0 (zero) records in left table, the join
will still return a row in the result, but with NULL in each
column from left tables.

 Syntax :
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_filed = table2.common_field;
 FULL JOIN
46
 The SQL FULL JOIN combines the results of both left and
right outer joins. The joined table will contain all records from
both tables, and fill in NULLs for missing matches on either
side

 Syntax:

SELECT table1.column1, table2.column2...

FROM table1

FULL JOIN table2

ON table1.common_filed = table2.common_field;
47 Example (JOINS)
Student table
Roll no Name Age Address Phone_no
1 Ramat 32 ABC 0925544763
2 Burtukan 25 Delhi 0911125896
3 Kedir 23 UK 0952874113
4 Zola 25 USA 0947772123
5 Tola 22 ETH 0987456321

StudentCourse table
Course_id Roll no
102 1
100 2
101 3
103 4
48 SQL Aggregate Functions
 An aggregate function is a function that performs a calculation on a set of
values, and returns a single value.

 The most commonly used SQL aggregate functions are:

 MIN():returns the smallest value within the selected column

 MAX ():returns the largest value within the selected column

 COUNT():returns the number of rows in a set

 SUM():returns the total sum of a numerical column

 AVG(): returns the average value of a numerical column

 Aggregate functions ignore null values (except for COUNT())

 COUNT function
49
 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.

 Syntax:

COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
50 Example
 Product_mast table
51 Example …
SELECT COUNT(*)
FROM product_mast;
COUNT with WHERE
SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
COUNT() with GROUP BY
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
52 SUM Function
 Sum function is used to calculate the sum of all selected
columns. It works on numeric fields only.
 Syntax:
SUM()
or
SUM( [ALL|DISTINCT] expression )
53 Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;
SUM() with WHERE
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
SUM() with GROUP BY
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
54 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.
 Syntax:
AVG()
or
AVG( [ALL|DISTINCT] expression )
Example
SELECT AVG(COST)
FROM PRODUCT_MAST;
55 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.
 Syntax:
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE)
FROM PRODUCT_MAST;
56 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.
 Syntax:
MIN()
or
MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;
57 The SQL UNION Operator

The UNION operator is used to combine the result-set of two

or more SELECT statements.
•Every SELECT statement within UNION must have the
same number of columns
•The columns must also have similar data types
•The columns in every SELECT statement must also be in the
same order
58 UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL Syntax
The union operator selects only distinct values by default. To allow
duplicate values, use UNION ALL.
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
59 The SQL INTERSECT
Operator
 The INTERSECT operator in SQL is used to retrieve the
records that are identical/common between the result sets of
two or more tables.
 Sytanx:
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., column
FROM table1, table2,..., tableN
There are some mandatory rules for INTERSECT operations such as the
number of columns, data types, and other columns must be the same in
both SELECT statements for the INTERSECT operator to work correctly.
60 Example
61 INTERSECT with BETWEEN Operator

 We can use the INTERSECT operator with the BETWEEN

operator in SQL to find records that fall within a specified range.

 Example

 Now, let us retrieve the name, age, and hobby of students aged
between 25 and 30 from both the 'STUDENTS' and
'STUDENTS_HOBBY' tables, returning only the common rows
within the specified age range −
62 Conts’…
SELECT NAME, AGE, HOBBY

FROM STUDENTS_HOBBY

WHERE AGE BETWEEN 25 AND 30

INTERSECT

SELECT NAME, AGE, HOBBY FROM STUDENTS

WHERE AGE BETWEEN 20 AND 30;

63 The SQL EXCEPT Operator
 The EXCEPT operator in SQL is used to retrieve all the unique records
from the left operand (query), except the records that are present in the
result set of the right operand (query).
 Syntax:
SELECT column1, column2,..., column
FROM table1, table2,..., tableN
[Conditions] //optional
EXCEPT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN [Conditions] //optional
64 Example
SELECT NAME, HOBBY, AGE
FROM STUDENTS
EXCEPT
SELECT NAME, HOBBY, AGE
FROM STUDENTS_HOBBY;
65 SQL String Functions
 SQL String Functions are powerful tools that allow us
to manipulate, format, and extract specific parts of text data
in our database. These functions are essential for tasks
like cleaning up data, comparing strings, and combining text
fields. Whether we’re working with names, addresses, or any
form of textual data, mastering SQL string functions is crucial
for efficient data handling and analysis.
66 Common SQL String
Functions
 1. CONCAT(): Concatenate Strings
 The CONCAT() function is used to concatenate (combine)
two or more strings into one string. It is useful when we want
to merge fields like first and last names into a full name.
 Query :
select CONCAT( ‘A’, ‘ ‘, ‘B’) AS Full Name;
Output:
AB
67 2. CHAR_LENGTH() /
CHARACTER_LENGTH(): Find String
Length
 The CHAR_LENGTH() or LENGTH() function returns
the length of a string in characters. It’s essential for validating
or manipulating text data, especially when you need to know
how many characters a string contains.

 QUERY:

SELECT CHAR_LENGTH(‘ABCD’) AS string Length

Output: 4
 3. UPPER() and LOWER(): Convert
68 Text Case
 These functions convert the text to uppercase or lowercase,
respectively. They are useful for normalizing the case of text
in a database.

 Query:

 Select UPPER(‘abcd’) AS UpperCase;

 Select LOWER(‘ABCD’) AS LowerCase;

 Output:

 ABCD

 abcd
69 4. LENGTH(): Length of String in Bytes

 LENGTH() returns the length of a string in bytes.

This can be useful for working with multi-byte
character sets.

 Query:

 Select length(‘hello’) as lenghtInBytes

output: 5
70 5. LEFT() and RIGHT(): Extract Substring from
Left or Right

 The LEFT() and RIGHT() functions allow you to extract a specified

number of characters from the left or right side of a string,
respectively. It is used for truncating strings for display.

 QUERY:

SELECT LEFT(‘HELLO WORLD’,5) AS LeftString;

SELECT RIGHT (‘HELLO WORLD’,5) AS RightString;

OUTPUT: HELLO, WORLD