CH15: Databases and SQL

Question 1

____ the database in the MySQL file tree to set the model as the default option for SQL commands

Answer 1

double click

Question 2

PRIMARY KEY

Answer 2

unique identifier in the table

Question 3

How does AUTO_INCREMENT affect a new entry to a table?

Answer 3

assigns every new entry a different integer value

Question 4

What does the following SQL cmd accomplish?
INSERT INTO seeds (crop, encourages, use_by)
VALUES (“Agastache”, “bees & hummingbirds”, 2020);

Answer 4

Adds new entry to seeds table in listed columns

Question 5

___ is stored in a column when a value for a field is not supplied

Answer 5

null

Question 6

If you leave out the ____ clause in a SQL UPDATE or DELETE FROM command, ALL records in a table are updated/deleted

Answer 6

WHERE

Question 7

SELECT * FROM example_table

Answer 7

Select all entries in a table

Question 8

Use the ___ to import csv files into a table

Answer 8

Table Data Import Wizard

Question 9

Define persistent data source

Answer 9

Data that is stored after process that created it has ended. It is written to non-volatile storage

Question 10

What does ACID stand for in database transactions?

Answer 10

Atomicity
Isolation
Consistency
Durability

Question 11

Define atomicity

Answer 11

transaction treated as single unit

Question 12

Define consistency

Answer 12

transaction can only bring the database from one valid state to another

Question 13

Define Isolation

Answer 13

Transactions often execute concurrently (multiple reading and writing to table). Isolation ensures this execution leave DB in same state as sequential execution

Question 14

Define Durability

Answer 14

Guarantees once transaction committed, stays that way, even in the event of system failure

Question 15

Define Relational Database

Answer 15

collection of data items with pre-defined relationships between them

Question 16

What is a RDMS, name one example

Answer 16

A tool used to interact with data stored in a table. SQL is a common example

Question 17

True/False: STRING is a SQL data type

Answer 17

False, VARCHAR handles strings in SQL

Question 18

What is the data type for handling integers in SQL?

Answer 18

INT (also DOUBLE, BIGINT, MEDIUMINT)

Question 19

Which SQL datatype are BOOL and BOOLEAN synonymous with?

Answer 19

TINYINT (0 = false, 1 = true)

Question 20

What is CRUD?

Answer 20

Create
Read
Update
Delete

These are the 4 major operations performed when working with data.

Question 21

How would you scaffold an update to a table?

Answer 21

UPDATE
SET
WHERE

Question 22

How would you scaffold adding a column to a table?

Answer 22

ALTER TABLE

ADD

Question 23

How would you scaffold creating a table from another table?

Answer 23

CREATE TABLE
AS SELECT
FROM
WHERE

Question 24

How would you scaffold deleting an entry from a table?

Answer 24

DELETE FROM

WHERE

Question 25

What purpose does a join serve

Answer 25

Use when combining querying two tables to create one result set

Question 26

List the 4 types of joins in SQL

Answer 26

Inner Join Left Outer Join Right Outer Join Full Outer Join

Question 27

What is the general syntax of a join?

Answer 27

FROM table_a | TYPEOFJOIN JOIN table_b ON table_a.column_name_1 = table_b.column_name_1;

Question 28

Joining two tables with an _______ produces a result set that only includes the values that are present in both tables

Answer 28

inner join

Question 29

Joining two tables with a _________ gives us a result set which includes all values in the left table and any matching records from the right table.

Answer 29

left outer join

Question 30

Joining two tables with a _____ gives us a result set that includes all records from both tables. The syntax is not supported in MySQL

Answer 30

full outer join