Question
Answer the following as SQL queries for the oracle 10g/11i/12c database system. With the sql script that follows. (11)List the names of restaurants and the
Answer the following as SQL queries for the oracle 10g/11i/12c database system. With the sql script that follows.
(11)List the names of restaurants and the number of orders they have received to-date in the system.
(12)Print the names of tough customer reviewers. Tough reviewers are ones who have given the lowest overall rating value that a restaurant has for each of the restaurants they have reviewed.
(13)Print the names of customers whose phone number starts with area code 414.
(14)Get the names of customers, and the total spend for each customer.
(15) Get the names of customers who have ordered ONLY from Indian restaurants.
Database Script
DROP TABLE RESTAURANT CASCADE CONSTRAINTS; CREATE TABLE RESTAURANT ( RestId integer primary key, RName varchar2(50) not null, Phone varchar2(12), Email varchar2(50), hours varchar2(50), DiningType varchar2(20) check (DiningType in ('Fast Food','Fast Casual','Casual Dining','Family Style', 'Fine Dining')), StreetNo number(5), StreetName varchar2(30), City varchar2(15), State varchar2(5), Zip number(10), PriceRange varchar2(15), DeliveryFlag char check(DeliveryFlag in ('Y','N')), OutdoorSeatingFlag char check(OutdoorSeatingFlag in ('Y','N')) ); DROP TABLE REST_CUISINE CASCADE CONSTRAINTS; CREATE TABLE REST_CUISINE ( RestId integer references RESTAURANT(RestId), CName varchar2(30) check (CNAME in ('American','Mexican','Indian','Pizza','Sandwich','Chinese','Italian')), primary key(RestId,CName) ); DROP TABLE FOOD_ITEM CASCADE CONSTRAINTS; CREATE TABLE FOOD_ITEM ( FoodName varchar2(100) primary key, FCatName varchar2(25) check (FCatName in ('Soup', 'Appetizer','Main','Dessert','Drink')) ); DROP TABLE MENU CASCADE CONSTRAINTS; CREATE TABLE MENU ( RestId integer references RESTAURANT(RestId), FoodName varchar2(25) references FOOD_ITEM(FoodName), Price number(5), primary key(RestId,FoodName) ); DROP TABLE RESTAURANT_CUSTOMER CASCADE CONSTRAINTS; CREATE TABLE RESTAURANT_CUSTOMER ( Email varchar2(40) primary key, FName varchar2(30) not null, minit char, LName varchar2(30) not null, Phone varchar2(12), Password varchar2(30) not null, StreetNo number(10), StreetName varchar2(30), City varchar2(15), State varchar2(10), Zip number(5) ); DROP TABLE RESTAURANT_ORDER CASCADE CONSTRAINTS; CREATE TABLE RESTAURANT_ORDER ( Ono integer primary key, OrderDate date not null, CustEmail references RESTAURANT_CUSTOMER(Email), OrderDineInFlag char check(OrderDineInFlag in ('Y','N')), OrderDeliveryFlag char check(OrderDeliveryFlag in ('Y','N')), OrderPickupFlag char check(OrderPickupFlag in ('Y','N')), OrderPickupDate date, OrderDeliveryDate date, OrderDineInReceivedDate date ); DROP TABLE RESTAURANT_ODETAILS CASCADE CONSTRAINTS; CREATE TABLE RESTAURANT_ODETAILS ( Ono integer references RESTAURANT_ORDER(Ono), FoodName varchar2(25) references FOOD_ITEM(FoodName), RestId integer references RESTAURANT(RestId), Qty number(5) not null check (Qty > 0), primary key(Ono,FoodName,RestId) ); DROP TABLE CUST_REVIEW CASCADE CONSTRAINTS; CREATE TABLE CUST_REVIEW ( ReviewId integer primary key, CustEmail varchar2(40) references RESTAURANT_CUSTOMER(Email), RestId integer references RESTAURANT(RestId), ReviewTitle varchar2(100), ReviewDescr varchar2(1000), ReviewDate date, Rating number(2) check (Rating >= 1 and Rating <= 5), unique (RestId,CustEmail,ReviewDate) ); DROP SEQUENCE Restaurant_RestId_Seq; CREATE SEQUENCE Restaurant_RestId_Seq START with 1000 INCREMENT BY 1; DROP SEQUENCE Customer_ReviewId_Seq; CREATE SEQUENCE Customer_ReviewId_Seq START with 12000 INCREMENT BY 1; DROP SEQUENCE Restaurant_Order_Ono; CREATE SEQUENCE Restaurant_Order_Ono START with 27000 INCREMENT BY 1; insert into RESTAURANT VALUES (Restaurant_RestId_Seq.nextval,'China Town', '111-111-1111',null,null,'Family Style',null,null,null,null,null,'5-20','Y','N'); insert into RESTAURANT VALUES (Restaurant_RestId_Seq.nextval,'Milwaukee Burger', '112-111-1111',null,null,'Casual Dining',null,null,null,null,null,'5-15','Y','Y'); insert into RESTAURANT VALUES (Restaurant_RestId_Seq.nextval,'Indian Mahal', '113-111-1111',null,null,'Family Style',null,null,null,null,null,'8-20','Y','N'); insert into RESTAURANT VALUES (Restaurant_RestId_Seq.nextval,'Olive Garden', '114-111-1111',null,null,'Family Style',null,null,null,null,null,'10-20','Y','Y'); insert into REST_CUISINE VALUES (1000,'Chinese'); insert into REST_CUISINE VALUES (1001,'American'); insert into REST_CUISINE VALUES (1001,'Sandwich'); insert into REST_CUISINE VALUES (1002,'Indian'); insert into REST_CUISINE VALUES (1003,'Italian'); insert into REST_CUISINE VALUES (1003,'American'); insert into REST_CUISINE VALUES (1003,'Sandwich'); insert into FOOD_ITEM VALUES ('Chicken Burger','Main'); insert into FOOD_ITEM VALUES ('Veggie Burger','Main'); insert into FOOD_ITEM VALUES ('Veggie Pasta','Main'); insert into FOOD_ITEM VALUES ('Chicken Pasta','Main'); insert into FOOD_ITEM VALUES ('Chicken Sandwich','Main'); insert into FOOD_ITEM VALUES ('Veggie Sandwich','Main'); insert into FOOD_ITEM VALUES ('Tomato Mozarella Sandwich','Main'); insert into FOOD_ITEM VALUES ('Chicken Lo Mein Noodles','Main'); insert into FOOD_ITEM VALUES ('Veggie Pan Fried Noodles','Main'); insert into FOOD_ITEM VALUES ('Paneer Tikka Masala Curry','Main'); insert into FOOD_ITEM VALUES ('Chicken Tikka Masala Curry','Main'); insert into FOOD_ITEM VALUES ('Cesear Salad','Appetizer'); insert into FOOD_ITEM VALUES ('Chicken Salad','Appetizer'); insert into FOOD_ITEM VALUES ('French Fries','Appetizer'); insert into FOOD_ITEM VALUES ('Cheese Curds','Appetizer'); insert into FOOD_ITEM VALUES ('Chicken Wings','Appetizer'); insert into FOOD_ITEM VALUES ('Nacho Chips','Appetizer'); insert into FOOD_ITEM VALUES ('Edamame Beans','Appetizer'); insert into FOOD_ITEM VALUES ('Spring Rolls','Appetizer'); insert into FOOD_ITEM VALUES ('Chicken Soup','Soup'); insert into FOOD_ITEM VALUES ('Bean Soup','Soup'); insert into FOOD_ITEM VALUES ('Veggie Soup','Soup'); insert into FOOD_ITEM VALUES ('Clam Chowder Soup','Soup'); insert into FOOD_ITEM VALUES ('Minestrone Soup','Soup'); insert into FOOD_ITEM VALUES ('Hot and Sour Soup','Soup'); insert into MENU VALUES (1000,'Edamame Beans',3.50); insert into MENU VALUES (1000,'Chicken Lo Mein Noodles',14.50); insert into MENU VALUES (1000,'Hot and Sour Soup',3.50); insert into MENU VALUES (1001,'Chicken Soup',3.70); insert into MENU VALUES (1001,'Bean Soup',3.25); insert into MENU VALUES (1001,'Cheese Curds',4.20); insert into MENU VALUES (1001,'Chicken Wings',4.50); insert into MENU VALUES (1001,'Chicken Sandwich',10.50); insert into MENU VALUES (1001,'Veggie Sandwich',10.00); insert into MENU VALUES (1001,'Chicken Burger',14.00); insert into MENU VALUES (1001,'Veggie Burger',12.00); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('john@restaurantadvisor.com','John','Smith','john123'); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('franklin@restaurantadvisor.com','Franklin','Wong','franklin123'); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('alicia@restaurantadvisor.com','Alicia','Zelaya','alicia123'); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('jennifer@restaurantadvisor.com','Jennifer','Wallace','jennifer123'); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('ramesh@restaurantadvisor.com','Ramesh','Narayan','ramesh123'); insert into RESTAURANT_CUSTOMER(Email,FName,LName,Password) values('james@restaurantadvisor.com','James','Borg','james123'); insert into RESTAURANT_ORDER VALUES (Restaurant_Order_Ono.nextval,sysdate,'john@restaurantadvisor.com','Y','N','N',null,null,sysdate + interval '10' minute); insert into RESTAURANT_ORDER VALUES (Restaurant_Order_Ono.nextval,sysdate,'franklin@restaurantadvisor.com','N','Y','N',null,sysdate + interval '45' minute,null); insert into RESTAURANT_ODETAILS VALUES (27000,'Chicken Soup','1001',1); insert into RESTAURANT_ODETAILS VALUES (27000,'Chicken Sandwich','1001',2); insert into RESTAURANT_ODETAILS VALUES (27000,'Veggie Burger','1001',1); insert into RESTAURANT_ODETAILS VALUES (27001,'Edamame Beans','1000',2); insert into RESTAURANT_ODETAILS VALUES (27001,'Hot and Sour Soup','1000',3); insert into RESTAURANT_ODETAILS VALUES (27001,'Chicken Lo Mein Noodles','1000',3); insert into CUST_REVIEW VALUES(Customer_ReviewId_Seq.nextval,'franklin@restaurantadvisor.com',1000,'Yummy Food','The noodles are a must try here. Awesome food.',sysdate,4.5); insert into CUST_REVIEW VALUES(Customer_ReviewId_Seq.nextval,'franklin@restaurantadvisor.com',1003,'Service Not Great','service was poor. We had to wait a long time',sysdate,1.5); insert into CUST_REVIEW VALUES(Customer_ReviewId_Seq.nextval,'john@restaurantadvisor.com',1001,'Lovely','Satisfying Food',sysdate,5);
Expert Answer
Hang tight! We are finding a Chegg Expert to answer this question.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started