D426 Chapter 2A — Practice Test (33 Q)

WGU D426 zyBooks Ch 2, sections 2.1–2.6 (2.5 Null values transcribed from the live zyBook because it didn't print). 33 questions (front: question + options A–D; back: answer + explanation). Not official WGU or zyBooks material.

Q1. (Scenario) A restaurant-reservation app's team lists four database tasks. Which task is written in Data Manipulation Language (DML)? | A) Retrieving tonight's reservations to show on the host stand's screen | B) Adding a SpecialRequests column to the Reservation table | C) Deleting a reservation's row after the guest cancels | D) Letting a new host's account read the Reservation table

C) Deleting a reservation's row after the guest cancels. Data Manipulation Language inserts, updates, and deletes data, so deleting a cancelled reservation's row is DML. Retrieving rows (A) is Data Query Language, which the book lists separately even though the MySQL documentation files both under Data Manipulation Statements. Adding a column (B) changes the table's structure, which is Data Definition Language's job, and letting an account read a table (D) is Data Control Language, which specifies user access to data. (§2.1, §2.3)

Q2. (Scenario) A clinic's database administrator runs two statements: (1) START TRANSACTION; before the statements that record a patient's transfer, and (2) GRANT ALL ON clinic.Visit TO 'intake'; so the intake account can use the Visit table. Which pairing names each statement's sublanguage? | A) (1) DML; (2) DDL | B) (1) DTL; (2) DCL | C) (1) DCL; (2) DTL | D) (1) DTL; (2) DDL

B) (1) DTL; (2) DCL. Data Transaction Language manages transactions (the book's DTL example is START TRANSACTION; followed later by COMMIT;), and Data Control Language specifies user access to data (its examples are CREATE USER and GRANT). The transfer statements that follow would be DML, but START TRANSACTION itself manages the transaction, and GRANT controls access rather than defining structure, so DDL (A, D) doesn't fit; C swaps the two. (§2.1)

Q3. (Scenario) During a code review for a food-truck app, a developer labels the parts of this statement: SELECT TruckName FROM FoodTruck WHERE MenuItems > 25; Which labeling is correct? | A) SELECT is a keyword, and FoodTruck is a literal | B) 25 is a literal, and WHERE is an identifier | C) The statement has three clauses, and MenuItems > 25 is a literal | D) TruckName is an identifier, and MenuItems > 25 is an expression

D) TruckName is an identifier, and MenuItems > 25 is an expression. TruckName (like FoodTruck and MenuItems) is an identifier, the name of a database object specified by the programmer, and MenuItems > 25 is an expression, a sequence of literals, identifiers, and operations that evaluates to a single value. Each distractor is half right: FoodTruck is an identifier, not a literal (A); WHERE is a keyword, not an identifier (B); and the statement does have three clauses, but MenuItems > 25 is an expression, while 25 alone is the literal (C). (§2.1)

Q4. Which statement about writing SQL is correct, according to the zyBook? | A) A statement may span several lines, because line breaks within a statement are ignored | B) Each clause must start on its own new line, or the language processor rejects the statement | C) String literals are case-sensitive only when a system parameter turns this on | D) A single-line comment begins with two dashes and must end with a semicolon

A) A statement may span several lines, because line breaks within a statement are ignored. Line breaks within a statement are ignored, so one statement may span several lines; writing each clause on a separate line is a good practice, not a requirement (B). String literals are always case-sensitive; it's keywords and identifiers whose case-sensitivity varies by database system and can usually be set with a system parameter (C). Single-line comments begin with -- followed by a space, and it's statements, not comments, that end with a semicolon (D). (§2.1)

Q5. Use this MySQL syntax definition: INSERT [INTO] tbl_name [(col_name [, col_name] ...)] {VALUES|VALUE} (value_list) [, (value_list)] ... Which statement follows it for a Rental table whose two columns are RentalID and Gear? | A) INSERT INTO Rental VALUES 77, 'Kayak'; | B) INSERT INTO Rental VALUES VALUE (77, 'Kayak'); | C) INSERT Rental VALUE (77, 'Kayak'); | D) INSERT INTO Rental [RentalID, Gear] VALUES (77, 'Kayak');

