Test 1 - Module 2

Question 1

Ad-hoc queries

Answer 1

A non-standard inquiry, created to obtain information as the need arises. Contrast with a query that is predefined and routinely processed

Question 2

Components of a data warehouse

Answer 2

• Operational databases, other internal data, external data
• ETL System (extraction/cleaning/preparation)
• DW DBMS
• BI tools

Question 3

SQL

Answer 3

• to work with relational databases
• Starts since 1970
• Not a full programming language
• Data sublanguage

Question 4

SQL categories

Answer 4

DDL: Data Definition Language
DML: Data Manipulation Language

Question 5

SELECT statement

Answer 5

SELECT [Column]
FROM [Table]
WHERE [condition];

Question 6

How to eliminate duplicates

Answer 6

SELECT DISTINCT

Question 7

WHERE

Answer 7

WHERE … AND…
WHERE … IN …
WHERE … NOT IN …
WHERE … BETWEEN …

Question 8

How to sort results

Answer 8

With the clause ‘ORDER BY’
ORDER BY [Column] DESC, [Column] ASC
//optional operators

Question 9

Which options WHERE clause has?

Answer 9

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)

Question 10

Which built-in Functions SQL has

Answer 10

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

Question 11

How to group results from a report?

Answer 11

Using the SQL keyword ‘GROUP BY’

Question 12

How to state GROUP BY keyword?

Answer 12

WHERE is placed before GROUP BY

Question 13

How restrict grouped results

Answer 13

Using HAVING operator

Question 14

How to eliminate a ambiguity when using HAVING operator

Answer 14

By always applying WHERE clause before HAVING operator, just for sure

Question 15

What is the syntax of a subquery in a DDL statement

Answer 15

SELECT SUM (ExtendedPrice) AS Revenue
FROM ORDER_ITEM
WHERE SKU IN
	(SELECT SKU
	FROM SKU_DATA
	WHERE Department = 'Water Sports');

Question 16

What’s the syntax to perform a junction between tables

Answer 16

SELECT Column1, Column2
FROM TABLE1, TABLE2
WHERE TABLE1.ColumnX = TABLE2.ColumnY
//admits AND or OR operators on WHERE clause

Question 17

What’s is the syntax of JOIN ON

Answer 17

SELECT Column1, Column2
FROM TABLE1 JOIN TABLE2
     ON TABLE1.ColumnX =
            TABLE2.ColumnY
WHERE ColumnWhatever > 1000000

Question 18

How INNER JOIN works?

Answer 18

Returns all rows when there is at least one match in BOTH tables

Question 19

How LEFT JOIN works?

Answer 19

Return all rows from the left table, and the matched rows from the right table

Question 20

How RIGHT JOIN works?

Answer 20

Return all rows from the right table, and the matched rows from the left table

Question 21

How FULL JOIN works?

Answer 21

Return all rows when there is a match in ONE of the tables

Question 22

What’s the difference between JOIN and subqueries?

Answer 22

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