Specify the updates of Exercise 3.11 using the SQL update commands.
What will be an ideal response?
Below, we show how each of the updates may be specified in SQL. Notice that some of these updates violate integrity constraints as discussed in the solution to Exercise 5.10, and hence should be rejected if executed on the database of Figure 5.6.
(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,
Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
INSERT INTO EMPLOYEE
VALUES ('Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX',
M, 58000, '888665555', 1)
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
INSERT INTO PROJECT
VALUES ('ProductA', 4, 'Bellaire', 2)
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
INSERT INTO DEPARTMENT
VALUES ('Production', 4, '943775543', '01-OCT-88')
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
INSERT INTO WORKS_ON
VALUES ('677678989', NULL, '40.0')
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
INSERT INTO DEPENDENT
VALUES ('453453453', 'John', M, '12-DEC-60', 'SPOUSE')
(f) Delete the WORKS_ON tuples with ESSN= '333445555'.
DELETE FROM WORKS_ON
WHERE ESSN= '333445555'
(g) Delete the EMPLOYEE tuple with SSN= '987654321'.
DELETE FROM EMPLOYEE
WHERE SSN= '987654321'
(h) Delete the PROJECT tuple with PNAME= 'ProductX'.
DELETE FROM PROJECT
WHERE PNAME= 'ProductX'
(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER=
5 to '123456789' and '01-OCT-88', respectively.
UPDATE DEPARTMENT
SET MGRSSN = '123456789', MGRSTARTDATE = '01-OCT-88'
WHERE DNUMBER= 5
(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to
'943775543'.
UPDATE EMPLOYEE
SET SUPERSSN = '943775543'
WHERE SSN= '999887777'
(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and
PNO= 10 to '5.0'.
UPDATE WORKS_ON
SET HOURS = '5.0'
WHERE ESSN= '999887777' AND PNO= 10
You might also like to view...
____ is the technique photographers, designers, and artists use to create the illusion of three dimensions on a flat or two-dimensional surface.
a. Layout b. Perspective c. Storyboard d. None of the above
The UPPER function converts all letters of each word in a text string to uppercase.?
Answer the following statement true (T) or false (F)
What is NOT a part of the VMware vCenter services component of vCenter Server?
A. Web Client B. Inventory database service C. Auto Deploy service D. VMware Certificate Authority
After manipulating the mesh and the lighting of a 3D object, you can change the ____________________ or surface texture.
Fill in the blank(s) with the appropriate word(s).