C) INSERT Rental VALUE (77, 'Kayak'); — Square brackets mark INTO and the column list as optional, and curly braces mean one, and only one, of VALUES or VALUE must appear, so INSERT Rental VALUE (77, 'Kayak'); fits. Parentheses are literal symbols that appear exactly as written, so the value list needs them (A); B includes both alternatives instead of one; and brackets are notation for an optional element, not symbols typed into the statement (D). (§2.1)

Q6. (Scenario) A study partner says, "DDL, DQL, DML, DTL, and DCL are five separate parts of the SQL standard." Which response is accurate? | A) That's right; ANSI and ISO publish each sublanguage as its own part | B) Not quite; all five sublanguages are specified in one part, called Foundation | C) Not quite; all five sublanguages are specified in the part called SQL/PSM | D) That's right, which is why the latest standard has exactly five active parts

B) Not quite; all five sublanguages are specified in one part, called Foundation. A part is a formal specification of syntax and behavior, while a sublanguage is an informal grouping of statements, and all five sublanguages are specified in one part, called Foundation. ANSI and ISO do publish the standard jointly, but not one part per sublanguage (A); SQL/PSM adds procedural statements such as loops and procedures (C); and the 2023 version has 16 parts, 11 of them active (D). (§2.1)

Q7. (Scenario) A game studio's reporting tool looks up which tables and columns exist in the studio's databases by querying the information_schema database. Which part of the SQL standard defines information_schema? | A) Foundation | B) SQL/PSM | C) SQL/XML | D) SQL/Schemata

D) SQL/Schemata. SQL/Schemata defines the information_schema database, which describes objects in user databases, and MySQL implements it. Foundation defines the core language, including the five sublanguages (A); SQL/PSM adds procedural statements such as loops and procedures (B); and SQL/XML defines the XML data type and related functions (C). (§2.1)

Q8. (Scenario) A cloud provider's server runs three separate executing copies of MySQL, one per customer, and each copy holds the mysql database plus that customer's own databases. What is each executing copy called? | A) A database system instance | B) A default database selected with USE | C) The mysql database | D) A user database

A) A database system instance. A database system instance is a single executing copy of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances, and each instance usually contains multiple system and user databases. The mysql database (C) is a system database that MySQL installs automatically for system administration, and USE picks a default database inside an instance (B). (§2.2)

Q9. (Scenario) A developer created a sandbox database for a ride-share app with CREATE DATABASE rideTest; and later added nine tables full of sample rows. She now runs this statement: DROP DATABASE rideTest; What is the result? | A) Only the empty tables are deleted, and tables that hold rows are kept | B) The statement fails until each table is first removed with DROP TABLE | C) The rows are deleted, but the nine empty tables remain in rideTest | D) The rideTest database is deleted, including all nine of its tables

D) The rideTest database is deleted, including all nine of its tables. DROP DATABASE DatabaseName deletes a database, including all tables in the database, so rideTest and its nine tables, rows and all, are gone. Nothing requires dropping the tables first (B), and no tables or rows are kept (A, C). (§2.2)

Q10. (Scenario) An airline's developer runs these statements in order: USE crewSchedule; SHOW TABLES FROM aircraftMaint; SHOW COLUMNS FROM Pilot; The crewSchedule and aircraftMaint databases each contain a table named Pilot. Which table's columns does the last statement list? | A) Pilot in aircraftMaint, because SHOW TABLES FROM changed the default database | B) Pilot in crewSchedule, because USE made crewSchedule the default database | C) Both Pilot tables, because SHOW COLUMNS searches every database on the instance | D) Neither; the statement fails because it doesn't name a database

B) Pilot in crewSchedule, because USE made crewSchedule the default database. USE DatabaseName selects a default database for use in subsequent statements, and SHOW COLUMNS FROM TableName lists all columns in that table of the default database. SHOW TABLES FROM aircraftMaint only lists that database's tables; it doesn't change the default the way USE does (A). (§2.2)

Q11. (Scenario) A game studio's developer wants the exact CREATE TABLE statement that built the Achievement table in the default database, so she can rebuild the table on a test server. Which statement shows it? | A) SHOW COLUMNS FROM Achievement; | B) SHOW TABLES FROM Achievement; | C) SHOW CREATE TABLE Achievement; | D) SHOW DATABASES;

