9 – Structured Query Language(SQL)

Components of SQL
1. Data Definition Language
DDL is a component of SQL that provides commands to deal with the schema (structure) definition
of the RDBMS. The DDL commands are used to create, modify and remove the database objects
such as tables, views and keys. The common DDL commands are CREATE, ALTER, and DROP.

Data Manipulation Language

DML is a component of SQL that enhances efficient user interaction with the database
system by providing a set of commands.
DML permits users to insert data into tables, retrieve existing data, delete data from tables and
modify the stored data. The common DML commands are SELECT, INSERT, UPDATE and
DELETE.

Data Control Language

Data Control Language (DCL) is used to control access to the database.

The commands GRANT and REVOKE are used as a part of DCL.

GRANT: Allows access privileges to the users to the database.
REVOKE: Withdraws user's access privileges given by using GRANT command.

Working on MySQL
MySQL is a free, fast, easy-to-use RDBMS, used for many applications.
MySQL is becoming very popular for many reasons:
• MySQL is released under an open-source license. So it is customizable.
• It provides high security to the database.
• It is portable as it works on many operating systems and with many languages
including PHP, PERL, C, C++, JAVA, etc.
• MySQL works rapidly and effectively even with large volume of data.
• It is highly compatible with PHP, one of the popular languages for web
development.

Creating a database

To create a database in MySQL, we use the

CREATE DATABASE command. The syntax is as follows:
CREATE DATABASE <database_name>;
Eg : CREATE DATABASE school;

Opening database
To perform operations on a database, we have to open it explicitly. When we open a database, it
becomes the active database in the MySQL server. MySQL gives a command USE to open a
database.
The syntax is:
USE <database_name>;
Let us open the data base school using the command as follows:
USE school;
The response of this command after the execution is given below:
Database changed
Now the database named school is the active database in our system. That means, the
different DDL, DML and DCL commands we execute hereafter will be related to the database
school.

The SHOW DATABASES command is used to check whether a database exists or not. It will list
the entire databases in our system. The syntax is:
SHOW DATABASES;

Data types in SQL

MySQL data types are classified into three. They are numeric data type, string (text) data type, and
date and time data type.

a. Numeric Data types

The most commonly used numeric data types in MySQL are INT or INTEGER and DEC or
DECIMAL.

(i) INT or INTEGER

Integers are whole numbers without a fractional part. They can be positive, zero or negative. The
data items like 69, 0, -112 belong to INT data type.

(ii) DEC or DECIMAL

Numbers with fractional parts can be represented by DEC or DECIMAL data type.
The standard form of this type is DECIMAL(size,D ) or DEC(size,D) . The parameter size indicates
the total number of digits the value contains including decimal part.
If we specify DEC(5,2), the range of values will be from -999.99 to +999.99

b. String (Text) data types

String is a group of characters. The most commonly used string data types in MySQL are
CHARACTER or CHAR and VARCHAR .

(i) CHAR or CHARACTER

The CHAR is a fixed length character data type. The syntax of this data type is CHAR(x) , where x
is the maximum number of characters that constitutes the data. The value of x can be between 0 and
255. CHAR is mainly used when the data in a column are of the same fixed length and small in size.

If the number of characters in the data is less than the declared size of the column, the remaining
character positions in the string will be filled with white spaces. So there is a wastage of memory.

(ii) VARCHAR(size)

VARCHAR represents variable length strings. It is similar to CHAR, but the space allocated for the
data depends only on the actual size of the string, not on the declared size of the [Link]
VARCHAR type saves memory space since VARCHAR type did not append spaces with the values
when they are stored. The length of the string can vary from 0 to 65535 characters.

C. Date and Time data types

(i) DATE
The DATE data type is used to store dates. MySQL represents date values in YYYY-MM-DD
'format. Example: '2024-10-17'
(ii) TIME
The TIME data type is used to specify a column to store time values in MySQL. It shows values in
the standard HH:MM:SS format. Example: '10:05:25'(10 hours 05 seconds and 25 seconds)

SQL commands

CREATE TABLE Command

The DDL command CREATE TABLE is used to define a table by specifying the name of the table
and giving the column definitions consisting of name of the column, data type and size, and
constraints if any, etc.
Syntax

CREATE TABLE <table_name>

(<column_name> <data_type> [<constraint>]
[, <column_name> <data_type> [<constraint>,]
..............................
..............................
);

Here, the <table_name> represents the name of the table that we want to create;
<column_name> represents the name of a column in the table;
<data_type> represents the type of data in a column of the table; and <constraint> specifies
the rules that we can set on the values of a column.

Rules for naming tables and columns

• The name may contain letters (A - Z, a - z), digits (0 - 9), under score ( _ ) and
dollar ($) symbol.
• The name must contain at least one character. (Names with only digits are
invalid).
• The name must not contain white spaces, special symbols.
• The name must not be an SQL keyword.
• The name should not duplicate with the names of other tables in the same data
base and with other columns in the same table.

Constraints
Constraints are the rules enforced on data that are entered into the column of a table.
Constraints ensure the accuracy and reliability of the data in the database.

Constraints could be column level or table level.

Column Constraints

1. NOT NULL
This constraint specifies that a column can never have NULL values.
ii. AUTO_INCREMENT
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
If no value is specified for the column with AUTO_INCREMENT constraint, then
MySQL will assign serial numbers automatically in that column.

iii. UNIQUE
It ensures that no two rows have the same value in the column specified with this
constraint.

iv. PRIMARY KEY

This constraint declares a column as the primary key of the table. A primary key constraint can be
considered as a combination of UNIQUE and NOT NULL constraints.

v. DEFAULT
Using this constraint, a default value can be set for a column, in case the user does not provide a
value for that column.

Example for creating a table using the constraints.

CREATE TABLE student
(adm_no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
gender CHAR DEFAULT 'M',
dob DATE,
course VARCHAR(15)
f_income INT);

b. Table constraints
When a constraint is to be applied on a group of columns of a table, it is called table constraint.

CREATE TABLE stock

(icode CHAR(2) PRIMARY KEY AUTO_INCREMENT,
iname VARCHAR(30) NOT NULL,
dt_purchase DATE,
rate DECIMAL(10,2),
qty INT,
UNIQUE (icode, iname));

In the above create table command, the constraint UNIQUE is applied to the combination of values
of columns icode and iname .
Viewing the structure of a table

The DESCRIBE command is used to display the structure

definitions of a table. The syntax is:
DESCRIBE <table_name>;
OR
DESC <table_name>;

The structure of the table student can be viewed using the command:
DESC student;

Inserting data into tables

The DML command INSERT INTO is used to insert tuples into tables.
Syntax:
INSERT INTO <table_name> [<column1>,<column2>,...,<columnN>]
VALUES(<value1>,<value2>,...,<valueN>);

Example
INSERT INTO student
VALUES (1001,'Alok','M','1998/10/2', 'Science', 24000);

INSERT INTO student (name, dob, course, f_income)

VALUES ('Nike','1998/11/26','Science',35000);

Here, the adm_no field will be filled with 1002 due to auto_increment constraint and gender field
will be filled with 'M' due to default constraint.

a. Condition based on a range of values

The SQL operator BETWEEN...AND is used to specify the range.
SELECT name, f_income FROM student
WHERE f_income>=25000 AND f_income<=45000;
The above command can be replaced using BETWEEN .....AND.
SELECT name, f_income FROM student
WHERE f_income BETWEEN 25000 AND 45000;

b. Conditions based on a list of values

SELECT * FROM student
WHERE course='Commerce' OR course='Humanities';
The above command retrieves the details of students studying for 'Commerce' and 'Humanities'.

SELECT * FROM student

WHERE course IN('Commerce', 'Humanities');
The command given above can be used to retrieve the same information by using IN clause.

SELECT * FROM student

WHERE course NOT IN('Science');
The command given above can be used to retrieve information who do not study 'Science' course.

c. Conditions based on pattern matching

SQL provides a pattern matching operator LIKE for retrieving data based on some pattern matching
. Patterns are specified using two special characters % and _ (underscore), where % (percentage)
matches a substring of characters and _ (underscore) matches a single character. Patterns are case
sensitive; i.e.; uppercase characters do not match lower case characters.

• "Ab%" matches any string beginning with "Ab".

• "%cat%" matches any string containing "cat" as substring. For example, "education", "indication",
"catering" etc.
• "_ _ _ _" matches any string of exactly four characters without any space in
between them.
• "_ _ _ %" matches any string of at least 3 characters.

SELECT name FROM student

WHERE name LIKE '%ar';
The above command retrieves the details of students ending with name 'ar'.
SELECT name FROM student
WHERE name LIKE 'Div_ _ ar';
The two characters can be any value. So the above command retrieves value of student 'Divakar'.

d. Conditions based on NULL value search

We can retrieve records with NULL values with the help of IS operator.
SELECT name, course FROM student
WHERE f_income IS NULL;
The above command retrieves the details of the students whose f_income is null.

SELECT name, course FROM student

WHERE f_income IS NOT NULL;
if we want to retrieve the records containing non-null values in the f_income column, the above
statement can be used.
Sorting results using ORDER BY clause
The result of a query can be sorted in the ascending or descending order by making use of ORDER
BY clause.
The order is to be specified by using the keyword ASC (for ascending) or DESC (for descending)
along with the column name that is used with ORDER BY clause.
By default, the display will be in the ascending order.

SELECT * FROM student ORDER BY name;

The above statement will retrieve the details of students in the ascending order of their names.
SELECT * FROM student
ORDER BY f_income DESC;
The above command displays the details of students in descending order of f_income.

Aggregate functions
MySQL provides a number of built-in functions that can be applied to all rows in a
table or to a subset of the table specified by WHERE clause.

SUM() Total of the values in the column specified as argument.

AVG() Average of the values in the column specified as argument.
MIN() Smallest value in the column specified as argument.
MAX() Largest of the values in the column specified as argument.
COUNT() Number of non NULL values in the column specified as argument.
Eg : SELECT MAX(f_income), MIN(f_income), AVG(f_income) FROM student;

Note that the * (asterisk) symbol stands for the collection of all the columns in the table. So, if there
is at least one field in a record, that record will be taken intoconsideration for COUNT(*) .
But COUNT(f_income) counts only the non- NULL values in column f_income of the records.

Grouping of records using GROUP BY clause

The rows of a table can be grouped together based on a common value using the GROUP BY
clause. The attribute (column) specified in the GROUP BY clause is used to form groups.

Applying conditions to form groups using HAVING clause

We can apply conditions to form groups with the help of HAVING clause.

SELECT course, COUNT(*) FROM student

GROUP BY course
HAVING COUNT(*) > 3;

The above code returns the course and count of students having a count greater than 3.

Modifying data in tables using UPDATE

Update command is used to modify the content of one or more rows in a table. The new data for the
column is given with the help of SET, which is an essential clause of UPDATE command.
The syntax of UPDATE command is:
UPDATE <table_name>
SET <column_name> = <value> [,<column_name> = <value>,...]
[WHERE <condition>];

Example:
UPDATE student
SET f_income=27000
WHERE name='Kaushi';
After the execution of this query, the income of student with name 'Kaushi' will be modified to
27000.
UPDATE student
SET f_income=20000
WHERE f_income IS NULL;
After the execution of this query, the f_income of all students which was NULL will be modified
to 20000.
Changing the structure of a table
SQL provides a DDL command ALTER TABLE to modify the structure of a table.

Adding a new column

One or more columns can be added at any position in an existing table.
Syntax:
ALTER TABLE <table_name>
ADD <column_name> <data_type> [<constraint>]
[FIRST | AFTER <column_name>];

ALTER TABLE student

ADD gr_mks INTEGER AFTER dob,
ADD reg_no INTEGER;
The above command adds a column gr_mks after the column dob, and adds a column reg_no as the
last column of the table.

Changing the definition of a column

We can modify the characteristics of a column like data type, size and/or constraints by using the
clause MODIFY with ALTER TABLE command.
Syntax :
ALTER TABLE <table_name>
MODIFY <column_name> <data_type>[<size>] [<constraint>];
Example:
ALTER TABLE student
MODIFY reg_no INTEGER UNIQUE;
Here the column reg_no is modified to add UNIQUE constraint.

Removing column from a table

If we want to remove an existing column from a table, we can use DROP clause along with ALTER
TABLE command.
Syntax:
ALTER TABLE <table_name>
DROP <column_name>;
Example :
ALTER TABLE student
DROP gr_mks;
The above command removes the gr_mks column from the table student.

Renaming a table
We can rename a table in the database by using the clause RENAME TO along with ALTER
TABLE command.
Syntax:
ALTER TABLE <table_name>
RENAME TO <new_table_name>;
Example:
ALTER TABLE student
RENAME TO student2023;
Here, the name of the table will be modified to student2023.

Deleting rows from a table

The DML command DELETE is used to remove individual or a set of rows from a table. The rows
which are to be deleted are selected by using the WHERE clause. If the WHERE clause is not used,
all the rows in the table will be deleted.
Syntax:
DELETE FROM <table_name> [WHERE <condition>];
Example:
DELETE FROM student WHERE adm_no=1010;
The above command removes the row whose adm_no is 1010 from the table student.

Removing table from a database

A table in a database can be removed from the database using DDL command DROP TABLE.
Syntax :
DROP TABLE <table_name>;
Example :
DROP TABLE student;
The above command removes the table student from the database.

Nested queries
Here the result of one query is dynamically substituted in the condition of another. A MySQL inner
query is also called sub query, while the query that contains the sub query is called an outer query.
SQL first evaluates the inner query(sub query) within the WHERE clause and the result of inner
query is then substituted in the condition of the outer query.

SELECT name, course FROM student

WHERE f_income=(SELECT MAX(f_income) FROM student);

The above command returns the name and course of the student who has highest income.

Concept of views
A view is a virtual table that does not really exist in the database, but is derived from one or more
tables.
CREATE VIEW <view_name>
AS SELECT <column_name1> [,<column_name2],...]
FROM <table_name>
[WHERE <condition>];

