CHAPTER 9 - STRUCTURED QUERY

LANGUAGE (SQL)
ELEMENTS OF MYSQL
 ELEMENTS OF MYSQL
The MYSQL implementation of SQL has certain elements that plays
an important role in defining/ querying a database. Some MYSQL basic
elements are…

MYSQL
Elements

Data
Literals Nulls Comments
Types 3
 ELEMENTS OF MYSQL
A. Literals:
Literals, in general, refer to fixed data value. This fixed value may be
of character or numeric literals e.g. ‘Synthia’, 8, 25.5, ‘Raj Singh’ etc.
the character literals are always enclosed with in single quotes. a text
literal can have maximum length of 4000 bytes where as a numeric
literal can store a maximum of 53 digits of precision.

‘A’ is a text literal

3 is a numeric 4
literal
ELEMENTS OF MYSQL

5
 ELEMENTS OF MYSQL
B. Data Types:
Data Types in MYSQL are used to assign the proper types of the
attributes (columns) at the time of creating relation(table). We can
choose the data types for an attribute based on the values we want
to store in it. MYSQL offers the following set of Data Types.

Data
Types

Date and
Numeric String
Time
6
 ELEMENTS OF MYSQL
B-1. Numeric:
This type of data type is used to store the numbers as a value with in
an attribute of a relation. A numbers (value) can be either with
fraction and without fractional part. In MYSQL the following data
types supports attribute’s value as numbers.
Sr No Data Type Description
1 INT Occupies 4 bytes per Cell
2 TINYINT Occupies 1 byte per Cell
3 SMALLINT Occupies 2 bytes per Cell
4 MEDIUMINT Occupies 3 bytes per Cell
5 BIGINT Occupies 8 bytes per Cell
6 FLOAT(P,S) Precision can be upto 24 places
7 DOUBLE (P,S) Precision can be upto 53 places
7
8 DECIMAL(P,S) Precision can be upto 53 places
 ELEMENTS OF MYSQL
e.g.
if we take float(10,4) or double(10,4) or decimal(10,4) then 10 is
Precision (total number of digits to be stored) and 4 is scale
(total number of digits supported after decimal point).

8
 ELEMENTS OF MYSQL
B-2. Date and Time:
This type of data type is used to store the Date and/ or Time as a
value with in an attribute of a relation. In MYSQL the following data
types supports attribute’s value as Date and/ or Time.
Sr No Data Type Description
1 DATE Stores date in YYYY-MM-DD format.
2 DATETIME Stores date and time in YYYY-MM-DD
HH:MM:SS format.
3 TIMESTAMP Stores date and time in YYYYMMDDHHMMSS
format
4 TIME Stores time in HH:MM:SS format
5 YEAR Stores year in 2-digit or 4-digit format.

9
 ELEMENTS OF MYSQL
B-3. String:
This type of data type is used to store the numbers as a text with in
an attribute of a relation. A texts (value) should be specified with in
single or double quotes. In MYSQL the following data types store
strings.
Sr No Data Type Description
1 CHAR(SIZE) A Fixed Length string between 1 to 255 characters. By default only 1
char per cell.
2 VARCHAR(SIZE) A Variable Length string between 1 to 255 or 65535 characters
depending upon MYSQL version. Length should be defined when
declaring it as varchar.
3 BLOB or TEXT It can store maximum 65635 characters. It is referred to binary large
objects.
4 TINYBLOB or It can store maximum 255 characters. It is referred to tiny binary large
TINYTEXT objects.
5 MEDIUMBLOB or It can store maximum 16777215 characters. It is referred to medium
MEDIUMTEXT binary large objects. 10
6 LONGBLOB or It can store maximum 4294967295 characters. It is referred to Long
LONGTEXT binary large objects.
 ELEMENTS OF MYSQL
Char Varchar

A Fixed Length string between 1 to 255 characters. A Variable Length string between 1 to 255 or
By default only 1 char per cell. 65535 characters depending upon MYSQL
version. Length should be defined when
declaring it as varchar.
When we use char with size 10 characters per cell, When we use varchar with size 10 characters
and specify lesser number characters as a value of per cell, and specify lesser number characters as
cell then also cell will occupy 10 characters by a value of cell then cell will occupy specified
suffixing trailing spaces. characters instead of suffixing trailing spaces.

11
 ELEMENTS OF MYSQL
C. Null Values:
Null Values are the special type of values that indicates to EMPTY
Cells. The null values of a attribute can be avoided using NOT
NULL constraint. Null value can be replaced but it can not be
processed. Any arithmetic expression containing null, always
evaluates to null e.g. null + 12 = null, null*45=null etc.

Null values.

12
 ELEMENTS OF MYSQL
D. Comments:
Comment is a text that is not executed instead of this it is used as an
internal document of your SQL command. This text will be helpful to
the users who wants to understand your SQL command. MYSQL
supports two types of comments namely single line and multi line.
The single line comments starts with either - - or # where as multi line
comments start with /* and ends with */

Multiline
comments…

13
SQL COMMANDS
MYSQL DATA DEFINITION LANGUAGE COMMANDS
1. To Create a New Database in MYSQL:
The Create Database command in MYSQL is used to create a new
database in MYSQL Server. This command accepts the name of the
database and creates it. If a database already exists then MYSQL
will report an error. The SYNTAX of this command is Create
Database <DatabaseName>;

A New Database
namely
MYTestDatabase
is created.
15
MYSQL DATA DEFINITION LANGUAGE COMMANDS

MYTestDatabase
is listed with the
list of database.

16
MYSQL DATA DEFINITION LANGUAGE COMMANDS
Naming Convention In MYSQL:
In computer programming, a naming convention is a set of rules for
choosing the character sequence to be used for identifiers like
DATABSE, TABLE, COLUMN or some other database object.

General Rules:
❑ Using lowercase will help speed typing, avoid mistakes as
MYSQL is case sensitive for Names.
❑ Using space between words is not allowed.
❑ While naming, it is essential that it starts with alphabet _ or $,
numbers are allowed from 2nd place onwards.
❑ Names should be a descriptive of the elements. i.e. — Self-
explanatory and not more than 64 characters. 17

❑ Name must be short and meaningful.

 MYSQL DATA DEFINITION LANGUAGE COMMANDS
2. To List Available Databases in MYSQL;
The Show Databases command in MYSQL is used to list all existing
databases in MYSQL Server. The SYNTAX of this command is Show
Databases;

List of available
databases.

18
MYSQL DATA DEFINITION LANGUAGE COMMANDS
3. To Delete (Remove) Databases in MYSQL;
The Drop Database command in MYSQL is used to delete and
existing database in MYSQL Server. The SYNTAX of this command
is Drop Database <DatabaseName>; or Drop Database If
Exists <DatabaseName>; Once a database is dropped then its all
associated tables and data will be destroyed and it will never be
restored.

Database is
dropped.

19
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Dropped database
will no longer
available in
MYSQL.

20
MYSQL DATA DEFINITION LANGUAGE COMMANDS
4. To Open a Databases in MYSQL;
The Use command in MYSQL is used to open and existing database
in MYSQL Server. The SYNTAX of this command is Use
<DatabaseName>. Once a database in opened that it is ready for all
DML commands or in other words all DML command will be executed
on the tables available in it. If a database is not opened the MYSQL
will report and error.

db1 database is
opened.

21
MYSQL DATA DEFINITION LANGUAGE COMMANDS

MYSQL reports an
error because
database it not
opened.

22
MYSQL DATA DEFINITION LANGUAGE COMMANDS
5. To List available tables in Database in MYSQL;
The show tables command in MYSQL is used to list existing tables
with in opened database. The SYNTAX of this command is show
tables;

List of tables
available in cce
database.

23
MYSQL DATA DEFINITION LANGUAGE COMMANDS
6. To Create a Table in Database in MYSQL;
The Create Table command in MYSQL is used to create a new table
with in opened database. The SYNTAX of this command is
Create Table <TableName>
(
ColName_1 Datatype (Size) Column Level Constraint,
ColName_2 Datatype (Size) Column Level Constraint,
ColName_3 Datatype (Size) Column Level Constraint,
.
.
.
24
ColName_N Datatype (Size) Column Level Constraint
);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table
created in Test
Database
25
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks table
created in Test
Database
26
MYSQL DATA DEFINITION LANGUAGE COMMANDS
7. To Create a Table with Column Level Primary Key Constraint:
The Primary Key constraint is a rule in table that allows you to
identify tuples uniquely. When we apply a primary key constraint to a
column then in future that column will not allow duplication as well as
value should be compulsorily entered at the time of inserting or
updating a tuple. There will be only one Column Level Primary Key
Constraint per table.
Create Table <TableName>
(
ColName_1 Datatype (Size) Primary Key,
ColName_2 Datatype (Size) Column Level Constraint,
.
27
ColName_N Datatype (Size) Column Level Constraint
);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table
created in Test
Database with
column level
primary key 28
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student can not

