Đưa ra danh sách các nhân viên sinh nhất trong tháng 3 SQL

use master go if exists [select name from sys.databases where name=N'Quanlysinhvien'] drop database Quanlysinhvien go create database Quanlysinhvien use Quanlysinhvien go create table Sinhvien [ Masv int not null primary key identity, Tensv nvarchar[50] not null, Gioitinh nvarchar[5], Ngaysinh date, Que nvarchar[50], Lop nvarchar[5] ] create table Monhoc [ Mamh int not null primary key identity, Tenmh nvarchar[50], DVHT int ] create table Ketqua [ Masv int, Mamh int, Diem int, constraint check_Diem check [Diem between 0 and 10], constraint primary_key primary key [Masv, Mamh], constraint khoaNgoai_SV_KQ foreign key [Masv] references Sinhvien [Masv], constraint khoaNgoai_MH_KQ foreign key [Mamh] references Monhoc [Mamh] ] insert Sinhvien values [N'Phạm Trung Tính', 'Nam', '03/30/1996', N'Quảng Ninh', 'L01'], [N'Trần Bảo Trọng', 'Nam', '12/14/1995', N'Hà Giang', 'L02'], [N'Lê Thùy Dung', N'Nữ', '05/12/1997', N'Hà Nội', 'L03'], [N'Lê Trường An', 'Nam', '11/20/1995', N'Ninh Bình', 'L04'], [N'Phạm Thị Hương Giang', N'Nữ', '2/21/1999', N'Hòa Bình', 'L02'], [N'Đoàn Duy Thức', 'Nam', '4/12/1994', N'Hà Nội', 'L01'], [N'Dương Tuấn Thông', 'Nam', '4/12/1991', N'Nam Định', 'L03'], [N'Lê Thành Đạt', 'Nam', '4/15/1993', N'Phú Thọ', 'L04'] insert Monhoc values [N'Toán cao cấp', 3], [N'Mạng máy tính', 3], [N'Tin học đại cương', 4], [N'Hệ quản trị cơ sở dữ liệu', 2], [N'Cơ sở dữ liệu', 2] insert Ketqua values [1,1,8],[1,2,5],[2,2,1], [3,2,7],[4,2,3],[1,3,7], [2,1,9],[4,1,2],[3,1,4], [2,3,2],[5,1,4],[6,1,2], [6,3,9],[6,2,7],[6,5,10] select * from Sinhvien select * from Monhoc select * from Ketqua /*Câu 1: Cho biết mã số, tên, điểm thi tất cả các môn của sv: abc*/ select mh.Mamh, Tenmh, Diem from Sinhvien sv, Monhoc mh, Ketqua kq where sv.Masv = kq.Masv and mh.Mamh = kq.Mamh and Tensv = N'Phạm Trung Tính' /*câu 2: Cho biết mã số, tên môn và điểm thi ở những môn mà sinh viên abc phải thi lại [điểm [select MIN[Diem] from Ketqua where Mamh = 1] /*câu 6: Cho biết mã số và tên những sinh viên có điểm thi môn 1 lớn hơn điểm thi môn 1 của sinh viên 3*/ select sv.Masv, Tensv from Sinhvien sv, Monhoc mh, Ketqua kq where sv.Masv = kq.Masv and mh.Mamh = kq.Mamh and mh.Mamh = 1 and Diem > [select Diem from Ketqua where Mamh = 1 and Masv = 3] /*câu 7: Cho biết số sinh viên phải thi lại môn Cơ sở dữ liệu */ select COUNT[*] as N'Số sv thi lại' from Monhoc mh, Ketqua kq where mh.Mamh = kq.Mamh and Tenmh = N'Mạng máy tính' and Diem < 5 /*câu 8: Đối với mỗi môn, cho biết tên môn và số sinh viên phải thi lại môn đó mà số sinh viên thi lại >=2*/ select Tenmh, COUNT[Diem] as N'Số sv thi lại' from Monhoc mh, Ketqua kq where mh.Mamh = kq.Mamh and Diem < 5 group by Tenmh having COUNT[Diem] > 2 /*Câu 9: Cho biết điểm cao nhất môn 1 mà các sinh viên đạt được*/ select MAX[Diem] as N'Điểm cao nhất môn 1' from Ketqua where Mamh = 1 /*Câu 10: Cho biết mã số, tên và lớp của sinh viên đạt điểm cao nhất môn Lý thuyết cơ sở dữ liệu */ select sv.Masv, Tensv, Lop from Sinhvien sv, Monhoc mh, Ketqua kq, [select MAX[Diem] as maxDiem from Monhoc mh, Ketqua kq where mh.Mamh = kq.Mamh and Tenmh = N'Cơ sở dữ liệu'] a where sv.Masv = kq.Masv and mh.Mamh = kq.Mamh and Tenmh = N'Cơ sở dữ liệu' and Diem = a.maxDiem /*câu 11: Cho biết sinh viên có điểm trung bình chung >=5 */ select Tensv, AVG[CAST[Diem as float]] as Diemtb from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv group by Tensv having AVG[CAST[Diem as float]] >= 5 /* Lệnh lấy điểm từng môn của sv để check lại điểm */ select Tensv, Tenmh, Diem from Sinhvien sv, Monhoc mh, Ketqua kq where sv.Masv = kq.Masv and mh.Mamh = kq.Mamh /*Câu 12: Với mỗi sinh viên cho biết mã số, tên và điểm trung bình chung học tập của sinh viên đó*/ select sv.Masv, Tensv, AVG[CAST[Diem as float]] as Diemtb from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv group by sv.Masv, Tensv /* câu 13: Đối với mỗi lớp, lập bảng điểm gồm mã số, tên sinh viên và điểm trung bình chung học tập. Sắp xếp danh sách theo chiều giảm dần của điểm trung bình chung học tập và chiều tăng dần của họ tên*/ select sv.Masv, Tensv, AVG[CAST[Diem as float]] as Diemtb from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv and Lop = 'L03' /* Lớp khác thì đổi tên lớp */ group by sv.Masv, Tensv order by Diemtb desc, Tensv asc /* Câu 14: Cho biết mã số và số điểm lớn hơn 7 của những sinh viên có hơn một nửa số điểm là >=7 */ /* Câu 15: Cho biết mã số và tên nhưng sinh viên có hơn một nửa số điểm >=5*/ select sv.Masv, Tensv from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv and Diem >= 5 having COUNT[Diem] > 50 PERCENT */ chua lam duoc */ /* Câu 16: Đối với mỗi lớp, cho biết mã số và tên nhưng sinh viên phải thi lại từ 2 môn trở lên*/ select sv.Masv, Tensv from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv and Diem < 5 and Lop = 'L04' /* Lớp khác thì đổi tên lớp */ group by sv.Masv, Tensv having COUNT[Diem] > 2 /* Câu 17: Cho biết mã số và tên những môn học mà tất cả các sinh viên đều đạt điểm >=5*/ select mh.Mamh, Tenmh from Monhoc mh, Ketqua kq where mh.Mamh = kq.Mamh and Diem >= 5/* chua lam duoc*/ /* Câu 18: Cho biết mã số và tên của sinh viên có điểm trung bình chung học tập >=8 */ select sv.Masv, Tensv from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv group by sv.Masv, Tensv having AVG[CAST[Diem as float]] >= 8 /* Câu 19: Cho biết mã số và tên những sinh viên có điểm trung bình chung học tập cao nhất*/ select top [1] sv.Masv, Tensv, AVG[CAST[Diem as float]] as Diemtb from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv group by sv.Masv, Tensv order by Diemtb desc select sv.Masv, Tensv from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv and sv.Masv = [select sv.Masv, Tensv, MAX[a.Diemtb] from Sinhvien sv, [select AVG[CAST[Diem as float]] as Diemtb from Sinhvien sv, Ketqua kq where sv.Masv = kq.Masv group by sv.Masv, Tensv] a] /* chua lam duoc */ /* Câu 20: Cho biết mã số và tên những sinh viên phai thi lại ở ít nhất là những môn mà sinh viên có mã số 3 phải thi lại*/ select sv.Masv, Tensv from Sinhvien sv, Monhoc mh, Ketqua kq where sv.Masv = kq.Masv and mh.Mamh = kq.Mamh and sv.Masv != 3 and Diem < 5 and Tenmh in [select Tenmh from Monhoc mh, Ketqua kq where mh.Mamh = kq.Mamh and Diem < 5 and kq.Masv = 3]

