Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Bài 2: Các kiểu dữ liệu và hàm trong SQL Server - Lê Thị Tú Kiên

e[,months]) 
DATEFROMPARTS(year,month,day) 
ISDATE(expression) 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 42 
Date part values and abbreviations 
Argument Abbreviations 
year yy, yyyy 
quarter qq, q 
month mm, m 
dayofyear dy, y 
day dd, d 
week wk, ww 
weekday dw 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 43 
Date part values and abbreviations (continued) 
Argument Abbreviations 
hour hh 
minute mi, n 
second ss, s 
millisecond ms 
microsecond mcs 
nanosecond ns 
tzoffset tz 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 44 
Examples that use date/time functions 
Function Result 
GETDATE() 2012-09-30 14:10:13.813 
GETUTCDATE() 2012-09-30 21:10:13.813 
SYSDATETIME() 2012-09-30 14:10:13.8160822 
SYSUTCDATETIME() 2012-09-30 21:10:13.8160822 
SYSDATETIMEOFFSET() 2012-09-30 14:10:13.8160822 
-07.00 
MONTH('2012-09-30') 9 
DATEPART(month,'2012-09-30') 9 
DATENAME(month,'2012-09-30') September 
DATENAME(m,'2012-09-30') September 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 45 
Examples that use date/time functions (continued) 
Function Result 
EOMONTH('2012-02-01') 2012-02-29 
EOMONTH('2012-02-01',2) 2012-04-30 
DATEFROMPARTS(2012,4,3) 2012-04-03 
ISDATE('2012-09-30') 1 
ISDATE('2012-09-31') 0 
ISDATE('23:59:59') 1 
ISDATE('23:99:99') 0 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 46 
Examples that use the DAY, MONTH, 
and YEAR functions 
Function Result 
DAY('2012-09-30') 30 
MONTH('2012-09-30') 9 
YEAR('2012-09-30') 2012 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 47 
Examples that use the DATEPART function 
Function Result 
DATEPART(day, '2012-09-30 11:35:00') 30 
DATEPART(month, '2012-09-30 11:35:00') 9 
DATEPART(year, '2012-09-30 11:35:00') 2012 
DATEPART(hour, '2012-09-30 11:35:00') 11 
DATEPART(minute, '2012-09-30 11:35:00') 35 
DATEPART(second, '2012-09-30 11:35:00') 0 
DATEPART(quarter, '2012-09-30 11:35:00') 3 
DATEPART(dayofyear, '2012-09-30 11:35:00') 273 
DATEPART(week, '2012-09-30 11:35:00') 40 
DATEPART(weekday, '2012-09-30 11:35:00') 1 
DATEPART(millisecond, '11:35:00.1234567') 123 
DATEPART(microsecond, '11:35:00.1234567') 123456 
DATEPART(nanosecond, '11:35:00.1234567') 123456700 
DATEPART(tzoffset, '11:35:00.1234567 -07:00') -420 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 48 
Examples that use the DATENAME function 
Function Result 
DATENAME(day, '2012-09-30 11:35:00') 30 
DATENAME(month, '2012-09-30 11:35:00') September 
DATENAME(year, '2012-09-30 11:35:00') 2012 
DATENAME(hour, '2012-09-30 11:35:00') 11 
DATENAME(minute, '2012-09-30 11:35:00') 35 
DATENAME(second, '2012-09-30 11:35:00') 0 
DATENAME(quarter, '2012-09-30 11:35:00') 3 
DATENAME(dayofyear, '2012-09-30 11:35:00') 274 
DATENAME(week, '2012-09-30 11:35:00') 40 
DATENAME(weekday, '2012-09-30 11:35:00') Sunday 
DATENAME(millisecond, '11:35:00.1234567') 123 
DATENAME(microsecond, '11:35:00.1234567') 123456 
DATENAME(nanosecond, '11:35:00.1234567') 123456700 
DATENAME(tzoffset, '11:35:00.1234567 -07:00') -07:00 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 49 
Examples that use the DATEADD function 
Function Result 
DATEADD(day, 1, '2012-09-30 11:35:00') 2012-10-01 11:35:00.000 
DATEADD(month, 1, '2012-09-30 11:35:00') 2012-10-30 11:35:00.000 
DATEADD(year, 1, '2012-09-30 11:35:00') 2013-09-30 11:35:00.000 
DATEADD(hour, 1, '2012-09-30 11:35:00') 2012-09-30 12:35:00.000 
DATEADD(minute, 1, '2012-09-30 11:35:00') 2012-09-30 11:36:00.000 
DATEADD(second, 1, '2012-09-30 11:35:00') 2012-09-30 11:35:01.000 
DATEADD(quarter, 1, '2012-09-30 11:35:00') 2012-12-30 11:35:00.000 
DATEADD(week, 1, '2012-09-30 11:35:00') 2012-10-07 11:35:00.000 
DATEADD(month, -1, '2012-09-30 11:35:00') 2012-08-30 11:35:00.000 
DATEADD(year, 1.5, '2012-09-30 11:35:00') 2013-09-30 11:35:00.000 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 50 
Examples that use the DATEDIFF function 
Function Result 
DATEDIFF(day, '2011-12-01', '2012-09-30') 304 
DATEDIFF(month, '2011-12-01', '2012-09-30') 9 
DATEDIFF(year, '2011-12-01', '2012-09-30') 1 
DATEDIFF(hour, '06:46:45', '11:35:00') 5 
DATEDIFF(minute, '06:46:45', '11:35:00') 289 
DATEDIFF(second, '06:46:45', '11:35:00') 17295 
DATEDIFF(quarter, '2011-12-01', '2012-09-30') 3 
DATEDIFF(week, '2011-12-01', '2012-09-30') 44 
DATEDIFF(day, '2012-09-30', '2011-12-01') -304 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 51 
Examples that use the addition 
and subtraction operators 
Operation Result 
CAST('2012-09-30 11:35:00' AS smalldatetime) + 1 
 2012-10-01 11:35:00 
