Assignment Content
Don't use plagiarized sources. Get Your Custom Essay on
Select and Update Data in Related Tables
Just from $13/Page
Top of Form
Running reports on existing data and updating data records are very common tasks in the industry.
For this assignment, you will begin with a database containing several pre-populated tables.
Download and unzip the Microsoft® Access® database (ZIP file) available on the Cengage’s website. The zip file is attached
Open the database in Microsoft® Access® and explore the tables it contains by following this path:
/folderYouNameWhenYouUnzip/1305627482_526407/Student/Ch07-Intro-to-SQL/Databases/Ch07_SaleCo.mdb
I have attached the Select and Update Data in Related Tables document and follow the instructions.
Your completed assignment will consist of one document and two screenshots placed in one document (one screenshot showing the results of the “before” SELECT statement and a second showing the results of the “after” SELECT statement).
DBM/380 v14
Title
ABC/123 vX
Page 2 of 2
Select and Update Data in Relation Tables
1. Begin with the populated Microsoft® Access® database you downloaded from the Cengage’s website. (If you have chosen not to use Microsoft® Access®, create and populate an identical database using the MySQL, Oracle®, or SQLServer statements provided.)
2. Select the invoice number, customer code, customer last name, customer first name, and invoice date from the appropriate tables (using the appropriate SQL join keyword), ordering the results by invoice number.
3. Take a screenshot of your results. Name your screenshot: “DBM380Week4_YourLastNameYourFirstName_BEFORE ”
4. Update the database so that:
a. The customer incorrectly named “Dunne” is named “Dipplehoffer.”
b. All invoice dates reflect the year 2018. Note: Date values are handled differently than numeric (100) or string (‘Ralph’) values. For help with the date format in Microsoft® Access®, read “
Create and run an update query
” from Microsoft®.”
5. Select the invoice number, customer code, customer last name, customer first name, and invoice date from the appropriate tables (using the appropriate SQL join keyword), ordering the results by customer last name.
6. Take a screenshot of your results. Name your screenshot: “DBM380Week4_YourLastNameYourFirstName_AFTER ”
7. In a Microsoft® Word document, type your answers to the following questions related to this assignment:
a. Database design. Briefly describe the business scenario this database was designed to model.
b. Entity-relationship diagram. Describe at least two relationships implemented in the database that would have been modeled in the ERD. Be sure to label each relationship that you list appropriately (e.g., “primary key” or “foreign key”).
c. Normalization. Were the tables in this database normalized? How do you know?
d.
Management. Data updates are destructive; that is, they can alter existing data in a way that makes reconstituting the original data difficult or even impossible. What strategies, or safeguards, did you use to mitigate the unintentional destruction of data while you were completing this assignment? For example, did you select data prior to updating it to create a fall-back record? Did you back up the database prior to updating it?
8. Prepare to turn in your assignment by zipping your two screenshots and the Microsoft® Word document into a single file named: “DBM380Week4_IndividualAsssignment_YourLastNameYourFirstName.zip”
Copyright© 2018 by University of Phoenix. All rights reserved.
Copyright© 20XX by University of Phoenix. All rights reserved.
|
|
| Customer ID |
|
|
|
|
| Order number |
|
|
| Order date |
| Customer number |
|
| Customer name |
|
| Customer address |
|
| Tel |
|
|
| Sales agent |
|
| Sales agent number |
| Product No |
|
|
| Description |
|
|
| Quantity |
| Unit Price |
|
|
|
| John D |
| 1
|
|
|
|
|
|
|
|
|
|
| 2 |
|
| 3 |
|
|
| 4 |
|
|
| 12 |
/
|
|
| 5 |
/
|
|
|
|
|
| 18 |
|
|
| 9876 |
|
|
| John Doe |
|
|
| 456 Bishop Street Honolulu, Hawaii 96813 |
|
|
| (808)8650990 |
|
|
|
|
|
|
| Lloyd Johnson |
|
|
|
|
| S99 |
12
|
| Boots |
2
|
| ¤ 6.00 |
John D
| 12
|
|
|
| 34 |
|
| 12/5/18 |
9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990 Lloyd Johnson S99 18
|
|
|
|
|
| Sunglasses |
3
|
|
|
|
|
| ¤ 12.00 |
|
|
| John W |
|
|
|
|
| 1326 |
|
| 12/8/18 |
|
| 5432 |
|
| John Walker |
|
| 124 Eugene, Oregon |
|
| (808)5378942 |
|
|
|
| James Milner |
|
|
| W8 |
5
|
|
| Waistcoat |
4 ¤ 12.00
|
|
| Peter G |
|
|
|
|
| 1578 |
|
| 12/9/18 |
|
| 1896 |
|
| Peter Griffin |
|
| 128 Eugene, Orlando |
|
| (808)4548415 |
|
|
|
|
|
|
|
| Peter Parker |
|
|
|
|
|
| A12 |
18 Sunglasses 2
|
|
| ¤ 8.00 |
|
|
| Sam S |
|
|
|
|
| 5678 |
|
| 12/7/18 |
|
| 5431 |
|
| Sam Smith |
|
| 789 Bishop Street Honolulu, Hawaii 96813 |
|
| (808)9876543 |
Peter Parker A12 34
|
|
| Scarf |
2
|
|
| ¤ 5.00 |
Customer ID
| Customer Number |
Customer name Customer address Tel
John D 9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990
John W 5432 John Walker 124 Eugene, Oregon (808)5378942
Peter G 1896 Peter Griffin 128 Eugene, Orlando (808)4548415
Sam S 5431 Sam Smith 789 Bishop Street Honolulu, Hawaii 96813 (808)9876543
Customer ID Order number
John D
|
|
|
|
|
| 1234 |
John W 1326
Peter G 1578
Sam S 5678
Order number Order date Sales agent Sales agent number
|
| Product number |
Description Quantity
| Unit price |
1234 12/5/18 Lloyd Johnson S99 12 Boots 2 ¤ 6.00
1234 12/5/18 Lloyd Johnson S99 18 Sunglasses 3 ¤ 12.00
1326 12/8/18 James Milner W8 5 Waistcoat 4 ¤ 12.00
1578 12/9/18 Peter Parker A12 18 Sunglasses 2 ¤ 8.00
5678 12/7/18 Peter Parker A12 34 Scarf 2 ¤ 5.00
| Sales Agent |
Sales Agent number |
James Milner W8
Lloyd Johnson S99
Peter Parker A12
Order number Order date Sales agent Product number Description Quantity Unit price
1234
|
| 12/5/2018 |
Lloyd Johnson 12 Boots 2 ¤ 6.00
1234 12/5/2018 Lloyd Johnson 18 Sunglasses 3 ¤ 12.00
1326
| 12/8/2018 |
James Milner 5 Waistcoat 4 ¤ 12.00
1578
| 12/9/2018 |
Peter Parker 18 Sunglasses 2 ¤ 8.00
5678
| 12/7/2018 |
Peter Parker 34 Scarf 2 ¤ 5.00
Order number Order date Customer number Customer name Customer address Tel Sales agent Sales agent number Product No Description Quantity Unit Price
1234 12/5/18 9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990 Lloyd Johnson S99
| 12,18 |
Boots, Sunglasses |
2,3 |
¤ 18.00 |
1326 12/8/18 5432 John Walker 124 Eugene, Oregon (808)5378942 James Milner W8 5 Waistcoat 4 ¤ 12.00
1578 12/9/18 1896 Peter Griffin 128 Eugene, Orlando (808)4548415 Peter Parker A12 18 Sunglasses 2 ¤ 8.00
5678 12/7/18 5431 Sam Smith 789 Bishop Street Honolulu, Hawaii 96813 (808)9876543 Peter Parker A12 34 Scarf 2 ¤ 5.00
|
|
|
|
|
|
|
|
|
| |
| CUS_CO
|
|
|
|
|
| D |
|
|
| E |
CUS_L
|
|
|
|
|
| N |
|
| A |
| M |
E
CUS_
| F |
NAME
CUS_INITIAL |
CUS_A
|
|
|
| R |
EACODE
CUS_
|
|
|
| P |
HONE
CUS_
| B |
ALANCE
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 1 |
0
0
|
|
|
|
| 10 |
Ramas |
Alfred |
A
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 6 |
1
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 8 |
| 4 |
4-
|
|
|
|
|
|
|
|
|
|
|
| 2 |
57
|
|
| 3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 0.0 |
|
|
|
|
|
|
|
|
| 100 |
1
1
Dunne |
Leona |
|
|
|
|
| K |
|
| 7
| 13 |
8
|
| 9 |
4-
|
|
|
|
|
|
|
|
|
|
| 12 |
38
0.0
|
| 10012 |
|
|
|
|
|
|
|
|
| Smith |
Kathy |
|
| W |
6
|
|
|
|
|
|
|
|
|
| 15 |
894-2285 |
345.86 |
| 10013 |
Olowski |
|
| Paul |
F
|
|
|
|
| 615 |
894-2
|
|
|
|
|
|
|
|
| 18 |
0
536.
|
|
|
|
|
|
|
| 75 |
| 100
|
| 14 |
Orlando |
Myron |
615
222-
| 16 |
72
0.0
|
| 10015 |
O’Brian |
Amy |
B 713
442-3381 |
0.0
| 10016 |
Brown |
James |
|
|
|
|
| G |
615
297-1228 |
221.
| 19 |
| 10017 |
|
| Williams |
|
|
|
|
|
| George |
615
290-
|
|
|
| 25 |
56
768.93 |
|
| 10018 |
Farriss |
|
| Anne |
G 713
382-7185 |
216.55 |
| 10019 |
Smith
Olette |
K 615
297-3809 |
0.0
|
| EMP_NUM |
| EMP_TITLE |
| EMP_LNAME |
| EMP_FNAME |
| EMP_INITIAL |
| EMP_DOB |
| EMP_HIRE_DATE |
| EMP_AREACODE |
| EMP_PHONE |
EMP_MGR |
100
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Mr. |
| Kolmycz |
George D
| 6/15/42 |
| 3/15/85 |
615
|
|
|
|
|
| 32 |
4-5456
|
| 101 |
|
|
|
|
|
|
| Ms. |
| Lewis |
| Rhonda |
G
| 3/19/65 |
| 4/25/86 |
615
| 324-4472 |
100
|
| 102 |
Mr.
| VanDam |
| Rhett |
|
|
|
|
| 11 |
/14/58
| 12/
|
|
|
| 20 |
/90
|
|
|
|
|
|
|
|
|
|
| 901 |
| 675-8993 |
100
|
| 103 |
Ms.
| Jones |
Anne M
| 10/16/74 |
| 8/28/94 |
615
| 898-3456 |
100
|
| 104 |
Mr.
| Lange |
| John |
P
| 11/8/71 |
| 10/20/94 |
901
|
|
|
|
| 50 |
4-4
|
|
| 43 |
0
|
|
|
|
|
|
| 105 |
105 Mr. Williams
|
| Robert |
D
| 3/14/75 |
| 11/8/98 |
615
| 890-3220 |
|
| 106 |
|
|
|
|
| Mrs. |
Smith
| Jeanine |
K
| 2/12/68 |
| 1/5/89 |
615
| 324-7883 |
105
|
| 107 |
Mr.
| Diante |
| Jorge |
D
| 8/21/74 |
| 7/2/94 |
615
| 890-4567 |
105
|
|
|
|
|
| 108 |
Mr.
| Wiesenbach |
Paul R
| 2/14/66 |
| 11/18/92 |
615
| 897-4
| 35 |
8
|
| 109 |
Mr. Smith George K
| 6/18/61 |
| 4/14/89 |
901
| 504-3339 |
108
|
| 110 |
Mrs.
| Genkazi |
| Leighla |
W
| 5/19/70 |
| 12/1/90 |
901
| 569-0093 |
108
|
| 111 |
Mr.
| Washington |
| Rupert |
E
| 1/3/66 |
| 6/21/93 |
615
| 890-4925 |
105
|
| 112 |
Mr.
| Johnson |
| Edward |
E
| 5/14/61 |
| 12/1/83 |
615
| 898-4387 |
100
|
| 113 |
Ms.
|
| Smythe |
| Melanie |
P
| 9/15/70 |
| 5/11/99 |
615
| 324-9006 |
105
|
| 114 |
Ms.
| Brandon |
| Marie |
G
| 11/2/56 |
| 11/15/79 |
901
| 882-0845 |
108
|
| 115 |
Mrs.
| Saranda |
| Hermine |
R
| 7/25/72 |
4/
|
|
|
|
|
|
|
|
| 23 |
/93
615
| 324-5505 |
105
|
| 116 |
Mr. Smith George A
| 11/8/65 |
| 12/10/88 |
615
| 890-2984 |
108
EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE_DATE
| EMP_
|
|
|
|
| Y |
EARS
EMP_AREACODE EMP_PHONE
100 Mr. Kolmycz George D 6/15/42 3/15/85 18 615 324-5456
101 Ms. Lewis Rhonda G 3/19/65 4/25/86 16 615 324-4472
102 Mr. VanDam Rhett 11/14/58 12/20/90 12 901 675-8993
103 Ms. Jones Anne M 10/16/74 8/28/94 8 615 898-3456
104 Mr. Lange John P 11/8/71 10/20/94 8 901 504-4430
105 Mr. Williams Robert D 3/14/75 11/8/98 4 615 890-3220
106 Mrs. Smith Jeanine K 2/12/68 1/5/89 14 615 324-7883
107 Mr. Diante Jorge D 8/21/74 7/2/94 8 615 890-4567
108 Mr. Wiesenbach Paul R 2/14/66 11/18/92 10 615 897-4358
109 Mr. Smith George K 6/18/61 4/14/89 13 901 504-3339
110 Mrs. Genkazi Leighla W 5/19/70 12/1/90 12 901 569-0093
111 Mr. Washington Rupert E 1/3/66 6/21/93 9 615 890-4925
112 Mr. Johnson Edward E 5/14/61 12/1/83 19 615 898-4387
113 Ms. Smythe Melanie P 9/15/70 5/11/99 3 615 324-9006
114 Ms. Brandon Marie G 11/2/56 11/15/79 23 901 882-0845
115 Mrs. Saranda Hermine R 7/25/72
| 4/23/93 |
9 615 324-5505
116 Mr. Smith George A 11/8/65 12/10/88 14 615 890-2984
|
| INV_NUMBER |
CUS_CODE
INV_DATE |
1001
|
| 10014 |
|
| 1/16/16 |
|
| 1002 |
|
| 10011 |
1/16/16
|
|
|
| 1003 |
10012 1/16/16
|
|
| 1004 |
10011
|
|
|
|
|
|
|
| 1/17/16 |
|
| 1005 |
10018 1/17/16
|
|
|
|
| 1006 |
10014 1/17/16
|
|
| 1007 |
10015 1/17/16
|
|
|
| 1008 |
10011 1/17/16
INV_NUMBER
| LINE_NUMBER |
|
|
|
|
| P_CODE |
LINE_UNITS |
LINE_PRICE |
1001 1
|
|
|
|
|
|
|
|
| 13-Q2/P2 |
|
|
|
|
|
|
|
| 1.0 |
1
|
|
|
|
|
|
|
| 4.99 |
1001 2
|
|
|
|
|
|
|
|
| 23109-HB |
1.0
|
|
|
|
|
|
| 9.95 |
1002 1
|
|
|
|
|
|
|
| 54778-2T |
|
| 2.0 |
4.99
1003 1
|
|
|
|
|
| 2238/QPD |
1.0
|
|
|
|
| 38.95 |
1003 2
|
|
|
|
|
| 1546-QQ2 |
1.0
|
|
|
|
| 39.95 |
1003 3 13-Q2/P2
|
| 5.0 |
|
|
|
|
|
|
| 14.99 |
1004 1 54778-2T
|
|
| 3.0 |
4.99
1004 2 23109-HB 2.0 9.95
1005 1
|
|
|
|
|
|
| PVC23DRT |
12.0 |
|
|
|
|
|
| 5.87 |
1006 1
|
|
|
|
|
| SM-18277 |
3.0
|
|
|
|
| 6.99 |
1006 2
|
|
|
|
|
| 2232/QTY |
1.0
|
|
|
|
| 109.92 |
1006 3 23109-HB 1.0 9.95
1006 4
|
|
|
|
|
| 89-WRE-Q |
1.0
|
|
|
|
| 256.99 |
1007 1 13-Q2/P2 2.0 14.99
1007 2 54778-2T 1.0 4.99
1008 1 PVC23DRT 5.0 5.87
1008 2
|
|
|
|
|
| WR3/TT3 |
3.0
|
|
|
|
| 119.95 |
1008 3 23109-HB 1.0 9.95
| PART_CODE |
PART_DESCRIPT |
PART_PRICE |
|
|
|
|
|
| V_CODE |
|
|
|
|
|
| 11QER/31 |
|
|
|
|
| Power painter, 15 psi., 3-nozzle |
|
|
|
|
| 109.99 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 25595 |
13-Q2/P2
|
|
|
|
|
| 7.25-in. pwr. saw blade |
14.99
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 21344 |
|
|
|
|
| 14-Q1/L3 |
|
|
|
| 9.00-in. pwr. saw blade |
|
|
|
| 17.49 |
21344
1546-QQ2
|
|
|
|
| Hrd. cloth, 1/4-in., 2×50 |
39.95
|
|
|
|
|
|
|
|
|
| 23119 |
|
|
|
|
| 1558-QW1 |
|
|
|
| Hrd. cloth, 1/2-in., 3×50 |
|
|
|
| 43.99 |
23119
2232/QTY
|
|
|
|
| B&D jigsaw, 12-in. blade |
109.92
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 24288 |
|
|
|
|
| 2232/QWE |
|
|
|
| B&D jigsaw, 8-in. blade |
|
|
|
| 99.87 |
24288
2238/QPD
|
|
|
|
| B&D cordless drill, 1/2-in. |
38.95 25595
23109-HB
|
|
|
|
| Claw hammer |
9.95
|
|
|
|
|
|
|
|
|
| 21225 |
|
|
|
|
| 23114-AA |
|
|
|
| Sledge hammer, 12 lb. |
|
|
|
| 14.4 |
54778-2T
|
|
|
|
| Rat-tail file, 1/8-in. fine |
4.99 21344
89-WRE-Q
|
|
|
|
| Hicut chain saw, 16 in. |
256.99 24288
PVC23DRT
|
|
|
|
| PVC pipe, 3.5-in., 8-ft |
5.87
SM-18277
|
|
|
|
| 1.25-in. metal screw, 25 |
6.99 21225
|
|
|
|
| SW-23116 |
|
|
|
| 2.5-in. wd. screw, 50 |
|
|
|
| 8.45 |
|
|
|
|
| 21231 |
WR3/TT3
|
|
|
|
| Steel matting, 4’x8’x1/6″, .5″ mesh |
119.95 25595
P_CODE
|
|
|
|
| P_DESCRIPT |
|
|
|
| P_INDATE |
|
|
|
| P_QOH |
|
|
|
| P_MIN |
|
|
|
| P_PRICE |
|
|
|
| P_DISCOUNT |
V_CODE
11QER/31 Power painter, 15 psi., 3-nozzle
|
|
| 11/3/15 |
8 5 109.99 0.0 25595
13-Q2/P2 7.25-in. pwr. saw blade
|
|
| 12/13/15 |
32 15 14.99
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 0.05 |
21344
14-Q1/L3 9.00-in. pwr. saw blade
|
|
| 11/13/15 |
18 12 17.49 0.0 21344
1546-QQ2 Hrd. cloth, 1/4-in., 2×50
|
|
|
|
|
|
|
| 1/15/16 |
15 8 39.95 0.0 23119
1558-QW1 Hrd. cloth, 1/2-in., 3×50 1/15/16 23 5 43.99 0.0 23119
2232/QTY B&D jigsaw, 12-in. blade
|
|
|
| 12/30/15 |
8 5 109.92 0.05 24288
2232/QWE B&D jigsaw, 8-in. blade
|
|
|
| 12/24/15 |
6 5 99.87 0.05 24288
2238/QPD B&D cordless drill, 1/2-in.
|
|
|
|
|
|
|
| 1/20/16 |
12 5 38.95 0.05 25595
23109-HB Claw hammer 1/20/16 23 10 9.95
|
|
|
|
|
|
|
| 0.1 |
21225
23114-AA Sledge hammer, 12 lb.
|
|
| 1/2/16 |
8 5 14.4 0.05
54778-2T Rat-tail file, 1/8-in. fine
|
|
|
| 12/15/15 |
43 20 4.99 0.0 21344
89-WRE-Q Hicut chain saw, 16 in.
|
|
| 2/7/16 |
11 5 256.99 0.05 24288
PVC23DRT PVC pipe, 3.5-in., 8-ft
|
|
| 2/20/16 |
|
|
| 188 |
75 5.87 0.0
SM-18277 1.25-in. metal screw, 25
|
|
| 3/1/16 |
|
|
| 172 |
75 6.99 0.0 21225
SW-23116 2.5-in. wd. screw, 50
|
|
| 2/24/16 |
|
|
| 237 |
100 8.45 0.0 21231
WR3/TT3 Steel matting, 4’x8’x1/6″, .5″ mesh 1/17/16 18 5 119.95 0.1 25595
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
|
| P_SALECODE |
11QER/31 Power painter, 15 psi., 3-nozzle 11/3/15 8 5 109.99 0.0 25595
13-Q2/P2 7.25-in. pwr. saw blade 12/13/15 32 15 14.99 0.05 21344
14-Q1/L3 9.00-in. pwr. saw blade 11/13/15 18 12 17.49 0.0 21344
1546-QQ2 Hrd. cloth, 1/4-in., 2×50 1/15/16 15 8 39.95 0.0 23119 2
1558-QW1 Hrd. cloth, 1/2-in., 3×50 1/15/16 23 5 43.99 0.0 23119
2232/QTY B&D jigsaw, 12-in. blade 12/30/15 8 5 109.92 0.05 24288 1
2232/QWE B&D jigsaw, 8-in. blade 12/24/15 6 5 99.87 0.05 24288 1
2238/QPD B&D cordless drill, 1/2-in. 1/20/16 12 5 38.95 0.05 25595
23109-HB Claw hammer 1/20/16 23 10 9.95 0.1 21225
23114-AA Sledge hammer, 12 lb. 1/2/16 8 5 14.4 0.05
54778-2T Rat-tail file, 1/8-in. fine 12/15/15 43 20 4.99 0.0 21344
89-WRE-Q Hicut chain saw, 16 in. 2/7/16 11 5 256.99 0.05 24288
PVC23DRT PVC pipe, 3.5-in., 8-ft 2/20/16 188 75 5.87 0.0
SM-18277 1.25-in. metal screw, 25 3/1/16 172 75 6.99 0.0 21225
SW-23116 2.5-in. wd. screw, 50 2/24/16 237 100 8.45 0.0 21231
WR3/TT3 Steel matting, 4’x8’x1/6″, .5″ mesh 1/17/16 18 5 119.95 0.1 25595
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE P_SALECODE
11QER/31 Power painter, 15 psi., 3-nozzle 11/3/15 8 5 109.99 0.0 25595 2
13-Q2/P2 7.25-in. pwr. saw blade 12/13/15 32 15 14.99 0.05 21344 2
14-Q1/L3 9.00-in. pwr. saw blade 11/13/15 18 12 17.49 0.0 21344 2
1546-QQ2 Hrd. cloth, 1/4-in., 2×50 1/15/16 15 8 39.95 0.0 23119 2
1558-QW1 Hrd. cloth, 1/2-in., 3×50 1/15/16 23 5 43.99 0.0 23119
2232/QTY B&D jigsaw, 12-in. blade 12/30/15 8 5 109.92 0.05 24288 1
2232/QWE B&D jigsaw, 8-in. blade 12/24/15 6 5 99.87 0.05 24288 2
2238/QPD B&D cordless drill, 1/2-in. 1/20/16 12 5 38.95 0.05 25595 1
23109-HB Claw hammer 1/20/16 23 10 9.95 0.1 21225 1
23114-AA Sledge hammer, 12 lb. 1/2/16 8 5 14.4 0.05
54778-2T Rat-tail file, 1/8-in. fine 12/15/15 43 20 4.99 0.0 21344 2
89-WRE-Q Hicut chain saw, 16 in. 2/7/16 11 5 256.99 0.05 24288 1
PVC23DRT PVC pipe, 3.5-in., 8-ft 2/20/16 188 75 5.87 0.0
SM-18277 1.25-in. metal screw, 25 3/1/16 172 75 6.99 0.0 21225
SW-23116 2.5-in. wd. screw, 50 2/24/16 237 100 8.45 0.0 21231
WR3/TT3 Steel matting, 4’x8’x1/6″, .5″ mesh 1/17/16 18 5 119.95 0.1 25595 1
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
11QER/31 Power painter, 15 psi., 3-nozzle
|
| 12/3/15 |
8 5 109.99 0.0 25595
13-Q2/P2 7.25-in. pwr. saw blade
|
|
| 1/13/16 |
32 15 14.99 0.05 21344
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
| P_MIN_ORDER |
P_REORDER |
11QER/31 Power painter, 15 psi., 3-nozzle 12/3/15 8 5 109.99 0.0 25595 25
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| false |
13-Q2/P2 7.25-in. pwr. saw blade 1/13/16 32 15 14.99 0.05 21344 50 false
14-Q1/L3 9.00-in. pwr. saw blade 1/13/16 18 12 17.49 0.0 21344 50 false
1546-QQ2 Hrd. cloth, 1/4-in., 2×50 12/15/15 15 8 39.95 0.0 23119 35 false
1558-QW1 Hrd. cloth, 1/2-in., 3×50 1/15/16 23 5 43.99 0.0 23119 25 false
2232/QTY B&D jigsaw, 12-in. blade 12/30/15 8 5 109.92 0.05 24288 15 false
2232/QWE B&D jigsaw, 8-in. blade
| 1/24/16 |
6 5 99.87 0.05 24288 15 false
2238/QPD B&D cordless drill, 1/2-in.
| 11/20/15 |
12 5 38.95 0.05 25595 12 false
23109-HB Claw hammer 1/20/16 23 10
| 5.95 |
0.1 21225 25 false
23114-AA Sledge hammer, 12 lb.
| 2/2/16 |
8 5 14.4 0.05 12 false
54778-2T Rat-tail file, 1/8-in. fine 1/15/16 43 20 4.99 0.0 21344 25 false
89-WRE-Q Hicut chain saw, 16 in.
| 12/7/15 |
11 5 256.99 0.05 24288 10 false
PVC23DRT PVC pipe, 3.5-in., 8-ft 1/20/16 188 75 5.87 0.0 50 false
SM-18277 1.25-in. metal screw, 25
| 12/29/15 |
172 75 6.99 0.0 21225 50 false
SW-23116 2.5-in. wd. screw, 50 12/24/15 237 100 8.45 0.0 21231 100 false
WR3/TT3 Steel matting, 4’x8’x1/6″, .5″ mesh 1/17/16 18 5 119.95 0.1 25595 10 false
V_CODE
| V_NAME |
V_CONTACT |
V_AREACODE |
V_PHONE |
V_STATE |
V_ORDER |
21225
| Bryson, Inc. |
Smithson |
615
223-3234 |
|
|
|
| TN |
Y
| 21226 |
SuperLoo, Inc. |
Flushing |
|
| 904 |
215-8995 |
|
| FL |
N
21231
| D&E Supply |
Singh |
615
228-3245 |
TN Y
21344
| Gomez Bros. |
Ortega |
615
889-2546 |
KY |
N
| 22567 |
Dome Supply |
Smith 901
678-1419 |
| GA |
N
23119
| Randsets Ltd. |
Anderson |
901
678-3998 |
GA Y
| 24004 |
Brackman Bros. |
Browning |
615
228-1410 |
TN N
24288
| ORDVA, Inc. |
Hakford |
615
898-1234 |
TN Y
| 25443 |
B&K, Inc. |
Smith 904
227-0093 |
FL N
| 25501 |
Damal Supplies |
Smythe 615
890-3529 |
TN N
25595
| Rubicon Systems |
Orton |
904
456-0092 |
FL Y
SELECT DISTINCTROW *
FROM PRODUCT;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE=21344;
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_PRICE, PRODUCT.V_CODE
FROM PRODUCT
WHERE (((PRODUCT.V_CODE)<>21344));
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_MIN, PRODUCT.P_PRICE
FROM PRODUCT
WHERE (((PRODUCT.P_PRICE)<=10));
SELECT PRODUCT.P_CODE, PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_MIN, PRODUCT.P_PRICE
FROM PRODUCT
WHERE (((PRODUCT.P_CODE)<"1558-QW1"));
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_MIN, PRODUCT.P_PRICE, PRODUCT.P_INDATE
FROM PRODUCT
WHERE (((PRODUCT.P_INDATE)>=#1/20/2016#));
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_PRICE, [P_QOH]*[P_PRICE] AS Expr1
FROM PRODUCT;
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_PRICE, [P_QOH]*[P_PRICE] AS TOTVALUE
FROM PRODUCT;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE=21344 Or V_CODE=24288;
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_INDATE, PRODUCT.P_PRICE, PRODUCT.V_CODE
FROM PRODUCT
WHERE (((PRODUCT.P_INDATE)>#1/15/2016#) AND ((PRODUCT.P_PRICE)<50));
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_INDATE, PRODUCT.P_PRICE, PRODUCT.V_CODE
FROM PRODUCT
WHERE (((PRODUCT.P_INDATE)>#1/15/2016#) AND ((PRODUCT.P_PRICE)<50)) OR (((PRODUCT.V_CODE)=24288));
SELECT DISTINCTROW PRODUCT_3.P_CODE, PRODUCT_3.P_DESCRIPT, PRODUCT_3.P_INDATE, PRODUCT_3.P_PRICE, PRODUCT_3.P_SALECODE
FROM PRODUCT_3;
SELECT DISTINCTROW PRODUCT.P_QOH, PRODUCT.P_PRICE, PRODUCT.P_CODE, PRODUCT.P_DESCRIPT
FROM PRODUCT
ORDER BY PRODUCT.P_PRICE;
SELECT DISTINCTROW EMPLOYEE.EMP_LNAME, EMPLOYEE.EMP_FNAME, EMPLOYEE.EMP_INITIAL, EMPLOYEE.EMP_AREACODE, EMPLOYEE.EMP_PHONE
FROM EMPLOYEE
GROUP BY EMPLOYEE.EMP_LNAME, EMPLOYEE.EMP_FNAME, EMPLOYEE.EMP_INITIAL, EMPLOYEE.EMP_AREACODE, EMPLOYEE.EMP_PHONE
ORDER BY EMPLOYEE.EMP_LNAME, EMPLOYEE.EMP_FNAME, EMPLOYEE.EMP_INITIAL;
SELECT DISTINCTROW PRODUCT.P_DESCRIPT, PRODUCT.V_CODE, PRODUCT.P_INDATE, PRODUCT.P_PRICE
FROM PRODUCT
GROUP BY PRODUCT.P_DESCRIPT, PRODUCT.V_CODE, PRODUCT.P_INDATE, PRODUCT.P_PRICE
HAVING (((PRODUCT.P_INDATE)<#1/21/2016#) AND ((PRODUCT.P_PRICE)<=50))
ORDER BY PRODUCT.V_CODE, PRODUCT.P_PRICE DESC;
SELECT DISTINCT PRODUCT.V_CODE
FROM PRODUCT;
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM VENDOR INNER JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;
SELECT DISTINCTROW PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM VENDOR INNER JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE
WHERE (((PRODUCT.P_INDATE)>#1/15/2016#))
ORDER BY PRODUCT.P_PRICE;
SELECT E.EMP_NUM, E.EMP_LNAME, E.EMP_MGR, M.EMP_LNAME
FROM EMP AS E, EMP AS M
WHERE (((E.EMP_MGR)=[M].[EMP_NUM]))
ORDER BY E.EMP_MGR;
SELECT PRODUCT.P_CODE AS PART_CODE, PRODUCT.P_DESCRIPT AS PART_DESCRIPT, PRODUCT.P_PRICE AS PART_PRICE, PRODUCT.V_CODE INTO PART
FROM PRODUCT;
SELECT V_CODE, SUM(P_ONHAND) AS TOTQTY, MAX(P_ONHAND) AS MAXQTY, MIN(P_ONHAND) AS MINQTY
FROM PRODUCT
GROUP BY V_CODE;
UPDATE PRODUCT_2
SET PRODUCT_2.P_CODE = ‘1’
WHERE (((PRODUCT_2.P_CODE) Between “#8/15/99#” And “#8/20/99#”));
UPDATE EMPLOYEE
SET EMPLOYEE.EMP_YEARS = Int((Date()-[EMPLOYEE]![EMP_HIRE_DATE])/365);
SELECT PART.*
FROM PART;
SELECT Count(*) AS [Count(Distinct v_code)]
FROM [SELECT distinct PRODUCT.V_CODE
FROM PRODUCT]. AS distinctvaluelist;
SELECT Count(v_code) AS [Count(Distinct v_code)]
FROM [SELECT distinct PRODUCT.V_CODE
FROM PRODUCT
where p_price <= 10]. AS distinctvaluelist;
SELECT Count(PRODUCT.P_CODE) AS CountOfP_CODE
FROM PRODUCT
WHERE (((PRODUCT.P_PRICE)<=10));
SELECT Max(PRODUCT.P_PRICE) AS MaxOfP_PRICE
FROM PRODUCT;
SELECT Min(PRODUCT.P_PRICE) AS MinOfP_PRICE
FROM PRODUCT;
SELECT p_code, p_descript, p_price
FROM PRODUCT
WHERE p_price = (select max(p_price) from product);
SELECT Sum(CUSTOMER.CUS_BALANCE) AS TOTBALANCE
FROM CUSTOMER;
SELECT Sum([p_qoh]*[p_price]) AS TOTVALUE
FROM PRODUCT;
