Structured Query Language 192022 Pierce College CIS 261

Structured Query Language 1/9/2022 Pierce College CIS 261 SQL 1

Module 6: Data Types and Built-In Functions Data Types Logical, scalar and conversion functions 1/9/2022 Pierce College CIS 261 SQL 2

Data Types “In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. ” Exact numerics Unicode character strings Approximate numerics Binary strings Date and time Other data types Character strings http: //technet. microsoft. com/en-us/library/ms 187752. aspx 1/9/2022 Pierce College CIS 261 SQL 3

Data Types Due to the time constraints of the course, we will deal primarily with the following data types: Data type Definition int The int data type is the primary integer data type in SQL Server. bit An integer data type that can take a value of 1, 0, or NULL. float Approximate-number data type for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. datetime Defines a date that is combined with a time of day with fractional seconds that is based on a 24 -hour clock. char A string data types of fixed length. varchar A string data type of variable length data whose length can from 1 to 8000. nvarchar A variable-length Unicode string data whose length can from 1 to 4000 varbinary Variable-length binary data from 0 through 8000. Varbinary can hold any kind of data uniqueidentifier A 16 -byte GUID 1/9/2022 http: //technet. microsoft. com/en-us/library/ms 187752. aspx Pierce College CIS 261 SQL 4

Data Types in Northwind Every attribute has a data type. This image shows the data types used in the Employees table. 1/9/2022 Pierce College CIS 261 SQL 5

Data Types in Northwind Explore what the data types in Northwind look like when queried 1/9/2022 Pierce College CIS 261 SQL 6

Int Data Type An integer is a member of the set of positive whole numbers {1, 2, 3, . . . }, negative whole numbers {-1, -2, -3, . . . }, and zero {0}. The American Heritage® Dictionary of the English Language The SQL Server int data type can contain integers with values between − 2, 147, 483, 648 and +2, 147, 483, 647 For larger numbers, use the bigint type, which can contain values between -9, 223, 372, 036, 854, 775, 808 and +9, 223, 372, 036, 854, 775, 807 To conserve more space, you can use the smallint (-32, 768 to 32, 767) or tinyint (0 to 255) data types. Ints are commonly used a primary key values, as most tables will never exceed 2 billion rows. However, that is certainly not always true, and a true analysis of the right data type is necessary before using int as a primary key field. http: //technet. microsoft. com/en-us/library/ms 187745. aspx 1/9/2022 Pierce College CIS 261 SQL 7

Char Data Type “Fixed length data type. The signature for char is char(n), where n is the length of the string. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. Valid values for char range from 1 to 8000. Thus a field could have a char(8000) data type assignment. This is not uncommon. Use char when the sizes of the column data entries are consistent. If you have sites that support multiple languages, consider using the Unicode nchar. ” http: //technet. microsoft. com/en-us/library/ms 176089. aspx 1/9/2022 Pierce College CIS 261 SQL 8

Varchar Data Type “Varchar is a variable length data type. The signature for varchar is varchar(n), where n is the length of the string. Valid values for char range from 1 to 8000. Thus a field could have a varchar(8000) data type assignment. This is not uncommon. The signature can also be varchar(max), where max indicates that the maximum storage size is 2 GB. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. Use varchar when the sizes of the column data entries vary considerably. ” http: //technet. microsoft. com/en-us/library/ms 176089. aspx 1/9/2022 Pierce College CIS 261 SQL 9

Nvarchar Data Type “Nvarchar is a variable length Unicode data type. The signature for nvarchar is varchar(n), where n is the length of the string. Valid values for char range from 1 to 4000. Thus a field could have an nvarchar(4000) data type assignment. The signature can also be varchar(max), where max indicates that the maximum storage size is 2 GB. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. Use varchar when the sizes of the column data entries vary considerably. ” http: //technet. microsoft. com/en-us/library/ms 186939. aspx What is Unicode? “A standard for representing characters as integers. Unlike ASCII, which uses 7 bits for each character, Unicode uses 16 bits, which means that it can represent more than 65, 000 unique characters. This is a bit of overkill for English and Western-European languages, but it is necessary for some other languages, such as Greek, Chinese and Japanese. Many analysts believe that as the software industry becomes increasingly global, Unicode will eventually supplant ASCII as the standard character coding format. ” http: //www. webopedia. com/TERM/U/Unicode. html 1/9/2022 Pierce College CIS 261 SQL 10

Uniqueidentifier Data Type “Stores 16 -byte binary values that operate as globally unique identifiers (GUIDs). When inserting a uniquidentifier value, use the built in NEWID function. The Transact-SQL NEWID function generates new uniqueidentifier values from the identification number of their network card plus a unique number from the CPU clock. Each network card has a unique identification number. The uniqueidentifier value that is returned by NEWID is generated by using the network card on the server. The uniqueidentifier value returned by application API functions and methods is generated by using the network card on the client. ” http: //technet. microsoft. com/en-us/library/ms 190215(v=sql. 105). aspx What is a GUID? A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites. http: //technet. microsoft. com/en-us/library/ms 190215(v=sql. 105). aspx 1/9/2022 Pierce College CIS 261 SQL 11

Date. Time Data Type Date. Time date range is between January 1, 1753, through December 31, 9999 Default value is 1900 -01 -01 00: 00 The Time range is 00: 00 through 23: 59. 997 Element Ranges: YYYY is four digits from 1753 through 9999 that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds. https: //technet. microsoft. com/en-us/library/ms 187819(v=sql. 110). aspx 1/9/2022 Pierce College CIS 261 SQL 12

Data Type Exercise Consider a hobby, sport or any activity that you like very much. By whatever means you like, create (2) new tables in the Northwnd database that can represent data used in that activity. • The tables must be structured to contain data that is related in a one-to-many relationship. A classic example of this (but not one you have to use) is an Orders and Order Details table. This means that each row in one table can have a natural INNER JOIN on many rows in the other table. • Between the two tables, you must also make use of each of these data types: int, bit, float, datetime, char or varchar, varbinary and uniqueidentifier. • You can look at the tables in both Northwind and Adventure. Works as a reference as to how they are properly used. This is an in-class activity, so feel free to work with me and each other to determine the appropriate use of these data types. 1/9/2022 Pierce College CIS 261 SQL 13

Built-in Functions “SQL Server provides many built-in functions and also lets you create userdefined functions. ” http: //technet. microsoft. com/en-us/library/ms 174318. aspx 1/9/2022 Pierce College CIS 261 SQL 14

Function Types Function type Definition Rowset Functions Return an object that can be used like table references in an SQL statement. Aggregate Functions Operate on a collection of values but return a single, summarizing value. Ranking Functions Return a ranking value for each row in a partition. Scalar Functions Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. “SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values. For more information, see Deterministic and Nondeterministic Functions in Technet or SQL Server Books Online. ” https: //technet. microsoft. com/en-us/library/ms 174318. aspx 1/9/2022 Pierce College CIS 261 SQL 15

Function Example - IIF “IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true value is returned if the Boolean expression is true, and the false value is returned if the Boolean expression is false or unknown. true value and false value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF. For more information, see CASE http: //technet. microsoft. com/en-us/library/hh 213574. aspx (Transact-SQL). ” 1/9/2022 Pierce College CIS 261 SQL 16

Function Example - CHOOSE “Choose returns the item at the specified index from a list of values in SQL Server 2012. CHOOSE ( index, val_1, val_2 [, val_n ] ) index Is an integer expression that represents a 1 -based index into the list of the items following it. If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null. val_1 … val_n List of comma separated values of any data type. ” http: //technet. microsoft. com/en-us/library/hh 213019. aspx 1/9/2022 Pierce College CIS 261 SQL 17

