T-SQL Querying Fundamentals

SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY

FROM>WHERE>GROUP BY>HAVING>ORDER BY>SELECT

FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY

FROM>WHERE>SELECT>GROUP BY>HAVING>ORDER BY

SELECT orderid, YEAR(orderdate) AS orderyear FROM Sales.Orders WHERE orderyear > 2006;

SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders FROM Sales.Orders GROUP BY orderyear;

SELECT orderid, YEAR(orderdate) AS orderyear, orderyear + 1 AS nextyear FROM Sales.Orders;

SELECT orderid, YEAR(orderdate) AS orderyear FROM Sales.Orders ORDER BY orderyear;

Three-valued-logic describes the three possible logical results of a predicate: true, false and unknown.

Three-valued-logic describes the three possible logical results of a predicate: true, false and NULL.

Three-valued-logic describes the three possible states of an integer expression: negative, positive and zero.

Three-valued-logic describes the three possible results of the SIGN function: -1, 1 and 0.

SELECT custid, country, region, city FROM Sales.Customers WHERE region N'WA' AND region IS NULL;

SELECT custid, country, region, city FROM Sales.Customers WHERE region N'WA' OR region IS NULL;

SELECT custid, country, region, city FROM Sales.Customers WHERE region N'WA';

SELECT custid, country, region, city FROM Sales.Customers WHERE NOT (region = N'WA');

SELECT custid FROM Sales.Customers AS C WHERE NOT EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid);

SELECT custid FROM Sales.Customers EXCEPT SELECT custid FROM Sales.Orders;

SELECT C.custid FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON C.custid = O.custid WHERE O.orderid IS NULL;

SELECT custid FROM Sales.Customers AS C WHERE custid NOT IN (SELECT custid FROM Sales.Orders);

All answers are correct.

The query attempts to return the column orderid even though the column neither appears in the GROUP BY list nor in an aggregate function.

Nothing is wrong with the query.

The query doesn’t include a filter.

The query uses a COUNT(*) aggregate while it should use COUNT(orderid).

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '2007-02-12';

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '02/12/2007';

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '20070212';

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '12/02/2007';

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= 'Feb 12, 2007';

Query 1 is nonstandard and Query 2 is.

Query 2 will always perform better than Query 1.

Both queries are standard and have the same result.

Query 1 uses a syntax that is deprecated while Query 2 uses supported syntax.

The query uses invalid syntax and therefore will fail.

SQL Server will figure out which columns to join by based on the same column names, and the query will return the desired results of the intended inner join.

SQL Server will figure out which columns to join by based on primary key-foreign key relationship, and the query will return the desired results of the intended inner join.

The query is syntactically valid, but it now represents a cross join between the tables involved, so it will run successfully but won’t return the intended result.

The query uses invalid syntax and therefore will fail.

SQL Server will figure out which columns to join by based on the same column names, and the query will return the desired results of the intended inner join.

SQL Server will figure out which columns to join by based on primary key-foreign key relationship, and the query will return the desired results of the intended inner join.

The query is syntactically valid, but it now represents a cross join between the tables involved, so it will run successfully but won’t return the intended result.

SELECT C.custid, C.country, O.orderid, O.orderdate FROM Sales.Customers AS C JOIN Sales.Orders AS O ON C.custid = O.custid WHERE C.country = N'UK';

SELECT C.custid, C.country, O.orderid, O.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON C.custid = O.custid AND C.country = N'UK';

SELECT C.custid, C.country, O.orderid, O.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON C.custid = O.custid WHERE C.country = N'UK';

SELECT C.custid, C.country, O.orderid, O.orderdate FROM Sales.Customers AS C JOIN Sales.Orders AS O ON C.custid = O.custid AND C.country = N'UK';

SELECT C.custid, orderid, orderdate FROM Sales.Customers AS C JOIN Sales.Orders AS O ON C.custid = O.custid;

SELECT Customers.custid, Orders.orderid, Orders.orderdate FROM Sales.Customers AS C JOIN Sales.Orders AS O ON Customers.custid = Orders.custid;

SELECT C.custid, O.orderid, O.orderdate FROM Sales.Customers AS C JOIN Sales.Orders AS O ON C.custid = O.custid;

SELECT Customers.custid, Orders.orderid, Orders.orderdate FROM Sales.Customers JOIN Sales.Orders ON Customers.custid = Orders.custid;

The query will fail at run time indicating that the subquery returns more than one value.

The subquery will return the customer ID of the first customer from the UK that is found, and therefore the outer query will return the orders placed by that customer.

As soon as the subquery identifies more than one matching customer ID it converts the result to a NULL, and therefore the outer query returns an empty set.

As soon as the subquery identifies more than one matching customer ID it converts the operator from = (equality) to IN, and the query will correctly return all orders placed by customers from the UK.

Employees who did not handle orders in 2007, but did handle orders in 2006 and in 2008.

Employees who did not handle orders in 2006, but did handle orders in 2007 and in 2008.

Employees who did not handle orders in 2007 and 2008, but did handle orders in 2006.

Employees who did not handle orders in 2006 and 2008, but did handle orders in 2007.

All columns returned by the query must have names.

All columns returned by the query must have unique names.

The query must have an ORDER BY clause.

The query isn’t allowed to have an ORDER BY clause unless TOP/OFFSET-FETCH/FOR XML also specified.

SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE ROW_NUMBER() OVER(ORDER BY orderid) BETWEEN 11 AND 20;

SELECT orderid, orderdate, custid, empid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.Orders WHERE rownum BETWEEN 11 AND 20;

WITH C AS ( SELECT orderid, orderdate, custid, empid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.Orders ) SELECT orderid, orderdate, custid, empid FROM C HAVING rownum BETWEEN 11 AND 20;

WITH C AS ( SELECT orderid, orderdate, custid, empid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.Orders ) SELECT orderid, orderdate, custid, empid FROM C WHERE rownum BETWEEN 11 AND 20;

SQL Server will allow creating the view but will not guarantee presentation ordering in the output of the query against the view because it doesn’t have an ORDER BY clause.

SQL Server will allow creating the view and the outer query is guaranteed to return the rows in country order.

SQL Server will not allow creating the view.

SQL Server will allow creating the view but will fail with a severe error when trying to run the query against the view.

The UNION ALL operator unifies the rows from the input queries and eliminates duplicates from the result, whereas the UNION operator just unifies the rows without trying to eliminate duplicates.

The UNION operator unifies the rows from the input queries and eliminates duplicates from the result, whereas the UNION operator just unifies the rows without trying to eliminate duplicates.

The UNION operator eliminates duplicates from each of the individual input queries, then unifies the rows, whereas the UNION ALL operator first unifies the rows from the input queries, then removes duplicates.

UNION ALL and UNION are the same; it’s just that the ALL keyword is optional.

Set operators require all columns to have names in both queries.

Set operators require corresponding column names in the two input queries to be identical.

Set operators treat two NULLs as different when comparing rows.

Set operators treat two NULLs as equal when comparing rows.

0

100

200

NULL

DELETE is a DML statement whereas TRUNCATE is a DDL statement according to the standard.

DELETE doesn’t reseed an identity column property whereas TRUNCATE does.

DELETE is a fully logged operation whereas TRUNCATE is a minimally logged one.

DELETE supports a filter whereas TRUNCATE doesn’t.

The query shouldn’t delimit the shipper IDs in the IN clause because they are values, not identifiers.

The query should use a * in the SELECT list.

The query implicitly groups the rows by all columns besides the ones used for aggregation (orderid) and spreading (shipperid). This includes other columns besides the intended grouping column custid.

There’s nothing wrong with the query, and in fact it does return the desired results.

SELECT YEAR(orderdate) AS orderyear, MONTH(orderdate) AS ordermonth, DAY(orderdate) AS orderday, SUM(val) AS sumval FROM Sales.OrderValues GROUP BY CUBE(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

SELECT YEAR(orderdate) AS orderyear, MONTH(orderdate) AS ordermonth, DAY(orderdate) AS orderday, SUM(val) AS sumval FROM Sales.OrderValues GROUP BY GROUPING SETS ( ( YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ), ( YEAR(orderdate), MONTH(orderdate) ), ( YEAR(orderdate) ) );

SELECT YEAR(orderdate) AS orderyear, MONTH(orderdate) AS ordermonth, DAY(orderdate) AS orderday, SUM(val) AS sumval FROM Sales.OrderValues GROUP BY ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

SELECT YEAR(orderdate) AS orderyear, MONTH(orderdate) AS ordermonth, DAY(orderdate) AS orderday, SUM(val) AS sumval FROM Sales.OrderValues GROUP BY GROUPING SETS ( ( YEAR(orderdate) ), ( MONTH(orderdate) ), ( DAY(orderdate) ), ( ) );

SELECT empid, ordermonth, val, SUM(val) OVER(PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS runningtotalval FROM Sales.EmpOrders;

SELECT empid, ordermonth, val, SUM(val) OVER(PARTITION BY empid) AS runningtotalval FROM Sales.EmpOrders ORDER BY ordermonth;

SELECT empid, ordermonth, val, SUM(val) OVER() AS runningtotalval FROM Sales.EmpOrders GROUP BY empid ORDER BY ordermonth;

SELECT empid, ordermonth, val, SUM(val) OVER(PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotalval FROM Sales.EmpOrders;