DML Commands

Data Manipulation Language (DML) is a class of SQL statements that are used to
query, edit, add and delete row-level data from database tables or views. The main
DML statements are SELECT, INSERT, DELETE, and UPDATE.
DML is contrasted with Data Definition Language (DDL) which is a series of SQL
statements that you can use to edit and manipulate the structure of databases and
the objects in them.

Types of DML Statements

The primary DML statements are:
� SELECT,
� INSERT,
� DELETE and
� UPDATE

With the exception of SELECT statements, all of the others are only applicable to
data within tables in a database.

To change the actual data that lives in tables, use:

� INSERT, DELETE and UPDATE statements

To access the data in database object, use:

� SELECT statements

DDL and DML Codes:

1.1 Let us CREATE first a database (DDL Command). (See code below, you can copy and
paste.)

CREATE DATABASE dbDeLaCruz;

1.2 Let us then CREATE the first database table (DDL Command). (See code below, you
can copy and paste.)

CREATE TABLE tblStudInfo

(
StudID VARCHAR(15) NOT NULL PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Age INT,
Address VARCHAR(30)
);

1.3 Let us then CREATE second database table (DDL Command). (See code below, you
can copy and paste.)

CREATE TABLE tblStudGrade

(
GradeID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudID VARCHAR(15) NOT NULL,
SubjCode VARCHAR(10),
SubjGrade VARCHAR(10),
SY VARCHAR(15),
Sem INT,
CourseYrSec VARCHAR(10)
);

1.4 Let us now INSERT data into the first database table (DML Command). (See code
below, you can copy and paste.)

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-007-01','FRED','MIGUEL',17,'LEGANES');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-204-01','JAMES','TAN',19,'PASSI CITY');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-116-01','CARLO','SIOCO',17,'LEGANES');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-229-01','KENT','LAURO',18,'POTOTAN');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-231-01','KENNETH','PORRAS',16,'JANIUAY');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-121-01','JOAN','TRINIDAD',16,'ESTANCIA');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-112-01','KRISTINE','CHUA',18,'ANTIQUE');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-341-01','JENNY','SANTOS',16,'STA. BARBARA');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-174-01','ANALIE','BAJANDI',17,'POTOTAN');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-311-01','EFREN','ZUBIRI',19,'ANTIQUE');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-333-01','JANNY','YOUNG',16,'POTOTAN');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-323-01','MELAI','ESTRELLA',18,'ESTANCIA');

INSERT INTO tblStudInfo(StudID,FirstName,LastName,Age,Address)

VALUES('22-423-01','KEREN','REYES',16,'LEGANES');

1.5 Let us check if the first table is already populated with data or records by
using the SELECT statement. (DML Command). (See code below, you can copy and
paste.)
SELECT * FROM tblStudInfo;

1.6 Let us now INSERT data into the second database table (DML Command). (See code
below, you can copy and paste.)

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-007-01','ENG101','86','2022-2023',1,'BS1-F');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-007-01','FIL101','88','2022-2023',1,'BS1-A');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-007-01','ACCTG101','90','2022-2023',1,'BS1-C');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-007-01','COMP101','92','2022-2023',1,'BS1-D');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-007-01','MGT101','84','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-204-01','ENG101','76','2022-2023',1,'BS1-E');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-204-01','FIL101','78','2022-2023',1,'BS1-F');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-204-01','ACCTG101','80','2022-2023',1,'BS1-E');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-204-01','COMP101','72','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-204-01','MGT101','74','2022-2023',1,'BS1-A');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-116-01','ENG101','84','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-116-01','FIL101','72','2022-2023',1,'BS1-C');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-116-01','ACCTG101','82','2022-2023',1,'BS1-G');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-116-01','COMP101','70','2022-2023',1,'BS1-C');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-116-01','MGT101','74','2022-2023',1,'BS1-D');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-229-01','ENG101','65','2022-2023',1,'BS1-F');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-229-01','FIL101','68','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-229-01','ACCTG101','71','2022-2023',1,'BS1-A');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-229-01','COMP101','86','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-229-01','MGT101','81','2022-2023',1,'BS1-D');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-231-01','ENG101','87','2022-2023',1,'BS1-E');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-231-01','FIL101','82','2022-2023',1,'BS1-F');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-231-01','ACCTG101','87','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-231-01','COMP101','70','2022-2023',1,'BS1-C');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-231-01','MGT101','75','2022-2023',1,'BS1-E');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-333-01','ENG101','75','2022-2023',1,'BS1-D');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-333-01','FIL101','78','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-333-01','ACCTG101','70','2022-2023',1,'BS1-A');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-174-01','ACCTG101','97','2022-2023',1,'BS1-D');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-174-01','COMP101','95','2022-2023',1,'BS1-C');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-174-01','MGT101','95','2022-2023',1,'BS1-F');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-323-01','ENG101','79','2022-2023',1,'BS1-G');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-323-01','FIL101','82','2022-2023',1,'BS1-A');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-323-01','ACCTG101','73','2022-2023',1,'BS1-E');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-423-01','ACCTG101','90','2022-2023',1,'BS1-B');

INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)

VALUES('22-423-01','COMP101','89','2022-2023',1,'BS1-A');
INSERT INTO tblStudGrade(StudID,SubjCode,SubjGrade,SY,SEM,CourseYrSec)
VALUES('22-423-01','MGT101','91','2022-2023',1,'BS1-D');

1.7 Let us check if the second table is already populated with data or records by
using the SELECT statement. (DML Command). (See code below, you can copy and
paste.)

SELECT * FROM tblStudGrade;

1.8 Let us UPDATE the name of �FRED� into �FREDERICO� in the first database table
(DML Command). (See code below, you can copy and paste.)

UPDATE tblStudInfo SET FirstName = 'FREDERICO'

WHERE StudID = '22-007-01';

1.9 Let us check if the name is updated by using the SELECT statement. (DML
Command). (See code below, you can copy and paste.)

SELECT * FROM tblStudInfo

WHERE StudID = '22-007-01';

2.0 Let us UPDATE the Subject Grade of �FREDERICO� in �ENG101� to �96� in the
second database table (DML Command). (See code below, you can copy and paste.)

UPDATE tblStudGrade SET SubjGrade = '96'

WHERE StudID = '22-007-01' AND SubjCode = 'ENG101';

2.1 Let us check if the name is updated by using the SELECT statement. (DML
Command). (See code below, you can copy and paste.)

SELECT * FROM tblStudGrade

WHERE StudID = '22-007-01';

2.2 Let us now DELETE specific data from the first database table (DML Command).
(See code below, you can copy and paste.)

DELETE FROM tblStudInfo where StudID = '22-311-01';

2.3 Let us check if the record is already DELETED in topic 2.2 using the SELECT
statement. (DML Command). (See code below, you can copy and paste.)

SELECT * FROM tblStudInfo

WHERE StudID = '22-311-01';

2.4 Let us now DELETE specific data from the second database table (DML Command).
(See code below, you can copy and paste.)

DELETE FROM tblStudGrade where SubjCode = 'ENG101';

2.5 Let us check if the record is already DELETED in topic 2.4 using the SELECT
statement. (DML Command). (See code below, you can copy and paste.)

SELECT * FROM tblStudGrade

WHERE SubjCode = 'ENG101';

(Note: if MySQL returned an empty result set, then your code is correct)

2.6 Let us for now DELETE all the data from the first database table (DML Command).
(See code below, you can copy and paste.)

DELETE FROM tblStudInfo;

2.7 Let us check if the records are already DELETED in topic 2.6 using the SELECT
statement. (DML Command). (See code below, you can copy and paste.)

SELECT * FROM tblStudInfo;

(Note: if MySQL returned an empty result set, then your code is correct)

2.8 Let us for now DELETE all the data from the second database table (DML
Command). (See code below, you can copy and paste.)

DELETE FROM tblStudGrade;

2.9 Let us check if the records are already DELETED in topic 2.8 using the SELECT
statement. (DML Command). (See code below, you can copy and paste.)
SELECT * FROM tblStudGrade;

(Note: if MySQL returned an empty result set, then your code is correct)

2.9 Please go back to topic 1.4, copy and paste the code and execute the command.

3.0 Please go back to topic 1.6, copy and paste the code and execute the command.