FIT 9132 Week 8

Which of the following is NOT a valid syntax for the UPDATE command?

• UPDATE table SET column1 = value1, column2 = value2 WHERE condition;

• UPDATE column1, column2 SET value1, value2 WHERE condition;

• UPDATE table SET column1 = value1 WHERE condition;

• UPDATE table SET column1 = value1, column2 = value2;

Which of the following statements is true regarding the UPDATE command?

• It can only modify one column at a time.

• It can modify multiple columns at a time.

• It can only update rows that match a certain condition.

• It can only update all rows in a table at once.

Which of the following operators can be used in the WHERE clause of an UPDATE command?

• LIKE

• IN

• BETWEEN

• All of the above

Which of the following SQL statements will update the "salary" column of a table called "employees" to increase by 10% for all employees whose job title is "Manager"?

• UPDATE employees SET salary = salary * 1.1 WHERE job_title = 'Manager';

• UPDATE employees SET salary = salary * 0.1 WHERE job_title = 'Manager';

• UPDATE employees SET salary = salary * 1.1 AND job_title = 'Manager';

• UPDATE employees SET salary = salary * 0.1 AND job_title = 'Manager';

Which of the following SQL statements will update the "status" column of a table called "orders" to "Shipped" for all orders with an order date in the year 2022?

• UPDATE orders SET status = 'Shipped' WHERE order_date LIKE '%2022%';

• UPDATE orders SET status = 'Shipped' WHERE TO_DATE(order_date, 'DD-MON-YYYY') BETWEEN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), TO_DATE('01-JAN-2023', 'DD-MON-YYYY'));

• UPDATE orders SET status = 'Shipped' WHERE TO_DATE(order_date, 'DD-MON-YYYY') IN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), TO_DATE('01-JAN-2023', 'DD-MON-YYYY'));

• UPDATE orders SET status = 'Shipped' WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

Which of the following SQL statements will update the "description" column of a table called "products" to make all text lowercase?

• UPDATE products SET description = LOWER(description);

• UPDATE products SET description = LOWER('description');

• UPDATE products SET description = TO_LOWER(description);

• UPDATE products SET description = TO_LOWER('description');

Which of the following SQL statements will update the "quantity" column of a table called "inventory" to be equal to the number of items in stock for each product in a table called "products"?

• UPDATE inventory SET quantity = (SELECT COUNT(*) FROM products WHERE products.id = inventory.product_id);

• UPDATE inventory SET quantity = products.quantity WHERE products.id = inventory.product_id;

• UPDATE inventory SET quantity = (SELECT SUM(quantity) FROM products WHERE products.id = inventory.product_id);

• UPDATE inventory SET quantity = (SELECT AVG(quantity) FROM products WHERE products.id = inventory.product_id);

Which of the following SQL statements does not follow best practice?

• UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';

• UPDATE customers SET city = 'New York' WHERE city LIKE '%york%';

• UPDATE orders SET order_date = TO_DATE('2022-04-30', 'YYYY-MM-DD') WHERE order_id IN (1001, 1002, 1003);

• UPDATE employees SET salary = salary * 1.05 WHERE age IN (50,51)

Which of the following SQL statements contains an error in the subquery used in the UPDATE command?

• UPDATE products SET price = price * 1.1 WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

• UPDATE orders SET status = 'Shipped' WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

• UPDATE employees SET manager_id = (SELECT employee_id FROM employees WHERE last_name = 'Smith') WHERE department_id = 100;

• No error.

Which of the following is NOT a property of ACID?

• Atomicity

• Durability

• Consistency

• Isolation

• None of the above