Lab 3 Singlerow Functions CISB 224 01 A

Lab 3: Single-row Functions CISB 224 01 A, 01 B, 02 A, 02 B CCSB 244 01 A, 01 B Semester I, 2007/2008 College of Information Technology, Universiti Tenaga Nasional 1

ROUND() Syntax ROUND(numeric_expression, length) n n Returns a numeric expression, rounded to the specified length or precision Accepts two arguments: n n numeric_expression - value to be rounded up length - precision to which the first argument is to be rounded, must be an integer College of Information Technology, Universiti Tenaga Nasional 2

ROUND() – cont. Rounding up the value 457. 923 to varying precisions: Precision Result Round(457. 923, -2) 500. 000 -2 Round(457. 923, -1) 460. 000 -1 Round(457. 923, 0) 458. 00 0 Round(457. 923, 1) 457. 900 1 Round(457. 923, 2) 457. 920 2 College of Information Technology, Universiti Tenaga Nasional 3

ROUND() – cont. Example 1 Round up 457. 923 to the hundreds. SELECT ROUND(457. 923, -2) Example 2 Round up 457. 923 to the hundredths, or to two decimal places. SELECT ROUND(457. 923, 2) College of Information Technology, Universiti Tenaga Nasional 4

LOWER() and UPPER() Syntax LOWER(character_expression) UPPER(character_expression) n n Returns a character expression, in lower-case or upper-case The argument may be a: n n n Table column String Concatenation/combination of the above College of Information Technology, Universiti Tenaga Nasional 5

LOWER() and UPPER() – cont. Example 3 Display employees’ names and job titles. Display the names in this format ‘LASTNAME, First. Name’ and give the column a suitable heading. SELECT UPPER(Last. Name) + ‘, ‘ + First. Name AS Name, Title AS ‘Job Title’ FROM Employees College of Information Technology, Universiti Tenaga Nasional 6

GETDATE() Syntax GETDATE() n n Returns the current system date and time Accepts no argument College of Information Technology, Universiti Tenaga Nasional 7

DATEADD() Syntax DATEADD(date_part, numeric_expression, date_expression) n n Returns a datetime value, the result of adding a specified length of time to a specified start date Accepts three arguments: n n n date_part – Measure of time to add e. g. Month numeric_expression – Multiple of the measure, to get a length of time e. g. 6 6 months date_expression – Start date College of Information Technology, Universiti Tenaga Nasional 8

DATEADD() – cont. Example 4 What date is 6 months from today? SELECT Get. Date() AS Today, Date. Add(Month, 6, Get. Date()) AS ‘ 6 Months from Today’ Functions can be nested! Here, Get. DATE() is nested inside Date. ADD() and used as the start date argument. College of Information Technology, Universiti Tenaga Nasional 9

DATEDIFF() Syntax DATEDIFF(date_part, date_expression) n n Returns the length of time between two dates, in a specified measure of time Accepts three arguments: n n n date_part – Measure of time to use e. g. Week, Day date_expression – Start date_expression – End date College of Information Technology, Universiti Tenaga Nasional 10

Date. Diff() – cont. Example 5 How many days more to the National Day? SELECT Date. Diff(Day, ‘ 8 August, 2007’, ’ 31 August, 2007’) Date string values are also enclosed in single quotes! College of Information Technology, Universiti Tenaga Nasional 11

DATENAME() Syntax DATENAME(date_part, date_expression) n n Returns a character string representing the specified date part of the specified date Accepts two arguments: n n date_part – Part of the date to be returned date_expression – Date to be used College of Information Technology, Universiti Tenaga Nasional 12

Date. Name() – cont. Example 6 What month is it now? SELECT Date. Name(Month, ‘ 8 August, 2007’) Some other ways of writing date constants are: • 'April 15, 1998' • '980415' • '04/15/98‘ Look up ‘Constants’ in Transact-SQL Help. College of Information Technology, Universiti Tenaga Nasional 13

Convert() Syntax Convert(data_type[(length)], expression[, style]) n n Converts an expression from one data type to another Accepts three arguments n n n data_type – data type to convert into expression – expression to be converted style – format of the returned expression (refer to the Transact-SQL Help) College of Information Technology, Universiti Tenaga Nasional 14

Convert() – cont. Example 7 Display the employees’ hire dates in British format i. e. dd/mm/yy. SELECT Last. Name, Convert(char, Hire. Date, 3) AS ‘Hire Date’ FROM Employees College of Information Technology, Universiti Tenaga Nasional 15

Convert() – cont. Example 8 Display all products and their prices in this format: nnn, nnn. SELECT Product. Name, Convert(char(10), Unit. Price, 1) AS ‘Unit Price’ FROM Products College of Information Technology, Universiti Tenaga Nasional 16

The LIKE Logical Operator – cont. Wildcard Characters % Represents any number of characters _ Represents exactly one character (Underscore) College of Information Technology, Universiti Tenaga Nasional 18

The LIKE Logical Operator – cont. Example 9 Display the details of the employee named Buchanan. But… you don’t know how to spell Buchanan SELECT * FROM Employees WHERE Last. Name LIKE ‘Bu%’ College of Information Technology, Universiti Tenaga Nasional 19

The LIKE Logical Operator – cont. Example 10 Display all products that do not have the letter ‘e’ as the second letter in their names. SELECT Product. Name FROM Products WHERE Product. Name NOT LIKE ‘_e%’ College of Information Technology, Universiti Tenaga Nasional 20

The LIKE Logical Operator – cont. Example 11 Display all employees who were hired in 1995. SELECT Last. Name, Hire. Date FROM Employees WHERE Hire. Date LIKE ‘%95%’ College of Information Technology, Universiti Tenaga Nasional 21

The LIKE Logical Operator – cont. Challenge yourself! Find other ways of obtaining the same results as Example 11. Use other operators in the condition. SELECT Last. Name, Hire. Date FROM Employees WHERE ? ? ? College of Information Technology, Universiti Tenaga Nasional 22