C) SHOW CREATE TABLE Achievement; — SHOW CREATE TABLE TableName shows the CREATE TABLE statement for that table in the default database. SHOW COLUMNS FROM lists each column's name, data type, and other characteristics but not the statement (A); the FROM clause of SHOW TABLES takes a database name, not a table name (B); and SHOW DATABASES lists all databases in the instance (D). (§2.2)

Q12. (Scenario) A library creates a Hold table with four columns. No patron has placed a hold yet, so the table has no rows. Which statement is correct? | A) It is an empty table, which is valid because a table can have any number of rows | B) It isn't a valid table until at least one row has been inserted into it | C) Its four columns stay unnamed until the first inserted row supplies values for them | D) It holds one row of NULL values until real data is inserted

A) It is an empty table, which is valid because a table can have any number of rows. A table must have at least one column but can have any number of rows, and a table without rows is called an empty table. Columns have names and data types from the start; it's a row that is an unnamed sequence of values (C). An empty table has no rows at all, not a placeholder row of NULL values (D). (§2.3)

Q13. (Scenario) A reviewer checks two drafts of an animal clinic's Pet table. Draft 1 has the columns PetID, Species, OwnerPhone, and OwnerPhone. Draft 2 stores 'Rabies, Distemper' in a single Vaccines cell. Which pairing names the relational rule each draft breaks? | A) (1) No duplicate rows; (2) Exactly one value per cell | B) (1) No duplicate column names; (2) No row order | C) (1) No duplicate column names; (2) Exactly one value per cell | D) (1) Exactly one value per cell; (2) No duplicate column names

C) (1) No duplicate column names; (2) Exactly one value per cell. Draft 1 repeats a column name, breaking the rule of no duplicate column names (duplicates are allowed in different tables, but not in the same table). Draft 2 puts two values in one cell, breaking the rule of exactly one value per cell. D swaps the two, and neither draft involves identical rows (A) or row order (B). (§2.3)

Q14. (Scenario) To speed up queries, a hospital's database administrator plans to change how the LabResult table's rows are organized on its storage device. A manager asks whether this could change the results of existing queries. Which answer is correct? | A) Yes; rows keep the order they were stored in, so query results will change too | B) No; by data independence, storage organization never affects query results | C) No, but only because the LabResult table follows the no-duplicate-rows rule | D) Yes, unless the administrator first runs ALTER TABLE to lock the current row order

B) No; by data independence, storage organization never affects query results. Rule 4, no row order, is called data independence: the organization of rows on a storage device never affects query results, which is what lets administrators improve query performance by changing how data is stored. Rows have no stored order to keep (A), the no-duplicate-rows rule is a separate rule (C), and ALTER TABLE changes columns, not row order (D). (§2.3)

Q15. (Scenario) A bike-share program needs a Station table with StationID (an integer), StationName (up to 40 characters), OpensAt (a time of day, such as 06:30:00), and DockFee (an exact amount up to 99.99). Which statement creates it correctly? | A) CREATE TABLE Station (StationID INT, StationName VARCHAR(40), OpensAt DATE, DockFee DECIMAL(4,2)); | B) CREATE TABLE Station (INT StationID, VARCHAR(40) StationName, TIME OpensAt, DECIMAL(4,2) DockFee); | C) CREATE TABLE Station (StationID INT StationName VARCHAR(40) OpensAt TIME DockFee DECIMAL(4,2)); | D) CREATE TABLE Station (StationID INT, StationName VARCHAR(40), OpensAt TIME, DockFee DECIMAL(4,2));

D) CREATE TABLE Station (StationID INT, StationName VARCHAR(40), OpensAt TIME, DockFee DECIMAL(4,2)); — CREATE TABLE names the table, then lists each column name followed by its data type, with the column definitions separated by commas. TIME stores a time of day (format hh:mm:ss), while DATE stores a date (YYYY-MM-DD), and DECIMAL(4,2) holds exact values up to 99.99. A uses DATE for a time of day, B puts each data type before its column name, and C leaves out the commas. (§2.3, §2.4)

Q16. (Scenario) A school district's nightly import script starts by deleting the old AttendanceStage table so it can be rebuilt. The script must not fail on a night when the table doesn't exist. Which statement should start the script? | A) DROP TABLE AttendanceStage; | B) DROP TABLE IF EXISTS AttendanceStage; | C) DROP TABLE IF NOT EXISTS AttendanceStage; | D) DELETE FROM AttendanceStage;

