Write an sql statement to prepare a list with salesman name


Instructions:

- Answers to questions 1,2 have to be executed in the MySQL terminal. Take a screenshot of the output and paste it in your assignment answer sheet.

- Do not upload a word document to the shell. Your answers should be in a pdf document.

- Make sure all your screenshots and answers are clear. Unclear answers will not be evaluated.

1. Execute the following commands in MySQL terminal

CREATE TABLE hiking ( trail CHAR (50), area CHAR (50), distance FLOAT,
est_time FLOAT); SHOW TABLES;
SHOW COLUMNS FROM hiking:

INSERT INTO hiking VALUES
('Cedar Creek Falls', 'Upper San Diego',4.5, 2.5);

INSERT INTO hiking (trail, area) VALUES
('East Mesa Loop', 'Cuyamaca Mountains' );

SELECT * FROM hiking

UPDATE hiking SET
distance = 10.5, est_time = 5.5 WHERE trail = 'East Mesa Loop';

DELETE FROM hiking WHERE trail = 'Cedar Creek Falls';

a) Give the SQL statements to insert the following values into the hiking table:

trail

area

distance

est_time

East Mesa Loop

Cuyamaca Mountains

10.50

5.50

Oak Canyon

NULL

3.00

NULL

b) Give the SQL statement(s) to update the entry for the 'Oak Canyon' trail. Set the area to 'Mission Trails Regional Park' and the estimated time (est_time) to 2 hours.Your table should then look like the following:

trail

area

distance

est_time

East Mesa Loop

Cuyamaca Mountains

10.50

5.50

Oak Canyon

Mission      Trails Regional Park

3.00

2.00

c) Give the SQL statement to delete trails with a distance greater than 5 miles.
d) Give the SQL statement to create a table called 'rating'. This table rates the difficulty of a hiking trail. It will have two columns: the trail name, 'trail' and the difficulty, 'difficulty'. The tail name is a string of no more than 50 characters and the difficulty is an integer (INT).
e) What is the command to delete the rating table?
f) Give the command to add another column to the hiking table called ‘trail id' with Primary key constraint. Add another column called ‘trail id' in the ‘rating' table, which should be the foreign key with the table referring to the hiking table.

2. Create a database ‘Hollywood' and create the below tables with the constraints listed below:
Movie(mID int, title text, year int, director text); Reviewer(rID int, name text);
Rating(rID int, mID int, stars int, ratingDate date);

Enforce the following constraints on the above database:
- Movie and Reviewer should have primary key constraints on the respective id columns.
- Place auto increment on the mID and rID columns in the Movie and Reviewer tables
- Rating table columns ‘rID' and ‘mID' should refer to the respective columns in the parent tables i.e. Movie and Reviewer.
- The default value of the ‘ratingDate' column in the Rating table should be the current date.
- The ‘year' column in the Movie table should not be greater than 2016.

3. Consider the sample tables below:

Customer:

customer_id

cust_name

city

grade

salesman_id

3002

Nick Rimando

New York

100

5001

3005

Graham Zusi

California

200

5002

3001

Brad Guzan

London

 

5005

3004

3004

Fabian Johns

Paris

300

5006

3007

Brad Davis

New York

200

5001

3009

Geoff Camero

Berlin

100

5003

3008

Julian Green

London

300

5002

3003

Jozy Altidor

Moscow

200

5007

Salesman:

salesman_id

name

city

commission

5001

James Hoog

New York

0.15

5002

Nail Knite

Paris

0.13

5005

Pit Alex

London

0.11

5006

Mc Lyon

Paris

0.14

5003

Lauson Hen

 

0.12

5007

Paul Adam

Rome

0.13

Order:

Order_No

Purch_Amt

Ord_Date

Customer_id

salesman_id

70001

150.5

2012-10-05

3005

5002

70009

270.65

2012-09-10

3001

5005

70002

65.26

2012-10-05

3002

5001

70004

110.5

2012-08-17

3009

5003

70007

948.5

2012-09-10

3005

5002

70005

2400.6

2012-07-27

3007

5001

70008

5760

2012-09-10

3002

5001

70010

1983.43

2012-10-10

3004

5006

70003

2480.4

2012-10-10

3009

5003

70012

250.45

2012-06-27

3008

5002

70011

75.29

2012-08-17

3003

5007

70013

3045.6

2012-04-25

3002

5001

Answer the following questions based on the above tables:

a) Write an SQL statement to prepare a list with salesman name, customer name and their cities for the salesmen and customer who belongs to same city.

b) Write an SQL statement to make a list with order no, purchase amount, customer name and their cities for those orders which order amount between 500 and 2000.

c) Write an SQL statement to know which salesman are working for which customer.

d) Write an SQL statement to find the list of customers who appointed a salesman for their jobs who gets a commission from the company is more than 12%

e) Write an SQL statement to find the list of customers who appointed a salesman for their jobs who does not live in same city where the customer lives, and gets a commission is above 12%

f) Write an SQL statement to find the details of an order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and how much commission he gets for an order.

g) Write an SQL statement to make a join within the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.

4. Consider an AIRLINE relational database schema shown below, which describes a database for airline flight information. Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date.

767_Figure1.jpg

- Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on.

- Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs-one for each Date on which the flight travels. FAREs are kept for each FLIGHT.

- For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg and the actual arrival and departure times and airports.

- An AIRPLANE is identified by an Airplane_id and is of a particular AIRPLANE_TYPE.

- CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land.

- An AIRPORT is identified by an Airport_code.

a. Give the operations for this update.

b. What types of constraints would you expect to check?

c. Which of these constraints are key, entity integrity, and referential integrity constraints, and which are not?

d. Specify all the referential integrity constraints that hold on the schema shown below.

5. Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course:

STUDENT(Ssn, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(Ssn, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_isbn) TEXT(Book_isbn, Book_title, Publisher, Author) Specify the foreign keys for this schema.

Solution Preview :

Prepared by a verified Expert
PL-SQL Programming: Write an sql statement to prepare a list with salesman name
Reference No:- TGS02208430

Now Priced at $35 (50% Discount)

Recommended (98%)

Rated (4.3/5)