/
Set Operators And Modifying Data
127 lines (102 loc) · 4.27 KB
/
Set Operators And Modifying Data
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
--Create a Union between Person and PersonAddress in Community assist and Employee in MetroAlt. You will need to fully qualify the tables in the CommunityAssist part of the query:
--CommunityAssist.dbo.Person etc
Use MetroAlt
Select EmployeeZipCode, EmployeeCity--both sides of the union have to have the same structure
From Employee --Column names from the top will persist for the whole query
Union
Select PersonAddressZip, PersonAddressCity--PersonFirstName should match PersonFirstName above
From Community_Assist.dbo.PersonAddress
--Do an intersect between the PersonAddress and Employee that returns the cities that are in both.
Select EmployeeCity--both sides of the union have to have the same structure
From Employee --Column names from the top will persist for the whole query
Intersect
Select PersonAddressCity--PersonFirstName should match PersonFirstName above
From Community_Assist.dbo.PersonAddress
--Do an except between PersonAddress and Employee that returns the cities that are not in both.
Select EmployeeCity--both sides of the union have to have the same structure
From Employee --Column names from the top will persist for the whole query
Except
Select PersonAddressCity--PersonFirstName should match PersonFirstName above
From Community_Assist.dbo.PersonAddress
--For this we will use the Data Tables we made in Assignment 1.
--Insert the following services into BusService:
--General Maintenance, Brake service, hydraulic maintenance, and Mechanical Repair.
--You can add descriptions if you like.
Use MetroAlt
Create Table BusService
(
BusServiceKey int identity(1,1) primary key,
BusServiceName nvarchar(255) not null,
BusServiceDescription nvarchar(255)
)
INSERT INTO BusService ( BusServiceName ) VALUES
('GeneralMaintenance'),
('BrakeService'),
('HydraulicMaintenance'),
('Mechanical Repair');
INSERT INTO Maintenance ( BusKey, MaintenanceDate ) VALUES
('12', GETDATE()),
('24', GETDATE());
--Next add entries into the Maintenance table for busses 12 and 24.
--You can use today’s date.
--For the details on 12 add General Maintenance and Brake Service,
--for 24 just General Maintenance.
--You can use employees 60 and 69 they are both mechanics.
ALTER TABLE MaintenanceDetail
ADD CONSTRAINT FK_MaintenanceDetail_Maintenance
FOREIGN KEY (MaintenanceKey)
REFERENCES Maintenance(MaintenanceKey);
INSERT INTO MaintenanceDetail ( EmployeeKey, MaintenanceNotes) VALUES
(60, 'General Maintenance and Brake Service')
WHERE MaintenanceKey='1';
UPDATE MaintenanceDetail
SET EmployeeKey=60
Where MaintenanceKey='1'
UPDATE MaintenanceDetail
SET EmployeeKey=69
WHERE MaintenanceKey='2'
UPDATE MaintenanceDetail
SET MaintenanceNotes='General Maintenance and Brake Service'
Where MaintenanceKey='1'
UPDATE MaintenanceDetail
SET MaintenanceNotes='General Maintenance'
Where MaintenanceKey='2'
--Create a table that has the same structure as Employee,
--name it Drivers.
CREATE TABLE Driver(
EmployeeKey int NOT NULL,
DriverLastName nvarchar(255) not null,
DriverFirstName nvarchar(255) not null,
DriverAddress nvarchar(255) not null,
DriverCity nvarchar(255) not null,
DriverZipCode nchar(5) not null,
DriverPhone nchar(10) null,
DriverEmail nvarchar(255) not null,
DriverHireDate date not null
)
ALTER TABLE Driver
ADD CONSTRAINT fk_Employee_Driver
FOREIGN KEY (EmployeeKey)
References Employee (EmployeeKey);
--Use the Select form of an insert to copy all the employees whose position is driver
--(1) into the new table.
Insert into Driver(EmployeeKey, DriverLastName, DriverFirstName, DriverAddress, DriverCity, DriverZipCode, DriverPhone, DriverEmail, DriverHireDate)
Select Employee.EmployeeKey, EmployeeLastName, EmployeeFirstName, EmployeeAddress, EmployeeCity, EmployeeZipCode, EmployeePhone, EmployeeEmail, EmployeeHireDate
From Employee
INNER JOIN EmployeePosition
ON Employee.EmployeeKey = EmployeePosition.EmployeeKey
WHERE PositionKey = '1'
--Edit the record for Bob Carpenter (Key 3)
--so that his first name is Robert and is City is Bellevue
UPDATE Employee
SET EmployeeFirstName='Robert'
Where EmployeeKey='3'
UPDATE Employee
SET EmployeeCity='Bellevue'
Where EmployeeKey='3'
--Give everyone a 3% Cost of Living raise.
UPDATE EmployeePosition
SET EmployeeHourlyPayRate = (EmployeeHourlyPayRate*1.03)
--Delete the position Detailer
DELETE FROM Position
WHERE PositionName='Detailer'