Please show in SQL code INPUT& OUTPUT (Screenshots) For each of the assigned pro

Question

Please show in SQL code

INPUT& OUTPUT (Screenshots)

For each of the assigned problems, write a SQL query to retrieve the required information and test the query in MySQL

Premiere Products Database: Problems 1-10

Exercises Premiere Products Use MySQL to make the following changes to the Premiere Products database (see Figure 1-2 in Chapter 1). After each change, execute an appropriate query to show that the change was made correctly. Use the notes at the end of Chapter 3 to print your output if directed to do so by your instructor 1. Create a NONAPPLIANCE table with the structure shown in Figure 6-18. NONAPPLIANCE Length Decimal Places Nulls Allowed? Description Column PART_NUM DESCRIPTION CHAR ON HAND CLASS PRICE Type No Part number (primary key) Part description Number of units on hand Item class Unit price CHAR 15 DECIMAL CHAR DECIMAL FIGURE 6-18 NONAPPLIANCE table layout 2. Insert into the NONAPPLIANCE table the part number, part description, number of units on hand, item class, and unit price from the PART table for each part that is not in item class AP 3. 4. In the NONAPPLIANCE table, change the description of part number AT94 to "Deluxe Iron." In the NONAPPLIANCE table, increase the price of each item in item class SG by 2%. (Hint Multiply each price by 1.02.) 5. Add the following part to the NONAPPLIANCE table: part number: TL92; description: Trimmer; number of units on hand: 11; class: HW; and price: 29.95. 6. Delete every part in the NONAPPLIANCE table for which the class is SG. 7. In the NONAPPLIANCE table, change the class for part FD21 to null. 8. Add a column named ON HAND VALUE to the NON APPLIANCE table. The on-hand value is a seven-digit number with two decimal places that represents the product of the number of units on hand and the price. Then set all values of ON_HAND_VALUE to ON_HAND PRICE 9. In the NONAPPLIANCE table, increase the length of the DESCRIPTION column to 30 characters. 10. Remove the NONAPPLIANCE table from the Premiere Products database.

Explanation / Answer

Solution:

1. SQL QUERY:

mysql> CREATE TABLE NONAPPLIANCE
-> (PART_NUM CHAR(4) PRIMARY KEY NOT NULL,
-> DESCRIPTION CHAR(15),
-> ON_HAND DECIMAL(4,0),
-> CLASS CHAR(2),
-> PRICE DECIMAL(6,2) );

Output:

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW COLUMNS
-> FROM NONAPPLIANCE;
+-------------+--------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+-------------+--------------+------+-----+---------+-------+
PART_NUM char(4) NO PRI
DESCRIPTION char(15) YES NULL
ON_HAND decimal(4,0) YES NULL
CLASS char(2) YES NULL
PRICE decimal(6,2) YES NULL
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------

2. SQL QUERY:

mysql> INSERT INTO NONAPPLIANCE
-> SELECT PART_NUM, DESCRIPTION, ON_HAND, CLASS, PRICE
-> FROM PART
-> WHERE NOT (CLASS = 'AP');

output:

Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+---------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+---------+
AT94 Iron 50 HW 24.95
BV06 Home Gym 45 SG 794.95
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 HW 159.95
KV29 Treadmill 9 SG 1390.00
+----------+----------------+---------+-------+---------+
5 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------------

3. SQL QUERY:

mysql> UPDATE NONAPPLIANCE
-> SET DESCRIPTION = 'Deluxe Iron'
-> WHERE PART_NUM = 'AT94';

Output:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+---------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+---------+
AT94 Deluxe Iron 50 HW 24.95
BV06 Home Gym 45 SG 794.95
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 HW 159.95
KV29 Treadmill 9 SG 1390.00
+----------+----------------+---------+-------+---------+
5 rows in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------

4. SQL QUERY:

mysql> UPDATE NONAPPLIANCE
-> SET PRICE = (PRICE*1.02)
-> WHERE CLASS = 'SG';

