6. Insert into LARGE_CONDO table the location number, unit number, bedrooms, bat
ID: 3821249 • Letter: 6
Question
6. Insert into LARGE_CONDO table the location number, unit number, bedrooms, baths, condo fee, and owner number for those condos whose square footage is greater than 1,500 square feet.
7. Solmaris has increased the condo fee of each large condo by $175. Update the condo fees in the LARGE_CONDO table accordingly.
8. Insert a new row into the LARGE_CONDO table. The location number is 1, the unit number is 605, the number of bedrooms is 3, the number of bathrooms is 3, the condo fee is $775, and the owner number is FE182.
9. Add to the LARGE_CONDO table a new character column named OCCUPIED that is one character in length. (This column will indicate whether the condo is currently occupied.) Set the value for the OCCUPIED column on all rows to Y.
10. Create a view named SMALL_CONDO. It consists of the location number, unit number, bedrooms, baths, condo fee, and owner number for every condo whose square footage is less than 1,100 square feet.
11. Use the view SMALL_CONDO to retrieve the location number, unit number, and condo fee for every condo with a condo fee of $400 or more.
12. Write, but do not execute, the commands to grant the following privileges: a. User Oliver must be able to retrieve data from the CONDO_FEES table. b. Users Crandall and Perez must be able to add new owners and condo units to the database. c. All users must be able to retrieve the unit number, condo fee, and owner number for every condo unit.
13. Write, but do not execute, the command to revoke all privileges from user Adams.
14. Add the OWNER_NUM column as a foreign key in the CONDO_UNIT table.
15. Ensure that the only legal values for the BDRMS column in the CONDO_UNIT table are 1, 2, or 3.
5. Create a LARGE CONDO table with the structure shown below: Column Type Length Decimal Nulls Description Places Allowed? No Marina LOCATION NUM CHAR number (primary key) Slip number UNIT NUM CHAR in the marina (primary key) BDRMS Number of INT bedrooms Number of INT BATHS bathrooms CONDO FEE Monthly DECIMAL 6 condo fee Number of OWNER NUM CHAR. condo owner 6. Insert into LARGE CONDO table the location number, unit number, bedrooms, baths, condo fee, and owner number for those condos whose square footage is greater than 1,500 square feet.Explanation / Answer
Hi,
Please find the queries for the problem statements given as follows:-
SQL QUERIES:-
=======================================================================================
6)INSERT INTO LARGE_CONDO VALUES('2','1600',2,2,175,’ADWIN’);
7) UPDATE LARGE_CONDO
SET CONDO_FEE = CONDO_FEE+175.00
WHERE OWNER_NUM=’Solmaris’;
8) INSERT INTO LARGE_CONDO VALUES('1','605',3,3,775,’FE182’);
9)ALTER TABLE LARGE_CONDO
ADD OCCUPIED char 1;
UPDATE LARGE_CONDO
SET OCCUPIED=’y’;
10) CREATE VIEW SMALL_CONDO AS
SELECT LOCATION_NUM, UNIT_NUM, BDRMS, BATHS, CONDO_FEE, OWNER_NUM
FROM LARGE_CONDO
WHERE UNIT_NUM <1100;
11) SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE
FROM SMALL_CONDO
WHERE CONDO_FEE>=400;
12)
a) GRANT SELECT
ON CONDO_FEES
TO OLIVER;
b) GRANT INSERT (OWNER_NUM,UNIT_NUM)
ON CONDO_FEES
TO CRANDALL,PEREZ;
c) GRANT SELECT (UNIT_NUM,CONDO_FEE,OWNER_NUM)
ON CONDO_FEES
TO PUBLIC;
13) REVOKE ALL ON LARGE_CONDO FROM Adams;
14) ALTER TABLE CONDO_UNIT
ADD FOREIGN KEY (OWNER_NUM)
REFERENCES LARGE_CONDO (OWNER_NUM);
15) ALTER TABLE CONDO_UNIT
ADD CONSTRAINT CK_BDRMS CHECK (BDRMS IN ('1','2','3'));
======================================================================================
Please let me know for any clarification.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.