T-SQL Querying Fundamentals

24 Questions | Total Attempts: 114

SettingsSettingsSettings
Please wait...
T SQL Quizzes & Trivia

Testing the knowledge gained by attending the course T-SQL Querying Fundamentals by SolidQ & SSWUG


Related Topics
Questions and Answers
  • 1. 
    Logical query processing order defines the conceptual order in which the different query clauses are evaluated in SQL to produce the correct query output. Choose from the following answers the one that correctly defines logical query processing order:
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 2. 
    Which of the following queries will not generate an error saying “Invalid column name 'orderyear'.”:
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 3. 
    Which of the following answers describes correctly the meaning of three-valued-logic in SQL:
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 4. 
    The Sales.Customers table stores the location information for each customer in attributes called country, region and city. In some locations the region is applicable, in which case it holds a non-NULL value. For example, in locations in the USA, the region attribute holds the state. In locations that have only two applicable parts—the country and the city—the region is set to NULL, indicating the fact that the location is inapplicable. For example, customers from London, UK have UK in the country attribute, London in the city attribute, and NULL in the region attribute. You are tasked with writing a query that returns all customers that aren’t from the region WA. Your query is supposed to include customers from locations where the region isn’t applicable. Which of the following queries returns the desired result?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 5. 
    Which of the following queries returns the IDs of customers who did not place orders?
    • A. 

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

    • B. 

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

    • C. 

      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;

    • D. 

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

    • E. 

      All answers are correct.

  • 6. 
    Consider the following query, which is supposed to return for each customer the customer ID, the first order ID, and the count of orders: SELECT custid, orderid, COUNT(*) AS numorders FROM Sales.Orders GROUP BY custid; What’s wrong with the query?
    • A. 

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

    • B. 

      Nothing is wrong with the query.

    • C. 

      The query doesn’t include a filter.

    • D. 

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

  • 7. 
    You are supposed to write a query that filters orders placed on or after February 12, 2007. The data type of the orderdate column is DATETIME. It is required that the query will run correctly regardless of the language of the user running the code. Which of the following queries will interpret the date correctly, in a language neutral manner?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

    • E. 

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

  • 8. 
    Consider the following two queries: Query 1: SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C, Sales.Orders AS O WHERE C.custid = O.custid   AND O.orderdate = '20070212'; Query 2: SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C   JOIN Sales.Orders AS O     ON C.custid = O.custid WHERE O.orderdate = '20070212'; Which of the following statements is correct about the two queries?
    • A. 

      Query 1 is nonstandard and Query 2 is.

    • B. 

      Query 2 will always perform better than Query 1.

    • C. 

      Both queries are standard and have the same result.

    • D. 

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

  • 9. 
    A developer wanted to write a query with an inner join between Sales.Employees and Sales.Orders using the syntax with a comma between the table names. The developer forgot to specify a WHERE clause with the join predicate, and ended up writing the following query: SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C, Sales.Orders AS O;   What will happen when you try running this query?
    • A. 

      The query uses invalid syntax and therefore will fail.

    • B. 

      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.

    • C. 

      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.

    • D. 

      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.

  • 10. 
    A developer wanted to write a query with an inner join between Sales.Employees and Sales.Orders using the ANSI SQL-92 syntax with the JOIN keyword between the table names. The developer forgot to specify an ON clause with the join predicate, and ended up writing the following query: SELECT C.custid, C.companyname, O.orderid, O.orderdate FROM Sales.Customers AS C   INNER JOIN Sales.Orders AS O;   What will happen when you try running this query?
    • A. 

      The query uses invalid syntax and therefore will fail.

    • B. 

      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.

    • C. 

      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.

    • D. 

      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.

  • 11. 
    Which of the following queries will return all customers in the output, but return matching orders only to customers from the UK?
    • A. 

      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';

    • B. 

      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';

    • C. 

      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';

    • D. 

      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';

  • 12. 
    Which of the following queries will fail due to incorrect use of table aliases?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 13. 
    Consider the following query: SELECT orderid, orderdate FROM Sales.Orders WHERE custid = (SELECT custid                 FROM Sales.Customers                 WHERE country = N'UK');   What will happen when you try running the query if more than one customer from the UK exists?
    • A. 

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

    • B. 

      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.

    • C. 

      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.

    • D. 

      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.

  • 14. 
    Consider the following query: SELECT empid, firstname, lastname FROM HR.Employees AS E WHERE EXISTS   (SELECT *    FROM Sales.Orders AS O    WHERE O.empid = E.empid      AND O.orderdate >= '20060101'      AND O.orderdate <  '20070101')   OR EXISTS   (SELECT *    FROM Sales.Orders AS O    WHERE O.empid = E.empid      AND O.orderdate >= '20070101'      AND O.orderdate <  '20080101')   AND EXISTS   (SELECT *    FROM Sales.Orders AS O    WHERE O.empid = E.empid      AND O.orderdate >= '20080101'      AND O.orderdate <  '20090101');   Which of the following employees will not be returned?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 15. 
    Which of the following is an incorrect statement about the query used to define a table expression (derived table, CTE, view, inline table-valued function)?
    • A. 

      All columns returned by the query must have names.

    • B. 

      All columns returned by the query must have unique names.

    • C. 

      The query must have an ORDER BY clause.

    • D. 

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

  • 16. 
    You are tasked with writing a query that computes row numbers and filters only the rows with row numbers 11 through 20. Which of the following queries correctly handles the task?
    • A. 

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

    • B. 

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

    • C. 

      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;

    • D. 

      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;

  • 17. 
    Consider the following view definition and the query the follows: CREATE VIEW Sales.MyCustomers AS   SELECT TOP (100) PERCENT custid, country FROM Sales.Customers ORDER BY country; GO   SELECT * FROM Sales.MyCustomers;   Which of the following is a correct statement?
    • A. 

      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.

    • B. 

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

    • C. 

      SQL Server will not allow creating the view.

    • D. 

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

  • 18. 
    What is the difference between the UNION and UNION ALL operators?
    • A. 

      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.

    • B. 

      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.

    • C. 

      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.

    • D. 

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

  • 19. 
    What is a true statement about set operators?
    • A. 

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

    • B. 

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

    • C. 

      Set operators treat two NULLs as different when comparing rows.

    • D. 

      Set operators treat two NULLs as equal when comparing rows.

  • 20. 
    Consider a table called dbo.T1 in your database with the following data: keycol      col1        col2 ----------- ----------- ----------- 1           100         0   You issue the following UPDATE statement against the table: UPDATE dbo.T1   SET col1 = col1 + 100,       col2 = col1;   What will be the value in col2 in the modified row after the update?
    • A. 

      0

    • B. 

      100

    • C. 

      200

    • D. 

      NULL

  • 21. 
    Which of the following statements about DELETE and TRUNCATE isn’t true?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

      DELETE supports a filter whereas TRUNCATE doesn’t.

  • 22. 
    . You are tasked with writing a query that pivots data from the Sales.Orders table. The query should return a row for each distinct customer ID, and a column for each distinct shipper ID in the list 1, 2, 3. In the intersection of each customer ID and shipper ID, the query should return the count of orders. You issue the following PIVOT query: SELECT custid, [1],[2],[3] FROM Sales.Orders   PIVOT(COUNT(orderid) FOR shipperid IN ([1],[2],[3])) AS P;   The query executes successfully, but returns incorrect results. Why?
    • A. 

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

    • B. 

      The query should use a * in the SELECT list.

    • C. 

      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.

    • D. 

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

  • 23. 
    Which of the following queries returns all and only daily, monthly, yearly, and grand aggregates?
    • A. 

      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));

    • B. 

      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) ) );

    • C. 

      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));

    • D. 

      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) ), ( ) );

  • 24. 
    Which of the following queries computes correctly the running total values for each employee and month?
    • A. 

      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;

    • B. 

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

    • C. 

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

    • D. 

      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;