SQL-database materials used for basic learning, SQL-basic database materials

SQL-database materials used for basic learning, SQL-basic database materials

 

------------------------
-- Create MERs table
------------------------
Create table MERs
(
Cust_id char (10) not null,
Cust_name char (50) not null,
Cust_address char (50) NULL,
Cust_city char (50) NULL,
Cust_state char (5) NULL,
Cust_zip char (10) NULL,
Cust_country char (50) NULL,
Cust_contact char (50) NULL,
Cust_email char (255) NULL
);

-------------------------
-- Create OrderItems table
-------------------------
Create table OrderItems
(
Order_num int not null,
Order_item int not null,
Prod_id char (10) not null,
Quantity int not null,
Item_price decimal (8, 2) NOT NULL
);

---------------------
-- Create Orders table
---------------------
Create table Orders
(
Order_num int not null,
Order_date datetime not null,
Cust_id char (10) NOT NULL
);

-----------------------
-- Create Products table
-----------------------
Create table Products
(
Prod_id char (10) not null,
Vend_id char (10) not null,
Prod_name char (255) not null,
Prod_price decimal (8, 2) not null,
Prod_desc text NULL
);

----------------------
-- Create Vendors table
----------------------
Create table Vendors
(
Vend_id char (10) not null,
Vend_name char (50) not null,
Vend_address char (50) NULL,
Vend_city char (50) NULL,
Vend_state char (5) NULL,
Vend_zip char (10) NULL,
Vend_country char (50) NULL
);

---------------------
-- Define primary keys
---------------------
Alter table Customers add primary key (cust_id );
Alter table OrderItems add primary key (order_num, order_item );
Alter table Orders add primary key (order_num );
Alter table Products add primary key (prod_id );
Alter table Vendors add primary key (vend_id );

---------------------
-- Define foreign keys
---------------------
Alter table OrderItems add constraint FK_OrderItems_Orders foreign key (order_num) REFERENCES Orders (order_num );
Alter table OrderItems add constraint FK_OrderItems_Products foreign key (prod_id) REFERENCES Products (prod_id );
Alter table Orders add constraint fk_orders_mers MERs foreign key (cust_id) REFERENCES MERs (cust_id );
Alter table Products add constraint FK_Products_Vendors foreign key (vend_id) REFERENCES Vendors (vend_id );

 

 

 

--------------------------
-- Populate Customers table
--------------------------
Insert into MERs (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES ('123', 'village Toys ', '1970 Maple Lane', 'detail', 'mi', '123', 'usa', 'John Smith ', 'sales @ languagetoys.com ');
Insert into MERs (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES ('000000', 'kids' Place ', '2017 South Lake Drive', 'columbus', 'Oh', '000000', 'usa', 'michelle green ');
Insert into MERs (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES ('000000', 'fun4all', '1 Sunny Place ', 'muncie', 'in', '000000', 'usa', 'Jim Jones ', 'jjones @ fun4all.com ');
Insert into MERs (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES ('20140901', 'fun4all', '1997 Riverside Drive ', 'phoenix', 'az', '20160901', 'usa', 'Denise L. ');
Insert into MERs (cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES ('123', 'toy store', '1970 53rd Street ', 'Chicago', 'Il ', '123', 'usa', 'Kim Howard ');

------------------------
-- Populate Vendors table
------------------------
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('brs01', 'bears R Us', '1970 Main Street ', 'bear Town', 'mi', '123', 'usa ');
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('bre02', 'bear Emporium ', '1970 Park Street', 'anytown', 'Oh', '123', 'usa ');
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('dl01', 'doll House Inc. ', '1970 High Street', 'domains', 'CA', '123', 'usa ');
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('frb01 ', 'furball Inc.', '1970 5th Avenue ', 'New York', 'ny ', '123', 'USA ');
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('fng01', 'fun and Games ', '42 Galaxy road', 'London', NULL, 'n16 6PS ', 'England ');
Insert into Vendors (vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('jts01', 'jouets et ours', '1 Rue Amusement ', 'Paris', NULL, '123', 'France ');

-------------------------
-- Populate Products table
-------------------------
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('br01', 'br01', '1970 teddy bear ', 8 inch, '1970 teddy bear, comes with cap and jacket ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('br02', 'brs01', '12 inch teddy bear ', 8.99, '12 inch teddy bear, comes with cap and jacket ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('br03', 'brs01', '18 inch teddy bear ', 11.99, '18 inch teddy bear, comes with cap and jacket ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('bnbg01', 'dl01', 'fish bean bag toy', 3.49, 'fish bean bag toy, complete with bean bag worms with which to feed it ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('bnbg02', 'dl01', 'bird bean bag toy', 3.49, 'bird bean bag toy, eggs are not supported ded ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('bnbg03', 'dl01', 'rabbit bean bag toy', 3.49, 'rabbit bean bag toy, comes with bean bag carrots ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('rgan01', 'dl01', 'raggedy Ann ', 4.99, '18 inch Raggedy Ann doll ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('ryl01', 'fng01', 'king doll ', 9.49, '12 inch King doll with royal garments and crown ');
Insert into Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('ryl02', 'fng01', 'Queen doll ', 9.49, '12 inch Queen doll with royal garments and crown ');

-----------------------
-- Populate Orders table
-----------------------
Insert into Orders (order_num, order_date, cust_id)
VALUES (20005, '2014-05-01 ', '20140901 ');
Insert into Orders (order_num, order_date, cust_id)
VALUES (20006, '2017-01-12 ', '20170101 ');
Insert into Orders (order_num, order_date, cust_id)
VALUES (20007, '2017-01-30 ', '20170101 ');
Insert into Orders (order_num, order_date, cust_id)
VALUES (20008, '2017-02-03 ', '20170901 ');
Insert into Orders (order_num, order_date, cust_id)
VALUES (20009, '2017-02-08 ', '20170901 ');

---------------------------
-- Populate OrderItems table
---------------------------
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20005, 1, 'br01', 100, 5.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20005, 2, 'br03', 100, 10.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 1, 'br01', 20, 5.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 2, 'br02', 10, 8.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 3, 'br03', 10, 11.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 1, 'br03', 50, 11.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 2, 'bnbg01 ', 100, 2.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 3, 'bnbg02', 100, 2.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 4, 'bnbg03', 100, 2.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 5, 'rgan01', 50, 4.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 1, 'rgan01', 5, 4.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 2, 'br03', 5, 11.99 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 3, 'bnbg01 ', 10, 3.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 4, 'bnbg02', 10, 3.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 5, 'bnbg03', 10, 3.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 1, 'bnbg01 ', 250, 2.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 2, 'bnbg02', 250, 2.49 );
Insert into OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 3, 'bnbg03', 250, 2.49 );