251 Final Important Flashcards
(Open Quiz 2 Diagram image)
It is mandatory for employees and mentors to exist in the table.
False
(Open Quiz 2 Diagram image)
A mentor can have only one employee mentee.
False
You have a parent Table A that has a 1-1 relationship with Table B. You want to ensure that a record in Table A is not deleted and remains active when you have a record in Table B. You establish a deletion rule of:
A. Set Default
B. Nullify
C. Cascade
D. Restrict
E. Deny
D
Which discipline below addresses unstructured data as a key part of its value offering:
a. NoSQL
b. Big Data
c. Cloud
d. In Memory Computing
e. RDBMS
B
The debate on __________has reintroduced some of the old discussions of non-structured DB focused on lower-level data management vs the benefits of structured relational DB.
a. NoSQL
b. Big Data
c. Cloud
d. In Memory Computing
e. RDBMS
A
An ideal table should have all of the following with the exception of:
a. Primary key
b. Foreign key
c. No calculated field
d. a and c
e. all are needed
B
Alternative to Relational DB where speed is the leading priority, but reliability can be a concern
Nosql
Includes focus on complex data sets that may include structured and unstructured data
big data
Requires the evaluation of data redundancy and geo-graphic redundancy
high availability
Write a SQL that produces a list of employees making less Than $19.50 per hour that work at store number 222. This list will be provided to the branch manager and she only needs the first and last names of the employees and their hourly rate. The manager wants it in alpha order by last name. Your field specifications state that the store number is defined as a character and the hourly rate as a decimal.
Fields: employee_firstname, employee_lastname, employee_hourlywage, store_number
SELECT employee_firstname, employee_lastname, employee_hourlywage, store_number
FROM EMPLOYEE
WHERE employee_hourlywage < 19.50 AND store_number = ‘222’
ORDER BY employee_lastname ASC;
Fields: store_number, store_address, store_city, store_state, store_squareft (ex: 1500 V)
Using the same tables above, the construction manager needs to visit each store that is over 1000 square ft (stored as numeric) to review existing city violations. However, the manager only wants to visit those stores that actually have a violation open. The store violations field stores a numeric value equal to the number of violations that are open. Although the manager doesn’t want to see the number of violations, he wants a special indicator for you to add next to the store square footage number of a “V” to remind him that there are violations open. You have been asked to create a report that looks as follows:
SELECT store_number, store_address, store_city, store_state, store_squareft || ‘ V’ AS store_squareft
FROM STORE
WHERE store_squareft > 1000 AND store_violations > 0;
Write an SQL that will return store number (no other info needed) that are in zip codes starting with 606 or 601, but only if they have zero (decimal) violations.
SELECT store_number
FROM STORE
WHERE store_violations = 0 AND (store_zip LIKE ‘606%’ OR store_zip LIKE ‘601%’);
Deletion Rule:
The RDBMS will not delete the record in the parent table, but will instead keep the record and designate it as “inactive.”
Deny (D)
Deletion Rule:
RDBMS does not delete the parent table record if it had related records in the child record
Restrict (R)
Deletion Rule:
RDBMS will delete the parent table record and all related records in the child table.
Cascade (C)
Deletion Rule:
The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to null.
(If you are going to use this deletion rule, you must modify the foreign key’s field specifications and set the Null Support logical element to “Nulls Allowed.”)
Nullify (N)
Deletion Rule:
RDBMS will delete the parent table record and updates the FK value of the related child table record with “default value” from the logical specification setting
Set Default (SD)
Do a inner table join for the vendors and products table.
The PK of vendors table is vendor_ID. This is a FK in products table.
The fields you’ll need are vendor_name, product_name, and product_price.
SELECT vendor_name, product_name, product_price
FROM vendors, products
WHERE vendors.vendor_ID = products.vendor_ID
Self Join Example: You want to send mail to all customer contacts that work at the same company where Jim Jones works. So, we have to first find what company (cust_name) Jim Jones works for and then pull all contacts at that same company.
SELECT cust_id, cust_name, cust_contact
FROM Customer
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = ‘Jim Jones’);
