SQL Data Entry and Queries Practice Quiz (17 questions)

All questions and answers

1. An `age` check uses `CHECK (age BETWEEN 12 AND 70)`. Which age is accepted by the constraint?

12
10
11
71

12

Explanation

`BETWEEN 12 AND 70` includes both endpoints, so age 12 is accepted. Age 11 falls below the range, age 71 exceeds it, and 10 is also below the lower bound.

2. Which definition correctly specifies the `adherents` table’s columns and primary key?

cin CHAR(8) PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), email VARCHAR(50), age INT, adresse VARCHAR(40)
cin CHAR(8) PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), email VARCHAR(40), age INT, adresse VARCHAR(50)
cin CHAR(8) PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), email VARCHAR(40), age VARCHAR(30), adresse VARCHAR(50)
cin CHAR(4) PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), email VARCHAR(40), age INT, adresse VARCHAR(50)

cin CHAR(8) PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), email VARCHAR(40), age INT, adresse VARCHAR(50)

Explanation

The `adherents` definition uses `cin CHAR(8)` as its primary key, the specified `VARCHAR` widths for the text fields, and `INT` for age. The alternatives alter the key length, swap the email and address widths, or change age’s type.

3. A table has a column named adresse, which must be renamed ville and given the type VARCHAR(30); which ALTER TABLE operation does this?

CHANGE adresse ville VARCHAR(30)
MODIFY adresse ville VARCHAR(30)
CHANGE ville adresse VARCHAR(30)
ADD ville VARCHAR(30)

CHANGE adresse ville VARCHAR(30)

Explanation

CHANGE takes the old column name, the new name, and the type, in that order. MODIFY changes a column definition without renaming it, ADD creates a column, and reversing the names would rename ville to adresse.

4. A table’s named age constraint ckAge currently allows ages from 12 through 70, and the minimum must become 10 while the maximum stays 70; what should the administrator do?

Drop ckAge, then add ckAge with age BETWEEN 12 AND 70
Drop ckAge and leave the age condition absent
Modify ckAge in place to use age BETWEEN 10 AND 70
Drop ckAge, then add ckAge with age BETWEEN 10 AND 70

Drop ckAge, then add ckAge with age BETWEEN 10 AND 70

Explanation

A named constraint is changed by dropping it and adding it again with the updated condition, preserving the upper limit of 70. Modifying it in place skips the required replacement, leaving it dropped removes the condition, and retaining 12 fails to lower the minimum.

5. A table named `visitors` needs a single-column primary key on `visitorId`, whose type is `CHAR(6)`, plus a `name` column of type `VARCHAR(25)`. Which definition correctly creates the table?

CREATE TABLE visitors (visitorId PRIMARY KEY, name VARCHAR(25));
CREATE TABLE visitors (visitorId CHAR(6) PRIMARY KEY, name VARCHAR(25));
CREATE TABLE visitors (visitorId CHAR(6), name VARCHAR(25), PRIMARY KEY (visitorId));
CREATE TABLE visitors (visitorId CHAR(6), name VARCHAR(25), PRIMARY KEY (visitorId, name));

CREATE TABLE visitors (visitorId CHAR(6) PRIMARY KEY, name VARCHAR(25));

Explanation

A single-column primary key can be declared directly alongside that column’s type in `CREATE TABLE`. A composite declaration adds an unintended key column, omitting types leaves the columns undefined, and a table-level single-column declaration is not the specified column-level form.

6. A report should list activity prices from highest to lowest. Which ordering clause should be used?

ORDER BY tarif > 0
ORDER BY tarif
ORDER BY tarif DESC
ORDER BY libelle DESC

ORDER BY tarif DESC

Explanation

ORDER BY tarif DESC sorts prices in descending order, from highest to lowest. ORDER BY tarif defaults to ascending order; sorting by libelle uses the wrong column, and a comparison condition is not a sort direction.

7. A `members.fullName` column is `VARCHAR(60)` and must be made mandatory. Which `ALTER TABLE` clause follows the required `MODIFY` syntax?

MODIFY fullName NOT NULL
MODIFY fullName VARCHAR(60) NOT NULL
MODIFY fullName VARCHAR(60) DEFAULT ''
ADD CONSTRAINT fullNameRequired NOT NULL (fullName)

MODIFY fullName VARCHAR(60) NOT NULL

Explanation

`MODIFY` makes the column mandatory by restating its type and adding `NOT NULL`. Omitting `VARCHAR(60)` leaves out the required type, `ADD CONSTRAINT` is not the stated method for this change, and a default value does not impose `NOT NULL`.

8. A database designer adds an eight-digit telephone number to a table; which column definition stores it as text rather than as a value for arithmetic?

telephone INT
telephone CHAR(8)
CHANGE telephone CHAR(8)
telephone VARCHAR(8)

telephone CHAR(8)

Explanation

The column is added with a character type because a telephone number is an identifier, not a quantity for calculations. INT treats it as numeric, CHANGE renames an existing column, and VARCHAR(8) is variable-length rather than the specified CHAR(8).

9. A `uqEmail` unique constraint is applied to `adherents.email`, which already contains `[email protected]`. What happens when another adherent is inserted with that same email?

