Question

(Queries of a single table with filtering conditions) Based on the plumbing supply store database from Chapter 7, write SQL queries that perform the following tasks: A Show the name and unit price of all products priced below $50 (note: SQL treats currency amounts like any other number, so you not use a $ sign or quotes in your query.) should 244 Introductory Relational Database Design for Business, with Microsoft Access B Show the product name, units on order, and units in stock for all products for which the number of units on order is at least 40% of the number of units in stock. C Show the same information as in part (b), but with the additional restriction that the number of units on order is no more than 10. D Show the first name, last name, city, and state of all customers out- side New Jersey (state code "NJ"). E Show the first name, last name, city, and state of all customers who are outside New Jersey or have the first name "Robert" (or both).

Answer 1

SQL Queries

Assuming your "Equipment" Table has the following attributes:

-product_name

-unit_price

-units_on_order

-units_in_stock

And Your "Customer" table has the following attributes:

-first_name

-last_name

-city

-state

A -

Solution:  Select product_name,unit_price from Equipment where unit_price<50

Explanation: Select product name, unit price from table Equipment where unit price value is less than $50

B -

Solution: Select product_name, units_on_order, units_in_stock from Equipment where units_on_order>=0.4*units_in_stock

Explanation: Select product name, unit on order and units in stock from table Equipment where unit on order is greater or equal to 40% of units in stock

C -

Solution:  Select product_name, units_on_order, units_in_stock from Equipment where units_on_order>=0.4*units_in_stock and units_on_order<=10

Explanation: Select product name, unit on order and units in stock from table Equipment where unit on order is greater or equal to 40% of units in stock as well as units on order is less than or equal to 10 (atmost 10)

D -

Solution:  Select first_name,last_name,city,state from Customer where state not in("NJ")

Explanation: select first name,last name,city,state from table Customer where state is not present in the following list ("NJ") i.e. New Jersey

E -  

Solution: Select first_name,last_name,city,state from Customer where state not in("NJ") or first_name="Robert"

Explanation: select first name,last name,city,state from table Customer where state is not present in the following list ("NJ") i.e. New Jersey or first name is Robert

Learn more about SQL: brainly.com/question/25694408

#SPJ4