be created as
multiple primary
keys.

29
MYSQL DATA DEFINITION LANGUAGE COMMANDS
If we want multiple primary keys on a table then we should use table level
primary key instead of column level.

Student is created
using multiple
primary keys on
different columns.
30
MYSQL DATA DEFINITION LANGUAGE COMMANDS
8. To Create a Table with Unique Constraint:
The Unique constraint is a rule in table that allows you to identify
tuples uniquely, this feature is similar to primary key but unique key
will accept null values where primary key will not allow null values.
When we apply a unique constraint to a column then in future
that column will not allow duplication but it allows null values. Unique
constraint can be applied on more than one columns.
Create Table <TableName>
(
ColName_1 Datatype (Size) Primary Key,
ColName_2 Datatype (Size) unique,
31
ColName_N Datatype (Size) unique
);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
unique key constraint.

32
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
unique key constraint.

33
MYSQL DATA DEFINITION LANGUAGE COMMANDS
9. To Create a Table with Not Null Constraint:
The Not Null constraint is a rule in table that allows you to avoid null
values (empty cells) with in column at the time or inserting or
updating it. A table can contain any number of columns with Not Null
constraint. A not null and unique constraint can be combined
and applied on column. If we apply the not null and unique constraint
combination to a column than that column will not allow duplicate
as well as it will also not allow null values.
Create Table <TableName>
(
ColName_1 Datatype (Size) not null,
ColName_2 Datatype (Size) not null unique,
34
ColName_N Datatype (Size) unique
);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
not null constraint.

35
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
not null and unique
constraints.

36
MYSQL DATA DEFINITION LANGUAGE COMMANDS
10. To Create a Table with Default Constraint:
The Default constraint is a rule in table that allows you to avoid null
values (empty cells) by setting default value to null values in a
column. A table can contain any number of columns with default
constraint. Default value will be inserted automatically when a
column is left blank during inserting a new record.
Create Table <TableName>
(
ColName_1 Datatype (Size) default <Default Value>,
ColName_2 Datatype (Size) default <Default Value>,
.
ColName_N Datatype (Size) default <Default Value> 37

);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
default constraint.

38
MYSQL DATA DEFINITION LANGUAGE COMMANDS
11. To Create a Table with Check Constraint:
The Check constraint is a rule in table that allows you to avoid those
tuples which doesn’t satisfy the given condition. A table can contain
any number of columns with check constraint.
Create Table <TableName>
(
ColName_1 Datatype (Size) check (Condition),
ColName_2 Datatype (Size) check (Condition),
.
ColName_N Datatype (Size) check (Condition)
); 39
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
check constraint.

40
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Student table created

in Test Database with
check constraint.

41
MYSQL DATA DEFINITION LANGUAGE COMMANDS
12. To Create a Table with Foreign Key Constraint:
The Foreign Key constraint is a non-key attribute whose values are
derived from the Primary Key of some other table. A table can have
many numbers of Foreign Keys on different columns.

A Foreign Key represents and association between two table’s

tuples. The table in which the Foreign Key is defined is called a
referencing table or child table. A table to which a Foreign Key
points is called a referenced table or parent table. To establish a
foreign key to a column in a table we should have primary key in
same other table with the same data type and size. A foreign key
column as a value can contains null, where as a primary key column
as a value will never contains a null. 42
MYSQL DATA DEFINITION LANGUAGE COMMANDS
Create Table <ParentTableName>
(
ColName_1 Datatype (Size) Primary Key,
.
ColName_N Datatype (Size)
);
Create Table <ForeignTableName>
(
ColName_1 Datatype (Size) Primary Key,
ColName_N Datatype (Size),
Foreign Key (Foreign Col) references <ParentTableName>
43
(Primary Col)
);
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks Table Created

with Foreign Key
Constraint

44
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks Table Can’t

Created with Foreign
Key Constraint

45
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks Table Created

with multiple Foreign
Keys Constraint

46
MYSQL DATA DEFINITION LANGUAGE COMMANDS
When we interlink two tables using primary Options Description
key and foreign key constraints, it associate CASCADE If Primary Key Column
the problem/ concept of referential integrity. A value is updated or
deleted then Foreign
referential integrity is a system of rules that a Key Column Value will
DBMS uses to ensure that relationships also gets updated or
deleted
between records in related tables are valid, SET NULL If Primary Key Column
and that users don’t accidentally delete or value is updated or
change the related data. This rule states that deleted then Foreign
Key Column Value will
when a primary key column’s value gets set to null
updated/changed or deleted than what NO If Primary Key Column
ACTION value is updated or
should be done for foreign key table records. deleted then Foreign
This problems of referential integrity can be Key Column Value will
remain as it is
solved at the time of creating a foreign table.
RESTRICT This will reject updating
At the time of creating foreign key we should 47
or deleting Primary Key
suffix the statement as ON DELETE and/or Column’ Value

UPDATE CASCADE or SET NULL or NO

MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks Table Created

with Foreign Key
Constraint and
referential integrity 48
MYSQL DATA DEFINITION LANGUAGE COMMANDS
In RDBMS using a foreign key concept a table can self reference
itself, it is known as self referential integrity.

Create Table <TableName>

(
ColName_1 Datatype (Size) Primary Key,
ColName_N Datatype (Size),
Foreign Key (Foreign Col) references <SameTableName>
(Primary Col)
);

49
MYSQL DATA DEFINITION LANGUAGE COMMANDS

emp table self-

referencing.

50
MYSQL DATA DEFINITION LANGUAGE COMMANDS
A foreign key can be named for a table, and the name applied to a
foreign key will be used to refer that foreign key, as well as it can also
be used to delete or modify the relationship.
Create Table <ForeignTableName>
(
ColName_1 Datatype (Size) Primary Key,
ColName_N Datatype (Size),
Constraint <Name> Foreign Key (Foreign Col) references
<SameTableName> (Primary Col)
);

51
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks table created

using Foreign Key
constraint Names.

52
MYSQL DATA DEFINITION LANGUAGE COMMANDS
13. To Modify the Structure of Table Using Alter Table Command:
The Alter Table command is used to modify the structure of existing
table. This command is used to ADD, MODIFY, CHANGE (Column
Name) and DELETE columns/ constraints of existing table.
To Add New Column
Alter Table <TN> ADD <NEW CN> <DataType> (Size) constraint First|
After Column Name;
To Change Column Data Type And Size
Alter Table <TN> MODIFY <OLD CN> <DataType>(Size) constraint;
To Change Name Of Column and Data Type And Size
Alter Table <TN> CHANGE <OLD CN> <NEW CN> <DataType>(Size)
constraint;
To Remove Column
Alter Table <TN> DROP <OLD CN>;
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table adds a

column namely rollno
in existing table.

54
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table adds a

column namely rollno
in existing table at the
beginning of the table.
55
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table adds a

column namely rollno
in existing table after
gender column.
56
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table adds a

column namely rollno
in existing table.

57
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Shows the structure of

a table.

58
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table command

modifies the name
column’s size from 50
to 80 characters.

59
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Shows the structure of

a table.

60
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table command

change the column
name from Name to
STUDENTNAME

61
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Shows the structure of

a table.

62
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table command

delete a column
namely contact no

63
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table Command

add a primary key
constraint to rollno
column

64
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table Command

deletes primary key
constraints

65
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Alter Table Command

deletes foreign key
constraint

66
MYSQL DATA DEFINITION LANGUAGE COMMANDS
14. To Remove / Delete a Table Command:
The Drop Table command is used to remove a table from the
database. Once a table is removed from the database it can not be
restored. A dropped table will be removed from the database along
with its structure and records. The SYNTAX of this command is as
follows. If a table being dropped has foreign table or it doesn’t exists
then MYSQL will report an error.

Drop Table <TableName>;

Or
67
Drop Table If Exists <TableName>;
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Marks table dropped. 68

MYSQL DATA DEFINITION LANGUAGE COMMANDS

Unable drop table a

Table Student
because, student has
foreign records.

69
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Unable drop table a

Table ABCD because it
doesn’t exists.

70
MYSQL DATA DEFINITION LANGUAGE COMMANDS
16. To Describe the Structure of a Table :
The Desc or Describe command used to view the structure of
existing able. The result of this command will show you the column
names, types, sizes and constraint used. The SYNTAX of this
command is as follows.

Desc <TableName>;

or

Describe <TableName>;
71
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Desc command shows

the structure of a table
ClassMaster

72
MYSQL DATA DEFINITION LANGUAGE COMMANDS

Describe command
shows the structure of
a table student

