Download Certified SQL Language Professional Exam and more Exams Technology in PDF only on Docsity!
Certified SQL Language Professional Practice Exam
Q1: What does SQL stand for? A) Structured Query Language B) Simple Query Language C) Sequential Query Language D) Standard Query Language Answer: A Explanation: SQL stands for Structured Query Language, which is used to manage and manipulate relational databases. Q2: Which SQL command is used to retrieve data from a database? A) INSERT B) SELECT C) UPDATE D) DELETE Answer: B Explanation: The SELECT statement is used to retrieve data from one or more tables in a database. Q3: What type of SQL command is used to create a new table? A) DML B) DDL C) DCL D) TCL Answer: B Explanation: DDL (Data Definition Language) commands such as CREATE TABLE are used to define or modify database structures. Q4: Which of the following is a DML command? A) DROP B) ALTER C) INSERT D) GRANT Answer: C Explanation: INSERT is a Data Manipulation Language command used to add records to a table. Q5: What does the acronym DCL stand for in SQL? A) Data Control Language B) Data Creation Language C) Data Compilation Language D) Data Command Language Answer: A Explanation: DCL stands for Data Control Language, which includes commands like GRANT and REVOKE for permissions. Q6: Which SQL command is used to remove an entire table from a database? A) DROP
B) DELETE
C) TRUNCATE
D) REMOVE
Answer: A Explanation: The DROP TABLE command completely removes a table and its structure from the database. Q7: In SQL, what is a primary key? A) A column that uniquely identifies each row B) A column that can accept duplicate values C) A temporary column used for query optimization D) A column used solely for sorting data Answer: A Explanation: A primary key is a unique identifier for table records and does not allow duplicate values. Q8: Which SQL command is used to modify an existing table structure? A) UPDATE B) ALTER C) MODIFY D) CHANGE Answer: B Explanation: The ALTER TABLE command is used to modify the structure of an existing table, such as adding or removing columns. Q9: What does the NOT NULL constraint do in a SQL table? A) Allows null values in a column B) Prevents null values in a column C) Creates a primary key automatically D) Deletes null values from a column Answer: B Explanation: The NOT NULL constraint ensures that a column cannot have a NULL value, enforcing data integrity. Q10: Which SQL command is used to remove rows from a table without removing the table structure? A) DROP B) DELETE C) TRUNCATE D) REMOVE Answer: B Explanation: The DELETE command is used to remove rows from a table while leaving the table structure intact. Q11: What is the purpose of the UNIQUE constraint in SQL? A) To ensure all values in a column are the same B) To allow only one duplicate value per column C) To ensure all values in a column are distinct D) To set a column as the primary key
Q17: Which of the following is a characteristic of SQL syntax? A) Case sensitivity for all commands B) Strict punctuation rules C) Keywords that are generally not case-sensitive D) Mandatory use of semicolons in all SQL dialects Answer: C Explanation: SQL keywords are generally not case-sensitive, though some systems may differ. Q18: What does the TRUNCATE command do? A) Deletes table structure and data B) Deletes all rows from a table quickly, preserving the table structure C) Deletes only duplicate rows D) Updates all rows to NULL Answer: B Explanation: TRUNCATE removes all rows from a table efficiently while preserving the table’s structure. Q19: In SQL database architecture, which component is responsible for processing queries? A) Storage engine B) Query processor C) Network interface D) Data loader Answer: B Explanation: The query processor is responsible for parsing, optimizing, and executing SQL queries. Q20: Which SQL command is used to change the database schema by renaming a table? A) UPDATE B) ALTER C) RENAME D) MODIFY Answer: B Explanation: The ALTER TABLE command can be used along with RENAME to change the name of a table in some SQL dialects. Q21: What is the purpose of the CHECK constraint? A) To ensure values in a column meet a specific condition B) To automatically check for duplicate rows C) To enforce primary key rules D) To back up table data Answer: A Explanation: The CHECK constraint is used to limit the values that can be placed in a column by specifying a Boolean expression. Q22: Which of the following is a valid SQL data manipulation operation? A) GRANT B) INSERT C) CREATE D) DROP
Answer: B Explanation: INSERT is a DML command used to add new records to a table. Q23: What is the role of SQL execution phases in query processing? A) They determine the order in which data is displayed B) They outline the steps SQL follows to parse, optimize, and execute queries C) They help in encrypting the query data D) They decide the physical storage of data Answer: B Explanation: SQL execution phases describe how a query is parsed, optimized, and executed, ensuring efficient data retrieval. Q24: Which of the following best describes SQL database architecture? A) A single-layer structure with no separation B) A multi-layered architecture including the relational engine and storage engine C) A flat file system D) A document-oriented database Answer: B Explanation: SQL database architecture typically consists of multiple layers such as the relational engine, which handles query processing, and the storage engine, which manages data storage. Q25: Why are SQL syntax and conventions important? A) They ensure that queries can be read and maintained easily B) They are only important for advanced users C) They are optional and can be ignored D) They increase the complexity of queries Answer: A Explanation: Consistent SQL syntax and conventions improve readability and maintainability of code, reducing errors and improving collaboration. Q26: Which clause is used to filter results in a SELECT statement? A) GROUP BY B) WHERE C) ORDER BY D) HAVING Answer: B Explanation: The WHERE clause filters records in a SELECT statement by specifying a condition that each record must satisfy. Q27: What is the difference between SELECT * and SELECT column_name? A) SELECT * retrieves only unique rows; SELECT column_name retrieves all rows B) SELECT * retrieves all columns; SELECT column_name retrieves only specified columns C) SELECT * is faster than SELECT column_name in every case D) There is no difference Answer: B Explanation: SELECT * returns all columns from a table, while specifying column names retrieves only those columns.
Explanation: The correct syntax to filter for NULL values is to use “IS NULL” because NULL is not comparable with = or !=. Q34: Which wildcard character is used with the LIKE operator to match any sequence of characters? A) % B) _ C) * D)? Answer: A Explanation: The percent sign (%) wildcard matches any sequence of zero or more characters in SQL. Q35: What does the BETWEEN operator do in SQL? A) It checks if a value is exactly equal to two values B) It checks if a value lies within a specified range C) It combines two queries D) It filters only non-null values Answer: B Explanation: BETWEEN is used to filter the result set within a specified range of values. Q36: Which join returns only the rows that have matching values in both tables? A) LEFT JOIN B) RIGHT JOIN C) FULL OUTER JOIN D) INNER JOIN Answer: D Explanation: INNER JOIN returns only those rows where there is a match in both joined tables. Q37: What is the purpose of a LEFT JOIN? A) To return only matching rows from two tables B) To return all rows from the left table and matching rows from the right table C) To return all rows from the right table only D) To return non-matching rows only Answer: B Explanation: A LEFT JOIN returns all rows from the left table and the matched rows from the right table; if there is no match, NULLs are returned for the right table’s columns. Q38: How does a RIGHT JOIN differ from a LEFT JOIN? A) It returns all rows from the right table and matching rows from the left table B) It always returns only matching rows C) It returns only non-matching rows D) It performs a union of two tables Answer: A Explanation: A RIGHT JOIN returns all rows from the right table along with the matching rows from the left table. Q39: What does a FULL OUTER JOIN do? A) Returns only the rows that match in both tables
B) Returns all rows when there is a match in one of the tables C) Returns all rows from both tables, with NULLs for non-matching rows D) Returns rows from one table only Answer: C Explanation: A FULL OUTER JOIN returns all rows from both tables and fills in NULLs where there is no match. Q40: When performing a self-join, what is the main purpose? A) To join two different databases B) To join a table to itself for comparing rows C) To create duplicate rows D) To combine two unrelated tables Answer: B Explanation: A self-join is used to join a table to itself to compare rows within the same table. Q41: What is a CROSS JOIN? A) A join that returns rows only when keys match B) A join that returns the Cartesian product of two tables C) A join that excludes duplicate rows D) A join that works only with indexed columns Answer: B Explanation: A CROSS JOIN returns the Cartesian product, meaning every row from the first table is combined with every row from the second table. Q42: Which SQL clause is used to combine the results of two queries? A) UNION B) JOIN C) INTERSECT D) MERGE Answer: A Explanation: UNION is used to combine the results of two or more SELECT statements into a single result set. Q43: What is a subquery in SQL? A) A query within another query B) A query that modifies the database structure C) A query that cannot be nested D) A query used only with joins Answer: A Explanation: A subquery is a query embedded within another SQL query, allowing for more dynamic data retrieval. Q44: How does a correlated subquery differ from a non-correlated subquery? A) It runs independently of the outer query B) It refers to a column from the outer query C) It cannot be used with SELECT statements D) It returns only one value
B) It specifies the output format of the query C) It limits the number of rows returned D) It sorts the result set globally Answer: A Explanation: The OVER() clause defines the window or partition over which the window function operates. Q51: What is the purpose of aggregate functions in SQL? A) To perform calculations on multiple rows B) To modify individual row data C) To join tables D) To create indexes Answer: A Explanation: Aggregate functions perform calculations on a set of rows, returning a single summarizing value. Q52: Which function would you use to retrieve the minimum value from a column? A) MIN() B) MAX() C) AVG() D) SUM() Answer: A Explanation: MIN() returns the smallest value in a column. Q53: How does the GROUP BY clause work? A) It filters rows based on conditions B) It aggregates rows that share a common value in specified columns C) It sorts the results D) It limits the number of returned rows Answer: B Explanation: GROUP BY aggregates rows that have the same value(s) in one or more columns. Q54: What does the HAVING clause allow you to do? A) Filter rows before aggregation B) Filter aggregated groups C) Sort query results D) Join multiple tables Answer: B Explanation: HAVING is used to filter groups after the GROUP BY operation has been applied. Q55: Which aggregate function is used to determine the highest value in a column? A) AVG() B) MIN() C) SUM() D) MAX() Answer: D Explanation: MAX() returns the highest value found in a specified column.
Q56: What is a window function in SQL used for? A) To perform calculations across a set of table rows that are related to the current row B) To modify table structures dynamically C) To create new tables from existing data D) To encrypt data during transmission Answer: A Explanation: Window functions perform calculations across a set of table rows related to the current row without collapsing the result set. Q57: Which window function returns the rank of each row within a partition? A) ROW_NUMBER() B) RANK() C) LEAD() D) LAG() Answer: B Explanation: RANK() assigns a rank to each row within a partition, with the same rank for ties. Q58: How do LEAD() and LAG() functions assist in data analysis? A) They calculate sums and averages B) They provide access to subsequent and preceding row values C) They join multiple tables D) They format date values Answer: B Explanation: LEAD() returns the value of a column in a subsequent row, while LAG() returns the value from a preceding row, aiding in comparative analysis. Q59: What is partitioning in the context of window functions? A) Dividing a table into physical storage segments B) Separating a result set into groups for independent calculations C) Filtering rows based on conditions D) Creating temporary tables Answer: B Explanation: Partitioning divides the result set into groups over which the window function is applied independently. Q60: What does the analytic function in SQL typically provide? A) Data encryption B) Detailed insight into data trends without aggregating the result set C) Indexing improvements D) Table creation functionality Answer: B Explanation: Analytic functions perform calculations over a set of rows and provide insights while keeping the original row-level detail intact. Q61: Which statement inserts multiple records into a table in one command? A) INSERT SINGLE B) INSERT MULTI
C) Because performance is not a concern with UPDATE statements D) Because UPDATE statements require table backups Answer: A Explanation: Updating a large number of rows can lock the table and degrade performance, so it’s important to optimize the query and possibly batch updates. Q67: What is the primary purpose of the DELETE command? A) To permanently remove a table B) To remove specific records from a table C) To update record values D) To change table structure Answer: B Explanation: DELETE is used to remove specific records from a table based on the provided conditions. Q68: How does truncating a table differ from deleting rows? A) Truncating a table does not reset identity columns B) Truncating is a logged operation while DELETE is not C) Truncating quickly removes all rows without logging each row deletion and resets identity columns D) There is no difference Answer: C Explanation: TRUNCATE is a faster operation that removes all rows from a table, typically resetting identity columns, and does not log individual row deletions. Q69: Which SQL command is used to create a new database? A) CREATE DATABASE B) NEW DATABASE C) BUILD DATABASE D) INIT DATABASE Answer: A Explanation: CREATE DATABASE is the command used to create a new database in SQL. Q70: What is a key consideration when altering a table structure? A) Data type compatibility and potential data loss B) The size of the disk C) The number of users D) The color scheme of the table Answer: A Explanation: When altering a table, you must ensure that changes to data types or structure do not result in data loss or compatibility issues. Q71: What does normalization in database design aim to achieve? A) Increase data redundancy B) Organize data to reduce redundancy and dependency C) Create duplicate tables for backup D) Eliminate the use of foreign keys Answer: B
Explanation: Normalization organizes data to reduce redundancy and dependency, which improves data integrity. Q72: Which normal form requires that every non-key attribute is fully functionally dependent on the primary key? A) First Normal Form (1NF) B) Second Normal Form (2NF) C) Third Normal Form (3NF) D) Boyce-Codd Normal Form (BCNF) Answer: B Explanation: Second Normal Form (2NF) requires that all non-key attributes are fully functionally dependent on the entire primary key. Q73: What is the purpose of the CREATE TABLE statement? A) To retrieve data B) To define a new table structure in the database C) To delete a table D) To update existing rows Answer: B Explanation: CREATE TABLE is used to define a new table and its columns within a database. Q74: When altering a table, which command is used to add a new column? A) ALTER TABLE ... ADD COLUMN B) UPDATE TABLE ... ADD C) MODIFY TABLE ... INSERT D) CHANGE TABLE ... NEW COLUMN Answer: A Explanation: ALTER TABLE with the ADD COLUMN clause is used to add a new column to an existing table. Q75: What is the difference between normalization and denormalization? A) Normalization increases redundancy; denormalization reduces it B) Normalization reduces redundancy; denormalization intentionally introduces redundancy for performance gains C) They are identical processes D) Denormalization is used only in NoSQL databases Answer: B Explanation: Normalization reduces data redundancy for integrity, while denormalization may introduce redundancy to improve read performance. Q76: Which SQL command is used to add new records to a table? A) INSERT B) UPDATE C) SELECT D) DELETE Answer: A Explanation: The INSERT command is specifically designed to add new records into a table.
C) ALLOW
D) PERMIT
Answer: B Explanation: The GRANT command is used to assign specific permissions to users or roles in SQL. Q83: What is a schema in SQL? A) A type of database index B) A logical grouping of database objects such as tables and views C) A command to update data D) A user-defined function Answer: B Explanation: A schema is a collection or namespace that groups related database objects, helping to organize and secure data. Q84: What is the main benefit of using foreign keys in a database? A) To speed up queries B) To ensure referential integrity between tables C) To automatically back up data D) To encrypt sensitive information Answer: B Explanation: Foreign keys enforce referential integrity by ensuring that a value in one table corresponds to a valid value in another table. Q85: When might you denormalize a database? A) When data integrity is not important B) To improve query performance in read-heavy environments C) To reduce the number of tables D) To eliminate the need for indexes Answer: B Explanation: Denormalization is often used in read-heavy environments to reduce the need for complex joins, thus improving query performance. Q86: What is the primary purpose of the ALTER DATABASE command? A) To modify the structure of a table B) To change database configuration or settings C) To delete a database D) To retrieve data from a database Answer: B Explanation: ALTER DATABASE is used to change database-level settings and configurations. Q87: How can you create a backup of a table structure without data? A) Using the TRUNCATE command B) Using the SELECT INTO command C) Using the CREATE TABLE ... AS command with a false WHERE clause D) By scripting the table definition Answer: D
Explanation: Scripting the table definition (or using tools to generate the CREATE TABLE statement) allows you to back up the structure without the data. Q88: Which command is used to drop a table from the database? A) DELETE B) REMOVE C) DROP D) ERASE Answer: C Explanation: The DROP TABLE command removes a table and its data from the database permanently. Q89: Why is normalization important in database design? A) It increases data redundancy B) It improves data integrity and reduces redundancy C) It complicates database queries D) It is only useful in small databases Answer: B Explanation: Normalization organizes data to reduce redundancy and improve data integrity, which is essential for efficient database design. Q90: What is denormalization? A) The process of reducing data redundancy B) The process of intentionally introducing redundancy for performance improvements C) A method for indexing data D) A type of database backup Answer: B Explanation: Denormalization intentionally introduces redundancy in a controlled manner to optimize query performance. Q91: Which of the following is a benefit of using indexes in SQL? A) They reduce disk space usage B) They speed up data retrieval operations C) They prevent data loss D) They ensure data integrity Answer: B Explanation: Indexes help speed up the retrieval of data by providing quick access paths to the data. Q92: What type of index enforces uniqueness in a column? A) Composite index B) Full-text index C) Unique index D) Spatial index Answer: C Explanation: A unique index ensures that all the values in the indexed column are distinct, thus preventing duplicate entries.
B) By ensuring proper indexing on the join columns C) By increasing the size of the database D) By avoiding the use of WHERE clauses Answer: B Explanation: Proper indexing on join columns can significantly improve the performance of complex join operations. Q99: Which isolation level allows dirty reads? A) Serializable B) Repeatable Read C) Read Committed D) Read Uncommitted Answer: D Explanation: Read Uncommitted isolation level allows transactions to read data that has been modified by other transactions but not yet committed, leading to dirty reads. Q100: What is the main purpose of a transaction in SQL? A) To permanently store data B) To group a set of operations that must all succeed or all fail C) To create new tables D) To optimize queries Answer: B Explanation: Transactions ensure that a set of operations are executed as a single unit; if any operation fails, the entire transaction is rolled back to maintain data integrity. Q101: What is a view in SQL? A) A physical copy of a table B) A virtual table that is defined by a SQL query C) A command to modify data D) A stored procedure Answer: B Explanation: A view is a virtual table representing the result of a stored query, which can simplify complex queries and improve security by restricting data access. Q102: Which SQL command is used to create a view? A) CREATE VIEW B) MAKE VIEW C) NEW VIEW D) VIEW CREATE Answer: A Explanation: The CREATE VIEW command defines a view based on a SELECT query. Q103: What is one limitation of views in SQL? A) They can be indexed B) They can only display data from one table C) They do not store data physically D) They always update the underlying table
Answer: C Explanation: Views do not store data physically; they dynamically display data from the underlying tables. Q104: What is a stored procedure in SQL? A) A saved collection of SQL statements that can be executed as a unit B) A dynamic query generated at runtime C) A temporary table D) A type of index Answer: A Explanation: A stored procedure is a precompiled collection of SQL statements that perform a specific task and can be executed repeatedly. Q105: Which of the following is true about stored procedures? A) They can only be written in SQL B) They allow for input and output parameters C) They cannot include conditional logic D) They are executed automatically on database startup Answer: B Explanation: Stored procedures can accept input and output parameters and often include conditional logic, loops, and error handling. Q106: What is the key difference between functions and stored procedures in SQL? A) Functions cannot return a value B) Stored procedures cannot be used in SELECT statements, while functions can C) Functions are used only for data modification D) There is no difference Answer: B Explanation: Functions can be used in SQL expressions and return a value, whereas stored procedures generally cannot be directly used in SELECT statements. Q107: What is the purpose of a trigger in SQL? A) To manually execute queries B) To automatically execute a predefined action in response to certain events on a table C) To schedule backups D) To optimize query performance Answer: B Explanation: Triggers are special procedures that automatically execute in response to events such as INSERT, UPDATE, or DELETE operations on a table. Q108: What is the difference between a BEFORE trigger and an AFTER trigger? A) BEFORE triggers execute before the triggering event; AFTER triggers execute after the event B) BEFORE triggers execute after the event; AFTER triggers execute before the event C) They are the same D) BEFORE triggers cannot modify data Answer: A
