Write an sql statement to prepare a list with salesman name

Assignment Help PL-SQL Programming
Reference no: EM131411802

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.

Verified Expert

This assignment is based on MySQL. In this assignment basic structure of the database is provided with sample insert and delete statement. They requested to write the insert data into the table with the insert query. The inserted data is then updated with the help of the update query. In the existing databases the constraints like primary and foreign key constraints are added. An existing database is provided with tables and data. From the given table the useful data are extracted using the SELECT query. From the business rules the statements are classified as constraints on the database.

Reference no: EM131411802

Questions Cloud

What did you find useful in the gtiven article : What did you find useful in this article? Did you follow any of the links? Are you using Bloom's Revised taxonomy or DOK to find verbs to drive your objectives? Did you learn anything new?
Concept of time value of money : Raul needs to choose one alternative from the four alternatives given below. Applying the concept of time value of money, which of the following alternatives should he select?
Different marketing mix elements to better appeal : Based on the information provided about the 5 demographic age groups in the U.S. population, explain how companies could alter two different marketing mix elements to better appeal to each one of those 5 groups. You can use the same company or severa..
Calculate the net working capital : Since he does not have the resources to hire a financial expert, he has to manage the company's finance in addition to managing the company. He needs to calculate the working capital of his business. From the following information, calculate the n..
Write an sql statement to prepare a list with salesman name : 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 - What types of constraints would you expect to check?
How are the luxury car competitors strategies similar : How are the luxury car competitors' strategies similar? In what manner are they different? Which company has the competitive advantage and why?
Write the switching expression for the burglar alarm : Write the switching expression for the burglar alarm that produces a logic I (rings a bell) when the safe is moved and the control switch is closed, or when the closet is opened after banking hours, or when the closet is opened with the control sw..
Explain are a and b complementary events : Are A and B independent events? Explain how you know.- Are A and B complementary events? Explain how you know.
Explain and illustrate one of the three portfolio approaches : Describe and illustrate one of the three portfolio approaches. Explain why a business would choose a low cost, differentiation, or speed-based strategy. Conduct a strategic analysis and choice for a multi-business company.

Reviews

inf1411802

3/15/2017 5:15:20 AM

I understand now that your work is certainly justified regardless of the cost and I will happily prescribe you all to my companions, family, kindred understudies, and colleagues. Keep up the considerable work!

inf1411802

3/15/2017 5:13:31 AM

Can you please assist me with this one...thx NENAD CIND110-WIN2017-Assignment 2.pdf If you send it in MS Word with screenshots where required, it will be great...thx. Just paid..thank you!

Write a Review

 

PL-SQL Programming Questions & Answers

  Describe all system privileges found in sql server

Describe all system privileges found in SQL Server

  Write an sql select statement that would re-organize result

Write a program in the language of your choice to calculate the sum of all the multiples of 3 or 5 below 1000. Include the source code and the calculated value in your answer.

  Perform some basic sql join commands

Perform the following from the SQLZoo site. In the labs at school, you can generate a .pdf by printing the page to the pseudo printer CutePDF Writer. If you use the Chrome browser, it has a built-in feature for creating PDFs.

  Create and populate the birthday-distribution table

The BIRTHDAY_DISTRIBUTION table consists of every day of the year, from January 1 to December 31. Create and populate the BIRTHDAY_DISTRIBUTION table by using the following SQL statements.

  Construct a query to show the expected payment date

Construct a query that will show the number of days that exist between the first invoice and last invoice, for each month, for each employee, using the DATEDIFF function. Be sure to provide the SQL script that will carry out this function.

  Write sql commands to implement profile

Write SQL commands which would implement this profile. Next, write query that would list profile, profile resource name, and limit value for the new profile.

  1 a table scan is reading every record from the table in a

1. a table scan is reading every record from the table in a sequential order to find the data that a query is looking

  How primary and foreign-key relationships are applied

At this point, you will add data to your database and validate that they loaded properly. In tabular format, include 3 rows for each table, making sure that the primary-key and foreign-key relationships are properly applied.

  List the course number for database system principles

List the course number for "Database System Principles". Set "Database System Principles" as the prerequisite for "Relational Database Systems". List all courses that have "Database System Principles" as prerequisite.

  Write a compete pl/sql program to display the staffno

Write a compete PL/SQL program to display the StaffNo,

  Difference between complete and differential backups

Explain the difference among the simple, full, and bulk-logged recovery models.

  Display customers table and its fields in sql

Display all the LastNames from the Customers table and any associated OrdersIDs from the Orders Table. Show the LastName even if they do not have any associated orders. Order the results by LastName in ascending order.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd