-
What does a subquery return if it contains an empty result set? What are the 3 esoteric predicates that operate on subqueries and explain the use of each. Provide 1 example.
- NULL;
- ALL: All predicates return equal to true
- ANY: Any predicates returned equal true
- SOME: Same meaning as ANY
- Welec * From Table1 WHERE unitprice < ALL (select unitprice FROM Products);
-
What is a correlated subquery and provide an example.
- Query in which a subquery is used within the outer query and contains a reference to the outer query.
- SELECT P1.Names
- FROM Products P1
- WHERE unitprice = (SELECT MIN(unitprice) FROM Products AS P2 WHERE P2.id = P1.id);
-
Which performs faster, a subquery solution or a join solution? Why?
Neither, they both have their better solutions and the only way to validate one over the other is to view the query plan to determine which performs quicker.
-
What two forms of the APPLY operator exist and show how to use each.
-
What are the 4 forms of table expressions? How do these differ from temporary tables and table variables?
- (1) Derived table (a named subquery table)
- (2) Common Table Expressions (CTE)
- (3) View
- (4) Inline Table-Valued Functions
- Table expressions are broken into basic table forms by the optimizer, that is, their table isn't persisted and used when placed against other tables, for this, use a temporary table (ie, #table1) or table variables (ie, @table2).
-
For small table sizes, it's better to use these types of tables. And for for larger tables?
- Smaller Tables: Use table variables (ie, @table1)
- Larger Tables: Use temporary tables (ie, #table2)
-
Yo
- SELECT ROW_NUMBER() OVER (PARTITION BY categoryid ORDER BY unitprice, productid) AS rownum, categoryid, productid, productname, unitprice
- FROM Products;
-
Write a basic CTE.
- WITH C1 AS
- (
- SELECT id, unitprice, category, orderdate FROM Product.Orders
- ),
- C2 AS
- (
- SELECT id, unitprice, category, orderdate FROM Product.Orders
- )
- SELECT * FROM C1 INNER JOIN C2 ON C1.id = C2.id WHERE C2.category <= 2;
-
Write a simple GROUP BY query that displays shippers, shipping year, and number of orders. What clause can modify this query to view only those with less than 5 orders to be displayed?
- SELECT shipperid, YEAR(shippeddate) AS shipped, COUNT(*) AS numoforders
- FROM Products.Orders
- GROUP BY shipperid, YEAR(shippeddate)
- You could add the clause: HAVING COUNT(*) < 5
-
Define: GROUPING SETS, CUBE, and ROLLUP.
i dunno.
-
Pivoting is a specialized case of grouping and aggregating of data. Unpivoting is the inverse of pivoting. Pseudocode a pivot example.
- WITH PivotData AS
- (
- SELECT
- <grouping column>
- <spreading column>
- <aggregation column>
- FROM SourceTable
- )
- SELECT *
- FROM PivotData
- PIVOT (<aggregate function>(<aggregate column>)
- FOR <spreading column> IN (<distinct spreading values>)) AS P;
-
Write a pivot table with zero based values for shipping orders.
- WITH PivotData AS
- (
- SELECT
- custid,
- shipperid,
- freight
- FROM Sales.Orders
- )
- SELECT custid,
- ISNULL([1], 0.00) AS [1],
- ISNULL([2], 0.00) AS [2],
- ISNULL([3], 0.00) AS [3]
- FROM PivotData
- PIVOT (SUM(freight) FOR shipperid, IN ([1],[2],[3])) AS P;
-
Write a PARTITION query against the system databases (master, model, msdb, and temp) displaying row #'s followed by another one which calculates row # for sales people based on their year-to-date sales ranking.
- SELECT row_number() over(order by name asc) AS ROW#,
- name, recovery_model_desc
- FROM sys.databases
- WHERE database_id < 5
- ORDER BY name ASC;
- SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
- FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
- FROM Sales.vSalesPerson
- WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
-
Describe the OVER clause when using a partition.
OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results. A partition will not reduce the number of rows returned.
-
Given the table below, what would the two queries below return. Compare & Contrast.
ID, FIRSTNAME MARK
1 Paul 40
2 Joe 45
3 judy 41
1 Paul 20
2 Joe 5
1 Paul 8
(A) SELECT a.id, a.firstname, SUM(Mark) AS Totals FROM Test AS A GROUP BY a.id, a.firstname;
(B) SELECT a.id, a.firstname, SUM(Mark) OVER(PARTITION BY a.id) AS Totals FROM Test AS A;
- ID, FIRSTNAME TOTALS
- 1 Paul 68
- 2 Joe 50
- 3 judy 41
- ID, FIRSTNAME TOTALS
- 1 Paul 68
- 1 Paul 68
- 1 Paul 68
- 2 Joe 50
- 2 Joe 50
- 3 judy 41
-
In plain terms, what are windows functions?
These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. For example, we can use them to get a running total rather than just totals. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others.
-
The query below uses a window function which will tell us what?
SELECT House, FullName, PhysicalSkill, SUM(PhysicalSkill) OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse, MentalSkill, SUM(MentalSkill) OVER (PARTITION BY House) AS TotalMentalSkillByHouseFROM WindowTable
It will break down each record into its simpliest form and show both the granular data as well as the summation data for that group (HOUSE)...essentially, a subset of totals along with underlying data.
-
Write a query to PIVOT the table below to only 2 columns and a summation of the values for each row. What does the final table look like?
course year earning
.net 2012 10,000
java 2012 20,000
.net 2012 5,000
.net 2013 48,000
java 2013 30,000
- SELECT * FROM dbo.Languages
- PIVOT(SUM(Earning)
- FOR Year IN ([2012], [2013])
- AS PVTTable
- course 2012 2013
- .net 15,000 48,000
- java 20,000 30,000
|
|
