learning Data Manipulation Language (DML)

Data Manipulation Language (DML)
ADD DATA

INSERT INTO table_name
VALUES (value_1, value_2 ,..., nilai_n)

OR

INSERT INTO table_name (kolom_1, kolom_2 ,..., Kolom_n)
VALUES (value_1, value_2 ,..., nilai_n);
NOTE

Fields with are mandatory NOT NULL konstraint
Writing data to the type of numbers are not enclosed in quotation marks
Writing data to the type of string or a date enclosed in quotation marks
Data on the child table (foreign key) must exist in the parent
EXAMPLE
INSERT INTO STUDENT VALUES (2001, 1, 'ANITA', 'MAGELANG', '1-JAN-85 ');
INSERT INTO STUDENTS (Thang, NIM, NAME) VALUES (2005, 9, 'David');

CHANGE DATA

UPDATE table_name
SET
kolom_1 = nilai_baru_1,
kolom_2 = nilai_baru_2,
...
kolom_3 = nilai_baru_n
[WHERE condition]
Change all the rows, one column
Change all thmasuk to 2003
UPDATE students
SET thmasuk = 2003;
Changing a particular column for a particular row
change the column name to Marani Anita in the table of his students who NIM = 200
UPDATE students
SET name = 'Anita Marani'
WHERE NIM = 200;
Change multiple columns at once
UPDATE Student
SET
name = 'Anita Mariana',
thmasuk = 2000
WHERE NIM = 200;
Operator Condition
Relational Operators (=,>, <,> =, <=, <>)
Boolean Operators (And, Or, Not)
UPDATE class
SET NIP = 1
WHERE CODE = 'P1'
AND NIP = 2
Between and Not Between
UPDATE class
SET NIP = 1
WHERE idkelas BETWEEN 1 AND 3
Operator Conditions (Cont ...)
In and Not In
UPDATE class
SET NIP = 1
Idkelas WHERE IN (1,2,3)
Is Null and Is Not Null
UPDATE Student
SET DATE = '21-Aug-89 '
WHERE date IS NULL
Like and Not Like
Wildcard
%  some arbitrary character
_  one arbitrary character
[]  any one character in the range
[^]  any single character outside the range

Sample Data: Erike, Mirna, Ani, Kaka, Ubaid
LIKE '% a': Mirna, Kaka
LIKE '% a%': Mirna, Kaka, Ubaid
LIKE '_a%': Kaka
LIKE '% [a-k] a%': Kaka
LIKE '% [^ a-k] a%': Mirna

Example Queries:

UPDATE Student
SET
Address = Null
WHERE Name LIKE '% a%';