T-Sql

Front 1

What is the order in which query clauses are processed?

Back 1

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Front 2

What type of predicate logic does t-sql use?

Back 2

Three valued predicate logic: TRUE, FALSE and UNKNOWN.

Front 3

What are the four table operators taht SQL 2008 supports?

Back 3

JOIN, APPLY, PIVOT, and UNPIVOT (the last 3 were introduced in SQL 2005)

Front 4

What are the three types of JOINS?

Back 4

CROSS, INNER and OUTER

Front 5

What is a self-join?

Back 5

A JOIN operation (CROSS, INNER or OUTER) against two instances of the same table.

Front 6

Why are table aliases not optional in self-joins?

Back 6

Without table aliases, all column names in the result of the join would be ambiguous.

Front 7

How do yo start and end a block comment in SQL?

Back 7

start: /*
end: */

Front 8

What is a composite join?

Back 8

A join based on a predicate that involves more than one attribute from each side, a primary-foreign key relationship where the relationship is composite.

FROM dbo.Table1 as T1
JOIN dbo.Table2 as T2
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2

Front 9

What is a non-equi join?

Back 9

A join when the condition involves any operator besides equality, e.g. <, >.

Front 10

What are the three logical phases of outer joins?

Back 10

Outer joins apply the two logical phases that inner joins apply (Cartesian product and the ON filter) as well as a third phase called Adding Outer Rows.

Front 11

What does RDBMS stand for?

Back 11

Relational Database Management System.

Front 12

What two branches of mathemtatics is the relational model built on?

Back 12

Set theory and predicate logic

Front 13

When would a table be considered a multiset or bag?

Back 13

When it does not possess a key--you will not be able to uniquely identify rows.

Front 14

What is a predicate?

Back 14

A property or expression that either holds or doesn't hold, i.e. true or false.

Front 15

What is a relation in mathematical terms?

Back 15

A relation is a representation of a set in set theory; in the relational model, it is a table.

Front 16

What is a proposition?

Back 16

A proposition is an assertion or a statement that must be true or false, e.g. employee Itzik Ben-Gan was born on Freb. 12, 1971 and belongs to the IT dept.. If the proposition is true, it will appear as a row in the table.

Front 17

What is a candidate key?

Back 17

A candidate key is a key defined on one or more attributes preventing more than one occurrence of the same tuple (row) in a relation.

Front 18

What are the requirements for 1NF?

Back 18

Rows in the table must be unique and attributes should be atomic.

Front 19

What are the requirements for 2NF?

Back 19

1. data must meet the 1NF requirements.
2. For every candidate key, every nonkey has to be fully functionally dependent on part of the candidate key.

Front 20

What are the requirements of 3NF?

Back 20

1. Data must meet requirements for 2NF.
2. All nonkey attributes must be mutually independent.

Front 21

Why is an OLTP environment not suitable for reporting purposes?

Back 21

A normalized model usually involves many tables (one for each entity) with complex relationships. Even simple reports require joining many tables, resulting in complex and poorly performing queries.

Front 22

What is a data mart?

Back 22

A data warehouse that only serves part of an organization.

Front 23

What is ETL?

Back 23

Extract transform and load--the process that puls data from source systems (OLTP and others), manipulates it, and loads it into a data warehouse. SQL Server's tool for this is SSIS.

Front 24

What is OLAP?

Back 24

Online Analytical Processing.--in SQL Server, the product is SSAS.

Front 25

What is the language used to manage and query SSAS cubes?

Back 25

Multidimensional Expressions (MDX).

Front 26

What is the language SSAS uses to manage and query data mining models?

Back 26

Data Mining Extensions (DMX).

Front 27

What is the DB_ID function?

Back 27

The DB_ID function accepts a database name as input and returns its internal database ID. If a databse with the input name does not exist, the function returns a NULL.
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;

Front 28

What is the OBJECT_ID function?

Back 28

The OBJECT_ID function accepts an object name and type as input. The type 'U' represents a user table. This function returns the internal object ID if an object with the given input name and type exists, and NULL otherwise.

IF OBECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees

Front 29

What is a primary key constraint?

Back 29

A primary key constraint enforces uniqueness of rows and also disallows NULLs in the constraint attribute.

Front 30

What is a unique constraint?

Back 30

A unique constraint enforces uniqueness of rows. Unlike primary keys, multiple unique constraints can be defined in the same table and unique contraints are not restricted to columns defined as NOT NULL.

Front 31