B) DROP TABLE IF EXISTS AttendanceStage; — DROP TABLE fails if the named table does not exist, and the optional IF EXISTS clause avoids that failure. Plain DROP TABLE (A) would fail on such a night; IF NOT EXISTS (C) is the clause for CREATE TABLE, which fails if the named table already exists; and DELETE FROM (D) deletes rows, not the table. (§2.3, §2.6)

Q17. (Scenario) A music festival's Performer table has a column Stage VARCHAR(10). The team wants to rename the column StageName and change its data type to VARCHAR(30). Which statement does this? | A) ALTER TABLE Performer CHANGE Stage StageName VARCHAR(30); | B) ALTER TABLE Performer CHANGE StageName Stage VARCHAR(30); | C) ALTER TABLE Performer ADD StageName VARCHAR(30); | D) ALTER TABLE Performer CHANGE Stage StageName;

A) ALTER TABLE Performer CHANGE Stage StageName VARCHAR(30); — The CHANGE clause modifies a column, following the pattern ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType. B reverses the current and new names, C adds a second column and leaves Stage in place, and D leaves out the new data type that the CHANGE syntax includes. (§2.3)

Q18. (Scenario) A podcast app wants to remove the unused Nickname column from its Listener table while keeping every listener's row and all the other columns. Which statement does this? | A) DROP TABLE Listener; | B) DELETE FROM Listener; | C) ALTER TABLE Listener DROP Nickname; | D) ALTER TABLE Listener DELETE Nickname;

C) ALTER TABLE Listener DROP Nickname; — ALTER TABLE with the DROP clause deletes a column (ALTER TABLE TableName DROP ColumnName), leaving the rows and other columns in place. DROP TABLE (A) deletes the whole table along with all its rows, DELETE FROM with no WHERE clause (B) deletes every row, and DELETE isn't one of the three ALTER TABLE clauses, which are ADD, CHANGE, and DROP (D). (§2.3, §2.6)

Q19. (Scenario) A bike-share app stores (1) the exact bytes of each photo riders upload when they report a damaged bike and (2) the boundary of each service zone, drawn as a polygon on the city map. Which pairing names the data type category for each? | A) (1) Document; (2) Spatial | B) (1) Binary; (2) Spatial | C) (1) Spatial; (2) Binary | D) (1) Binary; (2) Document

B) (1) Binary; (2) Spatial. Binary data types store data exactly as the data appears in memory or computer files, bit for bit (ex: BLOB), and spatial data types store geometric information, such as lines, polygons, and map coordinates (ex: POLYGON). Document data types contain textual data in a structured format such as XML or JSON, which fits neither item (A, D), and C swaps the two. (§2.4)

Q20. (Scenario) A museum records how many visitors enter each day. The count is never negative and never exceeds 50,000. Which MySQL data type stores it with the least storage? | A) SMALLINT | B) MEDIUMINT UNSIGNED | C) INT | D) SMALLINT UNSIGNED

D) SMALLINT UNSIGNED. SMALLINT UNSIGNED takes 2 bytes and ranges from 0 to 65,535, so it covers 0 to 50,000. Signed SMALLINT (A) tops out at 32,767, while MEDIUMINT UNSIGNED (B, 3 bytes) and INT (C, 4 bytes) fit but use more storage than needed, and the book says the data type with the smallest storage requirements should be used. (§2.4)

Q21. (Scenario) A game's world map stores each tile's elevation in whole meters, from -400 (below sea level) to 8,900. Which MySQL data type stores these values with the least storage? | A) SMALLINT | B) TINYINT | C) SMALLINT UNSIGNED | D) MEDIUMINT

A) SMALLINT. Signed SMALLINT takes 2 bytes and ranges from -32,768 to 32,767, which covers -400 to 8,900. TINYINT (B) only reaches -128 to 127, SMALLINT UNSIGNED (C) can't store the negative elevations because an unsigned number cannot be negative, and MEDIUMINT (D) fits but takes 3 bytes. (§2.4)

