Question

SQL – Microsoft Access

------------------------------------------------------------------------------

display all the fields mentioned; but do NOT display those not mentioned

create ONE QUERY (not five queries) to practice (1) GROUP BY, (2) expressions (calculations), (3) alias, (4) JOIN:

Join the tables Franchisees and Restaurants, (the following points are for you to check and make sure your SQL codes deliver the requirements):

1)            find for each credit rating (of franchisees) and for each state (we have CA and FL in the data) (each group is of different rating-state)

2)            the number of restaurants in each group – use proper column heading (alias),

3)            the average annual sales in each group – use alias,

4)            the maximum square footage of restaurant in each group – use alias,

5)            and the average sales per square footage in each group (use “SalesPerFt” as column header),

6)            for those restaurants whose SalesPerSqFt are $400 or higher.

------------------------------------------------------------------------------

FranchiseelD ▽| FirstName ▽| LastName ▽| HomeAddress HomeCity ▽| HomeState ▽| HomeZip ▽| HomePhone ' Miami Cellphone ▽| CreditRating ▽| Click to Add + F001 + F002 + F003 + F004 + F0O5 F006 F007 + F008 James Dottie Steve FL FL FL FL FL FL CA CA 33176(305) 777-8888 (305) 590-9090 AA 32504- 33427- 32801(407) 444-8888 (407) 666-2266 C Antonucci Balchunas Spann 8990 SW 71 Avenue 400 Canopy BlvdPensacola 5900 West Highway Boca Raton 4 Grove Road 903 SW 1 Street Vero Beach 1239 College Drive Gainesville 9500 Reseda Blvd Northridge 9888 Century Bl Los Angeles (850) 222-1111 (850) 222-9898 B (561) 999-1199 (561) 899-8899 A Miller Kinzer Portu Wong Megan Jessica Carlos Carol 50- (407) 333-0033 (407) 444-9900 B 32601 (352) 999-0044 (352) 999-8811 C 91324- (818) 787-8778 (818) 889-9877 AA 90401 (310) 210-4100 (310) 211-4111 AA te ▽| ㅜ! State ▽|ZipCode ▽| ▽| Annualsales ▽| SquareFootage ▽| DateOpened | ▽| vısa/MasterCard ▽| AmericanExpressワ|DiscoverCard | FranchiseelD ▽ RestaurantID Address 2345 SW 98 Street 3487 Main Highway 89 Tumberry Drive 4598 SW 136 Street 9000 Biscayne Blvd City Phone TypeofService Miami Pensacola FL OrlandoFL Miami Tallahassee FL R0005 32301 (904) 222-1111 33427(561) 444-1100 8990 SE 2 Avenue Miami 298 W 75 Terrace Vero Beach FL 32960 (407) 222-9999 $350,000 1000 Grand Avenue Gainesville FL 6767 NW 75 Street Miami (352) 666-7788 33120 (305) 887-8877 $55,000 1550 Grand Avenue Gainesville FL 9999 Reseada Ave Northridge CA 19010 Plummer St Northridge CA 91324- 2,345 12/30/1999 Table & Take-out 1,987 12/12/1998 Table & Take-out 1/1/2001 Table & Take-out 8765 Corbin Ave NorthridgeCA 91324

Answer 1

If you have any doubts, please give me comment...

-- 1

SELECT CreditRating, HomeState

FROM Franchisees;

-- 2

SELECT FranchiseeID, COUNT(*) noOfRestaurents

FROM Franchisees

GROUP BY FranchiseeID;

-- 3

SELECT FranchiseeID, AVG(AnnualSales) avgSales

FROM Restaurants

GROUP BY FranchiseeID;

-- 4

SELECT FranchiseeID, MAX(SquareFootage) maximuSquareFootage

FROM Restaurants

GROUP BY FranchiseeID;

-- 5

SELECT FranchiseeID, AVG(SquareFootage) SalesPerFt

FROM Restaurants

GROUP BY FranchiseeID;

-- 6

SELECT FranchiseeID, AVG(SquareFootage) avgSquareFootage

FROM Restaurants

GROUP BY FranchiseeID

HAVING AVG(SquareFootage)>=400;