Class, lets use the Northwind Database to create some views. You can find the script and ERD for the Northwind database in the Announcement area. Before posting any of these exercises, be sure to first execute the script and try out your View in MySQL.

3. Create a view named Animal_Products_V that contains all products in the Dairy products category, Meat/Poultry category, and seafood category only. But it should only have the category name and the product name. Display the data using this view.

DROP TABLE IF EXISTS Order_Details;

DROP TABLE IF EXISTS Orders;

DROP TABLE IF EXISTS Employees;

DROP TABLE IF EXISTS Shippers;

DROP TABLE IF EXISTS Customers;

DROP TABLE IF EXISTS Products;

DROP TABLE IF EXISTS Suppliers;

DROP TABLE IF EXISTS Categories;

Create Table Categories

(

CategoryID Int Primary key,

CategoryName Varchar(15),

Description Varchar(50)

);

insert into Categories Values

(1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),

(2,'Condiments','Sweet and savory sauces, relishes, spreads, and

seasonings'),

(3,'Confections','Desserts, candies, and sweet breads'),

(4,'Dairy Products','Cheeses'),

(5,'Grains/Cereals','Breads, crackers, pasta, and cereal'),

(6,'Meat/Poultry','Prepared meats'),

(7,'Produce','Dried fruit and bean curd'),

(8,'Seafood','Seaweed and fish');

Create Table Suppliers

(

SupplierID Int Primary Key,

CompanyName Varchar(40),

ContactName Varchar(30),

ContactTitle Varchar(30),

Address Varchar(30),

City Varchar(15),

Region Varchar(15),

ZipCode Varchar(10),

Country Varchar(20),

Phone Varchar(20)

);

Insert into Suppliers Values

(1,'Exotic Liquids','Charlotte Cooper','Purchasing Manager','49 Gilbert

St.','London',NULL,'EC1 4SD','UK','(171) 555-2222');

Insert into Suppliers Values

(2,'New Orleans Cajun Delights','Shelley Burke','Order

Administrator','P.O. Box 78934','New Orleans','LA','70117','USA','(100)

555-4822');

Insert into Suppliers Values

(3,'Grandma Kelly Homestead','Regina Murphy','Sales Representative','707

Oxford Rd.','Ann Arbor','MI','48104','USA','(313) 555-573');

Insert into Suppliers Values

(4,'Tokyo Traders','Yoshi Nagase','Marketing Manager','9-8

Sekimai','Tokyo',null,'100','Japan','(03) 3555-5011');

Insert into Suppliers Values

(5,'Cooperativa de Quesos Las Cabras','Antonio del Valle Saavedra

','Export Administrator','Calle del Rosal

4','Oviedo','Asturias','33007','Spain','(98) 598 76 54');

Insert into Suppliers Values

(6,'Mayumis','Mayumi Ohno','Marketing Representative','92

Setsuko','Osaka',null,'545' ,'Japan','(06) 431-7877');

Insert into Suppliers Values

(7,'Pavlova, Ltd.','Ian Devling','Marketing Manager','74 Rose

St.','Melbourne','Victoria','3058','Australia','(03) 444-2343');

Insert into Suppliers Values

(8,'Specialty Biscuits, Ltd.','Peter Wilson','Sales Representative','29

Kings Way','Manchester',null, 'M14 GSD','UK','(161) 555-4448');

Insert into Suppliers Values

(9,'PB Knckebrd AB','Lars Peterson','Sales Agent','Kaloadagatan

13','Gteborg',null,'S-345 67','Sweden ','031-987 65 43');

Insert into Suppliers Values

(10,'Refrescos Americanas LTDA','Carlos Diaz','Marketing Manager','Av. das

Americanas 12.890','So Paulo',null,'5442','Brazil','(11) 555 4640');

Create Table Products

(

ProductId int Primary key,

ProductName Varchar(40),

QuantityPerUnit Varchar(20),

UnitPrice Decimal(9,2),

UnitsInStock int,

UnitsOnOrder int,

ReOrderLevel int,

SupplierID int,

CategoryID int,

Constraint SupplierID_FK Foreign Key (SupplierID) references Suppliers

(SupplierID),

Constraint CategoryID_FK Foreign key (CategoryID) references Categories

(CategoryID)

);

Insert into Products values

(1,'Chai','10 boxes x 20 bags',18.00 ,39 ,0 ,10,10,1 );

Insert into Products values

(2,'Chang','24 - 12 oz bottles',19.00 ,17 ,40 ,29,9,2);

Insert into Products values

(3,'Aniseed Syrup','12 - 550 ml bottles',10.00,13 ,70 ,25,4,7);

Insert into Products values

(4,'Chef Antons Cajun Seasoning','48 - 6 oz jars',22.10 ,53 ,0 ,10,3,1);

Insert into Products values

(5,'Chef Antons Gumbo Mix','36 boxes',21.35,100,0,50,2,2);

Insert into Products values

(6,'Grandmas Boysenberry Spread','12 - 8 oz jars',25.00,120,0,25,5,2);

Insert into Products values

(7,'Uncle Bobs Organic Dried Pears','12 - 1 lb pkgs.',30.00,115,0,60,8,6);

Insert into Products values

(8,'Northwoods Cranberry Sauce','12 - 12 oz jars',40.00,116,0,80,5,2);

Insert into Products values

(9,'Mishi Kobe Niku','18 - 500 g pkgs.',97.00,129,80,200,7,3);

Insert into Products values

(10,'Ikura','12 - 200 ml jars',31.25,131,100,200,10,8);

Insert into Products values

(11,'Queso Cabrales','1 kg pkg.',21.75,122,80,150,1,3);

Insert into Products values

(12,'Queso Manchego La Pastora','10 - 500 g pkgs.',38.00,186,60,200,10,4);

Insert into Products values

(13,'Konbu','kg box',6,124,0,50,9,7);

Insert into Products values

(14,'Tofu','40 - 100 g pkgs.',23.25,135,0,60,8,2);

Insert into Products values

(15,'Genen Shouyu','24 - 250 ml bottles',15.50,139,0,100,8,3);

Insert into Products values

(16,'Pavlova','32 - 500 g boxes',17.45,129,100,200,4,3);

Insert into Products values

(17,'Alice Mutton','32 - 500 g boxes',17.45,129,40,130,7,7);

Insert into Products values

(18,'Carnarvon Tigers','16 kg pkg.',24.45,131,100,200,4,7);

Insert into Products values

(19,'Teatime Chocolate Biscuit','10 boxes x 12

pieces',85.45,167,0,100,3,1);

Insert into Products values

(20,'Sir Rodneys Marmalade','30 gift boxes',5.45,154,0,100,5,3);

Create Table Customers

(

CustomerID Varchar(5) Primary key,

CompanyName Varchar(40),

ContactName Varchar(30),

ContactTitle Varchar(30),

Address Varchar(40),

City Varchar(15),

Country Varchar(20),

phone varchar(15)

);

Insert into Customers values

('ALFKI','Alfreds Futterkiste','Maria Anders','Sales

Representative','Obere Str. 57','Berlin','Germany','030-0074321'),

('ANATR','Ana Trujillo Emparedados y helados','Ana

Trujillo','Owner','Avda. de la Constitucin 2222','Mxico

D.F.','Mexico','(5) 555-4729'),

('ANTON','Antonio Moreno Taquera','Antonio Moreno','Owner','Mataderos

2312','Mxico D.F.','Mexico','(5) 555-3932'),

('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120

Hanover Sq.','London','London','(171) 555-7788'),

('BSBEV','B Beverages','Victoria Ashworth','Sales

Representative','Fauntleroy Circus','London','UK','(171) 555-1212'),

('ERNSH','Ernst Handel','Roland Mendel','Sales Manager','Kirchgasse

6','Graz','Austria','7675-3425'),

('RANCH','Rancho grande','Sergio Gutirrez','Sales Representative','Av.

del Libertador 900','Buenos Aires','Argentina','(1) 123-5555'),

('RATTC','Rattlesnake Canyon Grocery','Paula Wilson','Assistant Sales

Representative','2817 Milton Dr.','Albuquerque NM','USA','(505) 555-

5939'),

('SPECD','Specialits du monde','Dominique Perrier','Dominique

Perrier','25, rue Lauriston','Paris','France','(1) 47.55.60.10'),

('SPLIR','Split Rail Beer & Ale','Art Braunschweiger','Sales

Manager','P.O. Box 555','Lander WY','USA','(307) 555-4680'),

('THEBI','The Big Cheese','Liz Nixon','Marketing Manager','89 Jefferson

Way','Portland OR','USA','(503) 555-361');

Create Table Shippers

(

ShipperID int primary key,

CompanyName Varchar(40),

Phone Varchar(24)

);

insert into Shippers values

(1,'Speedy Express','(503) 555-9831');

insert into Shippers values

(2,'United Package','(503) 555-3199');

insert into Shippers values

(3,'Federal Shipping','(503) 555-9931');

Create table Employees

(

EmployeeID int primary key,

LastName Varchar(20),

FirstName Varchar(15),

Title Varchar(30),

Hiredate date,

Address varchar(30),

City Varchar(15),

Country Varchar(15),

HomePhone Varchar(24)

);

insert into Employees values

(1,'Davolio','Nancy','Sales Representative','2009-01-03','507 - 20th Ave.

E.','Seattle','USA','(206) 555-9857'),

(2,'Fuller','Andrew','Vice President, Sales','2011-08-12','908 W. Capital

Way','Tacoma','USA','(206) 555-9482'),

(3,'Leverling','Janet','Sales Representative','2013-04-05','722 Moss Bay

Blvd.','Kirkland','USA','(206) 555-3412'),

(4,'Peacock','Margaret','Sales Representative','2013-04-10','4110 Old

Redmond Rd.','Redmond','USA','(206) 555-8122'),

(5,'Buchanan','Steven','Sales Manager','2015-04-15','14 Garrett

Hill','London','UK','(71) 555-4848'),

(6,'Suyama','Michael','Sales Representative','2016-04-21','Coventry

House','London','UK','(71) 555-7773');

Create Table Orders

(

OrderID int Primary key,

OrderDate date,

ShippedDate date,

TotalAmt decimal(9,2),

CustomerID Varchar(5),

ShipperID int,

EmployeeID int,

Constraint CustomerID_FK Foreign Key (CustomerID) references Customers

(CustomerID),

Constraint EmployeeID_FK Foreign key (EmployeeID) references Categories

(CategoryID),

Constraint ShipperID_FK Foreign Key (ShipperID) references Shippers

(ShipperID)

);

Insert into Orders values

(10248,'2019-01-15','2019-01-16', 9275,'RANCH',2,6);

Insert into Orders values

(10249,'2019-01-15','2019-01-16', 2487.5,'AROUT',1,6);

Insert into Orders values

(10250,'2019-01-16','2019-01-17',1046.25,'THEBI',1,6);

Insert into Orders values

(10251,'2019-01-23','2019-01-24',062.5 ,'THEBI',2,5);

Insert into Orders values

(10252,'2019-02-07',null,751.40,'RATTC',2,2);

Insert into Orders values

(10253,'2019-02-07',null,12475,'BSBEV',2,1);

Insert into Orders values

(10254,'2019-02-07',null,20145,'ERNSH',3,2);

Create Table Order_Details

(

OrderID int,

ProductID int,

Quantity int,

Constraint OrderDetails_PK Primary Key (OrderID, ProductID),

Constraint OrderID_FK Foreign Key (OrderID) References Orders (OrderID),

Constraint ProductID_FK Foreign Key (ProductID) References Products

(ProductID)

);

insert into Order_Details values

(10248,15,100),

(10248,13,200),

(10248,11,300),

(10249,10,10),

(10249,11,100),

(10250,14,45),

(10251,14,50),

(10251,2,100),

(10252,4,34),

(10253,4,100),

(10253,1,200),

(10253,6,355),

(10254,10,500),

(10254,11,1000),

(10254,14,100),

(10254,3,2);

Suppliers Categories SupplierD Categor D CompanyName Shippers ShippentD Products Customers stomerlO SupplierlD Employees Orders mployeelD OrderD Last Na me rst Name ShippedDate ustomer Shippent Order Details O rderD Product D Quantty