Flashcards
SQL
Cards in this deck (27)
- 1Which category of T-SQL statements concerns querying and modifying data?DML
- 2What are some examples of aggregate functions supported by T-SQL?SUM, MIN, COUNT, MAX, AVG
- 3Which SELECT statement element will be processed before a WHERE clause?FROM
- 4The ___ clause was added to SQL because the ___ keyword could not be used with ___ functions.HAVING, WHERE, aggregate
- 5In any join the output of the first logical step is called the ___ ___.Cartesian product
- 6Define Join Type: 'Cross'Combines all rows in both tables (creates Cartesian product)
- 7Define Join Type: 'Inner'Starts with Cartesian product; applies filter to match rows between tables based on predicate
- 8Define Join Type: 'Outer'Starts with Cartesian product; all rows from designated table preserved, matching rows from other table retrieved. Additional NULLs inserted as placeholders
- 9Why do we prefer tables joined by JOIN operator in FROM Clause (SELECT ... FROM Table1 JOIN Table2 ON over tables joined by commas in FROM Clause (SELECT ... FROM Table1, Table2 WHERE )Tables joined by commas in FROM Clause are not recommended: because Cartesian products can be created accidentally!
- 10If join predicate operator is =, also known as ___ .equi-join
- 11Return all customers and for those who have placed orders, return order information. Customers without matching orders will display NULL for order details.SELECT c.custid, count(o.orderid as 'Number of orders' FROM Sales.Customers c LEFT OUTER JOIN Sales.Orders o ON c.custid = o.custid GROUP BY c.custid ORDER BY count(o.orderid)
- 12Outer Join Syntax: Return all rows from first table, only matches from second:FROM t1 LEFT OUTER JOIN t2 ON t1.col = t2.col
- 13Outer Join Syntax: Return all rows from second table, only matches from first:FROM t1 RIGHT OUTER JOIN t2 ON t1.col = t2.col
- 14Outer Join Syntax: Return only rows from first table with no match in second:FROM t1 LEFT OUTER JOIN t2 ON t1.col = t2.col WHERE t2.col IS NULL
- 15Outer Join Examples: All customers with order details if present:SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON c.custid = o.custid;
- 16Outer Join Examples: Customers that did not place orders:SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON c.custid = o.custid WHERE o.orderid IS NULL;
- 17SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON c.custid = o.custid;Outer Join Examples: All customers with order details if present:
- 18SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON c.custid = o.custid WHERE o.orderid IS NULL;Outer Join Examples: Customers that did not place orders:
- 19What is the difference between Inner Join and Outer Join?Inner join starts with Cartesian product, adds filter
Outer join takes Cartesian output, filtered, adds back non-matching rows (with NULL placeholders) - 20What are some of the useful exceptions for Cross Joins (Cartesian output)?Table of numbers
or
Generating data for testing - 21Cross Join Examples: Create test data by returning all combinations of two inputs:SELECT e1.firstname, e2.lastname
FROM HR.Employees AS e1
CROSS JOIN HR.Employees AS e2; - 22Self Join Examples: Return all employees with ID of employee’s manager when a manager exists (inner join):SELECT e.empid, e.lastname,
e.title, e.mgrid, m.lastname
FROM HR.Employees AS e
JOIN HR.Employees AS m
ON e.mgrid=m.empid ; - 23Self Join Examples: Return all employees with ID of manager (outer join). This will return NULL for the CEO:SELECT e. empid, e.lastname,
e.title, m.mgrid
FROM HR.Employees AS e
LEFT OUTER JOIN HR.Employees AS m
ON e.mgrid=m.empid; - 24How does an inner join differ from an outer join?An inner join filters out rows which do not satisfy the predicate in the ON clause. An outer join includes all rows from both tables and includes NULLs for attributes where no match is found.
- 25Which join types include a logical Cartesian product?CROSS, INNER and OUTER
- 26Can a table be joined to itself?Yes, as a self join. An alias to at least one table is required in the FROM clause.
- 27Name two best practices regarding joining tablesTable aliases should always be defined when joining tables.
Joins should be expressed using SQL-92 syntax, with JOIN and ON keywords.
Information
- Description
- Computer Studies Flashcards on SQL, created by Iván Török on 16/12/2016.
- Created by
- Iván Török
- Views
- 8
- Rating