SQL

Question 1

Cache

Answer 1

d

Question 2

Checkpoint

Answer 2

d

Question 3

Universal No Lock

Answer 3

SET TRANSACTION LEVEL READ COMMITED

Question 4

Json

Answer 4

Identifies data types auto

Question 5

OLAP Cube

Answer 5

Pre-groups to save time.

Question 6

Normalization

Answer 6

d

Question 7

Entity

Answer 7

Tables

Question 8

Attributes

Answer 8

Columns

Question 9

Primary Key

Answer 9

Unique value per row. Can be made up of multiple columns (composite) key. Functions as a target which a foreign key can reference.

Question 10

Candidate Key

Answer 10

Possible primary keys.

Question 11

Surrogate Keys

Answer 11

Alternate primary keys that can be used to check the validity of primary key.

Question 12

Dependency

Answer 12

Columns retrievable by key? In order to lookup a value, you have to know the key first.

Question 13

Denormalization

Answer 13

SAMS vs RAD

Question 14

Duplication

Answer 14

Attribute unnecessarily repeated. (SAMS and RAD duplication.)

Question 15

Fact Table

Answer 15

d

Question 16

Dimension Table

Answer 16

d

Question 17

Forms

Answer 17

Every attribute must provide a fact about the key, the whole key, and nothing but they key, so help me Codd.

Question 18

1st Normal Form

Answer 18

No repeating columns.

Question 19

2nd Normal Form

Answer 19

Non-key columns shouldn’t be dependent on a part of the primary key.

Question 20

3rd Normal Form

Answer 20

All fields must only depend on a key. (3NF)

Question 21

Overnormalization

Answer 21

d

Question 22

String Values

Answer 22

Larger because there are way more than 9(10?) values per character.

Question 23

1 Byte

Answer 23

0 to 255 (8 bit)

Question 24

2 Byte

Answer 24

+-30,000 (smallint)

Question 25

4? Byte

Answer 25

+- 2,000,000,000 (int) (32 bit)

Question 26

8? Byte

Answer 26

bigint (64 bit)

Question 27

Fixed Precision

Answer 27

Decimal/Numeric. More costly. More precise/accurate than float.

Question 28

Scale (Data)

Answer 28

Number of decimal places (123.123 = 3)

Question 29

Floating Point Number

Answer 29

Efficient, but lose accuracy.

Question 30

GUID

Answer 30

Global Unique ID

Question 31

Parse()

Answer 31

Convert strings to dates.

Question 32

Try_Cast()

Answer 32

Same as Cast, but generates nulls for errors. (Cast/Convert/Parse)

Question 33

Schema

Answer 33

Groupings of objects (tables). Can grants permissions based on schemas.

Question 34

Use full object name.

Answer 34

Servicer/Database/Schema/Object. Improves performance because SQL doesn’t have to figure out.

Question 35

Persisted

Answer 35

SQL Function

Question 36

Exists

Answer 36

Returns True/False

Question 37

Data Integrity

Answer 37

d

Question 38

Domain Integrity

Answer 38

Integrity within column (consistent data type, nullable, add rules(1:10), prior to x date)

Question 39

Entity Integrity

Answer 39

Looks across columns. Uniqueness.

Question 40

Referential Integrity

Answer 40

(Order for employee that doesn’t exist)

Question 41

Default Value For Data Integrity

Answer 41

k today(), getdate()

Question 42

Uniqueness Constraint

Answer 42

Generally covered by primary key, but could also be valid for surrogate keys.

Question 43

Referential Enforcement

Answer 43

Cascade :Deletes all children when parent is deleted. Set Default: Catch all value when parent gets deleted. set null

Question 44

CREATE SEQUENCE

Answer 44

D

Question 45

Insert Into With Select

Answer 45

Insert Into Select * FROM WHERE

Question 46

Table Scan

Answer 46

Scans entire table contents.

Question 47

Leaf Level (Index)

Answer 47

All the data.

Question 48

Why not index everything?

Answer 48

Indexes have to be updated every time table changes.

Question 49

Clustered Index

Answer 49

Pyramid

Question 50

Selectivity(Index)

Answer 50

By how much does the field reduce the values to be searched.

Question 51

Density(Index)

Answer 51

Uniqueness

Question 52

Depth(Index)

Answer 52

d

Question 53

Windowed Functions Syntax

Answer 53

Function() Over (Partition By x,y,z Order By a,b,c,x,y,z)

Question 54

Foreign Key

Answer 54

Links to the primary key of another table.

Question 55

Connect to Python

Answer 55

import pyodbc conn = pyodbc.connect(‘Driver={SQL Server};’ ‘Server=he2qntvpsql382;’ ‘Database=MHASAMS01PDB;’ ‘Trusted_Connection=yes;’) cursor = conn.cursor() cursor.execute(‘SELECT Organization FROM MHASAMS01PDB.ra.OrgLookup’) for row in cursor: print(row)

Question 56

Scoping

Answer 56

D

Question 57

OLTP

Answer 57

Online Transaction Processing Typically facilitate and manage transaction-oriented (UPDATE, INSERT, DELETE, ALTER) applications vs analytical (OLAP).

Question 58

ACID

Answer 58

Atomicity - Guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely. Consistency - Ensures that a transaction can only bring the database from one valid state to another (transaction follows constraints, cascades, triggers, etc…) Isolation - Running transactions simultaneously produces the same outcome as running them sequentially. Durability - Once a transaction has been committed, it will remain committed. (Recorded in non-volatile memory).

Question 59

Transaction(db)

Answer 59

A sequence of database operations that satisfies the ACID properties (can be perceived as a single logical operation on the data).

Question 60

OLAP

Answer 60

Online Analytical Processing Optimized for read only. Good for analytics.

Question 61

ODS

Answer 61

Operational Data Store

Question 62

EDW

Answer 62

Enterprise Data Warehouse

Question 63

EDH

Answer 63

Enterprise Data Hub