CAST('2012-09-30 11:35:00' AS smalldatetime) – 1 
 2012-09-29 11:35:00 
CAST(CAST('2012-09-30' AS datetime) 
 - CAST('2011-12-01' AS datetime) AS int) 
 304 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 52 
The contents of the DateSample table 
A search condition that fails to return a row 
SELECT * FROM DateSample 
WHERE StartDate = '2011-10-28'; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 53 
SELECT statements that ignore time values 
Use the date type to remove time values 
(SQL Server 2008 or later) 
SELECT * FROM DateSample 
WHERE CONVERT(date, StartDate) = '2011-10-28'; 
Search for a range of dates 
SELECT * FROM DateSample 
WHERE StartDate >= '2011-10-28' AND 
 StartDate < '2011-10-29'; 
Search for month, day, and year components 
SELECT * FROM DateSample 
WHERE MONTH(StartDate) = 10 AND 
 DAY(StartDate) = 28 AND 
 YEAR(StartDate) = 2011; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 54 
SELECT statements that ignore time values 
(continued) 
Use the CAST function to remove time values 
SELECT * FROM DateSample 
WHERE CAST(CAST(StartDate AS char(11)) AS datetime) 
 = '2011-10-28'; 
Use the CONVERT function to remove time values 
SELECT * FROM DateSample 
WHERE CONVERT(datetime, CONVERT(char(10), StartDate, 
 110)) = '2011-10-28'; 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 55 
The contents of the DateSample table 
Two search conditions that fail to return a row 
SELECT * FROM DateSample 
WHERE StartDate = CAST('10:00:00' AS datetime); 
SELECT * FROM DateSample 
WHERE StartDate >= '09:00:00' AND 
 StartDate < '12:59:59:999'; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 56 
Two SELECT statements that ignore date values 
Use the time type to remove date values 
(SQL Server 2008 or later) 
SELECT * FROM DateSample 
WHERE CONVERT(time, StartDate) >= '09:00:00' AND 
 CONVERT(time, StartDate) < '12:59:59:999'; 
Use the CONVERT function to remove date values 
(prior to SQL Server 2008) 
SELECT * FROM DateSample 
WHERE CONVERT(datetime, CONVERT(char(12), StartDate, 8)) 
 >= '09:00:00' AND 
 CONVERT(datetime, CONVERT(char(12), StartDate, 8)) 
 < '12:59:59:999'; 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 57 
