Chapter 3 - SQL subsection Flashcards
What kind of statement would you use in SQL to recode a variable into categorical data?
CASE statement e.g.
SELECT age, CASE
WHEN age < 18 THEN ‘Under 18’
WHEN age BETWEEN 10 AND 24 THEN ‘18-24’
ELSE ‘25 +’
END AS age_group (this names the new column)
FROM agetable
SELECT REPLACE(Payment, ‘partially paid’, ‘not paid’) does what?
will replace all values with ‘partially paid’ with ‘not paid’
Command to use when merging two datasets and you need to preserve both datasets unaltered?
INTERMEDIATE APPEND
Column name “ID” has 4 digit numbers but as text string and needs converting to decimal. What SQL command can convert the string into decimal with a precision of 10 digits and 2 decimal places?
SELECT PARSE(@ID AS DECIMAL(10, 2))
AS Result
joins data such that ONLY records from BOTH tables appear in the result
INNER JOIN
returns results by joining rows from two or more tables
OUTER JOIN
left outer joins followed by aggregation is also known as what?
BLENDING
transposes columns into rows
UNPIVOT
Excel function - calculate how many business days exist between a start date in the past and an end date in the future
NETWORKDAYS()
Excel function - What Excel function will return a number 1 through 7 to designate the day of the week?
WEEKDAY([startdate])
A data engineer is optimizing hardcoded database queries by filtering the date before running the query results. Prescribe a method of passing data back to the command that filters the data to run different data sets from a single report.
PARAMETERIZATION
This optimizes the data by supplying a ? in the query that filters the data before running the result set.
PARAMETER
It is written for all or a group of records and not a single record
AGGREGATE FUNCTIONS
what clause in SQL limits the number of rows returned?
WHERE
What clause is added to a SQL query to limit the number of columns returned?
SELECT
