Using sql

The following two questions are based on the tables

dbo.vendors

dbo.invoices

Question1:

Write a SELECT statement that returns all columns from dbo.vendors

and dbo.invoices by joining Vendors table and Invoices table with an inner join

Question2:

Write a SELECT Statement that returns four columns

VendorName From the Vendors table

InvoiceNumber From the Invoices table

InvoiceDate From the Invoices table

Balance InvoiceTotal minus the sum of PaymentTotal and CreditTotal from invoice table

The result set should have one row for each invoice with a non-zero balance. Sort the result set by VendorName in ascending order.

The following 6 questions are based on the tables

RainbowRetail.RRproduct;

RainbowRetail.RRvendor;

RainbowRetail.RRcategory;

/* Execute the script below to answer the following 8 questions*/

Create schema RainbowRetail;

DROP TABLE RainbowRetail.RRproduct;

DROP TABLE RainbowRetail.RRvendor;

DROP TABLE RainbowRetail.RRcategory;

CREATE TABLE RainbowRetail.RRvendor

(

vendorid CHAR(2) NOT NULL,

vendorname VARCHAR(25) NOT NULL,

PRIMARY KEY (vendorid)

);

CREATE TABLE RainbowRetail.RRcategory

(

categoryid CHAR(2) NOT NULL,

categoryname VARCHAR(25) NOT NULL,

PRIMARY KEY (categoryid)

);

CREATE TABLE RainbowRetail.RRproduct

(

productid CHAR(3) NOT NULL,

productname VARCHAR(25) NOT NULL,

productprice NUMERIC (7,2) NOT NULL,

vendorid CHAR(2) NULL,

categoryid CHAR(2) NULL,

PRIMARY KEY (productid),

FOREIGN KEY (vendorid) REFERENCES RainbowRetail.RRvendor(vendorid),

FOREIGN KEY (categoryid) REFERENCES RainbowRetail.RRcategory(categoryid)

);

INSERT INTO RainbowRetail.RRvendor VALUES

('PG','Pacifica Gear'),

('MK','Mountain King'),

('DY','Disney'),

('CR','Cryola'),

('PM','Paper Mate');

INSERT INTO RainbowRetail.RRcategory VALUES

('CP','Camping'),

('FW','Footwear'),

('TY','Toys'),

('SS','School Supplies'),

('OS','Office Supplies');

INSERT INTO RainbowRetail.RRproduct VALUES

('101','Zzz Bag',100,'PG','CP'),

('102','Easy Boot',70,'MK','FW'),

('103','Cosy Sock',15,'MK','FW'),

('104','Dura Boot',90,'PG','FW'),

('105','Tiny Tent',150,'MK','CP'),

('106','Biggy Tent',250,'MK','CP'),

('107','Sweater',40,'DY',null),

('108','Pens',5,null,'SS'),

('109','Aveeno-Moisturizer',14,null,null),

('110','Printer',200,null,'OS'),

('111','Clock',20,null,null);

Question3:

Use update query to change the price of product number 107 in the RRproduct table to 30 dollars

Question 4:

The name of the vendor 'Pacifica Gear' has changed to 'Pacifica Inc'.

Reflect the change using update statements

Question 5:

Delete record number 111 from the RRproduct table

Question 6:

Use update statement to double the price of all products belonding

to the vendor 'Mountain King'in the products table

Question 7:

Write a select Query with inner joins to retrieve the Product id, product name, product price, vendorid,vendor name, categoryid,category name of all the products belonging to the vendor 'Mountain King'

Question 8:

Use Implicit inner join syntax to retrieve the columns Product id, product name, product price, vendorid,vendor name categoryid, and category name of all the products belonging to the vendor 'Mountain King'