Unit 4 - Advanced Database Commands

Question 1

ALTER TABLE

Answer 1

A clause that changes the structure of a table.

Question 2

Any and All

Answer 2

The ANY and ALL operators compare a value with a set of values returned by a subquery. These operators are particularly useful if you want to compare multiple values from a subquery without specifying each value explicitly.

Question 3

CROSS JOIN

Answer 3

A clause in a query that creates a new table with the same number of rows as the product of the original table sizes.

Question 4

Column Alias

Answer 4

An alternative name assigned to a column for use in a query’s result set.

Question 5

DROP VIEW

Answer 5

A statement that deletes a view.

Question 6

Deduplication

Answer 6

The removal of duplicates.

Question 7

EXPLAIN

Answer 7

A command that provides insight into the query execution plan for a given SQL query.

Question 8

FULL OUTER JOIN

Answer 8

A JOIN that retrieves all records from both tables, even if there is no matching record in the other table; the missing column entries appear as NULL.

Question 9

Foreign Key

Answer 9

A column or set of columns that refers to the primary key of another table.

Question 10

LEFT JOIN

Answer 10

A clause that combines data from two tables based on a specified condition. It retrieves all records from the left (or first) table and matching records from the right (or second) table. In the result set, any unmatched rows from the left table will have NULL values.

Question 11

NATURAL JOINs

Answer 11

SQL JOINs based on columns with identical names in the related tables. They are easier to set up than other JOINs but can result in unintended connections when the tables share multiple common column names.

Question 12

Node

Answer 12

A section of a query plan that describes a single operation within the query.

Question 13

Outer Query

Answer 13

A query that uses the output of a subquery (inner query) as input.

Question 14

Query Plan Node

Answer 14

The results of an EXPLAIN command, describing the cost of each operation in a query.

Question 15

RIGHT JOIN

Answer 15

A clause that combines data from two tables based on a specified condition. It retrieves all records from the right (or second) table and matching records from the left (or first) table. In the result set, only matching rows from the left and right tables are included, and any unmatched rows from the right table will have NULL values.

Question 16

Subquery

Answer 16

Also called an inner query, a query that is nested inside another query, so its results can be used as input for the outer query.

Question 17

Table Alias

Answer 17

An alternative name assigned to a table for use in a query’s result set.

Question 18

Union Operator

Answer 18

An operator that combines the results of two or more SELECT statements and removes duplicate rows.

Question 19

Venn Diagram

Answer 19

Venn diagrams illustrate logical relationships between two or more sets of items by using overlapping circles or shapes. In many cases, they serve as a visual way to summarize similar and different aspects of items.

Question 20

View

Answer 20

In a database, a view is the result set of a stored query, which can be queried like a persistent database collection object.

Question 21

Candidate key

Answer 21

A candidate key is not a specific type of constraint that can be set in a database. Instead, it is a set of attributes that uniquely identifies a table
record. A table could have multiple candidate keys, with one being set as the primary key for a table. However, any of those candidate keys could potentially have been
selected as the primary key. The candidate key would have all of the constraints similar to a primary key, but there may be better options to select as the primary key.

The properties of the candidate key include the following:
The column data should be unique.

The key can consist of multiple columns.

It should not contain any NULL values.