SQL

What is a Candidate key?

A candidate key is a minimal superkey—that is, a superkey without any unnecessary attributes. A candidate key is based on a full functional dependency.

What is the difference between a column constraint and a table constraint?

A column constraint applies to just one column. A table constraint may apply to many columns.

System Catalog

A detailed system data dictionary that describes all objects in a database.

What is a Foreign Key?

A foreign key (FK) is the primary key of one table that has been placed into another table to create a common attribute.

One-to-one Relationship:

A relationship between a single occurrence of an entity type with another single occurrence of the same entity type.

One-to-many Relationship:

A relationship between a single occurrence of an entity type with any number of occurrences (minimum one) of the same entity type.

Many-to-many Relationship:

A relationship between any occurrence of an entity type with any number of occurrences (minimum one) of the same entity type.

Weak (Non-Identifying) Relationships

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.

Strong (Identifying) Relationships

A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity.

Entity: Attribute: Relationship:

A thing within an organization that refers to individual system components that are considered significant to the end user. Describes the characteristics of the components of the database. It is a property of a given entity. An association exists between two database tables when one table has a foreign key that refers the primary key of the other table.

What is a recursive join?

An alias is especially useful when a table must be joined to itself in a recursive query. The recursive join is an operation used in relational databases, also sometimes called a "fixed-point join". It is a compound operation that involves repeating the join operation, typically accumulating more records each time, until a repetition makes no change to the results.

Attribute Types Composite: Derived: Multi-valued: Simple:

An attribute that can be subdivided to yield additional attributes having meanings of their own. An attribute that is not physically present within an entity and cannot be stored in the database but can be calculated via algorithm. An attribute that can have more than one values for a single entity occurrence. An attribute that cannot be divided into meaningful components.

Inner Join

An inner join only returns matched records from the tables that are being joined.

What is the difference between the COUNT aggregate function and the SUM aggregate function?

COUNT The number of rows containing non-null values SUM The sum of all values for a given column

Cardinality

Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. OR denotes how many instances of one object are related to instances of another object. OR cardinality refers to the maximum number of times this relationship may occur.

What are "referential constraint actions?"

Deleting Updating Inserting

What type of integrity is enforced when a primary key is declared?

Entity Integrity

What is Entity Integrity?

Entity integrity is the condition in which each row (entity instance) in the table has its own unique identity. To ensure entity integrity, the primary key has two requirements: (1) all of the values in the primary key must be unique, and (2) no key attribute in the primary key can contain a null.

In a SELECT Query, what is the difference between a WHERE clause and a HAVING clause?

HAVING Restricts the selection of grouped rows based on a condition WHERE Restricts the selection of rows based on a conditional expression

NULL

Is the absence of any data value, and it is never allowed in any part of the primary key.

Explain why the following command would create an error and what changes could be made to fix the error: SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;

Missing GROUP BY SELECT V_CODE, SUM(P_QOH) FROM PRODUCT GROUP BY V_CODE;

Explain the difference between an ORDER BY clause and a GROUP BY clause

ORDER BY clause is especially useful when the listing order is important to you. GROUP BY clause is generally used when you have attribute columns combined with aggregate functions in the SELECT statement.

What is a Cartesian product?

PRODUCT yields all possible pairs of rows from two tables

Data Dictionary

Provides a detailed description of all tables in the database created by the user and designer.

Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.

Sometimes a number sequence is pointless as a number. for instance, you would never perform a mathematical function on a phone number or social security number, but you might want to use them as string types - for instance to append or remove an area code or something.

Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.

The DATE data type uses numeric values based on the Julian calendar to store dates. This makes date arithmetic such as adding and subtracting days or fractions of days possible

Explain why the following two commands to produce different results: SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;

The difference is in the order of operations. The first command executes the Count function to count the number of values in V_CODE (say the count returns "14," for example) including duplicate values, and then the Distinct keyword only allows one count of that value to be displayed (only one row with the value "14" appears as the result). The second command applies the Distinct keyword to the V_CODEs before the count is taken so only unique values are counted.

Outer Join

The matched pairs would be retained, and any unmatched values in the other table would be left null. It is an easy mistake to think that an outer join is the opposite of an inner join.

How to ensure Referential Integrity?

Using a Foreign key, they're used to ensure referential integrity, the condition in which every reference to an entity instance by another entity instance is valid.

Rewrite the following WHERE clause without the use of the IN special operator: WHERE V_STATE IN ('TN', 'FL', 'GA')

WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA'

Referential Integrity

condition in which every reference to an entity instance by another entity instance is valid.

Modality

denotes an instance of a specific entity is optional or mandatory in a relationship. OR modality refers to the minimum number of times an instance of an entity can be associated with an instance in a related entity

Existence-Dependent

if it can exist in the database only when it is associated with another related entity occurrence.

Natural Join

links tables by selecting only the rows with common values in their common attribute(s). A natural join is the result of a three-stage process

Composite Attribute

not to be confused with a composite key, is an attribute that can be further subdivided to yield additional attributes

Right Outer Join

yields all of the rows in the AGENT table, including those that do not have matching values in the CUSTOMER table.

Left Outer Join

yields all of the rows in the CUSTOMER table, including those that do not have a matching value in the AGENT table.