Function Example - ROUND “The ROUND() function is used to round a numeric field to the number of decimals specified. ROUND ( numeric_expression , length [ , function ] ) numeric_expression - Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. length - Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length. function - Is the type of operation to perform. Function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated. http: //technet. microsoft. com/en-us/library/ms 175003. aspx 1/9/2022 Pierce College CIS 261 SQL 18

Function Example - RAND “Returns a pseudo-random float value from 0 through 1, exclusive. RAND ( [ seed ] ) seed - Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same. Repetitive calls of RAND() with the same seed value return the same results. “ http: //technet. microsoft. com/en-us/library/ms 177610. aspx 1/9/2022 Pierce College CIS 261 SQL 19

Function Example - CASE “CASE evaluates a list of conditions and returns one of multiple possible result expressions. Both formats support an optional ELSE argument. ” “CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select list, IN, WHERE, ORDER BY, and HAVING. “ Simple CASE expression: Searched CASE expression: • “The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned. • Allows only an equality check. • Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause. • Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE. • If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. ” 1/9/2022 • “The searched CASE expression evaluates a set of Boolean expressions to determine the result. • Evaluates, in the order specified, Boolean_expression for each WHEN clause. • Returns result_expression of the first Boolean_expression that evaluates to TRUE. • If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. ” Pierce College CIS 261 SQL http: //technet. microsoft. com/en-us/library/ms 181765. aspx 20

Function Example – Simple CASE “Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. ” This example uses the CASE expression to add a new field expression called Cool. Region. Description based on the Region. ID. http: //technet. microsoft. com/en-us/library/ms 181765. aspx 1/9/2022 Pierce College CIS 261 SQL 21

Function Example – Searched CASE “Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product. ” This example uses the CASE expression to add a new field expression called Price Range based on the Unit. Price field value. http: //technet. microsoft. com/en-us/library/ms 181765. aspx 1/9/2022 Pierce College CIS 261 SQL 22

SQL for slides (Instructor use) SELECT Product. ID, Product. Name, Unit. Price, "Price Range" = • SELECT o. Order. ID WHEN Unit. Price = 0 THEN 'Mfg item - not for resale' • , [Freight] WHEN Unit. Price < 50 THEN 'Under $50' • , RAND([Freight]) WHEN Unit. Price >= 50 and Unit. Price < 250 THEN 'Under $250' • FROM [dbo]. [Orders] o CASE WHEN Unit. Price >= 250 and Unit. Price < 1000 THEN 'Under $1000' ELSE 'Over $1000' • SELECT o. Order. ID, o. Order. Date • , CHOOSE(MONTH(o. Order. Date), 'Winter', • 'Spring', • 'Summer', • 'Autumn', 'Winter') • AS "Season Ordered" • FROM [dbo]. [Orders] o END FROM Products ORDER BY Product. Name • --Simple CASE expression • SELECT [Region. ID], Region. Description • • SELECT o. Order. ID • , [Freight] • , ROUND([Freight], 1) • , ROUND([Freight], -1) • FROM [dbo]. [Orders] o , Cool. Region. Description = CASE[Region. ID] WHEN 1 THEN 'Way Eastern!' • SELECT [Product. ID] • , [Discontinued] • FROM [NORTHWND]. [dbo]. [Products] • SELECT [Product. ID] • , IIF([Discontinued]=0, 'Available!', 'Discontinued!') • AS Discontinued • FROM [NORTHWND]. [dbo]. [Products] WHEN 2 THEN 'Way Western!' • WHEN 3 THEN 'Way Northern!' • WHEN 4 THEN 'Way Southern!' • END • FROM [NORTHWND]. [dbo]. [Region] http: //technet. microsoft. com/en-us/library/ms 181765. aspx 1/9/2022 Pierce College CIS 261 SQL 23

This work is licensed under a Creative Commons Attribution 4. 0 International License Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The ninecollege consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration, Grant #TC-23745 -12 -60 -A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 1/9/2022 Pierce College CIS 261 SQL 24