BASIS DATA

 CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    LastName VARCHAR(50),

    FirstName VARCHAR(50),

    Title VARCHAR(50),

    Address VARCHAR(100),

    City VARCHAR(50),

    Country VARCHAR(50),

    HomePhone VARCHAR(20)

);

INSERT INTO Employees (EmpID, LastName, FirstName, Title, Address, City, Country, HomePhone)

VALUES

(1, 'Davolio', 'Nancy', 'Sales Rep.', '507 - 20th Ave. E. Apt. 2A', 'Seattle', 'USA', '(206) 555-9857'),

(2, 'Fuller', 'Andrew', 'Vice President', '908 W. Capital Way', 'Tacoma', 'USA', '(206) 555-9482'),

(3, 'Leverling', 'Janet', 'Sales Rep.', '722 Moss Bay Blvd.', 'Kirkland', 'USA', '(206) 555-3412'),

(4, 'Peacock', 'Margaret', 'Sales Rep.', '4110 Old Redmond Rd.', 'Redmond', 'USA', '(206) 555-8122'),

(5, 'Buchanan', 'Steven', 'Sales Manager', '14 Garrett Hill', 'London', 'UK', '(71) 555-4848'),

(6, 'Suyama', 'Michael', 'Sales Rep.', 'Coventry House Miner Rd.', 'London', 'UK', '(71) 555-7773'),

(7, 'King', 'Robert', 'Sales Rep.', '12 Winchester Way', 'London', 'UK', '(71) 555-5598'),

(8, 'Callahan', 'Laura', 'Sales Coord.', '4726 - 11th Ave. N.E.', 'Seattle', 'USA', '(206) 555-1189'),

(9, 'Dodsworth', 'Anne', 'Sales Rep.', '7 Houndstooth Rd.', 'London', 'UK', '(71) 555-4444');

SELECT * FROM Employees;

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    SupplierID INT,

    QuantityPerUnit VARCHAR(50),

    UnitPrice DECIMAL(10, 2),

    UnitsInStock INT

);

INSERT INTO Products (ProductID, ProductName, SupplierID, QuantityPerUnit, UnitPrice, UnitsInStock)

VALUES

(2, 'Chang', 1, '24 - 12 oz bottles', 19.00, 17),

(5, 'Chef Anton''s Gumbo Mix', 2, '36 boxes', 21.35, 50),

(21, 'Sir Rodney''s Scones', 8, '24 pkgs. x 4 pieces', 10.00, 17),

(27, 'Schoggi Schokolade', 11, '100 - 100 g pieces', 43.90, 49),

(32, 'Mascarpone Fabioli', 14, '24 - 200 g pkgs.', 32.00, 9);

SELECT * FROM Products;

CREATE TABLE Customers (

    CustomerID VARCHAR(5) PRIMARY KEY,

    CompanyName VARCHAR(50),

    ContactName VARCHAR(50),

    Address VARCHAR(100),

    City VARCHAR(50),

    PostalCode VARCHAR(10),

    Country VARCHAR(50),

    Phone VARCHAR(20)

);

INSERT INTO Customers (CustomerID, CompanyName, ContactName, Address, City, PostalCode, Country, Phone)

VALUES

('SEVES', 'Seven Seas Imports', 'Hari Kumar', '90 Wadhurst Rd.', 'London', 'OX154', 'UK', '(171) 555-1717'),

('MAISD', 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', 'B-1180', 'Belgium', '(02) 201 24 67'),

('ISLAT', 'Island Trading', 'Helen Bennett', '75 Crowther Way', 'Cowes', 'PO31 7PJ', 'UK', '(198) 555-8888'),

('EASTC', 'Eastern Connection', 'Ann Devon', '35 King George', 'London', 'WX3FW', 'UK', '(171) 555-0297'),

('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany', '030-0074321');

SELECT * FROM Customers;

CREATE TABLE Lanjutan (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(50),

    SupplierID INT,

    QuantityPerUnit VARCHAR(50),

    UnitPrice DECIMAL(10, 2),

    UnitsInStock INT

);

INSERT INTO Lanjutan (ProductID, ProductName, SupplierID, QuantityPerUnit, UnitPrice, UnitsInStock)

VALUES

(37, 'Gravad lax', 17, '12 - 500 g pkgs.', 26.00, 11),

(39, 'Chartreuse verte', 18, '750 cc per bottle', 18.00, 69),

(41, 'Jack''s Clam Chowder', 19, '12 - 12 oz cans', 9.65, 85),

(53, 'Perth Pasties', 24, '48 pieces', 32.80, 15),

(57, 'Ravioli Angelo', 26, '24 - 250 g pkgs.', 19.50, 36),

(62, 'Tarte au sucre', 29, '48 pies', 49.30, 17),

(65, 'Hot Pepper Sauce', 2, '32 - 8 oz bottles', 21.05, 76),

(70, 'Outback Lager', 7, '24 - 355 ml bottles', 15.00, 15),

(74, 'Longlife Tofu', 4, '5 kg pkg.', 10.00, 4),

(77, 'Original Frankfurter', 12, '12 boxes', 13.00, 32);

SELECT * FROM Lanjutan;

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustID VARCHAR(5),

    EmpID INT,

    OrderDate DATE,

    RequiredDate DATE,

    ShippedDate DATE,

    ShipVia INT

);

INSERT INTO Orders (OrderID, CustID, EmpID, OrderDate, RequiredDate, ShippedDate, ShipVia)

VALUES

(10256, 'EASTC', 3, '1994-08-15', '1994-09-12', '1994-08-17', 2),

(10257, 'SEVES', 4, '1994-08-16', '1994-09-13', '1994-08-22', 3),

(10258, 'MAISD', 1, '1994-08-16', '1994-09-15', '1994-08-23', 1),

(10259, 'ALFKI', 4, '1994-08-18', '1994-09-16', '1994-08-25', 3),

(10260, 'ISLAT', 4, '1994-08-19', '1994-09-16', '1994-08-29', 1);

SELECT * FROM Orders;

CREATE TABLE OrderDetails (

    OrderID INT,

    ProductID INT,

    UnitPrice DECIMAL(10, 2),

    Quantity INT,

    Discount DECIMAL(3, 2),

    PRIMARY KEY (OrderID, ProductID)

);

INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, Quantity, Discount)

VALUES

(10256, 53, 26.20, 15, 0),

(10256, 77, 10.40, 12, 0),

(10257, 27, 35.10, 25, 0),

(10257, 39, 14.40, 6, 0),

(10257, 77, 10.40, 15, 0),

(10258, 2, 15.20, 50, 0.2),

(10258, 5, 17.00, 65, 0.2),

(10258, 32, 25.60, 10, 0.2),

(10259, 21, 8.00, 10, 0),

(10259, 37, 20.80, 1, 0),

(10260, 41, 7.70, 16, 0.25),

(10260, 57, 15.60, 50, 0),

(10260, 62, 39.40, 15, 0.25),

(10260, 70, 12.00, 21, 0.25);

SELECT * FROM OrderDetails;