Chapter 2 - Lesson 2 Flashcards
What is the diffrence between cast and try-cast?
cast fails fails when not convertable, while try_cast returns a null mark.
SELECT CAST('ABC' AS INT) -- FAILS
SELECT TRY_CAST('ABC' AS INT) -- RETURNS NULL
What are the 3 parameters for the convert() function?
- Target datatype
- Expression
- Style about how to read that expression.
What is a literal?
A constant. like ‘25-08-1983’
What is the format of this literal ‘25-08-983’ and how can i find it?
Italian 105. You ca find it at bookonline aka msdn then choosing sql server transact-sql database engine.
How do you convert the literal ‘ 25/08/1983’ to the literal varchar ‘25-08-1983’?
FIRST TO DATE THEN TO VARCHAR, BECAUSE DATE IS STORED AS YYYYMMDD. SELECT CONVERT(VARCHAR(10) ,CONVERT(DATE, '25/08/1983', 103),105)
WHAT DOES THE CODE BELOW RETURN AND WHY?
SELECT ‘1’ + 1
-IT RETURNS 2, BECAUSE INT HAS A HIGER PRECEDENCE "VOORRANG" WHEN NOT EXPLICITLY CONVERTED
SELECT CAST('1' AS VARCHAR(1)) + '1'What datatyype does the code below return and why?
SELECT 5 /2
It returns int, because both operands are of the type int.
cast it to numeric to get 2.5
SELECT CAST(5 AS NUMERIC(9,4)) / CAST(2 AS NUMERIC(9,4))
Which 4 key sorts can we use to generate unique identifiers?
- IDENTITY COLUMN (pk)
- SEQUENCE OBJECT (CREATE SEQUENCE)
- GUIDS(Global Unique Identifiers)
- Custom solution
Which two sorts of GUIDS (Global Unique Identifiers) can we use in sql server?
- NON sequential = Random @TEST = NEWID()
- Sequential
DECLARE @TEST TABLE (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID() )
What is the diffrence between these functions?
GETDATE()
CURRENT_TIMESTAMP
Returns the current date and time as datetime
The same as getdate() –> this one is the standard
What is the aim of using these functions?
SYSDATETIME()
SYSDATETIMEOFFSET()
returns a datetime2 datatype = more precise
returns a datetime2 datatype = more precise with offset
What is the diffrence?
GETUTCDATE()
SYSUTCDATETIME()
return time in utc terms as datetime
return time in utc terms as datetime2
utc = (coordinated universal time)
How can i return the current date or current time separated?
cast (CURRENT_TIMESTAMP AS DATE)
cast (CURRENT_TIMESTAMP AS TIME)
How can i return only a month, a day or a year for a given date?
use datepart! SELECT DATEPART(millisecond, CURRENT_TIMESTAMP) SELECT DATEPART(second, CURRENT_TIMESTAMP) SELECT DATEPART(year, CURRENT_TIMESTAMP)
How can i return the name of the current month?
use datename! SELECT DATENAME(month, current_timestamp)
How can i construct a date from the several datetime datatypes?
use DATEFROMPARTS()
SELECT DATEFROMPARTS (1983, 08, 25) SELECT DATETIME2FROMPARTS (1983, 08, 25,1,10,11,22,2)
etc..
How can i get the last day of this month?
EOMONTH SELECT EOMONTH(CURRENT_TIMESTAMP)
How can i add or substract a day,month or year for a given date? SELECT DATEADD(DAY,1,CURRENT_TIMESTAMP)
DATEADD SELECT DATEADD(DAY,1,CURRENT_TIMESTAMP)
What is the diffrence between signed and unsigned integer?
signed = positive and negative. u can use the + - sign
unsigned = only positive values
How can i calcluat the diffrence in years from a given date until today?
DATEDIFF SELECT DATEDIFF(year,'1983-08-25',CURRENT_TIMESTAMP)
How can i concatenate two strings? 2 ways
- ‘x’ + ‘y’
2. concat(‘x’,y)
What is the diffrence between concatenating strings according to manner 1 and 2?
- ‘x’ + null
- concat(y,null)
- null
2. Returns the not null or nothing. In this case y
How can i fix the problem of returning a null when concatenating two strings from which one is null using a + operator?
use coalesce
How can i get abc from abcde?
use substring
SELECT SUBSTRING(‘abcde’, 1, 3)
parameters: expression,start_position,end_position
What is the result of the following two statements?
SELECT LEFT('abcde', 3)
SELECT RIGHT('abcde', 3)- abc
- cde
How can i find the position first occurence of s in the string ‘janssen’?
CHARINDEX
SELECT CHARINDEX('s','janssen') = 4How can i extract the firstname from the following string ‘Henk Janssen’?
SELECT LEFT(‘HENK JANSSEN’,CHARINDEX(‘ ‘,’HENK JANSSEN’) -1) – note: the -1 skips the space
How can i search for a pattern in a string like numbers in a given string ‘abcd123efgh’?
PATINDEX
SELECT PATINDEX(‘%[0-9]%’, ‘abcd123efgh’)
–result: 5
How can i get the length of a given string: ‘Hello World’?
LEN
SELECT LEN(‘Hello World ‘)
—-result:11 trailing spaces removed!
How can i get the number of bytes for a given object or string?
DATALENGTH SELECT DATALENGTH(@x)
Are trailing and leading spaces removed with:
SELECT DATALENGTH(' x ') ??no a free space is one byte. result is 3
What is the diffrence between the folling strings; ‘x’ and N’x’?
The N indicates Unicode, which is 2 bytes per character, while ASCII chars only use one byte
How can i format the string ‘25-08-1983’ to ‘25/08/1983’?
REPLACE
SELECT REPLACE('25-08-1983', '-', '/')How can i replicate a give string for x number of times like a for 3 times/
REPLICATE
SELECT REPLICATE(‘A’,3)
result:AAA
What does stuff do?
inserts a string into another string.
STUFF ( character_expression , start , length , replaceWith_expression )
SELECT STUFF('test', 2, 3, 'hello');
--result:thello
What is the output?
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT RTRIM(LTRIM(' HI '))result: ABC
result: abc
result: hi with no spaces
The format function?
Returns a value formatted with the specified format and optional culture in SQL Server 2014.
SELECT FORMAT(01759, '0000') --result:1759
What is the output?
SELECT RIGHT('ABCDEFJ',3)
SELECT left('ABCDEFJ',3)
result: efg
result: ABC
Why is Case an expression and not a statement?
Because it evaluates to true or false.
while a statement returns a value.
Which 2 case expressions are available?
simple: case @var
searched: case when @var then …
What is the diffrence between an expression and a statement?
An expression can be evaluated to true or false, while a statement envolves an action.
Is it recommended to use the ISNULL function? and what does it do?
not recomended, it’s like coalesce but supports only 2 parameters
Does the use of coalesce influence the performance of your query when used to lay a join between two null marked tupels?, and how can i solve that?
yes it does.Check the example below. ON COALESCE(E.EMPID,' ') = COALESCE (L.EMPID,' ') better: ON E.EMPID = L.EMPID OR (E.EMPID IS NULL AND L.EMPID IS NULL)
How does nullif work, and is it recommended?
not recommended it returns null if the two parameters are equal and the first one if not
Would you use the type FLOAT to represent a product unit price?
No, because FLOAT is an approximate data type and cannot represent all values precisely.
What is the difference between NEWID and NEWSEQUENTIALID
NEWID = random NEWSEQUENTIAL = sequential
Which function returns the current date and time value as a DATETIME2 type?
SYSDATETIME()
Why is it important to use the appropriate type for attributes?
the type constrains the values to a certain domain of supported values
What is diffrence between an expression and a predicate?
an expression returns a value
a predicate will be evaluated to a proposition then get the value true or false.
Why is it a bad idea to use DATETIME for attributes like orderdate and invoicedate?
datetime is 8 bytes of storage use date is only 3 bytes.
Is the read performance better with larger or smaller datatypes?
The smaller, the faster. So use small datatypes when possible.
What is the diffrence between built-in functions and user defined functions?
The former is standard available is sql server, while the latter is created by sql server users.
what is the output?
select ascii('A')the ascii number of A
