|
7>
8>
9>
10> CREATE TABLE Customers (
11> CustomerID nchar (5) NOT NULL ,
12> CompanyName nvarchar (40) NOT NULL ,
13> ContactName nvarchar (30) NULL ,
14> ContactTitle nvarchar (30) NULL ,
15> Address nvarchar (60) NULL ,
16> City nvarchar (15) NULL ,
17> Region nvarchar (15) NULL ,
18> PostalCode nvarchar (10) NULL ,
19> Country nvarchar (15) NULL ,
20> Phone nvarchar (24) NULL ,
21> Fax nvarchar (24) NULL
22> )
23> GO
1>
2> INSERT Customers VALUES('1','A','Maria', 'Sales', 'Str. 57', 'Berlin' ,NULL,'12209', 'Germany','111-1111111','111-1111111')
3> INSERT Customers VALUES('2','M','Joe', 'Owner', 'Ave. 231','Vancouver' ,NULL,'05023', 'Mexico', '(222) 222-3332',NULL)
4> INSERT Customers VALUES('3','H','Thomas', 'Sales', 'Sq. 111','London' ,NULL,'1D00P', 'UK', '(444) 444-4444','(444) 444-4444')
5> INSERT Customers VALUES('4','B','Berg', 'Order', 'Blv 8','Toronto' ,NULL,'00222', 'Sweden', '4444-55 55 65','5555-55 55 55')
6> INSERT Customers VALUES('5','S','Moos', 'Sales', 'Fort 57','New York' ,NULL,'68306', 'Germany','6666-66666','6666-77777')
7> INSERT Customers VALUES('6','F','Cite', 'Manager','24 ','Dalles' ,NULL,'67000', 'France', '88.60.15.31','88.60.15.32')
8> INSERT Customers VALUES('7','C','Sommer', 'Owner', 'Araq, 67','Paris' ,NULL,'28023', 'Spain', '(91) 555 22 82','(91) 555 91 99')
9> INSERT Customers VALUES('8','P','Leb', 'Owner', '12 ','Beijing' ,NULL,'13008', 'France', '91.24.45.40','91.24.45.41')
10> INSERT Customers VALUES('9','D','Elizabeth','Manager','23 Blvd.','Tsawassen','BC', 'T2F8M4','Canada', '(604) 555-4729','(604) 555-3745')
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE Orders (
2> OrderID int NOT NULL ,
3> CustomerID nchar (5) NULL ,
4> EmployeeID int NULL ,
5> OrderDate datetime NULL ,
6> RequiredDate datetime NULL ,
7> ShippedDate datetime NULL ,
8> ShipVia int NULL ,
9> Freight money NULL DEFAULT (0),
10> ShipName nvarchar (40) NULL ,
11> ShipAddress nvarchar (60) NULL ,
12> ShipCity nvarchar (15) NULL ,
13> ShipRegion nvarchar (15) NULL ,
14> ShipPostalCode nvarchar (10) NULL ,
15> ShipCountry nvarchar (15) NULL
16> )
17> GO
1>
2>
3> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> GO
1> INSERT Products VALUES(1,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products VALUES(2,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products VALUES(3,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products VALUES(4,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products VALUES(5,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products VALUES(6,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products VALUES(7,'O',12,2,'12 boxes',13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE OrderDetails (
4> OrderID int NOT NULL ,
5> ProductID int NOT NULL ,
6> UnitPrice money NOT NULL DEFAULT (0),
7> Quantity smallint NOT NULL DEFAULT (1),
8> Discount real NOT NULL DEFAULT (0)
9> )
10> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT DISTINCT c.CompanyName
4> FROM Customers AS c
5> JOIN
6> (SELECT CustomerID
7> FROM Orders o
8> JOIN OrderDetails od
9> ON o.OrderID = od.OrderID
10> JOIN Products p
11> ON od.ProductID = p.ProductID
12> WHERE p.ProductName = 'Chocolade') AS spen
13> ON c.CustomerID = spen.CustomerID
14> JOIN
15> (SELECT CustomerID
16> FROM Orders o
17> JOIN OrderDetails od
18> ON o.OrderID = od.OrderID
19> JOIN Products p
20> ON od.ProductID = p.ProductID
21> WHERE ProductName = 'Vegie-spread') AS spap
22> ON c.CustomerID = spap.CustomerID
23> GO
CompanyName
----------------------------------------
(0 rows affected)
1>
2> drop table Products;
3> drop table OrderDetails;
4> drop table Orders;
5> drop table Customers;
6> GO
|