SQL Basics & Querying
This clause returns the users' requested queries
What is SELECT?
This type of constraint ensures that a column cannot accept null values
What is NOT NULL?
This type of key uniquely identifies a record using multiple columns
What is a composite key?
This type of join returns all rows from the left table and matching rows from the right.
What is a left join?
This breaks down a database into smaller, related tables
What is normalization?
Allows you to create conditions that a row must meet in order to be returned by the query
What is the WHERE clause?
This property ensures that a transaction is fully completed or fully rolled back.
What is atomicity?
This constraint ensures that values in a column must already exist in another table.
What is a Foreign Key Constraint?
This join type matches rows based on a condition that is not equality
What is non-equijoin?
This normal form ensures all attributes contain atomic (indivisible) values.
What is 1NF?
This SQL statement modifies existing records but ensures only certain rows are updated
What is UPDATE/SET/WHERE?
Single-element of a relation in database terms it is a row
What is a tuple?
This SQL command adds a foreign key constraint to an existing table
What is ALTER TABLE/ADD CONSTRAINT/FOREIGN KEY?
Combines two select statements and returns only the dataset that is common in both the statements
What is intersect operation?
This normalization step removes partial dependencies but allows transitive dependencies.
What is 2NF?
This operator looks for 0 or more occurrences of any character or any combination of characters
What is a wildcard operator (%)?
Equivalent to a column datatype and any constraints on the values of that data
What is a domain?
This type of relationship is typically optimized by merging tables instead of using a foreign key.
What is One-To-One relationship?
This type of join is used when a table is joined with itself
What is a self-join?
Normalization helps stop these issues, like data not being added, updated, or deleted correctly,
What are data anomalies?
(DAILY DOUBLE)This code outputs what:
SELECT * FROM store
WHERE item_number LIKE '_78%';
What is a single character before 78 and as many characters after the 78?
Simultaneous access of the same data by multiple users, if not existent, it would be possible for users to make changes that compromise data integrity
What is data concurrency?
There are 3 potential behaviors for a foreign key
1. Deletes from the parent table are prevented
2. Deletes from the parent table are cascaded to any matching records
3. Deletes from parent table causes matching records to have the foreign key column set to some value
What is CASCADE RESTRICT, CASCADE DELETE, CASCADE UPDATE?
This join creates all possible combinations between 2 tables (Cartesian Product)
What is a cross join?
This process makes sure database queries are fast and efficient
What is query optimization?