Using the data below, make SQl statements. Please label each question. Who are the employees that their
Fantastic news! We've Found the answer you've been seeking!
Question:
Using the data below, make SQl statements. Please label each question.
- Who are the employees that their emp_title contains the word freight? Display their full name and their title.
- Who are the employees who have been hired in the years of 2000 to 2010?
- How many products appear on each invoice? Display the inv_num, prod_SKU and an aliased suitable new column name.
- Show the employees who have issued any invoices: Display the inv_num, inv_date, employee first and last name.
- Show the employees (emp_num, first and last name) only once, if they were issued any invoices.
- What invoice has the highest amount? Display the inv_num and an alias name 'highest_amount'.
- Apply an increase of 15% in addition to the current Line Price: show the line_num and an alias name for calculated column 'Increased_price'.
- Write an SQL statement to change the phone number for employee 'Long Willard' to 245-8899.
Transcribed Image Text:
Line Table INV NUM LINE NUM PROD SKU LINE QTY LINE PRICE 104 6041-PBS 2 7.49 104 8523-UCB 1 21.59 6451-KGJ 1 6.99 6.59 8941-JFP 1 11.99 9324-PPG 2 6350-UPP 3 1838-421 3 1504-LVK 3 1074-VV3 1 7627+HI 1 6933-YOU 5 7158-4.JP 2 8726-ZNM 3 5642-IDA 2 5445-XOY 5 4 3503-FGI 3036-PCT 6249-NON 1 5 8937-WVA 5 104 105 106 106 106 106 107 107 108 108 108 109 109 109 109 109 109 110 110 110 110 110 111 1 2 83341 83347 83349 83359 83366 83371 83371 83372 83374 83378 3 1 1 2 83382 83385 4 1 2 1 2 3 1 2 3 4 5 6 1 2 3 4 S 83304 83308 83312 83314 83318 83321 83332 WILLARD 2455-201 3 5980-BME 1 5541HST 2 3610-RKR 5 8841-JFP 3 2553-FSW 5 BAKER CHAROLETTE DAVID PECK Employee Table EMP_NUM EMP FNAME EMP UNAME EMP EMAIL TAMARA MCDONALD LOVE CONNIE ROSALBA DARCIE ANGELINA FARMER LONG CHRISTINE CORTEZ WINN JENNIFFER SINGH QUINTIN WATTS BLEDSOE MATHEWS MATHEWS MERLE PHOEBE 12.49 12.59 21.99 23.29 11.59 8.99 ROXANE ROXANE CLAUDINE DAHL DARRON TILLEY FELICIA DUNHAM STELLA CONKLIN BRODERICK COLBERT 11.99 21.99 29.99 10.99 5.59 3.99 22.59 3.29 38.99 31.99 32.99 18.59 6.59 20.59 Invoice Table INV NUM INV_DATE CUST_CODE INV_TOTAL EMPLOYEE_ID 104 2017-01-30 88 105 2017-01-30 89 106 2017-01-30 90 107 108 109 110 111 112 113 114 115 116 117 118 GR628BE6665 119 120 121 122 123 124 127 2017-01-30 91 2017-01-30 92 2017-01-30 93 2017-01-31 94 2017-01-31 95 2017-01-31 96 2017-01-31 97 2017-01-31 98 99 2017-01-31 2017-01-31 2017-01-31 2017-01-31 2017-02-01 2017-02-01 2017-02-01 2017-02-01 2017-02-01 100 ESSESR525558 101 102 103 104 105 106 107 2017-02-01 108 2017-02-01 109 2017-02-01 110 2017-02-02 111 2010-10-20 T.MCDONA3@PRIMEPAINTS.COM 453-9342 C.LOVE 77@PRIMEPAINTS.COM 495-3082 R.BAKER9@PRIMEPAINTS.COM 632-8197 1984-12-15 2016-12-12 C.DAVID84@PRIMEPAINTS.COM 726-5086 1991-02-17 D.PECK4@PRIMEPAINTS.COM 680-6124 2011-05-03 A.FARMER86@PRIMEPAINTS.COM 734-7097 1993-09-08 W.LONG1@PRIMEPAINTS.COM 242-1979 2008-03-08 C.CORTEZ85@PRIMEPAINTS.COM 508-3269 1992-07-28 Q.WINN82@PRIMEPAINTS.COM 653-6753 1989-08-25 J.SINGH87@PRIMEPAINTS.COM 722-9727 1994-01-07 M.WATTS90@PRIMEPAINTS.COM 406-3691 1997-10-22 P.BLEDSO99 @PRIMEPAINTS.COM 686-7916 2006-08-14 R.MATHEW94@PRIMEPAINTS.COM 512-3569 2001-08-08 R.MATHEW94@PRIMEPAINTS.COM 512-3569 2001-08-08 C.DAHL90@PRIMEPAINTS.COM 326-1880 1997-10-29 2008-10-04 2012-12-31 D.TILLEY1@PRIMEPAINTS.COM 305-1308 F.DUNHAMS@PRIMEPAINTS.COM 255-9624 S.CONKLI80@PRIMEPAINTS.COM 511-5282 B.COLBER91@PRIMEPAINTS.COM 888-6311 43.56 6.59 1987-03-28 1998-11-25 165.19 34.88 496.38 196.28 327.66 422.63 507.43 449.24 278.93 163.95 261.83 24.99 372.46 243.80 47.07 154.71 29.98 321.20 81.94 197.98 58.15 175.90 83734 83734 84134 836-49 84134 83734 83792 84021 83820 84334 84420 83878 84392 84191 83820 83677 83649 83593 83906 84248 84364 83734 EMP PHONE EMP_HIREDATE EMP_TITLE ASSOCIATE SENIOR ASSOCIATE ASSOCIATE ASSOCIATE FREIGHT STOCKER BRAND ASSOCIATE BRAND ASSOCIATE HULL ASSOCIATE NULL SENIOR FREIGHTER SENIOR FREIGHTER GULL NULL SENIOR ASSOCIATE ASSOCIATE FREIGHT STOCKER FREIGHT STOCKER DRIVER FREIGHT STOCKER ASSOCIATE ASSOCIATE FREIGHT STOCKER 83509 83878 EMP_COMM NULL NULL HULL NULL NULL NULL NULL HULL HULL NULL HULL HULL HULL HALL HULL DEPT_NUM 400 250 300 280 500 280 280 250 500 500 300 400 500 500 550 500 600 400 500 Line Table INV NUM LINE NUM PROD SKU LINE QTY LINE PRICE 104 6041-PBS 2 7.49 104 8523-UCB 1 21.59 6451-KGJ 1 6.99 6.59 8941-JFP 1 11.99 9324-PPG 2 6350-UPP 3 1838-421 3 1504-LVK 3 1074-VV3 1 7627+HI 1 6933-YOU 5 7158-4.JP 2 8726-ZNM 3 5642-IDA 2 5445-XOY 5 4 3503-FGI 3036-PCT 6249-NON 1 5 8937-WVA 5 104 105 106 106 106 106 107 107 108 108 108 109 109 109 109 109 109 110 110 110 110 110 111 1 2 83341 83347 83349 83359 83366 83371 83371 83372 83374 83378 3 1 1 2 83382 83385 4 1 2 1 2 3 1 2 3 4 5 6 1 2 3 4 S 83304 83308 83312 83314 83318 83321 83332 WILLARD 2455-201 3 5980-BME 1 5541HST 2 3610-RKR 5 8841-JFP 3 2553-FSW 5 BAKER CHAROLETTE DAVID PECK Employee Table EMP_NUM EMP FNAME EMP UNAME EMP EMAIL TAMARA MCDONALD LOVE CONNIE ROSALBA DARCIE ANGELINA FARMER LONG CHRISTINE CORTEZ WINN JENNIFFER SINGH QUINTIN WATTS BLEDSOE MATHEWS MATHEWS MERLE PHOEBE 12.49 12.59 21.99 23.29 11.59 8.99 ROXANE ROXANE CLAUDINE DAHL DARRON TILLEY FELICIA DUNHAM STELLA CONKLIN BRODERICK COLBERT 11.99 21.99 29.99 10.99 5.59 3.99 22.59 3.29 38.99 31.99 32.99 18.59 6.59 20.59 Invoice Table INV NUM INV_DATE CUST_CODE INV_TOTAL EMPLOYEE_ID 104 2017-01-30 88 105 2017-01-30 89 106 2017-01-30 90 107 108 109 110 111 112 113 114 115 116 117 118 GR628BE6665 119 120 121 122 123 124 127 2017-01-30 91 2017-01-30 92 2017-01-30 93 2017-01-31 94 2017-01-31 95 2017-01-31 96 2017-01-31 97 2017-01-31 98 99 2017-01-31 2017-01-31 2017-01-31 2017-01-31 2017-02-01 2017-02-01 2017-02-01 2017-02-01 2017-02-01 100 ESSESR525558 101 102 103 104 105 106 107 2017-02-01 108 2017-02-01 109 2017-02-01 110 2017-02-02 111 2010-10-20 T.MCDONA3@PRIMEPAINTS.COM 453-9342 C.LOVE 77@PRIMEPAINTS.COM 495-3082 R.BAKER9@PRIMEPAINTS.COM 632-8197 1984-12-15 2016-12-12 C.DAVID84@PRIMEPAINTS.COM 726-5086 1991-02-17 D.PECK4@PRIMEPAINTS.COM 680-6124 2011-05-03 A.FARMER86@PRIMEPAINTS.COM 734-7097 1993-09-08 W.LONG1@PRIMEPAINTS.COM 242-1979 2008-03-08 C.CORTEZ85@PRIMEPAINTS.COM 508-3269 1992-07-28 Q.WINN82@PRIMEPAINTS.COM 653-6753 1989-08-25 J.SINGH87@PRIMEPAINTS.COM 722-9727 1994-01-07 M.WATTS90@PRIMEPAINTS.COM 406-3691 1997-10-22 P.BLEDSO99 @PRIMEPAINTS.COM 686-7916 2006-08-14 R.MATHEW94@PRIMEPAINTS.COM 512-3569 2001-08-08 R.MATHEW94@PRIMEPAINTS.COM 512-3569 2001-08-08 C.DAHL90@PRIMEPAINTS.COM 326-1880 1997-10-29 2008-10-04 2012-12-31 D.TILLEY1@PRIMEPAINTS.COM 305-1308 F.DUNHAMS@PRIMEPAINTS.COM 255-9624 S.CONKLI80@PRIMEPAINTS.COM 511-5282 B.COLBER91@PRIMEPAINTS.COM 888-6311 43.56 6.59 1987-03-28 1998-11-25 165.19 34.88 496.38 196.28 327.66 422.63 507.43 449.24 278.93 163.95 261.83 24.99 372.46 243.80 47.07 154.71 29.98 321.20 81.94 197.98 58.15 175.90 83734 83734 84134 836-49 84134 83734 83792 84021 83820 84334 84420 83878 84392 84191 83820 83677 83649 83593 83906 84248 84364 83734 EMP PHONE EMP_HIREDATE EMP_TITLE ASSOCIATE SENIOR ASSOCIATE ASSOCIATE ASSOCIATE FREIGHT STOCKER BRAND ASSOCIATE BRAND ASSOCIATE HULL ASSOCIATE NULL SENIOR FREIGHTER SENIOR FREIGHTER GULL NULL SENIOR ASSOCIATE ASSOCIATE FREIGHT STOCKER FREIGHT STOCKER DRIVER FREIGHT STOCKER ASSOCIATE ASSOCIATE FREIGHT STOCKER 83509 83878 EMP_COMM NULL NULL HULL NULL NULL NULL NULL HULL HULL NULL HULL HULL HULL HALL HULL DEPT_NUM 400 250 300 280 500 280 280 250 500 500 300 400 500 500 550 500 600 400 500
Expert Answer:
Answer rating: 100% (QA)
Based on the provided image containing the Line Table Invoice Table and Employee Table here are the ... View the full answer
Related Book For
Systems analysis and design
ISBN: 978-0136089162
8th Edition
Authors: kenneth e. kendall, julie e. kendall
Posted Date:
Students also viewed these databases questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Read the case study "Southwest Airlines," found in Part 2 of your textbook. Review the "Guide to Case Analysis" found on pp. CA1 - CA11 of your textbook. (This guide follows the last case in the...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
Find the equation for the lower half of the circle x + (y-8) = 7. Put the equation in the form y = g(x), and then enter g(x) into the answer box below. Enter your answer as a symbolic function of x,...
-
The plunger of a solenoid is connected to a spring. The spring force is given by f = K0 (0.9a x), where x is the air-gap length. The inductance of the solenoid is of the form L = L0 (1 x/a), and...
-
Indicate (by letter) the way each of the items listed below should be reported in a balance sheet at December 31, 2016. Reporting Method N. Not r C. Current liability L. Long-term liability D....
-
Suppose you raise this book (inertia \(m=3.4 \mathrm{~kg}\) ) from the floor to your desk, \(1.0 \mathrm{~m}\) above the floor. (a) Does the gravitational potential energy of the Earth-book system...
-
(Multiple Choice) 1. What are the components of the net position section of a governmental hospitals balance sheet? a. Unrestricted; temporarily restricted; permanently restricted b. Assets limited...
-
6. If the tangent to the curve y = x at the point P(t, t) meets the curve again at Q, then the ordinate of the point which divides PQ internally in the ratio 1:2 is: (a) 0 7. (b)-t The system of...
-
It is the end of 2019, and you have been asked to create financial statements for the year. Your manager has given you the trial balance on the left.
-
The internal energy of gas is given by U = 1.5 PV. It expands from 100 to 200 cm against a constant pressure of 1.0 x 10 Pa. The heat absorbed (in KJ) by the gas in the process is Backspace 7 8 9 4 5...
-
The primary difference between PERT and CPM is that a. PERT uses one time estimate. b. CPM has three time estimates. c. PERT has three time estimates. d. with CPM, it is assumed that all activities...
-
Simulation is relatively straightforward and flexible. It can be used to compare many different scenarios side by side. a. True b. False
-
What are the three types of route structure? Define or illustrate each.
-
A probability distribution has been developed, and the probability of 2 arrivals in the next hour is 0.20. A random number interval is to be assigned to this. Which of the following would not be an...
-
Network models such as PERT and CPM are used a. to plan large and complex projects. b. to schedule large and complex projects. c. to monitor large and complex projects. d. to control large and...
-
output: full name: Rachelle Green Geller Give a letter: e The number of letter e in your name is 6. Letter Counter Write a program that will let the user input their full name, including the whole...
-
The first law of thermodynamics is sometimes whimsically stated as, You cant get something for nothing, and the second law as, You cant even break even. Explain how these statements could be...
-
What is the first step to take in developing a decision table?
-
List three reasons for creating a logical data flow diagram.
-
What is a context-level data flow diagram? Contrast it to a level 0 DFD.
-
For coordinates \(\left(x^{1}, x^{2} ight)\) and metric \(g=\operatorname{diag}\left(g_{11}, g_{22} ight)\), the Gaussian curvature is For a sphere with coordinates defined in the following figure,...
-
Consider the holonomic basis defined in Box 26.1 . Using that the tangent vector for a curve can be written \(t=t^{\mu} e_{\mu}=\left(d x^{\mu} / d \lambda ight) e_{\mu}\), show that Thus, \(g_{\mu...
-
The Lie bracket of vector fields \(A\) and \(B\) is defined as their commutator, \([A, B]=\) \(A B-B A\). The Lie bracket of two basis vectors vanishes for a coordinate basis but not for a...
Study smarter with the SolutionInn App