Based on the Sample2312 database provided. Write SQL statements that: 1. List th
ID: 3918733 • Letter: B
Question
Based on the Sample2312 database provided. Write SQL statements that: 1. List the properties for rent that would work for client CR76, according to what he/she is willing to pay as maximum rent. 2. There is an issue with property PG4. Retrieve name and all contact information for its owner. 3. Modify (Alter) the column salary. Change its name to Compensation. 4. List the cities where their branches have no associated staff. 5. Insert in to Staff: (‘SL26’,’Manuel’,’Granados’,’Clerk’,’M’,’1975-12-12’,12000,’B001’) In case of finding problems with the insertion: • Describe the problem, • Explain the cause, • Propose a solution. 6. Attempt deletion (drop) of table Branch. Provide an explanation for the error message received (if received). 7. Attempt deletion of branch B003 from the table Branch. Provide an explanation for the error message received (if received). 8. Client CO46 complaint about his name being misspelled in the database. His correct name is ‘Jose’. Execute the correction in the records. Your submission is a single file that includes the corresponding SQL for the requested items along with the outputs provided by MySQL and all your explanations. Make sure every answer is properly identified to match the questions.
Explanation / Answer
show tables;
-- ------------------Fill the Tables------------------
insert into branch(branchNo,street,city,postcode)
values ('B005', '22 Deer Rd' ,'London' , 'SW1 4EH'),
('B007', '16 Argyll St','Aberdeen', 'AB2 3SU'),
('B003', '163 Main St' ,'Glasgow' , 'G11 9QX'),
('B004', '32 Manse Rd' ,'Bristol' , 'BS9 1NZ'),
('B002', '56 Clover Dr','London' , 'NW1 6EU');
***[Error] Script lines: 1-1 --------------------------
Table 'Sample2312.branch' doesn't exist.
***Solution : insert into Branch instead of branch
insert into Branch(branchNo,street,city,postcode)
values ('B005', '22 Deer Rd' ,'London' , 'SW1 4EH'),
('B007', '16 Argyll St','Aberdeen', 'AB2 3SU'),
('B003', '163 Main St' ,'Glasgow' , 'G11 9QX'),
('B004', '32 Manse Rd' ,'Bristol' , 'BS9 1NZ'),
('B002', '56 Clover Dr','London' , 'NW1 6EU');
--------------------------------------------------------------------------------------------------
insert into privateOwner(ownerNo,fName ,lName ,street ,city ,postcode ,telNo, eMail)
values ('CO46', 'Joe' ,'Keogh' , '2 Fergus Dr','Aberdeen', 'AB2 7SX' , '01224-861212' , 'jkeog.com'),
('CO87', 'Carol','Farrel' , '6 Achray St','Glasgow' , 'G32 9DX' , '0141-357-7419', 'cfarrel.com'),
('CO40', 'Tina' ,'Murphy' , '63 Well St' ,'Glasgow' , 'G42' , '0141-943-1728', 'tinam.com'),
('CO93' ,'Tony' ,'Shaw' , '12 Park Pl' ,'Glasgow' , 'G4 0QR' , '0141-225-7025', 'tony.com');
***[Error] Script lines: 1-1 --------------------------
Table 'Sample2312.privateOwner' doesn't exist.
***Solution : insert into PrivateOwner instead of privateOwner
insert into PrivateOwner(ownerNo,fName ,lName ,street ,city ,postcode ,telNo, eMail)
values ('CO46', 'Joe' ,'Keogh' , '2 Fergus Dr','Aberdeen', 'AB2 7SX' , '01224-861212' , 'jkeog.com'),
('CO87', 'Carol','Farrel' , '6 Achray St','Glasgow' , 'G32 9DX' , '0141-357-7419', 'cfarrel.com'),
('CO40', 'Tina' ,'Murphy' , '63 Well St' ,'Glasgow' , 'G42' , '0141-943-1728', 'tinam.com'),
('CO93' ,'Tony' ,'Shaw' , '12 Park Pl' ,'Glasgow' , 'G4 0QR' , '0141-225-7025', 'tony.com');
-- ----Test those tables-----
SELECT * FROM Branch;
SELECT * FROM privateOwner;
***[Error] Script lines: 1-1 --------------------------
Table 'Sample2312.privateOwner' doesn't exist.
***Solution : SELECT * FROM PrivateOwner
-- ---------Continue fillling the Tables---------
insert into Staff(staffNo,fName ,lName ,sPosition ,sex, DOB ,salary, branchNo)
values ('SL21', 'John' ,'White' , 'Manager' ,'M', '1945-10-01' , 30000, 'B005'),
('SG37', 'Ann' ,'Beech' , 'Assistant' ,'F', '1960-11-10' , 12000, 'B003'),
('SG14', 'David' ,'Ford' , 'Supervisor','M', '1958-03-24' , 18000, 'B003'),
('SA9' , 'Mary' ,'Howe' , 'Assistant' ,'F', '1970-02-19' , 9000, 'B007'),
('SG5' , 'Susan' ,'Brand' , 'Manager' ,'F', '1940-06-03' , 24000, 'B003'),
('SL41', 'Julie' ,'Lee' , 'Assistant' ,'F', '1965-06-13' , 9000, 'B005');
insert into CClient(clientNo,fName ,lName ,telNo ,prefType, maxRent, eMail)
values ('CR76', 'John' ,'Kay' ,'0207-774-5632' ,'Flat' , 425, 'john.kay.com'),
('CR56', 'Aline','Stewart' ,'0141-848-1825' ,'Flat' , 350, 'astewart.com'),
('CR74', 'Mike' ,'Ritchie' ,'01475-392178' ,'House', 750, 'mritchie01.co.uk'),
('CR62', 'Mary' ,'Tregear' ,'01224-196720' ,'Flat' , 600, 'maryt.com.uk');
insert into PropertyForRent(propertyNo, street , city , postcode, pType ,rooms, rent, ownerNo, staffNo, branchNo)
values ('PA14' ,'16 Holhead' ,'Aberdeen','AB7 5SU','House', 6 , 650, 'CO46' , 'SA9' , 'B007'),
('PL94' ,'6 Argyll St' ,'London' ,'NW2' ,'Flat' , 4 , 400, 'CO87' , 'SL41' , 'B005'),
('PG4' ,'6 Lawrence St','Glasgow' ,'G11 9QX','Flat' , 3 , 350, 'CO40' , NULL , 'B003'),
('PG36' ,'2 Manor Rd' ,'Glasgow' ,'G32 4QX','Flat' , 3 , 375, 'CO93' , 'SG37' , 'B003'),
('PG21' ,'18 Dale Rd' ,'Glasgow' ,'G12' ,'House', 5 , 600, 'CO87' , 'SG37' , 'B003'),
('PG16' ,'5 Novar Dr' ,'Glasgow' ,'G12 9AX','Flat' , 4 , 450, 'CO93' , 'SG14' , 'B003');
insert into Viewing(clientNo, propertyNo, viewDate , vComment)
values ('CR56' , 'PA14' ,'2013-05-24 10:00' ,'too small' ),
('CR76' , 'PG4' ,'2013-04-20 8:35' ,'too remote' ),
('CR56' , 'PG4' ,'2013-05-26 16:45' , NULL ),
('CR62' , 'PA14' ,'2013-05-14 13:32' ,'no dining room'),
('CR56' , 'PG36' ,'2013-04-28 11:00' , NULL );
insert into Registration
values ('CR76', 'B005', 'SL41', '2013-01-02'),
('CR56', 'B003', 'SG37', '2013-04-11'),
('CR74', 'B003', 'SG37', '2013-11-16'),
('CR62', 'B007', 'SA9' , '2013-03-07');
SELECT * FROM Branch;
SELECT * FROM PropertyForRent;
SELECT * FROM PrivateOwner;
SELECT * FROM Registration;
SELECT * FROM Viewing;
SELECT * FROM Staff;
SELECT * FROM CClient;
drop database sample2312;
Based on the Sample2312 database provided. Write SQL statements that:
1. List the properties for rent that would work for client CR76, according to what he/she is willing to pay as maximum rent.
Answer:
select pfr.street, pfr.city, pfr.postcode, pfr.pType, pfr.rooms, pfr.rent, pfr.ownerNo, pfr.staffNo, pfr.branchNo
from PropertyForRent pfr, Viewing vie, CClient ccli
where
pfr.propertyNo = vie.propertyNo and vie.clientNo = ccli.clientNo and ccli.clientNo = CR76 order by prf.rent desc
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. There is an issue with property PG4. Retrieve name and all contact information for its owner.
Answer:
select ccli.fName, ccli.lName, ccli.telNo, ccli.prefType, ccli.maxRent, ccli.eMail
from PropertyForRent pfr, Viewing vie, CClient ccli
where
pfr.propertyNo = vie.propertyNo and vie.clientNo = ccli.clientNo
Result:
fName lName telNo prefType maxRent eMail
-------------------------------------------------------------------
'John' 'Kay' '0207-774-5632' 'Flat' 425, 'john.kay.com'
'Aline' 'Stewart' '0141-848-1825' 'Flat' 350, 'astewart.com'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3. Modify (Alter) the column salary. Change its name to Compensation.
Answer:
ALTER TABLE Staff CHANGE salary Compensation INT;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4. List the cities where their branches have no associated staff.
Answer:
select city from Branch br where NOT EXISTS (
select 1 from Staff st where br.branchNo = st.branchNo
);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5. Insert in to Staff: (‘SL26’,’Manuel’,’Granados’,’Clerk’,’M’,’1975-12-12’,12000,’B001’) In case of finding problems with the insertion: • Describe the problem, • Explain the cause, • Propose a solution.
Answer:
insert into Staff(staffNo,fName ,lName ,sPosition ,sex, DOB ,salary, branchNo)
values ('SL26', 'Manuel' ,'Granados', 'Clerk', 'M', '1975-12-12', 12000, 'B001');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6. Attempt deletion (drop) of table Branch. Provide an explanation for the error message received (if received).
Answer:
DROP TABLE Branch
Cannot delete or update a parent row: a foreign key constraint fails (`Sample2312`.`Staff`, CONSTRAINT `branchNo_fk` FOREIGN KEY (`branchNo`) REFERENCES `Branch` (`branchNo`))
In order to drop/delete Branch table, first we delete all foreign key references related child tables. Child tables - Staff, PropertyForRent and Registration
1. DROP TABLE Staff;
2. DROP TABLE PropertyForRent;
3. DROP TABLE Registration;
Then we can able to delete/drop Branch table
4. DROP TABLE Branch;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7. Attempt deletion of branch B003 from the table Branch. Provide an explanation for the error message received (if received).
Answer:
DELETE FROM Branch WHERE branchNo = B003
[Error] Script lines: 1-1 --------------------------
Cannot delete or update a parent row: a foreign key constraint fails (`Sample2312`.`Staff`, CONSTRAINT `branchNo_fk` FOREIGN KEY (`branchNo`) REFERENCES `Branch` (`branchNo`))
In order to delete branchNo B003 record from Branch table, first we delete all foreign key references in child tables. Child tables - Staff, PropertyForRent and Registration
1. DELETE FROM Staff WHERE branchNo = B003;
2. DELETE FROM PropertyForRent WHERE branchNo = B003;
3. DELETE FROM Registration WHERE branchNo = B003;
Then we can able to delete B003 record in Branch table
4. DELETE FROM Branch WHERE branchNo = B003;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8. Client CO46 complaint about his name being misspelled in the database. His correct name is ‘Jose’. Execute the correction in the records.
Answer:
UPDATE PrivateOwner SET fName = 'Jose' WHERE ownerNo = 'CO46';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.