SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Customers (CustomerID int NOT NULL, LastName varchar(32) NOT NULL, FirstName varchar(32) NOT NULL, StreetAddress varchar(64), City varchar(32), State varchar(2), ZIP int, Phone varchar(12), Fax varchar(12), PRIMARY KEY(CustomerID)) ; INSERT INTO Customers (CustomerID, LastName, FirstName, StreetAddress, City, State, ZIP, Phone, Fax) VALUES (32, 'Purgolder', 'Willie', '2525 N Sherman Blvd', 'Milwaukee', 'WI', 53210, '414-875-5900', '414-555-1234'), (33, 'Fudd', 'Elmer', '123 Acme Lane', 'Albuquerque', 'NM', 80023, '234-555-1234', '234-555-4321'), (1, 'Moore', 'Antoine', '467 Ogden Street', 'Fredricksburg', 'TX', 78624, '830-555-8765', '830-555-0814'), (2, 'Vanegas', 'Marlene', '362 W. 182 Ave.', 'Hagerstown', 'MD', 21740, '240-555-5647', '240-555-1211'), (3, 'Garcia', 'Orlando', '72-63 Cook Avenue', 'San Marcos', 'CA', 92069, '760-555-1009', NULL), (4, 'Clark', 'Timothy', '162 Clayborn St.', 'Hewlett', 'NY', 11577, '516-555-4176', '516-555-2266'), (5, 'Parker', 'Eleanor', '1127 Lincoln Boulevard', 'Rocky Mount', 'NC', 27803, '252-555-1198', NULL), (6, 'Evans', 'Larry', '7 Runners Way', 'Winter Park', 'FL', 32789, '407-555-1520', '407-555-1153'), (7, 'Edwards', 'Denise', '142 Glenning', 'New Milford', 'NJ', 07646, '201-555-1698', '201-555-1811'), (8, 'Austin', 'Dennis', '206 26th Avenue', 'Spokane', 'WA', 99228, '509-555-7690', '509-555-7432'), (9, 'Lawson', 'Andrew', '42 Labate Drive', 'Warminster', 'PA', 18974, '215-555-8356', '215-555-6300'), (10, 'Castillo', 'Eduardo', '15 West 86 St.', 'Van Nuys', 'CA', 91316, '546-555-6456', NULL), (11, 'Wheeler', 'Evan', '40 John F. Kennedy Blvd', 'Hawleyville', 'CT', 06440, '203-555-2290', NULL), (12, 'Watanabe', 'Florence', '3 Lahr Circle', 'Coplay', 'PA', 92596, '610-555-2299', '610-555-5523'), (13, 'Morrison', 'David', '802 Mission Street', 'Atlanta', 'GA', 30303, '404-555-3387', '404-555-4196'), (14, 'Nguyen', 'Kim', '466 Mendenhall St.', 'Houston', 'TX', 77282, '281-555-4409', '281-555-7799'), (15, 'Fuller', 'Tam', '17 Petraney St.', 'Ida Grove', 'IA', 51445, '712-555-6992', '712-555-8826'), (16, 'Chan', 'Sally', '212-29 13 Ave.', 'Ankeny', 'IA', 50021, '515-555-1108', NULL), (17, 'Lynch', 'Terry', '1927 Veterans Blvd.', 'Maryville', 'IL', 62062, '618-555-7090', '618-555-4180'), (18, 'Bowman', 'Bruce', '309 Washington Street', 'Albright', 'WV', 26519, '304-555-3298', NULL), (19, 'Holland', 'Leonard', '7190 Olgemar Ave.', 'Litchfield Park', 'AZ', 85340, '623-555-1231', '623-555-3112'), (20, 'Welch', 'Manny', '142 Blythe Way', 'Brooklyn', 'NY', 11209, '718-555-6602', '718-555-1196'), (21, 'Douglas', 'Sophie', '601 Nelly St.', 'Los Angeles', 'CA', 90098, '213-555-8807', NULL), (22, 'Denisovich', 'Melanie', '562 Annerton Circle', 'Hatboro', 'PA', 19040, '215-555-8132', '215-555-0743'), (23, 'Jenson', 'Janet', '982 Helen St.', 'Foxboro', 'MA', 02035, '508-555-0071', '508-555-8243'), (24, 'Reiner', 'Frederick', '46 West 147 St.', 'North Kinston', 'RI', 02853, '401-555-9021', NULL), (25, 'Frazier', 'Wilson', '3210-59 Belton', 'Mayfield', 'MI', 49666, '231-555-5543', NULL), (26, 'Mendoza', 'Jenny', '818 Montoya Freeway', 'Atoona', 'PA', 16603, '814-555-5988', '814-555-6721'), (27, 'Daly', 'Harriet', '717 McCoy St.', 'Oxford', 'MS', 38655, '662-555-5132', '662-555-5643'), (28, 'Braddock', 'Dawn', '322 Billington Ave.', 'Flemington', 'NJ', 08822, '908-555-6259', '908-543-4432'), (29, 'Horton', 'Michael', '902 Henry Lewis James Turnpike', 'Dexter', 'MI', 48130, '734-555-4661', '734-555-0092'), (30, 'Sutton', 'Bob', '7953 Roland St.', 'Fargo', 'ND', 58109, '701-555-1196', '701-555-0099'), (31, 'Lowe', 'Betsy', '46 Court Press Way', 'Seatlle', 'WA', 98105, '206-555-1323', '206-555-7654') ; CREATE TABLE Shippers (ShipperID int, AccountNumber varchar(16), CompanyName varchar(64), Phone varchar(12), PRIMARY KEY(ShipperID)) ; INSERT INTO Shippers VALUES (1, '33428-74', 'Mercury Shopping', '561-555-6233'), (2, 'A20043276', 'Speedy Delivery', '754-555-7892'), (3, '4578989276', 'AAA Lines', '786-555-0987'), (4, 'Z123', 'USPS', '305-555-1378') ; CREATE TABLE Orders (OrderID int , CustomerID int, ShipperID int, OrderDate date , ShipDate date , Overnight bool, ShippingCost DECIMAL(10,2), PRIMARY KEY(OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID), FOREIGN KEY (ShipperID) REFERENCES Shippers(ShipperID)) ; INSERT INTO Orders VALUES (1, 11, 4, '2009-07-16', '2009-07-17', false, 5.95), (2, 8, 3, '2009-07-19', '2009-07-19', false, 5.95), (3, 28, 2, '2009-07-19', '2009-07-21', true, 16.95), (4, 14, 4, '2009-07-19', '2009-07-22', false, 5.00), (5, 13, 1, '2009-07-20', '2009-07-23', false, 5.95), (6, 4, 2, '2009-07-21', '2009-07-26', false, 11.95), (7, 5, 1, '2009-07-23', '2009-07-23', false, 9.85), (8, 23, 3, '2009-07-26', '2009-07-27', false, 6.95), (9, 24, 2, '2009-07-27', '2009-07-29', false, 27.95), (10, 5, 1, '2009-07-28', '2009-07-30', false, 22.95), (11, 8, 1, '2009-07-29', '2009-07-29', false, 5.57), (12, 11, 3, '2009-07-29', '2009-07-30', false, 5.95), (13, 7, 1, '2009-07-29', '2009-07-30', false, 1.50), (14, 9, 2, '2009-08-02', '2009-08-08', true, 14.95), (15, 5, 3, '2009-08-02', '2009-08-02', false, 5.95), (16, 21, 1, '2009-08-03', '2009-08-03', false, 6.95), (17, 7, 2, '2009-08-05', '2009-08-06', false, 27.95), (18, 27, 2, '2009-08-23', '2009-08-23', false, 3.57), (19, 21, 4, '2009-08-23', '2009-08-24', false, 6.55), (20, 1, 2, '2009-08-23', '2009-08-24', false, 5.95), (21, 9, 1, '2009-08-23', '2009-08-24', false, 8.95), (22, 16, 1, '2009-08-23', '2009-08-24', false, 5.95), (23, 31, 4, '2009-08-23', '2009-08-24', false, 27.95), (24, 15, 4, '2009-08-23', '2009-08-24', false, 6.42), (25, 19, 2, '2009-08-23', '2009-08-31', false, 7.95), (26, 10, 1, '2009-08-25', '2009-08-26', false, 2.95), (27, 15, 2, '2009-08-27', '2009-08-27', false, 8.95), (28, 11, 1, '2009-09-01', '2009-09-06', false, 12.95), (29, 26, 3, '2009-09-03', '2009-09-07', false, 8.95), (30, 12, 4, '2009-09-07', '2009-09-08', false, 3.75), (31, 8, 1, '2009-09-08', '2009-09-08', false, 8.95), (32, 18, 1, '2009-09-09', '2009-09-10', false, 8.95), (33, 15, 2, '2009-09-10', '2009-09-12', false, 14.00) ; CREATE TABLE Categories (CategoryID int, CategoryName varchar(32), Description varchar(255), PRIMARY KEY(CategoryID)); INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (1, 'Home Decoration', 'Rug, door knocker, Statue'), (2, 'Clothing', 'Shirts, socks, cap'), (3, 'Miscellaneous', 'Software, bumper sticker, coffee mug'), (4, 'Toys', 'Playing cards, inflatables, jigsaw puzzles'), (5, 'Jewelry', 'Earrings, charm, bracelet'); CREATE TABLE Suppliers (SupplierID int, CompanyName varchar(255), LastName varchar(32), FirstName varchar(32), Phone varchar(12), Fax varchar(12), Email varchar(255), Overnight bool, Terms varchar(255), Notes varchar(255), PRIMARY KEY(SupplierID)); INSERT INTO Suppliers VALUES (1, 'Sea Mammal Magic', 'Collingsworth', 'Peter', '312-555-1199', '312-555-8872', 'peter@dolphindatabase.com', false, '2/10, net 30', NULL), (2, 'Illinois Traders', 'Taylor', 'Virginia', '309-555-7005', '309-555-6172', 'virginia@dolphindatabase.com', false, '2/15, net 30', NULL), (3, 'The Novelty House', 'Perez', 'Janet', '507-555-1140', '507-555-8339', 'janet@dolphindatabase.com', false, 'net 45', 'Ask for Rolando if Janet isn\'t available'), (4, 'Bellissima Jewelry Creations', 'Rodriguez', 'Kevin', '212-555-1234', '212-555-2345', 'kevin_rodriguez@dolphindatabase.com', true, 'net 15', 'Closed for vacation the last two weeks in July'), (5, 'Nguyen Production', 'Nguyen', 'Beverly', '617-555-4739', '617-555-5627', 'bnguyen@dolphindatabase.com', false, '2/10, net 30', NULL), (6, 'Dolphin Delights', 'Taigras', 'Sean', '847-555-1941', '847-555-7584', 'seant@dolphindatabase.com', false, 'net 30', NULL), (7, 'Mrs. Rogers Fun House', 'Rogers', 'Shirley', '803-555-3197', '803-555-1185', 'shirley.rogers@dolphindatabase.com', true, 'net 30', NULL) ; CREATE TABLE Products (ProductID int, CategoryID int, SupplierID int, ProductName varchar(255), Description varchar(255), Price DECIMAL(7,2), StockNumber varchar(10), UnitsinStock int, UnitsOnOrder int, PRIMARY KEY(ProductID), FOREIGN KEY(CategoryID) REFERENCES Categories(CategoryID), FOREIGN KEY(SupplierID) REFERENCES Suppliers(SupplierID)) ; INSERT INTO Products VALUES (1, 1, 6, 'Door Knocker', 'Brass, 8" by 5"', 47.99, 'DK32A', 3, 0), (2, 1, 6, 'Candle Holders', 'Pewter, 10" by 4"', 49.50, 'V523A', 1, 0), (3, 4, 1, 'Doll pair', 'Cow and Baby, 8" and 2.5" long', 18.25, '145K903', 7, 0), (4, 1, 6, 'Collectable Plate', 'Mermaid with dolphin, 8" diameter', 38.60, 'S48B', 2, 2), (5, 1, 2, 'Wall clock', 'BLue dolphin, polyresin, 11" x 17" x 3"', 57.20, 'L4578H1', 2, 0), (6, 3, 3, 'Mirror', 'Brass and marble', 132.58, 'NH854', 2, 0), (7, 3, 3, 'Stamps', 'Various countries, 14 stamps, wooden frame', 41.75, 'NH673', 8, 0), (8, 2, 5, 'Mask', 'Plastic, for ages 8-plus', 6.00, 'S265C', 5, 0), (9, 1, 2, 'Statue', 'Bronze and brass, with marble base, 48" high', 965.00, 'L4582H3', 0, 0), (10, 2, 2, 'T-shirt', 'One size fits all, 95% cotton', 19.65, 'L4137H2', 7, 0), (11, 3, 5, 'Coffee mug', 'Picture of male in wild with born free caption', 14.92, '90876C', 3, 0), (12, 2, 1, 'Cap', 'Dolphin in front of crown, one size fits all', 17.50, '190KDZ', 3, 5), (13, 2, 2, 'Child overalls', 'Dolphin patches on both legs; 100% cotton, two pockets', 18.00, 'L4563H4', 6, 0), (14, 3, 3, 'Flipper Poster', 'From 1996 Movie', 18.00, 'NH8045', 2, 0), (15, 4, 6, 'Playing Cards', 'Deck with various dolphin photos', 2.50, 'PC64W', 10, 2), (16, 2, 5, 'Socks', 'Gray with black line dolphin drawing', 7.50, 'S348B', 10, 0), (17, 1, 2, 'Wall calendar', 'Various dolphin species', 15.00, 'L4522H1', 8, 0), (18, 3, 1, 'Screen saver', 'Software with dolphin photos', 9.95, '111KFD', 4, 6), (19, 1, 2, 'Bathroom rug', 'Bull, cow, and two babies; 5\' by 3\'', 39.00, '4599H8', 5, 0), (20, 4, 7, 'Inflatable Small', 'Pool Toy, 30"', 6.25, 'A457A', 8, 0), (21, 4, 7, 'Inflatable Large', 'Pool Toy, 48"', 10.75, 'A457B', 7, 0), (22, 1, 7, 'Figurine', 'Glass; 4" X 3", 2 pounds', 18.95, 'LL33A26', 8, 0), (23, 3, 1, 'Bumper Sticker', '"I break for Dolphins", 12"x4"', 6.45, '182KTZ', 14, 0), (24, 3, 3, 'Coin', 'British Virgin Islands, sterling silver, 2004', 29.95, 'NH790', 3, 0), (25, 3, 2, 'Decal', 'Window sticker', 4.50, 'L3786H2', 14, 0), (26, 5, 4, 'Pendant', 'Dolphin with Necklace, 10K white gold', 142.50, 'P16', 2, 0), (27, 3, 6, 'Flipper Movie', '1996 movie, DVD, US format', 39.00, 'G87A', 1, 4), (28, 5, 4, 'Charm', '14K ornament for bracelet', 39.95, 'C3', 7, 0), (29, 4, 1, 'Jigsaw puzzle', 'Dolphin, 411 pieces', 14.50, '162KOW', 8, 2), (30, 5, 4, 'Earrings', '14K, clip-on', 69.95, 'E7', 6, 1) ; CREATE TABLE OrderDetails (OrderID int, ProductID int, Quantity int, FOREIGN KEY(OrderID) REFERENCES Orders(OrderID), FOREIGN KEY(ProductID) REFERENCES Products(ProductID), CONSTRAINT PKOrder PRIMARY KEY (OrderID, ProductID)) ; INSERT INTO OrderDetails VALUES (1, 14, 1), (1, 21, 1), (1, 23, 1), (2, 12, 1), (3, 28, 1), (4, 7, 4), (5, 16, 1), (6, 2, 1), (6, 19, 1), (7, 21, 1), (8, 29, 1), (9, 3, 1), (9, 24, 1), (10, 5, 1), (11, 17, 1), (12, 5, 1), (13, 15, 2), (14, 13, 2), (15, 10, 2), (16, 18, 1), (16, 20, 3), (17, 9, 1), (18, 6, 2), (18, 15, 1), (19, 16, 1), (20, 26, 1), (21, 30, 1), (22, 23, 2), (23, 1, 2), (23, 16, 1), (23, 19, 1), (23, 23, 1), (23, 24, 1), (24, 15, 1), (25, 12, 3), (26, 18, 1), (27, 21, 1), (27, 29, 1), (28, 19, 1), (28, 16, 1), (29, 1, 1), (30, 6, 1), (31, 14, 1), (31, 30, 1), (32, 1, 1), (33, 25, 1) ; SELECT LastName, FirstName, State FROM Customers WHERE State='IA'; SELECT * FROM Shippers; SELECT Orders.OrderID, Customers.LastName, Customers.FirstName, Orders.OrderDate, Orders.ShipDate FROM (Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID); SELECT OrderDetails.OrderID, Products.ProductName, Products.Description, Products.Price, OrderDetails.Quantity, Products.Price * OrderDetails.Quantity FROM (OrderDetails INNER JOIN Products ON OrderDetails.ProductID=Products.ProductID) ORDER BY OrderDetails.OrderID; SELECT Customers.FirstName, Customers.LastName, Products.ProductName, Products.Description, Products.Price, OrderDetails.Quantity, (Products.Price * OrderDetails.Quantity), Orders.ShippingCost FROM Customers INNER JOIN (Orders INNER JOIN (OrderDetails INNER JOIN Products ON OrderDetails.ProductID=Products.ProductID) ON Orders.OrderID=OrderDetails.OrderID) ON Customers.CustomerID=Orders.CustomerID ORDER BY Orders.OrderID ; SELECT Customers.FirstName, Customers.LastName, Orders.OrderDate, Orders.ShipDate, SUM(Products.Price * OrderDetails.Quantity)+Orders.ShippingCost AS OrderCost FROM Customers INNER JOIN (Orders INNER JOIN (OrderDetails INNER JOIN Products ON OrderDetails.ProductID=Products.ProductID) ON Orders.OrderID=OrderDetails.OrderID) ON Customers.CustomerID=Orders.CustomerID GROUP BY Orders.OrderID ORDER BY Orders.OrderID ; -- Hint: use Ctrl+Enter for SQL autocomplete