UNIT – III Database Management

Structured Query Language

----------------------------------------------------------------------------------------------
Features of MYSQL:
Speed , Ease of use, cost, Query Language support, Portability, Data types,
Security, Connectivity.
Classification of SQL statements
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Data Definition Language(DDL):

o Data definition language (DDL) refers to the set of SQL commands that can
create and manipulate the structures of a database.
The commonly used commands of DDL are
• CREATE – To create a new database object
• ALTER – To modify existing database object
• DROP – To permanently remove existing database object.
• TRUNCATE - To remove all records from a table, including all spaces
allocated for the records are removed.
• COMMENT - To add comments to the data dictionary.
• RENAME - To rename an object existing in the database
.
Data Manipulation Language(DML):
✓ It is a language that enables users to access or manipulate data as organized by
the appropriate data model.

✓ DML commands are used to modify the database. It is responsible for all changes in
the database.
✓ DML is of two types:
Procedural DMLs require a user to specify what data is needed and how to get it.

1
 Non-Procedural DMLs require a user to specify what data is needed without
specifying how to get it.
The commonly used commands of DML are
• SELECT – To retrieve data from the table
• INSERT – To insert new tuple/row in the table
• UPDATE – To update existing tuple/row from the table
• DELETE – To delete existing tuple/row from the table

Transaction Control Language

TCL commands can only be used with DML commands like INSERT, DELETE and UPDATE.
• COMMIT: to save all the transactions to the database.
• ROLLBACK: undo transactions that have not already been saved to the
database.
• SAVEPOINT: Sets a savepoint within a transaction.
• SET TRANSACTION: establishes properties for the current transactions.
Data Control Language[DCL]
DCL commands are used to grant and take back authority from any database user.

• Grant: It is used to give users access privileges to a database.

• Revoke: It is used to take back permissions from the user.

DDL DML

DDL stands for Data Definition Language. DML stands for Data Manipulation Language.
It is used to create databases, schemas, It is used to select, insert, update or delete the
constraints, users, tables etc. records.

DDL has no further classification. DML is further classified into procedural DML
and non-procedural DML

Basic commands present in DDL are CREATE, Basic commands present in DML are INSERT,
DROP, RENAME ,ALTER etc SELECT, UPDATE ,DELETE etc

MYSQL ,Table Creation & Data Manipulation Commands

MYSQL Elements
✓ Literals : Refers to a fixed data value.
Types :Character literal eg : “hello” .
Numeric Literals can either be integer literals [without any
decimal] or real literals [with decimal].
✓ Data types
Types :Numeric, Date and Time , String
✓ Nulls : If a column in a row has no value, then column is said to be null.
✓ Comments : It is a non-executed text.

2
 1. Date and time values are always in the format YYYY-MM-DD HH:MM:SS
2. Enclose character datatype values in single or double quotation marks

Difference between char and varchar

Char Varchar
The CHAR datatype is used to store The VARCHAR datatype is used to store
character strings of fixed length. character strings of variable length.
In VARCHAR, if the length of the string is
In CHAR, if the length of the string is less less than the set or fixed length, then it
than the set or fixed length, then it is will be stored as is without being padded
padded with extra memory space. with extra memory spaces.
The storage size of the VARCHAR datatype
The storage size of CHAR datatypes is is equal to the actual length of the entered
equal to n bytes, i.e., set length. string in bytes.
Slow, as it takes size according to data, so
Fast, no memory allocation every time every time memory allocation is done,.

Simple Queries in MySQL

1. List all databases on MySql Server
show databases;
2. To create a new database.
Create database databasename
mysql>create database eg;
The above command will generate an error if the database already exists. To avoid
the error, the following command can be used:
mysql>create database if not exists eg;
3. To change/select/open given database
use databasename;
mysql> use eg;
4. List all the tables in the current database. If no database is selected, it will generate an error.

show tables;
5. Display the name of the current database.

select database();
6. To create a new table in a database.
create table table_name( column1 datatype[(size)] [constraint] ,column2
datatype[(Size)] [constraint] ,....);

