GA - Fundamentals of Databases & SQL

Question 1

What does a Structured Query Language (SQL) do?

Answer 1

It is used to communicate with databases to return data

Question 2

What are some of the capabilities of SQL?

Answer 2

‣Store and work with with very large amounts
of data (millions or billions of rows). 

‣Impose structure on data

‣Ability to access a dataset to rule them
all, which lots of people can access simultaneously

Question 3

When is SQL and Databases commonly used?

Answer 3

‣ When there’s lots of data.
‣ When many people need to access the data.
‣ When the data is being updated regularly.
‣ When data needs to be clean and well structured.

Question 4

What does a database do?

Answer 4

It houses large amounts of info in the form of data tables

Question 5

What is the purpose of a data table?

Answer 5

It provides relational information. Those creating a table use columns and rows to arrange the various data.

Question 6

Why do databases typically live off your computer on a server (basically a
computer-connected to the internet)?

Answer 6

Due to their size and to allow for easier

access.

Question 7

How do you download an SQL Query into a file (typically

a .csv)?

Answer 7

N/A

Question 8

What are the most common forms of SQL?

Answer 8

There are a number of implementations of databases, including: 
‣ PostgreSQL (what we will be using)
‣ MySQL
‣ MS SQL
‣ Oracle

Question 9

What are relational databases?

Answer 9

It is when the overall data is broken up into logical sets of
information, with KEYS or IDs enabling linking between tables.

Question 10

What is transactional data?

Answer 10

It is when large volumes of data are updated frequently. e.g. daily_dog_sales

Question 11

What is reference data?

Answer 11

It is data that rarely changes and is relatively small in volume. e.g. types_of_dog

Question 12

What are the three main clauses used to comm specific request/questions to databases?

Answer 12

Select
From
Where

Question 13

What does the SELECT clause comm?

Answer 13

“I WANT THESE COLUMNS”

‣ Allows you to select certain columns from a table.

Question 14

What does the FROM clause comm?

Answer 14

“FROM THIS DATA TABLE”

‣ Specifies the tables from which the query extracts data.

Question 15

What does the WHERE clause comm?

Answer 15

“ONLY WHEN THESE THINGS ARE TRUE“

‣ Determines which rows are selected from the tables.

Question 16

What does “*” mean in SQL?

Answer 16

It stands for “every column”

Question 17

What does SELECT DISTINCT mean in SQL?

Answer 17

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Question 18

What does ORDER BY do in SQL?

Answer 18

It sorts your results in an ascending (ASC) or descending (DESC) order, depending on which you choose.

After the ORDER BY is a number or column name that indicates which column you are sorting by; you either leave it blank or place a desc to determine if you will sort ascending (blank) or descending (desc).

Question 19

What do we need to take into acct when you want to implement multiple conditions in SQL?

Answer 19

You should consider using the AND, OR, AND CAST clauses

Question 20

What does the AND clause do?

Answer 20

It returns TRUE if both conditions are true

Question 21

What does the OR clause do?

Answer 21

It returns FALSE if neither condition is true (True if either is true)

Question 22

What does the CAST (field AS type) clause do?

Answer 22

It converts the contents of “field” to the specified data type