Midterm: 1.3 Understand Data Manipulation Language (DML)

structured query language (SQL)

SQL—DML—DDL relationship

A database sublanguage used in querying, updating, and managing relational databases— the de facto standard for database products. Acronym: SQL

SQL is a language designed to be used only with databases.

data definition language (DDL)

SQL—DML—DDL relationship

A language that defines all attributes and properties of a database, especially record layouts, field definitions, key fields, file locations, and storage strategy. Acronym: DDL.

DDL is used to create the framework for the database, the schema of the database, or both. This will be covered more in DDL review lesson 1.4

DML—DDL (continued)

data manipulation language (DML)

SQL—DML—DDL relationship

In a database management system (DBMS), a language that is used to insert data in, update, and query a database. DMLs are often capable of performing mathematical and statistical calculations that facilitate generating reports. Acronym: DML.

DML is used to manipulate the data of a database.

DML—DDL are the two main language features of SQL.

DML is used to retrieve and modify database information. These commands will be used by all database users during a routine workday. Following is a basic review of some of the most common DML commands

SELECT

The SELECT command is the most commonly used in DML. It allows users to retrieve specific information from the database.

SELECT *

FROM Grant_info

WHERE aid_awarded > 36000

With this code, we have selected all students who have been awarded more than $36,000 from the Grant_info table.

INSERT

The INSERT command is used to add records to an existing table.

INSERT INTO Grant_info

VALUES(‘John’, ‘Doe’,12345,2200)

In this code, we have created John Doe, given him a student ID, and set his grant value to $2200.

There are four data values specified for the record. These correspond to the table attributes/fields in the order they were defined: first_name, last_name, student_id, and credit.

UPDATE

The UPDATE command can be used to modify information contained within a table, either individual data or groups of data.

UPDATE Grant_info

SET aid_awarded = aid_awarded + 4000

WHERE student_id = 12345

This UPDATE command calls the Grant_info table and adds $4,000 to the value of the aid award for student 12345.

DELETE

The DELETE command is used to remove records from an existing table.

DELETE FROM Grant_info

WHERE student_id = 12345

Since we are deleting all the fields of this particular record from the table, we do not have to specify the field names, as we did when we inserted the record. This removes only the record with student_id = 12345