Download Certified Database Processing Operator Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. Which type of database organizes data in a tree-like structure with parent-child relationships? A) Relational B) NoSQL C) Hierarchical D) Object-oriented Answer: C Explanation: Hierarchical databases structure data as a tree, with each child having only one parent, suitable for representing one-to-many relationships. Question 2. What is a primary key in a relational database? A) A column that uniquely identifies each row B) A column used only for sorting C) A column that stores foreign data D) A column with duplicate values Answer: A Explanation: The primary key uniquely identifies each record in a table, ensuring entity integrity. Question 3. Which database model provides flexibility for storing unstructured data such as JSON documents? A) Relational B) NoSQL C) Hierarchical D) Network Answer: B Explanation: NoSQL databases are designed for flexible storage of unstructured or semi-structured data, often using documents like JSON.
Question 4. What does OLTP stand for in database processing? A) Object-Level Transaction Processing B) Online Transaction Processing C) Open Logical Transaction Protocol D) Offline Transaction Processing Answer: B Explanation: OLTP refers to Online Transaction Processing, which handles real-time transactional data. Question 5. In the three-schema architecture, what does the external schema represent? A) Storage details B) User views and interactions C) Logical data relationships D) Database engine Answer: B Explanation: The external schema defines how individual users view and interact with the database, tailored to their needs. Question 6. Which component maintains a record of all changes made to the database? A) DBMS B) Transaction log C) Database file D) Memory buffer Answer: B Explanation: The transaction log keeps a record of all database changes, essential for recovery and auditing.
Question 10. Which clause in a SELECT statement is used to sort the results? A) WHERE B) ORDER BY C) GROUP BY D) HAVING Answer: B Explanation: ORDER BY sorts the rows returned by a SELECT query according to specified columns. Question 11. Which aggregate function returns the total number of rows in a result set? A) SUM B) COUNT C) MAX D) AVG Answer: B Explanation: COUNT returns the number of rows, useful for summary statistics. Question 12. What does the INNER JOIN clause do in SQL? A) Combines all rows from two tables B) Combines rows with matching values in both tables C) Returns only unmatched rows D) Deletes duplicate rows Answer: B Explanation: INNER JOIN returns rows when there is a match between columns in both tables. Question 13. To add a record to a table, which SQL statement should you use?
A) SELECT
B) UPDATE
C) INSERT
D) DELETE
Answer: C Explanation: INSERT adds a new row to a table in SQL. Question 14. Which SQL statement removes records from a table? A) SELECT B) UPDATE C) DELETE D) DROP Answer: C Explanation: DELETE removes specific rows from a table as defined by the WHERE clause. Question 15. Which SQL statement is used to change existing data in a table? A) SELECT B) UPDATE C) ALTER D) INSERT Answer: B Explanation: UPDATE modifies existing data in a table based on criteria. Question 16. What does the GRANT statement do in SQL? A) Removes user permissions
C) Incremental backup D) Transaction log backup Answer: B Explanation: Differential backups store all changes since the last full backup, reducing backup size. Question 20. What is the role of the database server in client-server architecture? A) Store client applications B) Execute client queries and manage data C) Handle network traffic D) Encrypt user passwords Answer: B Explanation: The database server processes queries, manages data storage, and returns results to clients. Question 21. Which SQL clause groups rows with the same values in specified columns? A) WHERE B) ORDER BY C) GROUP BY D) HAVING Answer: C Explanation: GROUP BY collects rows sharing values in specified columns for aggregate processing. Question 22. Which key uniquely identifies records within a table? A) Foreign key B) Primary key
C) Composite key D) Candidate key Answer: B Explanation: The primary key is unique for each row, ensuring no duplicates. Question 23. To restrict which rows are returned in a SELECT statement, use which clause? A) GROUP BY B) WHERE C) HAVING D) ORDER BY Answer: B Explanation: WHERE filters rows based on specified conditions. Question 24. What is the purpose of a transaction log backup? A) To back up all data files B) To capture only schema changes C) To record all changes for recovery D) To delete old data Answer: C Explanation: Transaction log backups record changes, facilitating point-in-time recovery. Question 25. Which command removes an entire table from the database? A) DELETE B) DROP C) TRUNCATE
Answer: B Explanation: Indexes speed up data retrieval by allowing faster searches. Question 29. Which command changes the structure of an existing table? A) UPDATE B) ALTER C) CREATE D) DROP Answer: B Explanation: ALTER modifies the schema, such as adding or removing columns. Question 30. To combine results from two SELECT queries, which operator is used? A) JOIN B) UNION C) INTERSECT D) EXCEPT Answer: B Explanation: UNION merges results from two SELECT queries, removing duplicates unless UNION ALL is used. Question 31. Which backup method copies all database files regardless of changes? A) Differential backup B) Incremental backup C) Full backup D) Transaction log backup
Answer: C Explanation: Full backup saves all database data, regardless of modifications. Question 32. When should you use the HAVING clause in SQL? A) To filter before grouping B) To sort results C) To filter after grouping D) To join tables Answer: C Explanation: HAVING is used to filter results after GROUP BY aggregation. Question 33. What does the REVOKE statement do in SQL? A) Adds a new user B) Removes user privileges C) Grants permissions D) Modifies table structure Answer: B Explanation: REVOKE removes previously granted privileges from users. Question 34. Which file stores the actual data in a database system? A) Transaction log B) Database file C) Error log D) Backup file Answer: B
Question 38. What is referential integrity in databases? A) Ensuring encrypted data B) Ensuring relationships between tables are valid C) Removing duplicate records D) Storing indexes Answer: B Explanation: Referential integrity maintains valid links between tables using foreign keys. Question 39. Which SQL statement is used to permanently remove all records from a table but keep the structure? A) DROP B) DELETE C) TRUNCATE D) ALTER Answer: C Explanation: TRUNCATE deletes all rows but retains the table schema. Question 40. What is a transaction in databases? A) A backup job B) A single logical unit of work C) An index rebuild D) A database error Answer: B Explanation: A transaction is a sequence of operations performed as a single unit, ensuring atomicity.
Question 41. What does an incremental backup store? A) Only data changed since the last backup of any type B) All database files C) Only user accounts D) Only system logs Answer: A Explanation: Incremental backups save only changes made since the previous backup. Question 42. Which SQL command is used to delete a specific column from a table? A) UPDATE B) ALTER C) DROP D) DELETE Answer: B Explanation: ALTER TABLE can be used to drop a column from the table structure. Question 43. What is the function of the database management system (DBMS)? A) Encrypt user passwords B) Manage storage, retrieval, and modification of data C) Create network connections D) Generate reports Answer: B Explanation: DBMS handles all data manipulation and storage tasks for databases.
A) Store user passwords B) Speed up data retrieval C) Encrypt data D) Backup data Answer: B Explanation: Indexes facilitate fast searching and retrieval of data. Question 48. What does the SET clause do in an UPDATE statement? A) Deletes records B) Specifies columns to be changed C) Creates a new table D) Joins tables Answer: B Explanation: The SET clause defines which columns are updated in a record. Question 49. Which job schedules regular database tasks automatically? A) Manual entry B) Job scheduler C) Transaction log D) Data archiving Answer: B Explanation: A job scheduler automates routine tasks like backups and maintenance. Question 50. What is a database view? A) A physical copy of data
B) A virtual table created by a query C) A backup file D) A primary key Answer: B Explanation: Views are virtual tables based on SELECT queries, not storing data themselves. Question 51. What is the difference between DROP and DELETE? A) DROP removes records, DELETE removes tables B) DROP removes tables, DELETE removes records C) DROP modifies records, DELETE backs up data D) DROP encrypts data, DELETE compresses data Answer: B Explanation: DROP removes tables or objects, DELETE removes specific records. Question 52. Which SQL command is used to create a new user account? A) CREATE USER B) INSERT USER C) GRANT USER D) ALTER USER Answer: A Explanation: CREATE USER adds a new database user account. Question 53. Which aggregate function finds the highest value in a column? A) SUM B) AVG
D) Create indexes Answer: B Explanation: The transaction log records every change, aiding recovery and auditing. Question 57. Which join type returns all records from both tables, matching where possible? A) INNER JOIN B) OUTER JOIN C) CROSS JOIN D) NATURAL JOIN Answer: B Explanation: OUTER JOIN returns all rows from both tables, filling in nulls where there is no match. Question 58. What does the WHERE clause do in SQL? A) Sorts results B) Filters rows based on condition C) Joins tables D) Groups data Answer: B Explanation: WHERE restricts rows returned according to specified criteria. Question 59. What is the role of a DBA in database management? A) Monitor server performance B) Manage access, security, and optimization C) Write application code D) Design user interfaces
Answer: B Explanation: DBAs oversee database operations, security, optimization, and maintenance. Question 60. Which SQL keyword is used to prevent duplicate records in a SELECT query? A) DISTINCT B) UNIQUE C) GROUP BY D) ORDER BY Answer: A Explanation: DISTINCT removes duplicate records from query results. Question 61. What is the purpose of archiving old database data? A) To reduce database size and improve performance B) To remove all records permanently C) To encrypt data D) To rebuild indexes Answer: A Explanation: Archiving moves obsolete data to separate storage, keeping the main database efficient. Question 62. Which clause in SQL is used to filter rows after aggregation? A) WHERE B) HAVING C) ORDER BY D) GROUP BY Answer: B
