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

Source: Internet
Author: User

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 );

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.