70-761 Exam: Querying Data With Transact-SQL (SQL Server: Chapter 1)

  1. T-SQL has a very strong foundation in mathematics. Name the evolution of T-SQL which comprises its 5 steps.
    Set Theory & Predicate Logic -> Relational Model -> SQL (Standard) -> T-SQL (Microsoft)
  2. What are the most common SQL best practices?
    (1) Do not use SELECT * in your queries.

    (2) Always use table aliases when your SQL statement involves more than one source.

    (3) Use the more readable ANSI-Standard Join clauses instead of the old style joins.

    (4) Do not use column numbers in the ORDER BY clause.

    (5) Always use a column list in your INSERT statements.

    (6) Don’t ever use double quotes in your T-SQL code.

    (7) Do not prefix your stored procedure names with “sp_”

    (8) Always use a semi-colon to terminate your statement.
  3. What is T-SQL?
    A dialect of standard SQL created by Microsoft; all leading database vendors implement their own dialect version of SQL to their own advantage and purpose.
  4. Relational Model: A common misconception is that the name "relational" has to do with relationships in the table keys in the database (ie, foreign keys) whereas it actually refers to this:
    The mathematical relationship of tables representing data--including data's management and manipulation using these relationships.
  5. How is a "relation" described by the SQL standard?
    SQL attempts to represent a relation with a table which includes a heading and a body. The heading is a set of attributes (columns of a specific type) and a body which is a set of tuples (rows/records).
  6. What is a "set" as defined by George Cantor?
    ...any collection M into a whole of definite, distinct objects m (which are called the "elements" of M) of our perception or of our thought.

    That is, a set should be considered and acted upon as a whole. A set has (a) no duplicates and (b) no order. Collection {a,b,c} is equal to {c,c,a,b}.
  7. What is a sequence?
    An ordered set, such as { a, b, c, f, g}
  8. Is the collection {b, a, c, c, a, c} a set? Why or why not?
    No. It isn't because it contains duplicates.
  9. What is predicate logic? Explain.
    An expression that when attributed to some object, makes a proposition either true or false. For example, "Salary greater than $5,000" is a predicate. It's either true or false.
  10. SQL is based on three-valued predicate logic. Explain.
    True/False. It also deals with unknown values which it calls NULL. When comparing two known values, they are either true/false. However, if either one of them are unknown, then the comparison is also unknown (NULL).

    Always remember, NULL is a "unknown" missing marker...it is not a value itself.
  11. What does the acronym SQL mean?
    Structured Query Language. Originally called SEQUEL but, due to trademark disputes with an airline company, it's name was changed to SQL.
  12. What is the keyed-in order of SQL statements? How does this differ from the processing order?

  13. An alias can be used where in a query?
    Only in the SELECT and ORDER BY statements since these two are the very last to be processed by the compiler.
  14. What is wrong with this non-relational result?

    SELECT empid, firstname + ' ' + lastname FROM HR.Employees
    It's not relational and can't be used as a result since it doesn't contain a valid header (name); simply assign an alias to the name and the expression can then be used as input to another query (relational).
  15. SQL won't try to eliminate _______ unless explicitly instructed to do so. Because of this, a result is considered non-relational and will fail if an attempt was made to use this result.
    Duplicates. In order to remove duplicates in this situation, simply use the DISTINCT keyword and all returned records will have duplicates removed.
  16. Provide 3 ways to call the identifiers for HR (scheme) Employees (table)
    • HR.Employees
    • "HR"."Employees"
    • [HR].[Employees]
  17. All non-regular identifiers must be delimited (ie, [2017] or [Emp ID]). Yet delimiting is optional if the identifier is regular. What condition makes an identifier regular?
    • (1) first character is a letter, underscore, @, or #
    • (2) subsequent characters can include the same or number or $
    • (3) cannot be a reserved word, contain a space, and must not include supplementary characters
  18. What's the difference between VARCHAR and NVARCHAR? When would you use one over the other?

    • (1) Non-Unicode (ASCII) vs. Unicode (pretty much all languages in the world)
    • (2) 1 Byte vs. 2 Bytes per character
    • (3) 8000 character storage vs. 4000

    You would only use NVARCHAR when dealing with foreign languages as VARCHAR deals with only American language.
  19. The query filter "WHERE region <> N'WA'" pulls from a table with NULLS in the region column. How are these records handled?
    They will not be returned by the query even though they are not from WA because NULL values must be handled using the IS NULL operator.
  20. What's wrong with this statement?
    WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10
    Expressions are processed in same logical phase as a whole--so there's no guarantee that SQL will read and process this line from left-to-right. This is also an example of a non-deterministic result (unexpected outcome).
  21. What is the order of precedence when using AND, OR, and NOT
    NOT operator precedes AND, OR...and AND precedes OR.
  22. When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. What are the 8 levels of operator precedence?
    • Level Operators
    • 1 ~ (Bitwise NOT)
    • 2 * (Multiply), / (Division), % (Modulo)
    • 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
    • 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
    • 5 NOT
    • 6 AND
    • 8 = (Assignment)
  23. Give the 5 types of character filters used with the LIKE command and explain each.
    • (1) % includes any string including an empty one ('D%')
    • (2) _ includes a single character ('_D%')
    • (3) [character list] single character from list ('[AC]%' means first character is A or C)
    • (4) [character range] single character from a range ('[0-7]%' means first character is from 0-7)
    • (5) [^character list or range] single character that is not in the list or range ('[0-9]%' means string where first character is not a digit)
  24. SELECT orderid, orderdate, empid 
    FROM Sales.Orders
    WHERE orderdate = '02/12/17';

    How is this interpreted if you are an American? British? Japanese?
    • (a) American February 12, 2017
    • (b) British December 2, 2017
    • (c) Japanese December 17, 2002
  25. What are the two main approaches to interpret a date in SQL when dealing with languages?
    • (1) Use a language neutral expression in form 'YYYYMMDD' with no separators (with separators then this only works for DATE, DATETIME2, and DATETIMEOFFSET)
    • (2) Use convert method to interpret, such as CONVERT(DATE, '02/12/2016', 101) to American.
  26. Write a query using an ordinal ORDER BY clause. Why is this considered bad practice?
    • SELECT EmpId, FirstName, LastName
    • FROM HR.Employees
    • ORDER BY 2, 1, 3

    This is considered bad practice because an update in the SELECT list can result in the ORDER BY clause not being updated.
  27. Write an OFFSET-FETCH clause. Where is it located and explain it.

    Its located after the ORDER BY clause and this one will skip the first 50 rows and return the next 5; In T-SQL, the FETCH clause requires the OFFSET clause and the ORDER BY clause; Also, OFFSET is Standard SQL whereas TOP is not a SQL Standard.
  28. How do you write top 5% of records? Top 10 records? Is parenthesis important? If 8.3% of records are requested, how many are returned? Is TOP considered deterministic?
    • (b) SELECT TOP(10)
    • (c) Yes. It is a SQL standard.
    • (d) 9 records because ceiling is used
    • (e) Only if used with an ORDER BY clause
  29. Write a query to sort orders by shipped date with NULLs sorted last (standard SQL sorts NULLs together, but doesn't specify before or after main list).
    • SELECT orderid, shipdate
    • FROM Orders
    • ORDER BY shipdate ASC NULLS LAST


    • SELECT orderid, shipdate
    • FROM Orders
    • ORDER BY WHEN shipdate IS NULL THEN 1 ELSE 0 END, shipdate
  30. Can an ORDER BY clause contain a column which is not in the SELECT list? What if we use DISTINCT in the SELECT?
    Yes, we can. However, if we use DISTINCT clause, then the ORDER BY list is limited to only elements which appear in the SELECT list.
  31. What are the four set operators in SQL? Provide an example of a set which includes both sets that aren't part of one another.
    • Union (exclude duplicates)
    • Union All (include duplicates)
    • Intersect (SELECT a INTERSECT B)
    • Except (SELECT A EXCEPT B)

    *Non-overlapping areas of both sets A,B? A UNION B EXCEPT (A INTERSECT B)
  32. When querying a column that contains NULL markers, what are the traps?
    (1) Can’t match NULL columns, they aren’t returned using equality operators

    (2) Can’t use ISNULL(a.col1, ‘’) if that same value already exists in column, because extra columns will be returned in the set

    (3) Can use ISNULL(a.col1, ‘999’) if 999 is guaranteed not to be used in EITHER column
  33. Show how to use the INTERSECT operator. What does this operator return?
    Returns only distinct rows that are common to both sets (the intersect).

    • SELECT empid, name FROM Sales
    • SELECT employeeid, firstname from Orders
  34. What rules are used when dealing with set operators? ie., Union, Union All, Except, Intercept.
    • (1) number, type, and order of each column needs to be the same
    • (2) Distinctness-based comparison rather than equality based therefore comparison between two NULLs yield true.
    • (3) Column names of result columns are determined by the first query.
  35. What is a tuple? What is a heading? How does SQL attempt to represent these? Is SQL based more on set theory or multi-set theory? Why?
    • A tuple is an unordered set of known values with names...not the same as a record/row; A heading is a set of attributes. SQL attempts to represent the tuple with a row and an attribute with a column.
    • SQL is more based on multi-set theory..since it is similar to a set but can also have duplicates.
  36. Use TRY_CAST to query all employee ID's greater than 10.
    • SELECT empId, empName, empCity
    • FROM Sales.Employees 
    • WHERE propertyType = 'INT' AND TRY_CAST(empId AS INT) > 10
  37. '02/12/16' can be interpreted in 3 ways; Give two solutions to prevent this type of error.
    • (1) Use a language neutral solution in the form '20160212' 
    • (2) Explicitly convert string using CONVERT function, ie.,: CONVERT(DATE, '02/12/16', 101)
  38. What is wrong with this where clause where orderdate is a DATETIME value: WHERE orderdate BETWEEN '20160401' AND '20160430 23:59:59.999';? How do you prevent this type of error?
    In practice, users store DATETIME values as a single date with values all set to 00:00:00:000 (midnight)..so this first date is caught, however, the second, which is rounded to all zero's, is technically May, 1st. Prevent this type of error by using: orderdate >= '20160401' AND orderdate < '20160501';
  39. Show two queries using OFFSET - FETCH (SQL Standard) one with and one without the FETCH keyword. Create one last one which gets 3 randomly selected records.
    • SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;
    • SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
  40. The following WHERE clause on an OUTER JOIN states: WHERE s.supplierId = p.supplierId AND s.country = N'Japan'; However, other countries outside of Japan are returned. Why? How do we correct this?
    The ON and WHERE clause in an OUTER JOIN behave differently than with INNER JOIN. That is, WHERE still filters the records, however, with ON, a record in the preserved table is still returned in the record set regardless if a record exists in the non-preserved table. Correct this by using an INNER JOIN and then filtering to show only N'Japan' records.
  41. Write a simple join onto itself to determine employee and their managers, along with the lead manager being NULL. Columns include: empId, mgrId, and name; What kind of query is this?
    • SELECT E.empId, E.name As emp, E.name AS mgr
    • FROM HR.Employees E LEFT OUTER JOIN HR.Employees M ON E.mgrId = M.empId;
    • This is called a composite join with NULL columns.
  42. What's a common pitfall when mixing an OUTER JOIN followed by an INNER JOIN? Can this be eliminated?
    • In short, the inner join that followed the outer join can potentially nullify the outer part of the join, resulting in a null and therefore not being included in the set.
    • Of course it can, simply make two OUTER JOINs.
  43. Provide the parameters and use of each: CAST, CONVERT, PARSE, and FORMAT. What are their error-handling equivalents?
    TRY_CAST, TRY_CONVERT, and TRY_PARSE with each attempting to convert but if not, rather than throwing an error, null is returned.
  44. Computer the following modulos values: PRINT 2 % 1;PRINT 10 % 5;PRINT 5 % 10;PRINT 9 % 4;PRINT 10 % 6; PRINT 2.4 % 1
    0,0,5,1,4, 0.4
  45. Define aggregate function and give 5 examples. How are nulls handled in aggregates? If I don't want an integer in an aggregate, how do I do this?
    An aggregate function is a function supplied to  set of rows and gets back a single value. NULLs are not counted. Rather than AVG(qty), apply decimal by saying AVG(1.0 * qty)
  46. Write a query which will return a random number between 1-10;
  47. What does sargable mean? What are the rules? Give three non-sargable WHERE clauses.
    • Short for search argument, a filter predicate is sargable if the optimizer can rely on the index.
    • Rules include (1) no manipulation of filtered column, (2) ranges identified using expressions like  =, <, >=, BETWEEN, Like and not expressions like <> or LIKE with a wildcard.
    • Three examples: (a) WHERE YEAR(orderdate) < 2015; (b) WHERE DATEADD(day, -1, orderdate) < @today; (c) WHERE LEFT(lastname, 1) = 'D';
  48. Data can be manipulated in SQL using DML (data manipulation language). What 5 types are there? Insert 3 simple records using an INSERT statement, a default value, and a NULL value.
    • INSERT INTO Sales.Orders (orderId, name, employee, orderDate) VALUES (1, 'towel', DEFAULT, NULL),(2, 'paper', 'mnobes', NULL),(3, 'roll', DEFAULT, GETDATE());
  49. Write a query to insert records from a stored procedure into a table containing an identity ID column.
    • SET IDENTITY_INSERT Sales.Orders ON;
    • INSERT INTO Sales.Orders (orderid, custid, empid, orderdate) EXEC Sales.GetOrders @country = N'USA';
  50. Write a query to create a new table and insert records into it; make sure to first delete the table if it already exists.
    • DROP TABLE IF EXISTS Sales.MyOrders;
    • SELECT custId, empId, orderdate, country INTO Sales.MyOrders FROM Sales.Orders WHERE country = 'USA';
  51. Diagram and explain ALL join types and show the ON clause statement using Table A, B and relative key.
    • Types include inner, outer (left, right, full), and cross. Respectively, they are:
    • INNER: ON A.key = B.key
    • LEFT: ON A.key = B.key (WHERE B.key IS NULL)
    • RIGHT: ON A.key = B.key  (WHERE A.key IS NULL)
    • FULL: ON A.key = B.key  (WHERE A.key IS NULL AND B.key IS NULL)
    • CROSS: Can't be Venn Diagramed
  52. What are the 5 JOIN types?
    • 1. JOIN or INNER JOIN
    • 2. OUTER JOIN
    •     2.1 LEFT OUTER JOIN or LEFT JOIN
    •     2.3 FULL OUTER JOIN or FULL JOIN
    • 3. NATURAL JOIN (Typically a theoretical self join)
    • 4. CROSS JOIN (Cartesian Product)
    • 5. SELF JOIN (Join to itself)
  53. Explain what a bitwise AND, bitwise OR and bitwise Exlusive OR
    • &=, |=, and ^= are each used on two integer values which are taken down to their bit values and compared such as 170 & 75 produces
    • 0000 0000 1010 1010
    • 0000 0000 0100 1011
    • -------------------
    • 0000 0000 0000 1010
  54. Write a query which deletes 100 records at a time until all are deleted.
    • WHILE 1 =1 
    • BEGIN
    •      DELETE TOP (100) FROM Sales.Orders WHERE ProductType = 'Toilet Paper';

    • IF @@rowcount < 100 BREAK;
    • END
  55. Write a query to Truncate 3 partitions of a table. Write a second query to delete a named cursor.
    • (1) TRUNCATE TABLE Sales.Orders WITH ( PARTITION (1,2,3));
    • (2) DELETE FROM dbo.Orders WHERE CURRENT OF myCustomCursor;
  56. Explain how a common OLTP application can use the MERGE statement.
    A company may receive changes to a database in the middle of the night. A SQL job can be triggered, at say 2:00 am, to pull these records into staging tables of the database and another job can be ran at, say 3:00 am, to MERGE these two tables together so that all changes exist in Production environment on a nightly basis.
  57. Write a simple MERGE statement updating contents of a Production Orders table with a staging table called Stg_Orders.
    • USE tempdb;
    • GO
    • MERGE Production.Orders AS T
    • USING Production.Stg_Orders AS S
    • ON (T.EmployeeID = S.EmployeeID)
    • THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
    • THEN UPDATE SET T.EmployeeName = S.EmployeeName
    • OUTPUT $action, inserted.*, deleted.*;
    • GO
  58. Write a query that simultaneously inserts records into a table using a SELECT statement while also outputing values of each insert.
    • INSERT INTO Sales.Orders (custid, empid, orderdate)
    • OUTPUT inserted.id, inserted.custid, insert.empid, inserted.orderdate
    • SELECT custid, empid, orderdate
    • FROM Sales.Orders
    • WHERE shipcountry = 'Norway';
  59. Write a query that simultaneously deletes records and shows the output of the record at the same time.
    • DELETE 
    • FROM Sales.Orders
    •     OUTPUT deleted.id, deleted.orderid, deleted.orderdate
    • WHERE state = 'NY';
  60. Write code to (a) drop a column (b) add a column with default values including current NULL values (c) Trucate a table (d) add a constraint while adding a new date column
    • (a) ALTER Orders DROP Column B
    • (c) TRUNCATE TABLE Orders 
    • (d) ALTER TABLE Orders ADD requiredate DATE NOT NULL CONSTRAINT dft_Orders_requireddate DEFAULT ('19000101') WITH VALUES;
Card Set
70-761 Exam: Querying Data With Transact-SQL (SQL Server: Chapter 1)
General questions for the 70-761 Querying Data With T-SQL Exam