Create table Operator (

OpID int NOT NULL,

OpName varchar(30) NOT NULL, OpStAdd varchar(40) NOT NULL,

OpCity varchar(20) NOT NULL,

OpZIP int (5) NOT NULL,

OpState char(2) NOT NULL,

OpRating float (4,2),

PRIMARY KEY (OpID));

Create table Customer (

CID int NOT NULL,

CIncome float (8,2) NOT NULL, CName varchar(40) NOT NULL,

CStAdd varchar(40) NOT NULL,

CCity varchar(20) NOT NULL,

CZIP int (5) NOT NULL,

CState char(2) NOT NULL,

PRIMARY KEY (CID));

Create table Triptype (

TID int NOT NULL,

TName varchar(40) NOT NULL,

CDuration int NOT NULL,

PRIMARY KEY (TID));

Create table Location (

LID int NOT NULL,

LName varchar(40) NOT NULL,

LState varchar(20) NOT NULL,

LCountry varchar(25) NOT NULL,

PRIMARY KEY (LID));

Create table Activity (

AID int NOT NULL,

AName varchar(40) NOT NULL,

ALevel varchar(25) NOT NULL,

PRIMARY KEY (AID));

Create table Supplier (

SID int NOT NULL,

SName varchar(40) NOT NULL,

SStAdd varchar(40) NOT NULL,

SCity varchar(20) NOT NULL,

SZIP int (5) NOT NULL,

SState char(2) NOT NULL,

SRating float (4,2),

PRIMARY KEY (SID));

Create table Csurvey (

CSID int NOT NULL,

CComment varchar(50) NOT NULL,

OScore float (4,2),

TScore float (4,2),

LScore float (4,2),

SScore float (4,2),

PRIMARY KEY (CSID),

CID int references Customer

);

Create table Participation (

PID int NOT NULL,

PBDate timestamp(6),

PEDate timestamp(6),

PRIMARY KEY (PID),

CID int references Customer,

AID int references Activity

);

Create table Contract (

CoID int NOT NULL,

CoDate timestamp(6),

CoPrice float(7,2),

PRIMARY KEY (CoID),

OpID int references Operator,

SID int references Supplier,

AID int references Activity

);

Create table Tripsale (

TranID int NOT NULL,

TranDate timestamp(6),

PRIMARY KEY (TranID),

OpID int references Operator,

CID int references Customer

);

Create table Custitinerary (

ItID int NOT NULL,

ItBDate timestamp(6),

ItEDate timestamp(6),

ItPrice float(7,2),

PRIMARY KEY (ItID),

CID int references Customer,

LID int references Location,

TID int references Triptype

);

Create table Attraction (

AttID int NOT NULL,

AttComment varchar(50),

PRIMARY KEY (AttID),

AID int references Activity,

LID int references Location

);

1. Write a query to list the total number of suppliers who are not from China or Brazil and whose ratings are higher than 8.0.

2. Write a query to list the maximum, minimum and average incomes of customers who have visited at least location in the USA.

3. Write a query to list the names and states of male customers who live in CO, FL or WA and who bought an itinerary between Jan 10, 2015 and October 15, 2020, such that the duration of the trip was 5 days and longer. Sort the result on name.

4. Write a query to list the names of operators outside of NY and TX who have participated in two or more contracts. Also list the average contract price. Sort the results based on supplier name.

5. Write a query to list the activities that have received at least two comments.

6. Write a query to list all the non-France and non-India based locations that have beginner or intermediate level activities.