73
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
17. To Insert a record in a Table :
The Insert into command used to add a new record in a table. This
command accept the values of different columns and inserts a new
tuple in a table. There are two forms of insert into command, one
accepts the values of columns in the same order as the order of
columns in a table and second accept the column names and
corresponding values. In second form of insert into the order of the
column can be changed as well as we can omit values for some
columns. If we omit some columns from insert into command the null
or default value will be inserted. At the time of writing values the char,
varchar and date type values should be enclosed in single
quotes. The SYNTAXs of this command is as follows…
Insert into <TableName> Values (Val1, Val2,…, ValN); Or 74

Insert into <TableName>(Col1, Col2,…, ColN) Values (Val1, Val2,…, ValN);

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student Values(101,'Meeti Sheth',16,'F','1990-01-
02'); executed in MYSQL will be
record when

RELATION: Student
inserted

Roll No Name Age Gender DOB

This

101 Meeti Sheth 16 F 1990-01-02

75
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student Values('102', 'Rahul Raj', '17', 'M','1992-01-
02'); executed in MYSQL will be
record when

RELATION: Student
inserted

Roll No Name Age Gender DOB

This

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

76
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student Values(‘Mheg Raj', '18', 'M','1992-01-02');
executed in MYSQL will
not be inserted as number
when

of values not matching

number of columns.
record

RELATION: Student

Roll No Name Age Gender DOB

This

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

77
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student Values('103', NULL, ‘18', ‘F','1992-05-04');
executed in MYSQL will be
record when

RELATION: Student
inserted

Roll No Name Age Gender DOB

This

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

78
103 NULL 18 F 1992-05-04
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student (RollNo, Name, Age, Gender, DOB) Values
('104', ‘Heet Parmar', '18', ‘M','1998-10-09');
executed in MYSQL will be
when

RELATION: Student
record

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
inserted
This

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

104 Heet Parmar 18 M 1998-10-09

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student (Name, Age, Gender, DOB, RollNo ) Values
(‘Rajvi Patel', '17', ‘F','1997-09-09‘,'105');
executed in MYSQL will be
when

RELATION: Student
record

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
inserted
This

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

104 Heet Parmar 18 M 1998-10-09

105 Rajvi Patel 17 F 1997-10-09

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
Insert Into Student (Name, Age, Gender, DOB) Values (‘Radha
Patel', '17', ‘F','1996-06-09‘);
executed in MYSQL will be
when

RELATION: Student

Roll No Name Age Gender DOB

record

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

inserted

103 NULL 18 F 1992-05-04

This

104 Heet Parmar 18 M 1998-10-09

105 Rajvi Patel 17 F 1997-10-09

NULL Radha Patel 17 F 1996-06-09

MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Inserted two tuples in

student table.

82
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Inserted two tuples in

student table.

83
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

After inserting tuples

can be seen using
select command

84
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Null value can

specified to a column
using null keyword in
values list.
85
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
17. To Update/ Modify a record in a Table :
The Update command used to modify the values of existing record(s)
in a table. This command accept new values of different columns and
replaces it in a all/selected tuples. This command can use a WHERE
CLAUSE to specify the condition. If a condition is specified then only
those tuples will be updated otherwise all tuples will be updated. The
SYNTAX of update command is as follows…
If a

Update <TableName> Set Col=value, Col =Value, … ,Col =Value;

Or

Update <TableName> Set Col=value, Col =Value, … ,Col =Value Where

86

Condition1 and/ or Contition2 and/ or ConditionN;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti
XYZSheth 16 F 1990-01-02

102 Rahul
XYZRaj 17 M 1992-01-02

103 NULL
XYZ 18 F 1992-05-04
This command
104 HeetXYZ
Parmar 18 M 1998-10-09 will change all
values of name
105 Rajvi
XYZ Patel 17 F 1997-10-09 columns as XYZ
NULL Radha
XYZPatel 17 F 1996-06-09

Update Student Set Name=‘XYZ';

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 HeetXYZ
Parmar 18 M 1998-10-09 will change value
of name columns
105 Rajvi Patel 17 F 1997-10-09 as XYZ whose
NULL Radha Patel 17 F 1996-06-09
roll number is
104

Update Student Set Name=‘XYZ' Where RollNo=104;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar
NULL 18 M 1998-10-09 will change value
of name columns
105 Rajvi Patel 17 F 1997-10-09 as NULL whose
NULL Radha Patel 17 F 1996-06-09
roll number is
104

Update Student Set Name= NULL Where RollNo=104;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 HeetXYZ
Parmar 18 M 1998-10-09 will change value
of name columns
105 Rajvi
XYZ Patel 17 F 1997-10-09 as XYZ whose
NULL Radha Patel 17 F 1996-06-09
roll number is
104 or 105

Update Student Set Name=‘XYZ' Where RollNo=104 Or

RollNo=105;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will change value
of name columns
105 Rajvi Patel 17 F 1997-10-09 as XYZ whose
NULL Radha Patel 17 F 1996-06-09
roll number is
104 and 105 both

Update Student Set Name=‘XYZ' Where RollNo=104 And

RollNo=105;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 HeetXYZ
Parmar 18 M 1998-10-09 will change value
of name columns
105 Rajvi
XYZ Patel 17 F 1997-10-09 as XYZ whose
NULL Radha Patel 17 F 1996-06-09
roll number is
greater then 103

Update Student Set Name=‘XYZ' Where RollNo>103;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will change value
of rollno column
105 Rajvi Patel 17 F 1997-10-09 as 106 whose roll
NULL
106 Radha Patel 17 F 1996-06-09
number is NULL

Update Student Set RollNo=‘106' Where RollNo is NULL;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will change value
of rollno column
105 Rajvi Patel 17 F 1997-10-09 as 106 and name
NULL
106 Radha
Rekha Patel 17 F 1996-06-09
column as XYZ
whose rollno is
NULL

Update Student Set RollNo= '106‘, Name= ‘Rekha Patel' Where

RollNo is NULL;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16
17 F 1990-01-02

102 Rahul Raj 17

18 M 1992-01-02

103 NULL 18
19 F 1992-05-04
This command
104 Heet Parmar 18
19 M 1998-10-09 will change all
values of Age
105 Rajvi Patel 17
18 F 1997-10-09 column by
NULL Radha Patel 17
18 F 1996-06-09
increasing 1.

Update Student Set Age= Age+1;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This update command

updated only two 96
columns values but it
will update all rows.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This update command

update only one
column i.e. increasing
value of a column by 97 5
but it will update all
rows.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This update command

update only one
column i.e. change of98
gender but it will
selected tuples.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
18. To Delete/ Remove a record in a Table :
The Delete command used to remove existing record(s) in a table.
This command can use a WHERE CLAUSE to specify the condition.
If a condition is specified then only those tuples will be removed
otherwise all tuples will be removed. The SYNTAX of update
command is as follows…
If a

Delete From <TableName>;

Or

Delete From <TableName> Where Condition1 and/ or Contition2 and/99 or

ConditionN;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will remove all
records (tuples)
105 Rajvi Patel 17 F 1997-10-09 of table (relation)
NULL Radha Patel 17 F 1996-06-09
Student

Delete From Student;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104
105 Heet
RajviParmar
Patel 18
17 M
F 1998-10-09
1997-10-09 will remove only
those records
NULL
105 Radha
Rajvi Patel
Patel 17 F 1997-10-09
1996-06-09 (tuples) of table
NULL Radha Patel 17 F 1996-06-09
(relation) student
whose rollno is
104

Delete From Student Where RollNo=104;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
NULL
104 Heet
RadhaParmar
Patel 18
17 M
F 1998-10-09
1996-06-09 will remove only
those records
105 Rajvi Patel 17 F 1997-10-09 (tuples) of table
NULL Radha Patel 17 F 1996-06-09
(relation) student
whose rollno is
104 or 105

Delete From Student Where RollNo=104 Or RollNo=105;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will remove only
those records
105 Rajvi Patel 17 F 1997-10-09 (tuples) of table
NULL Radha Patel 17 F 1996-06-09
(relation) student
whose rollno is
104 and also 105

Delete From Student Where RollNo=104 And RollNo=105;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
NULL
104 Heet
RadhaParmar
Patel 18
17 M
F 1998-10-09
1996-06-09 will change value
of name columns
105 Rajvi Patel 17 F 1997-10-09 as XYZ whose
NULL Radha Patel 17 F 1996-06-09
roll number is
greater then 103

Delete From Student Where RollNo>103;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04

This command
104 Heet Parmar 18 M 1998-10-09 will remove only
those records
105 Rajvi Patel 17 F 1997-10-09 (tuples) of table
NULL Radha Patel 17 F 1996-06-09
(relation) student
whose rollno is
NULL

Delete From Student Where RollNo is NULL;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02

102
103 Rahul
NULL Raj 17
18 M
F 1992-01-02
1992-05-04

103
104 Heet Parmar
NULL 18 M
F 1992-05-04
1998-10-09
This command
104 Heet Parmar 18 M 1998-10-09 will remove only
those records
105 Rajvi Patel 17 F 1997-10-09 (tuples) of table
NULL Radha Patel 17 F 1996-06-09
(relation) student
whose Name
starts with R

Delete From Student Where Name like ‘R%’;

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
RELATION: Student

Roll No Name Age Gender DOB

101
103 Meeti
NULLSheth 16
18 F 1990-01-02
1992-05-04

102
104 Heet
RahulParmar
Raj 17
18 M 1992-01-02
1998-10-09

103
105 Rajvi
NULL Patel 18
17 F 1992-05-04
1997-10-09 This command
NULL
104 Heet
RadhaParmar
Patel 18
17 M
F 1998-10-09
1996-06-09
will remove only
those records
105 Rajvi Patel 17 F 1997-10-09 (tuples) of table
(relation) student
NULL Radha Patel 17 F 1996-06-09 whose DOB is
before 3rd May
1992.
Delete From Student Where DOB < ‘1992-05-03’;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This delete command

removes all records 108
of
a table
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This delete command

removes specific 109
records of a table
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This delete command

removes specific 110
records of a table
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19. To Select Records from a Table :
The Select command used to select existing records from a table and
displays it on output screen. This command can retrieve all records
and columns or it can retrieve all or few columns and records. This
select command offer four CLAUSES namely WHERE, GROUP BY,
HAVING, and ORDER BY. This CLAUSES helps the select command
to filter the record set. The WHERE CLAUSE used to specify the
conditions, if a record from a table satisfy the condition the that
record will be consider for result set. The GROUP BY CLAUSE
accepts one or more than one columns and group the records based
on specified column. The HAVING CLAUSE used to specify the
condition that can filer the returning GROUP or records. The ORDER
BY clause can accept one or more than one columns of a table and
111
arrange the result set returned by Select command in either
ASCENDING or DESCENDING order. The SYNTAX of Select
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

CLAUSES Select * or Col, Col, Col,…,Col From <TableName>

are
optional,
in case if Where Condition and/ or Condition and/ or Condition…
one of the
CLAUSE if
not
Group By Col, Col, Col,…,Col
specified
then its Having Condition and/ or Condition and/ or Condition…
effect will
not be
displayed.
Order By Col, Col, Col,…,Col In Select command GROUP BY CLAUSE
is then and only then if we wish we can
use HAVING CLAUSE. A HAVING
CLAUSE should be used when GROUP
BY CLAUSE is specified. When we select
command we should make sure that the
CLAUSES are used in the above
112
mentioned order.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Order Of Clauses
In
SELECT Command

113
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-1. To Select All Column and All Rows from a Table :
To select all column and all rows from a table we should use Select
command in following format…

Select * from <TableName>;

Or

Select Col,Col,Col,…,Col from <TableName>;

114
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT;
NULL Radha Patel 17 F 1996-06-09

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F
115
1997-10-09
NULL Radha Patel 17 F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

all columns in the116
same order as it is
located in table.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT ROLLNO, NAME,
NULL Radha Patel 17 F 1996-06-09
AGE, GENDER, DOB FROM
STUDENT;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F
117
1997-10-09
NULL Radha Patel 17 F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

all columns in the 118
same order as it is
located in table.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-2. To Select Specific Columns and All Rows from a Table :
To select specific columns and all rows from a table we should use
Select command in following format…

Select Col,Col,Col,…,Col from <TableName>;

119
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT ROLLNO, NAME,
NULL Radha Patel 17 F 1996-06-09
AGE FROM STUDENT;

Roll No Name Age

101 Meeti Sheth 16
102 Rahul Raj 17
103 NULL 18
104 Heet Parmar 18
105 Rajvi Patel 17
120

NULL Radha Patel 17

MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

first three columns in
the same order as it 121
is
located in table.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT GENDER, DOB
NULL Radha Patel 17 F 1996-06-09
FROM STUDENT;

Gender DOB
F 1990-01-02
M 1992-01-02
F 1992-05-04
M 1998-10-09
F 1997-10-09
122

F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

last four columns in
the same order as it is 123
located in table.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT NAME, ROLLNO,
NULL Radha Patel 17 F 1996-06-09
AGE, GENDER, DOB FROM
STUDENT;

Name Roll No Age Gender DOB

Meeti Sheth 101 16 F 1990-01-02
Rahul Raj 102 17 M 1992-01-02
NULL 103 18 F 1992-05-04
Heet Parmar 104 18 M 1998-10-09
Rajvi Patel 105 17 F
124
1997-10-09
Radha Patel NULL 17 F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

columns in any order
i.e. this command 125
reordering columns for
the out put.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-3. To Eliminate duplicate Data from column of a Table :
To Eliminate duplicate values from a column of a table we should use
distinct keyword before column name. This is as follows…

Select Distinct ColName from <TableName>;

126
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT GENDER FROM
NULL Radha Patel 17 F 1996-06-09
STUDENT;

Gender
F
M
F
M
F
127

F
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

deptno column from
empno table. This deptno128
column will also display
duplicate values.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT DISTINCT (GENDER)
NULL Radha Patel 17 F 1996-06-09
FROM STUDENT;

Gender
F
M

129
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

deptno column from
empno table. This deptno
column will display 130
duplicate values at once...
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT DISTINCT(*) FROM
NULL Radha Patel 17 F 1996-06-09
STUDENT;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F
131
1997-10-09
NULL Radha Patel 17 F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command selects all

columns and rows, 132
duplicate record will be
displayed once.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT DISTINCT NAME,
NULL Radha Patel 17 F 1996-06-09
AGE FROM STUDENT;

Name Age
Meeti Sheth 16
Rahul Raj 17
NULL 18
Heet Parmar 18
Rajvi Patel 17
133

Radha Patel 17
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command will

134
perform distinct operation
on fname column, not on
lname column
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT DISTINCT NAME,
NULL Radha Patel 17 F 1996-06-09
DISTINCT GENDER FROM
STUDENT;

135
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Distinct keyword can not

be applied on two
different columns.

136
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-4. To Select Specific / All Columns but Selected Rows from a Table
:
To select specific/ all columns and specific rows then our select
command should use WHERE CLAUSE and a condition in following
format…

Select * or Col, Col, Col,…,Col from <TableName> Where Condition

and/ or Condition and/ or Condition;

A WHERE CLAUSE is used to specify conditions, a condition can be

formed using any relational operator i.e. =, !=, <=, >=, > & <. If a
WHERE CALUSE contains more than one condition then it should be
concatenated using logical operators like AND(&&), OR(||) 137 and
NOT(!).
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE GENDER=‘F’;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
138
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select all

columns but only those139
rows which satisfy the
condition.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT NAME, AGE,
NULL Radha Patel 17 F 1996-06-09
GENDER FROM STUDENT
WHERE GENDER=‘F’;

Name Age Gender

Meeti Sheth 16 F
NULL 18 F
Rajvi Patel 17 F
Radha Patel 17 F
140
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select

141
specific columns and only
those rows which satisfy
the condition.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE>=16 and
AGE<=17;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
142
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select all

columns but only those
rows which satisfy the
143
condition.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NOT (AGE>=16 AND
AGE<=17);

Roll No Name Age Gender DOB

103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

144
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This command select all

columns but only those145
rows which satisfy the
condition.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE=16 AND
AGE=17;

146
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

this statement will not

give any result as empid
cell can not contain both
values at a time.

147
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE=16 OR AGE=18;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

148
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This statement will return

the record for a result set
when a record satisfies
the condition. 149
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE (AGE>=16 AND
AGE<17) OR GENDER=‘F’;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
150
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

This statement will return

the record for a result set151
when a record satisfies
the condition.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-5. Performing Simple Calculations on a Column(s) of a Table:
To perform simple calculations on a column of a table we should use
arithmetic operators i.e. +, -, * or /. When a column name is used with
arithmetic operators it is referred to as scalar expressions.

152
 RELATION: Student
Roll No Name Age Gender FEES
101 Meeti Sheth 16 F 4000
102 Rahul Raj 17 M 5000
103 NULL 18 F 6000
104 Heet Parmar 18 M 5000
105 Rajvi Patel 17 F 2000
SELECT FEES, FEES*0.15
NULL Radha Patel 17 F 9000
FROM STUDENT;

FEES FEES * 0.15

4000 600

5000 750

6000 900

5000 750

2000 300 153

9000 1350
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

15% of salary (Scalar 154

Expression)
 RELATION: Student
Roll No Name Age Gender FEES
101 Meeti Sheth 16 F 4000
102 Rahul Raj 17 M 5000
103 NULL 18 F 6000
104 Heet Parmar 18 M 5000
105 Rajvi Patel 17 F 2000
SELECT FEES, FEES/30
NULL Radha Patel 17 F 9000
FROM STUDENT;

FEES FEES/30
4000 133.33
5000 166.66
6000 200.00
5000 166.55
2000 66.66
155

9000 300.00
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Daily Salary 156

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-6. Renaming a column for output :
A column name can be changed for the output purpose using select
statement. This can be achieved using Column Name as ‘New
Column Name’ in select command. This concept of renaming a
column for output is known as Column Alias.

157
 RELATION: Student
Roll No Name Age Gender FEES
101 Meeti Sheth 16 F 4000
102 Rahul Raj 17 M 5000
103 NULL 18 F 6000
104 Heet Parmar 18 M 5000
105 Rajvi Patel 17 F 2000
SELECT FEES, FEES/30 AS
NULL Radha Patel 17 F 9000
‘DAILY FEES’ FROM
STUDENT;

FEES DAILY FEES

4000 133.33
5000 166.66
6000 200.00
5000 166.55
2000 66.66
158

9000 300.00
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Scalar Expression is 159

renamed as Daily Salary
 RELATION: Student
Roll No Name Age Gender FEES
101 Meeti Sheth 16 F 4000
102 Rahul Raj 17 M 5000
103 NULL 18 F 6000
104 Heet Parmar 18 M 5000
105 Rajvi Patel 17 F 2000
SELECT FEES, FEES*0.15 AS
NULL Radha Patel 17 F NULL
‘15% FEES’ FROM STUDENT;

FEES 15% FEES

4000 600

5000 750

6000 900

5000 750

2000 300 160

NULL NULL
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Scalar Expression is
renamed as 15% OF 161
Salary
 RELATION: Student
Roll No Name Age Gender FEES
101 Meeti Sheth 16 F 4000
102 Rahul Raj 17 M 5000
103 NULL 18 F 6000
104 Heet Parmar 18 M 5000
105 Rajvi Patel 17 F 2000
SELECT NAME AS ‘STUDENT
NULL Radha Patel 17 F 9000
NAME’ , AGE AS ‘STUDENT
AGE’ FROM STUDENT;

STUDENT NAME STUDENT AGE

Meeti Sheth 16
Rahul Raj 17
NULL 18
Heet Parmar 18
Rajvi Patel 17
162

Radha Patel 17
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Columns renamed for out

put as First Name and 163
Last Name
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-7. Putting Text in Query:
A text literal can be put for each row in result set by enclosing text in
single quotation marks. The SYNTAX to put a text in query is as
follows…

Select Col, ‘---’, COL from <TABLENAME>;

Select Col, ‘---’, COL, ‘%’, from <TABLENAME>;

164
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT NAME, ’HAS’ , AGE
NULL Radha Patel 17 F 1996-06-09
FROM STUDENT;

Name HAS Age

Meeti Sheth HAS 16
Rahul Raj HAS 17
NULL HAS 18
Heet Parmar HAS 18
Rajvi Patel HAS 17
165

Radha Patel HAS 17

MYSQL DATA MANIPULATION LANGUAGE COMMANDS

‘IS’ is a text literal will be

displayed for each row i.e. 166
as a column.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT NAME, AGE,’ YRS’
NULL Radha Patel 17 F 1996-06-09
FROM STUDENT;

Name Age YRS

Meeti Sheth 16 YRS
Rahul Raj 17 YRS
NULL 18 YRS
Heet Parmar 18 YRS
Rajvi Patel 17 YRS
167

Radha Patel 17 YRS

MYSQL DATA MANIPULATION LANGUAGE COMMANDS

‘$’ is a text literal will be

displayed for each row i.e. 168
as a column.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT NAME, ‘AGE’,’YRS’
NULL Radha Patel 17 F 1996-06-09
FROM STUDENT;

Name AGE YRS

Meeti Sheth AGE YRS
Rahul Raj AGE YRS
NULL AGE YRS
Heet Parmar AGE YRS
Rajvi Patel AGE YRS
169

Radha Patel AGE YRS

MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-8. A condition based on a Range:
The BETWEEN…AND keywords are used to specify the range, if a
row satisfy the condition i.e. the value falls in the given range
(including both numbers) then that row will be selected for output. The
BETWEEN…AND keywords working then columns which has data
type as either numeric or date.

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col

between Start_Value and End_Value;

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col

170
NOT between Start_Value and End_Value;
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE BETWEEN 16
AND 17;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
171
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Range search based on172

salary.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE >=16 AND
AGE<=17;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
173
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Range search based on

174
salary. This a replacement
of between…and
keywords.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE NOT BETWEEN
16 AND 17;

Roll No Name Age Gender DOB

103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

175
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Opposite range search

based on salary by
prefixing NOT operator.

176
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-8. A condition based on a List of Values:
The IN keyword is used to specify the list of values, from which if
record match a value then it will be considered form output. This
operator works for any data types i.e. either number, date, time or
string.

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col In

(Val1,Val2,…,ValN);

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col

NOT In (Val1,Val2,…,ValN);
177
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE IN (16,18 );

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

178
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Search based on List of

EMPID

179
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE = 16 OR
AGE=18 );

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

180
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Replacement to In
keyword.

181
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE AGE NOT IN (16,18 );

Roll No Name Age Gender DOB

102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09

182
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

183
Displays the list of record
which is not in a list.
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE GENDER IN (‘F’);

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
184
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the list of record

which is in a list.

185
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-8. Matching a Pattern for a String:
The LIKE keyword is used to match pattern for a string type column.
The LIKE keyword offers two wild card characters namely % and _.
The % can matches zero or more then zero characters(any length of
characters) underscore matches any character. This pattern
matching using LIKE operator is useful in matching string patterns
like starting with specific letter(s), ends with specific letter(s), contains
specific letters(s).

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col

LIKE ‘PATTERN’;

Select * or Col, Col, Col,…,Col from <TABLENAME> Where 186

Col
NOT LIKE ‘PATTERN’;
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME LIKE ‘R%’;

Roll No Name Age Gender DOB

102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09

187
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname start with J.

188
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME NOT LIKE
‘R%’;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09

189
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname doesn’t start 190
with J.
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME LIKE ‘%L’;

Roll No Name Age Gender DOB

105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09

191
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname ends with Y.

192
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME LIKE ‘%L%;

Roll No Name Age Gender DOB

102 Rahul Raj 17 M 1992-01-02
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09

193
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname contains any
occurrence of L at any
194
position.
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME LIKE ‘_ _ E%’;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
104 Heet Parmar 18 M 1998-10-09

195
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname contains 4th
character as N.
196
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meet Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME LIKE ‘_ _ _T’;

Roll No Name Age Gender DOB

104 Heet 18 M 1998-10-09

197
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which fname is of 4
character long and last
character is N.

198
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-9. Searching For Nulls:
The IS keyword is used to match the null (Empty Cells) values. As a
null is not a value, it can not be compared using = sign. Therefore,
the IS keyword is used to match the null values.

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col IS

NULL;

Select * or Col, Col, Col,…,Col from <TABLENAME> Where Col IS

NOT NULL;
199
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME IS NULL;

Roll No Name Age Gender DOB

103 NULL 18 F 1992-05-04

200
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays a list of record in

which ssn is NULL

201
 RELATION:
Roll No NameStudent
Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
WHERE NAME IS NOT NULL;

Roll No Name Age Gender DOB

101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F
202
1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

203
Displays a list of record in
which ssn is not NULL
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-10. Sorting Result Set:
The Select command in MYSQL will return the RESULT SET. The
records in the RESULT SET can be arranged in ascending or
descending order using ORDER BY CLAUSE. This CALUSE accept
one or two column names and sort the RESULT SET in ascending
order by default. If we want to arrange the RESULT SET in
descending order then we need to suffix the keyword desc. If we
specify only one column in ORDER BY CALUSE it is known as
SINGLE SORTING, and if we specify multiple columns then it is
referred to as MULTIPLE SORTING or NESTED SORTING.

Select * or Col, Col, Col,…,Col from <TABLENAME> Where

204
Conditions Order By Col, Col;
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY ROLLNO;

SINGLE SORTING Roll No Name Age Gender DOB

ON ROLLNO 101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
COLUMN IN 103 NULL 18 F 1992-05-04

ASCENDING 104
105
Heet Parmar
Rajvi Patel
18
17
M
F
1998-10-09
205
1997-10-09

ORDER NULL Radha Patel 17 F 1996-06-09

 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY ROLLNO ASC;

SINGLE SORTING
Roll No Name Age Gender DOB
ON 101 Meeti Sheth 16 F 1990-01-02

ROLLNO COLUMN 102 Rahul Raj 17 M 1992-01-02

103 NULL 18 F 1992-05-04
IN ASCENDING 104 Heet Parmar 18 M 1998-10-09
206
ORDER 105 Rajvi Patel 17 F 1997-10-09
NULL Radha Patel 17 F 1996-06-09
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY ROLLNO DESC;

SINGLE SORTING
ON
Roll No Name Age Gender DOB
ROLLNO COLUMN 105 Rajvi Patel 17 F 1997-10-09
104 Heet Parmar 18 M 1998-10-09
IN 103 NULL 18 F 1992-05-04

DESCENDING 102
101
Rahul Raj
Meeti Sheth
17
16
M
F
1992-01-02
207
1990-01-02
ORDER NULL Radha Patel 17 F 1996-06-09
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY NAME DESC;

SINGLE SORTING
Roll No Name Age Gender DOB
ON 105 Rajvi Patel 17 F 1997-10-09

NAME COLUMN IN 102 Rahul Raj 17 M 1992-01-02

NULL Radha Patel 17 F 1996-06-09
DESCENDING 101 Meeti Sheth 16 F 1990-01-02
104 Heet Parmar 18 M
208
1998-10-09
ORDER 103 NULL 18 F 1992-05-04
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY GENDER,
ROLLNO;
MULTIPLE SORTING
/ NESTED SORTING Roll No Name Age Gender DOB
ON GENDER AND 101 Meeti Sheth 16 F 1990-01-02
103 NULL 18 F 1992-05-04
ROLLNO COLUMNS 105 Rajvi Patel 17 F 1997-10-09

IN ASCENDING NULL Radha Patel 17 F 1996-06-09

209
102 Rahul Raj 17 M 1992-01-02
ORDER 104 Heet Parmar 18 M 1998-10-09
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY GENDER,
ROLLNO DESC;
MULTIPLE SORTING /
NESTED SORTING ON
GENDER AND ROLLNO Roll No Name Age Gender DOB
COLUMNS IN ASCENDING 105 Rajvi Patel 17 F 1997-10-09

ORDER OF GENDER AND 103 NULL 18 F 1992-05-04

101 Meeti Sheth 16 F 1990-01-02
DSCENDING ORDER OF
NULL Radha Patel 17 F 1996-06-09
ROLLNO 210
104 Heet Parmar 18 M 1998-10-09
102 Rahul Raj 17 M 1992-01-02
 RELATION: Student
Roll No Name Age Gender DOB
101 Meeti Sheth 16 F 1990-01-02
102 Rahul Raj 17 M 1992-01-02
103 NULL 18 F 1992-05-04
104 Heet Parmar 18 M 1998-10-09
105 Rajvi Patel 17 F 1997-10-09
SELECT * FROM STUDENT
NULL Radha Patel 17 F 1996-06-09
ORDER BY GENDER DESC,
ROLLNO DESC;
MULTIPLE SORTING /
NESTED SORTING ON Roll No Name Age Gender DOB
GENDER AND ROLLNO 104 Heet Parmar 18 M 1998-10-09
COLUMNS IN DESCENDING 102 Rahul Raj 17 M 1992-01-02

ORDER OF GENDER AND 105 Rajvi Patel 17 F 1997-10-09

DSCENDING ORDER OF 103 NULL 18 F 1992-05-04

101 Meeti Sheth 16 F
211
1990-01-02
ROLLNO
NULL Radha Patel 17 F 1996-06-09
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Default order of records212

of the table.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Ascending Order of 213
fname.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Descending Order of 214
fname.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Ascending Order of
fname.
215
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Ascending Order of
initially with lname when216
two lname are same then
by fname.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Ascending Order of
initially with lname when
217
two lname are same then
in Descending order of
fname.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Arranging Result Set In

Ascending Order of 218
LASTNAME i.e. column
alias.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
19-10. Grouping Result:
The GROUP BY clause in Select command is used to combine those
records that have identical values in a particular column or a group of
column. If aggregate functions are used with group by clause
then this function will display summery. This operation if used then
divides the table into number of groups for a RESULT SET. The
group by clause works on column name(s), a column with group by
clause can be single or multiple. If multiple columns are used in
group by clause then it is known as ‘Nested Grouping’. In case of
multiple grouping 1st column determines high level group, 2nd column
determines second level group and so on. The RESULT set returned
by GROUP BY clause can be filtered by HAVING CLAUSE. The
HAVING CLAUSE may contain more than one conditions and it
219
shows only desired groups from RESULT SET.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
SELECT DEPARTMENT_ID, SUM(SALARY) FROM EMP GROUP BY DEPARTMENT_ID;

220
MYSQL DATA MANIPULATION LANGUAGE COMMANDS
SELECT DEPARTMENT_ID, SUM(SALARY) FROM EMP GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID<40;

221
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Original tuples of emp 222

table.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays job column from

emp table. 223
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays job column

grouping by job from emp
table. This is similar to
distinct job but not
identical to it.
224
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

When aggregate function

are used then result will
be summery of records.

225
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Nested grouping, i.e

group on multiple 226
columns.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

When aggregate function

are used then result will227
be summery of records.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

When aggregate function

are used then result will
be summery of records.
228
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

When group by clause works

displays all columns and
works only one column then it
groups by records given 229
column and displays different
values for all other columns .
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

When group by clause works

displays all columns and
works only one column then it
groups by records given
column and displays different 230
values for all other columns .
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Having clause will display

only those group which 231
satisfy the given condition.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Having clause will display

only those group which 232
satisfy the given condition.
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Having clause will display

only those group which
satisfy the given condition.

233
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

If select command contains all

clauses then the order of
clauses will be as WHERE,
GROUP BY, HAVING and
ORDER BY

234
235
236
 MYSQL’S

BUILT-IN-FUNCTIONS
 MYSQL BUILT IN FUNCTIONS
A Function is a special type of predefined command set that per-
forms some operation and returns a single or multiple value(s). In MYSQL
the functions are categorized as follows…

MYSQL
Functions

Multiple
Single Row
Row (Group/
(Scalar) 238
Aggregate)
 MYSQL BUILT IN FUNCTIONS
A. Group/ Aggregate/ Multiple Row Functions:
In MYSQL, This types of functions accepts a column, i.e. multiple
rows (values) and return the summery of multiple value i.e.
aggregated value from multiple rows. All aggregate functions
generally works for numeric type only. This includes the following
functions…

Aggregate
Functions

SUM() MIN() MAX() AVG() COUNT() 239

COUNT(*)
 MYSQL BUILT IN FUNCTIONS
A.1 SUM() Function:
This function accept a column of a table and return the addition of all
values of a column. If in a query a where condition is used then only
selected values of a column will be added and returned. The
SYNTAX to use this function is as follows…

SELECT SUM(DISTINCT or ALL COL) from <TABLENAME> Where

Condition(s);

240
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

SUM(FEES)
SELECT SUM(FEES) FROM STUDENT;
21500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

SUM(ALL FEES)
SELECT SUM(ALL FEES) FROM STUDENT;
21500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

SUM(DISTINCT FEES)
SELECT SUM(DISTINCT FEES) FROM STUDENT;
16500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

SUM(DISTINCT SUM(ALL
SELECT SUM(DISTINCT FEES), SUM(ALL FEES) FEES) FEES)
FROM STUDENT; 16500 21500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

SUM(DISTINCT SUM(ALL
SELECT SUM(DISTINCT FEES), SUM(ALL FEES) FEES) FEES)
FROM STUDENT WHERE AGE<=17; 10500 10500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AGE SUM(FEES)
SELECT AGE, SUM(FEES) FROM STUDENT
16 4000
GROUP BY AGE;
17 6500
18 11000
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the addition of

all values of salary
column from emp table.

247
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the addition of

distinct values of salary
column from emp table.

248
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the addition of

selected values of salary
column from emp table.

249
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the addition of

15% of salary of selected
values of salary column
of emp table.

250
 MYSQL BUILT IN FUNCTIONS
A.2 MAX() Function:
This function accept the column of a table and return the maximum of
all values of a column. If in a query a where condition is used then
only selected values of a column will be will be considered and
maximum will be returned. This functions works with numeric and
date time columns, for a date time column it returns the most recent
date time as MAX date e.g. if we apply this functions on today’s date
and yesterday’s date then it returns today’s date. The SYNTAX to use
this function is as follows…

SELECT MAX(DISTINCT or ALL COL) FROM <TABLENAME>

WHERE Condition;
251
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MAX(FEES)
SELECT MAX(FEES) FROM STUDENT;
6000
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MAX(DOB)
SELECT MAX(DOB) FROM STUDENT;
1998-10-09
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MAX(DISTINCT FEES)
SELECT MAX(DISTINCT FEES) FROM STUDENT;
6000
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MAX(FEES) MAX(DOB)
SELECT MAX(FEES), MAX(DOB) FROM STUDENT;
6000 1998-10-09
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MAX(FEES) MAX(DOB)
SELECT MAX(FEES), MAX(DOB) FROM STUDENT
5000 1997-10-09
WHERE AGE<=17;
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AGE MAX(FEES)
SELECT AGE, MAX(FEES) FROM STUDENT
16 4000
GROUP BY AGE;
17 5000
18 6000
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the maximum of

all values of salary
column from emp table.

258
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the maximum of

distinct values of salary
column from emp table.

259
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the maximum of

selected values of salary
column from emp table.

260
 MYSQL BUILT IN FUNCTIONS
A.3 MIN() Function:
This function accept the column of a table and return the minimum of
all values of a column. If in a query a where condition is used then
only selected values of a column will be will be considered and
minimum will be returned. This functions works with numeric and
date time columns, for a date time column it returns the oldest date
time as MIN date e.g. if we apply this functions on today’s date and
yesterday’s date then it returns yesterday date. The SYNTAX to use
this function is as follows…

SELECT MIN(DISTINCT or ALL COL) FROM <TABLENAME>

WHERE Condition(s);
261
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MIN(FEES)
SELECT MIN(FEES) FROM STUDENT;
1500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MIN(DOB)
SELECT MIN(DOB) FROM STUDENT;
1990-01-02
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MIN(DISTINCT FEES)
SELECT MIN(DISTINCT FEES) FROM STUDENT;
1500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MIN(FEES) MIN(DOB)
SELECT MIN(FEES), MIN(DOB) FROM STUDENT;
1500 1990-01-02
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

MIN(FEES) MIN(DOB)
SELECT MIN(FEES), MIN(DOB) FROM STUDENT
1500 1990-01-02
WHERE AGE<=17;
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AGE MIN(FEES)
SELECT AGE, MIN(FEES) FROM STUDENT GROUP
16 4000
BY AGE;
17 1500
18 5000
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the minimum of

all values of salary
column from emp table.

268
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the minimum of

distinct values of salary
column from emp table.

269
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the minimum of

selected values of salary
column from emp table.

270
 MYSQL BUILT IN FUNCTIONS
A.4 AVG() Function:
This function accept the column of a table and return the
average(mean) of all values of a column. An average of values will be
calculated as addition of values divided by total number of values. If
in a query a where condition is used then only selected values of a
column will be will be considered and average will be returned. This
function will ignore NULL cells i.e. if a columns contains 4 values out
of which one value is NULL then average will be calculated as
addition of 3 values divided by 3. The SYNTAX to use this function is
as follows…

SELECT AVG(DISTINCT or ALL COL) FROM <TABLENAME>

WHERE Condition(s); 271
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AVG(FEES)
SELECT AVG(FEES) FROM STUDENT;
4300
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AVG(ALL FEES)
SELECT AVG(ALL FEES) FROM STUDENT;
4300
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AVG(DISTINCT FEES)
SELECT AVG(DISTINCT FEES) FROM STUDENT;
4125
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AVG(DISTINCT AVG(ALL
SELECT AVG(DISTINCT FEES), AVG(ALL FEES) FEES) FEES)
FROM STUDENT; 4125 4300
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AVG(DISTINCT AVG(ALL
SELECT AVG(DISTINCT FEES), AVG(ALL FEES) FEES) FEES)
FROM STUDENT WHERE AGE<=17; 3500 3500
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AGE AVG(FEES)
SELECT AGE, AVG(FEES) FROM STUDENT GROUP
16 4000
BY AGE;
17 3250
18 5500
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the average of

all values of salary
column from emp table.

278
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the average of

distinct values of salary
column from emp table.

279
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays the average of

selected values of salary
column from emp table.

280
 MYSQL BUILT IN FUNCTIONS
A.5 Count() Function:
This function accept the column of a table and return the total
number of cells contains value(s) other than NULL. If in a query a
where condition is used then only selected values of a column will be
will be considered and count of values will be returned. For e.g. if a
column contains 4 values out of which 2 values are NULL then this
function returns 2 as result. This function will ignore NULL cells and
count. The SYNTAX to use this function is as follows…

SELECT COUNT(DISTINCT or ALL COL) FROM <TABLENAME>

WHERE Condition(s);

281
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

COUNT(FEES)
SELECT COUNT(FEES) FROM STUDENT;
5
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

COUNT(ALL FEES)
SELECT COUNT(ALL FEES) FROM STUDENT;
5
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

COUNT(DISTINCT FEES)
SELECT COUNT(DISTINCT FEES) FROM
4
STUDENT;
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

AGE COUNT (FEES)

SELECT AGE, COUNT(FEES) FROM STUDENT
16 1
GROUP BY AGE;
17 2
18 2
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays total cells

contains values for ssn
column of emp table.

286
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays total cells

contains values for ssn
column of emp table.
Distinct values only.

287
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays total cells

contains values for ssn
column of emp table.
Distinct and selected
values only.

288
 MYSQL BUILT IN FUNCTIONS
A.5 Count(*) Function:
This function will count and return the total numbers of rows
available in a table. It has nothing to with NULL values stored in
table. The SYNTAX to use this function is as follows…

SELECT COUNT(*) FROM <TABLENAME> WHERE Condition(s);

289
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

COUNT(*)
SELECT COUNT(*) FROM STUDENT;
6
 MYSQL BUILT IN FUNCTIONS
RELATION: STUDENT
Roll No Name Age Gender DOB Fees
101 Meeti Sheth 16 F 1990-01-02 4000
102 Rahul Raj 17 M 1992-01-02 5000
103 NULL 18 F 1992-05-04 6000
104 Heet Parmar 18 M 1998-10-09 5000
105 Rajvi Patel 17 F 1997-10-09 1500
NULL Radha Patel 17 F 1996-06-09 NULL

COUNT(*) COUNT(FEES)
SELECT COUNT(*), COUNT(FEES) FROM
6 5
STUDENT;
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays total records of

emp table.

292
MYSQL DATA MANIPULATION LANGUAGE COMMANDS

Displays total records of

result set from emp table.

293
MYSQL’S JOINS
 JOINS
A Join is a SELECT query (command) that combines rows from two
or more tables. In a join-query , more than one table are listed in from
clause. The function of combining multiple tables is called joining. The
SYNTAX of joining more than one table is as follows…

SELECT * Or Cols, Cols, …, Cols FROM <Table1>, <Table2>, …,

<TableN>;

e.g.
This query will result a
SELECT * FROM emp, dept; Cross Join of emp and
dept table.
295
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS;

 JOINS
SELECT * FROM STUDENT, MARKS;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
101 Meeti Sheth 16 F 1990-01-02 102 S1 35
101 Meeti Sheth 16 F 1990-01-02 103 S2 89
102 Rahul Raj 17 M 1992-01-02 101 S1 59
102 Rahul Raj 17 M 1992-01-02 101 S2 58
102 Rahul Raj 17 M 1992-01-02 102 S1 35
102 Rahul Raj 17 M 1992-01-02 103 S2 89
103 Saumya Charotar 18 F 1992-05-04 101 S1 59
103 Saumya Charotar 18 F 1992-05-04 101 S2 58
103 Saumya Charotar 18 F 1992-05-04 102 S1 35
103 Saumya Charotar 18 F 1992-05-04 103 S2 89
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM MARKS, STUDENT;

 JOINS
SELECT * FROM MARKS, STUDENT;
ROLLNO SUBJECT MARKS ROLLNO NAME AGE GENDER DOB
CODE OBT
101 S1 59 101 Meeti Sheth 16 F 1990-01-02
101 S1 59 102 Rahul Raj 17 M 1992-01-02
101 S1 59 103 Saumya Charotar 18 F 1992-05-04
101 S2 58 101 Meeti Sheth 16 F 1990-01-02
101 S2 58 102 Rahul Raj 17 M 1992-01-02
101 S2 58 103 Saumya Charotar 18 F 1992-05-04
102 S1 35 101 Meeti Sheth 16 F 1990-01-02
102 S1 35 102 Rahul Raj 17 M 1992-01-02
102 S1 35 103 Saumya Charotar 18 F 1992-05-04
103 S2 89 101 Meeti Sheth 16 F 1990-01-02
103 S2 89 102 Rahul Raj 17 M 1992-01-02
103 S2 89 103 Saumya Charotar 18 F 1992-05-04
 JOINS

Dept Table

Emp Table

300
JOINS

When we join two tables

the resultant table will be
the Cartesian product
(Cross Join) of two tables.
It is also referred to as
Unrestricted Join. 301
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS WHERE

[Link]=[Link];
 JOINS
SELECT * FROM STUDENT, MARKS WHERE
[Link]=[Link];
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
102 Rahul Raj 17 M 1992-01-02 102 S1 35
103 Saumya Charotar 18 F 1992-05-04 103 S2 89
 JOINS

Dept Table

Emp Table

304
JOINS

When we join two tables

based on identical column of
both tables (Primary Key –
Foreign Key) then as a result
only matching values will be
displayed (instead of Cross
Join)
305
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS WHERE

[Link]=[Link] AND
[Link]=101;
 JOINS
SELECT * FROM STUDENT, MARKS WHERE
[Link]=[Link] AND
[Link]=101;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
 JOINS

Dept Table

Emp Table

308
JOINS

Joining two tables based on

identical column from both
tables and also an additional
condition. 309
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS WHERE

[Link]=[Link];
 JOINS
SELECT * FROM STUDENT, MARKS WHERE
[Link]=[Link];
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
102 Rahul Raj 17 M 1992-01-02 102 S1 35
103 Saumya Charotar 18 F 1992-05-04 103 S2 89

Identical Column ROLLNO (Common Column in tables printed

multiple times as we have used * sign to select all columns of both
tables.)
 When join is performed
JOINS between two tables using
identical column of both
tables then both tables will
be displayed in result set.

312
Identical Column
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT STUDENT.*,SUBJECTCODE, MARKSOBT FROM

STUDENT, MARKS WHERE
[Link]=[Link];
 JOINS
SELECT STUDENT.*,SUBJECTCODE, MARKSOBT FROM
STUDENT, MARKS WHERE
[Link]=[Link];
ROLLNO NAME AGE GENDER DOB SUBJECT MARKSOBT
CODE
101 Meeti Sheth 16 F 1990-01-02 S1 59
101 Meeti Sheth 16 F 1990-01-02 S2 58
102 Rahul Raj 17 M 1992-01-02 S1 35
103 Saumya Charotar 18 F 1992-05-04 S2 89

Identical Column ROLLNO (appears in result set only one time as

columns to be considered for the result set are explicitly specified. In
this case STUDNET.* means all columns of STUDENT table and only
SUBJECTCODE and MARKSOBT from MARKS table)
JOINS

During joining process

identical column from both
tables can be displayed once
in result set by specifying a 315
identical column only one
time.
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT STUDENT.*,SUBJECTCODE, MARKSOBT FROM

STUDENT, MARKS WHERE [Link]=ROLLNO;
This query will generate error as Identical column or column(s) with
same name must be prefixed with TABLENAME. Otherwise, mysql
engine will generate an error of ambiguity
 JOINS
When we join more than one table, the identical column (common
column name in tables) in more than one table should not be mentioned
directly in query, as it refer to as a problem of ambiguity. The identical
column should be mentioned by prefixing <TableName> and a period sign
(.) sign.

Deptno is identical
column in both tables, it
shows the problem of
ambiguity as it is
mentioned without
prefixing table name.
317
 Now, no problem of
ambiguity as identical
JOINS column is prefixed with
column name.

318
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT S.*,[Link], [Link] FROM

STUDENT S, MARKS M WHERE [Link]=[Link];
 JOINS
SELECT S.*,[Link], [Link] FROM
STUDENT S, MARKS M WHERE [Link]=[Link];
ROLLNO NAME AGE GENDER DOB SUBJECT MARKSOBT
CODE
101 Meeti Sheth 16 F 1990-01-02 S1 59
101 Meeti Sheth 16 F 1990-01-02 S2 58
102 Rahul Raj 17 M 1992-01-02 S1 35
103 Saumya Charotar 18 F 1992-05-04 S2 89

In this example STUDENT table is renamed as S and MARKS table is

renamed as M, so with in query if we want to refer the columns of
STUDENT table then we need to prefix S. and if we want to refer
column of MARKS table then we need to prefix M. this concept is
called TABLE ALIAS.
 JOINS
A table in join query can be renamed in order to make a table name
short(referred to as Table Alias), by suffixing <SPACE> <New Table Name
For Query>.

Tables are renamed as E and

D. E refers to emp and D
refers to Dept. Once a table
is renamed in query then 321
only new name should be
used in query.
JOINS

This query shows an error,

because tables are renamed
and original table is used.

322
 JOINS
In MYSQL, two retrieve records from more than one table, a join
operation should be performed in select command. A MYSQL, offers
following types of joins…

Joins

Equi Natural Cross

Join Join Join 323
 JOINS
A. Equi Join:
In MYSQL, the join, in which columns are compared for equality
(using equality operator i.e. = on identical column) is called Equi Join.
In this type of join all the columns from joining tables appear in the
result set even if they are identical.

This join represents equi

join, because the records
from both tables are derived 324
using = operator.
JOINS

ON (join condition) will also 325

produce equi-join.
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS WHERE

[Link]=[Link] AND
[Link]=101;
 JOINS
SELECT * FROM STUDENT, MARKS WHERE
[Link]=[Link] AND
[Link]=101;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS ON

[Link]=[Link] WHERE
[Link]=101;
 JOINS
SELECT * FROM STUDENT, MARKS ON
[Link]=[Link] WHERE
[Link]=101;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
 JOINS
B. Natural Join:
In MYSQL, the join, in which only one of the identical column
(coming from joined tables) exists, is called Natural Join. When we
perform Natural Join the tables should have Parent - Child
relationship using Primary Key and Foreign Key. A Natural Join
between tables can be done using either using Natural Join or
Using keywords.

Natural Join between emp

and dept. 330
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT NATURAL JOIN MARKS WHERE

[Link]=101;
 JOINS
SELECT * FROM STUDENT NATURAL JOIN MARKS WHERE
[Link]=101;
ROLLNO NAME AGE GENDER DOB SUBJECT CODE MARKSOBT
101 Meeti Sheth 16 F 1990-01-02 S1 59
101 Meeti Sheth 16 F 1990-01-02 S2 58
JOINS

The USING (ColName) clause

in join will also produce a
Natural Join On Two Tables. 333
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT JOIN MARKS USING(ROLLNO)

WHERE [Link]=101;
 JOINS
SELECT * FROM STUDENT JOIN MARKS USING(ROLLNO)
WHERE [Link]=101;
ROLLNO NAME AGE GENDER DOB SUBJECT CODE MARKSOBT
101 Meeti Sheth 16 F 1990-01-02 S1 59
101 Meeti Sheth 16 F 1990-01-02 S2 58
 JOINS
C. Cross Join:
In MYSQL, the Cross Join (Cartesian Product) is a very basic type of
join that simply matches each row from the one table to every
row from another table. A Cross Join between tables can be done
using the following SYNTAX.

SELECT * or Col1, Col1,…, Col1 FROM Table1 CROSS JOIN

Table2;

Or

SELECT * or Col1, Col1,…, Col1 FROM Table1 , Table2; 336

JOINS

When we join two tables

the resultant table will be
the Cartesian product
(Cross Join) of two tables.337
It is also referred to as
Unrestricted Join.
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT, MARKS;

 JOINS
SELECT * FROM STUDENT, MARKS;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
101 Meeti Sheth 16 F 1990-01-02 102 S1 35
101 Meeti Sheth 16 F 1990-01-02 103 S2 89
102 Rahul Raj 17 M 1992-01-02 101 S1 59
102 Rahul Raj 17 M 1992-01-02 101 S2 58
102 Rahul Raj 17 M 1992-01-02 102 S1 35
102 Rahul Raj 17 M 1992-01-02 103 S2 89
103 Saumya Charotar 18 F 1992-05-04 101 S1 59
103 Saumya Charotar 18 F 1992-05-04 101 S2 58
103 Saumya Charotar 18 F 1992-05-04 102 S1 35
103 Saumya Charotar 18 F 1992-05-04 103 S2 89
JOINS

When we join two tables

using CROSS Join the
resultant table will be the
Cartesian product of two 340
tables. It is also referred to
as Unrestricted Join.
 JOINS
RELATION: STUDENT RELATION: MARKS
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59

102 Rahul Raj 17 M 1992-01-02 101 S2 58

103 Saumya Charotar 18 F 1992-05-04 102 S1 35

103 S2 89

SELECT * FROM STUDENT CROSS

JOINMARKS;
 JOINS
SELECT * FROM STUDENT CORSS JOINMARKS;
ROLL NAME AGE GENDER DOB ROLLNO SUBJECT MARKSOBT
NO CODE
101 Meeti Sheth 16 F 1990-01-02 101 S1 59
101 Meeti Sheth 16 F 1990-01-02 101 S2 58
101 Meeti Sheth 16 F 1990-01-02 102 S1 35
101 Meeti Sheth 16 F 1990-01-02 103 S2 89
102 Rahul Raj 17 M 1992-01-02 101 S1 59
102 Rahul Raj 17 M 1992-01-02 101 S2 58
102 Rahul Raj 17 M 1992-01-02 102 S1 35
102 Rahul Raj 17 M 1992-01-02 103 S2 89
103 Saumya Charotar 18 F 1992-05-04 101 S1 59
103 Saumya Charotar 18 F 1992-05-04 101 S2 58
103 Saumya Charotar 18 F 1992-05-04 102 S1 35
103 Saumya Charotar 18 F 1992-05-04 103 S2 89