SQL Consider the database for the customers of a club Member

SQL: Consider the database for the customers of a club Members

Phone Name City     Zip      Area-Code YTD *

Ch, 7   Ch, 30 Ch, 20 Ch, 10 Ch, 3           Currency *

YTD: Tot Year To Date Purchase Amount

Products Product Code Name Description color

               Ch,5               Ch 25 Ch 50         Ch 10

Purchases Phone ProdID Quantity SalesmanCode

                Ch, 7   Ch, 5    Integer     Ch, 2

Write a few examples of data for understanding, and then write the SQL Statements to retrieve data for the following queries. (You don\'t have to use Access, just write the statements here)

a. Show all the information about customers

b. Show all purchase Information

c. Show the Name and address of all customers who live in S.F.

d. Show all the information for the members who are in 510 area code

e. Show the Product ID\'s and the amount for customer whose phone is: 555 1234

f. Show all the Products that Salesman Joe Brown sold; his code is: JB

g. Show the information for customers who have purchased more that $5000 (so far)

h. Show the phone numbers of the best customers (define it as having Year to Date of more than $15,000

i. Which tables are to be used, what operation needs to be done to do the following queries? 1) Show the Customer\'s names, phones, and the products that they have purchased 2) Show the name, address of the customers who have purchased product form Salesperson \"JB\" 1) 2)

Solution

a) Select Phone,name,City,Zip,Area-Code,YTD from Customers;

b) Select ProductCode,name,Description,Color,Quantity from Products;

c) Select Name,Zip,Area-Code from Customers where City =\'S.F.\';

d) Select Phone,Name,City,Zip from Customers where Area-Code = 510;

e) Select Purchases.ProdID,YTD.PurchaseAmount from Purchases inner join Customers on Purchases.Phone = Customers.Phone inner join YTD on Customers.YTD = YTD.TotYearToDate where Customers.Phone =\'555 1234\';

f) Select Products.ProductCode,Products.Name from Products inner join Purchases on Products.ProductCode =Purchases.ProdID where Purchases.SalesmanCode = \'JB\';

g) Select Customers.Phone,Customers.Name,Customers.City,Customers.Zip,Customers.Area-Code,Customers.YTD from Customers inner join YTD on Customers.YTD = Ytd.TotYearToDate where YTD.PurchaseAmount > 5000;

h) Select Phone from Customers where YTD >15000;

i)

Tables:

Customers(Phone,name,City,Zip,Area-Code,YTD *)

YTD(TotYearToDate,PurchaseAmount)

Products(ProductCode,name,Description,Color)

Purchases(Phone,ProdID,Quantity,SalesmanCode)

1.

Select Customers.Name,Customers.Phone,Products.ProductCode,Products.Name from Customers inner join Purchases on Customers.Phone = Purchases.Phone inner join Products on Products.ProductCode = Purchases.ProdID;