Fresher Training Program Relational Database Management System

1. Fresher Training ProgramRelational Database Management System Nguyen Minh Tien

2. Contents • DDL statements • DML statements

3. Learning approach • The following are strongly suggested for a better learning and understanding of this course: • Noting down the key concepts in the class • Analyze all the examples / code snippets provided • Study and understand the self study topics • Completion and submission of all the assignments, on time • Completion of the self review questions in the lab guide • Study and understand all the artifacts including the reference materials / e-learning / supplementary materials specified • Completion of the project (if application for this course) on time inclusive of individual and group activities • Taking part in the self assessment activities • Participation in the doubt clearing sessions

4. SQL – Structure Query Language • SQL is a language that all commercial RDBMS implementations understand. • SQL is a non-procedural language

5. Data Types • String data • CHAR(n) – string type with fixed length include n characters (maximum 2000 bytes). • VARCHAR2(n) – string type with changeable length include n characters (maximum 4000 bytes). • LONG – string with changeable length, maximum 4GB (only one column in a table). • Numeric data • NUMBER(p,q) – kiểu số có p ký số và q số lẻ. • INTEGER(p) – kiểu số nguyên có p ký số. • Date-time data • DATE – datetime type with fixed length in dd-mm-yy format

6. Constants/Literals • ANSI standard defines format for literals • Numeric: 21, -32, $0.75,1.2E4 • String: enclosed within ‘ …’ • Date : 12-mar-03*

7. Comparision Conditions

8. Logical Conditions

9. NULL • Missing/unknown/inapplicable data represented as a null value • NULL is not a data value. It is just an indicator that the value is unknown

10. Statements • DDL: Data definition language (CREATE, ALTER, DROP, TRUNCATE) • DML: Data manipulation language (SELECT, INSERT, UPDATE, DELETE) • DCL: Data control language (COMMIT, ROLLBACK, SET TRANSACTION, GRANT, REVOKE)

11. SQL – DDL Data Definition Language

12. CREATE TABLE • CREATE TABLE used to create structure of table. • Syntax: CREATE TABLE <table name> (<column_definition>, … [<table_constraint_definition>]);

13. CREATE TABLE • Steps of creating a table • Step 1. Define data column. • Step 2. Column may contain NULL value??. • Step 3. Define columns has unique data (candidate key). • Step 4. Define primay key – foreign key. • Step 5. Define default values. • Step 6. Define constraint. • Step 7. Create table & index.

14. CREATE TABLE – CHECK constraint • CHECK constraint: used to specify constraint conditions to data. Whenever data changed (INSERT, UPDATE), these constraints are used to verify regular data. • Syntax: [CONSTRAINT tên_ràng_buộc] CHECK (điều_kiện)

15. CREATE TABLE – PRIMARY constraint • PRIMARY constraint: used to specify primary key of table. • Syntax: [CONSTRAINT tên_ràng_buộc] PRIMARY KEY [(danh_sách_cột)]

16. CREATE TABLE – UNIQUE constraint • UNIQUE constraint: used to define candidate keys for the table. • Syntax: [CONSTRAINT tên_ràng_buộc] UNIQUE [(danh_sách_cột)]