What is the T-SQL code to create a primary key (PK_Employees) on the table dbo.Employees using the empid column?

Back 31

ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_EMPLOYEES
PRIMARY KEY(empid);

Front 32

What is the TSQL code to create a unique constraint (UNQ_Employees) on the dbo.Employees table using the ssn column?

Back 32

ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);

Front 33

What is the TSQL code to create a FK (FK_Orders_Employees) constraint on the empid column in the dbo.Orders table pointing to the empid column in the dbo.Employees table?

Back 33

ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);

Front 34

What does the option ON DELETE CASCASE do?

Back 34

When you delete a row from the referenced table, the RDBMS will delete the related rows from the referencing table.

Front 35

What is a check constraint?

Back 35

It allows you to define a predicate that a row must meet to enter the table or to be modified.

ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);

Front 36

What is a default constraint?

Back 36

It is an expression that is used as the default value when an explicit value is not specified for the attribute when you insert a row.

ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_ordersts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

Front 37

What does a query with an ORDER BY clause return?

Back 37

A cursor--a nonrelational result with order guaranteed among rows.

Front 38

What is the only phase processed after the SELECT phase?

Back 38

The ORDERY BY phase.

Front 39

If you use DISTINCT in the SELECT list, can you ORDER BY an expression not listed in the SELECT list?

Back 39

No; a single result row might represent multiple source rows; therefore, it might not be clear which of the multiple possible values in the ORDER BY expression should be used.

Front 40

How is the WITH TIES option used?

Back 40

When specified with TOP, it returns all other rows from the table that have the same sort value as the last one found.
e.g.
SELECT TOP (5) WITH TIES orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

Front 41

How do you use TOP with percentage?

Back 41

SELECT TOP (1) PERCENT orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

Front 42

Where can you use the OVER clause?

Back 42

Only in the SELECT or ORDER BY phases.

Front 43

What is the result of using empty parentheses with the OVERY clause?

Back 43

It exposes all rows available after the FROM, WHERE, GROUP BY and HAVING phases are complete to the calculation.

Front 44

If you want to restrict the rows that an OVER clause operates on, what can you specify?

Back 44

You can use the PARTITION BY clause:
SELECT SUM(val) OVER(PARTITION BY custid) AS custototalvalue

Front 45

What are the four ranking functions?

Back 45

ROW_NUMBER
RANK
DENSE_RANK
NTILE

Front 46

What does ROW_NUMBER do?

Back 46

It assigns incrementing sequential integers to the rows in the result set of a query based on the logical order that is specified in the ORDERY BY subclause of the OVER clause.
SELECT ROW_NUMBER() OVER(ORDER BY val) AS rownum

Front 47

What is the difference between RANK and DENSE_RANK?

Back 47

RANK indicates how many rows have a lower ordering value while DENSE_RANK indicates how many distinct ordering values are lower.
SELECT orderid,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank

Front 48

What does the NTILE function do?

Back 48

It allows you to associate the rows in the result with tiles--you specify how many--by assigning a tile number to each row.
SELECT NTILE(10) OVER(ORDER BY val) AS ntile

Front 49

Why should you not specify both DISTINCT and ROW_NUMBER in the same SELECT statement?

Back 49

The DISTINCT cluase would have no effect.

Front 50

How do you use the IN predicate?

Back 50

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);

Front 51

How do you use the BETWEEN predicate?

Back 51

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;

Front 52

How do you use the LIKE predicate?

Back 52

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';

Front 53

What does the expression 5/2 return?

Back 53

the integer 2

Front 54

What are the two forms of a CASE expression?

Back 54

Simple and searched.

Front 55

What is a simple CASE expression.

Back 55

It compares one value, or scalar expression, with a list of possible values and returns a value back for the first match. If no match, it returns the value in the ELSE clause (if it exists) or a NULL if it does not.

Front 56

What is a searched CASE expression?

Back 56

It allows you to specify predicates or logical expressions in the WHEN clause rather than restricting to equality comparisons. e.g.
SELECT orderid, custid, val,
CASE
WHEN val < 1000.00 THEN 'Less then 1000'
WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'
WHEN val > 3000.00 THEN 'More than 3000'
ELSE 'Unknown'
END AS valuecategory
FROM Sales.OrderValues;

Front 57

What is meant by SQL's all at once operations?

Back 57

All expressions that appear in the same logical query processing phase are evaluated as if at the same point in time.

Front 58

What are the two character data types that SQL Server supports?

Back 58

regular (CHAR and VARCHAR) and Unicode (NCHAR and NVARCHAR)