Example
CREATE VIEW Science
AS SELECT * FROM student
WHERE course = 'Science';
The above command creates a view named 'Science' from table student. This view will contain only
the details of students who are studying for 'Science' course.

These tuples are not physically stored anywhere, but its definition is stored in the database.
We can use all the DML commands with the view in the same way as we use in the case of tables.
The modification done to a view using a DML command will be reflected in the base table of the
view.

A view can be removed from the database with DROP VIEW command. But it will not affect the
base table(s).
Syntax:
DROP VIEW <view_name>;
Example
DROP VIEW Science;
The above command will remove the view 'Science' from the database.

Comparison between UNIQUE and PRIMARY KEY

UNIQUE PRIMARY KEY

NULL values are allowed for UNIQUE NULL values are not allowed for PRIMARY
constraint KEY constraint

Comparison between WHERE and HAVING clause

WHERE HAVING
WHERE clause is used to check condition on HAVING clause is used to check condition on a
individual rows. group of rows.
It can not be used with GROUP BY clause. It is only used with GROUP BY clause.

Comparison between UPDATE and ALTER commands

UPDATE ALTER
UPDATE is a DML command. ALTER is a DDL command.
It is used to modify the content of rows in a It is used to modify the structure of the table.
table.

Comparison between DELETE and DROP command

DELETE DROP
UPDATE is a DML command. DROP is a DDL command.
It is used to remove one or more rows from a It is used to remove the entire table from the
table. database.