�ݺ�ߣ

DML COMMANDS
BY Ms. Sonali Sonavane

INSERT Command
 MySQL INSERT statement is used to store or add data in MySQL
table within the database.
 We can perform insertion of records in two ways using a single
query in MySQL:
 Insert record in a single row
 Insert record in multiple rows
Syntax:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );
Example:
INSERT INTO People (id, name, occupation, age)
VALUES (101, 'Peter', 'Engineer', 32);

��ǲԳ�…
 Inserting Multiple records into table
Syntax:
INSERT INTO table_name VALUES
( value1, value2,...valueN ),( value1, value2,...valueN ),
...........,(value1, value2,...valueN );
Example:
INSERT INTO People VALUES (102, 'Joseph', 'Develope
r', 30),(103, 'Mike', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45);

��ǲԳ�…
 If we want to store records without giving all fields,
we use the following partial field statements. In such
case, it is mandatory to specify field names.
Example:
INSERT INTO People (name, occupation)
VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');

Update command
 MySQL UPDATE query is a DML statement used to
modify the data of the MySQL table within the
database.
 The UPDATE statement is used with the SET and
WHERE clauses. The SET clause is used to change
the values of the specified column. We can update
single or multiple columns at a time.

��ǲԳ�…
Syntax:
UPDATE table_name SET column_name1 = new -
value1,column_name2=new-value2, ...
[WHERE Clause]
Example:
1. update stud set city='pune' where name='aarti';
(Only city field of row having name as aarti will be changed as
‘pune’)
2. . update stud set city='pune'
(city field of All row will be changed as ‘Pune’)

DELETE Command
 MySQL DELETE statement is used to delete data from the
MySQL table within the database.
 By using delete statement, we can delete records on the
basis of conditions.
Syntax:
DELETE FROM table_name WHERE (Condition specified);
Example:
delete from stud where grade='72';
DELETE FROM Stud;

DQL Commands:SELECT
 The MySQL SELECT statement is used to fetch data from the one or
more tables in MySQL. We can retrieve records of all fields or
specified fields.
Syntax:
SELECT column list
FROM table
[WHERE conditions];
Example:
1. Select rno, name from stud;
2. Select * from stud;
3. Select * from stud where grade>60;
4. Select grade from Stud where name=‘aarti’;

Where clause
 MySQL WHERE Clause is used with SELECT, INSERT,
UPDATE and DELETE clause to filter the results.
 It specifies a specific position where you have to do the
operation.
Example:
1.SELECT *FROM emp
WHERE ecity = ’pune' AND age >30;
2. select * from emp where(ecity='pune' and did=1)or
(age<33);

LIKE Operator- search for a specified pattern in a column
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
SQL Wildcards
 SQL wildcards can substitute for one or more
characters when searching for data in a database.
 SQL wildcards must be used with the LIKE operator.
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character

LIKE Operator- Example
P_Id LastName Address
1 Heena Pune
2 Savita Pune
3 Sarika Bombay
"Persons" table
We use the following SELECT statement:
1. SELECT LastName from Person where LastName like ‘S%’
2.SELECT ename FROM emp WHERE address LIKE ‘_________90';
3. SELECT ename FROM emp WHERE ecity NOT LIKE ’mum%';

Order By- clause
 The ORDER BY keyword is used to sort the result-set by a specified column.
 The ORDER BY keyword sort the records in ascending order by default.
 If you want to sort the records in a descending order, you can use the DESC
keyword.
Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC
Example
1. Select * from Stud order by rno desc;
2. Select * from Stud order by rno ;
3. Select * from Stud order by Name;
4. select name ,city from stud where grade>70 order by name asc, city
desc;

DISTINCT Operator
 The DISTINCT keyword can be used to return only
distinct (different) values.
Syntax
 SELECT DISTINCT column_name(s) FROM table_name;
Example
 SELECT distinct(Address) from emp;

IN Operator
The MySQL IN condition is used to reduce the use of
multiple OR conditions in a SELECT, INSERT, UPDATE and
DELETE statement.
The IN operator allows you to specify multiple values in
a WHERE clause.
Syntax
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...)
Example
SELECT * FROM emp WHERE city IN ('Pune',mumbai');

NULL Operator-
 To test if a value is NULL you cannot use =, because
every NULL is considered distinct from every other
one. Must use "IS NULL" or "IS NOT NULL“
Syntax
SELECT Attr_List FROM table_name
WHERE Attr_name IS NULL;
Example
1. SELECT * FROM Stud WHERE city is null;
2. SELECT * FROM Stud WHERE city is not null;

Not Equal operator
 MySQL Not Equal is an inequality operator that
used for returning a set of rows after comparing
two expressions that are not equal. The MySQL
contains two types of Not Equal operator, which are
(< >) and (! =).
Example:
 SELECT * FROM emp WHERE city <> “pune";
 SELECT * FROM emp WHERE city != “pune";

Between Operator
 The MYSQL BETWEEN condition specifies how to
retrieve values from an expression within a specific
range. It is used with SELECT, INSERT, UPDATE and
DELETE statement.
Example:
SELECT * FROM stud
WHERE grade BETWEEN 70 AND 80;

Aggregate Functions
 MySQL aggregate functions retrieve a single value
after performing a calculation on a set of values.
 In general, aggregate functions ignore null values.
• Used to find the minimum value of a
certain column
Min
• Used to find the maximum value of a
certain column
Max
• Used to calculate the average value of
the numeric type
Sum
• Used to calculate the sum of all
selected columns
Avg
• Used to Count the number of rows in a
database table
Count

Avg(),Min(),Max(),Sum()
Syntax
SELECT AVG( column_name) FROM table_name;
Example
SELECT AVG(Marks) FROM Stud;
Syntax
SELECT Min( column_name) FROM table_name;
Example
SELECT Min(Marks) FROM Stud;
Syntax of all other functions are similar.

Count()- Syntax
 COUNT function is used to Count the number of rows in a
database table. It can work on both numeric and non-numeric
data types.
Syntax
SELECT COUNT( column_name) FROM table_name;
 COUNT function uses the COUNT(*) that returns the count of all
the rows in a specified table. COUNT(*) considers duplicate
and Null.
Syntax
SELECT COUNT(*) FROM table_name;

Group By and Having Clause
 The MYSQL GROUP BY Clause is used to collect data from
multiple records and group the result by one or more column. It
is generally used in a SELECT statement.
 You can also use some aggregate functions like COUNT, SUM,
MIN, MAX, AVG etc. on the grouped column.
Syntax:
SELECT Column1, column2, ... Column_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY column1, column2, ... Column_n;

��ǲԳ�…
Example:
1. SELECT city, COUNT(*) FROM stud
GROUP BY city;
2. SELECT ename, did, SUM(salary) AS "Total dept
salary" FROM emp GROUP BY did;

HAVING Clause
 MySQL Having Clause is used only with GROUP BY
clause.
 It always returns the rows where condition is TRUE.
Syntax:
SELECT column_name(s) FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition ;

GROUP BY & Having Clause- Example
id name type price
123451 Park's Great Hits Music 19
123452 Silly Puddy Toy 5
123453 Playstation Toy 89
123454 Men's T-Shirt Clothing 32
123455 Blouse Clothing 34
123456 Electronica 2002 Music 3
SELECT type, MIN(price) FROM
products GROUP BY type
Type Min(Prize)
Music 3
Toy 5
Clothing 32
Products Table
Out Put
SELECT type, Min(price) FROM
products GROUP BY type
having Min(price)>4
Type Min(Prize)
Toy 5
Clothing 32

��ǲԳ�…
Example:
SELECT ename, did, SUM(salary) AS "Total dept
salary" FROM emp GROUP BY did having
sum(salary)>50000;

MySQL first function
 The MySQL first function is used to return the first
value of the selected column. Here, we use limit clause
to select first record or more.
Syntax:
SELECT column_name
FROM table_name
LIMIT 1;

SET Operations: Union
 MySQL UNION operator allows you to combine two or more result sets of queries
into a single result set.
Syntax:
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list ...
To combine result set of two or more queries using the UNION operator, there are the
basic rules that you must follow:
 First, the number and the orders of columns that appear in all SELECT statements must
be the same.
 Second, the data types of columns must be the same or convertible.
 By default, the UNION operator removes duplicate rows even if you don’t specify the
DISTINCT operator explicitly.

Assignment
Eid EName Address Salary Commision
1 Amit Pune 35000 5000
2 Sneha Pune 25000
3 Savita Nasik 28000 2000
4 Pooja Mumbai 19000
5 Sagar Mumbai 25000 3000
1. Find different locations from where employees belong to?
2. What is maximum and minimum salary?
3. Display the content of employee table according to the ascending order of salary amount.
4. Find the name of employee who lived in Nasik or Pune city.
5. Find the name of employees who does not get commission.
6. Change the city of Amit to Nashik.
7. Find the information of employees whose name starts with ‘A’.
8. Find the count of staff from Mumbai.
9. Find the count of staff from each city
10.Find the address from where employees are belonging as well as where projects are going on.
11.Find city wise minimum salary.
12.Find city wise maximum salary having maximum salary greater than 26000
13.Delete the employee who is having salary greater than 30,000.
Create Employee table, Project table and add rows shown below
PrNo Addr
10 Mumbai
20 Pune
30 Jalgoan

�ݺ�ߣ

SQL Data Manipulation language and DQL commands

Recommended

More Related Content

Similar to SQL Data Manipulation language and DQL commands (20)

More from sonali sonavane (9)

Recently uploaded (20)

SQL Data Manipulation language and DQL commands