Database programming practice questions.

The table MEMBER has the columns MEMBERID, SURNAME and CITY. Which statement lists the surnames of the members living in Bern in reverse alphabetical order?

• SELECT SURNAME FROM MEMBER WHERE CITY = 'Bern' ORDER BY SURNAME DESC
• SELECT SURNAME FROM MEMBER ORDER BY SURNAME DESC WHERE CITY = 'Bern'
• SELECT SURNAME FROM MEMBER WHERE CITY = 'Bern' ORDER BY SURNAME ASC
• SELECT SURNAME FROM MEMBER WHERE CITY = 'Bern' AND ORDER BY SURNAME DESC

Answer: SELECT SURNAME FROM MEMBER WHERE CITY = 'Bern' ORDER BY SURNAME DESC

The clause sequence SELECT - FROM - WHERE - ORDER BY is fixed, so a sort placed before the row filter is a syntax error, and clauses are never joined by AND. DESC gives Z to A; ASC would return the same rows in the opposite order.

A database user runs a DELETE statement on the table STOCK but forgets to write a WHERE clause. What is the effect on the database?

• The statement is rejected, because a DELETE statement must always name a search condition
• Every row is removed, but the table STOCK and its column definitions stay in place
• The table STOCK disappears from the database together with all of its rows
• Only the row that was inserted first is removed from the table STOCK

Answer: Every row is removed, but the table STOCK and its column definitions stay in place

A DELETE without a condition simply matches every row, so the data is lost while the empty table survives and can be filled again. Removing the structure itself is the job of DROP TABLE, which is the confusion this question targets.

The price of every product in the category 'Tools' has to be raised by 10 per cent. Which statement does this?

• UPDATE PRODUCT SET PRICE = 1.1 WHERE CATEGORY = 'Tools'
• INSERT INTO PRODUCT SET PRICE = PRICE * 1.1 WHERE CATEGORY = 'Tools'
• UPDATE PRICE FROM PRODUCT SET PRICE = PRICE * 1.1 WHERE CATEGORY = 'Tools'
• UPDATE PRODUCT SET PRICE = PRICE * 1.1 WHERE CATEGORY = 'Tools'

Answer: UPDATE PRODUCT SET PRICE = PRICE * 1.1 WHERE CATEGORY = 'Tools'

UPDATE names the table, SET describes how the stored value is derived from itself, and WHERE limits the change to the wanted rows. Writing SET PRICE = 1.1 replaces the price of every Tools row with the same fixed number instead of scaling the value it already holds.

Which condition selects exactly those surnames that begin with the letters 'Sch' and are five characters long, and no others?

• SURNAME LIKE 'Sch__'
• SURNAME LIKE 'Sch%'
• SURNAME LIKE '%Sch'
• SURNAME LIKE '_Sch%'

Answer: SURNAME LIKE 'Sch__'

In a LIKE pattern an underscore stands for exactly one character and a percent sign for any number of them, so 'Sch' followed by two underscores fixes the total length at five. The percent pattern also returns longer names such as Schneider, and a pattern that starts with a placeholder accepts names that merely end with or contain 'Sch'.

In a library database the table LOAN holds BOOKID as a foreign key referring to BOOK. A user tries to delete a BOOK row that a LOAN row still refers to. How does a database that enforces referential integrity react?

• It deletes the BOOK row and leaves the LOAN row referring to a book that no longer exists
• It deletes the BOOK row and gives the orphaned LOAN row a primary key value of its own
• It refuses the deletion while a LOAN row still refers to that BOOK
• It deletes the BOOK row together with every LOAN row created on the same date

Answer: It refuses the deletion while a LOAN row still refers to that BOOK

Referential integrity guarantees that every foreign key value points at an existing primary key, so the deletion is blocked until the child rows are removed or redirected. Believing that the constraint only checks inserts is the usual gap in understanding.

An extra column MOBILE has to be added to the existing table MEMBER without losing the rows that are already stored. Which statement is used?

• UPDATE MEMBER ADD MOBILE CHAR(12)
• CREATE TABLE MEMBER ADD MOBILE CHAR(12)
• INSERT INTO MEMBER VALUES MOBILE CHAR(12)
• ALTER TABLE MEMBER ADD MOBILE CHAR(12)

Answer: ALTER TABLE MEMBER ADD MOBILE CHAR(12)

Changing the structure of a table that already exists is a data definition task, and ALTER TABLE is the only statement here that performs one. UPDATE and INSERT INTO work on rows, and CREATE TABLE would demand a table name that is still free.