From TAL database table that you have downloaded from module 2 . Answer the foll

ID: 669091 • Letter: F

Question

From TAL database table that you have downloaded from module 2 . Answer the following questions using SQL command.

Submit this file using the word document format. Just write the SQL statements for question 6-11

1. How do you write a compound condition in an SQL query? When is a compound condition true?

2. What is a computed field? How can you use one in an SQL query? How do you assign a name to a computed field?

3. How do you sort data in SQL? When there is more than one sort key, how do you indicate which one is the major and minor sort keys.

4. What is a major sort key? How do you sort data in descending order?

5. What are the SQL built-in functions? How do you use them in an SQL query?

6. list all columns and all rows in the Item table. Sort the results by Item description.

7. list all columns and all rows in the Item table. Sort the results by item number within category.

8. List the category and the sum of the number of items on hand. Group the results by category, and order the records by category.

9. Create a new table named Toy to contain the columns ItemNum, Description, OnHand, and Price for all rows on which the category is TOY.

10. In the Toy table, change the description of item DL51 to “Classic Train Set.”

11. .In the Toy table, delete every row on which the price is greater than $120

*Also TurnitIn is enabled

As you asked me to answer only queries from 6 – 11, here are the answers:

6. list all columns and all rows in the Item table. Sort the results by Item description.

SELECT * FROM Item ORDER BY Description;

7. list all columns and all rows in the Item table. Sort the results by item number within category.

SELECT * FROM Item ORDER BY Category, ItemNum;

8. List the category and the sum of the number of items on hand. Group the results by category, and order the records by category.

SELECT Category, Sum(OnHand) FROM Item GROUP BY Category ORDER BY Category;

9. Create a new table named Toy to contain the columns ItemNum, Description, OnHand, and Price for all rows on which the category is TOY.

CREATE TABLE Toy (ItemNum char(4), Description varchar2(50), OnHand number, Price number(8,2));

10. In the Toy table, change the description of item DL51 to “Classic Train Set.”

UPDATE Toy SET Description = “Classic Train Set” WHERE ItemNum = “DL51”;

11. .In the Toy table, delete every row on which the price is greater than $120

DELETE FROM Toy WHERE Price > 120;

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.