Lab Sheet: Data Manipulation Language (DML) Syntax & Example – Database

Lab Sheet: Database Management System ( DBMS )
Title: Data Manipulation Language ( DML )

Introduction to Data Manipulation Language – MySQL

Data manipulation is

  • The retrieval of information stored in the database
  • The insertion of new information into the database
  • The deletion of information from the database
  • The modification of information stored in the database

A data-manipulation language ( DML ) is a language that enables users to access or manipulate data as organized by the appropriate data model. There are basically two types:

  1. Procedural DML s require a user to specify what data are needed and how to get those data.
  2. Declarative DML s (also referred to as nonprocedural DML s) require a user to specify what data are needed without specifying how to get those data.

DML Syntax and examples

MySQL INSERT

INSERT INTO <tablename> (column1,column2, … , columnN) values (value1, value2, … , valueN);

OR

INSERT INTO <tablename> VALUES (value1, value2, ... , valueN);

ex: INSERT INTO ACCOUNT VALUES ('A101','POKHARA',500);

INSERT INTO ACCOUNT (ACCTNO,BRANCH,BALANCE) VALUES ('A101','POKHARA',500);

MySQL SELECT

SELECT * FROM <tablename>; - retrieving everything from specified table.

SELECT column1,column2,...columnN FROM <tablename>; - retrieving only values of specified columns from the table.

SELECT * FROM <tablename> WHERE <condition> ; - applying condition in where clause.

ex: SELECT * FROM ACCOUNT;

SELECT ACCTNO,BRANCH FROM ACCOUNT;

SELECT * FROM ACCOUNT WHERE ACCTNO = 'A101';

MySQL UPDATE

UPDATE <tablename> SET columnN='value'; - updates all values of that attribute

UPDATE <tablename> SET columnN='value' WHERE <condition>; - updates value of the attribute meeting criteria of specified condition.

ex: UPDATE ACCOUNT SET BRANCH='KTM'; - updates all branch values to kathmandu

UPDATE ACCOUNT SET BRANCH='KTM' WHERE ACCTNO = 'A101'; - changes value of A101'S BRANCHA POKHARA to Kathmandu.

 MySQL DELETE

DELETE FROM <tablename>; - deletes all records from that table.

DELETE FROM <tablename> WHERE <condition>; - deletes only records meeting the specified condition.

ex: DELETE FROM ACCOUNT;

DELETE FROM ACCOUNT WHERE BALANCE < 1000;

 

EXERCISE:

  1. Create database named ‘College’.
  2. Create tables Course, Students, Staffs in database College with following structure and appropriate data type.
    • Course : course_id, name, credit, lecturerid.
    • Staffs : staff_id, name, mobileno, address.
    • Students : student_id, name, phone, address, “previous college”.
  3. Insert 4-5 records in all tables with relevant data like:
    • Course
      CMP101,Fundamental Computer,1,Lec15
      MTH103,Engineering Maths,3,Lec11
      CMP109,Internet Technology,2,Lec21
      PST119,Problem Solving Technology,3,Lec22
      PRJ111,Project,4,Lec01
    • Staffs
      STF11, Criag , 9874563211, Lamachaur
      ………
  4. Display all records of table ‘Staffs’.
  5. Display name and address of table ‘Students’ with specific student_id.
  6. Display course records where credit is less than or equal to 3.
  7. Display all records of table ‘Staffs’ whose name starts from ‘R’.
  8. Change Course name of ‘Project’ to ‘Minor Project’ in Course table.
  9. Remove record(s) from table Course where credit is less than 2.
  10. Remove every data from table Staffs.
  11. Remove the table Staffs.

Solution

Create MySQL Database

To create database college

CREATE DATABASE `college`;

Creating MySQL Tables

To create tables Course, Students, Staffs in database College with following structure and appropriate data type.

  • Course : course_id, name, credit, lecturerid.
  • Staffs : staff_id, name, mobileno, address.
  • Students : student_id, name, phone, address, “previous college”.

Table Course

CREATE TABLE IF NOT EXISTS `Course` (
  `name` varchar(30) DEFAULT NULL,
  `credit` int(11) DEFAULT NULL,
  `course_id` varchar(10) NOT NULL,
  `lecturerid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`course_id`)
);

Table Staffs

CREATE TABLE IF NOT EXISTS `Staffs` (
  `name` varchar(30) DEFAULT NULL,
  `mobileno` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  `staff_id` varchar(10) NOT NULL,
  PRIMARY KEY (`staff_id`)
);

Table Students

CREATE TABLE IF NOT EXISTS `Students` (
  `name` varchar(30) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL,
  `previous college` varchar(30) DEFAULT NULL,
  `students_id` varchar(10) NOT NULL,
  PRIMARY KEY (`students_id`)
);

 Insert into MySQL Tables

Insert into Course table

INSERT INTO `Course` (`name`, `credit`, `course_id`, `lecturerid`) VALUES
('Fundamental Computer', 1, 'CMP101', 'Lec15'),
('Internet Technology', 2, 'CMP109', 'Lec21'),
('Engineering Maths', 3, 'MTH103', 'Lec22'),
('Project', 4, 'PRJ111', 'Lec01'),
('Problem Solving Technology', 3, 'PST119', 'lec22');

Insert into Staffs table

INSERT INTO `Staffs` (`name`, `mobileno`, `address`, `staff_id`) VALUES
('Ram Bahadur', '9874563211', 'Lamachaur', 'STF11'),
('Hari K.C.', '1234554312', 'Annapurna Tole', 'STF12'),
('Krishna Poudel', '9866600990', 'Airport', 'STF13');

Insert into Students table

INSERT INTO `Students` (`name`, `phone`, `address`, `previous college`, `students_id`) VALUES
('Bijaya Parajuli', '9807658904', 'Chipledhunga', 'Shishu Niketan', 'STD11'),
('Luzan Baral', '9802354678', 'Malepatan', 'WRC', 'STD12'),
('Deepti Baral', '9876543210', 'Zero', 'Amarsing H.S.S.', 'STD13');

 Display data from table – MySQL SELECT

To display all records of table ‘Staffs’.

SELECT * FROM `Staffs`;

Display name and address of table ‘Students’ with specific student_id. (Hint: use WHERE clause)

SELECT `name`,`address` FROM `Students` WHERE students_id ='STD11';

Display course records where credit is less than or equal to 3.

SELECT * FROM `Course` WHERE `credit` <= '3';

Display all records of table ‘Staffs’ whose name starts from ‘R’.

SELECT * FROM `Staffs` WHERE `name` LIKE 'R%';

Update MySQL table

To change Course name of ‘Project’ to ‘Minor Project’ in Course table.

UPDATE `Course` SET `name`='Minor Project' WHERE `name`='Project';

DELETE/ DROP from MySQL Tables

To remove record(s) from table Course where credit is less than 2.

DELETE FROM `Course` WHERE `credit` < 2;

To remove every data from table Staffs.

DELETE FROM `Staffs`;

To remove the table Staffs.

DROP TABLE `Staffs`;

 

This practical sheet was created for the submission of 2nd lab class of Database Management System (DBMS), titled Introduction to Data Manipulation Language (DML) with Syntax and Examples.
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>