Mình còn câu 14, 15, 17, 19 chưa làm được, ai có giải pháp gì để viết câu lệnh truy vấn giúp mình với. Và giúp mình kiểm tra các câu còn lại mình làm đúng chưa. Cảm ơn mn

• Techblog
• Kiến thức cơ bản

Tính toán và xử lý ngày tháng bằng câu lệnh SQL như thế nào?

Giang

1530

31-08-2021

SQL được biết đến là 1 trong những ngôn ngữ lập trình truy vấn có cấu trúc rất phổ biến hiện nay trong thế giới công nghệ. Trong SQL thì kiểu dữ liệu ngày tháng là kiểu dữ liệu thường gây ra khó khăn cho các lập trình viên khi thao tác. Bài viết này củaBizfly Cloudsẽ tổng hợp và hướng dẫn các bạn cách tính toán và xử lý ngày tháng bằng câu lệnh SQL. Chúng ta cùng tìm hiểu nhé.

Kiểu dữ liệu SQL cho ngày

Giá trị ngày/thời gian trong cơ sở dữ liệu MySQL thường có những loại như sau:

Date - format YYYY-MM-DD

DATETIME - format: YYYY-MM-DD HH:MI:SS

TIMESTAMP - format: YYYY-MM-DD HH:MI:SS

YEAR – format: YYYY hoặc YY

Riêng đối với Sql server thì giá trị ngày/thời gian trong cơ sở dữ liệu có các loại như sau:

DATE – format: YYYY-MM-DD

DATETIME - format: YYYY-MM-DD HH:MI:SS

SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS

TIMESTAMP - format: 1 số duy nhất

Các function thường dùng để tính toán và xử lý ngày tháng bằng câu lệnh SQL

Để tính toán và xử lý ngày tháng bằng câu lệnh SQL, các hàm dưới đây thường hay được sử dụng nhiều nhất.

Tính toán và xử lý ngày tháng bằng câu lệnh SQL như thế nào?

Giả sử chúng ta có bảng dữ liệu mẫu về một nhóm 6 người bạn trong SQL như sau [bảng dữ liệu này được sử dụng chung cho các tính toán bên dưới].

Friends

Giả sử ngày hiện tại là 2021-06-03. Để tính toán còn bao nhiêu ngày nữa, MySQL sử dụng hàm DATEDIFF[] để trừ hai giá trị ngày và trả về số ngày giữa chúng. Giả sử chúng ta muốn tính toán xem ngày hiện tại so với ngày sinh của nhóm 6 người bạn ở mẫu dữ liệu trên là bao nhiêu thì bạn code như sau:

SELECT DATEDIFF[CURDATE[], birthday] AS days_difference

FROM friends

LIMIT 6;

Và đây là kết quả được trả về:

Để biết được nhóm 6 người này có lần lượt số tuổi là bao nhiêu thì bạn chỉ cần chia cho 365 ngày là ra số tuổi của từng người. Code như sau:

SELECT ROUND[DATEDIFF[CURDATE[], birthday] / 365, 0] AS years

FROM friends

LIMIT 6;

Trong đó hàm toán học ROUND [] được dùng để làm tròn kết quả thành 1 số nguyên

Và đây là kết quả

Ngoài ra bạn cũng có thể tính toán tuổi của bạn bè mình bằng cú pháp sau:

SELECT

first_name,

last_name,

[YEAR[CURDATE[]] - YEAR[birthday]] - [RIGHT[CURDATE[], 5] < RIGHT[birthday, 5]] AS years

FROM

friends

Đến đây mình giải thích 1 chút nhé. Lấy ví dụ như Thu có ngày sinh là 1996-12-12. Hàm CURDATE [] được áp dụng nhằm mục đích trả về ngày hiện tại của máy tính. Giả sử là ngày 2021-06-03. Hàm YEAR [] dùng để trả về năm của ngày đã chỉ định. Ở đây, [YEAR[CURDATE[]] - YEAR[birthday]] sẽ trả về kết quả số năm chênh lệch giữa năm hiện tại và năm sinh của người được tính tuổi. Nghĩa là lấy 2021-1996 = 25.

Hàm RIGHT [] dùng để trả về số lượng ký tự như được chỉ định trong hàm từ chuỗi hoặc ngày đã cho. Hàm right có công thức RIGHT[chuoi, so_ky_tu]. Số ký tự là 5, nghĩa là giá trị mà hàm Right lấy ra là ngày và tháng của ngày hiện tại và ngày sinh nhật. Ở đây hàm RIGHT[CURDATE[], 5] lấy ra giá trị là 06-03 và hàm RIGHT[birthday, 5] lấy ra giá trị là 12-12. Trong đó, phần của biểu thức so sánh các trả về từ hàm RIGHT [] ước tính 1 hoặc 0. Nghĩa là 06-03 < 11-11 nên lấy giá trị 1. Từ đó SQL sẽ tính toán được tuổi của Thu là 25-1=24 tuổi.

Và đây là kết quả trả về

Sau khi tính toán ra số tuổi của bạn bè mình, bạn có thể sắp xếp lại trong list danh sách bạn bè theo thứ tự số tuổi tăng dần hoặc giảm dần bằng cách thêm các trường sau đây vào cuối mệnh đề ở trên.

ORDER BY age ASC; //sắp xếp tăng dần

ORDER BY age DESC; // sắp xếp giảm dần

Tính toán ngày sinh nhật

Khi tính toán và xử lý ngày tháng bằng câu lệnh SQL, bạn có thể tính toán xem trong tuần có ngày sinh nhật nào của bạn bè mình hay không bằng cách sử dụng hàm DAYOFWEEK[] là hàm trả về một giá trị số cho tham số giá trị ngày tháng. Và những con số đó đại diện cho:

1 = Chủ nhật,

2 = Thứ hai, v.v.

Ở đây bạn có thể đặt 1 case biểu thức để tính toán như sau:

SELECT

first_name,

last_name,

birthday,

CASE

WHEN DAYOFWEEK[birthday] = '1' THEN 'Sunday'

WHEN DAYOFWEEK[birthday] = '2' THEN 'Monday'

WHEN DAYOFWEEK[birthday] = '3' THEN 'Tuesday'

WHEN DAYOFWEEK[birthday] = '4' THEN 'Wednesday'

WHEN DAYOFWEEK[birthday] = '5' THEN 'Thursday'

WHEN DAYOFWEEK[birthday] = '6' THEN 'Friday'

WHEN DAYOFWEEK[birthday] = '7' THEN 'Saturday'

ELSE 'not a day of week'

END AS day_of_week

FROM

friends

LIMIT 10

Vàkết quả có được như sau:

Bằng cách này thì kết quả tương đối ổn. Tuy nhiên để lấy tên 1 ngày trong tuần thì nó còn khá dài. Vì thế có sử dụng 1 functionDAYNAME[] phù hợp hơn cho việc này. Đơn giản chỉ cần cung cấp cho nó một giá trị ngày và kết quả sẽ nhanh hơn rất nhiều. Code như sau:

SELECT

first_name, last_name, DAYNAME[birthday]

FROM

friends

LIMIT 10

