Data Manipulation Language

Data manipulation language (DML) is SQL command used to manipulate database. Here given some Data Manipulation Language with it’s function explanation :

1. INSERT
   Used to insert data into table.
2. SELECT
   Used to select and show data.
3. UPDATE
   Used to update data of a table.
4. DELETE
   Used to delete data from a table.

INSERT

There are some forms of insert statement, which are :

1. INSERT INTO tablename VALUES (value1, value2,..);

The first form is used to insert data into a table without explicitly specifying the column names of the table where the data will be inserted. The number of value inserted must be the same with the number of column in the purpose table. The insertion order is left to right (from column one to last column). Example:

INSERT INTO subject VALUES (“MMS0001”, ”Citizenship”,”2”,” 12300001”, ”Wednesday”, ”07:00:00”,”M11”);

2. INSERT INTO tablename (column_list) VALUES (value_list);

The second form specifies both the column names and the values to be inserted. The lack of this form is there is possibility that the column with “not null” definition will be empty. Example :

INSERT INTO lecturer(id, name, sex, position, major, phone) VALUES (“12300001”,”Zara Erawati”,”P”,”Lecture”,”Network”,”89687796”);

3. INSERT INTO tablename SET column_name = value, ... ;

INSERT INTO lecturer SET id = “12300001”, sex = ”Zara Erawati”);

The third form is almost the same like the second form. But it use keyword set and it directly pair the column names and values to be inserted.

DELETE

Delete is used to delete record value in a table. Common syntax of delete command is,

DELETE FROM tablename WHERE <condition>

For example,

DELETE FROM student WHERE status=”Not Active” ; 

Here is another delete example which involve keyword between,

DELETE FROM student WHERE age between 30 and 50; 

The difference between the above syntax and the previous one is on the conditional. If the data is numeric, it is possible to use a range of numeric value in where condition.

Filtering on delete could be SELECT function like,

DELETE FROM student WHERE student_id IN (SELECT student_id FROM csp WHERE value = ‘E’);

The above syntax only can be used if SELECTION involve only one column, because like the sample above, the matching process of student_id just can be done with one column. Furthermore, Filtering also can involve aggregate function, like example

DELETE FROM csp WHERE value < (SELECT AVG(value) FROM csp);

SELECT part on the above example give result an average value of all values exist in column value in table csp. Next, if there is any data of column value whose value less than average value obtained from the selection then the record of it's value will be deleted. Note that, different from normal selection which can involve more than two columns, this kind of syntax can be used if it involve only one table.

UPDATE

Update is a command to update data exist in a table. Syntax for update command is,

UPDATE tablename SET <column_name> = <value> WHERE <condition>

Example of update syntax :

UPDATE student SET name= Susi WHERE student_id = 13001;

Update command above intented to change the value of column name in table student which has student_id = 13001 to susi. Set keyword act like assignment operator to change value of certain column in certain table. The value assigned to such column could be either a constant like the example above or some kind of expressions like example below,

UPDATE student SET age = age + 5;

The query is used to increase all value in column age by five. Next is syntax to update two columns at once,

UPDATE student SET name=Susi, age=20 WHERE student_id = 13001;

Update syntax can be treated to delete value of spesific column in table. Pay attention to example below,

UPDATE student SET address = ” ” WHERE student_id = 13001;

The query result is data address whose student_id 13001 will be deleted or set to NULL. Another example of update query given here,

UPDATE student
    SET age = case
    WHEN age > 45 then 40
    WHEN age > 30 then 25
    ELSE 20;
END;

Updating at column age, table student, done with some different conditions and different actions. At the example, there are three conditions of age in which for every condition there will be different action statement. If age value more than 45 years then age value will be set to 40, if age value more than 30 then age value will be set to 25, and if age value in another range than the previous two conditions then age value will be set to 20.

SELECT

SELECT command is used to select and show data from spesific database, either from a table or more than one table. SELECT query in simple form only have one pair of word that are SELECT which indicate columns will be selected and showed, and FROM keyword which indicate table from where the data will be taken. Common form of SELECT command is :

SELECT <column_name> FROM <tablename>

Example :

SELECT * FROM subject;

Syntax above is used to select all record and column values from table subject. This form of selection make redundancy possible to happen.

1. SELECT with Math Function

- The syntax to count the number of row exist in a table is:

SELECT count(*) FROM tablename;

- The syntax to summary value of some rows of certain column is :

SELECT SUM(column_name) FROM tablename;

- The syntax to result average value of some records of certain column in a table is :