Q22. (Scenario) An airline stores (1) each crew member's badge code, which is always exactly 6 characters, and (2) each crew member's full name, usually 10 to 25 characters but never more than 60. Following the zyBook's advice to minimize storage, which pairing fits best? | A) (1) VARCHAR(6); (2) CHAR(60) | B) (1) CHAR(6); (2) TEXT | C) (1) VARCHAR(6); (2) VARCHAR(60) | D) (1) CHAR(6); (2) VARCHAR(60)

D) (1) CHAR(6); (2) VARCHAR(60). CHAR(N) is a fixed-length string that takes N bytes, so the 6-character code fits CHAR(6) in 6 bytes, while VARCHAR(6) (A, C) would take the number of characters + 1 byte. The varying name fits VARCHAR(60), which takes the number of characters + 1 byte, while CHAR(60) (A) always takes 60 bytes and TEXT (B) takes the number of characters + 2 bytes. (§2.4)

Q23. (Scenario) A clinic's Invoice table must store each charge exactly, from 0.01 up to 9,999.99. Which MySQL data type fits? | A) FLOAT | B) DECIMAL(4,2) | C) DECIMAL(6,2) | D) DOUBLE

C) DECIMAL(6,2). DECIMAL(M,D) is an exact decimal number with M significant digits, D of them after the decimal point, so DECIMAL(6,2) reaches 9999.99. DECIMAL(4,2) (B) allows only 4 digits in all, up to 99.99, which catches anyone who reads M as the digits before the point, and FLOAT and DOUBLE (A, D) store approximate decimal numbers. (§2.4)

Q24. (Scenario) An animal shelter's Volunteer table was created with this statement: CREATE TABLE Volunteer (VolunteerID SMALLINT UNSIGNED, FullName VARCHAR(50), ShiftHours TINYINT UNSIGNED); Which statement correctly adds volunteer 310, Dana Ruiz, who works 6-hour shifts? | A) INSERT INTO Volunteer (ShiftHours, FullName, VolunteerID) VALUES (6, 'Dana Ruiz', 310); | B) INSERT INTO Volunteer (FullName, VolunteerID, ShiftHours) VALUES (310, 'Dana Ruiz', 6); | C) INSERT INTO Volunteer VALUES ('Dana Ruiz', 310, 6); | D) INSERT INTO Volunteer (VolunteerID, FullName, ShiftHours) VALUES (310, Dana Ruiz, 6);

A) INSERT INTO Volunteer (ShiftHours, FullName, VolunteerID) VALUES (6, 'Dana Ruiz', 310); — The value order must match the column order in the INTO clause, and that column list doesn't have to follow the table's order, so (ShiftHours, FullName, VolunteerID) pairs correctly with (6, 'Dana Ruiz', 310). B's values don't match its column list, C omits the column names, so its values must follow the table's column order, and D leaves the string literal unquoted, but string literals are enclosed in single or double quotes. (§2.6, §2.1)

Q25. (Scenario) A ferry company's Booking table and a new insert: CREATE TABLE Booking (BookingID INT, Passenger VARCHAR(40), Seats TINYINT UNSIGNED DEFAULT 1, Deck VARCHAR(10) DEFAULT 'Main'); INSERT INTO Booking (BookingID, Deck) VALUES (501, 'Upper'); Which values does the new row hold for Passenger, Seats, and Deck? | A) Passenger NULL, Seats 1, Deck 'Main' | B) Passenger NULL, Seats 1, Deck 'Upper' | C) Passenger NULL, Seats NULL, Deck 'Upper' | D) None; the insert is rejected because two columns are omitted

B) Passenger NULL, Seats 1, Deck 'Upper'. A column omitted from an INSERT gets its DEFAULT value when one is defined (Seats = 1) and NULL when not (Passenger). Deck was listed, so it takes the supplied 'Upper', not its default (A). An omitted column causes a rejected insert only when it has the NOT NULL constraint, and none of these do (D). (§2.6)

Q26. (Scenario) A food truck's owner wants to raise the price of menu item 14 only (ItemID = 14) but runs this statement: UPDATE MenuItem SET Price = 9.50; What happens? | A) Only the most recently added row gets the new Price of 9.50 | B) The statement fails, because an UPDATE must include a WHERE clause | C) Every row in MenuItem gets a Price of 9.50 | D) No rows change until a WHERE clause names the rows to update