Output:

Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 1

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+---------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+---------+
AT94 Deluxe Iron 50 HW 24.95
BV06 Home Gym 45 SG 810.85
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 HW 159.95
KV29 Treadmill 9 SG 1417.80
+----------+----------------+---------+-------+---------+
5 rows in set (0.00 sec)

---------------------------------------------------------------------------------------------------------------------------------------------------

5. SQL QUERY:

mysql> INSERT INTO NONAPPLIANCE
-> VALUES
-> ('TL92', 'Trimmer','11', 'HW', 29.95);

Output:
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+---------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+---------+
AT94 Deluxe Iron 50 HW 24.95
BV06 Home Gym 45 SG 810.85
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 HW 159.95
KV29 Treadmill 9 SG 1417.80
TL92 Trimmer 11 HW 29.95
+----------+----------------+---------+-------+---------+
6 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------

6. SQL QUERY:

mysql> DELETE FROM NONAPPLIANCE
-> WHERE CLASS = 'SG';

Output:
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+--------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+--------+
AT94 Deluxe Iron 50 HW 24.95
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 HW 159.95
TL92 Trimmer 11 HW 29.95
+----------+----------------+---------+-------+--------+
4 rows in set (0.00 sec)

-------------------------------------------------------------------------------------------------------------------------------------------------------

7. SQL QUERY:

mysql> UPDATE NONAPPLIANCE
-> SET CLASS = NULL
-> WHERE PART_NUM = 'FD21';

Output:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+--------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE
+----------+----------------+---------+-------+--------+
AT94 Deluxe Iron 50 HW 24.95
DL71 Cordless Drill 21 HW 129.95
FD21 Stand Mixer 22 NULL 159.95
TL92 Trimmer 11 HW 29.95
+----------+----------------+---------+-------+--------+
4 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------------------

8. SQL QUERY:

Alter Table:

mysql> ALTER TABLE NONAPPLIANCE
-> ADD ON_HAND_VALUE DECIMAL(7,2);

Output:
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS
-> FROM NONAPPLIANCE;
+---------------+--------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------------+--------------+------+-----+---------+-------+
PART_NUM char(4) NO PRI
DESCRIPTION char(15) YES NULL
ON_HAND decimal(4,0) YES NULL
CLASS char(2) YES NULL
PRICE decimal(6,2) YES NULL
ON_HAND_VALUE decimal(7,2) YES NULL
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Update statement:

mysql> UPDATE NONAPPLIANCE
-> SET * PRICE);

Output:
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> SELECT * FROM NONAPPLIANCE;
+----------+----------------+---------+-------+--------+---------------+
PART_NUM DESCRIPTION ON_HAND CLASS PRICE ON_HAND_VALUE
+----------+----------------+---------+-------+--------+---------------+
AT94 Deluxe Iron 50 HW 24.95 1247.50
DL71 Cordless Drill 21 HW 129.95 2728.95
FD21 Stand Mixer 22 NULL 159.95 3518.90
TL92 Trimmer 11 HW 29.95 329.45
+----------+----------------+---------+-------+--------+---------------+
4 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------------------

9. SQL QUERY:

mysql> ALTER TABLE NONAPPLIANCE
-> MODIFY DESCRIPTION CHAR(30);

Output:
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS
-> FROM NONAPPLIANCE;
+---------------+--------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------------+--------------+------+-----+---------+-------+
PART_NUM char(4) NO PRI
DESCRIPTION char(30) YES NULL
ON_HAND decimal(4,0) YES NULL
CLASS char(2) YES NULL
PRICE decimal(6,2) YES NULL
ON_HAND_VALUE decimal(7,2) YES NULL
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------------

10. SQL QUERY:

mysql> DROP TABLE NONAPPLIANCE;

Output:
Query OK, 0 rows affected (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------

Please upvote if you are happy with the answer

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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