Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Query Sql server > Chapter 2 - Lesson 2 > Flashcards

Chapter 2 - Lesson 2 Flashcards

1
Q

What is the diffrence between cast and try-cast?

A

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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the 3 parameters for the convert() function?

A
  1. Target datatype
  2. Expression
  3. Style about how to read that expression.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a literal?

A

A constant. like ‘25-08-1983’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is the format of this literal ‘25-08-983’ and how can i find it?

A

Italian 105. You ca find it at bookonline aka msdn then choosing sql server transact-sql database engine.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How do you convert the literal ‘ 25/08/1983’ to the literal varchar ‘25-08-1983’?

A 
FIRST TO DATE THEN TO VARCHAR, BECAUSE DATE IS STORED AS YYYYMMDD.
SELECT CONVERT(VARCHAR(10) ,CONVERT(DATE, '25/08/1983', 103),105)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

WHAT DOES THE CODE BELOW RETURN AND WHY?

SELECT ‘1’ + 1

A 
-IT RETURNS 2, BECAUSE INT HAS A HIGER PRECEDENCE "VOORRANG" WHEN NOT EXPLICITLY CONVERTED
SELECT CAST('1' AS VARCHAR(1)) + '1'
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What datatyype does the code below return and why?

SELECT 5 /2

A

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))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which 4 key sorts can we use to generate unique identifiers?

A
  1. IDENTITY COLUMN (pk)
  2. SEQUENCE OBJECT (CREATE SEQUENCE)
  3. GUIDS(Global Unique Identifiers)
  4. Custom solution
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which two sorts of GUIDS (Global Unique Identifiers) can we use in sql server?

A
  1. NON sequential = Random @TEST = NEWID()
  2. Sequential

DECLARE @TEST TABLE (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID() )

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the diffrence between these functions?
GETDATE()
CURRENT_TIMESTAMP

A

Returns the current date and time as datetime

The same as getdate() –> this one is the standard

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is the aim of using these functions?
SYSDATETIME()
SYSDATETIMEOFFSET()

A

returns a datetime2 datatype = more precise

returns a datetime2 datatype = more precise with offset

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the diffrence?
GETUTCDATE()
SYSUTCDATETIME()

A

return time in utc terms as datetime
return time in utc terms as datetime2
utc = (coordinated universal time)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can i return the current date or current time separated?

A

cast (CURRENT_TIMESTAMP AS DATE)

cast (CURRENT_TIMESTAMP AS TIME)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can i return only a month, a day or a year for a given date?

A 
use datepart!
SELECT DATEPART(millisecond, CURRENT_TIMESTAMP)
SELECT DATEPART(second, CURRENT_TIMESTAMP)
SELECT DATEPART(year, CURRENT_TIMESTAMP)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How can i return the name of the current month?

A 
use datename!
SELECT DATENAME(month, current_timestamp)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How can i construct a date from the several datetime datatypes?

A

use DATEFROMPARTS()

SELECT DATEFROMPARTS (1983, 08, 25)
SELECT DATETIME2FROMPARTS (1983, 08, 25,1,10,11,22,2)

etc..

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How can i get the last day of this month?

A 
EOMONTH
SELECT EOMONTH(CURRENT_TIMESTAMP)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q 
How can i add or substract a day,month or year for a given date?
SELECT DATEADD(DAY,1,CURRENT_TIMESTAMP)
A 
DATEADD
SELECT DATEADD(DAY,1,CURRENT_TIMESTAMP)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the diffrence between signed and unsigned integer?

A

signed = positive and negative. u can use the + - sign

unsigned = only positive values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How can i calcluat the diffrence in years from a given date until today?

A 
DATEDIFF
SELECT DATEDIFF(year,'1983-08-25',CURRENT_TIMESTAMP)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How can i concatenate two strings? 2 ways

A
  1. ‘x’ + ‘y’

2. concat(‘x’,y)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is the diffrence between concatenating strings according to manner 1 and 2?

  1. ‘x’ + null
  2. concat(y,null)
A
  1. null

2. Returns the not null or nothing. In this case y

23
Q

How can i fix the problem of returning a null when concatenating two strings from which one is null using a + operator?

A

use coalesce

24
Q

How can i get abc from abcde?

A