C) Every row in MenuItem gets a Price of 9.50. The WHERE clause in an UPDATE is optional, and omitting it results in all rows being updated, so every menu item now costs 9.50. The statement doesn't fail (B) or wait for a WHERE clause (D), and nothing in it targets only the newest row (A). Adding WHERE ItemID = 14 would have limited the change to one row. (§2.6)

Q27. (Scenario) In a scooter-rental company's Scooter table, scooter 88 (ScooterID = 88) must get the Status 'Retired' and the Location 'Depot'. Which statement changes both columns for that scooter only? | A) UPDATE Scooter SET Status = 'Retired', Location = 'Depot' WHERE ScooterID = 88; | B) UPDATE Scooter SET Status = 'Retired' AND Location = 'Depot' WHERE ScooterID = 88; | C) UPDATE Scooter WHERE ScooterID = 88 SET Status = 'Retired', Location = 'Depot'; | D) INSERT INTO Scooter (Status, Location) VALUES ('Retired', 'Depot') WHERE ScooterID = 88;

A) UPDATE Scooter SET Status = 'Retired', Location = 'Depot' WHERE ScooterID = 88; — UPDATE uses the SET clause to list the new values as Column1 = Value1, Column2 = Value2, separated by commas, and the optional WHERE clause follows SET to pick the rows. AND joins parts of a condition, not assignments (B); the clauses must come in the order UPDATE, SET, WHERE (C); and INSERT adds new rows instead of modifying existing ones (D). (§2.6)

Q28. (Scenario) A climbing gym's Locker table holds these rows (LockerID, Size, Floor): 1, Small, 1; 2, Large, 3; 3, Small, 2; 4, Small, 3; 5, Large, 1. Which lockers remain after this statement runs? DELETE FROM Locker WHERE Size = 'Small' AND Floor > 1; | A) Lockers 3 and 4 only | B) Locker 5 only | C) Lockers 2 and 5 only | D) Lockers 1, 2, and 5

D) Lockers 1, 2, and 5. DELETE removes only the rows where the WHERE condition is true, and with AND both parts must hold, as in the book's example that deletes salaries between 40,000 and 80,000. Lockers 3 and 4 are Small and above floor 1, so they are the rows deleted (A), leaving lockers 1, 2, and 5. B reads AND as OR, and C ignores the Floor condition. (§2.6)

Q29. (Scenario) At the end of each season, a trivia league wants to remove every row from its Standings table but keep the table and its columns for next season. Which pair of statements would each do this? | A) DROP TABLE Standings; and DELETE FROM Standings; | B) DELETE FROM Standings; and TRUNCATE TABLE Standings; | C) ALTER TABLE Standings DROP Points; and DELETE FROM Standings; | D) TRUNCATE TABLE Standings; and DROP TABLE Standings;

B) DELETE FROM Standings; and TRUNCATE TABLE Standings; — A DELETE statement with no WHERE clause deletes all rows in the table, and TRUNCATE deletes all rows from a table, nearly identical to DELETE with no WHERE clause except for minor differences that depend on the database system. DROP TABLE (A, D) deletes the table itself along with its rows, and ALTER TABLE with the DROP clause (C) deletes a column, not rows. (§2.6, §2.3)

Q30. (Scenario) A ski resort's Lift table has the columns LiftID, LiftName, and Seats, in that order. Which single statement correctly adds lift 4 (Summit Express, 6 seats) and lift 5 (Bear Run, 4 seats)? | A) INSERT INTO Lift VALUES (4, 'Summit Express'), (5, 'Bear Run'); | B) INSERT INTO Lift (LiftID, LiftName, Seats) VALUES (4, 'Summit Express', 6, 5, 'Bear Run', 4); | C) INSERT INTO Lift VALUES ('Summit Express', 4, 6), ('Bear Run', 5, 4); | D) INSERT INTO Lift VALUES (4, 'Summit Express', 6), (5, 'Bear Run', 4);

D) INSERT INTO Lift VALUES (4, 'Summit Express', 6), (5, 'Bear Run', 4); — Column names may be omitted as long as the VALUES clause lists all column values in the same order as the table's columns, and one INSERT can add any number of rows, each value list in its own parentheses. A leaves out the Seats values without naming columns, B squeezes two rows into one value list, and C puts the values in the wrong order. (§2.6, §2.1)

