Brainscape's Knowledge GenomeTM

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


See full index

Sql 70-761 Certification > Session 1 - data types and tables > Flashcards

Session 1 - data types and tables Flashcards

1
Q

CURRENT_TIMESTAMP

A

Returns DateTime datatype - LEGACY

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

GETDATE()

A

Returns DateTime

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

SYSDATETIME()

A

Returns datetime2

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

How to get name of day, month from a date

A

DATENAME(interval, DateTime)

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

ADDS AN INCREMENT TO A DATE (DAY, MONTH, YEAR)

A

DATEADD(INTERVAL, INCREMENT, DATETIME)

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

EXTRACTS THE INTERVAL FROM THE GIVEN DATE

A

DATEPART(INTERVAL, DATETIME)

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

SHOWS THE DIFFERENCE IN INTERVAL BETWEEN TWO DATES

A

DATEDIFF(INTERVAL, BEGIN DATE, END DATE)

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

CONVERT DATE TO STRING using convert

A

CONVERT(NVARCHAR(20), DATE, 101 or other number for dates)

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

Convert date time using parse

A

PARSE(‘DATE STRING’ AS DATE [USING ‘es-ES’]for other languages)

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

Convert date time to string using cast

A

FORMAT(CAST(DATE AS DATETIME), ‘dd-MM-yyyy’, ‘language’)

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

If you’re filtering on summarized data, use _____ otherwise use where

A

Having (vs where)

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

Default number of rows you can insert is _____

A

1000 rows (pertaining to defaults)

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

Database Diagrams can create _____ _____ between tables

A

foreign keys (pertaining to database diagrams)

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

Query order of operations

A
  1. FROM, including JOINS
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. WINDOW functions
  6. SELECT
  7. DISTINCT
  8. UNION
  9. ORDER BY
  10. LIMIT and OFFSET
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

List all types of joins

A

INNER - RECORDS EXIST IN BOTH
LEFT (outer) - ALL RECORDS IN FIRST
RIGHT (outer) -ALL RECORDS IN LAST
FULL (outer) - ALL RECORDS FROM BOTH

Cross - A join that puts all rows in table A against all rows in table B (IE if table A has 3 rows 1,2,3 and table B has 3 rows A,B,C the result set would be 1,A, 1,B, 1,C, 2,A, 2,B, 2,C, etc.)

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

4 standard databases

A

Master
Tempdb
Model
Msdb

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

What is masterdb

A

Primary configuration database

Contains information on all the databases that exist on the server

physical database files and their locations.

SQL Server’s configuration settings

login account information.

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

What is tempdb

A

used as a store of:

internal objects,
row versions,
work tables,
temporary tables,
indexes.

Available for use to all participants connected to a SQL Server instance (it is a global resource)

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

What is model database

A

Template for all databases created on an instance of SQL Server.

Model is required because of TempDB.

Newly created user databases use the same recovery model as the model db

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

What is msdb

A

Used by SQL Server Agent for:
scheduling alerts
jobs

and by other features such as:
SQL Server Management Studio,
Service Broker
Database Mail.

backup-and-restore history

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

Convert Syntax

A

Convert(dataTypeToConvertTo, variableToConvert)

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

Cast Syntax

A

Cast(@myvar as DataType)

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

What is type used when working with SIN, COS, TAN, etc

A

Radians

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

What are the three functions for rounding

A

FLOOR
CEILING
ROUND

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

What does the function FLOOR do

A

Rounds down to the nearest 1 (15.6 - 15)

26
Q

What does the function CEILING do?

A

Rounds up to the nearest 1 (15.3 - 16)

27
Q

What does the Round function do?

What’s its syntax?

A

Rounds to the nearest place you specify

ROUND(1543.3435, 2) - 1543.3400

28
Q

How do you get value of PI?

A

select PI() as PI

29
Q

How do you get value of E

A

select EXP(1) as E

30
Q

How do you use exponents?

A

POWER(@myvar, powerToRaiseTo)

31
Q

How do you get a number squared?

A

SQUARE(@myvar)

32
Q

How do you get a square root of a number?

A

SQRT(@myvar)

POWER(@myvar, .5)

33
Q

What’s the syntax for converting but giving null if it doesn’t work?

A 
TRY_CONVERT(tinyint, @myvar)
OR
TRY_CAST(@myvar as tinyint)
34
Q

What is difference between varchar and nvarchar (char and nchar as well)

A

varchar uses roman alphabet (ASCII) and nvarchar uses Unicode (same with char/nchar)

35
Q

What is a derived table?

