Unit 4 Module 4 - SQL and Databses

Question 1

What do you call an organized collection of information or data?

Answer 1

A Database

Question 2

What do you call a structed database containing tables that are related to eachother?

Answer 2

Relational Database

Question 3

What do you call a column where every row has a unique entry?

Answer 3

Primary Key

Question 4

What do you call a table that is a primary key in another table?

Answer 4

Foreign Key

Question 5

What is a programming language used to create, interect with, and request information from a database?

Answer 5

SQL (Structured Query Language)

Question 6

What do you call a request for data from a database table or a combination of tables?

Answer 6

Query

Question 7

What do you call a record of events that occur within an organizations system?

Answer 7

Log

Question 8

What is the command to access SQL from Linux?

Answer 8

sqlite3

Question 9

What 3 ways do Linux and SQL filter data differently?

Answer 9

Structure, Joining Tables, Best uses

Structure - SQL offers more structure and tidyness rather than Linux

Joining Tables - SQL allows analysts to join multiple tables together when returning data, Linux does not.

Best uses - Sometimes looking for certain files that aren’t text SQL can’t format it.

Question 10

In SQL, what command indicates which columns to return?

Answer 10

SELECT

Question 11

In SQL, what command indicates which table to query?

Answer 11

FROM

Question 12

In SQL, what command sorts a specified keyword in a column?

Answer 12

ORDER BY

Ex) ORDER BY city;

Question 13

In SQL, what command sorts a specified keyword in a column by descending order?

Answer 13

DESC

Ex) ORDER BY city DESC;

Question 14

What will this SQL command do to the columns?

SELECT customerid, city, country
FROM customers
ORDER BY country, city;

Answer 14

SQL then sorts the output by country, and for rows with the same country, it sorts them based on city.

Question 15

What do you call selecting data that matches a certain condition?

Answer 15

Filtering

Question 16

What do you call a symbol or keyword that represents an operation?

Answer 16

Operator

Question 17

What command in SQL indicates there condition for a filter?

Answer 17

WHERE

SELECT firstname, lastname, title, email
FROM employees
WHERE title = ‘IT Staff’;

Ex) Want to filter by country?
WHERE country = ‘USA’

Question 18

What command in SQL uses WHERE to search for a pattern in a column?

Answer 18

LIKE

Question 19

When filtering in SQL where should you put the ; ?

Answer 19

At the end of a query

Question 20

What do you call a special character that can be substituted with any other character? Also, what are they?

Answer 20

Wildcard

% and _

Question 21

In order to use the wildcard command ( %, _ ) in SQL , what other command do you need?

Answer 21

LIKE

Question 22

What do you call data consisting of numbers?

Answer 22

Numeric Data

Question 23

What do you call data representing a date and/or time?

Answer 23

Date and time data

Question 24

What operator filters for numbers or dates within a range?

Answer 24

BETWEEN

SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate BETWEEN ‘2002-01-01’ AND ‘2003-01-01’;

Question 25

What are the **6** different operator symbols? What do they do?

Answer 25

**<** - less than **>** - Greater than **=** - Equal to <= - Less than or equal to **>=** - Greater than or equal to **<>** - Not equal to

Question 26

What command specifies that both condition must be met simultaneously?

Answer 26

**AND** SELECT firstname, lastname, email, country, supportrepid FROM customers WHERE supportrepid = 5 AND country = 'USA';

Question 27

What command specifies that either condition can be met?

Answer 27

**OR** SELECT firstname, lastname, email, country FROM customers WHERE country = 'Canada' OR country = 'USA';

Question 28

What command negates a condition?

Answer 28

**NOT** SELECT firstname, lastname, email, country FROM customers WHERE NOT country = 'USA';

Question 29

What command returns rows matching on a specified column that **exists in more than one table** ?

Answer 29

**INNER JOIN**

Question 30

What command returns all of the records of the first table, but only returns rows of the second table that match on a specified column?

Answer 30

**LEFT JOIN**

Question 31

What command returns all of the records of the second table, but only returns rows from the first table that match on a specified column?

Answer 31

**RIGHT JOIN**

Question 32

What command returns all records from both tables?

Answer 32

**FULL OUTER JOIN**

Question 33

What do you call the functions that perform a calculation over multiple data points and return the result of the calculation? What are 3 of them?

Answer 33

**Aggregate functions** 1) COUNT - returns a single number that represents the number of rows returned from your query. 2) AVG - Returns a single number that represents the average of the numerical data in a column. 3) SUM - returns a single number that represents the sum of the numerical data in a column.