Q31. (Scenario) A food-delivery app's Courier table has the columns VehiclePlate VARCHAR(10) and WeeklyTips DECIMAL(6,2). Courier 14 rides a bicycle, which has no plate. Courier 22 drives a car whose plate number hasn't been reported yet. Courier 31 worked all week and received no tips. Which values follow the zyBook's guidance on NULL? | A) 14: VehiclePlate NULL; 22: VehiclePlate NULL; 31: WeeklyTips 0.00 | B) 14: VehiclePlate blank; 22: VehiclePlate NULL; 31: WeeklyTips NULL | C) 14: VehiclePlate NULL; 22: VehiclePlate blank; 31: WeeklyTips 0.00 | D) 14: VehiclePlate NULL; 22: VehiclePlate NULL; 31: WeeklyTips NULL

A) 14: VehiclePlate NULL; 22: VehiclePlate NULL; 31: WeeklyTips 0.00. NULL is a special value that represents either unknown or inapplicable data: courier 22's plate exists but isn't known yet (unknown), and a bicycle has no plate at all (inapplicable), so both are NULL. NULL is not the same as blanks for character data types, so a blank plate (B, C) isn't the way to record either case. Courier 31's zero tips are known and applicable, like the book's zero bonus for an employee who can, but hasn't, earned a bonus, so WeeklyTips is 0.00, not NULL (B, D). (§2.5)

Q32. (Scenario) A coworking space's Member table must always store each member's MemberID and FullName. CompanyName may be left NULL, since some members are freelancers and others haven't said where they work. Which statement creates the table with the right constraints? | A) CREATE TABLE Member (MemberID SMALLINT UNSIGNED, FullName VARCHAR(60), CompanyName VARCHAR(40) NOT NULL); | B) CREATE TABLE Member (MemberID SMALLINT UNSIGNED NOT NULL, FullName VARCHAR(60) NOT NULL, CompanyName VARCHAR(40)); | C) CREATE TABLE Member (NOT NULL MemberID SMALLINT UNSIGNED, NOT NULL FullName VARCHAR(60), CompanyName VARCHAR(40)); | D) CREATE TABLE Member (MemberID SMALLINT UNSIGNED NOT NULL, FullName VARCHAR(60), CompanyName VARCHAR(40) NOT NULL);

B) CREATE TABLE Member (MemberID SMALLINT UNSIGNED NOT NULL, FullName VARCHAR(60) NOT NULL, CompanyName VARCHAR(40)); — The NOT NULL constraint prevents a column from having a NULL value, and it follows the column name and data type in a CREATE TABLE statement, so MemberID and FullName each get NOT NULL, while CompanyName is left without it, since columns may contain NULL values by default. A puts the constraint only on the one column that must allow NULL, C writes NOT NULL before the column name, and D leaves FullName open to NULL while blocking the NULL that CompanyName needs for freelancers. (§2.5)

Q33. (Scenario) A blood-donation center's Donation table was created with this statement: CREATE TABLE Donation (DonationID INT UNSIGNED NOT NULL, DonorName VARCHAR(50) NOT NULL, BloodType VARCHAR(3), VolumeMl SMALLINT UNSIGNED); Then statements 1 and 2 run in order. Statement 1: INSERT INTO Donation (DonationID, DonorName, BloodType, VolumeMl) VALUES (7001, 'K. Osei', NULL, 450); Statement 2: UPDATE Donation SET DonorName = NULL WHERE DonationID = 7001; What is the result? | A) Both statements succeed, because NULL is not the same as a blank | B) Statement 1 is rejected because BloodType is NULL; statement 2 succeeds | C) Statement 1 succeeds, and statement 2 is automatically rejected | D) Both succeed, but statement 2 stores a blank instead of NULL in DonorName

C) Statement 1 succeeds, and statement 2 is automatically rejected. BloodType has no NOT NULL constraint, and columns may contain NULL values by default, so statement 1 adds the row with an unknown BloodType (not B). DonorName is NOT NULL, and statements that insert NULL, or update a value to NULL, are automatically rejected, so statement 2 fails (not A). The database doesn't swap in a blank either (D): NULL is not the same as blanks for character data types, and the update is simply rejected. (§2.5)