Database Systems Review: SQL, ERDs, Normalization, and Relational Algebra

What is the SQL clause that specifies the table(s) from which data is to be retrieved?

FROM

What clause in an SQL statement adds a condition to limit the rows to be displayed?

WHERE

What is the comparison operator used in an SQL statement to check whether an attribute does or does not have a value?

IS NULL or IS NOT NULL

What is a query that is embedded inside of another query?

subquery

What is the process for evaluating and correcting table structures to minimize data redundancies and anomalies?

normalization

What query clause will combine rows with the same values into summary rows?

GROUP BY clause

What is an attribute used as a primary key in one table and as a reference in another table?

foreign key (FK)

What is the type of relationship created when each team may have many players and each player may be on one team?

1:M (one-to-many)

What is an attribute whose value is calculated from other attributes?

derived attributes

What term refers to the cardinalities in an ERD?

cardinalities - One to many, one to one, many to many, etc..

What is the proper model/design for the business rules where a student may enroll in many classes and each class may enroll many students?

Many-to-Many relationship

What is the SQL command used to create a table's structure with its attributes and constraints?

CREATE TABLE

What SQL command is used to remove a table structure from a database?

DROP TABLE

What is the SQL statement required to insert a row into the CUSTOMER table with specified attributes?

INSERT INTO CUSTOMER (Cust_LName, Cust_FName)
VALUES
('Bright','Brittany');

What SQL statement would create a table named CUSTOMER with specified attributes?

CREATE TABLE CUSTOMER(
Cust_ID INTEGER NOT NULL,
Cust_Fname VARCHAR(50),
Cust_Lname VARCHAR(50),
PRIMARY KEY(Cust_ID));
);

What is the SQL statement for removing a duplicated customer with an ID of 45?

DELETE FROM CUSTOMER
WHERE Cust_ID = 45;

What is the set of commands that allow a database administrator to DEFINE / CREATE the database structure?

Data Definition Language (DDL)

What are common data types for SQL? Name at least 3.

INTEGER, DOUBLE/DECIMAL, CHAR, VARCHAR, DATE

What is the SQL command used for the construction of table structures?

CREATE

What are 3 of the SQL constraints?

NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY, FOREIGN KEY

What is the SQL statement to remove a row from the EMPLOYEE table with Employee ID # 67564?

DELETE FROM EMPLOYEE
WHERE EmpID = 67564;

What SQL statement displays the student first and last name for any student whose last name starts with 'S'?

SELECT stuLName, stuFName
FROM STUDENT
WHERE stuLName LIKE 'S%';

What is the term for a set of commands that allow for the manipulation of data in a database?

Data Manipulation Language (DML)

What SQL command is used to retrieve data from tables that yields the values of all rows or a subset of rows based on a given condition?

SELECT

What are alternative names for a column or table in SQL, following the AS keyword?

aliases

What is the order of operations for calculated/derived fields in a database?

Parenthesis, Exponents, Multiplication & Division (left to right), Addition & Subtraction (left to right)

What SQL clause produces a list of unique values for an attribute?

DISTINCT

What are the different clauses that can be added to data fields to limit their contents?

constraints (e.g. NOT NULL, DEFAULT, UNIQUE)

What are the 3 wildcard characters in SQL and what do they do?

* (asterisks) Returns all columns, % (percent) Any & All characters, _ (underscore) One Single Character

What SQL command is used to retrieve data from multiple tables?

JOIN

What is the general syntax of a SELECT statement including a standard JOIN?

SELECT column1, column2
FROM table1 JOIN table2
ON table1.col = table2.col

What type of query returns the rows with and without matching values in another table?

outer join

What type of query would you use to determine which vendors we have not purchased any products from?

OUTER JOIN

What is the difference between a right and left outer join?

Left outer join returns all rows in the FROM table; Right outer join returns all rows in the JOIN table.

What SQL query would you use to display customer name, order number, order date, and order status for all orders?

SELECT C.CustName, O.OrderNum, O.OrderDate, O.OrderStatus
FROM CUSTOMER C
JOIN ORDER O
ON C.CustID = O.CustID

What SQL query would you use to find customers who have not purchased anything?

SELECT C.Cust_ID, C.Cust_Name
FROM CUSTOMER C
LEFT OUTER JOIN ORDER O
ON C.Cust_ID = O.Cust_ID
WHERE O.Order_ID IS NULL

What characters/symbols are found around any subquery within an SQL statement?

Open/close parenthesis

When should a subquery be used instead of a join?

A subquery should be used when data from only 1 table needs to be displayed or when performance dictates the use of a join.

What is the first query in an SQL statement that includes a subquery called?

outer query

What are the two primary sections of a query that a subquery can be implemented inside?

FROM & Condition (WHERE or HAVING)

What operator is required when a subquery returns more than 1 value in a WHERE condition?

IN operator

What is executed first in a subquery consisting of an outer query and an inner query?

the inner query

What query is used to display products where the product price is greater than the overall average product price?

SELECT Prod_Num, Prod_Price
FROM PRODUCT
WHERE Prod_Price >= (SELECT AVG(Prod_Price) FROM PRODUCT)

What is used to depict data models and queries utilizing mathematical concepts?

relational algebra

What symbol is used to depict the WHERE portion of an SQL's SELECT statement with relational algebra?

the SELECT/SIGMA symbol (σ)

What symbol is used to depict the selection of columns for a SELECT statement with relational algebra?

the PROJECT symbol (π)

What symbol is used to depict the joining (inner) of two tables in a SELECT statement with relational algebra?

the theta symbol (⋈)

What symbols are used to depict a left and right outer join in a SELECT statement with relational algebra?

the right and left theta symbols (⟕ for Left Outer Join, ⟖ for Right Outer Join)

What SQL query corresponds to the relational algebraic equation π(custID, fName, lName) σ(OrderID IS NULL) (CUSTOMER C ⟕ INVOICE I (C.CustID = I.CustID))?

SELECT custID, fName, lName
FROM CUSTOMER C
LEFT OUTER JOIN INVOICE I
ON C.CustID = I.CustID
WHERE I.OrderID IS NULL

What relational algebra corresponds to the query
SELECT fName, lName FROM EMPLOYEE
WHERE EmpID IN (SELECT Mngr_ID FROM EMPLOYEE WHERE fName = 'Brittany' AND lName = 'Bright')?

π(fName, lName) σ(fName = 'Brittany' ∧ lName = 'Bright') (EMPLOYEE E ⨝ EMPLOYEE EE (E.EmpID = EE.Mngr_ID))

//Will not be given something complex like this.