3
 mysql>create table student (rollno int primary key, sname VARCHAR(20) not null,
rank int(2));
A constraint is a condition or check applicable to a field or set of fields. These can
be specified at the time of creating the table. They are used to ensure the integrity
of a relation, hence named as integrity constraints. Types: Column constraint and
Table constraint.

• NOT NULL Constraint It ensures that a column cannot store a NULL value.
• UNIQUE Constraint It is used to uniquely identify each record in a
database.
• PRIMARY KEY Constraint uniquely identifies each record in a table. The
primary key cannot contain NULL values. A table can have only one primary
key.
• FOREIGN KEY Constraint It is a column or combination of columns as a
foreign key and establishes its relationship with a primary key in different
tables.
• CHECK KEY Constraint It is used to limit the range of values for that
column.
• DEFAULT Constraint It is used to set the default value for a column.

Difference between unique and primary key constraints.

PRIMARY KEY Constraint UNIQUE Constraint
Only one primary key can be created on More than one UNIQUE constraint can
a table. be added to a table.
We cannot insert null values in the We can insert null values in the column
column, which is defined as PRIMARY with UNIQUE constraint.
KEY.
With the primary key, we cannot modify With the unique key, we can modify the
or delete the values. column values.

7. To display the structure of the table.

desc<tablename> (or)
describe <tablename>
mysql> desc student;
8. To add a new row to the table.
INSERT INTO table_name VALUES (list of values); (or)
INSERT INTO table_name(column1, column2, ...)VALUES(value1, value2,
...);

4
 mysql>insert into student values (10,'sumitha',5);
9. Select data from table using Select statement
1. Selecting/displaying the entire data from the table
SELECT * From tablename;
mysql>select * from student;

[Link]/displaying data from a specific column

SELECT column1, column2, column3, …. FROM tablename;
mysql>select rollno,sname from student;

[Link] alias name for output purpose.

SELECT */col_name as ‘aliasname’ FROM tablename;
mysql> select sname as 'student name' from student;

4. Removing duplicate values from the column using a distinct clause

SELECT DISTINCT column_name FROM tablename;
mysql> select distinct dept from student;

[Link] simple calculations in the query

mysql>Select 2+4;
All operations can be performed: addition (+), subtraction (-), multiplication (*),
division(/) and remainder (%)

[Link] rows using the where clause.

SELECT columnname FROM tablename where condition ;
mysql>select * from student where mark>75;

[Link] nulls: The IFNULL() function returns a specified value if the expression is
NULL.
mysql>SELECT IFNULL(mark, “absent”) from student;

10. Update data from the table.

UPDATE table_name SET column_name=new_value WHERE condition;
mysql>UPDATE student set dept=’mat-csc’ where rollno =2;

11. Membership Operator

in -operator allows you to specify multiple values in a WHERE clause. The IN
operator is a shorthand for multiple OR conditions.
mysql> select * from student where dept in(‘mat-cs’,’bio-cs’);

5
 Not in operator makes sure that the expression proceeded does not have any of the
values present in the arguments.
mysql> select * from student where dept not in(‘mat-cs’,’bio-cs’);
12. Searching for NULL

NULL (absence of value) cannot be compared using the relational operator. It is not
possible to test for NULL values with comparison operators such as =, <, or <>.

We have to use the IS NULL and IS NOT NULL operators.

is null - operator checks whether a value is NULL.

is not null - operator checks whether a value is not NULL

mysql> select sname from student where dept is null;

13. Range Operator

Between operator selects values within a given range. The values can be
numbers, text, or dates. Both the lower value and the upper value are inclusive
mysql> select * from student where mark between 90 and 100;

14. ORDER BY clause is used to arrange data in ascending or descending order.

