• Create a new table Product and populate it with data

DROP TABLE IF EXISTS Product;

CREATE TABLE Product (
productCode varchar(10) PRIMARY KEY,
Description varchar(35) not null,
inDate date not null,
quantityOnHand smallint not null,
minQuantityOnHand smallint not null,
Price decimal(8, 2),
discountRate decimal(2, 2) not null DEFAULT 0.00,
vendorCode int
);

• Insert the following products

INSERT INTO Product (productCode, description, inDate, quantityOnHand, minQuantityOnHand, price, discountRate, vendorCode) VALUES
('11QER/31', 'Power painter, 15 psi., 3-nozzle', '2012-11-03', 8, 5, 109.99, 0.00, 25595),
('13-Q2/P2', '7.25-cm. pwr. saw blade', '2012-12-13', 32, 15,  14.99, 0.05, 21344),
('14-Q1/L3', '9.00-cm. pwr. saw blade', '2012-11-13',  18, 12,  17.49, 0.00, 21344),
('1546-QQ2', 'Hrd. cloth, 1/4-cm., 2x50', '2013-01-15',  15, 8, 39.95, 0.00, 23119),
('1558-QW1', 'Hrd. cloth, 1/2-cm., 3x50', '2013-01-15',  23, 5, 43.99, 0.00, 23119),
('2232/QTY', 'B\\&D jigsaw, 12-cm. blade', '2012-12-30', 8, 5, 109.92, 0.05, 24288),
('2232/QWE', 'B\\&D jigsaw, 8-cm. blade', '2012-12-24', 6, 5, 99.87, 0.05, 24288),
('2238/QPD', 'B\\&D cordless drill, 1/2-cm.', '2012-01-20', 12, 5, 38.95, 0.05, 25595),
('23109-HB', 'Claw hammer', '2012-01-20',  23, 10, 9.95, 0.10, 21225),
('23114-AA', 'Sledge hammer, 6kg.', '2012-01-02', 8, 5, 14.40, 0.05,  NULL),
('54778-2T', 'Rat-tail file, 1/8-cm. fine', '2013-12-15', 43, 20, 4.99, 0.00, 21344),
('89-WRE-Q', 'Hicut chain saw, 16 cm.', '2013-02-07', 11, 5, 256.99, 0.05, 24288),
('PVC23DRT', 'PVC pipe, 3.5-cm., 2m', '2013-02-20', 188, 75, 5.87, 0.00,  NULL),
('SM-18277', '1.25-cm. metal screw, 25','2013-03-01', 172, 75, 6.99, 0.00, 21225),
('SW-23116', '2.5-cm. wd. screw, 50', '2013-02-24', 237, 100, 8.45, 0.00, 21231),
('WR3/TT3' , 'Steel matting, 4x8x1/6cm,5m mesh', '2013-01-17', 18, 5, 119.95, 0.10, 25595);

• Delete all the data that exists in the table Vendor. (DO NOT DELETE THE TABLE)
• Insert the following Vendors.

INSERT INTO Vendor (vendorCode, name, contactPerson, areacode, phone, country, previousOrder) VALUES
(21225, 'Bryson, Inc.', 'Smithson', '0181', '223-3234', 'UK', 'Y'),
(21226, 'SuperLoo, Inc.', 'Flushing', '0113', '215-8995', 'SA', 'N'),
(21231, 'D\\&E Supply', 'Singh' , '0181', '228-3245', 'UK', 'Y'),
(21344, 'Gomez Bros.', 'Ortega' , '0181', '889-2546', 'UK', 'N'),
(22567, 'Dome Supply', 'Smith' , '7253', '678-1419', 'FR', 'N'),
(23119, 'Randsets Ltd.', 'Anderson', '7253', '678-3998', 'FR', 'Y'),
(24004, 'Brackman Bros.', 'Browning', '0181', '228-1410', 'UK', 'N'),
(24288, 'ORDVA, Inc.', 'Hakford' , '0181', '898-1234', 'UK', 'Y'),
(25443, 'B\\&K, Inc.', 'Smith' , '0113', '227-0093', 'SA', 'N'), 
(25501, 'Damal Supplies', 'Smythe' , '0181', '890-3529', 'UK', 'N'),
(25595, 'Rubicon Systems', 'Orton' , '0113', '456-0092', 'SA', 'Y');

AND Operator

• Write the SQL statement to list the productcode and price of products in inventory starting January 2012 while the price is within the range 100-500.
• Generate the list of products supplied by the vendor whose code 25595 and whose price is more than $100
• Write the SQL statement to display all the products that are supplied by vendors with a unit price higher than 200.

OR Operator

• Write the SQL statement to list the products having a quantity on hand less than 10 or the products having no discount.
• Write the SQL statement to list the products supplied by vendors 21344 and 24288, which cost less than $100.00.

AS keyword (aliases)

An alias is an alternate name that can be assigned to a table or a column temporarily in an SQL statement. The AS keyword is used to rename a column using an alias. The alias is only used for the query and ceases to exist afterward.

Syntax:

SELECT column1 AS column_alias, column2, ... FROM tbl_name
[WHERE where_condition];

-- You can also express the same thing without using `AS` (most of the time)

SELECT column1 column_alias, column2, ... FROM tbl_name
[WHERE where_condition];

• Display information about vendors using the alias Country Code for the country column and the alias Contact Person for the contactperson column.