DML

What is Data Manipulation Commands?

are used to manipulate the data stored in database tables

INSERT

Insert row(s) into a table.

SELECT

Selects attributes from rows in one or more tables

WHERE

Restricts the selection of rows based on a conditional expressions

GROUP BY

Groups the selected rows based on one or more attributes

HAVING

Restricts the selection of grouped rows based on a condition

ORDER BY

Orders the selected rows based on one or more attributes

UPDATE

Modifies an attribute’s values in one or more table’s rows

DELETE

Deletes one or more rows from a table.

Comparison Operators
=,<,>,<=,>=,<>,!=

Used in conditional expressions

Logical Operators

AND/OR/NOT

Used in conditional expressions

Special Operators
BETWEEN

Checks whether an attribute value is within a range.

Special Operators
LIKE

Checks whether an attribute values matches a given string pattern

Special Operators
IN

Checks whether an attribute value matches any value within a value list

Special Operators
EXISTS

Checks whether a subquery returns any rows

Aggregate Functions
COUNT

Returns the number of rows with non-null values for a given column

Aggregate Functions
MIN

Returns the minimum attribute value found in a given column

Aggregate Functions
MAX

Returns the maximum attribute value found in a given column

Aggregate Functions
SUM

Returns the sum of all values for a given column

Aggregate Functions
AVG

Returns the average of all values for a given column

INSERT Command

Command used to enter data into a table
EX. INSERT INTO customer (cus_code,cus_lname,cus_fname,cus_mname,cu s_areacode,cus_phone) VALUES ('CUS8','Santos','Anna','C','8601','342-0078');

tablename

represents the table or relation

value

represents the value to be inserted

DELETE Command

Command used to delete table rows
EX. DELETE FROM tablename [WHERE conditionlist];

conditionlist

used in a where clause
-this is optional in a delete command

Select command

Used to list the contents of a table
EX. SELECT p_code,p_description,p_price,p_quantity,p_type ,v_code FROM product;

Selecting Rows with Conditional Restrictions

-Select table contents by placing restrictions on the rows to be included in output
-Done using WHERE clause in SELECT statement

EX. SELECT *FROM product WHERE v_code='21228';