Ordering can be done on more than one column; in this case, data is arranged
according to the first column and then the second column and so on. By default,
the data is arranged in ascending order.
mysql> select * from student order by dept;
mysql> select * from student order by dept desc, sname asc;
15. Pattern Matching
Like is used to match patterns using two wild card characters.
Not Like is used to exclude those rows that match the criteria followed by the LIKE
operator.
Wildcard characters: _ (underscore) – single unknown character
% (modulo) - Zero or more unknown characters
t%’ words staring with ‘t’
‘%t’ words ending with ‘t’
‘%t%’ words containing ‘t’
‘_t%’ word with ‘t’ as the second letter
‘%t_ _’ word with ‘t’ as the third last character.
‘_ t_ _’ word containing four letters and ‘t’ as the second letter.
16. Alter table command is used to modify the structure of an existing table, such as
adding a new column, removing an existing column, renaming it, or changing the
data type, size and constraints.

6
 1. Adding new column to the exiting table

Alter table tablename Add column_name datatype [(size)];

mysql>alter table student add mark int;

2. Removing an existing column from the table

Alter table tablename drop column column_name;

mysql> alter table student drop column mark;

3. Changing datatype or size of the column

Alter table tablename modify colname datatype(size);

mysql> alter table student modify dept varchar(30);

4. Renaming a column

Alter table tablename Change oldcolname newcolname datatype (size);

mysql> alter table student change rollno regno int(5);

5. To remove the constraint

alter table tablename drop constraintname;

mysql> alter table student drop primary key;

6. Add the constraint

Alter table tablename add constraintname (columnname);

mysql> alter table student add primary key(rollno);

7. Order of Columns

alter table tablename modify columnname datatype(size) [First/After] columnname;

mysql> alter table student modify name varchar(25) first;

17. Drop table command is used to permanently delete the table from the database.
drop table <tablename>;
mysql>drop table student;

7
 18. Drop database command is used to remove an existing database.
drop database<databasename>;
mysql>drop database eg;
The above command will generate an error, if database already exists. To avoid
this error, the following command can be used:
mysql>drop database if exists mydb;

▪ Relational Operator
= Equal to > Greater than <Lesser than >=Greater than equal to
<=Lesser than equal to <> or != Not Equal to

▪ Logical Operator
The Logical operator[AND OR NOT] is used to combine two or more logical
expressions.

MYSQL Functions:
-----------------------------------------------------------------------------------------------
A function is a special type of predefined command set that performs some operation and
returns a single value.

Single row Functions : String functions, Numeric functions & Date functions.
Multiple row Functions: Aggregate functions.

String Functions:
Function Description Example
UCASE(string) Converts a string into mysql> select ucase("hello");
OR uppercase. ----------------
UPPER(string) ucase("hello")
----------------
HELLO
LCASE(string) Converts a string into mysql> select ucase("hello");
OR lowercase. ----------------
LOWER(string) ucase("hello")
----------------
HELLO
MID(string, pos, n) Returns a substring of mysql> select mid("computer",3,4);
OR size n starting from the ---------------------
SUBSTRING(string, specified position (pos) mid("computer",3,4)
of the string. If n is not
pos, n) ---------------------
specified, it returns the
OR mput
substring from the
SUBSTR(string, pos, n) position pos until the mysql> select mid("computer",5);
end of the string. -------------------
mid("computer",5)
-------------------
uter
8
LENGTH(string) Return the number of mysql> select length("computer");
characters in the --------------------
specified string. length("computer")
--------------------
8

LEFT(string, N) Returns N number of mysql> select left("computer",4);

characters from the --------------------
left side of the string. left("computer",4)
--------------------
comp
RIGHT(string, N) Returns N number of mysql> select right("computer",4);
characters from the --------------------
right side of the right("computer",4)
string. --------------------
uter
INSTR(string, Returns the position mysql> select instr("computer","mp");
substring) of the first ------------------------
occurrence of the instr("computer","mp")
substring in the ------------------------
given string. Returns 3
0, if the substring is
not present in the
string.
LTRIM(string) Returns the given mysql> select ltrim(" computer ");
string after removing ------------------------------
leading white space ltrim(" computer ")
characters. ------------------------------
computer

RTRIM(string) Returns the given mysql> select rtrim(" computer ");

string after removing ------------------------------
trailing white space rtrim(" computer ")
characters. ------------------------------
computer
------------------------------
TRIM(string) Returns the given mysql> select trim(" computer ");
string after removing -----------------------------
both leading and trim(" computer ")
trailing white space -----------------------------
characters. computer
char(value) Returns the mysql> select char(65);
character for each ----------
integer passed. char(65)
----------
A