SELECT AVG(column_name) FROM tablename;

- To get maximum value of certain column in a table, we can use :

SELECT MAX(column_name) FROM tablename;

- To get minimum value of certain column in a table, we can use :

SELECT MIN(column_name) FROM tablename;

2. Distinct

Distinct is used to view different specific record based on spesific column. See example
below :

SELECT DISTINCT name FROM subject;

Such query show column name value with no repetition in name value (the number of record for specific name value is one). Distinct is only used for selection of one column. For selection more than two columns, see the example below :

SELECT DISTINCT * FROM student GROUP BY sex ;

Different form the previous query, the above SELECT query example is complemented with group by keyword. This is caused of DISTINCT can only be implemented in one column. So no matter how many column selected, there only one column is allowed to be distinct and any others columns showed based on this distinct will be grouped by the column after GROUP BY keyword. The result of query is all column and record values in table student will be selected and no same value in column sex repeated.

3. Operator Relational and Logic

Beside FROM, the compliment of SELECT query is where. Where is used to filter selection condition so that the query SELECT result will be suitable as expected. There are some relational operator such as =, <>, <, >, <=, >=, and logic operator such as AND, OR, XOR used as condition at where clause. Here is given SELECT query with where clause :

SELECT <column_name>
  FROM <tablename>
  WHERE <condition>

Word SELECT is followed by column name choosen to be shown. Word FROM is followed by table name in which such column exist. Word where is followed by one or more than one condition to filter the data value. Next given example of the use of SELECT with where clause :

SELECT student_id
  FROM csp
  WHERE value = “A” AND semester =”Odd”;

The query will be parsed beginning from keyword FROM. FROM krs means all columns and records in table krs will be selected. Then, value = “A” AND semester=”Odd” means all records will be filtered until only value “A” and semester “Odd” left. Last, SELECT student_id means all columns selected first will be filtered again until only student_id column left. There is another operator for where conditional such as between.

See example below :

SELECT student_id
  FROM student
  WHERE 19 <= age AND age <= 22 ;

The above query has the same concept with below query:

SELECT student_id
  FROM student
  WHERE age BETWEEN 19 AND 22; 

Contrary of between is NOT BETWEEN.

4. Operator in and not in

Others conditions that come with where in select are in and not in. In or not in are used to filter the selected record. If value in certain column occur in the list of in or not in then record contains such value will be shown.

SELECT student_id
  FROM student
  WHERE age in (19, 22);

The query above will show student_id record from table student whose age value 19 or 22. If use String conditional then the form of in or not in value will be like example below :

SELECT name
  FROM subject
  WHERE day in (“Monday”, “Wednesday”, “Friday”);

5. Like Operator

Like is only used when the data is String or character. Pay attention to example of query using like below,

SELECT *
  FROM student
  Where name like "%iz%" ;

The syntax above is used to select row from table student containing character 'iz' in the middle of the name. Characters used in like operator are persentage (%) and underscore( _ ).

% -> any string
_  -> any character

Here given some examples of implementation and result of like operator characters in order to ease the understanding of the use of like function :

1. "Rum%" -> "Rumanystring" -> word contain exact Rum character at the beginning of the word..
2. "%fi%" -> "anystringfianystring" -> word contain exact fi character between others characters.
3. " _ _ " -> word with exact two character.
4. " _ _ _ _%" -> word that consist of minimum four characters.

6. Null and Not null

Another MySQL filtering is using keyword null or is not null. Pay attention to query below,

SELECT distinct name
  FROM student
  Where phone is not null ;

Result of such query is all column name values of table student whose phone is not null.

7. Renaming

Renaming is the name change of a table / give a table an alias. This renaming only effect the current query. After the query executed the given alias of the table or column is not set again. Renaming usually done to shorten a name of table or column.

SELECT colleger.name, colleger.id as registration_number FROM college_student colleger

Name	registration_number
Anita	130001
Budi	130002
Candra	130003

In the query above, shown that mahasiswa table renamed as mhs. Renaming can also used to rename a field, as in the example id renamed as registration_number.

8. Distinct

Distinct use to display unique value in a column. Look at query below.

SELECT courses_id FROM csp

courses_id
MMS0001
MMS0002
MMS0003
MMS0004
MMS0004
MMS0004

SELECT DISTINCT courses_id FROM csp

courses_id
MMS0001
MMS0002
MMS0003
MMS0004

In first query, shown that courses_id column from all rows is displayed. As of second query, only unique value from this column is displayed. In other word, DISTINCT only display rows values with multiple instance only one time.

9. Order by

ORDER BY is a keyword in SQL used to sort displayed data. There are 2 kinds of sorting, descending or ascending. The default sorting used by ORDER BY is ascending. It only sort descending if DESC keyword found after ORDER BY

SELECT * FROM lecture ORDER BY name

lecture_id	name	phone
1230000002	xaverius	811223344
1230000003	yogaswara	822334455
1230000001	zara erawati	833445566

This query will display data that sorted by name column in ascending order.

SELECT lecture_id, name, phone FROM lecture ORDER BY name DESC

lecture_id	name	phone
1230000001	zara erawati	833445566
1230000003	yogaswara	822334455
1230000002	xaverius	811223344

Look at query above, it show lecture data sorted by name in descending order.

10. Limit

This query is used to limit the data displayed. Construct of this function as follow:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

At first, take a look at query below:

SELECT * FROM room

Code	Building	Status
M2.01	science south	Good
T2.00	science south	Good
T2.01	science south	Good
U2.00	science south	Good
U2.01	science south	Good

Query below is one of query instance with LIMIT function.

SELECT * FROM ruang LIMIT 2

Code	Building	Status
M2.01	science south	Good
T2.00	science south	Good

Note that this query display 2 first of room data.

SELECT * FROM room LIMIT 1,2

Code	Building	Status
M2.01	science south	Good
T2.00	science south	Good

The execution of above query return 2 of room data which is second data and third data. (LIMIT 1,x. result in second data as beginning of result, cause of the numbering of data is begin with index 0)

See Also : Introduction Algorithms and Data Structures

11. Inner Join

Inner join is a join with ON clause that must be provided as well in the query. If ON clause not provided the execution of query will be terminated and an error message returned.

SELECT name, day, hour, room.code, building FROM courses INNER JOIN room ON room.code = courses.room_code

name	day	hour	Code	Building
Citizenship	Wednesday	07.00	T2.01	science south
Programming algorithm	Thursday	13.00	U2.01	science south
Calculus	Monday	09.00	M2.01	science south
Introduction to IT	Tuesday	12:00	T2.00	science south

12. Straight Join

Straight join is identical with join, except in straight join ON clause is optionally provided.

SELECT csp.nim, registration_number, courses_id, grade FROM college student STRAIGHT JOIN csp

registration_number	name	courses_id	grade
130001	anita	MMS0004	C
130001	budi	MMS0004	C
130001	candra	MMS0004	C
130002	anita	MMS0004	B
130002	budi	MMS0004	B
130002	candra	MMS0004	B
130003	anita	MMS0004	A
130003	budi	MMS0004	A
130003	candra	MMS0004	A

13. Left (Outer) Join

Left outer join display table in the right side of operation (room) with null, if there are no relation with left table (courses). To do this you must edit the room field in the subject Introduction to IT as NULL.

SELECT name, day, hour, room.code, building FROM courses LEFT JOIN room ON room.code = courses.room_code

name	day	hour	Code	Building
Citizenship	Wednesday	07.00	T2.01	science south
Programming algorithm	Thursday	13.00	U2.01	science south
Calculus	Monday	09.00	M2.01	science south
Introduction to IT	Tuesday	12:00	NULL	NULL

14. Right (Outer) Join

RIGHT JOIN is the exact opposite of LEFT JOIN, if the right join have no relation with left table it will shown as NULL. You have to add a row to table dosen to before execute this query.

SELECT m.registration_number, m.name, d.lecture_id, d.name FROM college_student m RIGHT JOIN lecture d ON m.supervisor = d.lecture_id

registration_number	name	lecture_id	name
130003	candra	1230000001	zara erawati
130001	anita	1230000002	xaverius
130002	budi	1230000003	yogaswara
NULL	NULL	1230000004	mercedes

15. SELECT with more than 2 tables

SQL not limit table which can be mentioned in a SELECT statement. Basic rule to create a JOIN statement is quite generic. First you have to list which table is involved then you have determine the relation among them.

SELECT m.registration_number, m.name, course.name, k.grade FROM college_student m
INNER JOIN csp k ON m.registration_number = k.registration_number
INNER JOIN courses course ON k.courses_id = course.code
WHERE m.registration_number = ‘130001’

registration_number	name	name	grade
130001	anita	Citizenship	B
130001	anita	Programming algorithm	A
130001	anita	Calculus	B
130001	anita	Introduction to IT	C

Query above display courses’s data that belongs to student with registration_number 130001 along with its schedule. This query involved 3 tables, college_student, csp and course. There are 3 table mentioned, so there are 2 instance of INNER JOIN keyword in the query, or N-1 (where N is number of tables involved).