The insert is rejected because the value lacks an at-sign.
The insert is rejected as a duplicate, with error 1062.
The insert succeeds because the email format is valid.
The insert succeeds unless the name is also duplicated.

The insert is rejected as a duplicate, with error 1062.

Explanation

A unique constraint prevents two adherents from having the same email, so the duplicate is rejected with error 1062. A valid email format does not override uniqueness; an at-sign check is a separate rule, and the constraint concerns the email column rather than the name.

10. A query should return activities whose price is strictly below 40. Which WHERE condition applies the required comparison?

tarif <= 40
tarif < 40
tarif > 40
tarif = 40

tarif < 40

Explanation

The less-than operator selects values strictly below 40. Using <= includes 40, > reverses the direction, and = selects only values equal to 40.

11. A column has a default value of 20, and an INSERT statement leaves that column out of its column list; what value does the new row receive in that column?

An explicit NULL value
The numeric value 0
No value, regardless of the default
The column's default value, 20

The column's default value, 20

Explanation

When a column is omitted from the INSERT column list, its default is used, so this row receives 20. Explicit NULL is a supplied value, while zero and leaving the column without its default do not follow the stated rule.

12. A query should display every column in a table named members. Which SELECT clause does this?

SELECT * FROM columns
SELECT * FROM members
SELECT members FROM *
SELECT columns FROM members

SELECT * FROM members

Explanation

The asterisk in SELECT * requests every column from the chosen table. Listing column names would return only those specified; treating the table name or the word columns as a wildcard confuses table and column references.

13. An `activites` table requires `libelle` to be mandatory and `tarif` to be greater than zero under the named constraint `ckTarif`. Which pair of changes applies those two rules?

ADD CONSTRAINT ckTarif CHECK (libelle IS NOT NULL); MODIFY tarif DECIMAL(6,3) NOT NULL
MODIFY libelle VARCHAR(30); ADD CONSTRAINT ckTarif CHECK (tarif > 0); MODIFY tarif DECIMAL(6,3) NOT NULL
MODIFY libelle VARCHAR(30) NOT NULL; ADD CONSTRAINT ckTarif CHECK (tarif > 0)
MODIFY libelle VARCHAR(30) NOT NULL; ADD CONSTRAINT ckTarif CHECK (tarif >= 0)

MODIFY libelle VARCHAR(30) NOT NULL; ADD CONSTRAINT ckTarif CHECK (tarif > 0)

Explanation

The label’s mandatory status is set with `MODIFY ... NOT NULL`, while the named `ckTarif` check separately requires `tarif > 0`. A nonnegative check accepts zero, and making the tariff mandatory does not make the label mandatory.

14. A new table `registrations` uses `memberCode` and `eventCode` together as its primary key. Which declaration correctly defines that key?

PRIMARY KEY (memberCode), PRIMARY KEY (eventCode)
PRIMARY KEY (memberCode)
PRIMARY KEY (memberCode, eventCode)
memberCode PRIMARY KEY, eventCode PRIMARY KEY

PRIMARY KEY (memberCode, eventCode)

Explanation

A composite primary key is declared once at the end of the table with both columns listed together. Separate primary-key declarations are invalid, while either single-column declaration fails to make the key composite.

15. A proposed INSERT supplies an email already used by another row in a UNIQUE column; what happens when the database enforces its constraints?

The database replaces the email with a unique value
The database rejects the INSERT
The database accepts the row and issues a warning
The database accepts the row unless email is a primary key

The database rejects the INSERT

Explanation

The database rejects an INSERT that violates a UNIQUE constraint. Treating the violation as a warning, limiting rejection to primary keys, or assuming automatic correction does not match constraint enforcement.

16. A member should be included only if they live in Sousse and are older than 20. Which logical operator should connect the two conditions?

OR
<>
ORDER BY
AND

AND

Explanation

AND returns rows only when both conditions are true, matching the requirement that the member satisfy both criteria. OR accepts a row meeting either criterion, while <> and ORDER BY do not connect filter conditions this way.

17. When inserting a row, a person's telephone number is unknown; how should the value be written to represent an unknown value rather than a literal hyphen?

'NULL'
'-'
0
NULL

NULL

Explanation

NULL represents an unknown value and is written without apostrophes. A quoted hyphen is a literal string, quoted NULL is text, and zero is a known numeric value rather than an unknown.

Study with flashcards

Lock in the answers with 39 flashcards on SQL Data Entry and Queries.

How do you declare a single-column primary key when creating a table?

Add `PRIMARY KEY` to that column’s definition.

Which column is the primary key of the `adherents` table?

`cin CHAR(8)`.

Which fields and types make up the `adherents` table besides its primary key?

`nom VARCHAR(30)`, `prenom VARCHAR(30)`, `email VARCHAR(40)`, `age INT`, and `adresse VARCHAR(50)`.

See the flashcards →

Read the study guide

Go over the full study guide for SQL Data Entry and Queries.

Read the study guide →

Related classes

Make your own practice quizzes

Upload your class notes and Revizly writes a practice quiz with explained answers in about a minute.

Quiz maker