Use SQL commands to finish the questions. Please include BOTH SQL commands and results in your answers.1. List the first and last names of customers with credit limits of $1,000 or more.2. List the order number for each order placed by customer number 125 on 11/15/2021.3. List the item ID and description of each item that is not in category ‘HOR’.4. List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column. (Hint: On-hand value is the production of the units on hand and price.)5. Use the IN operator to list the item ID and description of each item in category FSH or BRD or CAT.6. Find the total of the balances for all customers represented by sales rep 10 with balances that are less than their credit limits.7. List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items. (Hint: Use a subquery.)8. What is the price of the least expensive item in the database?9. List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150. Order the results by sales rep ID.10. List the item ID of any item with an unknown description. ITEMITEM_IDAD72BC33CA75DT12FM23FS39FS42KH81LD14LP73PF19QB92SP91UF39WB49DESCRIPTIONDog Feeding StationFeathers Bird Cage (12x24x18)Enclosed Cat Litter StationDog Toy Gift SetFly Mask with EarsFolding Saddle StandAquarium (55 Gallon)Wild Bird Food (25 lb)Locking Small Dog DoorLarge Pet CarrierPump & Filter KitQuilted Stable BlanketSmall Pet CarrierUnderground Fence SystemInsulated Water BucketFIGURE 1-2 Sample data for KimTay Pet Supplies1421614219142221422414228142311423314237INVOICESINVOICE_NUM INVOICE_DATEON HAND12101527CUST_ID11/15/2021 12511/15/2021 22711/16/2021 29418211/16/202111/18/2021 43511/18/2021 12511/18/2021 43511/19/2021 6164112524142353218734FIGURE 1-2 Sample data forKimTay Pet Supplies (Continued)CATEGORYDOGBRDCATDOGHORHORFSHBRDDOGDOGFSHHORCATDOGHOR142161421914219142221422414228142281423114233142331423314237LOCATIONBBCLD14KH81FS42PF19UF39KH81QB92WB49LP73BCсAСABAсBACopyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).itorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require itCIntroduction to Kim Tay Pet Supplies and StayWell Student Accommodation DatabasesINVOICE_LINEINVOICE_NUM ITEM_ID QUANTITYCA75AD72DT12PRICE$79.99$79.9932$39.99$39.994141121443$24.95$39.99$124.99$19.99$49.99$59.99$74.99$119.99$39.99$199.99$79.99QUOTED_PRICE$37.99$79.99$39.99$47.99$18.99$124.99$74.99$189.99$19.99$109.95$74.95$54.955 CUSTOMERCUST_FIRST_ID126 Joey182227294375492NAME314 Tom543616BillySandra435 James05101520MelanieSamantha SmithElmerAngieSally721 Leslie795 RandyLAST_NAMESmithRuftonPincherRascalJacksonGonzalezJacksonCruzADDRESS17 Fourth St21 Simple CirSmith53 Verde Ln22 JacksonFarm RdHendricks 27 Locklear In14 Rock Ln1 Rascal FarmRd199 18th Ave123 SheeplandRdBlacksmith 75 Stream RdSALES REPREP_ID FIRST NAME LAST NAME ADDRESSSusanRichardGarciaMillerSmithJacksonDonnaDanielCITYCodyGarland42 BlackwaterWay16 Rockway Rd WapitiPowellRalstonCodyElk ButteGarlandPowellRalstonElk ButteCodySTATE POSTAL EMAILWYWYWYWYWYWYWYWYWYWY82414824358244082440824148243382450824358244082440WY 82433WY 82414CITY42 Mountain Ln Cody WY87 Pikes DrRalston WY312 Oak Rd Powell WY19 Lookout DrElk Butte WYjsmith 17@example.combillyruff@example.comspinch2@example.comssmith5@example.comtrascal3@example.commjackson5@example.comjgonzo@example.comejackson4@example.comBALANCE CREDIT_REPLIMIT ID$80.68$500.00 05Ismith12@example.com$43.13 $750.00 10$156.38 $500.00 15$58.60$17.25 $250.00 15$500.00 10$252.25 $250.00 05$230.40 $1,000.00 15$45.20 $500.00 10ahendricks7@ $315.00 $750.00 05example.comscruz5@example.com$8.33 $500.00 15$166.65 $1,000.00 10rblacksmith6@ $61.50example.comSTATE POSTAL CELL PHONE82414 307-824-1245824408244082433307-406-4321307-982-8401307-883-9481$500.00 05COMMISSION RATE$12,743.16 0.04$20,872.11 0.06$14,912.92 0.04$0.00 0.04

Answered: Use SQL commands to finish the…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Use SQL commands to finish the questions. Please include BOTH SQL commands and results in your answers.

1. List the first and last names of customers with credit limits of $1,000 or more.

2. List the order number for each order placed by customer number 125 on 11/15/2021.

3. List the item ID and description of each item that is not in category ‘HOR’.

4. List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column. (Hint: On-hand value is the production of the units on hand and price.)

5. Use the IN operator to list the item ID and description of each item in category FSH or BRD or CAT.

6. Find the total of the balances for all customers represented by sales rep 10 with balances that are less than their credit limits.

7. List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items. (Hint: Use a subquery.)

8. What is the price of the least expensive item in the database?

9. List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150. Order the results by sales rep ID.

10. List the item ID of any item with an unknown description.

ITEM
ITEM_ID
AD72
BC33
CA75
DT12
FM23
FS39
FS42
KH81
LD14
LP73
PF19
QB92
SP91
UF39
WB49
DESCRIPTION
Dog Feeding Station
Feathers Bird Cage (12x24x18)
Enclosed Cat Litter Station
Dog Toy Gift Set
Fly Mask with Ears
Folding Saddle Stand
Aquarium (55 Gallon)
Wild Bird Food (25 lb)
Locking Small Dog Door
Large Pet Carrier
Pump & Filter Kit
Quilted Stable Blanket
Small Pet Carrier
Underground Fence System
Insulated Water Bucket
FIGURE 1-2 Sample data for KimTay Pet Supplies
14216
14219
14222
14224
14228
14231
14233
14237
INVOICES
INVOICE_NUM INVOICE_DATE
ON HAND
12
10
15
27
CUST_ID
11/15/2021 125
11/15/2021 227
11/16/2021 294
182
11/16/2021
11/18/2021 435
11/18/2021 125
11/18/2021 435
11/19/2021 616
41
12
5
24
14
23
5
32
18
7
34
FIGURE 1-2 Sample data for
KimTay Pet Supplies (Continued)
CATEGORY
DOG
BRD
CAT
DOG
HOR
HOR
FSH
BRD
DOG
DOG
FSH
HOR
CAT
DOG
HOR
14216
14219
14219
14222
14224
14228
14228
14231
14233
14233
14233
14237
LOCATION
B
B
C
LD14
KH81
FS42
PF19
UF39
KH81
QB92
WB49
LP73
B
C
с
A
С
A
B
A
с
B
A
Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
itorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it
C
Introduction to Kim Tay Pet Supplies and StayWell Student Accommodation Databases
INVOICE_LINE
INVOICE_NUM ITEM_ID QUANTITY
CA75
AD72
DT12
PRICE
$79.99
$79.99
3
2
$39.99
$39.99
4
1
4
1
1
2
1
4
4
3
$24.95
$39.99
$124.99
$19.99
$49.99
$59.99
$74.99
$119.99
$39.99
$199.99
$79.99
QUOTED_PRICE
$37.99
$79.99
$39.99
$47.99
$18.99
$124.99
$74.99
$189.99
$19.99
$109.95
$74.95
$54.95
5

CUSTOMER
CUST_FIRST_
ID
126 Joey
182
227
294
375
492
NAME
314 Tom
543
616
Billy
Sandra
435 James
05
10
15
20
Melanie
Samantha Smith
Elmer
Angie
Sally
721 Leslie
795 Randy
LAST_
NAME
Smith
Rufton
Pincher
Rascal
Jackson
Gonzalez
Jackson
Cruz
ADDRESS
17 Fourth St
21 Simple Cir
Smith
53 Verde Ln
22 Jackson
Farm Rd
Hendricks 27 Locklear In
14 Rock Ln
1 Rascal Farm
Rd
199 18th Ave
123 Sheepland
Rd
Blacksmith 75 Stream Rd
SALES REP
REP_ID FIRST NAME LAST NAME ADDRESS
Susan
Richard
Garcia
Miller
Smith
Jackson
Donna
Daniel
CITY
Cody
Garland
42 Blackwater
Way
16 Rockway Rd Wapiti
Powell
Ralston
Cody
Elk Butte
Garland
Powell
Ralston
Elk Butte
Cody
STATE POSTAL EMAIL
WY
WY
WY
WY
WY
WY
WY
WY
WY
WY
82414
82435
82440
82440
82414
82433
82450
82435
82440
82440
WY 82433
WY 82414
CITY
42 Mountain Ln Cody WY
87 Pikes Dr
Ralston WY
312 Oak Rd Powell WY
19 Lookout Dr
Elk Butte WY
jsmith 17@
example.com
billyruff@
example.com
spinch2@
example.com
ssmith5@
example.com
trascal3@
example.com
mjackson5@
example.com
jgonzo@
example.com
ejackson4@
example.com
BALANCE CREDIT_REP
LIMIT ID
$80.68
$500.00 05
Ismith12@
example.com
$43.13 $750.00 10
$156.38 $500.00 15
$58.60
$17.25 $250.00 15
$500.00 10
$252.25 $250.00 05
$230.40 $1,000.00 15
$45.20 $500.00 10
ahendricks7@ $315.00 $750.00 05
example.com
scruz5@
example.com
$8.33 $500.00 15
$166.65 $1,000.00 10
rblacksmith6@ $61.50
example.com
STATE POSTAL CELL PHONE
82414 307-824-1245
82440
82440
82433
307-406-4321
307-982-8401
307-883-9481
$500.00 05
COMMISSION RATE
$12,743.16 0.04
$20,872.11 0.06
$14,912.92 0.04
$0.00 0.04

Expert Solution

Step 1

SQL :

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Here are some common SQL commands:

SELECT: This command is used to retrieve data from one or more tables in a database.

INSERT: This command is used to add new data to a table.

UPDATE: This command is used to modify existing data in a table.

DELETE: This command is used to remove data from a table.

CREATE TABLE: This command is used to create a new table in a database.

DROP TABLE: This command is used to delete a table from a database.

ALTER TABLE: This command is used to modify the structure of an existing table.

CREATE INDEX: This command is used to create an index on one or more columns of a table to speed up data retrieval.

GROUP BY: This command is used to group rows in a table based on one or more columns.

ORDER BY: This command is used to sort the result set of a query in ascending or descending order based on one or more columns.

JOIN: This command is used to combine rows from two or more tables based on a related column between them.

DISTINCT: This command is used to retrieve only unique values from a column.

WHERE: This command is used to filter data based on a specified condition.

HAVING: This command is used to filter the result set of a GROUP BY query based on a specified condition.

LIMIT: This command is used to limit the number of rows returned by a query.

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

Please solve question no. 10 including screenshots of SQL results.

Solution

by Bartleby Expert

SEE SOLUTION

Follow-up Question

Need help with solutions to questions 7 to 9.

Solution

by Bartleby Expert

SEE SOLUTION

Follow-up Question

Only answered 3 questions. The other 7 are missing.

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.