exercise-must-be-completed- Nursingresearchtutors

–Just run the script below

CREATE TABLE Manufacturers (

Code INTEGER,

Name VARCHAR(255) NOT NULL,

PRIMARY KEY (Code)

);

CREATE TABLE Products (

Code INTEGER,

Name VARCHAR(255) NOT NULL ,

Price DECIMAL NOT NULL ,

Manufacturer INTEGER NOT NULL,

PRIMARY KEY (Code),

FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)

) ;

INSERT INTO Manufacturers(Code,Name) VALUES(1,’Sony’);

INSERT INTO Manufacturers(Code,Name) VALUES(2,’Creative Labs’);

INSERT INTO Manufacturers(Code,Name) VALUES(3,’Hewlett-Packard’);

INSERT INTO Manufacturers(Code,Name) VALUES(4,’Iomega’);

INSERT INTO Manufacturers(Code,Name) VALUES(5,’Fujitsu’);

INSERT INTO Manufacturers(Code,Name) VALUES(6,’Winchester’);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,’Hard drive’,240,5);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,’Memory’,120,6);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,’ZIP drive’,150,4);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,’Floppy disk’,5,6);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,’Monitor’,240,1);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,’DVD drive’,180,2);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,’CD drive’,90,2);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,’Printer’,270,3);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,’Toner cartridge’,66,3);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,’DVD burner’,180,2);

Perform following tasks:

Some of the following queries require join condition.

Select A.Col1,B.Col1â€¦.

From Table A, Table B

Where A.Joinkey=B.JoinKey

Select the name and price of all products with a price larger than or equal to $180, and sort first by price
Compute the number of products with a price larger than or equal to $180
Compute the average price of all the products
Compute the average price of all products with manufacturer code equal to 2
Select the average price of each manufacturer’s products, showing only the manufacturer’s code â€“hint group by manufacturer’s code

exercise-must-be-completed-