Introduction to SQL Flashcards
What does CAST() function do?
What does TRY_CAST() do?
CAST( AttributeXXX ASDatatype yyy) is the ANSI function to convert the datatype for AttributeXXX to another datatype (yyy) if the attribute value is compatible with the target data type. An error will be returned if incompatible.
E.g.
CAST(ProductID AS varchar(6)) converts the ProductID attribute to a string of 6 characters.
TRY_CAST() acts as CAST() but it returns NULL if some of the values in the dataset to be converted are not compatible with the target datatype.
To be considered as a Data Manipulation Language function?
What does NULLIF() function do?
The NULLIF(Attributename, value) function returns NULL under certain conditions.
E.g.
NULLIF(UnitPriceDiscount, 0) returns NULL if the the attribute UnitPriceDiscount = 0.
What does CONVERT() function do?
What does TRY_CONVERT() function do?
CONVERT (Target Datatype, data item). Identical to CAST - It converts the data item to the Target Datatype if the conversion is legal.
E.g.
CONVERT(varchar(4), ProductID) converts the ProductID data items to characters ( a string) of the type “varchar(4)” if the conversion is possible.
TRY_CONVERT acts just as TRY_CAST, meaning that it converts to NULL whenever the conversion to the desired target datatype is not feasible due to compatibility issues.
Explain what NULL data is?
A NULL value means no value or unknown. It does not mean zero or blank, or even an empty string since those values are not unknown.
DML = ?
DML: Data Manipulation Language
Feature of SQL that allows Queries, + to create, inserte and delete tuples.
DDL = ?
DDL: Data Definition Language
Feature of SEL that allowsthe creation, deletion and modification of definitions for tables and views.
The Basic form of a SQL query: SELECT()
SELECT [DISTINCT] …: select-list = clause selecting the columns to be retained for the result
FROM …: from-list = clause specifying the (cross product of) table(s) to select the columns from.
WHERE …: qualification = clause specifying conditions for selection on the tables from the from clause.
Example:
SELECT DISTINCT S.name, S.age
FROM Sailors S
Selects and presents the 2 columns “name” and “age” for all unique records of the table “Sailor”. Note the use of “S” (so-called Range Variable) as an alias for the table “Sailor” in the query.
SELECT () & Relational algebra
SELECT() can be understood as a stepwize sequence og relational algebra operations:
1 - a cross-product of the tables specified in the from-list.
2- a selection of the relevant tuples through the qualification in the WHERE clause.
3 - a projection of the columns specified the SELECT - list.
Name three datatypes for Exact Numeric data.
Smallint
int
numeric
decimal/numeric
Name 2 datatypes for Approximate Numeric data.
float
real
Name three datatypes for Characters (“strings)
char
varchar
nchar
text
Name three datatypes for Date/Time data?
date
time
datatime
smalldatetime
What does PARSE() function do?
What does TRY_PARSE() function do?
The PARSE() function is designed to convert formatted strings that represent numeric or date/time values.
E.g.
PARSE(‘01/01/2021’ AS date) converts the string “01/01/2021” to a date datatype
TRY_PARSE acts just as TRY_CAST and TRY_CONVERT by converting to NULL whenever the conversion to the desired target datatype is not feasible due to compatibility issues.
What does STR() function do?
The STR function converts a numeric value to a varchar.
E.g.
STR(ListPrice) converts the ListPrice dataitems (a numerical of some kind) to a character string.
ISNULL()
The ISNULL() function takes two arguments. The first is an expression we are testing. If the value of that first argument is NULL, the function returns the second argument. If the first expression is not null, it is returned unchanged. ISNULL is T-SQL.
E.g.
ISNULL(MiddleName, ‘None’) will return ‘None’ if MiddleName is NULL.
