Please complete following questions in the query, then put your queries and outputs in a notepad(.txt)...

 

 

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

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