Introduction to DBMS and SQL Course Web Design

Introduction to DBMS and SQL Course: Web Design and Development II Lecturer: SOK Phearin Email: phearin. sok@gmail. com Tel: (+855) 92 990 233 / (+855) 86 299 606

Data vs. Information

មលដឋ នទនននយ • យ ងអ ចនយ យប នមយ ងទ តថ មលដឋ នទនននយ (Database) ជ សន ដ លព កពនធគន ។ ន ត រ ងទនននយ(Table) Table Name: tbl. Scores Table Name: tbl. Students Student. ID Name Gender Address 1001 Dara Male Phnom Penh 1002 Lena Female Battambang 1003 Dalis Female Kompong Cham Score. ID Stu. ID Subject. ID Score 1 1001 CS 101 92 2 1001 CS 102 94 3 1002 CS 101 93 4 1002 AC 101 95 Subject. ID Subject. Name Department CS 101 Web Design 1 Computer Science CS 102 Web Design 2 Computer Science AC 101 Basic Accounting Acounting Table Name: tbl. Subjects

បរភ ទទនននយ ជ ទទ មលដឋ នទនននយអ ចរកស ផទកទនននយដ លម នបរភ ទសរដ ងគន ដចខ ងករ ម (My. SQL) Column Data TINYINT -128 to 127, or 0 to 255 SMALLINT -32768 to 32767, or 0 to 65535 MEDIUMIN -8388608 to 8388607, or 0 to 16777215 INT -2147483648 to 2147483647, or 0 to 4294967295 BIGINT -9223372036854775808 to 9223372036854775807, or 0 to 18446744073709551615 FLOAT Floating-point number, single-precision DOUBLE Floating-point number, double-precision CHAR Fixed-length strings up to 255 characters in size VARCHAR Variable-length strings up to 255 characters in size TEXT Huge, variable-length strings, case-insensitive BLOB Huge, variable-length strings, case-sensitive DATETIME Date and time in the format YYYY-MM-DD HH: MM: SS

SELECT Statement • SELECT ������������������ SELECT column_name 1, column_name 2 FROM table_name; ឬ SELECT * FROM table_name; v ឧទ ហរណ ៖ o SELECT * FROM tbl. Students; o SELECT studentid, name FROM tbl. Students; o SELECT stubjectid, subjectname, department FROM tbl. Subjects;

WHERE Clause • WHERE ���������������� SELECT column_name 1, column_name 2 FROM table_name WHERE column_name operator value; v ឧទ ហរណ ៖ o SELECT * FROM tbl. Students WHERE studentid=1001; o SELECT studentid, name FROM tbl. Students WHERE gender=‘Female’; o SELECT stubjectid, subjectname, department FROM tbl. Subjects WHERE department=‘Computer Science’;

WHERE Clause • Operator ����� WHERE ������

WHERE Clause v ឧទ ហរណ ៖ o SELECT * FROM tbl. Students WHERE age BETWEEN 18 AND 30; o SELECT stubjectid, studentid FROM tbl. Scores WHERE score > 92; o SELECT studentid, name FROM tbl. Students WHERE name LIKE ‘D%’ AND gender=‘Female’; o SELECT * FROM tbl. Students WHERE address=‘Phnom Penh’ OR address=‘Battambang’;

ORDER BY Clause • ORDER BY ����������������� SELECT column_name 1, column_name 2 FROM table_name ORDER BY column_name ASC | DESC; v ឧទ ហរណ ៖ o SELECT * FROM tbl. Scores ORDER BY score DESC; o SELECT * FROM tbl. Students ORDER BY name DESC; o SELECT studentid, name FROM tbl. Students ORDER BY name ASC; o SELECT studentid, name FROM tbl. Students where gender=‘Male’ ORDER BY age;

INSERT INTO Statement • INSERT INTO ���������������� INSERT INTO table_name VALUES (value 1, value 2, value 3, …); ឬ INSERT INTO table_name (colmn 1, column 2, …) VALUES (value 1, value 2, …); v ឧទ ហរណ ៖ o INSERT INTO tbl. Students VALUES (1004, ‘Davin’, ‘Male’, ‘Kompong Thom’, 19); o INSERT INTO tbl. Subjects VALUES (‘EB 201’, ‘English for Biz’, ‘English’); o INSERT INTO tbl. Subjects (subjectname, department, subjectid) VALUES (‘Finance Management’, ‘Account’, ‘FM 202’);

UPDATE Statement • UPDATE ������������������ UPDATE table_name SET column 1 = value 1, column 2 = value 2, … WHERE condition; v ឧទ ហរណ ៖ o UPDATE tbl. Students SET age=18 WHERE studentid=1004; o UPDATE tbl. Students SET name=‘Vimean’ WHERE studentid=1004; o UPDATE tbl. Subjects SET subjectname=‘English department=‘Business’ WHERE subjectid=‘EB 201’; for Business’,

DELETE Statement • DELETE ������������������ DELETE FROM table_name WHERE condition; v ឧទ ហរណ ៖ o DELETE FROM tbl. Students WHERE studentid=1004; o DELETE FROM tbl. Students WHERE absence>80; o DELETE FROM tbl. Subjects WHERE department=‘Electronic Engineering’ OR department=‘Information Technology’;

INNER JOIN • INNER JOIN ����������������� SELECT column 1 FROM table 1 INNER JOIN table 2 ON table 1. column_name=table 2. column_name; v ឧទ ហរណ ៖ o SELECT name, score FROM tbl. Students INNER JOIN tbl. Scores ON tbl. Students. studentid=tbl. Scores. studentid; o SELECT subjectname, score FROM tbl. Subjects INNER JOIN tbl. Scores ON tbl. Subjects. subjectid=tbl. Scores. subjectid;