D2 DBMS LAB QUIZ

You need to modify an existing database table BOOKS by adding a new column to store the publication year of books. Which SQL statement should you use?

• ALTER TABLE Books ADD COLUMN publication_year INT;

• UPDATE TABLE Books SET publication_year = 2023;

• INSERT INTO Books (publication_year) VALUES (2023);

• DELETE COLUMN publication_year FROM Books;

. You are responsible for maintaining a database of customer orders for an online store. You need to update the price of a product with ID 101 to $19.99. Which SQL statement should you use?

• SELECT * FROM Products WHERE product_id = 101;

• DELETE FROM Products WHERE product_id = 101;

• UPDATE Products SET price = 19.99 WHERE product_id = 101;

• INSERT INTO Products (product_id, price) VALUES (101, 19.99);

You are designing a database for an e-commerce platform. You need to create a table to store customer addresses, including street address, city, state, and postal code. Which data type would be most appropriate for the postal code column?

• A) VARCHAR(20)

• B) INT

• C) CHAR(5)

• D) DATE

You are working on a database for a social media platform. You need to create a table to store user profiles, including their usernames, email addresses, and profile pictures. Which SQL constraint should be used to ensure that usernames are unique?

• A) PRIMARY KEY

• B) UNIQUE

• C) CHECK

• D) FOREIGN KEY

You are managing a database for a bank. You need to transfer $500 from one customer's account to another customer's account. Which SQL statement(s) should you use to perform this transfer?

• A) UPDATE Accounts SET balance = balance - 500 WHERE account_number = 123;

• B) UPDATE Accounts SET balance = balance + 500 WHERE account_number = 456;

• C) INSERT INTO Transactions (from_account, to_account, amount) VALUES (123, 456, 500);

• D) A combination of A and B

You are developing a database for a car rental company. You need to retrieve a list of available cars for rent on a specific date. Which SQL query should you use?

• A) SELECT * FROM Cars WHERE status = 'Available' AND rental_date = '2023-09-15';

• B) SELECT * FROM Cars WHERE status = 'Rented' AND rental_date <> '2023-09-15';

• C) SELECT * FROM Cars WHERE status = 'Available' AND rental_date IS NULL;

• D) SELECT * FROM Cars WHERE status = 'Available' AND 'rental_date' = '2023-09-15';

You are designing a database for an online store. You want to ensure that product prices are always greater than or equal to $5. Which SQL constraint should you use to enforce this rule?

• A) PRIMARY KEY

• B) CHECK

• C) FOREIGN KEY

• D) UNIQUE

As part of our efforts to enhance our database, we want to implement specific requirements to improve the quality of customer information. We need to validate two criteria: First, customers must be at least 21 years old, and second, they must have a residence in an Asian country. Which type of SQL constraint should we use to enforce these requirements?

• A. Customer must be greater than 21 years old

• B. Customer must have a residence in an Asian Country

• C. Both A and B

• D. None of the above

As part of your role, you handle financial transactions in the company's database. Your manager has asked you to explain when a DML statement may not be executed during database operations. Consider the following situations: modifying existing rows, deleting entire tables, and deleting specific rows. In which of these cases would a DML statement not be executed?

• A. When existing rows are modified.

• B. When a table is deleted.

• C. When some rows are deleted.

• D. All of these

Think of our company's database as a blueprint for organizing information. The schema defines how data is structured, and the tables represent where data is stored. Now, let's clarify some key points about DDL commands, which are essential for managing this blueprint. Which statements are TRUE regarding DDL commands?

• A. Our data is stored in a table that is described by the schema, thus DDL commands deal with the schema.

• B. With the DDL commands, any structural changes can be made to the table, including creation, deletion, and alteration.

• C. Both A. and B.

• D. None of the above