Bài giảng Hệ quản trị cơ sở dữ liệu - Bài 4: Hàm và các kiểu kết nối và câu truy vấn con

 Bài 4: Hàm và các kiểu kết nối và câu truy vấn conMục tiêu: Cách dùng hàm xử lý chuỗi trong câu truy vấnCách dùng hàm xử lý thời gian trong câu truy vấnCách dùng hàm xử lý toán học trong câu truy vấn Sử dụng các kiểu kết nối inner join Sử dụng các kiểu kết nối outter join Sử dụng các kiểu kết nối self joinSử dụng câu truy vấn conLưu kết quả câu truy vấn vào bảng tạm1 Hàm và truy vấn dữ liệuHiển thị kết quả câu truy vấn bằng chữ hoaTrong SQL, nếu ta muốn kết quả của câu truy vấn được chuyển sang toàn chữ hoa thì ta có thể dùng hàm upper.Cách dùng hàm trong câu lệnh select có cú pháp như sau:	SELECT function_name (parameters)Ví dụ demo: 	Viết câu truy vấn hiển thị tên của tờ báo, tên người liên lạc và số điện thoại liên lạc để công ty liên hệ quảng cáo. Tên của tờ báo phải được hiển thị bằng chữ hoa.	SELECT 'Newspaper Name‘ = UPPER(cNewspaperName), vContactPerson,cPhone FROM Newspaper2 Hàm và truy vấn dữ liệuMột số hàm xử lý chuỗi cơ bảnASCII STR REVERSE NCHAR DIFFERENCE UNICODE SOUNDEX QUOTENAME LOWER CHAR STUFF RIGHT PATINDEX LEFT UPPER SPACE REPLICATE LTRIM CHARINDEX SUBSTRING RTRIM REPLACE LEN 3 Hàm và truy vấn dữ liệuHàm xử lý ngày giờ (Datetime function)Hàm xử lý ngày tháng được dùng để xử lý các giá trị về thời gian, thực hiện một số phép tóan như cộng trừ thời gian hoặc có các hàm để lấy một phần thông tin của thời gian như: ngày, tháng, năm.Cách dùng hàm datetime trong câu lệnh select có cú pháp như sau:	SELECT datetime_function (parameters)Ví dụ demo: 	Ngày hết hạn dự kiến tuyển dụng là 10 ngày từ khi bắt đầu quá trình tuyển dụng. Viết câu truy vấn dữ liệu hiển thị mã trường, ngày bắt đầu tuyển dụng, ngày hết hạn cho tất cả các trường mà công ty định tuyển sinh viên.	SELECT cCollegeCode, dRecruitmentStartDate, 'Proposed Deadline' =	DATEADD(dd, 10, dRecruitmentStartDate)	FROM CampusRecruitment4 Hàm và truy vấn dữ liệuMột số hàm xử lý datetimeTên hàmTham sốDATEADD(datepart, number, date)DATEDIFF(datepart, date1, date2)DATENAME(datepart, date)DATEPART(datepart, date)GETDATE()5 Hàm và truy vấn dữ liệuHàm toán họcHàm toán học dùng để thực hiện các phép tính trên giá trị trả về của câu truy vấn Cách dùng hàm toán học trong câu lệnh select có cú pháp như sau:	SELECT function_name (parameters)Ví dụ demo: 	Viết câu truy vấn hiển thị tên, số điện thoại và điểm phỏng vấn của ứng viên có tên là Helen. Điểm phải được làm tròn.	SELECT vFirstName, cPhone,'Score'= ROUND(79.9,0)	FROM ExternalCandidate	WHERE vFirstName='Helen'6 Hàm và truy vấn dữ liệuMột số hàm toán học cơ bảnTên hàmTham sốABS(numeric_expression)ACOS, ASIN,ATAN(float_expression)COS, SIN,COT, TAN(float_expression)ROUND(numeric_expression,length)RAND([seed])7 Hàm và truy vấn dữ liệuKết nối (Join)Toán tử JOIN được dùng trong các câu truy vấn lấy dữ liệu từ nhiều bảng trong cùng thời điểm.Cú pháp cho toán tử JOIN như sau:SELECT column_name, column_name [,column_name]FROM table_name [CROSS|INNER|[LEFT | RIGHT]OUTER] JOIN	table_name	[ON table_name.ref_column_name join_operator	table_name.ref_column_name][WHERE search_condition]8 Hàm và truy vấn dữ liệuJoin (Tiếp theo)Có nhiều kiểu kết nối khác nhau:Inner JoinOuter JoinCross JoinEqui JoinNatural JoinSelf Join9 Hàm và truy vấn dữ liệuHiển thị dữ liệu từ hai bảng dùng inner joinInner join được dùng để hiển thị dữ liệu từ hai hay nhiều bảng bằng cách so sách giá trị của những cột giống nhau, nếu dòng nào có giá trị của hai cột trên bằng nhau thì sẽ được hiển thị.Ví dụ minh họa:	Viết câu truy vấn hiển thị tên của ứng viên và tên của văn phòng tuyển dụng ứng viên này?	SELECT 'Candidate Name'=vFirstName,'Recruitment Agency' = cName	FROM ExternalCandidate JOIN RecruitmentAgencies	ON ExternalCandidate.cAgencyCode=RecruitmentAgencies.cAgencyCode10 Hàm và truy vấn dữ liệuKết nối dữ liệu dùng outter joinOutter join hiển thị tất cả các dòng trên 1 bảng, riêng bảng còn lại chỉ hiển thị những dòng được so trùng với bảng kia.Ví dụ minh họa:	Viết câu truy vấn hiển thị tên của ứng viên bên ngòai và tên của văn phòng tuyển dụng cho ứng viên này nếu có.SELECT vFirstName, vLastName, cNameFROM ExternalCandidateLEFT OUTER JOIN RecruitmentAgencies ON ExternalCandidate.cAgencyCode=RecruitmentAgencies.cAgencyCode	11 Hàm và truy vấn dữ liệuKết nối dữ liệu trong cùng một bảng (Self Join)Self Join là kiểu kết nối mà một hàng trong bảng kết nối với hàng khác trong cùng một bảng.Ví dụ minh họa:	SELECT t1.title,t2.title , t1.priceFROM titles t1 JOIN titles t2 ON t1.price=t2.priceWHERE t1.price=2.99	12 Hàm và truy vấn dữ liệuCâu truy vấn con (Sub query)Câu truy vấn được dùng trong các lệnh SQL như SELECT, UPDATE, INSERT và DELETE được gọi là câu truy vấn con.Câu truy vấn con được dùng để truy xuất dữ liệu từ một hoặc nhiều bảng nên được dùng để thay thế toán tử Join.Câu truy vấn con còn được sử dụng trong các mệnh đề WHETE và HAVING.Khi thực thi câu lệnh SQL có câu truy vấn con, câu truy vấn con sẽ được thực thi trước, sau đó mới đến câu lệnh truy vấn bên ngòai.	13 Hàm và truy vấn dữ liệuVí dụ minh họa câu truy vấn conHãy viết câu truy vấn hiển thị tên các nhà tuyển dụng sống cùng thành phố với ứng viên từ bên ngoài có tên: Barbara Johnson.	SELECT cName	FROM ContractRecruiter	WHERE cCity =	(SELECT cCity FROM ExternalCandidate	WHERE vFirstName = 'Barbara' AND vLastName = 'Johnson')	14 Hàm và truy vấn dữ liệuCâu truy vấn con với mệnh đề INNếu câu truy vấn con trả về hơn 1 giá trị thì người ta phải dùng mệnh đề IN thay cho toán tử so sánh bằng (=)Ví dụ minh họa: Viết câu truy vấn trả về mã tác giã có sách bán được? 	SELECT Au_Id	FROM TitleAuthor	WHERE Title_Id IN (SELECT Title_Id FROM Sales)	15 Hàm và truy vấn dữ liệuCâu truy vấn con với mệnh đề EXISTSCâu truy vấn con được dùng với mệnh đề EXISTS để kiểm tra sự tồn tại của một dữ liệu nào đó hay không? Mệnh đề EXISTS trả về 2 giá trị TRUE hoặc FALSE.Ví dụ minh họa: Viết câu truy vấn trả về tên nhà xuất bản có xuất bản sách thuộc thể loại business.	SELECT Pub_Name	FROM Publishers	WHERE EXISTS (	SELECT * FROM Titles	WHERE Type = 'business')	16 Hàm và truy vấn dữ liệuCâu truy vấn con được dùng với hàm nhómCâu truy vấn con được dùng với hàm nhómVí dụ minh họa: Hiển thị tên các sách có tiền đặt trước lớn hơn số tiền đặt trước trung bình của thể loại ‘Business’.	SELECT Title	FROM Titles	WHERE Advance > (SELECT AVG(Advance)	FROM Titles	WHERE Type = 'business')	17 Hàm và truy vấn dữ liệuLưu kết quả câu truy vấn vào bảngKết quả của câu truy vấn có thể được lưu vào bảng bằng mệnh đề SELECT  INTOVí dụ minh họa: Hãy viết câu truy vấn lưu thông tin các ứng viên bên ngoài trong tháng 5 năm 2006 vào bảng tạm có tên là TempExternalCandidate	SELECT * INTO tempExternalCandidate 	FROM ExternalCandidate	WHERE DATEPART(mm,dDateOfApplication)= 5	AND DATEPART(yyyy,dDateOfApplication)= 2001	18 Hàm và truy vấn dữ liệuTóm tắtTrong bài này bạn đã học: Cách dùng hàm xử lý chuỗi trong câu truy vấnCách dùng hàm xử lý thời gian trong câu truy vấnCách dùng hàm xử lý toán học trong câu truy vấn Sử dụng các kiểu kết nối inner join Sử dụng các kiểu kết nối outter join Sử dụng các kiểu kết nối self joinSử dụng câu truy vấn conSử dụng câu truy vấn con với mệnh đề INSử dụng câu truy vấn con với mệnh đề EXITSSử dụng câu truy vấn con với hàm nhómLưu kết quả câu truy vấn vào bảng tạm	19 Hàm và truy vấn dữ liệuBài tậpBài 1: Viết câu truy vấn hiển thị tên, mô tả và đánh giá cho tất cả các đồ chơi trẻ em, tuy nhiên chỉ hiển thị 40 ký tự đầu tiên của phần mô tả.Bài 2: Viết câu truy vấn hiển thị các thông tin về trình trạng giao hàng theo mẩu sau:Bài 3: Viết câu truy vấn hiển thị thông tin về hóa đơn có mã là 00009 theo mẩu sau:	20 Hàm và truy vấn dữ liệuBài tậpBài 4: Viết câu truy vấn hiển thị tên của tất cả các loại đồ chơi và tên của thể loại mà nó thuộc về.Bài 5: Lưu thông tin các đồ chơi có giá lớn hơn 20 USD vào bảng PremiumToys.21 Hàm và truy vấn dữ liệu