Chapter 11, Problem 20MC

Use the following table to answer the questions below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a customer Char 5 Primary key custFName stores the first name of a customer Varchar2 35 Cannot be null custLName Stores the last name of a customer Varchar2 35 Cannot be null custGender Store the gender of a customer Char 1 It should be ‘M’ or ‘F’ custDOB Stores the date of birth of a customer Date     custIncome Store the income of a customer Number 4     Write in SQL a command that creates the table Customers according to the above description (2 marks).  Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account (1.5 marks).  Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990 (1.5 Marks).…

Based on the following SQL statement: SELECT acct_id, balance FROM Account WHERE balance < 0 AND acct_type = 'Checking' Which if the following is False? Query is retrieving data from the acct_id and balance columns Query is retrieving data from the Account table Query is retrieving all checking accounts with negative balance Query returns all columns from every row in the Account table

Design a query of Stores with the following: Store Name, Store Size, Sum of order amounts, Percent of unpaid order amount, Weighted average volume discount. a. Volume discount: Percent difference between order amount and payment amount Design a query of Stores with the following: Store Name, Sum of all order amounts, Number of all orders, Top customer name, Top customer sum of invoice amounts, Top customer number of invoices, Top customer % of total invoice amount. (4 different tables :  Orders table: orderid storeid customerid OrderDate OrderAmount paymentid PreferredOrder orderstatusid  Customers table:  customerid Name Company Type ContractDate PreferredEnrollmentDate PreferredVoidDate Payments Table: paymentid VolumeDiscount PaymentAmount PaymentDate PaymentMethod Stores Table : storeid Name Store Size Category ContractDate Type Order Status: orderstatusid Name

Some rows of the STUDENT table are shown below:   CODE NAME GPA YEAR 291 ALEX 3.1 2 938 MICHELE 2.3 1 931 JHON 3.3 1 182 JOE 3.4 2 190 REY 2.0 2 330 RON 3.9 3   Which best describes the result of the query below?   SELECT YEAR, AVG(GPA) FROM STUDENT WHERE GPA > 2.0 GROUP BY YEAR     a. The average maximum GPA of all the students in each year.   b. The year of the student with the maximum GPA.   c. The average GPA of students with a GPA higher than 2.0 each year.   d. The average GPA of the students with a GPA higher than 2.0.

Complete the following join operation to find the set of courses each student has taken.   SELECT name, course_id FROM student __________ JOIN takes;

Below are some rows of the PROVIDERS table for a factory: PCODE 10192 98411 0489035 NAME Hans, Inc. Tools and More Motors Inc. PHONE 231 - 3092 231-1029 993 - 1821 All providers from the factory zone of the city start with 231. You want to get the providers that are in the factory zone of the city. Which of the following is more appropriate? O a. O b. SELECT * FROM PROVIDERS WHERE PHONE LIKE '% 231' O C. O d. SELECT * FROM PROVIDERS WHERE PHONE LIKE $231%' SELECT * FROM PROVIDERS WHERE PHONE LIKE 231 SELECT * FROM PROVIDERS WHERE PHONE LIKE '%231%'

The team wants to track potential list of users who may have forgotten their items on the cars. Therefore, you need to return USER_FIRST_NAME, USER_LAST_NAME, TRAVEL_START_TIME, TRAVEL_END_TIME information from the USERS and TRAVELS tables connected inside a JOIN statement by the USING function and USER_ID field

Change to use the database frozenfood Alter table customerOrder add column total as data type decimal size 7,2, not null, default value of 0.0 Update table customerOrder, set column total equal to column quantity multiplied by subquery a. (select column price from table product where column productId in table customerOrder equals column id in table product) Update table customerOrder, set column orderStatelId equal to subquery a. (select column id from table orderstate where state equals “Payment received") Alter table customerPayment add column paymentDate as data type date, not null, default value of (CURRENT_DATE) (Note: the parenthesis MUST be included) Insert into table customerPayment, columns customerOrderId and total result set returned from query a. Select columns id and total from table customerOrder where column orderStateld is equal to subquery i. (select column id from table orderState where column state is equal to "Payment received") Write Test Case 1 as a SQL join query…

Which of the following SELECT statements lists only the book with the largest profit? (refer to the tables in the JustLee Books database.)a. SELECT title, MAX(retail-cost)FROM booksGROUP BY title;b. SELECT title, MAX(retail-cost)FROM booksGROUP BY titleHAVING MAX(retail-cost);c. SELECT title, MAX(retail-cost)FROM books;d. none of the above