HomeworkLib
Question

We are using, Oracle Database 11g Express Edition.

use SQL and the TAL Distrutors database (see figure 1-2 in chapter 1) to complete the following exercises. if directed to do so by your instructor, use the information provided with the chapter 3 exercises to print your output or save it to a document.

1. list the item number, description, and price for all items.

2. list all rows and columns for the complete ORDERS table.

3. list the names of customers with credit limits of $10,000 or more.

4. list the order number for each placed by customer number 126 on 10/15/2015.

5. list the number and name of each customer represented by sales rep 30 or sales rep 45.

6. list the item number and description of each item that is not in category PZL.

7. List the item number, description, and number of units on hand for each item that has between 20 and 40 units on hand, including both 20 and 40. do this two ways.

8. list the item number, description, and on-hand value (units on hand * until price) of each item in category TOY. (on-hand value is really units on hand * cost, but there is no COST column in the ITEM table.) Assign the name ON_HAND_VALUE to the computed column.

9. list the item number, description, and on-hand value for each item whose on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.

10. use the IN operator to list the item number and description of each item in category GME or PZL.

11. Find the number and name of each customer whose name begins with the letter "C".

12. list all details about all items. order output by description.

13. list all details about all items. order the output by item number within storehouse. (that is, order the output by storehouse and then by item number.)

14. how many customers have balances that are more than their credit limits?

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

16. list the item number, 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.

17. what is the price of the least expensive item in the database?

18. what is the item number, description, and price of the least expensive item in the database?

19. list the sum of the balances of all customers for each sales rep. order and group the results by sales rep number.

20. 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 $5,000 . order the results by sales rep number.

21. list the item number of any item with an unknown description.

22. list the item number and description of all items that are in the PZL or TOY category and contain the word "Set" in the description.

23. TAL Distrutors is considering discounting the price of all items by 10 percent. list the item number, description, and discounted price for all items. use DISCOUNTED_PRICE as the name for the computed column.

51623 FH24 51623 KD34 51625 MTO3 ITEM ITEM NUM DESCRIPTION ON HAND CATEGORY STORE HOUSE PRICE Patience 9 GME $22.99 Skittles BR23 $29.99 1 CD33 Wood Block Set (48 piece) 36 TOY Classic Railway Set DL51 12 TOY $107.95 s3195 DR67 Giant Star Brain Teaser 24 PZL DW23 Mancala. 40 GME 3 sso 00 FDI Rocking Horse 8 TOY $124.95 L1 3895 FI124 65 PZL Puzzle Gift Set KA12 Cribbage Set 60 PZL Pentomino es Brain Teaser $14.95 110 GME K178 $10.95 Pick Up Sticks MT03 45 PZL Zauber kasten Brain Teaser $119.75 NLA% Wood Block set (62 piece) 32 TOY 75 GME TW3S 30 TOY FIGURE 1-2 Sample data for TAL Distributors (continued)

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

1.

Query:

SELECT ITEM_NUM, DESCRIPTION, PRICE FROM ITEM

Output:

1 SELECT ITEM NUM, DESCRIPTION, PRICE 2 FROM ITEM DESCRIPTION PRICE ITEM NUM Patience 22.99 AH74 Skittles BR23 29.99 CD33 Woo

2.

Query:

SELECT * FROM ORDERS;

Output:

1 SELECTFROM ORDERS; ORDER NUM ORDER DATE CUSTOMER NUM 51610 12-ост-15 334 51613 13-OCT-15 386 51614 13-ост-15 260 51617 15-О

3.

Query:

SELECT CUSTOMER_NAME FROM CUSTOMER
WHERE CREDIT_LIMIT>=10000;

Output:

1 SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CREDIT-LIMIT>=10000; 3 CUSTOMER NAME Brookings Direct Almondton General Store Cres

4.

Query:

SELECT ORDER_NUM FROM ORDERS
WHERE CUSTOMER_NUM=126
AND ORDER_DATE = '15-OCT-2015';

Output:

