DML Commands in SQL
• DML is an abbreviation of Data Manipulation Language.
• The DML commands are used to change the data present in the SQL database.
• We can easily access, store, modify, update and delete the existing records from the database using DML
commands.
Following are the four main DML commands in SQL:
1. INSERT Command
2. SELECT Command
3. UPDATE Command
4. DELETE Command
INSERT Command
The INSERT INTO statement is used to insert new records in a table.
Syntax1(Specify both the column names and the values to be inserted):
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Ex1:
INSERT INTO student(sid, name , login , gpa, age)
VALUES('1023422',’ABC', '1','6.7', '20’);
Syntax2(If you are adding values for all the columns of the table, you do not need to specify the column names .
However, make sure the order of the values is in the same order as the columns in the table)
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Ex:
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway’);
Syntax3: Inserting multiple rows into the table
INSERT INTO table_name VALUES
(value1, value2, value3, ...),
(….., …..,…..,…. )…………….;
Ex:
INSERT INTO student(sid, name , login , gpa, age)
VALUES ('1023422',’ABC', '1','6.7', '20’),
('1023423','ACD', '2','6.7', '20’),
('1023424','ADE', '3','7.5', '21');
o/p:
3 rows affected
Sid Name Login Gpa age
1023422 A 1 20 6.7
1023423 ACD 2 20 6.7
1023423 ACD 2 20 6.7
1023424 ADE 3 21 7.5
Syntax 4: Inserting values in only specified columns
Ex:
INSERT INTO Student (NAME, Age) VALUES ('PRATIK','19’);
Sid Name Login Gpa age
1023422 A 1 20 6.7
1023423 ACD 2 20 6.7
1023423 ACD 2 20 6.7
1023424 ADE 3 21 7.5
PRATIK 19
SELECT DML Command
-It is used to retrieve the data from all the columns of the table.
Syntax1:Retrieving the data from all columns
SELECT * FROM table_name;
Ex:
SELECT * FROM student;
Sid Name Login Gpa age
1023422 A 1 20 6.7
1023423 ACD 2 20 6.7
1023423 ACD 2 20 6.7
1023424 ADE 3 21 7.5
Syntax2:Retrieving the data from specific columns
SELECT column1, column2, ...
FROM table_name;
Ex:
SELECT Name,gpa
FROM student;
Clauses SELECT: Specifies which columns to retrieve.
SELECT column1, column2 FROM table_name;
FROM: Indicates the table or tables from which to retrieve
data.
In SQL, a clause is a component of a SQL statement SELECT column1 FROM table_name;
that specifies a particular condition or action.
Clauses are used to help define and control the WHERE: Filters the records based on specified conditions.
behavior of the SQL query and dictate what data is SELECT column1 FROM table_name WHERE condition;
retrieved, how it is filtered, and how it is presented.
GROUP BY: Groups rows that have the same values in
The order of clauses in a SQL statement generally specified columns.
follows a specific sequence: SELECT column1, COUNT(*) FROM table_name
GROUP BY column1;
SELECT column1, column2
FROM table_name HAVING: Filters groups based on a condition, typically
used with GROUP BY.
WHERE condition
SELECT column1, COUNT(*) FROM table_name
GROUP BY column1 GROUP BY column1 HAVING COUNT(*) > 5;
HAVING condition
ORDER BY: Sorts the result set by one or more columns.
ORDER BY column SELECT column1 FROM table_name ORDER BY
column1 DESC;
LIMIT number;
LIMIT: Restricts the number of rows returned.
SELECT column1 FROM table_name LIMIT 10;
WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfil a specified condition.
Syntax:
SELECT name, column2, ...
FROM table_name
WHERE condition;
DEMO Table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
Ex:
SELECT * FROM Customers
WHERE Country = 'Mexico’;
O/P:
Number of records:5
CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
Ex:
SELECT * FROM Customers
WHERE CustomerID = 1;
O/P:
Number of Records: 1
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Operators in The WHERE Clause
Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !
=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
BETWEEN Operator
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Ex:
SELECT * FROM student
WHERE age BETWEEN 19 AND 20;
Examples:
1. SELECT * FROM Products
WHERE Price = 18;
o/p:
Number of Records: 4
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18.00
35 Steeleye Stout 16 1 24 - 12 oz bottles 18.00
39 Chartreuse verte 18 1 750 cc per bottle 18.00
76 Lakkalikööri 23 1 500 ml 18.00
2. SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;
o/p:
Number of Records: 2
ProductID ProductName SupplierID CategoryID Unit Price
51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00
59 Raclette Courdavault 28 4 5 kg pkg. 55.00
AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Combining AND, OR and NOT
SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');
IN Operator
• The IN operator allows you to specify multiple values in a WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
OR
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Ex1:
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK’);
Ex:2
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK’);
EX3:
SELECT * FROM student
WHERE sid IN (SELECT sid FROM course);
LIKE Operator
LIKE Operator Description
WHERE CustomerName Finds any values that start with "a"
• The LIKE operator is used in a WHERE clause to
LIKE 'a%'
search for a specified pattern in a column. WHERE CustomerName Finds any values that end with "a"
LIKE '%a'
• There are two characters often used in conjunction WHERE CustomerName Finds any values that have "or" in any position
with the LIKE operator: LIKE '%or%'
WHERE CustomerName Finds any values that have "r" in the second position
• The percent sign (%) represents zero, one, or LIKE '_r%'
multiple characters WHERE CustomerName Finds any values that start with "a" and are at least 2
LIKE 'a_%' characters in length
• The underscore sign (_) represents one, single WHERE CustomerName Finds any values that start with "a" and are at least 3
LIKE 'a__%' characters in length
character
WHERE ContactName LIKE Finds any values that start with "a" and ends with "o"
'a%o'
• The percent sign and the underscore can also be
used in combinations!
• SELECT * FROM Customers
WHERE CustomerName LIKE 'a%’;
• Ex1:
• SELECT * FROM Customers
WHERE CustomerName LIKE '%a’;
• Ex2:
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
Lab Assignment-2
1. Write a query to change the student name column to new columns for First name and Last name.
2. Write a query to display the names (first_name, last_name) using alias name "First Name", "Last Name".
3. Add student_mark column in the student table.
4. Write a query to find the student_id who have mark above 50.
5. retrieve all records from the “student” table where the “Student_name” column does not start with the
letter ‘A’.
6. write a SQL query to find instructor whose room _no is either 10 or 20 or whose telephone no started
with 63.. Return instructor_id, instructor_name.
7. Add student_dept to the student table.
8. Retrieve all records from the “student” table where the “student_dept” column is equal to ‘IT’ and the
“student_mark” column is less than 80.
9. Retrieve all the records of students who have scored marks above 90 and less than 50.
10. retrieve the name of the instructor if the department is either “CS” or “IT”.
