Session 1 - data types and tables

Question 1

CURRENT_TIMESTAMP

Answer 1

Returns DateTime datatype - LEGACY

Question 2

GETDATE()

Answer 2

Returns DateTime

Question 3

SYSDATETIME()

Answer 3

Returns datetime2

Question 4

How to get name of day, month from a date

Answer 4

DATENAME(interval, DateTime)

Question 5

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

Answer 5

DATEADD(INTERVAL, INCREMENT, DATETIME)

Question 6

EXTRACTS THE INTERVAL FROM THE GIVEN DATE

Answer 6

DATEPART(INTERVAL, DATETIME)

Question 7

SHOWS THE DIFFERENCE IN INTERVAL BETWEEN TWO DATES

Answer 7

DATEDIFF(INTERVAL, BEGIN DATE, END DATE)

Question 8

CONVERT DATE TO STRING using convert

Answer 8

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

Question 9

Convert date time using parse

Answer 9

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

Question 10

Convert date time to string using cast

Answer 10

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

Question 11

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

Answer 11

Having (vs where)

Question 12

Default number of rows you can insert is _____

Answer 12

1000 rows (pertaining to defaults)

Question 13

Database Diagrams can create _____ _____ between tables

Answer 13

foreign keys (pertaining to database diagrams)

Question 14

Query order of operations

Answer 14

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

Question 15

List all types of joins

Answer 15

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

Question 16

4 standard databases

Answer 16

Master
Tempdb
Model
Msdb

Question 17

What is masterdb

Answer 17

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.

Question 18

What is tempdb

Answer 18

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)

Question 19

What is model database

Answer 19

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

Question 20

What is msdb

Answer 20

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

Question 21

Convert Syntax

Answer 21

Convert(dataTypeToConvertTo, variableToConvert)

Question 22

Cast Syntax

Answer 22

Cast(@myvar as DataType)

Question 23

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

Answer 23

Radians

Question 24

What are the three functions for rounding

Answer 24

FLOOR
CEILING
ROUND

Question 25

What does the function FLOOR do

Answer 25

Rounds down to the nearest 1 (15.6 - 15)

Question 26

What does the function CEILING do?

Answer 26

Rounds up to the nearest 1 (15.3 - 16)

Question 27

What does the Round function do? | What's its syntax?

Answer 27

Rounds to the nearest place you specify | ROUND(1543.3435, 2) - 1543.3400

Question 28

How do you get value of PI?

Answer 28

select PI() as PI

Question 29

How do you get value of E

Answer 29

select EXP(1) as E

Question 30

How do you use exponents?

Answer 30

POWER(@myvar, powerToRaiseTo)

Question 31

How do you get a number squared?

Answer 31

SQUARE(@myvar)

Question 32

How do you get a square root of a number?

Answer 32

SQRT(@myvar) | POWER(@myvar, .5)

Question 33

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

Answer 33

``` TRY_CONVERT(tinyint, @myvar) OR TRY_CAST(@myvar as tinyint) ```

Question 34

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

Answer 34

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

Question 35

What is a derived table?

Answer 35

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

Question 36

TinyInt Range and Storage

Answer 36

0–255 | 1 byte

Question 37

Smallint range and storage

Answer 37

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

Question 38

Int Range and Storage

Answer 38

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

Question 39

Bigint Range and Storage

Answer 39

–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

Question 40

How much space is required for char(10)

Answer 40

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

Question 41

How much space is required for varchar(10)

Answer 41

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

Question 42

How much space is required for nchar(10)

Answer 42

20. NChar requires 2 bytes per character specified in the parentheses.

Question 43

How much space is required for nvarchar(10)

Answer 43

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

Question 44

datetime Storage Size, range, accuracy Format

Answer 44

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

Question 45

date Storage Size, range, accuracy Format

Answer 45

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

Question 46

datetime2 Storage Size, range, accuracy Format

Answer 46

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

Question 47

smalldatetime Storage Size, range, accuracy format

Answer 47

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

Question 48

time Storage Size, range, accuracy format

Answer 48

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

Question 49

Datetimeoffset Storage Size, format and range

Answer 49

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

Question 50

bit data type Storage and values

Answer 50

8 or fewer columns – 1 byte 9 – 16 columns – 2 bytes etc. values: 1, 0 and null

Question 51

numeric and decimal data type storage, values and syntax

Answer 51

``` –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

Question 52

Money data type storage and range

Answer 52

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

Question 53

smallmoney data type storage and range

Answer 53

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

Question 54

Exact numeric Datatypes

Answer 54

``` bigint int smallint tinyint numeric decimal bit smallmoney money ```

Question 55

Approximate numeric data types

Answer 55

Float | Real

Question 56

Date and time datatypes

Answer 56

``` date datetimeoffset datetime2 smalldatetime datetime time ```

Question 57

Character strings data types

Answer 57

varchar char text

Question 58

Unicode strings data types

Answer 58

nvarchar nchar ntext

Question 59

binary strings data types

Answer 59

binary varbinary image

Question 60

Other data types

Answer 60

``` Cursor Rowversion Hierarchyid Uniqueidentifier Sql_variant Xml Spatial geometry types Spatial geography types Table ```

Question 61

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

Answer 61

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