SQL DATABASE

-- Create database

MIS database

-- Create table

create table customers (

customerid int primary key,

customername varchar(100),

contactname varchar(100),

address varchar(200),

city varchar(100),

postalcode varchar(20),

country varchar(50)

);

-- Insert sample records

insert into customers (customerid, customername, contactname, address, city, postalcode, country)
values

(1, 'alpine traders', 'john smith', '123 mountain rd', 'denver', '80202', 'usa'),

(2, 'oceanic supplies', 'linda johnson', '456 ocean ave', 'san diego', '92101', 'usa'),

(3, 'tech haven', 'michael brown', '789 silicon blvd', 'san francisco', '94107', 'usa'),

(4, 'global goods', 'emma davis', '12 commerce st', 'new york', '10001', 'usa'),

(5, 'sunrise markets', 'olivia wilson', '34 sunrise dr', 'los angeles', '90001', 'usa'),

(6, 'maple stores', 'ethan moore', '56 maple ave', 'toronto', 'm5h 2n2', 'canada'),

(7, 'nordic imports', 'sophia taylor', '78 fjord st', 'stockholm', '11122', 'sweden'),

(8, 'eastern exports', 'liam anderson', '90 silk rd', 'beijing', '100000', 'china'),

(9, 'southern traders', 'isabella thomas', '101 bay st', 'sydney', '2000', 'australia'),

(10, 'continental supplies', 'noah jackson', '202 continental rd', 'london', 'sw1a 1aa', 'uk'),

(11, 'pacific traders', 'mia white', '303 pacific blvd', 'vancouver', 'v5k 0a1', 'canada'),
(12, 'mountain essentials', 'lucas harris', '404 alpine rd', 'zurich', '8001', 'switzerland'),

(13, 'city distributors', 'amelia martin', '505 downtown ave', 'chicago', '60601', 'usa'),

(14, 'riverbank goods', 'benjamin lee', '606 river st', 'bangkok', '10100', 'thailand'),

(15, 'desert supplies', 'charlotte walker', '707 desert rd', 'dubai', '00000', 'uae'),

(16, 'harbor traders', 'james hall', '808 harbor blvd', 'singapore', '049321', 'singapore'),

(17, 'central market', 'harper young', '909 central st', 'berlin', '10115', 'germany'),

(18, 'island imports', 'alexander king', '111 island rd', 'manila', '1000', 'philippines'),

(19, 'northern essentials', 'evelyn wright', '222 northern ave', 'oslo', '0150', 'norway'),

(20, 'metro supplies', 'daniel scott', '333 metro st', 'paris', '75001', 'france');

-- Create table

create table categories (

categoryid int primary key,

categoryname varchar(100),

description varchar(255)

);

-- Insert records

insert into categories (categoryid, categoryname, description) 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

create table employees (

employeeid int primary key,
lastname varchar(50),
firstname varchar(50),
birthdate date,
photo varchar(100),
notes text
);

-- Insert records with notes in Proper Case

insert into employees (employeeid, lastname, firstname, birthdate, photo, notes) values