The number of tables that can be involved in a query can be adjusted as practician want. In the example above where are joining about 3 tables, in the next example the tables involved will be 4.

SELECT m.registration_number, m.name, course.name, course.room_code, d.name FROM college_student m
INNER JOIN krs k ON m.registration_number = k.registration_number
INNER JOIN courses course ON k.courses_id = course.code
INNER JOIN lecture d ON course.supervisor = d.lecture_id
WHERE m.registration_number = '130001' AND k.year = '2013' AND k.semester = 'odd'

registration_number	name	name	code	name
130001	anita	Algoritma Pemrograman	U2.01	xaverius
130001	anita	Kalkulus	M2.01	yogaswara

Query above used to display subject that taken by student with registration_number 130001 on odd semester of 2013. This query is example of joining 4 tables as college_student, csp, courses and lecture.

16. Subquery

Nested query or also known as subquery is a query when there are two or more SELECT keyword. Look at this example:

SELECT m.registration_number, m.name FROM college_student m
JOIN (SELECT DISTINCT
registration_number    FROM csp
    WHERE year = '2012') SUB
  ON SUB.registration_number = m.registration_number

Query above display registration_number and name student that have take a subject on 2012. This query also equal with:

SELECT m.registration_number, m.name
FROM college_student m
WHERE registration_number IN (SELECT DISTINCT registration_number
  FROM csp
  WHERE year = '2012')

registration_number	name
130001	anita
130002	budi
130003	candra

17. Aggregat Function and GROUP BY

Aggregat function is a mathematical function in SQL. Aggregation only can be used towards columns with simple type (ex. Integer, shortint). Aggregation function is described as below:

• SUM -> the total sum of a column
• AVG -> average value of a column
• MAX -> the maximal value of a column
• MIN -> the minimal value of a column
• COUNT -> the number of instance in a column

GROUP BY function used to group data based on a column or more. This function is used to facilitate the Aggregation function.

SELECT
  m.registration_number,
  m.name,
  k.year,
  k.semester,
  COUNT(*) sum_of_courses
FROM college_student m
JOIN csp k ON k.registration_number = m.registration_number
GROUP BY k.registration_number, k.year, k.semester

registration_number	name	year	semester	sum_of_courses
130001	anita	2012	odd	1
130001	anita	2012	even	1
130001	anita	2013	odd	2
130002	budi	2012	odd	1
130003	candra	2012	odd	1

18. HAVING

Having is a complementary function of aggregation and can be used along with GROUP BY. The function is to filter displayed data with specific parameter.

SELECT
  m.registration_number,
  m.name,
  k.year,
  k.semester,
  COUNT(*) sum_of_courses
FROM college_student m
JOIN csp k ON k.registration_number = m.registration_number
GROUP BY k.registration_number, k.year, k.semester
HAVING sum_of_courses > 1

registration_number	name	year	semester	sum_of_courses
130001	anita	2013	odd	2

Query above is modification from previous query. the different between them is, in this query the student displayed is filtered only student who have take subject more than 2.

19. Exist

Keyword Exist is used on filtering after where keyword. Usually used on nested query. Look at the example below:

SELECT id_member,name
  FROM buyer
  WHERE EXIST (SELECT id_member
FROM purchase);

20. View

View began exist since MySQL version 5.0. View is used to simplify SQL query and to limit fields required when accessing the table. View is like table, but the data comes from another table. The syntax of view is :

CREATE VIEW <name view> AS <select statement>;

Look at the sample query below :

CREATE VIEW big_purchase
 AS
SELECT A.name, B.no_purchase, sum(B.total)
FROM buyer A, purchase B
WHERE A.id_member =B.id_member
GROUP BY A.name
HAVING SUM(B.total)>500000 

The above query will create view that show buyer name, bill number, and total purchase > 500000. Just like on table, we can do query on view, but only select QUERY.

EXERCISE

1. Look at back to the exercise of chapter III. For each table created :
   a) Insert data into each proper table
   b) Update data on student whose sex and student_id in certain condition (the value to be inserted handed over to the user).
   c) Show course name which have credit value more than 2 and held on Wednesday.
   d) Show the number of student whose sex is women.
   e) Show all student name whose name prefixed with R.
2. Show rooms that used as teaching room on odd semester 2013!
3. Show subject taken by 130001 student on odd semester 2013 with his lecture name!
4. Show subject that not taken by student 130001!
5. Create a view to display the name of the student, courses taken and its value!