The syntax of the simple CASE function 
CASE input_expression 
 WHEN when_expression_1 THEN result_expression_1 
 [WHEN when_expression_2 THEN result_expression_2]... 
 [ELSE else_result_expression] 
END 
A SELECT statement with a simple CASE function 
SELECT InvoiceNumber, TermsID, 
 CASE TermsID 
 WHEN 1 THEN 'Net due 10 days' 
 WHEN 2 THEN 'Net due 20 days' 
 WHEN 3 THEN 'Net due 30 days' 
 WHEN 4 THEN 'Net due 60 days' 
 WHEN 5 THEN 'Net due 90 days' 
 END AS Terms 
FROM Invoices; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 58 
The syntax of the searched CASE function 
CASE 
 WHEN conditional_expression_1 THEN result_expression_1 
 [WHEN conditional_expression_2 
 THEN result_expression_2]... 
 [ELSE else_result_expression] 
END 
A SELECT statement 
with a searched CASE function 
SELECT InvoiceNumber, InvoiceTotal, InvoiceDate, 
InvoiceDueDate, 
 CASE 
 WHEN DATEDIFF(day, InvoiceDueDate, GETDATE()) > 30 
 THEN 'Over 30 days past due' 
 WHEN DATEDIFF(day, InvoiceDueDate, GETDATE()) > 0 
 THEN '1 to 30 days past due' 
 ELSE 'Current' 
 END AS Status 
FROM Invoices 
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 59 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 60 
The syntax of the IIF function 
IIF(conditional_expression, true_value, false_value) 
A SELECT statement with an IIF function 
SELECT VendorID, SUM(InvoiceTotal) AS SumInvoices, 
 IIF(SUM(InvoiceTotal) < 1000, 'Low', 'High') 
 AS InvoiceRange 
FROM Invoices 
GROUP BY VendorID; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 61 
The syntax of the CHOOSE function 
CHOOSE(index, value1, value2 [,value3]...) 
A SELECT statement with a CHOOSE function 
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, 
 CHOOSE(TermsID, '10 days', '20 days', '30 days', 
 '60 days', '90 days') AS NetDue 
FROM Invoices 
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 62 
The syntax of the COALESCE function 
COALESCE(expression_1 [, expression_2]...) 
The syntax of the ISNULL function 
ISNULL(check_expression, replacement_value) 
A SELECT statement with a COALESCE function 
SELECT PaymentDate, 
 COALESCE(PaymentDate, '1900-01-01') AS NewDate 
FROM Invoices; 
The same statement with an ISNULL function 
SELECT PaymentDate, 
 ISNULL(PaymentDate, '1900-01-01') AS NewDate 
FROM Invoices; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 63 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 64 
A SELECT statement that substitutes 
a different data type 
SELECT VendorName, 
 COALESCE(CAST(InvoiceTotal AS varchar), 'No invoices') 
 AS InvoiceTotal 
FROM Vendors LEFT JOIN Invoices 
 ON Vendors.VendorID = Invoices.VendorID 
ORDER BY VendorName; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 65 
The syntax of the GROUPING function 
GROUPING(column_name) 
A summary query with a GROUPING function 
SELECT 
 CASE 
 WHEN GROUPING(VendorState) = 1 THEN 'All' 
 ELSE VendorState 
 END AS VendorState, 
 CASE 
 WHEN GROUPING(VendorCity) = 1 THEN 'All' 
 ELSE VendorCity 
 END AS VendorCity, 
 COUNT(*) AS QtyVendors 
FROM Vendors 
WHERE VendorState IN ('IA', 'NJ') 
GROUP BY VendorState, VendorCity WITH ROLLUP 
ORDER BY VendorState DESC, VendorCity DESC; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 66 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 67 
The syntax for the four ranking functions 
ROW_NUMBER() 
 OVER ([partition_by_clause] order_by_clause) 
RANK() 
 OVER ([partition_by_clause] order_by_clause) 
DENSE_RANK() 
 OVER ([partition_by_clause] order_by_clause) 
NTILE(integer_expression) 
 OVER ([partition_by_clause] order_by_clause) 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 68 
