Database Management

restaurants-rev16.accdb

Home >Information Systems homework help >Database Management

FranchiseeID	FirstName	LastName	HomeAddress	HomeCity	HomeState	HomeZip	HomePhone	CellPhone	CreditRating
F001	James	Antonucci	8990 SW 71 Avenue	Miami	FL	33176	3057778888	3055909090	AA
F002	Dottie	Balchunas	400 Canopy Blvd	Pensacola	FL	32504	8502221111	8502229898	B
F003	Steve	Spann	5900 West Highway	Boca Raton	FL	33427	5619991199	5618998899	a
F004	Benjamin	Grauer	4 Grove Road	Orlando	FL	32801	4074448888	4076662266	C
F005	Megan	Miller	903 SW 1 Street	Vero Beach	FL	32960	4073330033	4074449900	B
F006	Jessica	Kinzer	1239 College Drive	Gainesville	FL	32601	3529990044	3529998811	C
F007	Carlos	Portu	9500 Reseda Blvd	Northridge	CA	91324	8187878778	8188899877	AA
F008	Carol	Wong	9888 Century Bl	Los Angeles	CA	90401	3102104100	3102114111	AA

RestaurantID	Address	City	State	Zipcode	Phone	AnnualSales	SquareFootage	DateOpened	TypeofService	Visa/MasterCard	AmericanExpress	DiscoverCard	FranchiseeID
R0001	2345 SW 98 Street	Miami	FL	33133	3054448787	¤ 575,000.00	2000	6/1/98	Table Service	true	false	true	F001
R0002	3487 Main Highway	Pensacola	FL	32504	8508865555	¤ 350,000.00	1000	4/15/99	Table & Take-out	true	false	false	F002
R0003	89 Turnberry Drive	Orlando	FL	32801	4075559999	¤ 750,000.00	2500	1/1/00	Table Service	true	true	true	F004
R0004	4598 SW 136 Street	Miami	FL	33156	3054444444	¤ 200,000.00	750	3/1/98	Take-out	true	false	false	F001
R0005	9000 Biscayne Blvd	Tallahassee	FL	32301	9042221111	¤ 800,000.00	2200	12/1/00	Table & Take-out	true	true	true	F003
R0006	2 State Street	Boca Raton	FL	33427	5614441100	¤ 175,000.00	750	5/1/01	Take-out	false	false	false	F003
R0007	8990 SE 2 Avenue	Miami	FL	33133	3057877889	¤ 500,000.00	1800	3/31/01	Table Service	true	true	true	F001
R0008	298 W 75 Terrace	Vero Beach	FL	32960	4072229999	¤ 350,000.00	2850	6/1/00	Table & Take-out	true	false	false	F005
R0009	1000 Grand Avenue	Gainesville	FL	32601	3526667788	¤ 700,000.00	1000	5/31/99	Take-out	true	false	false	f006
R0010	6767 NW 75 Street	Miami	FL	33120	3058878877	¤ 750,000.00	1200	4/3/99	Table Service	true	true	false	f005
R0011	7890 Concord St	Orlando	FL	32801		¤ 675,000.00	1300	5/5/05	Table & Take-out	false	false	false	F004
R0012	979 Alba Dr	Orlando	FL	32801		¤ 555,000.00	2000	6/6/06	Table & Take-out	false	false	false	F004
R0013	7050 Biscayne Blvd	Tallahassee	FL	32301		¤ 580,000.00	1800	7/7/07	Table Service	false	false	false	F002
R0014	1550 Grand Avenue	Gainesville	FL	32601		¤ 480,000.00	2200	8/8/08	Table & Take-out	false	false	false	F006
R0015	9999 Reseada Ave	Northridge	CA	91324		¤ 366,000.00	1900	1/1/91	Take-out	false	false	false	F002
R0016	19010 Plummer St	Northridge	CA	91324		¤ 876,000.00	2222	1/1/06	Table Service	false	false	false	F002
R0017	8989 Tampa Ave	Northridge	CA	91324		¤ 888,000.00	2345	12/30/99	Table & Take-out	false	false	false	F004
R0018	8765 Corbin Ave	Northridge	CA	91324		¤ 654,000.00	1987	12/12/98	Table & Take-out	false	false	false	F007
R0019	1777 Main St	Santa Monica	CA	90401		¤ 765,000.00	2000	1/1/01	Table & Take-out	false	false	false	F008
R0020	135 Lincoln	Santa Monica	CA	90406		¤ 399,000.00	1100	6/6/06	Take-out	false	false	false	F008

SELECT DISTINCTROW * FROM Franchisees;

SELECT DISTINCTROW * FROM Restaurants;

SELECT DISTINCTROW * FROM [Query1-ex];

SELECT Restaurants.RestaurantID, Restaurants.Address, Restaurants.City, Restaurants.State, Restaurants.Zipcode, Franchisees.FranchiseeID, Franchisees.FirstName, Franchisees.LastName, Franchisees.HomePhone, Restaurants.AnnualSales, Restaurants.SquareFootage, Restaurants.TypeofService FROM Franchisees INNER JOIN Restaurants ON Franchisees.FranchiseeID=Restaurants.FranchiseeID;

SELECT restaurantID, annualsales, city FROM Restaurants AS R WHERE annualsales> (select avg(annualsales) from restaurants where city = r.city group by city) ORDER BY city;