mysql> select char(65,66);

-------------
char(65,66)
-------------
AB
concat(string,string) adds two or more mysql> select
concat("computer","science");
expressions together
concat("computer","science")
------------------------------
9
 computerscience

Numeric Functions:
Function Description Example
POWER(X,Y) Calculates X to the mysql> select power(5,2);
power of Y. power(5,2)
POW(X,Y) --------------
25

ROUND(N,D) Rounds off number N mysql> select round(549.79,1);

to D number of round(549.79,1)
decimal places. --------------------
Note: If D=0, then it 549.8
rounds off the mysql> select round(549.79,2);
number to the round(549.79,2)
nearest integer. -----------------
549.79

mysql> select round(549.79,-1);

round(549.79,-1)
------------------
550

mysql> select round(549.79,-2);

------------------
round(549.79,-2)
------------------
500

MOD(A, B) Returns the remain mysql> select mod(21,2);

der after dividing mod(21,2)
number A by number -----------
B. 1
sign(a) Returns the sign of a mysql> select sign(-155);
number ------------
sign(-155)
------------
-1

mysql> select sign(155);

-----------
sign(155)
-----------
1
sqrt(a) returns the square mysql> select sqrt(81);
root of a number ----------
sqrt(81)
----------
9

truncate (a,b) Truncates a number mysql> select truncate(549.79,1);

to the specified --------------------
truncate(549.79,1)
10
 number of decimal --------------------
places 549.7

mysql> select truncate(549.79,2);

--------------------
truncate(549.79,2)
--------------------
549.79

mysql> select truncate(549.79,-1);

---------------------
truncate(549.79,-1)
---------------------
540
ABS(value): It returns the mysql> select ABS(-10);
absolute value of a ----------
number. ABS(-10)
----------
10

CEIL(Value) CEIL function mysql> select ceil(100.49);

accepts a numeric ----------
expression and ceil(100.49)
----------
rounds up the
101
argument to the
nearest integer

FLOOR(Value) Floor function mysql> select floor(100.99);

accepts a numeric ----------
expression and ceil(100.99)
----------
rounds up the
100
argument to the
lowest integer

Date and Time Functions:

Function Description Example

It returns the current mysql> select now();
system date and --------------------
NOW() time. now()
---------------------
2023-11-26 17:01:43
It returns the date mysql> select date(now());
part from the given -------------
DATE() date/time date(now())
expression. -------------
2023-11-26
mysql> select date('2023-11-26');
--------------------
date('2023-11-26')
--------------------
2023-11-26
It returns the month mysql> select month(now());
in numeric form from --------------
MONTH(date) the date. month(now())

11
 --------------
11
mysql> select month('2023-11-26');
--------------------
month('2022-11-26')
--------------------
11

It returns the month mysql> select monthname('2024-01-01');

name from the ------------------------
MONTHNAME(date) specified date. monthname('2024-01-01')
-------------------------
January

YEAR(date) It returns the year mysql> select year('2024-01-01');

from the date. --------------------
year('2024-01-01')
--------------------
2024
DAY(date) It returns the day mysql> select day('2024-01-01');
part from the date. -------------------
day('2024-01-01')
-------------------
1
DAYNAME(date) It returns the name mysql> select dayname('2024-01-01');
of the day from the -------------------------
date. dayname('2023-01-01')
-------------------------
Monday
sysdate() Returns the current mysql> select sysdate();
date and time ---------------------
sysdate()
---------------------
2023-11-26 7:04:05

Aggregate Functions

• An aggregate function performs a calculation on multiple values and returns a

single value per group.
• These sets of rows to which the group function is applied may be the whole table or
the table split into groups.

Function Description Output

Returns the largest value from the mysql> SELECT MAX(MARK) FROM
specified column. STUDENT;
MAX(column)
Output:
99
Returns the smallest value from the mysql> SELECT MIN(MARK) FROM
specified column. STUDENT;
MIN(column)
Output:
12
 23