A query with a ROW_NUMBER function 
SELECT ROW_NUMBER() OVER(ORDER BY VendorName) AS RowNumber, 
 VendorName 
FROM Vendors; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 69 
A query that uses the PARTITION BY clause 
SELECT ROW_NUMBER() OVER(PARTITION BY VendorState 
 ORDER BY VendorName) As RowNumber, VendorName, 
 VendorState 
FROM Vendors; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 70 
A query with RANK and DENSE_RANK functions 
SELECT RANK() OVER (ORDER BY InvoiceTotal) As Rank, 
 DENSE_RANK() OVER (ORDER BY InvoiceTotal) 
 As DenseRank, InvoiceTotal, InvoiceNumber 
FROM Invoices; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 71 
A query that uses the NTILE function 
SELECT TermsDescription, 
 NTILE(2) OVER (ORDER BY TermsID) AS Tile2, 
 NTILE(3) OVER (ORDER BY TermsID) AS Tile3, 
 NTILE(4) OVER (ORDER BY TermsID) AS Tile4 
FROM Terms; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 72 
The syntax of the analytic functions 
{FIRST_VALUE|LAST_VALUE}(scalar_expression) 
 OVER ([partition_by_clause] order_by_clause 
 [rows_range_clause]) 
{LEAD|LAG}(scalar_expression [, offset [, default]]) 
 OVER ([partition_by_clause] order_by_clause) 
{PERCENT_RANK()|CUME_DIST} 
 OVER ([partition_by_clause] order_by_clause) 
{PERCENTILE_CONT|PERCENTILE_DISC}(numeric_literal) 
 WITHIN GROUP (ORDER BY expression [ASC|DESC]) 
 OVER (partition_by_clause) 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 73 
The columns in the SalesReps table 
Column name Data type 
RepID int 
RepFirstName varchar(50) 
RepLastName varchar(50) 
The columns in the SalesTotals table 
Column name Data type 
RepID int 
SalesYear char(4) 
SalesTotal money 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 74 
A query that uses the FIRST_VALUE 
and LAST_VALUE functions 
SELECT SalesYear, RepFirstName + ' ' + 
 RepLastName AS RepName, SalesTotal, 
 FIRST_VALUE(RepFirstName + ' ' + RepLastName) 
 OVER (PARTITION BY SalesYear 
 ORDER BY SalesTotal DESC) 
 AS HighestSales, 
 LAST_VALUE(RepFirstName + ' ' + RepLastName) 
 OVER (PARTITION BY SalesYear 
 ORDER BY SalesTotal DESC 
 RANGE BETWEEN UNBOUNDED PRECEDING AND 
 UNBOUNDED FOLLOWING) 
 AS LowestSales 
FROM SalesTotals JOIN SalesReps 
 ON SalesTotals.RepID = SalesReps.RepID; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 75 
The result set 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 76 
A query that uses the LAG function 
SELECT RepID, SalesYear, SalesTotal AS CurrentSales, 
 LAG(SalesTotal, 1, 0) 
 OVER (PARTITION BY RepID ORDER BY SalesYear) 
 AS LastSales, 
 SalesTotal - LAG(SalesTotal, 1, 0) 
 OVER (PARTITION BY REPID ORDER BY SalesYear) 
 AS Change 
FROM SalesTotals; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 77 
A query that uses four more functions 
SELECT SalesYear, RepID, SalesTotal, 
 PERCENT_RANK() OVER (PARTITION BY SalesYear 
 ORDER BY SalesTotal) AS PctRank, 
 CUME_DIST() OVER (PARTITION BY SalesYear 
 ORDER BY SalesTotal) AS CumeDist, 
 PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY SalesTotal) 
 OVER (PARTITION BY SalesYear) AS PercentileCont, 
 PERCENTILE_DISC(.5) WITHIN GROUP (ORDER BY SalesTotal) 
 OVER (PARTITION BY SalesYear) AS PercentileDisc 
FROM SalesTotals; 
Murach's SQL Server 2012, C8 
Lê Thị Tú Kiên - HQT SQL Server 
Slide 78 
Terms 
 Logical functions 
 Ranking functions 
 Analytic functions 
Murach's SQL Server 2012, C8