A

A result set (from select statement) that you then use as a FROM to generate a new query. It’s almost like using a WITH command

36
Q

TinyInt Range and Storage

A

0–255

1 byte

37
Q

Smallint range and storage

A

–2^15 (–32,768) to 2^15–1 (32,767)
~–32,700 – ~32,700
2 Bytes

38
Q

Int Range and Storage

A

–2^31 (–2,147,483,648) to 2^31–1 (2,147,483,647)
~–2 trillion – ~2 trillion
4 Bytes

39
Q

Bigint Range and Storage

A

–2^63 (–9,223,372,036,854,775,808) to 2^63–1 (9,223,372,036,854,775,807)
~–9 quintillion – 9 quintillion
8 Bytes

40
Q

How much space is required for char(10)

A

10 bytes (It’s whatever the number is in the parentheses. It requires 1 byte for every character it holds)

41
Q

How much space is required for varchar(10)

A

Varchar is variable. It will require 1 character per character USED + 2. So ‘test’ would require 6 bytes, and ‘a’ would require 3 bytes.

42
Q

How much space is required for nchar(10)

A
  1. NChar requires 2 bytes per character specified in the parentheses.
43
Q

How much space is required for nvarchar(10)

A

It is however long the string that is being held * 2 + 2. So ‘test’ would be 10 bytes (4 length * 2 + 2) and ‘a’ would be 4 bytes (1 length * 2 + 2).

44
Q

datetime Storage Size, range, accuracy Format

A

YYYY–MM–DD hh:mm:ss[.nnn]
1753–01–01 through 9999–12–31
.00333 second
8 bytes

45
Q

date Storage Size, range, accuracy Format

A

YYYY–MM–DD
0001–01–01 through 9999–12–31
1 day
3 bytes

46
Q

datetime2 Storage Size, range, accuracy Format

A

YYYY–MM–DD hh:mm:ss[.nnnnnnn]
0001–01–01 00:00:00.0000000 through 9999–12–31 23:59:59.9999999

100 nanoseconds
6 to 8

47
Q

smalldatetime Storage Size, range, accuracy format

A

YYYY–MM–DD hh:mm:ss
1900–01–01 through 2079–06–06
1 minute

4 bytes

48
Q

time Storage Size, range, accuracy format

A

hh:mm:ss[.nnnnnnn]
00:00:00.0000000 through 23:59:59.9999999
100 nanoseconds

3 to 5 bytes

49
Q

Datetimeoffset Storage Size, format and range

A

YYYY–MM–DD hh:mm:ss[.nnnnnnn] [+|–]hh:mm
0001–01–01 00:00:00.0000000 through 9999–12–31 23:59:59.9999999 (in UTC)
100 nanoseconds
8 to 10

50
Q

bit data type Storage and values

A

8 or fewer columns – 1 byte
9 – 16 columns – 2 bytes etc.

values: 1, 0 and null

51
Q

numeric and decimal data type storage, values and syntax

A 
–10^38 – 10^38
Precision:
1–10 – 5 bytes
11–20 – 9 bytes
21–29 – 13 bytes
30–38 – 17 bytes

syntax: decimal(p[,s])

p – precision is the total number of digits to be stored (including left and right of decimal)

s – scale (numbers to right of decimal)

Numeric and decimal are synonyms and can be used interchangibly

52
Q

Money data type storage and range

A

–922,337,203,685,477.5808 to 922,337,203,685,477.5807
~–922 trillion – ~922 trillion
8 bytes

53
Q

smallmoney data type storage and range

A

– 214,748.3648 to 214,748.3647
– ~214 thousand to ~214 thousand
4 bytes

54
Q

Exact numeric Datatypes

A 
bigint
int
smallint
tinyint
numeric
decimal
bit
smallmoney
money
55
Q

Approximate numeric data types

A

Float

Real

56
Q

Date and time datatypes

A 
date
datetimeoffset
datetime2
smalldatetime
datetime
time
57
Q

Character strings data types

A

varchar
char
text

58
Q

Unicode strings data types

A

nvarchar
nchar
ntext

59
Q

binary strings data types

A

binary
varbinary
image

60
Q

Other data types

A 
Cursor
Rowversion
Hierarchyid
Uniqueidentifier
Sql_variant
Xml
Spatial geometry types
Spatial geography types
Table
61
Q

What is difference between varchar and nvarchar (char and nchar as well)

A

varchar uses roman alphabet (ASCII) and nvarchar uses Unicode (same with char/nchar)

Sql 70-761 Certification (9 decks)

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