LHMBj5Ac

1/* Suppose you decide to collapse vendor_contacts into vendors 
2becuase you observe that (1) there is only one extra contact for 
3some vendors and (2) vendor_contacts does not even have a PK
4defined for it.*/
5
6CREATE TABLE vendors_2
7(
8  vendor_id                     NUMBER          NOT NULL,
9  vendor_name                   VARCHAR2(50)    NOT NULL,
10  vendor_address1               VARCHAR2(50),
11  vendor_address2               VARCHAR2(50),
12  vendor_city                   VARCHAR2(50)    NOT NULL,
13  vendor_state                  CHAR(2)         NOT NULL,
14  vendor_zip_code               VARCHAR2(20)    NOT NULL,
15  vendor_phone                  VARCHAR2(50),
16  vendor_contact_last_name      VARCHAR2(50),
17  vendor_contact_first_name     VARCHAR2(50),
18  vendor_contact2_last_name VARCHAR2(50),
19  vendor_contact2_first_name VARCHAR2(50),
20  default_terms_id              NUMBER          NOT NULL,
21  default_account_number        NUMBER          NOT NULL,
22  CONSTRAINT vendors_2_pk 
23    PRIMARY KEY (vendor_id),
24  CONSTRAINT vendors_2_vendor_name_uq 
25    UNIQUE (vendor_name),
26  CONSTRAINT vendors_2_fk_terms
27    FOREIGN KEY (default_terms_id) 
28    REFERENCES terms (terms_id),
29  CONSTRAINT vendors_2_fk_accounts
30    FOREIGN KEY (default_account_number) 
31    REFERENCES general_ledger_accounts (account_number)
32);
33
34/* Now you want to load data that exists in vendors and in vendor-contacts
35into the new table. Better look at the data first. */
36SELECT COUNT(vendor_id) FROM vendor_contacts GROUP BY vendor_id;
37
38SELECT vendors.vendor_id, vendor_name, vendor_contact_last_name,
39vendor_contact_first_name, vc.last_name,vc.first_name
40FROM vendors LEFT OUTER JOIN vendor_contacts vc
41ON vendors.vendor_id=vc.vendor_id
42ORDER BY vendor_id;
43
44SELECT * FROM vendor_contacts;
45
46/* From the SELECTs, you see that there is a second vendor contact
47for 8 vendors, but the data is in the vendor_contacts table twice.
48Entering these identical rows could happen because there is no primary key
49defined for vendor_contacts. */
50/* If you try deleting th eduplicate rows in vendor_contacts, what will
51you use to uniquely identify one row and not another???? This is a problem
52since the rows do not have a unique identifier. So deleting the duplicate data 
53may be a bad idea. In this case, there are only 8 rows to preserve, but just 
54imagine in a real database there might be thousands and thousands of rows.*/
55
56/* First lets write a command to select the information from the two existing tables
57so that it looks like the info in the new vendors_2 table. */
58
59SELECT v.vendor_id, v.vendor_name, v.vendor_address1,
60v.vendor_address2,  v.vendor_city, v.vendor_state,
61v.vendor_zip_code, v.vendor_phone,v.vendor_contact_last_name,
62v.vendor_contact_first_name, vc.last_name,vc.first_name,
63v.default_terms_id,v.default_account_number
64FROM vendors v INNER JOIN vendor_contacts vc
65ON v.vendor_id=vc.vendor_id
66ORDER BY v.vendor_id;
67
68/* That works, but there are two rows for the eight vendors with a second contact and
69now rows for the vendors without a second contact. 
70Fix that.*/
71
72SELECT v.vendor_id, v.vendor_name, v.vendor_address1,
73v.vendor_address2,  v.vendor_city, v.vendor_state,
74v.vendor_zip_code, v.vendor_phone,v.vendor_contact_last_name,
75v.vendor_contact_first_name, vc.last_name,vc.first_name,
76v.default_terms_id,v.default_account_number
77FROM vendors v LEFT OUTER JOIN vendor_contacts vc
78ON v.vendor_id=vc.vendor_id
79ORDER BY v.vendor_id;
80
81/* That gets the vendors without a second contact, but for the vendors with a 
82second contact, both rows that are in the vendor_contacts table for that vendor are
83retrieved. Fix that. */
84
85SELECT DISTINCT v.vendor_id, v.vendor_name, v.vendor_address1,
86v.vendor_address2,  v.vendor_city, v.vendor_state,
87v.vendor_zip_code, v.vendor_phone,v.vendor_contact_last_name,
88v.vendor_contact_first_name, vc.last_name,vc.first_name,
89v.default_terms_id,v.default_account_number
90FROM vendors v LEFT OUTER JOIN vendor_contacts vc
91ON v.vendor_id=vc.vendor_id
92ORDER BY v.vendor_id;
93
94/* That retrieves 127 rows. Is that the same as the number of records in the
95vendors table? */
96SELECT * FROM vendors;
97/* Yes!! */
98
99/* Turn the query into a view for ease of coding the SELECT INTO command. It is also 
100helpful to name the last_name and first_name columns the same as they are named
101in vendor 2.*/
102CREATE VIEW transfer_vendors_to_vendors2
103AS
104SELECT DISTINCT v.vendor_id, v.vendor_name, v.vendor_address1,
105v.vendor_address2,  v.vendor_city, v.vendor_state,
106v.vendor_zip_code, v.vendor_phone,v.vendor_contact_last_name,
107v.vendor_contact_first_name, vc.last_name AS vendor_contact2_last_name,
108vc.first_name AS vendor_contact2_first_name,
109v.default_terms_id,v.default_account_number
110FROM vendors v LEFT OUTER JOIN vendor_contacts vc
111ON v.vendor_id=vc.vendor_id
112ORDER BY v.vendor_id;
113
114/* SELECT from the view to test it. */
115SELECT * FROM transfer_vendors_to_vendors2;
116
117/* Now insert into vendors2 by selecting from the view */
118INSERT INTO vendors_2
119SELECT * FROM transfer_vendors_to_vendors2;
120
121/* SELECT from vendors_2 to see that the data transferred successfully. */
122SELECT * FROM vendors_2;
123
124/* Commit the changes. */
125COMMIT;