Basic DB
Queries
Queries+
Spreadsheets
Wildcard
100
Two-dimensional structure with rows and columns.
What is a table?
100
Specifies how data is sorted in a results set.
What is ORDER BY?
100
SELECT COUNT(DISTINCT(counselors))
FROM campers
WHERE year = 2020;
How many unique camp counselors were there in 2020?
100
First requirement when creating a function
What is the equal sign?
100
What is an entity relationship diagram, ERD?
200
This uniquely identifies a row in a table (employee id, SSN, etc..)
What is a primary key?
200
Use the syntax /* */ or --
What is a comment?
200
SELECT *
FROM Orders
WHERE OrderDate = '2008-11-10' AND status = 'returned';
What is all the information for returned items that were originally ordered on November 10, 2008?
200
Function used to replace default error messages with custom messages
What is IFERROR?
200
What is metadata - or a data dictionary?
300
A type of database that 'relates' tables to each other.
Relational database?
300
It is used to rename a table or column. The renaming of the table or column is temporary and does not change the actual name of the table in the database.
What is an alias?
300
SELECT Emp_ID, COUNT(Sale) FROM employee
GROUP BY Emp_ID
ORDER BY 2 DESC
LIMIT 1;
What is the employee that made the most number of sales?
300
Function which returns values based on one or more true or false conditions.
What is IFS?
300
COUNT, SUM, MAX, MIN, AVG
What are the 5 aggregate functions in SQL?
400
Databases that store data in a non-tabular format (e.g. MongoDB, Neo4j, Apache Cassandra)
What is a NoSQL database?
400
This statement is used to specify conditional search
What is WHERE?
400
SELECT CONCAT(first_name, ' ', last_name)
FROM employees
WHERE LOWER(job) LIKE '%scientist%'
ORDER BY salary DESC
LIMIT 3;
What are the names of the 3 highest paid employees with 'scientist' in their job title?
400
Function which allows searches across columns.
What is VLOOKUP?
400
Chart used to display the distribution of a single numeric variable
What is a histogram?
500
The process of arranging information in a way, which removes redundancy and ambiguity
What is normalization?
500
The keyword used to retrieve unique records in specified Columns
What is DISTINCT?
500
SELECT department, SUM(amount) AS q1_sales
FROM sales_table
WHERE Date BETWEEN '01-01-2023' AND '03-31-2023'
GROUP BY department
HAVING q1_sales > 5000;
Which departments had more than 5000 in sales in 2023?
500
Function that counts cells that have values, both letters and numbers.
What is COUNTA?
500
A separator such as a space, comma, or semicolon between elements and imported data in Google Sheets.
What is a delimiter?