- Practice
- About 11 min
- 5 practice questions
Commands, data types and database objects: match-up practice
Classify SQL commands, pick data types, and distinguish tables, queries, forms and reports in quick match-up questions.
If something looks wrong, please tell us.
Many marks come from quick matching: which command belongs to which group, which object does which job, which short form means what. These are memory questions, so the best preparation is a clear table and a few traps to avoid.
SQL command groups
| Group | Full name | Commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP |
| DML | Data Manipulation Language | SELECT, INSERT, UPDATE, DELETE |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK |
Database objects and their jobs
| Object | Job |
|---|---|
| Table | Stores the data in rows and columns |
| Query | Asks a question and returns matching data |
| Form | A screen for entering or viewing one record at a time |
| Report | A formatted, printable summary of data |
| View | A stored query that acts like a virtual table |
| Index | A lookup aid that speeds up searching |
Short forms
- DBMS: Database Management System; RDBMS: Relational DBMS.
- SQL: Structured Query Language.
- DBA: Database Administrator.
- ER diagram: Entity-Relationship diagram.
- ACID: Atomicity, Consistency, Isolation, Durability, the properties of a reliable transaction.
Worked examples
Example 1. Classify: (a) ALTER TABLE, (b) INSERT INTO, (c) REVOKE, (d) COMMIT. (a) ALTER changes structure, so DDL. (b) INSERT adds data, so DML. (c) REVOKE withdraws permission, so DCL. (d) COMMIT saves a transaction, so TCL.
Example 2. The school wants a printed list of students grouped by class with a title and page numbers. Which object? A report. A query would give the data, but not the formatted printout.
Example 3. Choose a data type: (a) a student's date of admission, (b) whether a book is returned, (c) the price of a book, (d) an STD code such as 0471, which begins with 0. Answers: (a) date, (b) Yes/No (Boolean), (c) decimal or currency, (d) text, so that a leading zero is not lost.
Example 4. A transaction transfers 500 from account X to account Y. The debit succeeds but the system crashes before the credit. Which property demands that the whole transaction be undone? Atomicity: all or nothing. After recovery, X's debit is rolled back.
Example 5. Statement A: 'A table can have more than one primary key.' Statement B: 'A table can have more than one candidate key.' Only B is true. A table has one primary key, though it may have several candidate keys.
DDL changes structure, DML changes data, DCL changes permissions, TCL changes the fate of a transaction.
Example 6. A clerk should be allowed to read the STUDENT table but not change it. Which command gives that permission, and which group is it in? GRANT SELECT ON STUDENT TO the clerk. GRANT belongs to DCL, since it controls access and does not touch the data itself.
Traps to avoid
- Putting SELECT under DDL. It reads data, so it is DML.
- Calling a report a form. Forms are for entry; reports are for output.
- Choosing a number type for phone or PIN codes.
- Confusing DELETE (removes rows) with DROP (removes the whole table).
Key takeaways
- Learn the four command groups with an example of each.
- Match object to job: table stores, query asks, form enters, report prints.
- Identifiers like phone and PIN code are text.
Check your understanding
-
1. CREATE, ALTER and DROP belong to
- DML
- DDL
- DCL
- TCL
Show answer
Answer: B. These commands define or change structure, so they are in Data Definition Language.
-
2. Which object is used mainly to print a formatted summary?
- Form
- Query
- Index
- Report
Show answer
Answer: D. A report presents data in a layout suitable for printing.
-
3. COMMIT is a command of
- DDL
- DML
- TCL
- DCL
Show answer
Answer: C. COMMIT ends a transaction and saves its changes, so it is a transaction control command.
-
4. Which data type is best for a telephone STD code such as 0471?
- Text
- Integer
- Date
- Decimal
Show answer
Answer: A. Treated as a number, the leading zero would be lost. Text keeps it.
-
5. The property that says a transaction happens completely or not at all is
- Isolation
- Atomicity
- Durability
- Indexing
Show answer
Answer: B. Atomicity means all of the transaction is done or none of it is.
Sign in to keep track of the lessons you finish.