Please complete following questions in the query, then put your queries and outputs in a notepad(.txt)...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Please complete following questions in the query, then put your queries and outputs in a notepad(.txt) document to upload your work. 1. Show the distinct postal_codes and their city names? 2. How many customers are in each Postal code. List postal code, city, state, and Num_Of_Customers. 3. How many items are there in each storehouse of each category. Sort by storehouse, category. 4. What is the price of the least expensive item in the item table. 5. List Customer number, customer name, Balance, and Credit_Limit. Create a new column called Balance Level and categorize balance above $3000 as High, between $1000 to $3000 as Moderate, below $1000 as Low. 6. For each order, list the order number, and OrderValue (which should the based on the total of quantity x price data) with OrderValue of $500 or above. Before learning how to design a database, you need to be familiar with some important database concepts related to relational databases, which are the types of databases you examined in Chapter 1 and that you will use throughout the rest of this text. The terms entity, attribute, and relationship are important to understand when designing a database; the concepts of functional dependence and primary keys are critical when learning about the database design process. Relational Databases A relational database is a collection of tables like the ones you examined for TAL Distributors in Chapter 1 and that appear in Figure 2-1. Formally, these tables are called relations, and this is how this type of database gets its name. REP STATE POSTAL CODE COMMISSION RATE 90092 REP NUM LAST_NAME FIRST NAME STREET CITY Campos Gradey Rafael Megan Hui Janet 724 Vinca Dr. Grove $23,457 50 $41317.00 15 CA CA 1785 Tyler Ave. Northfield 0.06 30 632 Lintris St. Fullton 90UNS $27.789 25 S0.00 CA 0.06 90098 90097 45 Tian Sefton 267 Oakley St Congaree CA 60 0.06 CUSTOMER CUSTOMER NUM CUSTOMER_NAME STREET CITY STATE POSTAL CODE BALANCE CREDIT LIMIT REP NUM Fullton Toys Galore Brookings Direct The Everything Shop Johson's Department Store CA CA Congaree CA $7.500.00 15 $10,000.00 30 $7,500.00 45 126 28 Laketon St. $1,210.25 260 452 Columbus De Grove 90092 $S7500 342 Magve St. 90097 $2.345.75 334 124 Main St. Northficld CA 90098 $S79 25 S7.500.00 30 Grove Itistorical Museum Store 440 3456 Central Ave. Fullton CA 90085 $45.00 $5,000.00 45 502 Cards and More 167 Hale St. Mena CA 90104 $5.025.75 $5,00L00 15 Almondton General Store Almondton CA 90125 $15,000 00 45 586 3345 Devon Ave. $3,456.75 372 Oxford St $678.90 Cricket Git Shup Cress Store Grove GA CA 90092 90097 665 S7.500.00 30 12 Rising Sun Ave. Congaroe 786 Passmore St. NorthfieldCA CA CA $10,000.00 15 $7.500.00 45 713 $4,234.60 Unique Gifts Kline's All Season Gifes 796 90098 $124,75 945 Gilham St. 382 Wildwood Ave Fullton Mesa 90104 $2.475.99 $15,000.0030 $7.50000 15 824 SAS.75 FIGURE 2-1 Sample data for TAL Distributors Database Design Fundamentals ORDERS ORDER LINE ORDER DATE CUSTOMER_NUM 10/12/2015 126 10/12/2015 334 NUM ORDERED QUOTED PRICE ORDER NUM ORDER_NUM ITEM_NUM 51608 51608 CD33 $86.99 51610 51610 KL78 25 $10.95 10/13/2015 386 10/13/2015 260 10/15/2015 586 10/15/2015 126 10/15/2015 586 10/16/2015 796 51613 51610 TR40 10 $13.99 51614 51613 DL51 5. $104.95 51617 51614 FD11 $124.95 51619 51617 NL89 4. $115.99 $116.95 $121.95 $29.95 $36.95 51623 51617 TW35 51625 51619 FD11 51623 DR67 5. 51623 F124 12 51623 KD34 10 $13.10 51625 MT03 $45.79 ITEM ITEM_NUM DESCRIPTION Al174 ON HAND CATEGORY 9 GME STOREHOUSE PRICE Patience Skittles $22.99 BR23 21 GME $29.99 CD33 Wood Block Set (48 piece) 36 TOY 12 TOY $89.49 DL51 Classic Railway Set $107.95 DR67 Giant Star Brain Teaser 24 PZL 2. $31.95 DW23 Mancala 40 GME 3. $50.00 FD11 Rocking Horse 8 TOY 3. $124.95 65 PZI. 56 GME FH24 Puzzle Gift Set $38.95 KA12 Cribbage Set 3. $75.00 KD34 Pentominoes Brain Teaser 60 PZL 2. $14.95 110 GME 45 PZI. 32 TOY KL78 Pick Up Stieks 1. $10.95 MT03 Zauberkasten Brain Teaser 1. $45.79 NL89 Wood Block Set (62 piece) $119.75 TR40 Tie Tac Toe 75 GME 2. $13.99 TW35 Fire Engine 30 TOY 2. $118.95 FIGURE 2-1 Sample data for TAL Distributors (continued) Script buput GOAND 207 GEAR 15 209 CAN 222 soT 231 MOT 232 NOT 1100 241 MAER 285 L 2000 10 TIRE 4000 1 row elerted. FRICE DELIVERY TIHE PANTNO 221 10 231 20 53 222 13 241 54 209 21 54 30 231 241 54 30 200 14 21 Onans RICE DELIVET TI pcEn Script. Output 2/2 222 241 44 44 209 19.5 14 seleted. ADORESS 51 Derecto PARTS 14 BON ST. BROS A Wr 52 vssovave, INC. S12 AMCIENT svp., POKPEEI Y sa ATLANTES co. OCEAN AVE., MAINarOn De 54 TETANIE PARTS R LARGE T.. Eon Tx ST EAGLE MANDWANE 4 TANDUELITY PACE, APOLLO 128 OraT vo... aIDHEY ASTRALIA 24 ARTIR CoURT, CELOT eAND 1 aY SARTS 4 TONT STD. 1 Eowsleotet. PARTHO 20 01-AP- 10 02-APR- 30 03-AR- 50 03-AFR- 40 10-aPN-e 232 221 221 241 222 Please complete following questions in the query, then put your queries and outputs in a notepad(.txt) document to upload your work. 1. Show the distinct postal_codes and their city names? 2. How many customers are in each Postal code. List postal code, city, state, and Num_Of_Customers. 3. How many items are there in each storehouse of each category. Sort by storehouse, category. 4. What is the price of the least expensive item in the item table. 5. List Customer number, customer name, Balance, and Credit_Limit. Create a new column called Balance Level and categorize balance above $3000 as High, between $1000 to $3000 as Moderate, below $1000 as Low. 6. For each order, list the order number, and OrderValue (which should the based on the total of quantity x price data) with OrderValue of $500 or above. Before learning how to design a database, you need to be familiar with some important database concepts related to relational databases, which are the types of databases you examined in Chapter 1 and that you will use throughout the rest of this text. The terms entity, attribute, and relationship are important to understand when designing a database; the concepts of functional dependence and primary keys are critical when learning about the database design process. Relational Databases A relational database is a collection of tables like the ones you examined for TAL Distributors in Chapter 1 and that appear in Figure 2-1. Formally, these tables are called relations, and this is how this type of database gets its name. REP STATE POSTAL CODE COMMISSION RATE 90092 REP NUM LAST_NAME FIRST NAME STREET CITY Campos Gradey Rafael Megan Hui Janet 724 Vinca Dr. Grove $23,457 50 $41317.00 15 CA CA 1785 Tyler Ave. Northfield 0.06 30 632 Lintris St. Fullton 90UNS $27.789 25 S0.00 CA 0.06 90098 90097 45 Tian Sefton 267 Oakley St Congaree CA 60 0.06 CUSTOMER CUSTOMER NUM CUSTOMER_NAME STREET CITY STATE POSTAL CODE BALANCE CREDIT LIMIT REP NUM Fullton Toys Galore Brookings Direct The Everything Shop Johson's Department Store CA CA Congaree CA $7.500.00 15 $10,000.00 30 $7,500.00 45 126 28 Laketon St. $1,210.25 260 452 Columbus De Grove 90092 $S7500 342 Magve St. 90097 $2.345.75 334 124 Main St. Northficld CA 90098 $S79 25 S7.500.00 30 Grove Itistorical Museum Store 440 3456 Central Ave. Fullton CA 90085 $45.00 $5,000.00 45 502 Cards and More 167 Hale St. Mena CA 90104 $5.025.75 $5,00L00 15 Almondton General Store Almondton CA 90125 $15,000 00 45 586 3345 Devon Ave. $3,456.75 372 Oxford St $678.90 Cricket Git Shup Cress Store Grove GA CA 90092 90097 665 S7.500.00 30 12 Rising Sun Ave. Congaroe 786 Passmore St. NorthfieldCA CA CA $10,000.00 15 $7.500.00 45 713 $4,234.60 Unique Gifts Kline's All Season Gifes 796 90098 $124,75 945 Gilham St. 382 Wildwood Ave Fullton Mesa 90104 $2.475.99 $15,000.0030 $7.50000 15 824 SAS.75 FIGURE 2-1 Sample data for TAL Distributors Database Design Fundamentals ORDERS ORDER LINE ORDER DATE CUSTOMER_NUM 10/12/2015 126 10/12/2015 334 NUM ORDERED QUOTED PRICE ORDER NUM ORDER_NUM ITEM_NUM 51608 51608 CD33 $86.99 51610 51610 KL78 25 $10.95 10/13/2015 386 10/13/2015 260 10/15/2015 586 10/15/2015 126 10/15/2015 586 10/16/2015 796 51613 51610 TR40 10 $13.99 51614 51613 DL51 5. $104.95 51617 51614 FD11 $124.95 51619 51617 NL89 4. $115.99 $116.95 $121.95 $29.95 $36.95 51623 51617 TW35 51625 51619 FD11 51623 DR67 5. 51623 F124 12 51623 KD34 10 $13.10 51625 MT03 $45.79 ITEM ITEM_NUM DESCRIPTION Al174 ON HAND CATEGORY 9 GME STOREHOUSE PRICE Patience Skittles $22.99 BR23 21 GME $29.99 CD33 Wood Block Set (48 piece) 36 TOY 12 TOY $89.49 DL51 Classic Railway Set $107.95 DR67 Giant Star Brain Teaser 24 PZL 2. $31.95 DW23 Mancala 40 GME 3. $50.00 FD11 Rocking Horse 8 TOY 3. $124.95 65 PZI. 56 GME FH24 Puzzle Gift Set $38.95 KA12 Cribbage Set 3. $75.00 KD34 Pentominoes Brain Teaser 60 PZL 2. $14.95 110 GME 45 PZI. 32 TOY KL78 Pick Up Stieks 1. $10.95 MT03 Zauberkasten Brain Teaser 1. $45.79 NL89 Wood Block Set (62 piece) $119.75 TR40 Tie Tac Toe 75 GME 2. $13.99 TW35 Fire Engine 30 TOY 2. $118.95 FIGURE 2-1 Sample data for TAL Distributors (continued) Script buput GOAND 207 GEAR 15 209 CAN 222 soT 231 MOT 232 NOT 1100 241 MAER 285 L 2000 10 TIRE 4000 1 row elerted. FRICE DELIVERY TIHE PANTNO 221 10 231 20 53 222 13 241 54 209 21 54 30 231 241 54 30 200 14 21 Onans RICE DELIVET TI pcEn Script. Output 2/2 222 241 44 44 209 19.5 14 seleted. ADORESS 51 Derecto PARTS 14 BON ST. BROS A Wr 52 vssovave, INC. S12 AMCIENT svp., POKPEEI Y sa ATLANTES co. OCEAN AVE., MAINarOn De 54 TETANIE PARTS R LARGE T.. Eon Tx ST EAGLE MANDWANE 4 TANDUELITY PACE, APOLLO 128 OraT vo... aIDHEY ASTRALIA 24 ARTIR CoURT, CELOT eAND 1 aY SARTS 4 TONT STD. 1 Eowsleotet. PARTHO 20 01-AP- 10 02-APR- 30 03-AR- 50 03-AFR- 40 10-aPN-e 232 221 221 241 222
Expert Answer:
Answer rating: 100% (QA)
solution 1 select distinct postal code city from customer postal code city 90085 fullton 90092 grove 90097 congaree 90098 northfield 90104 mesa 90125 ... View the full answer
Related Book For
Posted Date:
Students also viewed these accounting questions
-
A marketer is interested in how many customers are likely to buy a particular product in a particular store. Let P be the proportion of all customers in the store who will buy the product. Let the...
-
Write the answers to the following questions in the space provided. Note: all the following questions relate to March 814. Register Reports 1. What is the total amount of purchase orders for the...
-
Write the answers to the following questions in the space provided. Note: all the following questions relate to March 814. Register Reports 1. What is the total amount of purchase orders for the...
-
Roberts Originals Co. (ROC) provides new and unique cases and otherassignments to professors each semester to ensure that students will not be able to find the solutions published online. Due to the...
-
The following table gives information on the amount of sugar (in grams) and the calorie count in one serving of a sample of 13 varieties of Kelloggs cereal. a. Construct a scatter diagram for these...
-
Calculate the irreversibility for the process described, assuming that the heat transfer is with the surroundings at 61 F.
-
What the differences are between civil litigation and other types of litigation
-
Company A uses the FIFO method to cost inventory, and Company B uses the LIFO method. The two companies are exactly alike except for the difference in inventory costing methods. Costs of inventory...
-
Assume that a share of common stock has just paid a dividend of $1.50, and that this dividend is expected to grow ata. constant growth rate of 6.0 percent each year. Also assume that the expected...
-
Given the accompanying sample data, use Excels formula options to find the 99% confidence interval for the population mean. Assume that the population is normally distributed.
-
47) Sakinah Sdn Bhd offers credit terms of 3/4, net 15. Based on experience, 89 percent of all customers will take the discount. The firm sells 356 units each month at a price of RM643 each. What is...
-
ACME Real Estate Developers has $100 million in a floating rate construction loan at LIBOR plus 2.0%. The forecast is for rising interest rates and the borrower is seeking an interest rate swap on...
-
Purpose This assignment is a team analysis exercise. It allows you to experience a team project and assess the team dynamics. It provides an opportunity for you to demonstrate your learning about the...
-
A company will be paying a dividend of $6.00 next period. if the cost of equity is 13%, what would be the approximate price of this stock today if the company is expected to grow at 4% for the...
-
21. Mr. Montzingo has the following auto insurance policy 50/100/25. Mrs. Yanzick and Mrs. Wooster are on their way to the dance club when Mr. Montzingo hits their car. It is Mrs. Wooster's car and...
-
If you want to construct a 95% confidence interval of a population mean and you only have a sample standard deviation calculated from a sample with a size 12, what is the critical value?
-
3. Transform the following nondeterministic finite automata into deterministic finite automata accepting the same languages: (Each 1 Point, Total 3 Points) a. b. a a b b 9 a a a a
-
What mass of KBr (in grams) should you use to make 350.0 mL of a 1.30 M KBr solution?
-
Brian Pud opened a consulting company, and the following transactions resulted: a. Brian invested $40,000 in the consulting agency. b. Bought office equipment on account, $7,000. c. Agency received...
-
The following is the stockholders equity of Piesco Corporation on October 1, 201X: 1. Journalize the transactions in general journal form. 2. Prepare the stockholders equity section of the balance...
-
John Conway, Steve Barrett, and Mary Fitzpatrick invested $3,000, $12,600, and $14,400, respectively. At the end of the first year, the companys net income was $48,000. Assuming no agreement was...
-
Determine the reactions at the pin support \(A\) and roller \(B\). 500 lb/ft 30 ft 700 lb/ft 48 ft- 48 ft BA 20 ft B
-
For methanol synthesis reaction \[ \mathrm{CO}(\mathrm{g})+2 \mathrm{H}_{2}(\mathrm{~g}) ightleftharpoons \mathrm{CH}_{3} \mathrm{OH}(\mathrm{g}) \] Using a feed mixture of carbon monoxide and...
-
The L2 steel blade of the band saw wraps around the pulley having a radius of \(12 \mathrm{in}\). Determine the maximum normal stress in the blade. The blade has a width of 0.75 in. and a thickness...
Study smarter with the SolutionInn App