(Laboratory No. 3.4: Basic DML SQL 1) : Objectives

College of Computing Education
3rd Floor, DPT Building

Matina Campus, Davao City
Telefax: (082)
Phone No.: (082)300-5456/305-0647 Local 116
Name :_______________________________ Date :__________________________________

Time :_______________________________ Prof :_______________________________
[ Laboratory No. 3.4: Basic DML SQL 1]

Objectives:
1. To store, search and retrieve, and remove records from a database using SQL-DML commands
Materials:
1. PC or Laptop
2. WAMP/XAMPP Installer
3. Web Browser or CLI
Background
DML- Data Manipulation Language (DML) statements are used for managing data within schema
objects DML deals with data manipulation, and therefore includes most common SQL statements such
SELECT, INSERT, etc. DML allows to add / modify / delete data itself.
DML is used to manipulate with the existing data in the database. Below are the commands:
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
Instructions:
1. Create a database with a name following the format <your last name>_activityBasicDML1
Example: BASTE_ActivityBasicDML1.
2. Using your previous database in previous activity which you were able to backup, you can import it right
here. Otherwise, you can simply create a new database file and table.
Do the following instruction and provide a screenshot (or paste in a notepad) for each action you performed.
-------------------------------------- [DML-Records Manipulation] -----------------------------------

INSERT STATEMENT is used to insert a single record or multiple records into a table in MySQL.
READ
SYNTAX
THIS
In its simplest form, the syntax for the INSERT statement when inserting a single record using the
VALUES keyword in MySQL is:
INSERT INTO table (column1, column2, ... )

VALUES (expression1, expression2, ... ), (expression1, expression2, ... ),...;
a. If you have the following table definition, here’s how you do it.
Pet_id Pet_name Pet_gender Pet_age
INSERT INTO tbl_pet values (1, ‘Nymeria’,’female’, 5);
CCE104/L Martzel P. Baste|Page 1

Telefax: (082)
Phone No.: (082)300-5456/305-0647 Local 116
Your table would become:

1 Nymeria female 5
SQL / Output
b. By using SELECT command, you are able to display the records containing in the tbl_pet table.
As you wish, invoke the following statement:
SELECT *from tbl_pet;
That’s it, easy!
SQL / Output
c. What if you want to insert 2 or more than rows or records? Here’s how.
INSERT INTO tbl_pet values (2, ‘Lee Tsu Non’,’male’, 15), (3, ‘Kelly Cally’,’female’, 16);
Your table would become:

1 Nymeria female 5
2 Lee Tsu Non male 15
3 Kelly Cally female 16
SQL / Output
Or if you want to store only specific value or fields to your table, you do this:
INSERT INTO tbl_pet (pet_id, pet_name, pet_age) values (2, ‘Anne ann’, 12);
Your table would have additional information about Anne:

2 Anne ann 12
SQL / Output
d. Using your tbl_pet table, store these values as an additional record:
Pet_ID Pet_Name Owner_Gender Pet_Age

1 Amenadiel Female 5
4 Lucifer Morningstar Male 4

Telefax: (082)
Phone No.: (082)300-5456/305-0647 Local 116
SQL / Output
e. Now, show the records of your tbl_pet table using SELECT command.
SQL / Output
NOTE: You will observe that you are able to store records having the same IDs even if they
have different information. Hence, it is called data duplications and it must be avoided. For
now, let us leave it as it is. We will have another activity intended for that purpose.
f. You just tried removing tbl_buyer table from your previous using DROP command. This time I
want you to use TRUNCATE TABLE command. To do it see below:
TRUNCATE TABLE tbl_pet;
SQL / Output
NOTE: You will observe that all of the records of your table tbl_pet is now gone but NOT the
tbl_pet. Unlike when you use DROP command, the table all of its records are all eradiated
from the database. For your information DROP and TRUNCATE commands are belonged to
DDL SQL.

Telefax: (082)
Phone No.: (082)300-5456/305-0647 Local 116
DB Programmer’s tasks:
1. Using your tbl_owner table, store these values: 1,” Cain”, “Davao City”, “female”, 2229614.
Your table should become:
Owner_ID Owner_Name Owner_Address Owner_Gender Owner_Contact

1 Cain Davao City Female 2229614
SQL / Output
2. Show records of your tbl_owner table using SELECT statement. Invoke the SQL statement below to
accomplish the task:
SELECT *from tbl_owner;
You should have shown like this:
Owner_ID Owner_Name Owner_Address Owner_Gender Owner_Contact

1 Cain Davao City Female 2229614
SQL / Output
3. Using the tbl_pet table, store these information (sequence):

1, “Anna”, “Female”, “Pinkish White”, 12,’Dog’,”Husky”, 2300.45. (Show your records after)
Your table should have become:
Pet_ID Pet_Name Pet_Gender Pet_Color Pet_Age Pet_Type Pet_Breed Pet_Price

1 Anna female Pinkish White 12 Dog Husky 8900.00
SQL / Output
4. Insert information for tbl_pet atleast 5 of your own specifications and preference.
SQL / Output
5. Insert the following information to your tbl_pet:

(Look at the table below carefully of what columns or fields are used upon storing values.)

1 Nymeria female 5
2 Lee Tsu Non male 15
3 Kelly Cally female 16
4 Anne ann 12
Telefax: (082)
Phone No.: (082)300-5456/305-0647 Local 116
SQL / Output
NOTE: Observe that you are able to store information of pet having the same Pet_id since it wasn’t
specified as primary key. Hence, duplication is possible.
6. Display all fields from the table tbl_pet. [DDL].
SQL / Output
7. Show records of your tbl_pet.
SQL / Output
8. Insert information to your table tbl_owner atleast 3 of your own specifications and preference.
SQL / Output
9. Show records of your tbl_owner.
SQL / Output
10. Display data dictionary of tbl_owner and tbl_pet. [DDL].
SQL / Output
11. Type “Happy!” in the comment section upon submitting.
Congratulations! You have just made your first database application. To summarize, you have encountered
some basic SQL statements. The first part was about creating database, tables, describing and specifying its
structures. Some of the commands are CREATE, ALTER, DROP, RENAME, SHOW, DESCRIBE which belongs
significantly to DDL or Data Definition Language. On the other hand, we have DML or Data
Manipulation Language which you are taught to store information to a table using INSERT and display this
information using SELECT statement. Some of the SQL commands under DML are DELETE and UPDATE.

(Laboratory No. 3.4: Basic DML SQL 1) : Objectives

Uploaded by

Copyright:

Available Formats

(Laboratory No. 3.4: Basic DML SQL 1) : Objectives

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

(Laboratory No. 3.4: Basic DML SQL 1) : Objectives

Uploaded by

Copyright:

Available Formats

College of Computing Education

3rd Floor, DPT Building

Name :_______________________________ Date :__________________________________

[ Laboratory No. 3.4: Basic DML SQL 1]

-------------------------------------- [DML-Records Manipulation] -----------------------------------

INSERT INTO table (column1, column2, ... )

Pet_id Pet_name Pet_gender Pet_age

INSERT INTO tbl_pet values (1, ‘Nymeria’,’female’, 5);

CCE104/L Martzel P. Baste|Page 1

Your table would become:

Pet_id Pet_name Pet_gender Pet_age

SELECT *from tbl_pet;

That’s it, easy!

Your table would become:

Pet_id Pet_name Pet_gender Pet_age

Your table would have additional information about Anne:

Pet_id Pet_name Pet_gender Pet_age

d. Using your tbl_pet table, store these values as an additional record:

Pet_ID Pet_Name Owner_Gender Pet_Age

CCE104/L Martzel P. Baste|Page 2

TRUNCATE TABLE tbl_pet;

CCE104/L Martzel P. Baste|Page 3

Owner_ID Owner_Name Owner_Address Owner_Gender Owner_Contact

Owner_ID Owner_Name Owner_Address Owner_Gender Owner_Contact

3. Using the tbl_pet table, store these information (sequence):

Pet_ID Pet_Name Pet_Gender Pet_Color Pet_Age Pet_Type Pet_Breed Pet_Price

5. Insert the following information to your tbl_pet:

Pet_id Pet_name Pet_gender Pet_age

6. Display all fields from the table tbl_pet. [DDL].

7. Show records of your tbl_pet.

9. Show records of your tbl_owner.

10. Display data dictionary of tbl_owner and tbl_pet. [DDL].

11. Type “Happy!” in the comment section upon submitting.

CCE104/L Martzel P. Baste|Page 5

You might also like

Name :_ Date :____