Database Basics
Practice questions
The property that says a transaction happens completely or not at all is
- Isolation
- Atomicity
- Durability
- Indexing
Answer and explanation
Answer: B
Atomicity means all of the transaction is done or none of it is.
Which data type is best for a telephone STD code such as 0471?
- Text
- Integer
- Date
- Decimal
Answer and explanation
Answer: A
Treated as a number, the leading zero would be lost. Text keeps it.
COMMIT is a command of
- DDL
- DML
- TCL
- DCL
Answer and explanation
Answer: C
COMMIT ends a transaction and saves its changes, so it is a transaction control command.
Which object is used mainly to print a formatted summary?
- Form
- Query
- Index
- Report
Answer and explanation
Answer: D
A report presents data in a layout suitable for printing.
CREATE, ALTER and DROP belong to
- DML
- DDL
- DCL
- TCL
Answer and explanation
Answer: B
These commands define or change structure, so they are in Data Definition Language.
A foreign key
- Must stay unique in its table
- Is always left as NULL
- Is the same as the degree
- Points to another table's key
Answer and explanation
Answer: D
A foreign key stores values that match the primary key of a related table.
Table R(A, B, C) has A as its only candidate key. How many superkeys does it have?
- 4
- 1
- 2
- 8
Answer and explanation
Answer: A
A superkey must contain A; B and C are each optional, giving 2 x 2 = 4 sets.
In ENROL(StudentID, CourseID, Grade), the primary key is
- StudentID, taken alone
- Grade, taken alone
- CourseID, taken alone
- StudentID with CourseID
Answer and explanation
Answer: D
Only the combination is unique for each row, so a composite key is needed.
A candidate key not chosen as primary key is called
- Foreign key
- Alternate key
- Composite key
- Super table
Answer and explanation
Answer: B
Candidate keys that are not made primary are alternate keys.
A table has 12 rows and 5 columns. Its cardinality is
- 5
- 17
- 12
- 60
Answer and explanation
Answer: C
Cardinality means the number of rows, which is 12. The degree is 5.
How many rows does SELECT Cat, COUNT(*) FROM SALES GROUP BY Cat return?
- 6
- 3
- 1
- 2
Answer and explanation
Answer: D
There are two categories, so there are two output rows.
The AVG(Rate) for Accessories is
- 110
- 220
- 150
- 450
Answer and explanation
Answer: B
(450 + 120 + 90) / 3 = 660 / 3 = 220.
Which clause can use the condition SUM(Qty) > 60?
- WHERE
- ORDER BY only
- FROM
- HAVING
Answer and explanation
Answer: D
A condition on an aggregate belongs in HAVING, which filters groups.
What is the value of SUM(Qty * Rate) for Accessories?
- 2250
- 3600
- 4410
- 1440
Answer and explanation
Answer: C
2250 + 1440 + 720 = 4410.
In the SALES table, what is SUM(Qty) for the Stationery group?
- 120
- 90
- 100
- 80
Answer and explanation
Answer: A
50 + 30 + 40 = 120.
What does SELECT EName, Salary + 2000 FROM EMP WHERE Job = 'Manager' do to the stored salaries?
- Raises them by 2000 in the table
- Deletes the managers' rows
- Only displays higher values
- Adds a new column for good
Answer and explanation
Answer: C
SELECT only displays results. To change stored values you would need UPDATE.
SELECT DISTINCT DeptNo FROM EMP returns
- 8 rows
- 3 rows
- 2 rows
- 1 row
Answer and explanation
Answer: B
The department numbers are 10, 20 and 30, so three different values appear.
Which employee has the second-highest salary in EMP?
- Bela
- Fatima
- Dolly
- Gopal
Answer and explanation
Answer: A
Fatima earns 52000 and Bela 45000, which is second. Dolly earns 38000.
SELECT EName FROM EMP WHERE EName LIKE '%a'; returns how many rows?
- 2
- 4
- 5
- 3
Answer and explanation
Answer: D
Bela, Fatima and Hema end with a. Dolly, Gopal and the rest do not.
How many employees in the table have Job = 'Clerk'?
- 3
- 5
- 2
- 4
Answer and explanation
Answer: D
The clerks are Aman, Chirag, Eshan and Hema, so there are four.