Question: Please I need help. I have this SQL Tables created and now I need to answer some questions. The questions are down below. CREATE TABLE

Please I need help. I have this SQL Tables created and now I need to answer some questions. The questions are down below.

CREATE TABLE City(

City# NUMBER(4),

City VARCHAR2(15) NOT NULL,

CONSTRAINT city#_pk PRIMARY KEY(city#)

);

CREATE TABLE Users(

User# NUMBER(4),

First_name VARCHAR2(20) NOT NULL,

Last_name VARCHAR2(20) NOT NULL,

Email VARCHAR2(50) NOT NULL,

Phone_Number VARCHAR2(15),

CONSTRAINT user#_pk PRIMARY KEY(user#)

);

CREATE TABLE Category(

Category# NUMBER(4),

Name VARCHAR2(50) NOT NULL,

CONSTRAINT category#_pk PRIMARY KEY(category#)

);

CREATE TABLE Business(

Business# NUMBER(4),

City# NUMBER(4) NOT NULL,

Business_name VARCHAR2(50) NOT NULL,

CONSTRAINT business#_pk PRIMARY KEY(business#),

CONSTRAINT city#_fk FOREIGN KEY (city#)

REFERENCES city(city#)

);

CREATE TABLE Business_Category(

Business# NUMBER(4) NOT NULL,

Category# NUMBER(4) NOT NULL,

CONSTRAINT bs_category#_pk PRIMARY KEY(business#,category#),

CONSTRAINT bs_business#_fk FOREIGN KEY (business#)

REFERENCES business(business#),

CONSTRAINT bs_category#_fk FOREIGN KEY (category#)

REFERENCES category(category#)

);

CREATE TABLE Business_Rating(

Rate# NUMBER(4),

User# NUMBER(4) NOT NULL,

Business# NUMBER(4) NOT NULL,

Rating_Scale NUMBER(1) NOT NULL,

Rating_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

Comments VARCHAR2(200) NOT NULL,

CONSTRAINT business_rating#_pk PRIMARY KEY(rate#),

CONSTRAINT user#_fk FOREIGN KEY (user#)

REFERENCES users(user#),

CONSTRAINT br_business#_fk FOREIGN KEY (business#)

REFERENCES business(business#)

);

/*Populating City table*/

INSERT INTO City VALUES (1000,'Chicago');

INSERT INTO City VALUES (1001,'New York');

INSERT INTO City VALUES (1002,'Los Angeles');

INSERT INTO City VALUES (1003,'Houston');

INSERT INTO City VALUES (1004,'San Diego');

/*Populating Users table*/

INSERT INTO Users VALUES (100,'SMITH', 'SAM','smithsam@hotmail.com','(541)754-3010');

INSERT INTO Users VALUES (101,'JONES','JANICE','jonesj@hotmail.com','(541) 774-3332');

INSERT INTO Users VALUES (102,'AUSTIN','JAMES','austinj@yahoo.com','(541) 743-3044');

INSERT INTO Users VALUES (103,'MARTINEZ','SHEILA','martinezs@hotmail.com','(541) 734-3976');

INSERT INTO Users VALUES (104,'KZOCHSKY','TAMARA','kzochsky@biz.com','(541) 788-3098');

INSERT INTO Users VALUES (105,'PORTER','LISA','porterl@edu.com','(541) 994-3099');

INSERT INTO Users VALUES (106,'ADAMS','JUAN','adamsj@yahoo.com','(541) 774-1210');

INSERT INTO Users VALUES (107,'BAKER','JACK','bakerjck@edu.com','(541) 754-3111');

INSERT INTO Users VALUES (108,'PETERSON','TINA','petersont@biz.com','(541) 444-3040');

INSERT INTO Users VALUES (109,'WHITE','WILLIAM','whitewlm@hotmail.com','(591) 654-6010');

INSERT INTO Users VALUES (110,'WHITE','LISA','whitels@yahoo.com','(541) 254-3810');

INSERT INTO Users VALUES (111,'ROBINSON','ROBERT','robinsonr@hotmail.com','(541) 554-3019');

INSERT INTO Users VALUES (112,'FIELDS','OSCAR','fieldo@yahoo.com','(571) 954-37123');

INSERT INTO Users VALUES (113,'WILKINSON','ANTHONY','wilkinsona@hotmail.com','(542) 774-8010');

INSERT INTO Users VALUES (114,'GITHUB','GURU','githubg@example.com','(541) 854-3910');

/*Populating Category table*/

INSERT INTO Category VALUES (10,'Service Business');

INSERT INTO Category VALUES (11,'Merchandising Business');

INSERT INTO Category VALUES (12,'Manufacturing Business');

INSERT INTO Category VALUES (13,'Craft');

INSERT INTO Category VALUES (14,'Entertainment');

/*Populating Business table*/

INSERT INTO Business VALUES (2000,1000,'Mic Tech Computers.');

INSERT INTO Business VALUES (2001,1000,'Harrizon Fashions.');

INSERT INTO Business VALUES (2002,1000,'Tom Entertainments.');

INSERT INTO Business VALUES (2003,1000,'Joy Fruit Juices.');

INSERT INTO Business VALUES (2004,1001,'Zebra Entertaiments.');

INSERT INTO Business VALUES (2005,1002,'Hybrid Manufacturers.');

INSERT INTO Business VALUES (2006,1002,'Buffalo Manufacturers.');

INSERT INTO Business VALUES (2007,1003,'A Plus Service Providers.');

INSERT INTO Business VALUES (2008,1004,'Best Rapper Entertainers.');

INSERT INTO Business VALUES (2009,1004,'Guru Designers.');

/*Populating Business_Category table*/

INSERT INTO Business_Category VALUES (2000,10);

INSERT INTO Business_Category VALUES (2006,11);

INSERT INTO Business_Category VALUES (2005,12);

INSERT INTO Business_Category VALUES (2004,13);

INSERT INTO Business_Category VALUES (2009,14);

INSERT INTO Business_Category VALUES (2003,13);

INSERT INTO Business_Category VALUES (2008,12);

INSERT INTO Business_Category VALUES (2002,11);

INSERT INTO Business_Category VALUES (2007,10);

INSERT INTO Business_Category VALUES (2001,14);

/*Populating Business_Rating table*/

INSERT INTO Business_Rating VALUES (1,100,2000,5,TIMESTAMP '2017-12-31 23:59:58','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (2,101,2000,2,TIMESTAMP '2017-11-21 13:29:52','Poor Customer Services.');

INSERT INTO Business_Rating VALUES (3,102,2000,4,TIMESTAMP '2017-10-11 23:59:57','Awesome Customer Services.');

INSERT INTO Business_Rating VALUES (4,103,2000,4,TIMESTAMP '2018-01-20 10:39:59','Good Customer Services.');

INSERT INTO Business_Rating VALUES (5,104,2000,5,TIMESTAMP '2017-12-01 20:59:50','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (6,105,2001,5,TIMESTAMP '2018-01-31 23:49:58','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (7,106,2001,5,TIMESTAMP '2017-07-21 13:29:42','Poor Customer Services.');

INSERT INTO Business_Rating VALUES (8,107,2001,4,TIMESTAMP '2017-11-11 23:59:57','Awesome Customer Services.');

INSERT INTO Business_Rating VALUES (9,108,2001,5,TIMESTAMP '2018-03-20 10:32:59','Execllent Customer Services.');

INSERT INTO Business_Rating VALUES (10,109,2001,5,TIMESTAMP '2018-02-01 21:59:50','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (11,102,2002,5,TIMESTAMP '2018-02-19 09:59:58','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (12,102,2002,5,TIMESTAMP '2018-01-21 15:29:52','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (13,103,2002,5,TIMESTAMP '2018-04-11 22:59:57','Awesome Customer Services.');

INSERT INTO Business_Rating VALUES (14,101,2002,4,TIMESTAMP '2017-01-20 10:39:59','Good Customer Services.');

INSERT INTO Business_Rating VALUES (15,104,2002,5,TIMESTAMP '2017-12-01 06:59:50','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (16,102,2003,2,TIMESTAMP '2018-01-31 18:49:58','Poor Customer Services and products.');

INSERT INTO Business_Rating VALUES (17,102,2003,1,TIMESTAMP '2018-02-21 16:29:42','Poor Customer Services and products.');

INSERT INTO Business_Rating VALUES (18,102,2003,1,TIMESTAMP '2018-03-11 12:50:57','Disguisting Customer Services.');

INSERT INTO Business_Rating VALUES (19,108,2003,1,TIMESTAMP '2018-04-20 11:32:59','Very poor Customer Services.');

INSERT INTO Business_Rating VALUES (20,107,2003,4,TIMESTAMP '2018-04-01 19:54:50','Good Customer Services.');

INSERT INTO Business_Rating VALUES (21,103,2004,4,TIMESTAMP '2018-02-21 09:59:58','Good Customer Services.');

INSERT INTO Business_Rating VALUES (22,103,2004,2,TIMESTAMP '2017-10-21 15:29:52','Bad Customer Services.');

INSERT INTO Business_Rating VALUES (23,102,2004,5,TIMESTAMP '2018-02-11 22:59:57','Extreamly good Customer Services.');

INSERT INTO Business_Rating VALUES (24,102,2004,5,TIMESTAMP '2017-11-20 10:39:59','Best Customer Services.');

INSERT INTO Business_Rating VALUES (25,104,2004,5,TIMESTAMP '2017-12-01 06:59:50','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (26,107,2004,3,TIMESTAMP '2018-01-31 18:49:58','Better Customer Services and products.');

INSERT INTO Business_Rating VALUES (27,106,2004,4,TIMESTAMP '2018-02-21 16:29:42','Good Customer Services and products.');

INSERT INTO Business_Rating VALUES (28,105,2004,5,TIMESTAMP '2018-03-11 12:50:57','Best Customer Services.');

INSERT INTO Business_Rating VALUES (29,108,2005,1,TIMESTAMP '2018-04-20 11:32:59','Very poor Customer Services.');

INSERT INTO Business_Rating VALUES (30,109,2005,4,TIMESTAMP '2018-04-01 19:54:50','Good Customer Services.');

INSERT INTO Business_Rating VALUES (31,103,2006,4,TIMESTAMP '2018-02-01 09:59:58','Good Customer Services.');

INSERT INTO Business_Rating VALUES (32,104,2006,2,TIMESTAMP '2017-10-29 15:29:52','Bad Customer Services.');

INSERT INTO Business_Rating VALUES (33,104,2006,5,TIMESTAMP '2018-02-17 22:59:57','Extreamly good Customer Services.');

INSERT INTO Business_Rating VALUES (34,105,2007,5,TIMESTAMP '2017-12-20 10:39:59','Best Customer Services.');

INSERT INTO Business_Rating VALUES (35,107,2007,5,TIMESTAMP '2017-12-01 06:59:50','Ecellent Customer Services.');

INSERT INTO Business_Rating VALUES (36,101,2007,3,TIMESTAMP '2018-02-11 18:49:58','Need improvement');

INSERT INTO Business_Rating VALUES (37,101,2007,2,TIMESTAMP '2018-04-21 16:29:42','Poor Services and products.');

INSERT INTO Business_Rating VALUES (38,108,2008,2,TIMESTAMP '2018-04-11 12:50:57','Disgusting Customer Services.');

INSERT INTO Business_Rating VALUES (39,106,2009,2,TIMESTAMP '2018-03-20 11:32:59','Very poor Customer Services.');

INSERT INTO Business_Rating VALUES (40,109,2009,5,TIMESTAMP '2018-02-01 19:54:50','Good Customer Services.');

INSERT INTO Business_Rating VALUES (41,104,2009,3,TIMESTAMP '2017-02-12 18:29:58','Need improvement');

INSERT INTO Business_Rating VALUES (42,102,2007,2,TIMESTAMP '2018-01-10 06:29:40','Poor Services and products.');

INSERT INTO Business_Rating VALUES (43,103,2007,1,TIMESTAMP '2018-04-01 07:50:07','Disgusting Customer Services.');

INSERT INTO Business_Rating VALUES (44,103,2009,1,TIMESTAMP '2018-03-04 10:32:19','Very poor Customer Services.');

INSERT INTO Business_Rating VALUES (45,104,2008,4,TIMESTAMP '2018-02-03 12:54:22','Good Customer Services.');

Answer this questions please!

-List of all businesses with 4-star rating and above

-Business with most number of ratings in a particular city

-City with most number of businesses with 5-star ratings

-User with most number of ratings

-List of all businesses with their ratings

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!