Front 59

What is the difference between regular and Unicode data types?

Back 59

Regular data types use one byte of storage for each character while Unicode characters require two bytes per character.

Front 60

How do you express a regular character literal?

Back 60

Use single quotes:
'This is a regular character string literal'

Front 61

How do you express a Unicode character literal?

Back 61

You need to specify the character N (for National) as a prefix:
N'This is a Unicode character string literal'

Front 62

Why are fixed length data types (e.g. CHAR(25) more suited for write-focused systems?

Back 62

SQL server preserves the space, e.g. 25 characters, in the row regardless of the length of the stored character string, so no expansion of the row is required when the strings are expanded. This might result in less than optimal reads however.

Front 63

How much storage space is required for VARCHAR or NVARCHAR data types?

Back 63

SQL server uses as much storage space in the row as required to store the characters that appear n the character string plus two extra bytes for offset data.

Front 64

How are read and update operations affected by VARCHAR or NVARCHAR as compared to CHAR AND NCHAR?

Back 64

Because storage consumption is reduced when compared to fixed-length types, read operations are faster, but updates may result in row expansion which might cause data to be moved outside the current page (and would be less efficient).

Front 65

What is the default threshold for variable-length data types with the MAX specifier (e.g. VARCHAR(MAX)? Where is the data stored?

Back 65

8,000 bytes. It is stored inline in the row. A value size above threshold is stored external to the row as a large object (LOB).

Front 66

What is collation?

Back 66

A property of character data that encapsulates several aspects, including language support (relevant to regular types as Unicode supports all languages), sort order, case sensitivity, accent sensitivity, etc.

Front 67

How do you get the set of supported collations and their descriptions?

Back 67

You can the use table function: fn_helpcollations as follows:
SELECT name description
FROM sys.fn_helpcollations();

Front 68

If BIN appeared in a collation name, what would it indicate?

Back 68

Sorting and comparison of character data is based on binary representation of characters ('A' < 'B' < 'a' < 'b')

Front 69

What does CI in the collation name indicate?

Back 69

The data is case insensitive ('A' = 'a')

Front 70

What does AS in a collation name indicate?

Back 70

The data is accent sensitive.

Front 71

What are the four levels that collation can be defined for?

Back 71

instance, database, column, expression
(the lowest level is the effective one)

Front 72

If you want to make a filter case sensitive when the column's collation is case insensitive, what can you use?

Back 72

CAST, e.g.
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';

Front 73

How do you delimit irregular identifiers like table or column names that include a space or start with a digit?

Back 73

Double quotes., e.g.
"Irregular Identifier"
SQL also supports brackets
[Irregular identifier]

Front 74

How would you delimit a string that has a single quote character as part of the string, .e.g. abc'de?

Back 74

Specify two single quotes
'abc' 'de'.

Front 75

What is the result of concatenation with a NULL?

Back 75

It results in a NULL.

Front 76

Although not recommended, how could you change the default behavior of the way SQL treats concatenation with NULL?

Back 76

SET CONCAT_NULL_YIELDS_NULL OFF;
SQL will treat a NULL as an empty string

Front 77

What can you use to substitute a NULL with an empty string?

Back 77

you can use the COALESCE function. This function accepts a list of input values and returns the first that is not NULL.
SELECT custid, country, region, city, country + N',' + COALESCE(region, N'') + N',' + city AS location
FROM Sales.Customers;

Front 78

What does the SUBSTRING function do?

Back 78

It extracts a substring from a string:
SUBSTRING( string, start, length )
SELECT SUBSTRING('abcde', 1, 3); returns 'abc'

Front 79

What are the LEFT and RIGHT functions?

Back 79

They are abbreviations of the SUBSTRING function, returning a requested number of characters from the left or right of the input string:
LEFT(string, n) where n is the number of charcterts to extract
e.g. SELECT RIGHT('abcde', 3); returns cde

Front 80

What does the LEN function do?

Back 80

Returns the number of characters in the input string:
LEN(string);

Front 81

How would you get the number of bytes in a string?

Back 81

Use the DATALENGTH function:
SELECT DATALENGTH(N'abcde'); returns 10.

Front 82

How do LEN and DATALENGTH differ in their treatment of leading blanks?

Back 82

LEN ignores then while DATALENGTH does not.

Front 83

What does the CHARINDEX function do?

Back 83

It provides the position of the first occurrence of a substring within a string:
CHARINDEX(substring, string[, start_pos])
If the substring is not found, it returns 0.

Front 84

What does the PATINDEX function do?

Back 84

It returns the position of the first occurrence of a pattern within a string:
PATINDEX(pattern, string)
e.g.
SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); returns 5.

Front 85

What does the REPLACE function do?

Back 85

It replaces all occurrences of a substring with another:
REPLACE(string, substring1, substring2)
e.g.
SELECT REPLACE('1-a 2-b', '-', ':'); returns: '1:a 2:b'

Front 86

How could you use the REPLACE function to count the number of occurrences of a character within a string?

Back 86

Replace all occurences of the character with an empty string and calculate the original length minus the new:
LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur

Front 87

What does the REPLICATE function do?

Back 87

It replicates a string a requested number of times:
REPLICATE(string, n), e.g.
SELECT REPLICATE('abc', 3) returns 'abcabcabc'

Front 88

What does the STUFF function do?

Back 88

It allows you to remove a substring from a string and insert a new substring instead:
STUFF(string, pos, delete_length, insertstring)

Front 89

What doe the UPPER and LOWER functions do?

Back 89

Return the string with all uppercase or lowercase, e.g.
SELECT UPPER('hello') returns 'HELLO'

Front 90

What do the RTRIM and LTRIM functions do?

Back 90

They return the input string with leading or trailing spaces removed, e.g.
SELECT RTRIM(LTRIM(' abc ')); returns 'abc'

Front 91

What does a % sign represent when used with the LIKE predicate?

Back 91

A string of any size, e.g.
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';

Front 92

What does an underscore mean when used with the LIKE predicate?

Back 92

It represents a single character.
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'_e%';

Front 93

What do square brackets mean when used with the LIKE predicate?

Back 93

They represent a single character that must be one of the characters specified in the list.
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[ABC]%';

Front 94

What do square brackets with a range represent when used with the LIKE predicate?

Back 94

They represent a single character that must be in the range specified.
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[A-E]%';

Front 95

What doe square brackets with a caret sign followed by a character list or range represent when used with the LIKE predicate?

Back 95

They represent a single character no in the specified character list or range.
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[^A-E]%';

Front 96

How would you look for a character that is also used as a wildcard, e.g. ('%', '_', '[', ']')?

Back 96

You can specify a character you know for sure doesn't appear in the data as an escape character in front of the escape character you are looking for and specify ESCAPE right after the pattern:
LIKE ‘%!_%' ESCAPE ‘!'

Front 97

Prior to SQL 2008, what were the two temporal data types?

Back 97

DATETIME and SMALLDATETIME

Front 98

What are the fournew temporal data types that SQL 2008 introduced?

Back 98

DATE
TIME
DATETIME2
DATETIMEOFFSET

Front 99

Why is it best to use language-neutral formats like 'YYYYMMDD'?

Back 99

They are interpreted the same way independent of language or date format settings.

Front 100

How is the use of indexes impacted when you apply manipulation to the filtered column, such as:
WHERE YEAR(orderdate) = 2007;?

Back 100

In most cases, you cannot use an index in an efficient manner.

Front 101

How could you get the current system time?

Back 101

SELECT CAST(SYSDATETIME() AS TIME) AS [current_time];

Front 102

How could you get the current system date?

Back 102

SELECT CAST(SYSDATETIME() AS DATE) AS [current_date];

Front 103

What is the syntax for CAST?

Back 103

CAST(value AS datatype)

Front 104

What is the syntax for CONVERT?

Back 104

CONVERT(datatype, value [, style number])

Front 105

Why should you use CAST over CONVERT?

Back 105

CAST is ANSI and CONVERT is not.

Front 106

What does the SWITCHOFFSET function do?

Back 106

It adjusts an input DATETIMEOFFSET value to a specified time zone.
SWITCHOFFSET(datetimeoffset_value, time_zone)
e.g. the following code adjusts the current system datetimeoffset value to time zone -05:00:
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

Front 107

What does the TODATETIMEOFFSET function do?

Back 107

It sets the time zone offset of an input date and time value
TODATETIMEOFFSET(date_and_time_value, time_zone)

Front 108

What does the DATEADD function do?

Back 108

It allows you to add a specified number of units of a specified date part.
DATEADD(part, n, dt_val)
Valid values for the part input include year, quarter, month,
dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, and nanosecond

Front 109

What does the DATEDIFF function do?

Back 109

It returns the difference between two date and time values in terms of a specified date part.
DATEDIFF(part, dt_val1, dt_val2)