Và đây là kết quả

Tính toán và xử lý tháng

Khi tính toán và xử lý ngày tháng bằng câu lệnh SQL , từ 1 giá trị ngày tháng cung cấp thì các hàm MONTH[] được sử dụng để lấy giá trị số theo tháng. Khi kết quả trả về là 1 [đại diện cho tháng 1] và 2 [đại diện cho tháng 2]…tương tự các tháng còn lại cũng tuân theo quy luật trên.

SELECT

[MONTH[birthday]] AS month, COUNT[*] AS number_of_birthdays

FROM

friends

GROUP BY month

ORDER BY month ASC

Trong truy vấn này, hàm COUNT[] có nhiệm vụ đếm số người có cùng ngày sinh trong mỗi tháng.

Kết quả như sau:

Sau đó chúng ta dùng hàm MONTHNAME[] để lấy tên của tháng. Code như sau:

SELECT DISTINCT

[MONTHNAME[birthday]] AS month,

COUNT[*] AS number_of_birthdays

FROM

friends

GROUP BY month

Và kết quả trả về như sau:

Tính toán và xử lý ngày

Giả sử như bạn muốn nhập thông tin về ngày sinh nhật của một người mới trong quá trình tính toán và xử lý ngày tháng bằng câu lệnh SQL bạn phải nhập ngày ở dạng chuỗi. Giả sử người đó sinh vào ngày 10 tháng 8 năm 2017 thì bạn nhập như sau:

SELECT STR_TO_DATE["August 10 2017", "%M %d %Y"]

Trong chuỗi ký hiệu được định dạng %M %d,%Y thì %M là tên tháng. %d là ngày trong tháng. %Y là 4 chữ số năm.

Ngày có giá trị NOT NULL

Có thể dùng câu lệnh MySQL dưới đây để kiểm tra ngày không phải là Null

SELECT

first_name, last_name, birthday

FROM

friends

WHERE

birthday IS NOT NULL;

Nó sẽ lọc ra các hàng có ngày sinh nhật không phải là null cho bạn.

Lấy ra những ngày trong khoảng ngày từ ngày... đến ngày ...

Trong tính toán và xử lý ngày tháng bằng câu lệnh SQL thì để có thể lấy ra những ngày trong 1 khoảng thời gian định sẵn thì các bạn code như sau:

SELECT

*

FROM

friends

WHERE

birthday BETWEEN '1996-10-05 00:00:00' AND '1996-12-25 23:59:59'

Và đây là kết quả trả về dữ liệu của người có ngày sinh trong khoảng thời gian trên.

Đến đây có lẽ bạn đã hiểu được cách tính toán và xử lý ngày tháng bằng câu lệnh SQL thông qua những ví dụ cụ thể trong bài viết này. Nếu để ý kỹ bạn sẽ thấy nó không hề khó chút nào. Chỉ cần bạn kiên nhẫn thực hành luyện tập nhiều lần thì sẽ không còn bỡ ngỡ nếu phải tính toán và xử lý ngày tháng bằng câu lệnh SQL trong công việc của mình ở hiện tại và tương lai. Chúc bạn thành công.

BizFly Cloud là nhà cung cấp dịch vụ điện toán đám mây với chi phí thấp, được vận hành bởi VCCorp.

BizFly Cloud là một trong 4 doanh nghiệp nòng cốt trong "Chiến dịch thúc đẩy chuyển đổi số bằng công nghệ điện toán đám mây Việt Nam" của Bộ TT&TT; đáp ứng đầy đủ toàn bộ tiêu chí, chỉ tiêu kỹ thuật của nền tảng điện toán đám mây phục vụ Chính phủ điện tử/chính quyền điện tử.

Độc giả quan tâm đến các giải pháp của BizFly Cloud có thể truy cập .

DÙNG THỬ MIỄN PHÍ và NHẬN ƯU ĐÃI 3 THÁNG tại: Manage.bizflycloud

SHARE

Facebook

Twitter