SQL

Question 1

SQL

Answer 1

SQL, a programming language used to manipulate and query data in relational databases

the language used in talking to relational databases

Question 2

Relational database

Answer 2

A type of database in which the relationships between the data points matter, and that requires a programming language to pull desired data

Question 3

Field

Answer 3

A column in a database table

Question 4

Record

Answer 4

A row in a database table

Question 5

CRUD Functionality

Answer 5

Create
Read
Update
Delete

Question 6

SELECT

Answer 6

Requests a column of tabular data

Question 7

FROM

Answer 7

Specifies the table to access. Specifies what fields you want information from

Question 8

WHERE

Answer 8

Sets conditions for inclusion. Specifies what tables those fields are coming from

Question 9

GROUPBY

Answer 9

Aggregates the resulting rows into groups. Specifies how to aggregate the results

Question 10

HAVING

Answer 10

Specifies any criteria that the aggregate results should meet

Question 11

ORDER BY

Answer 11

Determines how the data is ordered. Specifies how to sort the results. Sorts all selected fields based on the value of one or more fields. By defaults, sorts in ascending order

Question 12

ORDER BY… DESC

Answer 12

Sorts data in descending order

Question 13

LIMIT

Answer 13

Determines how the data is limited in count. Specify how many records/rows to return in results

Question 14

KEY

Answer 14

The attribute that uniquely identifies each item in the list

Question 15

SCHEMA

Answer 15

A representation of tables that only shows the columns, The structure of a database or table, indicating how the data is organized and connected

Question 16

ONE-TO-MANY

Answer 16

When an item in one table can be found in multiple references in another table

Question 17

FOREIGN KEY

Answer 17

Points to a primary key from another table. It is not a unique identifier in it’s own table

Question 18

PRIMARY KEY

Answer 18

Is usually the unique identifier in its table

Question 19

ALIASING

Answer 19

Temporarily renaming a field with a name that exists only for the duration of the query

Question 20

IDENTIFIER

Answer 20

Identifies a database object, often a table or the name of a field

Question 21

OPERATOR

Answer 21

Take some action, such as adding fields or renaming or aliasing a field

Question 22

EXPRESSION

Answer 22

Find specific sets of data

Question 23

PARENT TABLE

Answer 23

The table that stores the primary key

Question 24

CHILD TABLE

Answer 24

A table that uses a foreign key to reference the parent table

Question 25

ONE TO ONE RELATIONSHIP

Answer 25

A relationship where a single record in one table is related to a single record in another table, and neither column has duplicate records

Question 26

ONE TO MANY RELATIONSHIP

Answer 26

A relationship where a single record in one table is related to multiple records in another table

Question 27

MANY TO MANY RELATIONSHIP

Answer 27

A relationship where multiple records in one table are related to multiple records in another table

Question 28

JOINS

Answer 28

Operations that use data from two different tables

Question 29

INNER JOIN

Answer 29

requires that the tables being joined share a key and produces the set of all matching records from the tables. Returns records that have matching values in both tables

Question 30

LEFT OUTER JOIN

Answer 30

All records from the first table are returned, regardless of whether or not a match is found in the second table

Question 31

RIGHT OUTER JOIN

Answer 31

All records from the second table are returned, regardless of whether or not a match is found in the first table

Question 32

FULL OUTER JOIN

Answer 32

returns all records from both Table A and Table B, regardless of whether or not there's a match in the other table.

Question 33

JOINS NOTES

Answer 33

Take note of the combiner table. If all records from the first table is more important to keep, use it as the combiner, and vice versa LEFT JOIN for table A over table B, is the same as RIGHT JOIN for table B over table A LEFT JOIN for table A over table B is the opposite of the LEFT JOIN for table B over table A, and similar rule applies to RIGHT JOINS

Question 34

CTE

Answer 34

Common Table Expression, a temporary table or other result set that you can define and reference within a single query

Question 35

SUBQUERY

Answer 35

A query nested inside of another query, often used for some calculation or conditional logic that provides data to be used in the main part of the query

Question 36

date_part('year', date_field)

Answer 36

used to extract the part of a date that is equal to the year

Question 37

R-SQUARED

Answer 37

measures how well your model fits your data