use substring
SELECT SUBSTRING(‘abcde’, 1, 3)
parameters: expression,start_position,end_position

25
Q 
What is the result of the following two statements?
SELECT LEFT('abcde', 3) 
SELECT RIGHT('abcde', 3)
A
  • abc

- cde

26
Q

How can i find the position first occurence of s in the string ‘janssen’?

A 
CHARINDEX
SELECT CHARINDEX('s','janssen') = 4
27
Q

How can i extract the firstname from the following string ‘Henk Janssen’?

A

SELECT LEFT(‘HENK JANSSEN’,CHARINDEX(‘ ‘,’HENK JANSSEN’) -1) – note: the -1 skips the space

28
Q

How can i search for a pattern in a string like numbers in a given string ‘abcd123efgh’?

A

PATINDEX
SELECT PATINDEX(‘%[0-9]%’, ‘abcd123efgh’)
–result: 5

29
Q

How can i get the length of a given string: ‘Hello World’?

A

LEN
SELECT LEN(‘Hello World ‘)
—-result:11 trailing spaces removed!

30
Q

How can i get the number of bytes for a given object or string?

A 
DATALENGTH
SELECT DATALENGTH(@x)
31
Q 
Are trailing and leading spaces removed with:
	SELECT DATALENGTH(' x ')  ??
A

no a free space is one byte. result is 3

32
Q

What is the diffrence between the folling strings; ‘x’ and N’x’?

A

The N indicates Unicode, which is 2 bytes per character, while ASCII chars only use one byte

33
Q

How can i format the string ‘25-08-1983’ to ‘25/08/1983’?

A 
REPLACE
SELECT REPLACE('25-08-1983', '-', '/')
34
Q

How can i replicate a give string for x number of times like a for 3 times/

A

REPLICATE
SELECT REPLICATE(‘A’,3)
result:AAA

35
Q

What does stuff do?

A

inserts a string into another string.

STUFF ( character_expression , start , length , replaceWith_expression )

SELECT STUFF('test', 2, 3, 'hello');
	--result:thello
36
Q 
What is the output?
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT RTRIM(LTRIM(' HI '))
A

result: ABC
result: abc
result: hi with no spaces

37
Q

The format function?

A

Returns a value formatted with the specified format and optional culture in SQL Server 2014.

SELECT FORMAT(01759, '0000')
	--result:1759
38
Q

What is the output?

SELECT RIGHT('ABCDEFJ',3)
SELECT left('ABCDEFJ',3)
A

result: efg
result: ABC

39
Q

Why is Case an expression and not a statement?

A

Because it evaluates to true or false.

while a statement returns a value.

40
Q

Which 2 case expressions are available?

A

simple: case @var
searched: case when @var then …

41
Q

What is the diffrence between an expression and a statement?

A

An expression can be evaluated to true or false, while a statement envolves an action.

42
Q

Is it recommended to use the ISNULL function? and what does it do?

A

not recomended, it’s like coalesce but supports only 2 parameters

43
Q

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?

A 
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)
44
Q

How does nullif work, and is it recommended?

A

not recommended it returns null if the two parameters are equal and the first one if not

45
Q

Would you use the type FLOAT to represent a product unit price?

A

No, because FLOAT is an approximate data type and cannot represent all values precisely.

46
Q

What is the difference between NEWID and NEWSEQUENTIALID

A 
NEWID = random
NEWSEQUENTIAL = sequential
47
Q

Which function returns the current date and time value as a DATETIME2 type?

A

SYSDATETIME()

48
Q

Why is it important to use the appropriate type for attributes?

A

the type constrains the values to a certain domain of supported values

49
Q

What is diffrence between an expression and a predicate?

A

an expression returns a value

a predicate will be evaluated to a proposition then get the value true or false.

50
Q

Why is it a bad idea to use DATETIME for attributes like orderdate and invoicedate?

A

datetime is 8 bytes of storage use date is only 3 bytes.

51
Q

Is the read performance better with larger or smaller datatypes?

A

The smaller, the faster. So use small datatypes when possible.

52
Q

What is the diffrence between built-in functions and user defined functions?

A

The former is standard available is sql server, while the latter is created by sql server users.

53
Q 
what is the output?
select ascii('A')
A

the ascii number of A

Query Sql server (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions