Test 1 - Module 2 Flashcards
Ad-hoc queries
A non-standard inquiry, created to obtain information as the need arises. Contrast with a query that is predefined and routinely processed
Components of a data warehouse
- Operational databases, other internal data, external data
- ETL System (extraction/cleaning/preparation)
- DW DBMS
- BI tools
SQL
- to work with relational databases
- Starts since 1970
- Not a full programming language
- Data sublanguage
SQL categories
DDL: Data Definition Language
DML: Data Manipulation Language
SELECT statement
SELECT [Column]
FROM [Table]
WHERE [condition];
How to eliminate duplicates
SELECT DISTINCT
WHERE
WHERE … AND…
WHERE … IN …
WHERE … NOT IN …
WHERE … BETWEEN …
How to sort results
With the clause ‘ORDER BY’
ORDER BY [Column] DESC, [Column] ASC
//optional operators
Which options WHERE clause has?
AND to add more than one condition
OR to admit one condition OR another
IN to look for a condition of a sub-conjunct
NOT IN to negate a condition of sub-conjunct
BETWEEN to establish ranges
Math symbols
LIKE that admits wildcards _ or % (SQL 92)
Which built-in Functions SQL has
COUNT - counts the amount of rows
SUM - sums the values of a column
AVG - returns the average value of a column
MIN - returns the minimum value of a column
MAX - returns the maximum value of a column
How to group results from a report?
Using the SQL keyword ‘GROUP BY’
How to state GROUP BY keyword?
WHERE is placed before GROUP BY
How restrict grouped results
Using HAVING operator
How to eliminate a ambiguity when using HAVING operator
By always applying WHERE clause before HAVING operator, just for sure
What is the syntax of a subquery in a DDL statement
SELECT SUM (ExtendedPrice) AS Revenue FROM ORDER_ITEM WHERE SKU IN (SELECT SKU FROM SKU_DATA WHERE Department = 'Water Sports');
What’s the syntax to perform a junction between tables
SELECT Column1, Column2
FROM TABLE1, TABLE2
WHERE TABLE1.ColumnX = TABLE2.ColumnY
//admits AND or OR operators on WHERE clause
What’s is the syntax of JOIN ON
SELECT Column1, Column2
FROM TABLE1 JOIN TABLE2
ON TABLE1.ColumnX =
TABLE2.ColumnY
WHERE ColumnWhatever > 1000000How INNER JOIN works?
Returns all rows when there is at least one match in BOTH tables
How LEFT JOIN works?
Return all rows from the left table, and the matched rows from the right table
How RIGHT JOIN works?
Return all rows from the right table, and the matched rows from the left table
How FULL JOIN works?
Return all rows when there is a match in ONE of the tables
What’s the difference between JOIN and subqueries?
In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
Subqueries works in a top-down approach, while JOIN derives “new” tables
