Chapter 3, Using Multiple Tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
CREATE TABLE Customers ( CustomerID int IDENTITY ,[First name] varchar(100) ,[Last name] varchar(100) ,[Address] varchar(100) ,PRIMARY KEY (CustomerID)) CREATE TABLE Orders ( OrderID int PRIMARY KEY ,CustomerID int NULL ,OrderDate datetime ,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)); CREATE TABLE Orderlines ( OrderID int references orders(orderid) ,[Order line number] int ,Item varchar(100) ,quantity int ,price decimal(18,2) ,primary key (orderid, [Order line number])) CREATE TABLE Employees ( EmployeeID int IDENTITY ,[First name] varchar(100) ,[Last name] varchar(100) ,[Address] varchar(100) ,PRIMARY KEY (EmployeeID)) --DROP TABLE orderlines --DROP TABLE Orders --DROP TABLE customers --DROP TABLE Employees INSERT Customers values ('John','Smith','First Street 1, Washington'); INSERT Customers values ('Peter','Jones','Second Street 2, London'); INSERT Customers values ('Frank','James','Champs-Elysees , Paris'); INSERT Customers values ('Jim','Gray','Main Street 7, New York'); INSERT Customers VALUES ('Peter', 'Jones', 'Second Street 2, London') ,('Frank', 'James', 'Champs-Elysees , Paris') ,('Jim', 'Gray', 'Main Street 7, New York'); INSERT Orders VALUES ('1','1', '2013-01-01'); INSERT Orders VALUES ('2','2', '2012-07-01'); INSERT Orders VALUES ('3','3', '2012-08-15'); INSERT Orders VALUES ('4','4', '2012-12-31'); INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99); INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90); INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98); INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99); INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York'); INSERT Employees values ('Jim','Gray','Main Street 7, New York'); --outer join SELECT * FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Orderlines ol on ol.OrderID = o.OrderID; --subqueries SELECT * FROM Customers WHERE [First name] in ( SELECT [First name] FROM Employees); --subquery SELECT 'Address' = [Street name] + [House number] + ', ' + City FROM Shipments --table subquery example CREATE TABLE Shipments ( ShipmentID int identity ,Shipmentdate datetime ,[Street name] varchar(100) ,[House number] varchar(10) ,[City] varchar(100)); --outer query SELECT * FROM Customers c INNER JOIN (...) s on c.Address = s.Address --Total SELECT * FROM Customers c INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City FROM Shipments ) s on c.Address = s.Address; |