Chapter 2: Getting Started with the SELECT Statement

Question 1

What are the roles of the FROM clause?

Answer 1

The FROM clauses has two roles:

(1) Indicate the table which will be queried.
(2) Apply set operators between tables, like JOIN, LEFT JOIN, and so on.

Question 2

Is a good practice type the table with his schema?

Answer 2

Yes, it is. The way which we hit this practice is: [Schema].[Table].

Question 3

What’s considered as best practice to alias a table in a query?

Answer 3

This way: [Schema].[Table] AS [Alias]

Question 4

What is the convention for alias table?

Answer 4

The convention is use short name, and capital letter. For example, the table [HR].[Employee] should be aliased with the letter “E”.

Question 5

Why this query falls: “SELECT E.empid, HR.Employees.firstname, lastname
FROM HR.Employees AS E;”?

Answer 5

Because when we alias a table, the original name of the table, in this case “HR.Employees”, doesn’t exist anymore in the query context. So it’ll fall when we try to reference the original name of the table. Instead of that, we can use the alias “E” or doens’t put a prefix in the column.

Question 6

What are the roles of the SELECT clause?

Answer 6

The SELECT clause has two roles:

(1) It evaluates expressions that define the attributes in the query’s result, assigning them with aliases if it is needed.
(2) Using a DISTINCT clause, you can eliminate duplicates in the results.

Question 7

What’s the difference between DISTINCT clause and GROUP BY clause? Both launch the same results and execution plan!

Answer 7

DISTINCT clause eliminate duplicates in the result, and also groups the result. If we specify more than one column, it’ll group the results (like in the GROUP BY). While the GROUP BY clause, groups the results (also eliminate duplicates) but you can apply aggregate functions to the groups (with DISTINCT clause you cannot do that). So the real difference between both clauses is both groups the result (also eliminate duplicates), but with GROUP BY clause you can apply aggregate functions, while with DISTINCT clause you cannot do that, just show the distinct groups.

Question 8

Why is considered as bad practice list all attributes of a table?

Answer 8

Because it hurts the perfomance of the system. It’s considered as good practice specify the columns which really needs.

Question 9

Which way we can alias a column in a SELECT clause?

Answer 9

It’s considered as good practice alias an expression or a column in the SELECT clause like this way: [Column/Expression] AS [ALIAS].

Question 10

What’s the difference between T - SQL SELECT clause and SQL SELECT clause?

Answer 10

T-SQL allows to do a SELECT clause without a FROM clause. For example, SELECT 10 AS [Number]. This query doesn’t fall, it works.

Question 11

What’s the difference between a regular identifier and an irregular identifier?

Answer 11

An irregular identifier is a reserved T-SQL keyword, or start with a digit, or has embedded space, so it needs to be used with square brackets “[]”. While regular identifier the square brackets are just optional.

Question 12

Mention different categories of data types

Answer 12

Exact Numeric (INT, NUMERIC), character strings (CHAR, VARCHAR), Unicode character strings (NCHAR, NVARCHAR), approximate numeric (DECIMAL, FLOAT, REAL), binary strings (BINARY, VARBINARY), date and time (DATE, DATETIME, DATETIME2, SMALLDATETIME) and so on.

Question 13

How many bytes hold the INT type?

Answer 13

4 byte.

Question 14

How many bytes hold the TINYINT type?

Answer 14

1 byte.

Question 15

How many bytes hold the DATETIME type?

Answer 15

8 byte.

Question 16

How many bytes hold the DATE type?

Answer 16

3 bytes.

Question 17

How many bytes hold the FLOAT type?

Answer 17

8 bytes.

Question 18

How many bytes hold the REAL type?

Answer 18

4 bytes.

Question 19

What’s the difference between CHAR and VARCHAR?

Answer 19

CHAR’s length is static, while VARCHAR’s length is dynamic. For example, CHAR(20). If the string has a length of 15 characters, his storage space will be for 20 characters (5 characters are not being used). Instead we use VARCHAR(20), if the string’s length is 15, the VARCHAR length will be automaticaly 15.

Question 20

What’s the difference between CHAR and NCHAR?

Answer 20

The former is for regular character (english language), while the latter is form Unicode characters (more than one language is supported). A regular character holds one byte per character, while Unicode characters holds two byte per character.

Question 21

What’s the difference between a function which has the “TRY” prefix and a function which doesn’t have the “TRY” as prefix? For example, “TRY_CAST” and “CAST”.

Answer 21

Functions with “TRY” as prefix returns NULL if the conversion doesn’t work correctly, while without “TRY” just fails.

Question 22

What’s the difference between CAST, CONVERT and PARSE function?

Answer 22

With CAST you just indicate the source value and the target type, with CONVERT you indicate the source value, the target type and a third argument which means the style/format of the target type; and with PARSE you use anyformat supported by .NET Framework.

Question 23

Explain the data type precedence.

Answer 23

/NOTE: Precedence means Priority in this context/

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

Question 24

How I can retrieve the current SQL Server Instance date and time?

Answer 24

With the GETDATE built-in function.

Question 25

What's the difference between the function DATEPART and the set of functions YEAR, DAY, MONTH?

Answer 25

With the DATEPART function, you specify date part which you want (for example, year, month or day) and the date which will be extracted; whereas with the YEAR, DAY or MONTH function, you just specifiy the date and it'll return the YEAR, DAY or MONTH, depends which one you use.

Question 26

What's the difference between the function DATEPART and DATENAME?

Answer 26

DATEPART retrieves the value of the date, whereas the DATENAME function retrieves the name of the date. For example, DATEPART(month, '20170728') retrieves '07', while DATENAME(month, '20170728') retrieves 'July' (depends the language's sesion).

Question 27

Which are commonly date functions?

Answer 27

DATEADD and DATEDIFF. The former is for add a specific date to the date passed as argument (day, month or year), whereas DATEDIFF retrieves the difference between two dates depending which criteria is passed (day, month or year).

Question 28

What happens when you try to concatenate a string with a NULL using the '+' operator?

Answer 28

It returns NULL.

Question 29

What happens when you try to concatenate a string with a NULL using the CONCAT function?

Answer 29

If it is NULL one operator, returns an empty string, so it'll concatenate the non-empty string with the empty string, which originally was NULL.

Question 30

What are the functions to extract a substring from a string and know the position?

Answer 30

SUBSTRING(String sourceValue, int startPosition, int subStringLength): extract a string from another string, starting in the "startPosition" value. LEFT(String sourceValue, int subStringLength): returns, from the beginning, a string from another string. RIGHT(String sourceValue, int subStringLength): returns, from the end, a string from another string. The functions above just extract a substring from a string. CHARINDEX(String searchString, String sourceValue): this function returns the position of the first ocurrence of "searchString" in the "sourceValue" string.

Question 31

What are the functions to know the length of a string?

Answer 31

LEN(String value): this function returns the quantity of characters in the given string (also removes the spaces), DATALENGTH(String value): returns the number of bytes of the given string.

Question 32

What are the functions to alter a string?

Answer 32

REPLACE(String sourceValue, String stringPattern, String stringToReplace): find all ocurrences of the "stringPattern" in the "sourceValue" string and replace it with the "stringToReplace" value. REPLICATE(String stringToRepliacate, int n): replicate a string "n" times. STUFF(String sourceValue, int startPosition, int subStringToDeleteLength, String stringToReplaceAfterDelete): this function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.

Question 33

What are the functions to format a string?

Answer 33

UPPER: convert to uppercase all characters in the given string. LOWER: convert to lowercase all characters in the given string. LTRIM: delete spaces from the beginning. RTRIM: delete spaces from the end. FORMAT: READ THIS IN THE DOCUMENTATION.

Question 34

What are the forms of the CASE statement?

Answer 34

``` (1) Simple form: It is used for compare the input expression with one or multiple values. For example: SELECT CASE SecondLastName, WHEN Nationality = Dominican THEN SecondLastName ELSE NULL (2) Searched form: uses predicates with the input expression and return a results depends if the predicate came true. For example: SELECT CASE SecondLastName, WHEN Salary > 5000 THEN SecondLastName WHEN Salary <= 2000 THEN ... ELSE NULL ```