Question

NEED THE SQL QUERIES ASAP PLEASE(LIKE 1 HOUR) THE ONES WITH ID ARE PRIMARY KEYS OR FOREIGN etc

Customer [ CustID, LastName, FirstName, Address, City, State, Zip, Phone, Fax, Email]

Product [ ProdID, Description, Color, Size, Pack]

Sales [ TransID, CustID, ProdID, Price, Quantity, Amount]

1. Write SQL statement to produce a list of unique products and their prices from the Sales table. Please ensure that the products do not repeat.
2. Write an SQL statement to list ProdID and Description for all items that cost more than $10 and less than $ 18. Hint [ Product and Sales tables have to be joined using a WHERE condition]

3. Write an SQL statement to list out all products, their colors and their sizes where the product description ends with ‘ster’

4. Use the SQL IN feature to generate a list of customers (lastname, firstname and address) who live in the following cities: Herndon, Seattle
5. The company just made a new email policy which stipulates that all emails should have the following format: Last Name (7) . First Name (2) @ MIS.COM. Write a SQL statement which when run on the customer table can give an additional field names New_Email with the above mentioned format.

Answer 1

A. SQL statement to produce a list of unique products and their prices from the Sales table is

select distinct ProdId ,Price from Sales

So as per the sql statement prodid and price from the sales table will be shown which is also unique. no repitition of the product will be shown because of the keyword distinct.

B.SQL statement to list ProdID and Description for all items that cost more than $10 and less than $ 18. Hint [ Product and Sales tables have to be joined using a WHERE condition] is

select ProdId, Discription from Product ,Sales where Product.ProdId = Sales.ProdId and Price Between $10 and $18.

So as per the sql statement it gives the product id and their description who have cost more than $10 and less than $ 18. Here we join the two table sales and product with cross product and gives the condition of equality of same prodid so we can say that equi join on relation wihch is written in where clause and used the between operator for the cost condition in where clause only. So the relation gives the product with cost more than $10 and less than $18.

A.  SQL statement to list out all products, their colors and their sizes where the product description ends with ‘ster’ is

Select ProdId ,Color, Size from Product where description like '%ster'.

so this sql statement gives the product id ,their color and size from the product table whoever only the description ending with ster for which we use the "like" operator in where clause to search for specific pattern in a column of description. % sign represens zero one or multipe characters.

A.Use the SQL IN feature to generate a list of customers (lastname, firstname and address) who live in the following cities: Herndon, Seattle

Select lastname,firstname,address from Customer where city in (Herndon, Seattle)

So above SQL generates a list of customers (lastname, firstname and address) who live in the following cities: Herndon, Seattle. Here we use "in" operator to give the condition to select the customer who live in Herndon, Seattle. "in " operator allows you to specify multiple values in a where clause.

B. company just made a new email policy which stipulates that all emails should have the following format: Last Name (7) . First Name (2) @ MIS.COM. Write a SQL statement which when run on the customer table can give an additional field names New_Email with the above mentioned format is

ALTER TABLE Customer  ADD New_Email varchar(20);

it will add the new field in the relation customer with named New_Email.