(1, 'davolio', 'nancy', '1968-12-08', '[Link]', 'Education includes a BA in psychology from Colorado
State University. She also completed (The Art of the Cold Call). Nancy is a member of Toastmasters
International.'),

(2, 'fuller', 'andrew', '1952-02-19', '[Link]', 'Andrew received his BTS commercial and a Ph.D. in
international marketing from the University of Dallas. He is fluent in French and Italian and reads
German. He joined the company as a sales representative, was promoted to sales manager and was
then named vice president of sales. Andrew is a member of the Sales Management Roundtable, the
Seattle Chamber of Commerce, and the Pacific Rim Importers Association.'),

(3, 'leverling', 'janet', '1963-08-30', '[Link]', 'Janet has a BS degree in chemistry from Boston
College. She has also completed a certificate program in food retailing management. Janet was hired as
a sales associate and was promoted to sales representative.'),

(4, 'peacock', 'margaret', '1958-09-19', '[Link]', 'Margaret holds a BA in English literature from
Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned
to the London office before returning to her permanent post in Seattle.'),

(5, 'buchanan', 'steven', '1955-03-04', '[Link]', 'Steven Buchanan graduated from St. Andrews
University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6
months in an orientation program at the Seattle office and then returned to his permanent post in
London, where he was promoted to sales manager. Mr. Buchanan has completed the courses Successful
Telemarketing and International Sales Management. He is fluent in French.'),

(6, 'suyama', 'michael', '1963-07-02', '[Link]', 'Michael is a graduate of Sussex University (MA,
Economics) and the University of California at Los Angeles (MBA, Marketing). He has also taken the
courses Multi-Cultural Selling and Time Management for the Sales Professional. He is fluent in Japanese
and can read and write French, Portuguese, and Spanish.'),

(7, 'king', 'robert', '1960-05-29', '[Link]', 'Robert King served in the Peace Corps and traveled
extensively before completing his degree in English at the University of Michigan and then joining the
company. After completing a course entitled Selling in Europe, he was transferred to the London
office.'),
(8, 'callahan', 'laura', '1958-01-09', '[Link]', 'Laura received a BA in Psychology from the University
of Washington. She has also completed a course in Business French. She reads and writes French.'),

(9, 'dodsworth', 'anne', '1969-07-02', '[Link]', 'Anne has a BA degree in English from St. Lawrence
College. She is fluent in French and German.'),

(10, 'west', 'adam', '1928-09-19', '[Link]', 'An old chum.');

-- Create table

create table orderdetails (

orderdetailid int primary key,

orderid int,

productid int,

quantity int

);

-- Insert sample records

insert into orderdetails (orderdetailid, orderid, productid, quantity) values

(1, 10248, 11, 12),

(2, 10248, 42, 10),

(3, 10248, 72, 5),

(4, 10249, 14, 9),

(5, 10249, 51, 40),

(6, 10250, 41, 10),

(7, 10250, 51, 35),

(8, 10250, 65, 15),

(9, 10251, 22, 6),

(10, 10251, 57, 15)

-- Create table

create table orders (

orderid int primary key,

customerid int,

employeeid int,

orderdate date,

shipperid int

);

-- Insert records

insert into orders (orderid, customerid, employeeid, orderdate, shipperid) values

(10248, 90, 5, '1996-07-04', 3),

(10249, 81, 6, '1996-07-05', 1),

(10250, 34, 4, '1996-07-08', 2),

(10251, 84, 3, '1996-07-08', 1),

(10252, 76, 4, '1996-07-09', 2),

(10253, 34, 3, '1996-07-10', 2),

(10254, 14, 5, '1996-07-11', 2),

(10255, 68, 9, '1996-07-12', 3),

(10256, 88, 3, '1996-07-15', 2),

(10257, 35, 4, '1996-07-16', 3),

(10258, 20, 1, '1996-07-17', 1),

(10259, 13, 4, '1996-07-18', 3),

(10260, 55, 4, '1996-07-19', 1);

-- Create table

create table products (

productid int primary key,

productname varchar(100),

supplierid int,

categoryid int,

unit varchar(50),

price decimal(10,4)

);

-- Insert records

insert into products (productid, productname, supplierid, categoryid, unit, price) values

(1, 'chais', 1, 1, '10 boxes x 20 bags', 18.0000),

(2, 'chang', 1, 1, '24 - 12 oz bottles', 19.0000),

(3, 'aniseed syrup', 1, 2, '12 - 550 ml bottles', 10.0000),

(4, 'chef antons cajun seasoning', 2, 2, '48 - 6 oz jars', 22.0000),

(5, 'chef antons gumbo mix', 2, 2, '36 boxes', 21.3500),

(6, 'grandmas boysenberry spread', 3, 2, '12 - 8 oz jars', 25.0000),

(7, 'uncle bobs organic dried pears', 3, 7, '12 - 1 lb pkgs.', 30.0000),

(8, 'northwoods cranberry sauce', 3, 2, '12 - 12 oz jars', 40.0000),

(9, 'mishi kobe niku', 4, 6, '18 - 500 g pkgs.', 97.0000),

(10, 'ikura', 4, 8, '12 - 200 ml jars', 31.0000),

(11, 'queso cabrales', 5, 4, '1 kg pkg.', 21.0000),

(12, 'queso manchego la pastora', 5, 4, '10 - 500 g pkgs.', 38.0000),

(13, 'konbu', 6, 8, '2 kg box', 6.0000),

(14, 'tofu', 6, 7, '40 - 100 g pkgs.', 23.2500),

(15, 'genen shouyu', 6, 2, '24 - 250 ml bottles', 15.5000),

(16, 'pavlova', 7, 3, '32 - 500 g boxes', 17.4500),

(17, 'alice mutton', 7, 6, '20 - 1 kg tins', 39.0000),

(18, 'carnarvon tigers', 7, 8, '16 kg pkg.', 62.5000),

(19, 'teatime chocolate biscuits', 8, 3, '10 boxes x 12 pieces', 9.2000),

(20, 'sir rodneys marmalade', 8, 3, '30 gift boxes', 81.0000);

-- Create table

create table shippers (

shipperid int primary key,

shippername varchar(100),

phone varchar(20)

);

-- Insert records

insert into shippers (shipperid, shippername, phone) values

(1, 'speedy express', '(503) 555-9831'),

(2, 'united package', '(503) 555-3199'),

(3, 'federal shipping', '(503) 555-9931');

-- Create table

create table suppliers (

supplierid int primary key,
suppliername varchar(100),
contactname varchar(100),
address varchar(200),
city varchar(100),
postalcode varchar(20),
country varchar(50),
phone varchar(20)
);

-- Insert records

insert into suppliers (supplierid, suppliername, contactname, address, city, postalcode, country, phone)
values
(1, 'exotic liquid', 'charlotte cooper', '49 gilbert st.', 'londona', 'ec1 4sd', 'uk', '(171) 555-2222'),

(2, 'new orleans cajun delights', 'shelley burke', 'p.o. box 78934', 'new orleans', '70117', 'usa', '(100) 555-
4822'),

(3, 'grandma kellys homestead', 'regina murphy', '707 oxford rd.', 'ann arbor', '48104', 'usa', '(313) 555-
5735'),

(4, 'tokyo traders', 'yoshi nagase', '9-8 sekimai musashino-shi', 'tokyo', '100', 'japan', '(03) 3555-5011'),

(5, 'cooperativa de quesos las cabras', 'antonio del valle saavedra', 'calle del rosal 4', 'oviedo', '33007',
'spain', '(98) 598 76 54'),

(6, 'mayumis', 'mayumi ohno', '92 setsuko chuo-ku', 'osaka', '545', 'japan', '(06) 431-7877'),

(7, 'pavlova, ltd.', 'ian devling', '74 rose st. moonie ponds', 'melbourne', '3058', 'australia', '(03) 444-
2343'),

(8, 'specialty biscuits, ltd.', 'peter wilson', '29 kings way', 'manchester', 'm14 gsd', 'uk', '(161) 555-4448'),

(9, 'pb knäckebröd ab', 'lars peterson', 'kaloadagatan 13', 'göteborg', 's-345 67', 'sweden', '031-987 65
43'),

(10, 'refrescos americanas ltda', 'carlos diaz', 'av. das americanas 12.890', 'são paulo', '5442', 'brazil',
'(11) 555 4640');