SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Customers ( cust_id char(10) NOT NULL, cust_name char(50) NOT NULL, cust_address char(50), cust_city char(50), cust_state char(5), cust_zip char(10), cust_country char(50), cust_contact char(50), cust_email char(255) ); INSERT INTO Customers (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'), ('1000000002', 'Kids Place', '333, South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green', NULL), ('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'), ('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'), ('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard', NULL) CREATE TABLE OrderItems ( order_num int(11) NOT NULL, order_item int(11) NOT NULL, prod_id char(10) NOT NULL, quantity int(11) NOT NULL, item_price decimal(8,2) NOT NULL ) INSERT INTO OrderItems (order_num,order_item,prod_id,quantity,item_price) VALUES (20005, 1, 'BR01', 100, 5.49), (20005, 2, 'BR03', 100, 10.99), (20006, 1, 'BR01', 20, 5.99), (20006, 2, 'BR02', 10, 8.99), (20006, 3, 'BR03', 10, 11.99), (20007, 1, 'BR03', 50, 11.49), (20007, 2, 'BNBG01', 100, 2.99), (20007, 3, 'BNBG02', 100, 2.99), (20007, 4, 'BNBG03', 100, 2.99), (20007, 5, 'RGAN01', 50, 4.49), (20008, 1, 'RGAN01', 5, 4.99), (20008, 2, 'BR03', 5, 11.99), (20008, 3, 'BNBG01', 10, 3.49), (20008, 4, 'BNBG02', 10, 3.49), (20008, 5, 'BNBG03', 10, 3.49), (20009, 1, 'BNBG01', 250, 2.49), (20009, 2, 'BNBG02', 250, 2.49), (20009, 3, 'BNBG03', 250, 2.49) CREATE TABLE Orders ( order_num int(11) NOT NULL, order_date datetime NOT NULL, cust_id char(10) NOT NULL ) INSERT INTO Orders (order_num, order_date, cust_id) VALUES (20005, '2004-05-01 00:00:00', '1000000001'), (20006, '2004-01-12 00:00:00', '1000000003'), (20007, '2004-01-30 00:00:00', '1000000004'), (20008, '2004-02-03 00:00:00', '1000000005'), (20009, '2004-02-08 00:00:00', '1000000001') 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 ) INSERT INTO Products VALUES ('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket'), ('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket'), ('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket'), ('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it'), ('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included'), ('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots'), ('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll'), ('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown'), ('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown') CREATE TABLE Vendors ( vend_id char(10) NOT NULL, vend_name char(50) NOT NULL, vend_address char(50), vend_city char(50), vend_state char(5), vend_zip char(10), vend_country char(50) ) INSERT INTO Vendors VALUES ('BRS01', 'Bears R Us', '123 Main Street', 'Bear Town', 'MI', '44444', 'USA'), ('BRE02', 'Bear Emporium', '500 Park Street', 'Anytown', 'OH', '44333', 'USA'), ('DLL01', 'Doll House Inc.', '555 High Street', 'Dollsville', 'CA', '99999', 'USA'), ('FRB01', 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'USA'), ('FNG01', 'Fun and Games', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England'), ('JTS01', 'Jouets et ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France') SELECT prod_id, prod_name, prod_price FROM Products WHERE prod_name = '8 inch teddy bear' OR prod_price < 5; SELECT prod_id, prod_name, prod_price FROM Products WHERE prod_price IN (3.49, 5.99); SELECT prod_name FROM Products WHERE vend_id NOT IN ('DLL01'); SELECT prod_price FROM Products WHERE prod_name LIKE '% Fish bean'; SELECT prod_name FROM Products WHERE prod_name LIKE '___ bean bag toy'; SELECT cust_zip FROM Customers WHERE cust_zip RLIKE '[4-5]'; SELECT vend_name || vend_country FROM Vendors ORDER BY vend_name; SELECT RTRIM(vend_name) || RTRIM(vend_country) FROM Vendors ORDER BY vend_name; SELECT RTRIM(vend_name) || RTRIM(vend_country) AS vendor_info FROM Vendors ORDER BY vend_name; SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_data FROM OrderItems;