5>
6> CREATE TABLE airplane
7> (containing_assembly VARCHAR(10),
8> contained_assembly VARCHAR(10),
9> quantity_contained INT,
10> unit_cost DECIMAL (6,2))
11> GO
1> insert into airplane values ('Airplane', 'Fuselage', 1, 10)
2> insert into airplane values ('Airplane', 'Wings', 1, 11)
3> insert into airplane values ('Airplane', 'Tail', 1, 12)
4> insert into airplane values ('Fuselage', 'Cockpit', 1, 13)
5> insert into airplane values ('Fuselage', 'Cabin', 1, 14)
6> insert into airplane values ('Fuselage', 'Nose', 1, 15)
7> insert into airplane values ('Cockpit', NULL, 1, 13)
8> insert into airplane values ('Cabin', NULL, 1, 14)
9> insert into airplane values ('Nose', NULL, 1, 15)
10> insert into airplane values ('Wings', NULL, 2, 11)
11> insert into airplane values ('Tail', NULL, 1, 12)
12> 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 rows affected)
1> --The following example shows the use of the WITH clause to define a query that calculates the total costs of each assembly.
2>
3> WITH list_of_parts(assembly, quantity, cost) AS
4> (SELECT containing_assembly, quantity_contained, unit_cost
5> FROM airplane
6> WHERE contained_assembly IS NULL
7> UNION ALL
8> SELECT a.containing_assembly, a.quantity_contained,
9> CAST(l.quantity*l.cost AS DECIMAL(6,2))
10> FROM list_of_parts l,airplane a
11> WHERE l.assembly = a.contained_assembly)
12> SELECT * FROM list_of_parts
13> GO
assembly quantity cost
---------- ----------- --------
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Cockpit 1 13.00
Cabin 1 14.00
Nose 1 15.00
Wings 2 11.00
Tail 1 12.00
Airplane 1 12.00
Airplane 1 22.00
Fuselage 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Airplane 1 13.00
Airplane 1 12.00
Airplane 1 22.00
Fuselage 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Airplane 1 13.00
Airplane 1 12.00
Airplane 1 22.00
Fuselage 1 15.00
Airplane 1 15.00
Fuselage 1 14.00
Airplane 1 14.00
Fuselage 1 13.00
Airplane 1 13.00
(141 rows affected)
1>
2> drop table airplane;
3> GO
1>