Assignment 4: SQL for Data Retrieval (Single Table) Assignment 4 is based on the ABC Database's four tables we have created: CUSTOMER, ORDERTBL, PRODUCT and ORDERLINE. 1. List the product (ProdID, Category and price) whose color is black and whose price falls between 200 and 300. 2. List the ID, category and description of the product that has the word 'jet' in its description. 3. List all customers whose name is 'Wally Jones' and lives in the state of Washington (WA). 4. List all customers (last name, first name and zip code) whose zip code is missing (unknown). 5. List the name and phone number of the customer with area code of '678'. 6. We'd like to markdown by 10% the price of the product whose price is greater than or equal to 300. List the ID, category, price, and mark-downed price. Rename the mark- downed price as 'SalePrice'. 7. List all color of the product for category B. Remove any duplicates. 8. List the ID, category and size of the product whose size is 1, 2 or 3. 9. List the ID, category, inventory value of the product whose inventory value is greater than 10,000. Inventory value can be calculated by multiplying price and quantity on hand. Name the inventory value as InventoryValue. 10. List the ID and date of any orders that were placed in August, 2016.

The order of operands is significant in EXCEPT (MINUS) queries but is irrelevant in UNION queries?

List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.

Order of tuples in a table is irrelevant, true or false?

4. Update the name of the student to "John Doe" where its id is 10. Enter your answer 5. Modify the "student" table by adding "address" column/field as string and allows null.

How can we check if a table has minimum redundency?A) There is no way to checkB) See if a table has any primary values duplicated somewhere elseC) Use a normal form to checkD) See if a table has a lot of null values

Students: Johnson, Williams, Matt, Jones, Eric, Lili, John and Jason, whose grades are listed below. Each column is the grades each student received. There are 8 students with 10 grades each. Johnson: 79, 92, 85, 0, 100, 64, 72, 99, 90, 86 Williams: 80, 75 93, 94, 0, 90, 89, 88, 87, 86 Matt: 82, 67, 72, 74, 89, 0, 70, 89, 100, 76 Jones: 85, 84, 82.5, 82, 84, 80, 80.5, 90, 96, 85 Eric: 90, 91, 87, 85.5, 86.5, 0, 90, 90, 91, 92 Lili: 60, 75, 81, 70, 80, 81, 94, 67.5, 99, 98 John: 79,0, 74, 96, 88.5, 88, 0, 100, 86, 83 Jason: 89, 90, 90, 85, 85, 90, 91, 88, 100, 79 Write user-defined functions or build-in functions (e.g., sum, mean, max, min, etc.) to calculate: The Average Grade of each student; this average should be a vector. The average of the class; this average is a scalar vector. The highest grade among the Average Grade of Each Student The lowest grade among the Average Grade of Each Student Show a table with each student's name, average grade and letter score using A, B, C, D, F…

Composite main keys have prospective keys.

A Pivot Table is the column name with its table name. O True O False

Please do the exercise! Thank youuu