Question

Given the SQL table

• Select the prices that occur in the alp_inventory table. A specific price should only appear once. Display the Price.
• Select the alp_inventory items that are in stock. Display Id, Price and Quantity on Hand
• List the alp_inventory items that are ‘Coral’ or ‘Olive’ and have a Quantity on Hand of less than 105. Display Id and Quantity on Hand

CREATE TABLE alp_inventory
(inv_id int primary key auto_increment not null,
item_id int not null,
item_size varchar(2),
alp_color varchar(15),
price decimal(6,2) not null,
quantity_on_hand int not null);


INSERT INTO alp_inventory
VALUES(null, 5, null, 'Sienna', 274.99, 14);
INSERT INTO alp_inventory
VALUES(null, 5, null, 'Forest', 274.99, 8);
INSERT INTO alp_inventory
VALUES(null, 1, 'S', 'Khaki', 32.95, 57);
INSERT INTO alp_inventory
VALUES(null, 1, 'M', 'Khaki', 32.95, 89);
INSERT INTO alp_inventory
VALUES(null, 1, 'L', 'Khaki', 32.95, 0);
INSERT INTO alp_inventory
VALUES(null, 1, 'S', 'Olive', 32.95, 110);
INSERT INTO alp_inventory
VALUES(null, 1, 'M', 'Olive', 32.95, 51);
INSERT INTO alp_inventory
VALUES(null, 1, 'L', 'Olive', 32.95, 23);
INSERT INTO alp_inventory
VALUES(null, 2, 'S', 'Teal', 64.95, 112);
INSERT INTO alp_inventory
VALUES(null, 2, 'M', 'Teal', 64.95, 37);
INSERT INTO alp_inventory
VALUES(null, 2, 'L', 'Teal', 64.95, 125);
INSERT INTO alp_inventory
VALUES(null, 2, 'S', 'Coral', 64.95, 0);
INSERT INTO alp_inventory
VALUES(null, 2, 'M', 'Coral', 64.95, 86);
INSERT INTO alp_inventory
VALUES(null, 2, 'L', 'Coral', 64.95, 140);
INSERT INTO alp_inventory
VALUES(null, 3, '10', 'Blue', 15.99, 78);
INSERT INTO alp_inventory
VALUES(null, 3, '11', 'Blue', 15.99, 86);
INSERT INTO alp_inventory
VALUES(null, 3, '12', 'Blue', 15.99, 23);
INSERT INTO alp_inventory
VALUES(null, 3, '6', 'Blue', 15.99, 89);
INSERT INTO alp_inventory
VALUES(null, 3, '10', 'Red', 15.99, 56);
INSERT INTO alp_inventory
VALUES(null, 3, '11', 'Red', 15.99, 35);
INSERT INTO alp_inventory
VALUES(null, 3, '12', 'Red', 15.99, 84);
INSERT INTO alp_inventory
VALUES(null, 3, '6', 'Red', 15.99, 0);
INSERT INTO alp_inventory
VALUES(null, 4, 'S', 'Green', 199.95, 92);
INSERT INTO alp_inventory
VALUES(null, 4, 'M', 'Green', 199.95, 17);
INSERT INTO alp_inventory
VALUES(null, 4, 'L', 'Green', 209.95, 0);
INSERT INTO alp_inventory
VALUES(null, 4, 'XL', 'Green', 209.95, 12);

Answer 1

Solution:

Create table:

CREATE TABLE alp_inventory (inv_id int primary key auto_increment not null,item_id int not null,item_size varchar(2),
alp_color varchar(15),price decimal(6,2) not null,quantity_on_hand int not null);

Insert values into table:

INSERT INTO alp_inventory VALUES(null, 5, null, 'Sienna', 274.99, 14);
INSERT INTO alp_inventory VALUES(null, 5, null, 'Forest', 274.99, 8);
INSERT INTO alp_inventory VALUES(null, 1, 'S', 'Khaki', 32.95, 57);
INSERT INTO alp_inventory VALUES(null, 1, 'M', 'Khaki', 32.95, 89);
INSERT INTO alp_inventory VALUES(null, 1, 'L', 'Khaki', 32.95, 0);
INSERT INTO alp_inventory VALUES(null, 1, 'S', 'Olive', 32.95, 110);
INSERT INTO alp_inventory VALUES(null, 1, 'M', 'Olive', 32.95, 51);
INSERT INTO alp_inventory VALUES(null, 1, 'L', 'Olive', 32.95, 23);
INSERT INTO alp_inventory VALUES(null, 2, 'S', 'Teal', 64.95, 112);
INSERT INTO alp_inventory VALUES(null, 2, 'M', 'Teal', 64.95, 37);
INSERT INTO alp_inventory VALUES(null, 2, 'L', 'Teal', 64.95, 125);
INSERT INTO alp_inventory VALUES(null, 2, 'S', 'Coral', 64.95, 0);
INSERT INTO alp_inventory VALUES(null, 2, 'M', 'Coral', 64.95, 86);
INSERT INTO alp_inventory VALUES(null, 2, 'L', 'Coral', 64.95, 140);
INSERT INTO alp_inventory VALUES(null, 3, '10', 'Blue', 15.99, 78);
INSERT INTO alp_inventory VALUES(null, 3, '11', 'Blue', 15.99, 86);
INSERT INTO alp_inventory VALUES(null, 3, '12', 'Blue', 15.99, 23);
INSERT INTO alp_inventory VALUES(null, 3, '6', 'Blue', 15.99, 89);
INSERT INTO alp_inventory VALUES(null, 3, '10', 'Red', 15.99, 56);
INSERT INTO alp_inventory VALUES(null, 3, '11', 'Red', 15.99, 35);
INSERT INTO alp_inventory VALUES(null, 3, '12', 'Red', 15.99, 84);
INSERT INTO alp_inventory VALUES(null, 3, '6', 'Red', 15.99, 0);
INSERT INTO alp_inventory VALUES(null, 4, 'S', 'Green', 199.95, 92);
INSERT INTO alp_inventory VALUES(null, 4, 'M', 'Green', 199.95, 17);
INSERT INTO alp_inventory VALUES(null, 4, 'L', 'Green', 209.95, 0);
INSERT INTO alp_inventory VALUES(null, 4, 'XL', 'Green', 209.95, 12);

Query 1:

select distinct price from alp_inventory;

Explanation:

By using distinct keyword we can print the unique values.

Query 2:

select item_id,price,quantity_on_hand from alp_inventory;

Query 3:

select item_id,quantity_on_hand from alp_inventory where alp_color in ('Coral','Olive') and quantity_on_hand <105;

Explanation:

By using IN Keyword we are checking color of item is olive or coral and checking quantity is less than 105 or not.

Code and Output Screenshots:

Note : if you have any queries please post a comment thanks a lot..always available to help you....