1.Which statement best defines a schema?
A schema is a logical group of database objects—such as tables and indexes—that are related to each other.
2. Which of the following is NOT a valid clause of CREATE TABLE statement?
SECONDARY KEY
3. Which of the following is NOT a table constraint?
ON INSERT
4. Which SQL command changes the structure of a TABLE?
ALTER
5. Which of the following is NOT a valid constraint?
UNIQUE
6. Which of the following is not a valid command to change a COLUMN in a TABLE?
DELETE
7. Which commands would you use to save and undo table changes?
COMMIT and ROLLBACK
8. Which of the following is a valid UPDATE command?
UPDATE table_name SET column_name WHERE condition;
9. Which of the following is a valid characteristic of a VIEW?
The name of a view can be used anywhere a table name is expected in a SQL statement.
10. Which of the following updatable view restrictions is NOT valid?
Updatable view must include the name(s) of the base table(s) used in the view.
Chapter 08 Use It
1. What is the correct order to CREATE the tables? Think about referential integrity.
Owner, Pet Appointments, Treatment, Treatment_Appt
2. The Appointments table was not created with a primary key. What is the correct SQL to create a primary key for the table and which field should be the primary key?
ALTER TABLE Appointments
Add Primary Key (APPOINTMENT_ID));
3. The OWNER_ID in the Pet table should be NOT NULL. What SQL corrects this?
ALTER TABLE Pet
MODIFY OWNER_ID INT NOT NULL
4. All owners reside in Nevada. What SQL makes NV the default when a row is entered?
| ALTER TABLE Owner MODIFY OWNER_STATE CHAR (2) DEFAULT ‘NV’; |
5. In the Owner table, for OWNER_ID 1 all the data was not entered. Select the correct SQL to enter the data. Refer to the data listed below.
UPDATE Owner
SET OWNER_ST_ADDRESS = ‘123 Geiger Town’,
OWNER_CITY = ‘VC Highlands’,
OWNER_POSTAL_CODE = ‘85921’,
OWNER_PHONE = 3457689,
OWNER_EMAIL = ‘mcanton@gmail.com
WHERE OWNER_ID =1;
6. The Appointments table needs an INVOICE column added which can contain five numbers and two decimals. A total of seven numbers which include the decimals. Select the correct SQL.
ALTER TABLE Appointments
ADD (INVOICE NUMBER(7,2));
7. Create a view to display all appointments where the bill has not been paid. Select the correct SQL.
CREATE VIEW Invoice_Paid AS
SELECT APPOINTMENT_ID, PET_ID, APPOINTMENT_DATE,INVOICE,PAID
FROM Appointments
WHERE PAID = ‘N’;
8. Create a trigger which checks when an PET_AGE is added to Pet. If the age is less than 0 insert 0 for the age. Select the correct SQL.
DEIMITER //
CREATE TRIGGER AGECHECK BEFORE INSERT ON Pet
FOR EACH ROW IF NEW.PET_AGE < 0
THEN SET NEW.PET_AGE = O;
ENDIF;//
DELIMITER;
9. In the Pet table, for PET_ID 2 all the data was not entered. Select the correct SQL to enter the missing data.
UPDATE Pet
SET PET_BREED = ‘Husky’,
WHERE PET_ID = 2;
10. The Pet table needs an PET_AGE field added. Select the correct SQL.
| ALTER TABLE Pet ADD (PET_AGE INTEGER); |
11. Create a view to display all appointments where the bill has been paid. Display all the columns in the Appointments table. Select the correct SQL.
CREATE VIEW Invoice_Paid AS
SELECT APPOINTMENT_ID, PET_ID, APPOINTMENT_DATE,INVOICE,PAID
FROM Appointments
WHERE PAID = ‘Y’;
12. Create a trigger which checks when an INVOICE is added to Appointments. If the INVOICE is less than 0 insert 0 for the INVOICE. Select the correct SQL.
DEIMITER //
CREATE TRIGGER INVOICECHECK BEFORE INSERT ON Appointments
FOR EACH ROW IF NEW.INVOICE< 0
THEN SET NEW.INVOICE= O;
ENDIF;//
DELIMITER;
13. In the TREATMENT table, a new row of data needs to be added using PL/SQL. Include a message indicating the row was added successfully. Select the correct SQL.
INSERT INTO Treatment
VALUES (9, ‘Give more greens’, ‘Eating grass’;
14. Create a procedure to add 0 to the invoice column if it is null. Select the correct SQL.
CREATE OR REPLACE PROCEDURE CHECK_INVOICE
AS BEGIN
UPDATE Appointments
SET INVOICE = 0
WHERE INVOICE IS NULL;
DBMS_OUTPUT.PUTLINE(“Update completed”);
END;
15. Add a check constraint to the Appointments table. Ensure that the INVOICE is greater than or equal to 0. Select the correct SQL.
ALTER TABLE Appointments
ADD CHECK (INVOICE >=0);
16. Add a foreign key to Pet referencing Owner. Also, add a check constraint ensuring PET_AGE is greater than 0. Select the correct SQL.
ALTER TABLE Pet
ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID)
ADD CHECK (PET_AGE >0);
17. A primary key needs to be added to the Owner table. Select the best field for the primary key. Also add a check constraint to ensure only NV can be added. Select the correct SQL.
ALTER TABLE Owner
ADD PRIMARY KEY (OWNER_ID)
ADD CHECK (OWNER_STATE = ‘NV’);
18. Create a procedure to add a reason to the Appointments table if the APPOINTMENT_REASON is blank. Ensure you output a message that the change was completed. Select the correct SQL.
CREATE OR REPLACE PROCEDURE CHECK_APPOINTMENT_REASON
AS BEGIN
UPDATE Appointments
SET APPOINTMENT_REASON = ‘Not given’
WHERE APPOINTMENT_REASON IS NULL;
DBMS_OUTPUT.PUTLINE(‘Update completed’);
END;
19. Change the Appointments table so when a APPOINTMENT_ID is added a PET_ID must be added. Select the correct SQL.
ALTER TABLE Appointments
MODIFY (PET_ID INTEGER NOT NULL);
20. Add the appropriate foreign key(s) to the Treatment_Appt table. Select the correct SQL.
ALTER TABLE Treatment_Appt
ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID);
ALTER TABLE Treatment_Appt
ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID);
Other Links:
See other websites for quiz:
Check on QUIZLET