CIT 111
If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed?DATEDIFF(expiration_date, NOW()) AS exp_days
-45
What would result from: SELECT FLOOR(DATEDIFF('2020-10-10', '2018-09-10') / 365);
2
Typically, most database designers consider a database structure normalized if it's in the ________________________ normal form.
3rd
In most cases, the join condition in the ON clause of an inner join uses the ____________ operator to compare two keys.
=
If I want to delete a ward row in the ward table, but the related table members has members from that ward; (In other words, the primary key of that ward is used as a foreign key of the members table) What has to happen first before I can delete that ward?
All the members of that ward must be deleted first
What is the error with the following statement? SELECT category_name, COUNT(p.category_id) FROM category c JOIN product p ON c.category_id = p.category_id JOIN stock s ON p.product_id = s.product_id WHERE COUNT(p.category_id) = 2 GROUP BY category_name;
An aggregate is being used in a WHERE clause instead of HAVING
What does the following code create if run: CREATE TABLE employee ( emp_no INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(25) NOT NULL, first_name VARCHAR(30) NOT NULL, PRIMARY KEY(emp_no));
An employee table with 3 columns that will require a value
Each year, you would like to display all sales orders starting from January 1 to December 31. Assuming you have an order date column that holds this information, what would make the most sense to use in your WHERE clause?
BETWEEN
stateAbbrev _________ A two letter abbreviation for a state's name
CHAR(2)
Which function will give the result of: $25,900.00
CONCAT('$', FORMAT(25900, 2))
Write an aggregate expression for the number of entries in the vendor_name column:
COUNT(vendor_name)
birthday __________ A person's birthday
DATE
startMoment __________ The date and time that a student began taking a test
DATETIME
You use the ______________ function to add a specified interval to a date.
DATE_ADD
price__________ A product's price in dollars and cents.
DECIMAL(10,2)
If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____________________ keyword in the VALUES clause of the INSERT statement.
DEFAULT
By default, all duplicate values are included in the aggregate calculation (such as summing together two duplicate prices of $5 each), unless you specify the ___________________________ keyword.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
DISTINCT
Data types are important in helping us establish Data Consistency .
Data Consistency
If the data for a product price is entered as text instead of a number, what is being violated?
Data consistency
If you had unreliable data that let a user register a student for a class that isn't offered that semester. This would be violating...
Data integrity
When a student's last name exists in multiple locations in the system, this is an example of ...
Data redundancy
class_rank __________ The class rank of a student. Data should be restricted to only allow these values: Freshman, Sophomore, Junior, or Senior.
ENUM
With the code: UPDATE movie SET title = "Finding Nemo"; What's will happen to each row of the table?
Every movie title will be changed to 'Finding Nemo'
The ________________ clause of the SELECT statement specifies the table that contains the data.
FROM
_______ joins will only return rows that have matching values in both tables.
INNER
You would like to average a price column for each category within each supplier that provides products to your company. You decide to use AVG(price). But what should you do with the supplier and category columns?
Include them in the column SELECT list and the GROUP BY.
What is true of an ISBN number for books? (Check all that apply)
It would make a good primary key for a table containing books It is a natural key
If you want to join all of the rows in the first table listed with just the matched rows in a second table, you use a/an _______________ join.
LEFT OUTER
The ___________ function returns the string with any leading (or beginning) spaces removed.
LTRIM
Write an aggregate expression to find the oldest (earliest) date in the invoice_date column:
MIN(invoice_date)
Which function returns the current local time and date from the user's system?
NOW()
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.
NULL
Write the code that uses the ROUND function to return the payment_total column with 1 decimal digit.
ROUND(payment_total, 1)
What type of database model organizes data into tables with rows that can be linked to other tables' rows by sharing a common attribute value.
Relational
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
Which of the following would make a good natural primary key? [Check all that apply]
Social security number for a person ISBN for a book
SOW stands for...
Statement of Work
isRegistered __________ 1 for TRUE if a person is registered, 0 for FALSE if otherwise A person is either registered or not. In other words, we need to store only two values: 1 or 0
TINYINT
Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?INSERT INTO invoices(vendor_id, invoice_number, invoice_total, payment_total, credit_total,terms_id, invoice_date, invoice_due_date)VALUES(97, '456789', 8344.50, 0, 0, 1, '2012-08-31')
The number of items in the column list doesn't match the number of values in the VALUES list.
Which of the statements below best describes the result set returned by this SELECT statement? SELECT state, COUNT(*) FROM store GROUP BY state HAVING COUNT(*) > 1
The number of stores in each state that has more than one store
Which of the statements below best describes the result set returned by this SELECT statement? SELECT brand_name, SUM(list_price) FROM brand b JOIN product p ON b.brand_id = p.brand_id WHERE list_price > 500 GROUP BY brand_name;
The total of all product prices over 500 for each brand
Which is true of the column staff_id?
The user must enter a unique value for this column for each row of the table
Joins are useful for the following reasons: (Choose the one that does NOT apply)
To find the highest value of a column within one table.
Identifying data sources is a key step that should occur before organizing data. (T/F)
True
What type of database model can store virtually any structure of data and doesn't need a predefined data model?
Unstructured
firstName __________ A person's first name
VARCHAR (20)
If you want to filter the rows that are returned by a SELECT statement, you must include a/an ___________________ clause.
WHERE
When you code a DELETE or UPDATE statement for one or more rows, the _________________ clause specifies which row or rows are to be deleted.
WHERE
Which of the following WHERE clauses will return vendor names from A to C?
WHERE vendor_name < 'D'
You attempt an INSERT statement and get an error saying that the foreign key relationship is violated. This may mean...
You are trying to insert data in a child table that doesn't have a matching parent record.
If we look at the referential integrity of these two tables, what would happen if a new staff was hired and we added a row for that staff to the staff table and gave the store_id the value of 4 as a foreign key value for that new staff member?
You would not be able to add that store_id value of 4 because there is no store with a primary key of 4 in the related table
Looking at referential integrity again, what would happen if you tried to delete store number 1 from the store table?
You wouldn't be able to because there are employees that belong to that store. You'd have to delete all those employees at store number 1 first before you could delete store number 1
One column of a database table should contain...
a set of values belonging to an attribute of the entity
A table ___________________ can be used when you want to assign a temporary name to a table.
alias
Unless you assign a/an _____________________________, the column name in your result set is the same as the column name in the table you are querying.
alias
Conditions searched for in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
When two column values combined make up the primary key, this is called a ________ key.
composite
According to our reading, rather than just making decisions based on intuition or observations alone, organizations should use ________ to make ________-driven decisions. (same word is used in both blanks)
data
If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(CONCAT(LEFT(first_name,1), LEFT(last_name,7))) AS solution
ewilliam
If you had a class list but only needed to see those students from the list majoring in Data Science you could hide all unwanted information to display only the students you needed. This is called...
filtering
Consider this relational database entity: Title | Yr Released | Genre Toy Story | 1995 | Family, Animated Which normal form does the entity violate?
first
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
The database may store many posts from a single user. This means that within the post table, the user_id attribute is what? (Check all that apply.)
foreign key
What would result from the following statement: SELECT right('Williams', 4);
iams
SELECT vendor_name, invoice_dateFROM vendors v JOIN invoices iON v.vendor_id = i.vendor_id This type of join is called a/an __________________ join.
inner
What is the SQL keyword that adds rows to a table?
insert
When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less?
invoice_total IN (0, 1000)
The red diamond next to store_id means...
it is a foreign key in the staff table
What would result from the following statement: SELECT SUBSTRING('comments', 4, 3);
men
Each table in a database should represent...
one real world entity
In relational database design, a many-to-many relationship must be resolved into 2 ____________ relationships.
one-to-many
Each row of a database table should contain...
only one entity (or instance of an entity)
Which attribute value can be left blank as the user is entering data for each staff member?
phone
Consider the following code: SELECT prod_name, prod_price + 2.00 AS mark_up FROM products WHERE prod_price > 1.99 ORDER BY mark_up DESC What might the result set look like?
prod_name | mark_up doll 7.99
Consider the following code: SELECT prod_name, prod_price + 2.00 FROM products WHERE prod_price + 2.00 > 3.99 ORDER BY prod_price What might the result set look like?
prod_name | product_price + 2.00 bouncy ball 4.99
With database design, the goal is to organize the data in such a way that ____________ is minimized.
redundancy
To arrange a list of numbers in a way that the smallest numbers are first and the largest numbers last. For example: 3 6 10 22 24
sort
Why can the values in the store_id column repeat in the staff table but not the store table? [Check all that apply]
store_id is the primary key of the store table and primary keys cannot repeat, they must be unique store_id is the foreign key of the staff table and multiple employees can belong to one store foreign key values can repeat in one-to-many relationships
The post_id attribute in the post table is an auto-incrementing number that the database guarantees is unique for each post. This means that within the post table, the post_id is what? (Check all that apply.)
surrogate key primary key
SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id The 'v' in this example is known as a/an __________.
table alias
SELECT vendor_name, invoice_dateFROM vendors v JOIN invoices iON v.vendor_id = i.vendor_id The 'v' in this example of code is known as a/an _______________.
table alias
Typically, most database designers consider a database structure normalized if it's in the ________________________ normal form.
third
SELECT magName, magPrice, lastName, firstName FROM magazine m LEFT JOIN subscription s ON m.magKey = s.magKey LEFT JOIN subscriber s ON s.scribKey = s.scribKey ORDER BY lastName; What is the problem with the code here?
two alias with same name
n a join, column names need to be qualified only
when the same column name exists. in both tables