QUESTION 1 Based on the code above, list the number, name, and balance of all cu

Question

QUESTION 1

Based on the code above, list the number, name, and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000.

a.

SELECT CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

b.

SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

c.

SELECT CustomerNum, CustomerName FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

d.

SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance > 2000 ;

QUESTION 2

Based on the code above, list the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both.

a.

SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ WHERE Customer.CustomerNum=Orders.CustomerNum ;

b.

SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders ;

c.

SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ;

d.

SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer ;

QUESTION 3

Based on the code above, list the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both.

a.

SELECT Description FROM Customer WHERE Storehouse=’3’ AND OnHand>20 ;

b.

SELECT Description FROM Item WHERE Storehouse=’3’ ;

c.

SELECT Description FROM Item WHERE Storehouse=’3’ OR OnHand>20 ;

d.

SELECT Description FROM Item WHERE OnHand>20 ;

QUESTION 4

Based on the code above, list the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand.

a.

SELECT Description FROM Item WHERE Storehouse=’3’ OR OnHand>20 ;

b.

SELECT Description FROM Item WHERE Storehouse=’3’ ;

c.

SELECT Description FROM Customer WHERE Storehouse=’3’ AND OnHand>20 ;

d.

SELECT Description FROM Item WHERE Storehouse=’3’ AND OnHand>20 ;

QUESTION 5

1.    

Based on the code above, list the number, name, credit limit, and balance for all customers with credit limits that exceed their balances.

a.

SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer ;

b.

SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE CreditLimit>Balance ;

c.

SELECT CustomerNum, CustomerName FROM Customer WHERE CreditLimit>Balance ;

d.

SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer WHERE CreditLimit>Balance ;

a.

SELECT CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

b.

SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

c.

SELECT CustomerNum, CustomerName FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;

d.

SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance > 2000 ;

Explanation / Answer

QUESTION 1
Based on the code above, list the number, name, and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000.
As you want to print 3 attributes, number, name, and balance, options a, and c are ruled out.
And option d selects the attributes that satisfies only one condition, i.e., balance > 2000.
So, this is also ruled out.
Finally option b is the one which selects the number, name, and balance of the customers
whose balance is between 2000, and 5000.

QUESTION 2
Based on the code above, list the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both.
You can't use two WHERE clauses on the same query, so option A is ruled out. (Syntactically incorrect).
Option b in the second part creates a huge list, as it is unconditional (NO WHERE clause),
and will just join the data of 2 tables Customer, and Orders. So, this will also not work for us.
Option c will fit our requirement. The first query selects all the customers from the customer
table who are represented by sales rep 30. And the second query selects all the customers
from the customers table, who currently have orders. And the union of both these queries
will give you the final result.
So, you don't have to check for option d.

QUESTION 3

Based on the code above, list the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both.
Option b, and option d are checking with only one constraint, but we have to do with 2. So,
both those options are ruled out.
Option a, will select the descriptions of Customers(this should be items actually) which satisfy both the conditions together.
But that is not our requirement. Our requirement is the description of customers which satisfy
either the first condition or the second condition or both. So, option a is also ruled out.
Finally left with option c, which is our answer. Here the OR in the WHERE clause will
select items which satisfy any of the conditions.

QUESTION 4
Based on the code above, list the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand.
As explained in the previous question, now you want to list the customers who satisfy both
the conditions. So, now you should select the option d.
Option A is selecting items which satisfy any of the condition. So, ruled out.
Option B is checking with only one condition.
Option C is selecting from Customer, but it should be from item. So, ruled out.
Option D is the right answer.

QUESTION 5
1.
Based on the code above, list the number, name, credit limit, and balance for all customers with credit limits that exceed their balances.
Option A is unconditional, but we have a constraint here. So, ruled out.
Option B is selecting only 3 attributes but we need a total of 4 attributes. So, ruled out.
Option C is selecting only 2 attributes but we need a total of 4 attributes. So, ruled out.
So, finally Option D chooses all the required attributes, with specified condition.
So, D is the answer.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.