What Is Data Manipulation Language (DML) Commands ? How To Use DML In SQL Queries | Database Engineering

Objectives:

To guide you through execution of basic Data Manipulation commands.

Description:

Data manipulation language (DML) Commands used to maintain and query a database, including

those for updating, inserting, modifying, and querying data.

INSERTING DATABASE CONTENTS:

The SQL command that is used to populate tables is the INSERT command. When entering a value

for every column in the table, you can use a command like the following,

Command 1: To insert a row of data into a table where a value will be inserted for every attribute.

Command 2: To insert a row of data into a table where some attributes will be left null.

DELETING DATABASE CONTENTS:

Rows can be deleted from a database individually or in groups.

Command 1: Deleting rows that meet a certain criterion from a table.

The simplest form of DELETE eliminates all rows of a table.

UPDATING DATABASE CONTENTS:

To update data in SQL, we must inform the DBMS what relation, columns, and rows are involved.

RETRIEVING DATABASE CONTENTS:

Most SQL data retrieval statements include the following three clauses:

1. SELECT (include list of columns)

2. FROM (specify table name from where data needs to be retrieved)

3. WHERE (Includes the conditions for row selection within the items in the FROM clause)

To select the contents of entire table, asterisk (*) is used without where clause.

Now we will try to understand this topic through some examples we will let you know how to use them.

Write SQL commands for the following:

1. Create two different forms of the INSERT command to add a Student with a student ID of

65798 and last name Lopez to the Student table?

Query:

insert into Student Values (65798,'Lopez');

insert into Student (StudentId,StudentName) Values (65798

2. Now write a command that will remove Lopez from the Student table.

delete from Student where StudentName='Lopez' 

OR 

delete from Student where StudentId

3. Create an SQL command that will modify the name of course ISM 4212 from Database to

Introduction to Relational Databases.

Query:

Update Course SET CourseName='Introduction to Relational Databases' 

where CourseID='ISM 4212'

 select * from Cours

OUTPUT:

Write SQL queries to answer the following questions: 

1. Which students have an ID number that is less than 50000? 

Query:

select StudentId, StudentName From Student Where StudentId

OUTPUT:

2. What is the name of the faculty member whose ID is 4756? 

Query:

select FacultyName From Faculty Where FacultyId=475

OUTPUT:

3. What is the smallest section number used in the first semester of 2008?

Query:

 select min(SectionNo) From Section Where CourseID='I-2008' 

OUTPUT:

Write SQL queries to answer the following questions: 

1. Select students that are enrolled in Section 2714?

Query:

 SELECT s.StudentId, s.StudentName 

FROM Student S INNER JOIN Registration n

 ON s.StudentId = n.StudentId 

WHERE SectionNo=2714

OUTPUT:

2. Which faculty members have qualified to teach a course since 1993? 

Query:

SELECT fac.FacultyId, fac.FacultyName 

FROM Faculty fac INNER JOIN Qualified Qua

 ON fac.FacultyId = Qua.FacultyId 

WHERE year(DateQualified)<=1993 

OUTPUT:

3. List the faculty ID, course, and date of qualification. 

Query:

SELECT f.FacultyId, n.CourseName ,f.DateQualified 

FROM Qualified f INNER JOIN Course n 

ON f.CourseId = n.CourseI

OUTPUT: