Skillstorm Intro to Coding - SQL and MySQL Flashcards
What is normalization in Databases?
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.
What are the requirements for a relation in a database to be in First Normal Form or 1NF?
- 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
What are the requirements for a relation in a database to be in Second Normal Form or 2NF?
- 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
What are the requirements for a relation in a database to be in Third Normal Form or 3NF?
- 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
Who is the father of the relational database model?
Edgar F. Codd who released his landmark paper in 1970 “A Relational Model of Data for Large Shared Data Banks”
What is a good mnemonic for remembering the normal forms?
What is Functional Dependence?
- One value determines another
==> Your birthday determines your age
==> Order number determines the date the order was placed
- First name does not determine anything
What is a composite key in 2NF?
- It’s when two or more column’s come together to determine the primary key
What is a Transitive Dependency?
- 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.
Transitive Dependency: See attached image…
Description
Partial Dependence: See attached images…
Order_Date
Assign the right cardinality of each relationship: See attached images…
1) Many-to-Many
2) One-to-Many
3) One-to-One
Repeating Groups: See attached images…
Items_Ordered
The SELECT statement is used to return a result set of __________ from one or more ___________.
records
tables
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.
rows
SELECT
INSERT
UPDATE
DELETE
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 _________.
TRUE
FALSE
UNKNOWN
The BETWEEN operator allows you to select values within a specified _________. These values can be ________, ________, or ________.
range
numbers
text
dates
The BETWEEN operator is inclusive: _______ and ________ values are included.
begin
end
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.
pattern
wildcard
WHERE
rows
Retrieve Payments: See attached image…
SELECT
FROM
WHERE
BETWEEN, AND
Retrieve Country United States: See attached images…
SELECT
FROM
WHERE
Comparison Operators
>
>=
=
!=
Several Comparisons: See attached images…
SELECT, FROM, WHERE, LIKE, %
SELECT, FROM, WHERE, LIKE, %(Z)%
SELECT, FROM WHERE, LIKE, _
Actor’s First and Last Name: See attached image…
SELECT
FROM
INSERT is a command that is used for inserting one or more ________ into a database table with specified table ________ values.
rows
column
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.
records
rows
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.
records
records
Remove Rental: See attached images
DELETE, FROM
WHERE
Create Rows in Actor and City Tables: See attached image…
INSERT, INTO
VALUES
INSERT, INTO
VALUES
INSERT, INTO
VALUES
Change Values for a Row: See attached image…
UPDATE
SET
WHERE
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.
Cartesian Product
rows
tables
table
rows
A JOIN clause is used to combine rows from two or more tables, based on a related ________ between them.
column
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.
rows
condition
keyword
rows
tables
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 ________.
two
tables
table
rows
rows
rows
table
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.
left table
rows
right
left
right
City Records and Related Country Records: See attached images…
SELECT
FROM
INNER JOIN
ON
Find Customer’s Who Haven’t Rented: See attached images…
SELECT
FROM
LEFT JOIN
ON
Find Cartesian Product between Film and Actor: See attached images…
SELECT
FROM
CROSS JOIN
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.
primary
unique
foreign
column
table
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.
add
delete
modify
constraints
DROP TABLE is a statement that is used to remove a table definition and all the ________, ________, _________, __________, and _________ specifications for that table.
data
indexes
triggers
constraints
permission
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.
data
accuracy
reliability
constraint
data
abort
Update Hero Table: See attached image…
ALTER TABLE
ADD COLUMN
Hero_Name is always given value: See attached image…
NOT NULL UNIQUE
FOREIGN KEY
REFERENCES
Remove Hero Table: See attached images…
DROP TABLE
Create New Hero Table: See attached images…
CREATE TABLE
To achieve 1st normal form, your tables cannot have
Repeating Groups
Given a table called Customer, which query will find only customers who have a last name beginning with ‘Z’?
SELECT * FROM CUSTOMER WHERE LAST_NAME LIKE ‘Z%’
The _____ constraint allows a record to reference a record in another table.
Foreign Key
To achieve 2nd normal form, your tables cannot have _______ or ________.
Repeating Groups
Partial Dependencies
To achieve 3rd normal form, your tables cannot have ________, _________, and __________.
Partial Dependencies
Repeating Groups
Transitive Dependencies
Given a table Users with fields ID (Number) and Username (varchar), which of the following will create a new record?
INSERT INTO USERS VALUES (1, ‘DAN’);
INSERT INTO USERS(USERNAME) VALUES (‘DAN’);
Given a Customer table and Order table that references Customer, which of the following will find only customers who have placed an order?
SELECT * FROM CUSTOMER C INNER JOIN ORDERS O ON O.CUST_ID = C.CUST_ID;
The ____ statement is designed specifically to remove rows from a table.
DELETE
The constraint that defines the unique identifier for a row is ______
Primary Key
Given a Customer table and Order table that references Customer, which of the following will find only customers who have never placed an order?
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;
