SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE Customers ( Id INT NOT NULL , FirstName VARCHAR(25) NULL , LastName VARCHAR(25) NULL , StreetAddress VARCHAR(50) NULL , City VARCHAR(30) NULL , Province VARCHAR(2) NULL , PostalCode VARCHAR (7) NULL , AreaCode smallint DEFAULT (0) NULL , PhoneNumber VARCHAR (8) NULL, CONSTRAINT Customers_PK PRIMARY KEY(Id) ); -- Let's insert some Customer data INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1001, 'Susan', 'Vincent', '15127 NW 25th, #393', 'Toronto', 'ON', 'M5E-1W5', 416, '555-2986'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1002, 'Tom', 'Williams', '127 River Drive', 'Vancouver', 'BC', 'V5J-1A1', 604, '555-2981'); INSERT INTO Customers(Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1003, 'George', 'Perkins', 'Oak Ave', 'London', 'ON', 'N5Y-5R6', 519, '555-2676'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1004, 'Bob', 'Roberts', '726 Lambert Ave', 'Ottawa', 'ON', 'K2J-0K6', 613, '555-2498'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1005, 'Dee', 'McAulish', '411 Sycamore Rd.', 'London', 'ON', 'N5Y-5R6', 519, '555-2507'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1006, 'Jake', 'Washington', '15 North St, #727', 'London', 'ON', 'N5Y-5R6', 519, '555-2517'); INSERT INTO Customers(Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1007, 'Marianne', 'Sergeant', '1901 Spruce St', 'Hamilton', 'ON', 'L0R-1H0', 289, '555-2528'); INSERT INTO Customers(Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1008, 'Norm', 'Peterson', '33 East Ave', 'Edmonton', 'AB', 'T6X-2A3', 680, '555-4541'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1009, 'Andy', 'Capp', '50 - 25th St. Apt. 12C', 'Toronto', 'ON', 'M5E-1W5', 416, '555-7601'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1010, 'Angie', 'Kendal', '12 River Road', 'Ottawa', 'ON', 'K2J-0K6', 613, '555-4571'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1012, 'Leon', 'Keys', '920 N. 40th Street', 'Montreal', 'QU', 'H1A 0A1', 514, '555-2856'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1013, 'Raoul', 'Peterson', '114 Lovers Lane', 'Edmonton', 'AB', 'T6X-2A3', 680, '555-2846'); INSERT INTO Customers (Id, FirstName, LastName, StreetAddress, City, Province, PostalCode, AreaCode, PhoneNumber) VALUES (1025, 'May', 'Peterson', '45 Sienne Rd', 'Calgary', 'AB', 'T6X-2A3', 403, '555-4291'); CREATE TABLE Orders ( Id SMALLINT NOT NULL, OrderDate DATE NULL, ShipDate DATE NULL, CustomerId INT DEFAULT(0) NULL, EmployeeId INT DEFAULT(0) NULL, CONSTRAINT Orders_PK PRIMARY KEY(Id) ) INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (1, '2021-09-01', '2021-09-04', 1018, 607); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (2, '2021-09-01', '2021-09-03', 1001, 603); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (3, '2021-09-01', '2021-09-04', 1002, 607); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (4, '2021-09-01', '2021-09-03', 1009, 603); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (5, '2021-09-01', '2021-09-01', 1024, 608); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (6, '2021-09-01', '2021-09-05', 1014, 602); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (7, '2021-09-01', '2021-09-04', 1001, 608); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (8, '2021-09-01', '2021-09-01', 1003, 603); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (9, '2021-09-01', '2021-09-04', 1007, 608); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (10, '2021-09-01', '2021-09-04', 1012, 601); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (11, '2021-09-02', '2021-09-04', 1020, 606); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (12, '2021-09-02', '2021-09-05', 1024, 606); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (13, '2021-09-02', '2021-09-02', 1024, 604); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (14, '2021-09-02', '2021-09-03', 1013, 604); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (15, '2021-09-02', '2021-09-06', 1004, 601); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (16, '2021-09-02', '2021-09-06', 1001, 607); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (17, '2021-09-02', '2021-09-03', 1014, 602); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (18, '2021-09-02', '2021-09-03', 1016, 608); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (19, '2021-09-02', '2021-09-06', 1027, 607); INSERT INTO Orders (Id, OrderDate, ShipDate, CustomerId, EmployeeID) VALUES (20, '2021-09-02', '2021-09-02', 1011, 606); CREATE TABLE Vendors ( Id SMALLINT NOT NULL , Name VARCHAR(25) NULL , StreetAddress VARCHAR(50) NULL , City VARCHAR(30) NULL , Province VARCHAR(2) NULL , PostalCode VARCHAR(7) NULL , PhoneNumber VARCHAR(15) NULL , WebURL VARCHAR(50) NULL , EMail VARCHAR(50) NULL , CONSTRAINT Vendors_PK PRIMARY KEY(Id) ); -- Let's insert some Vendor data INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (1, 'Nike Inc.', 'One Bowerman Drive', 'Toronto', 'ON', 'M5E-1W5', '(416)555-6453', 'http://www.nike.com', 'Sales@nike.com'); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (2, 'CCM', '19 Rue St. Larent', 'Montreal','QU', 'H1A-0A1', '(514)555-1234', 'http://www.ccm.ca', 'Saless@ccm.ca'); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (3, 'Callaway Corp', '88 Old North Road Ave', 'Ottawa', 'ON', 'K2J-0K6', '(613)555-1234', 'http://www.bigbertha.com/', 'bb@callaway.com'); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (7, 'Wilson', '575 Edison Ave.', 'Calgary', 'AB', 'T6X-2A3', '(403)555-9876', NULL, NULL); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (8, 'Yonex', 'PO Box 82', 'Calgary', 'AB', 'T6X-2A3', '(403)555-9876', NULL, NULL); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (9, 'Rawlings', '740 King St', 'Toronto', 'ON', 'M5E-1W5', '(905)555-9876', NULL, NULL); INSERT INTO Vendors (Id, Name, StreetAddress, City, Province, PostalCode, PhoneNumber, WebURL, Email) VALUES (10, 'Vaughn', '12 Highbury Ave', 'London', 'ON', 'N5Y-5R6', '(519)555-9876', 'http://www.vaughnpads.com/', 'Sales@Vaughn.com'); --SELECT Name as 'Vendor Name', StreetAddress + ' ' + City + ' ' + PostalCode as 'Address' --FROM vendors --WHERE StreetAddress LIKE '%Ave%' AND Name LIKE'_a%' AND PostalCode LIKE '%6'; SELECT * FROM Orders WHERE Id LIKE '%2' AND CustomerId LIKE '%4' AND EmployeeId LIKE '%6' ORDER BY Id;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear