Skillstorm Intro to Coding - SQL and MySQL

Question 1

What is normalization in Databases?

Answer 1

Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

Question 2

What are the requirements for a relation in a database to be in First Normal Form or 1NF?

Answer 2

• The domain of each attribute (column) contains only indivisible (atomic) values
• The value of each attribute (column) contains only a single value from that domain

In Other Words

• No repeating groups (multi-valued column)

==> Only one value per column

• Table must define a primary key

==> A unique identifier for a row

Question 3

What are the requirements for a relation in a database to be in Second Normal Form or 2NF?

Answer 3

• It is in 1NF.
• It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation.

In Other Words

• See Attached Image

Question 4

What are the requirements for a relation in a database to be in Third Normal Form or 3NF?

Answer 4

• It is in 2NF
• 3NF states that all column references in referenced data that are not dependent on the primary key should be removed

In Other Words

• No transitive dependencies

==> A column determined by a non-key attribute

• Includes derived attributes, which should be calculated

==> Your Birthday determines your age

==> Item price times quantity determines line total

==> Sum of line totals determines the order sub-total

Question 5

Who is the father of the relational database model?

Answer 5

Edgar F. Codd who released his landmark paper in 1970 “A Relational Model of Data for Large Shared Data Banks”

Question 6

What is a good mnemonic for remembering the normal forms?

Answer 6

Question 7

What is Functional Dependence?

Answer 7

• One value determines another

==> Your birthday determines your age

==> Order number determines the date the order was placed

• First name does not determine anything

Question 8

What is a composite key in 2NF?

Answer 8

• It’s when two or more column’s come together to determine the primary key

Question 9

What is a Transitive Dependency?

Answer 9

• A column determined by a non-key attribute

In Other Words

• A transitive dependency in a database is an indirect relationship between values in the same table that causes a functional dependency.
• By its nature, a transitive dependency requires three or more attributes (or database columns) that have a functional dependency between them, meaning that Column A in a table relies on Column B through an intermediate Column C.

Question 10

Transitive Dependency: See attached image…

Answer 10

Description

Question 11

Partial Dependence: See attached images…

Answer 11

Order_Date

Question 12

Assign the right cardinality of each relationship: See attached images…

Answer 12

1) Many-to-Many
2) One-to-Many
3) One-to-One

Question 13

Repeating Groups: See attached images…

Answer 13

Items_Ordered

Question 14

The SELECT statement is used to return a result set of __________ from one or more ___________.

Answer 14

records

tables

Question 15

A WHERE clause specifies that a SQL Data Manipulation Language statement should only affect _____ that meet specified criteria. The WHERE clause is used to extract only those results from a SQL statement, such as: _________, _________, __________, and ___________ statement.

Answer 15

rows

SELECT

INSERT

UPDATE

DELETE

Question 16

Comparison (or relational) operators are mathematical symbols used to compare two values. They are used in conditions that compare one expression with another. The result of a comparison can be _________, ________, or _________.

Answer 16

TRUE

FALSE

UNKNOWN

Question 17

The BETWEEN operator allows you to select values within a specified _________. These values can be ________, ________, or ________.

Answer 17

range

numbers

text

dates

Question 18

The BETWEEN operator is inclusive: _______ and ________ values are included.

Answer 18

begin

end

Question 19

The LIKE operator determines if a character string matches a specified ________. The pattern may include regular characters and ________ characters. Use the LIKE operator in the ________ clause of the SELECT, UPDATE, and DELETE statements to filter ________ based on pattern matching.

Answer 19

pattern

wildcard

WHERE

rows

Question 20

Retrieve Payments: See attached image…

Answer 20

SELECT

FROM

WHERE

BETWEEN, AND

Question 21

Retrieve Country United States: See attached images…

Answer 21

SELECT

FROM

WHERE

Question 22

Comparison Operators

Answer 22

>

>=

=

!=

Question 23

Several Comparisons: See attached images…

Answer 23

SELECT, FROM, WHERE, LIKE, %

SELECT, FROM, WHERE, LIKE, %(Z)%

SELECT, FROM WHERE, LIKE, _

Question 24

Actor’s First and Last Name: See attached image…

Answer 24

SELECT

FROM

Question 25

INSERT is a command that is used for inserting one or more ________ into a database table with specified table ________ values.

Answer 25

rows column

Question 26

UPDATE is a statement that changes the data of one or more ________ in a table. Either all the _________ can be updated, or a subset may be chosen using a condition.

Answer 26

records rows

Question 27

DELETE is a statement that removes one or more ________ from a table. A subset may be defined for deletion using a condition, otherwise, all ________ are removed.

Answer 27

records records

Question 28

Remove Rental: See attached images

Answer 28

DELETE, FROM WHERE

Question 29

Create Rows in Actor and City Tables: See attached image...

Answer 29

INSERT, INTO VALUES INSERT, INTO VALUES INSERT, INTO VALUES

Question 30

Change Values for a Row: See attached image...

Answer 30

UPDATE SET WHERE

Question 31

When there is no relationship defined between the two tables, use the _______ \_\_\_\_\_\_\_\_ to return all the ________ in all the ________ listed in the query. Each row in the first ________ is paired with all the ________ in the second table.

Answer 31

Cartesian Product rows tables table rows

Question 32

A JOIN clause is used to combine rows from two or more tables, based on a related ________ between them.

Answer 32

column

Question 33

The INNER JOIN is a keyword that selects all _________ from both the tables as long as the _________ satisfies. This ________ will create the result set by combining all ________ from both the ________ where the condition satisfies, i.e., value of the common field will be same.

Answer 33

rows condition keyword rows tables

Question 34

The RIGHT/LEFT JOIN combines data from ________ or more \_\_\_\_\_\_\_\_. The RIGHT JOIN starts selecting data from the right ________ and matching with the ________ from the left table. The RIGHT JOIN returns a result set that includes all __________ in the right table, whether or not they have matching __________ from the left \_\_\_\_\_\_\_\_.

Answer 34

two tables table rows rows rows table

Question 35

The LEFT JOIN starts selecting data from the _________ \_\_\_\_\_\_\_\_\_\_ and matching with the _________ from the ________ table. The LEFT JOIN returns a result set that includes all rows in the ________ table, whether or not they have matching rows from the __________ table.

Answer 35

left table rows right left right

Question 36

City Records and Related Country Records: See attached images...

Answer 36

SELECT FROM INNER JOIN ON

Question 37

Find Customer's Who Haven't Rented: See attached images...

Answer 37

SELECT FROM LEFT JOIN ON

Question 38

Find Cartesian Product between Film and Actor: See attached images...

Answer 38

SELECT FROM CROSS JOIN

Question 39

CREATE TABLE is a statement that is used to create tables to store data. You can define integrity constraints such as ________ key, ________ key, and _________ key for the columns while creating the table. The integrity constraints can be defined at the ________ level or _________ level.

Answer 39

primary unique foreign column table

Question 40

ALTER TABLE is a statement that is used to \_\_\_\_\_\_\_\_, \_\_\_\_\_\_\_\_, or ________ columns in an existing table. You can also use the ALTER TABLE statement to add and drop various ________ on an existing table.

Answer 40

add delete modify constraints

Question 41

DROP TABLE is a statement that is used to remove a table definition and all the \_\_\_\_\_\_\_\_, \_\_\_\_\_\_\_\_, \_\_\_\_\_\_\_\_\_, \_\_\_\_\_\_\_\_\_\_, and _________ specifications for that table.

Answer 41

data indexes triggers constraints permission

Question 42

Constraints are used to specify rules for the data in a table. You can use them to limit the type of _________ that goes into a table. This allows you to ensure the ________ and __________ of the data in the table. Any violation between the _________ and the ________ action will ________ the action.

Answer 42

data accuracy reliability constraint data abort

Question 43

Update Hero Table: See attached image...

Answer 43

ALTER TABLE ADD COLUMN

Question 44

Hero\_Name is always given value: See attached image...

Answer 44

NOT NULL UNIQUE FOREIGN KEY REFERENCES

Question 45

Remove Hero Table: See attached images...

Answer 45

DROP TABLE

Question 46

Create New Hero Table: See attached images...

Answer 46

CREATE TABLE

Question 47

To achieve 1st normal form, your tables cannot have

Answer 47

Repeating Groups

Question 48

Given a table called Customer, which query will find only customers who have a last name beginning with 'Z'?

Answer 48

SELECT \* FROM CUSTOMER WHERE LAST\_NAME LIKE 'Z%'

Question 49

The _____ constraint allows a record to reference a record in another table.

Answer 49

Foreign Key

Question 50

To achieve 2nd normal form, your tables cannot have _______ or \_\_\_\_\_\_\_\_.

Answer 50

Repeating Groups Partial Dependencies

Question 51

To achieve 3rd normal form, your tables cannot have \_\_\_\_\_\_\_\_, \_\_\_\_\_\_\_\_\_, and \_\_\_\_\_\_\_\_\_\_.

Answer 51

Partial Dependencies Repeating Groups Transitive Dependencies

Question 52

Given a table Users with fields ID (Number) and Username (varchar), which of the following will create a new record?

Answer 52

INSERT INTO USERS VALUES (1, 'DAN'); INSERT INTO USERS(USERNAME) VALUES ('DAN');

Question 53

Given a Customer table and Order table that references Customer, which of the following will find only customers who have placed an order?

Answer 53

SELECT \* FROM CUSTOMER C INNER JOIN ORDERS O ON O.CUST\_ID = C.CUST\_ID;

Question 54

The ____ statement is designed specifically to remove rows from a table.

Answer 54

DELETE

Question 55

The constraint that defines the unique identifier for a row is \_\_\_\_\_\_

Answer 55

Primary Key

Question 56

Given a Customer table and Order table that references Customer, which of the following will find only customers who have never placed an order?

Answer 56

select \* from orders o right join customer c on o.cust\_id = c.cust\_id where o.order\_id is null; OR select \* from customer c left join orders o on o.cust\_id = c.cust\_id where o.order\_id is null;