Returns the average of the values in mysql> SELECT AVG(MARK) FROM
the specified column. STUDENT;
AVG(column)
Output:
58.7143
SUM(column) Returns the sum of the values for the mysql> SELECT SUM(MARK) FROM
specified column. STUDENT;
Output:
411
COUNT Returns the number of values in the
(column) specified column ignoring the NULL
values.
Note:
In this example, let us consider a
MANAGER table with two attributes
and four records.

Returns the number of records in a

table.
Note: In order to display the number
of records that match a particular
COUNT(*) criteria in the table, we have to use
COUNT(*) with the WHERE clause.

Note:

• All group functions,except count (*), ignore NULL values.

• Functions -sum() and -avg() are used with NUMERIC data.
• Functions -min() and max() can be used with any data type.

Differentiate between Single row function and multiple row function

13
 Differentiate between WHERE clause and HAVING clause
WHERE Clause HAVING Clause

WHERE Clause is used to filter the records HAVING Clause is used to filter record from
from the table based on the specified the groups based on the specified
condition. condition.

Can be used without GROUP BY Clause Cannot be used without GROUP BY Clause

Implements Row operations Implements Column operations

Select, update and delete statements Only select statement

WHERE Clause is used before GROUP BY HAVING Clause is used after GROUP BY
Clause Clause

Used with single row operations such as Applicable with multiple row functions such
Upper, Lower and so on as Sum, count and so on

Grouping Records, Joins in SQL

Group By Clause :
• The GROUP BY clause is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.

• The SELECT statement is used with the GROUP BY clause.

• WHERE clause is placed before the GROUP BY clause.
• ORDER BY clause is placed after the GROUP BY clause.

SYNTAX:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

14
mysql>select dept, count(*) from student group by dept;

mysql> select dept,count(*) from student group by dept order by dept desc;

mysql> select name,dept,count(*) from student group by dept;

mysql> select name,dept,max(mark) from student group by dept;

15
Note :
A non – group field is the field that has different values in the rows belonging to
that group. In this case, it will return the value from the first record of the group from that
non – group field.

Having clause:
• The HAVING clause is used with GROUP BY clause to place condition because the
WHERE keyword could not be used with aggregate functions.
• If you omit the GROUP BY clause, the HAVING clause behaves like the WHERE
clause.
SYNTAX:
SELECT column(s) FROM table_name WHERE condition GROUP BY column1,
HAVING condition;

Joins
✓ A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
✓ In a join query, more than one tables are listed in FROM clause.
✓ The SQL Join condition is always used in the WHERE clause of SELECT, UPDATE and
DELETE statements.
✓ When two or more tables have a column with the same name, the name can be
accessed by using the table name combined with the period (.) in referring to the
column. Eg: [Link]

Cartesian Product (Cross Join or unrestricted join)

✓ In a CARTESIAN JOIN there is a join for each row of one table to every row of
another table. This usually happens when the matching column or WHERE condition
is not specified.
✓ It returns n1 x n2 rows where n1 is the number of rows in the first table and n2 is
the number of rows in the second table.

16
SELECT * FROM CUSTOMERS, CONTACTS;

Equi Join
The join, in which the columns are compared for equality (=). Results of Equi joins
are the same as simple joins or inner joins.

e.g. To join two tables PERSON and ORDERS using a P_Id column (common column)
MySQL>SELECT Last_Name, First_Name, Order_No FROM PERSON, ORDERS WHERE
PERSON.P_Id=ORDERS.P_Id ORDER BY PERSON.Last_Name;
17
NON EQUI JOIN :
NON EQUI JOIN performs a JOIN using comparison operator other than equal(=)
sign like >, <, >=, <= with conditions.

Natural Join

• Natural join is an SQL join operation that creates a join on the base of the
common columns in the tables
• The join in which only one of the identical columns exists is called a natural join.
• The natural join is a type of equi join and is structured in such a way that columns
with the same name as associated tables will appear only once.
SELECT * FROM EMPLOYEE;

SELECT * FROM DEPARTMENT;

SELECT * FROM employee NATURAL JOIN department;

18
Table aliases
It is a temporary label given along with the table name in FROM clause.

19