SQL Flashcards
From Facebook Blueprint SQL lessons (not included in the MarSci Study Guide)
Name this type of JOIN:
LEFT JOIN
Name this type of JOIN:
FULL OUTER JOIN
Name this type of JOIN:
INNER JOIN
Name this type of JOIN:
RIGHT JOIN
What is the name of the programming language designed to communicate with databases?
SQL
SQL stores data in tables, also known as what?
relational database structures
What SQL query is considered the most basic form?
SELECT
What SQL statement is used to retrieve data from all columns in a table or to retrieve data from specified columns?
SELECT
Fill in the blank: In order to get all all data from a customers table, use the _____ _ _____ query.
SELECT * FROM
Scenario:
There is one table of data called “customers.” We need to get ids, first names, last names, and age from the “customers” table.
What query would we write to do this?
SELECT customer_id, first_name, last_name, customer_age
FROM customers
What SQL clause filters results?
WHERE
Scenario:
There is one table of data called “customers.” We want to get first names and last names from the table, but only of those customers who are less than 35 years old.
What query would we write to do this?
SELECT first_name, last_name
FROM customers
WHERE age < 35
Scenario:
There is one table of data called “customers.” We want to get customer IDs from the table, but only for those who have the last name “Smith.”
What query would we write to do this?
SELECT customers_id
FROM customers
WHERE last_name = ‘Smith’
Fill in the blank: _____ _____ is a SQL query used to sort data
ORDER BY
What SQL query returns results in ascending order by default?
ORDER BY
ORDER BY sorts query results in ascending order by default. What do you add to the statement in order to sort in descending order?
DESC
Scenario:
You have a table of data called “customers.” You want to retrieve a list of all customers by first name and last name, ordered alphabetically by last name.
What query would you write to do this?
SELECT first_name, last_name
FROM customers
ORDER BY last_name
Scenario:
You have a table called “customers.” You want to pull a list of customers’ last names, customer IDs, and ages. You only want customers under the age of 40 included in the last, and you want it ordered alphabetically by last name.
What query do you write to do this?
SELECT last_name, customer_id, ages
FROM customers
WHERE age < 40
ORDER BY last_name
Scenario:
You have a table called “customers.” You want to pull all the data in the table, sorted by age in descending order.
What query do you write to do this?
SELECT *
FROM customers
ORDER BY age DESC
What SQL command is used to only pull a small subset of data?
LIMIT
What SQL command would you use to test out a query without pulling a large set of data?
LIMIT
What are the three main data types in SQL?
INTEGER, TEXT, and BOOLEAN
What category of SQL commands allow you to use math functions with your data?
Aggregators
What SQL aggregator is used to find out how many records are in a data set?
COUNT
What SQL aggregator is used to add up the total value of a set of numeric values?
SUM
What SQL aggregator is used to find the mean of a set of numeric values?
AVG
What SQL query word is used to create columns in your table using results from an aggregator command, or just to rename an existing column in the table?
as
Scenario:
You have a table called “customers.” You want to discover the average number of orders per customer (under the “order_count” column).
What querty do you write to do this?
SELECT AVG(order_count)
FROM customers
Scenario:
You have a table called “customers.” You want to generate a list of data from the table, telling you how many customers there are (each customer has a unique ID).
What query do you write to do this?
SELECT COUNT(customer_id)
FROM customers
Scenario:
You have a table called “customers.” The table has a row for each order (and each order has its own order ID), even if the customer has ordered multiple times. You want to create a column that shows the total number of orders that customer (identified by their customer ID) has placed.
What query do you write to do this?
SELECT COUNT(order_id)
as customer_order_total
FROM customers
Scenario:
You have a table called “customers.” In this table is a column featuring a unique string of numbers assigned to every order. It is called “order_identity.” Because customers are also assigned a unique string and this column is called “customer_id,” you want to rename the order column “order_id.”
What querty do you write to do this?
SELECT order_identity
as order_id
FROM customers