17. CREATE TABLE – FOREIGN • FOREIGN constraint: used to define relationships between tables in the database. • Syntax: [CONSTRAINT tên_ràng_buộc] FOREIGN KEY [(danh_sách_cột)] REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu) [ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT] [ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

18. CREATE TABLE - Exp CREATE TABLE lop ( malop NVARCHAR(10) NOT NULL, tenlop NVARCHAR(30) NOT NULL, khoa SMALLINT NULL, hedaotao NVARCHAR(25) NULL, namnhaphoc INT NULL, makhoa NVARCHAR(5), CONSTRAINT pk_lop PRIMARY KEY (malop), CONSTRAINT unique_lop_tenlop UNIQUE(tenlop) )

19. ALTER TABLE • ALTER TABLE • ALTER TABLE is used to change table structure: add/remove/update/change columns and constraints Syntax: ALTER TABLE <Table_Name> ALTER COLUMN [<Column_name> <New_data_type>]| ADD [<Column_name> <Data_Type>] | DROP COLUMN [<Column _Name>] With <Table_Name> table name should be changed <ALTER COLUMN> change column <Column_Name> column name need to add, change or remove <New_data_type> date type changed to column. ADD add new column to table. DROP COLUMN remove column from table.

20. ALTER TABLE - Exp CREATE TABLE nhanvien ( manv NVARCHAR(10) NOT NULL, hoten NVARCHAR(30) NOT NULL, ngaysinh DATETIME, diachi CHAR(30) NOT NULL ) ALTER TABLE nhanvien ADD dienthoai NVARCHAR(6) CONSTRAINT chk_nhanvien_dienthoai CHECK (dienthoai LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

21. DROP TABLE • DROP TABLE • DROP TABLE statement is used to delete a table from DB, all relevant objects are removed: indexes, Triggers, Constraints. Syntax: With, <Table_Name> table name removed DROP TABLE <Table_Name>

22. DROP TABLE - Exp • Remove constraint firstly ALTER TABLE nhanvien DROP CONSTRAINT fk_nhanvien_madv • Remove table: DROP TABLE donvi

23. SQL – DML Data Manipulation Language

24. Data Manipulation Language - DML • DML includes the following statements: SELECT, INSERT, UPDATE, DELETE, used to manipulate data in the database. • DML’s statements: • SELECT statement • INSERT statement • UPDATE statement • DELETE statement

25. INSERT • Syntax: INSERT INTO tablename VALUES (value list) Exp: • Single-row insert INSERT INTO Sinhvien VALUES(‘SV3’,’SUP3’,’BLORE’,10) • Inserting one row, many columns at a time INSERT INTO Sinhvien (MaSV, Hoten) VALUES (‘SV3’, ‘Smith’); • Inserting many rows, all/some columns at a time. INSERT INTO luusinhvien SELECT hodem,ten,ngaysinh FROM sinhvien WHERE noisinh like ‘%Huế%’

26. Specify column name in INSERT • Must specify column’s name in INSERT statement • INSERT INTO Table_A (Col1, Col2, Col3) VALUES (‘1’, ‘2’, ‘3’)

27. UPDATE • Syntax: UPDATE tablename SET column_name =value [ WHERE condition] • Exp: UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’ UPDATE EMP SET SAL = 1.10 * SAL

28. Use CASE in UPDATE statement UPDATE nhatkyphong SET tienphong=songay*CASE WHEN loaiphong='A' THEN 100 WHEN loaiphong='B' THEN 70 ELSE 50 END

29. UPDATE data from tables UPDATE nhatkybanhang SET thanhtien = soluong*gia FROM mathang WHERE mathang.mahang =(SELECT mathang.mahang FROM mathang WHERE mathang.mahang = nhatkybanhang.mahang)

30. DELETE • Syntax: DELETE FROM tablename WHERE condition Exp: • DELETE FROM SP WHERE PNO= ‘P1’ • DELETE FROM SP

31. Use sub-query in DELETE DELETE FROM sinhvien FROM lop WHERE lop.malop=sinhvien.malop AND tenlop='Tin K24' DELETE FROM lop WHERE malop NOT IN (SELECT DISTINCT malop FROM sinhvien)

32. Delete all data DELETE FROM diemthi Or TRUNCATE TABLE diemthi

33. SELECT • Syntax: SELECT [ALL/DISTINCT] <Column name1>, <Column name2>, … FROM <Table name> [WHERE <Search condition>] [GROUP BY grouping columns] [HAVING search condition] [ORDER BY sort specification] Exp: SELECT SNAME, CITY FROM S

34. Change column title SELECT 'Mã lớp'= malop,tenlop 'Tên lớp', khoa AS 'Khoá' FROM lop

35. Use CASE structure in SELECT • Syntax: CASE biểu_thức WHEN biểu_thức_kiểm_tra THEN kết_quả [ ... ] [ELSE kết_quả_của_else] END SELECT masv,hodem,ten, CASE gioitinh WHEN 1 THEN 'Nam' ELSE 'Nữ' END AS gioitinh FROM sinhvien

36. Using constant & Expression SELECT tenmonhoc,'Số tiết: ',sodvht*15 AS sotiet FROM monhoc

37. DISTINCT & TOP • SELECT DISTINCT khoa FROM lop • SELECT TOP 5 hodem,ten,ngaysinh FROM sinhvien • SELECT TOP 10 PERCENT hodem,ten,ngaysinh FROM sinhvien

38. IN and NOT IN SELECT * FROM monhoc WHERE sodvht=2 OR sodvht=4 OR sodvht=5 May be replaced by: http://www.sqlviet.com/blog/cac-loai-join-trong-sql-server SELECT * FROM monhoc WHERE sodvht IN (2,4,5)

39. LIKE and NOT LIKE

40. LIKE and NOT LIKE SELECT hodem,ten FROM sinhvien WHERE hodem LIKE 'Lê%' AND ten LIKE '[AB]%'

41. Create new table from SELECT SELECT hodem,ten,YEAR(GETDATE())-YEAR(ngaysinh) AS tuoi INTO tuoisv FROM sinhvien

42. Specify data existing in a table • Do not use COUNT(*) to specify a table has data or not: SELECT COUNT(*) FROM Table_A • Must use: SELECT TOP 1 FROM Table_A

43. Sorting • Syntax: SELECT COL1,COL2,....... FROM TABLE_NAME WHERE <SEARCHCONDITION> ORDER BY COL-NAME [DESC] Example: SELECT CITY,COLOR,WEIGHT FROM P WHEREWEIGHT IN (12,17) ORDER BY 1 DESC, 2

44. UNION • UNION is used to add data from many tables into one table • Syntax: UNION [ALL] Câu_lệnh_2 [UNION [ALL] Câu_lệnh_3] ... [UNION [ALL] Câu_lệnh_n] [ORDER BY cột_sắp_xếp] [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]]

45. INTERSECT • INTERSECT operator return all rows that are common to multiple queries • The number of columns and the data types of the columns in the queries must be identical in all the SELECT statements used in the query. • Reversing the order of the intersected tables does not alter the result • INTERSECT does not ignore NULL values

46. INTERSECT • Display the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired (that is, they changed jobs but have now gone back to doing their original job) SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;

47. MINUS • MINUS operator returns rows returned by the first query that are not present in the second query • The number of columns and the data types of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. • All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work

48. MINUS • Display the employee IDs of those employees who have not changed their jobs even once SELECT employee_id, job_id FROM employees MINUS SELECT employee_id, job_id FROM job_history;

49. JOIN • Inner join • Cross join • Outer join • Left-outer join • Right-outer join • Self join

50. INNER JOIN • Common type of join • Combines records from two tables with matching values on a column. • Example: SELECT hodem,ten,ngaysinh FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE tenlop='Tin K24'