KIỂM TRA SỰ TIẾN BỘ CỦA HỌC VIÊN
-
______ được sử dụng như một bí danh cho tên một bảng.
-
Mệnh đề AS
-
FROM
-
INTO
-
WHERE
-
Từ khóa ______ chỉ hiển thị vài dòng đầu tiên của tập kết quả.
-
VIEW
-
PERCENT
-
TOP
-
DISTINCT
-
______ hiển thị một chuỗi có độ dài bất kỳ.
-
[]
-
%
-
–
-
[^]
-
Mệnh đề ______ với từ khóa ______ làm giảm số các dòng được nhóm gộp.
-
WHERE và GROUP BY
-
HAVING và GROUP BY
-
ALL và GROUP BY
-
GROUP BY và DISTINCT
-
Mệnh đề ______ sắp xếp tập kết quả theo một trật tự nhất định.
-
GROUP BY
-
ORDER BY
-
WHERE
-
ASC
CHƯƠNG 10
TRUY CẬP DỮ LIỆU TỪ CSDL (THỰC HÀNH)
Mục tiêu :
Kết thúc chương này, các bạn sẽ có thể:
-
Sử dụng câu lệnh SELECT
-
Giải thích cách dùng câu lệnh SELECT với mệnh đề INTO
-
Sử dụng câu lệnh SELECT với mệnh đề FROM
-
Sử dụng mệnh đề WHERE
-
Giải thích cách dùng câu lệnh SELECT với mệnh đề GROUP BY
-
Giải thích cách dùng câu lệnh SELECT với mệnh đề HAVING
-
Sử dụng mệnh đề ORDER BY
|
Phần I – 60 phút
Bài tập 1: Sử dụng câu lệnh SELECT với các mệnh đề khác nhau
Bài tập này sẽ giải thích cách dùng câu lệnh SELECT với các mệnh đề khác nhau như: FROM, WHERE, INTO, GROUP BY, HAVING và ORDER BY. Xem xét kịch bản của một công ty cần lưu giữ dấu vết của tất cả nhân viên trong công ty và các nhân viên mới vào. Biotron Technologies là một công ty hàng đầu trong lĩnh vực công nghệ sinh học ở Mỹ. Để thực thi công việc này họ dùng SQL Server và tạo ra một bảng có tên Employee với cấu trúc như bảng 10.1.
-
Field Name
|
Type
|
Size
|
Mô tả
|
EmployeeID
|
smallint
|
|
Xác định mỗi nhân viên với một số duy nhất
|
FirstName
|
char
|
10
|
Lưu trữ họ của nhân viên
|
LastName
|
char
|
10
|
Lưu trữ tên của nhân viên
|
Department
|
varchar
|
50
|
Lưu trữ bộ phận của nhân viên
|
HiredDate
|
datetime
|
|
Lưu trữ ngày nhân viên gia nhập công ty
|
Salary
|
money
|
|
Lưu trữ lương của mỗi nhân viên
|
Bảng 10.1: Bảng Employee
Dữ liệu mẫu của bảng Employee được hiển thị như hình 10.1
Hình 10.1: Các bản ghi trong bảng Employee
Theo kịch bản ở trên, hãy viết các truy vấn phù hợp:
-
Viết truy vấn trả lại tất cả các bản ghi từ bảng Employee.
-
Viết truy vấn trả lại danh sách các nhân viên có lương lớn hơn 350000. Tập kết quả gồm các trường EmployeeID, FirstName, LastName.
-
Biotron Technologies trả tiền thưởng năm cho tất cả nhân viên là 5% lương từng người. Viết truy vấn trên với các trường EmployeeID, FirstName, Department, Salary và tính khoản tiền thưởng năm. Lưu kết quả vào một bảng khác với tên EmployeeBonus.
-
Viết truy vấn trả lại danh sách các nhân viên có tên bắt đầu bằng ký tự ‘J’ gồm hai trường FirstName, Department.
-
Quản lý của công ty cần quyết định số lượng nhân viên của mỗi bộ phận. Viết một truy vấn trả lại số lượng nhân viên trong mỗi bộ phận.
-
Quản lý công ty cần lên kế hoạch về ngân sách hàng năm. Họ cần biết tổng chi tiêu về lương cho từng bộ phận cùng với số nhân viên của trong bộ phận đó. Viết một truy vấn trả lại tổng lương của tất cả nhân viên trong mỗi bộ phận với tên bộ phận và số nhân viên trong bộ phận đó.
Giải pháp:
Sử dụng câu lệnh SELECT trong SQL Server Management Studio
-
Mở một cửa sổ truy vấn mới và gõ lệnh sau:
USE AdventureWorks
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Để trả lại tất cả các hàng từ bảng Employee sử dụng câu lệnh SELECT và mệnh đề FROM, thực hiên theo các bước sau:
-
Gõ câu lệnh sau:
SELECT * FROM EMPLOYEE
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Kết quả được hiểnthị như hình 10.2.
Hình 10.2: Câu lệnh SELECT
Để hiển thị danh sách các nhân viên có lương lớn hơn $350000 gồm các trường EmployeeID, FirstName, LastName, thực hiện theo các bước sau:
-
Gõ câu lệnh sau:
SELECT EmployeeID, FirstName, Department FROM Employee WHERE Salary >350000
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Kết quả được hiểnthị như hình 10.3.
Hình 10.3: Câu lệnh SELECT dùng với mệnh đề FROM và WHERE
Sử dụng mệnh đề INTO để lưu trữ kết quả truy vấn vào một bảng mới trong cùng một CSDL
Để tính 5% lương của mỗi nhân viên và hiển thị nó trong một cột gọi là YearlyBonus và lưu kết quả vào một bảng mới trong cùng một CSDL, thực hiện theo các bước sau:
-
Gõ câu lệnh sau:
SELECT EmployeeID, FirstName, Department, Salary, Salary*0.05 AS YearlyBonus
INTO EmployeeBonus FROM Employee Select * FROM EmployeeBonus
-
Ấn phím F5 để thực hiện câu lệnh trên
|
Kết quả được hiển thị như hình 10.4.
Hình 10.4: Sử dụng mệnh đề INTO
Sử dụng mệnh đề WHERE với toán tử LIKE
-
Gõ câu lệnh sau:
SELECT FirstName, Department FROM Employee
WHERE FirstName LIKE ‘J%’
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Kết quả được hiển thị như hình 10.5.
Hình 10.5: Sử dụng mệnh đề WHERE với toán tử LIKE
Sử dụng mệnh đề GROUP BY
Thực hiện các bước sau để truy vấn số nhân viên trong mỗi bộ phận:
-
Viết truy vấn SELECT sau:
SELECT Department, COUNT(*) ‘NumberOfEmployees’
FROM Employee GROUP BY Department
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Kết quả được hiển thị như hình 10.6.
Hình 10.6: Sử dụng mệnh đề GROUP BY
Sử dụng mệnh đề GROUP BY với HAVING
Truy vấn phải dùng các hàm SUM() và COUNT(). Hàm SUM() được dùng để tính tổng lương của tất cả nhân viên trong một bộ phận. Tổng số nhân viên trong mỗi bộ phận sẽ dùng hàm COUNT(). Để nhóm kết quả theo mỗi bộ phận dùng mệnh đề GROUP BY. Hình 10.7 minh họa cho truy vấn này.
Để viết truy vấn tính tổng lương của tất cả các nhân viên trong mỗi bộ phận thực hiện theo các bước sau:
-
Gõ câu lệnh sau:
SELECT Department, SUM(Salary) AS ‘SalaryTotal’,
COUNT( EmployeeID ) AS ‘NumberOfEmployees’
FROM Employee
GROUP BY Department
-
Ấn phím F5 để thực hiện câu lệnh trên.
|
Kết quả được hiển thị như hình 10.7.
Hình 10.7: Sử dụng câu lệnh SELECT với mệnh đề GROUP BY và HAVING
Phần II – 60 phút
-
Quản lý công ty muốn thưởng cho những nhân viên đã hoàn thành nhiệm vụ trong 03 năm. Sử dụng bảng Employee, viết truy vấn trả về gồm các trường EmployeeID, FirstName, Department, và HireDate.
Hướng dẫn:
Dùng hàm getDate(). Hàm này trả lại ngày giờ hiện tại theo định dạng yyyy/mm/dd hh:min:sec AM/PM
-
Quản lý công ty cần tăng 25% lương cho các nhân viên đã làm việc trong công ty 1 năm. Viết một truy vấn trả lại gồm các trường EmployeeID, FirstName, Department của các nhân viên đã làm việc một năm tại công ty và cột lương đã tăng với tiêu đề là RevisedSalary.
-
Chủ tịch công ty cần biết lương trung bình phải trả cho mỗi bộ phận. Viết truy vấn để hiển thị dữ liệu như theo yêu cầu.
-
Viết truy vấn tính lương trung bình cho các bộ phận có số nhân viên > 1. Kết quả được sắp xếp giảm dần và lưu trong 1 bảng mới với tên bảng là DepartmentWiseSalary.
-
Quản lý công ty cần biết danh sách những phòng ban có nhân viên đã làm việc tại công ty trên 3 năm. Viết truy vấn để hiển thị dữ liệu theo yêu cầu.
BÀI TẬP TỰ LÀM
1. MetroLink là một đại lý du lịch cần thiết kế một Web Portal để khách hàng có thể truy cập và đăng ký trực tuyến các dịch vụ. Họ cần phải tạo một bảng Hotels để lưu trữ chi tiết về các khách sạn như bảng 10.2. Mỗi khách sạn có nhiều loại phòng khác nhau và mỗi khách sạn có thể tọa lạc trên nhiều thành phố.
Field Name
|
Type
|
Size
|
Mô tả
|
HotelNumber
|
Smallint
|
|
Xác định mỗi khách sạn là một số duy nhất
|
HotelName
|
varchar
|
50
|
Lưu trữ tên của khách sạn
|
City
|
varchar
|
50
|
Lưu trữ tên của thành phố có khách sạn
|
NumberOfRooms
|
smallint
|
|
Lưu trữ số phòng trong khách sạn
|
RoomType
|
varchar
|
50
|
Lưu trữ các loại phòng
|
Price
|
money
|
|
Lưu trữ giá cho thuê của 1 phòng
|
Chia sẻ với bạn bè của bạn: |