- 22 Suppose That You Have A Sellers Table With A Location Column And You D Like To Update The Location Of Every Seller 1 (85.28 KiB) Viewed 49 times
- 22 Suppose That You Have A Sellers Table With A Location Column And You D Like To Update The Location Of Every Seller 2 (120.13 KiB) Viewed 49 times
- 22 Suppose That You Have A Sellers Table With A Location Column And You D Like To Update The Location Of Every Seller 3 (72.36 KiB) Viewed 49 times
- 22 Suppose That You Have A Sellers Table With A Location Column And You D Like To Update The Location Of Every Seller 4 (78.39 KiB) Viewed 49 times
23. Suppose that, instead of having just a sellers table, we have two tables: a sellers table and a locations table, and that the sellers table has a location_id that references the locations table. Also, since Olympus Mons is pretty large, suppose that we've broken it into multiple locations (all of which begin with Olympus). Which of the following WHERE clauses would you use? (5 Points) None of the above is correct O WHERE location_id IN (SELECT id FROM locations WHERE name LIKE "Olympus%") 4 More than one of the above is correct O WHERE location_id = locations["Olympus Mons"] name: WHERE location_id IN locations.name AND locations.name LIKE "Olympus%" Submit
21. Suppose that you have a Sellers table, which has an address field. You'd like to update the address for Chasm City Florists, which has an ID of 15; which of the following is correct? (4 Points) UPDATE sellers(address) WITH "123 Chasm City" WHERE id = 15; UPDATE sellers SET address = "123 Chasm City" WHERE id = 15; INSERT INTO sellers (address) VALUES ("Chasm City") WHERE id = 15; More than one of the above is correct None of the above are correct
20. Suppose you have a sellers table, which has name, address, and ID columns, specified in that order, only ID has a default value, and none of them can be null. Which of the following statements is not valid? (4 Points) A INSERT INTO sellers (name, address) VALUES ("Arc-Royal Heavy Industries", "Arc-Royal"); INSERT INTO sellers VALUES ("Pathfinder Gate Services", "Pathfinder City", DEFAULT); INSERT INTO sellers VALUES (name: "Risa Tours LLC", address: "Risa", id: DEFAULT); INSERT INTO sellers (id, address, name) VALUES (55, "Chasm City", "In-Town Landscaping");
19. Suppose you would like to delete every product from a products table that has the seller_id for Quicksell Corporation. (You don't know Quicksell's ID, so you'll need to fetch it from the table.) Which of the following is correct? (4 Points) DELETE FROM products WHERE seller_id IN (SELECT id FROM sellers WHERE name = "Quicksell Corporation") DELETE FROM products INNER JOIN sellers ON products.seller_id = sellers.id; DELETE FROM products FILTER seller_id = (sellers["Quicksell"].id); You can't do this with "pure" SQL, you will need some functional logic in the client.
18. Suppose you would like to delete every product from a products table that has a seller_id of 15. Which of the following statements is correct? (4 Points) DELETE product FROM products WHERE seller_id = 15; DROP FROM products WHERE seller_id == 15; DELETE FROM products WHERE seller_id = 15; DELETE FROM products WHERE seller_id <> 15:
17. Suppose that a store has a buyers table and an orders table; the orders table has a foreign-key constraint, such that every order has a buyer, but not every buyer has an order. Now suppose that you want to see a list of every buyer, whether they have orders or not, together with any orders they might have; how should you combine these tables? (3 Points) A SEARCH statement with a FROM clause A SELECT statement from the buyers table, using a FOR-EACH clause for filtering. A SELECT statement from the buyers table, inner-joined with the orders table. A SELECT statement from the buyers table, left-outer-joined with the orders table.
16. Suppose that a store has a buyers table and an orders table; the orders table has a foreign-key constraint, such that every order has a buyer, but not every buyer has an order. Suppose you wanted a list of every buyer and their orders, but you don't want to see buyers that don't have orders; how should you combibe these tables? (3 Points) A SEARCH statement with a FROM clause A SELECT statement from the buyers table, using a FOR-EACH clause for filtering. A SELECT statement from the buyers table, inner-joined with the orders table. A SELECT statement from the buyers table, left-outer-joined with the orders table.
15. Someone is trying to build a store-front to sell sci-fi devices, and they use the following SQL statements to start building their database. If both of these SQL statements are run, what will be the result? CREATE TABLE products ( id INT PRIMARY KEY, description VARCHAR(256) NOT NULL ); INSERT INTO products(id, description) VALUES (1, "Flux capacitor"), (2, "Tachyon inverter"), (3, "Interocitor"), (4, "DeLorean"); (3 Points) O The first statement will produce an error. The second statement will produce an error. Both statements will work, but the result won't be what was intended. Both statements will work, and the results will be correct.
14. Consider the following two SQL snippets, referring to a database for a hypothetical store: SELECT products.name, seller.name FROM products INNER JOIN sellers ON products.seller_id = sellers.id; SELECT products.name, seller.name FROM products. RIGHT OUTER JOIN sellers ON products.seller_id = sellers.id; Assume that products.seller_id is a foreign key referring to sellers.id, also assume that all products have sellers, but not all sellers have products. Will these two queries produce the same results? (4 Points) The two queries will produce the same results. The first query will produce rows that the second query won't. The second query will produce rows that the first query won't. Each query will produce rows that the other won't. There isn't enough information to tell.
13. Consider the following two SQL snippets, referring to a database for a hypothetical store: SELECT products.name, sellers.name FROM products INNER JOIN sellers ON products.seller_id = sellers.id; SELECT products.name, seller.name FROM products LEFT JOIN sellers ON products.seller_id = sellers.id; Assume that products.seller_id is a foreign key referring to sellers.id; also assume that all products have sellers, but not all sellers have products. Will these two queries produce the same results? (4 Points) The two queries will produce the same results. The first query will produce rows that the second query won't. The second query will produce rows that the first query won't. Each query will produce rows that the other won't. There isn't enough information to tell.
11. True or false: once a row has been added to the table, it can be modified or nulled, but it cannot be completely erased. (2 Points) True False 12. Which of the following is not a fundamental SQL operation? (2 Points) O UPDATE OBRANCH FORK SELECT D More than one of the above is not a fundamental operation All of the above are fundamental operations
8. True or false: you can use an inner join to join a table to itself. (2 Points) True False 9. True or false: you can use a left outer join to join a table to itself. (2 Points) True False 10. True or false: a right outer join is logically equivalent to an inner join, but has a different performance profile. (2 Points) True False
6. Is the following snippet of SQL valid? If it is, what will it produce? SELECT CONCAT('the user', "s ID'); (2 Points) Yes, it will produce "the user's ID" Yes, it will produce "the users ID" Yes, it will produce "The user's #", where # will be the user's numeric ID. No, it is not valid A 7. Is the following SQL snippet valid? If it is, what does it produce? SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d'); (2 Points) Yes, it produces output like "2022-05-18" Yes, it produces output like "2022-May-Wednesday" Yes, it produces output like "2022-%m-%d". No, it is not valid.
4. Which of the following is not an SQL keyword? (2 Points) O WHERE JOIN AS WHILE 5. Is the following SQL snippet valid? If it is, what will it produce? SELECT CONCAT("Hello", "world"); (2 Points) Yes, it will produce "Hello world" Yes, it will produce "Hello, world" 4 Yes, it will produce "Helloworld" No, it is not valid