1 SELECT ORDER NUM FROM ORDERS 2 WHERE CUSTOMER_NUM-126 3 AND ORDER DATE = 15-OCT-2015 4 ORDER NUM 51619

5.

Query:

SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
WHERE REP_NUM IN (30,45);

Output:

1 SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE REP-NUM IN (30,45); 3 CUSTOMER NUM CUSTOMER_ NAME Brookings Direct 2

6.

Query:

SELECT ITEM_NUM, DESCRIPTION FROM ITEM
WHERE CATEGORY <> 'PZL';


Output:

1 SELECT ITEM NUM, DESCRIPTION FROM ITEM 2 WHERE CATEGORYPZL; DESCRIPTION ITEM NUM Patience AH74 Skittles BR23 Wood Block Se

7.

Query:

SELECT ITEM_NUM, DESCRIPTION, ON_HAND FROM ITEM
WHERE ON_HAND >= 20 AND ON_HAND <= 40;

Output:

SELECT ITEM_NUM, DESCRIPTION, ON_HAND FROM ITEM
WHERE ON_HAND BETWEEN 20 AND 40;

Output:

1 SELECT ITEM NUM, DESCRIPTION, ON HAND FROM ITEM 2 WHERE ON HAND 20 AND ON HAND 40; ITEM_NUM DESCRIPTION ON HAND BR23 Skittl


8.

Query:

SELECT ITEM_NUM, DESCRIPTION, ON_HAND*PRICE AS ON_HAND_VALUE
FROM ITEM
WHERE CATEGORY = 'TOY';

Output:

1 SELECT ITEM_NUM, DESCRIPTION, ON_HAND*PRICE AS ON HAND_VALUE 2 FROM ITEM WHERE CATEGORY = TOY; 4 ON HAND VALUE ITEM NUM D

9.

Query:

SELECT ITEM_NUM, DESCRIPTION, ON_HAND * PRICE AS ON_HAND_VALUE
FROM ITEM
WHERE ON_HAND * PRICE>=1500;

Output:

1 SELECT ITEM NUM, DESCRIPTION, ON HAND 2 FROM ITEM 3 WHERE ON_HANDPRICE-1500; PRICE AS ON HAND_VALUE 4. ITEM NUM DESCRIPTION

10.

Query:

SELECT ITEM_NUM, DESCRIPTION
FROM ITEM
WHERE CATEGORY IN ('GME','PZL');

Output:

1 SELECT ITEM NUM, DESCRIPTION 2 FROM ITEM 3 WHERE CATEGORY IN (GME PZL); 4. ITEM NUM DESCRIPTION Patience AH74 Skittles B

11.

Query:

SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
WHERE CUSTOMER_NAME LIKE 'C%';

Output:

1 SELECT CUSTOMER NUM, CUSTOMER NAME 2 FROM CUSTOMER C%; 3 WHERE CUSTOMER-NAME LIKE 4 CUSTOMER_NUM CUSTOMER NAME Cards and

12.

Query:

SELECT * FROM ITEM
ORDER BY DESCRIPTION;

Output:

1 SELECTFROM ITEM 2 ORDER BY DESCRIPTION ITEM NUM DESCRIPTION ON HAND CATEGORY STOREHOUSE PRICE ΤΟΥ Classic Railway Set 12 10

13.

Query:

SELECT * FROM ITEM
ORDER BY STOREHOUSE, ITEM_NUM;

Output:

1 SELECTFROM ITEM 2 ORDER BY STOREHOUSE, ITEM_NUM; ITEM NUM DESCRIPTION ON HAND CATEGORY STOREHOUSE PRICE ΤΟΥ Wood Block Set

14.

Query:

SELECT COUNT(*) FROM CUSTOMER
WHERE BALANCE > CREDIT_LIMIT;

Output:

1 SELECT COUNT(FROM CUSTOMER 2 WHERE BALANCE CREDIT_LIMIT; 3 COUNT(*)

15.

Query:

SELECT SUM(BALANCE) FROM CUSTOMER
WHERE BALANCE < CREDIT_LIMIT
AND REP_NUM=15;

Output:

1 SELECT SUM (BALANCE) FROM CUSTOMER 2 WHERE BALANCE< CREDIT-LIMIT 3 AND REP-NUM=15; 4 SUM (BALANCE) 6380.6

16.

Query:

SELECT ITEM_NUM, DESCRIPTION, ON_HAND FROM ITEM
WHERE ON_HAND > (SELECT AVG(ON_HAND) FROM ITEM)

Output:

1 SELECT ITEM NUM, DESCRIPTION, ON HAND FROM ITEM 2 WHERE ON HAND (SELECT AVG (ON HAND) FROM ITEM); 4 ITEM NUM DESCRIPTION ON

17.

Query:

SELECT MIN(PRICE) FROM ITEM;

Output:

1 SELECT MIN(PRICE) FROM ITEM; 2 MIN(PRICE) 10.95

18.

Query:

SELECT ITEM_NUM, DESCRIPTION, PRICE FROM ITEM
WHERE PRICE=(SELECT MIN(PRICE) FROM ITEM);

Output:

1 SELECT ITEM_NUM, DESCRIPTION, PRICE FROM ITEM 2 WHERE PRICE (SELECT MIN (PRICE) FROM ITEM); 3 ITEM NUM PRICE DESCRIPTION Pi

19.

Query:

SELECT REP_NUM, SUM(BALANCE) FROM CUSTOMER
GROUP BY REP_NUM
ORDER BY REP_NUM;

Output:

1 SELECT REP_NUM, SUM(BALANCE) FROM CUSTOMER 2 GROUP BY REP_NUM 3 ORDER BY REP_NUM; 4 REP NUM SUM (BALANCE) 11406.35 15 30 46

20.

Query:

SELECT REP_NUM, SUM(BALANCE) FROM CUSTOMER
GROUP BY REP_NUM
HAVING SUM(BALANCE) >5000
ORDER BY REP_NUM;

Output:

1 SELECT REP_NUM, SUM (BALANCE) FROM CUSTOMER 2 GROUP BY REP NUM 3 HAVING SUM (BALANCE) 5000 4 ORDER BY REP NUM; 5 REP NUM SU

21.

Query:

SELECT ITEM_NUM FROM ITEM
WHERE DESCRIPTION IS NULL;

Output:

1 SELECT ITEM NUM FROM ITEM 2 WHERE DESCRIPTION IS NULL; no data found

22.

Query:

SELECT ITEM_NUM, DESCRIPTION FROM ITEM
WHERE CATEGORY IN ('PZL','TOY')
AND DESCRIPTION LIKE '%Set%';

Output:

1 SELECT ITEM_NUM, DESCRIPTION FROM ITEM 2 WHERE CATEGORY IN (PZL TOY) 3 AND DESCRIPTION LIKE %Set%; 4 ITEM NUM DESCRIPTI

23.

Query:

SELECT ITEM_NUM, DESCRIPTION, PRICE * 90 / 100 AS DISCOUNTED_PRICE
FROM ITEM;

Output:

(PRICE-PRICE* 0.1) 1 SELECT ITEM-NUM, 2 FROM ITEM price, DESCRIPTION, AS DISCOUNTED-PRICE 3 ITEM NUM DESCRIPTION PRICE DISCOU

0 0
Add a comment
Know the answer?
Add Answer to:
We are using, Oracle Database 11g Express Edition. use SQL and the TAL Distrutors database (see...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • ONLY ODD NUMBERS. YOU MUST USE ORACLE OR MY SQL. THANKS Chapter 3 TAL Distributors Use...

    ONLY ODD NUMBERS. YOU MUST USE ORACLE OR MY SQL. THANKS Chapter 3 TAL Distributors Use SQL to complete the following exercises 1. Create a table named SALES_ REP. The table has the same structure as the REP table shown in Figure 3-11 except the LAST_NAME column should use the VARCHAR data type and the COMMISSION and RATE columns should use the NUMBER data type. Execute the command to describe the layout and characteristics of the SALES_REP table. Add the...

  • 27. Display item number, item name, and amount on hand for the items that have less...

    27. Display item number, item name, and amount on hand for the items that have less than data by the on hand value. 28. Display the total on hand value for each category in the Item table. Order the results by category. 29. Display item number, item name, and amount on hand for the items that have less than 20 on hand or are storehouse 2. 334 665 Rep RepNum LastName FirstName Street City State PostalCode Commission Rate 15 Campos...

  • Sample data is provided for the database for the sales system. Using the sample data, you...

    Sample data is provided for the database for the sales system. Using the sample data, you will determine the entities, key components of the entities, and business rules for the entities. Using the entities and business rules you will then create an ERD. Tasks: 1. For each entity provide the name, description, fields, data type, primary key, and foreign key. 2. For each direct entity type pair, provide the business rules. 3. Provide the ERD. Customer Table Customer ID, Last...

  • Create a view that will list the order number, order date, part number, part description, and...

    Create a view that will list the order number, order date, part number, part description, and item class for each part that makes up the order from the TAL database. TAL Database: Table_Name Column Name Column Type Column Length Nullable Key Orders Order_Num Character 5 No Primary orders order_date Date Yes Orders Customer_num Character 3 Yes Foreign Order_Line Order_Num Character 5 No Primary Order_Line Part_Num Character 4 No Primary Order_Line Num_Ordered Number 3,0 Yes Order_Line Quoted_Price Number 6,2 Yes Part...

  • Write the following SQL statements in Microsoft Access by using the Books database from Week 2...

    Write the following SQL statements in Microsoft Access by using the Books database from Week 2 Assignment 2. Once complete, copy and paste the SQL statements in a Microsoft Word document: Write SQL statements: To update the publisher name from READ WITH US to READ FOR US To verify the updated name field for the publisher with ID 6 To make the following updates to the Publisher table (be careful with WHERE): Make Contact="John Travolta" for publisher with ID number...

  • There are five categories of data to be stored in the database: Ingredients Ingredient Name Amount...

    There are five categories of data to be stored in the database: Ingredients Ingredient Name Amount in Stock Menu Items Menu Item Name Ingredients Regular Price Sale Price Customers Customer Name Customer Address Ingredients Allergic To Employees Social Security Number Employee Name Employee Address Hourly Wage Sales Data Transaction Date and Time Server (i.e. Employee) ID Customer ID Menu Items Sold Price Each Item Sold At 3. (30 points) Based on the relational model you provided as your answer to...

  • This problem is from Chapter 5 of A Guide to SQL ninth addition. 9. For each...

    This problem is from Chapter 5 of A Guide to SQL ninth addition. 9. For each item, list the item number, description, units on hand, order number, and number of units ordered. All items should be included in the results. For those items that are currently not on order, the order number and number of units ordered should be left blank. Order the results by item number. All answers I have come up with use the full outer join, however,...

  • Create the following SQL Server queries that access the Northwind database Same as problem 3 but,...

    Create the following SQL Server queries that access the Northwind database Same as problem 3 but, limit the list to all customers who placed 3 or more orders. The product id, product name, last date that the product was ordered for all items in the Grains/Cereals category. (Hint: Use MAX) The product ID, product name, and number of distinct customers who ordered that product in 1996 Thank you. Categories Customers Employees Order Details Orders Products Shippers 9 CategoryID CategoryName Description...

  • May I ask the SQL code as follows? The relational database moviedb has the following database...

    May I ask the SQL code as follows? The relational database moviedb has the following database schema: Movie(title, production year, country, run time, major genre) primary key : {title, production year} Person(id, first name, last name, year born) primary key : {id} Award(award name, institution, country) primary key : {award name} Restriction Category(description, country) primary key : {description, country} Director(id, title, production year) primary key : {title, production year} foreign keys : [title, production year] ⊆ Movie[title, production year] [id]...

  • Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL y...

    Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL you used to complete the task and the number of rows retrieved. Ensure you follow the coding standards listed in the ETSU SQL standards guide found on D2L. Save this document as a PDF and name it your_name_lab7.pdf Display the employees’ first name, last name and phone number of employees with salaries greater than 10,000 dollars. SELECT first_name, last_name, phone_number,...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT