SQL Flashcards

From Facebook Blueprint SQL lessons (not included in the MarSci Study Guide)

1
Q

Name this type of JOIN:

A

LEFT JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Name this type of JOIN:

A

FULL OUTER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Name this type of JOIN:

A

INNER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Name this type of JOIN:

A

RIGHT JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the name of the programming language designed to communicate with databases?

A

SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

SQL stores data in tables, also known as what?

A

relational database structures

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What SQL query is considered the most basic form?

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What SQL statement is used to retrieve data from all columns in a table or to retrieve data from specified columns?

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Fill in the blank: In order to get all all data from a customers table, use the _____ _ _____ query.

A

SELECT * FROM

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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?

A

SELECT customer_id, first_name, last_name, customer_age

FROM customers

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What SQL clause filters results?

A

WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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?

A

SELECT first_name, last_name

FROM customers

WHERE age < 35

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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?

A

SELECT customers_id

FROM customers

WHERE last_name = ‘Smith’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Fill in the blank: _____ _____ is a SQL query used to sort data

A

ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What SQL query returns results in ascending order by default?

A

ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

ORDER BY sorts query results in ascending order by default. What do you add to the statement in order to sort in descending order?

A

DESC

17
Q

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?

A

SELECT first_name, last_name

FROM customers

ORDER BY last_name

18
Q

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?

A

SELECT last_name, customer_id, ages

FROM customers

WHERE age < 40

ORDER BY last_name

19
Q

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?

A

SELECT *

FROM customers

ORDER BY age DESC

20
Q

What SQL command is used to only pull a small subset of data?

A

LIMIT

21
Q

What SQL command would you use to test out a query without pulling a large set of data?

A

LIMIT

22
Q

What are the three main data types in SQL?

A

INTEGER, TEXT, and BOOLEAN

23
Q

What category of SQL commands allow you to use math functions with your data?

A

Aggregators

24
Q

What SQL aggregator is used to find out how many records are in a data set?

A

COUNT

25
Q

What SQL aggregator is used to add up the total value of a set of numeric values?

A

SUM

26
Q

What SQL aggregator is used to find the mean of a set of numeric values?

A

AVG

27
Q

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?

A

as

28
Q

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?

A

SELECT AVG(order_count)

FROM customers

29
Q

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?

A

SELECT COUNT(customer_id)

FROM customers

30
Q

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?

A

SELECT COUNT(order_id)

as customer_order_total

FROM customers

31
Q

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?

A

SELECT order_identity

as order_id

FROM customers