Download PrepIQ Certified SQL Language Professional Ultimate Exam and more Exams Technology in PDF only on Docsity!
Professional Ultimate Exam
Question 1. Which SQL clause is used to restrict the rows returned by a SELECT statement? A) GROUP BY B) HAVING C) WHERE D) ORDER BY Answer: C Explanation: The WHERE clause filters rows before any grouping or ordering occurs. Question 2. In a relational database, which of the following best describes a primary key? A) A column that can contain duplicate values B) A column that uniquely identifies each row and cannot be NULL C) A column used only for indexing purposes D) A foreign key that references another table Answer: B Explanation: A primary key must be unique for every row and cannot contain NULL values. Question 3. What does the ANSI SQL keyword DISTINCT do? A) Removes duplicate rows from the result set B) Sorts the result set in ascending order C) Limits the number of rows returned D) Groups rows based on a column value Answer: A Explanation: DISTINCT eliminates duplicate rows, returning only unique combinations of selected columns. Question 4. Which of the following is a correct way to create an index on the column “email” of the table “users”? A) CREATE INDEX idx_email ON users(email); B) ALTER TABLE users ADD INDEX idx_email(email);
Professional Ultimate Exam
C) Both A and B are correct D) Neither A nor B are correct Answer: C Explanation: Both CREATE INDEX and ALTER TABLE … ADD INDEX are valid syntaxes for creating a non-unique index. Question 5. Which isolation level allows a transaction to read rows that have been modified but not yet committed by other transactions? A) READ COMMITTED B) REPEATABLE READ C) READ UNCOMMITTED D) SERIALIZABLE Answer: C Explanation: READ UNCOMMITTED permits dirty reads, meaning uncommitted changes can be seen. Question 6. In SQL Server, which system view provides information about all database objects? A) sys.objects B) INFORMATION_SCHEMA.TABLES C) pg_catalog.pg_class D) DBA_TABLES Answer: A Explanation: sys.objects lists all schema-scoped objects (tables, views, procedures, etc.) in the current database. Question 7. Which of the following statements about a LEFT JOIN is true? A) It returns only rows that have matching values in both tables. B) It returns all rows from the right table and matching rows from the left table. C) It returns all rows from the left table and matching rows from the right table. D) It eliminates duplicate rows automatically. Answer: C
Professional Ultimate Exam
A) TEXT
B) JSON
C) JSONB
D) VARCHAR
Answer: C Explanation: JSONB stores JSON in a binary format, supporting indexing and efficient query operations. Question 12. Which clause is mandatory when using an aggregate function without a GROUP BY clause? A) HAVING B) ORDER BY C) SELECT DISTINCT D) None – the query is valid as written Answer: D Explanation: An aggregate function can be used without GROUP BY; the result is a single row summarizing the entire table. Question 13. What does the SQL keyword “OVER” enable when used with an aggregate function? A) It creates a subquery B) It defines a window or analytic function C) It forces the query to run in parallel D) It limits the number of rows returned Answer: B Explanation: OVER defines the window (partition and order) for analytic functions, allowing row-by-row calculations. Question 14. Which of the following statements about foreign keys is FALSE? A) They enforce referential integrity between tables. B) They can reference a column that is not indexed. C) They can be defined on composite columns. D) Deleting a referenced row without handling can cause an error.
Professional Ultimate Exam
Answer: B Explanation: While a foreign key can reference a non-indexed column, most DBMS automatically create an index to enforce referential integrity efficiently; however, the statement that they can reference a column that is not indexed is technically true but considered poor practice. The false statement in the context of standard behavior is B because most systems require the referenced column to be a primary or unique key, which is indexed. Question 15. Which SQL statement is used to permanently remove a table and all its data? A) DELETE TABLE employees; B) DROP TABLE employees; C) TRUNCATE TABLE employees; D) REMOVE TABLE employees; Answer: B Explanation: DROP TABLE deletes the table definition and all data; it cannot be rolled back in most systems. *Question 16. In Oracle, which hint forces the optimizer to use a full table scan? ** A) /+ INDEX(table alias) / B) /+ FULL(table alias) / C) /+ NO_INDEX(table alias) / D) /+ LEADING(table alias) */ Answer: B Explanation: The FULL hint tells Oracle to perform a full table scan on the specified table. Question 17. Which of the following is NOT a valid set operator in standard SQL? A) UNION B) INTERSECT C) EXCEPT D) DIFFERENCE Answer: D
Professional Ultimate Exam
A) MyISAM B) MEMORY C) InnoDB D) CSV Answer: C Explanation: InnoDB implements the InnoDB foreign key constraint mechanism; MyISAM does not. Question 22. Which of the following statements about the “MERGE” statement in SQL Server is TRUE? A) It can only insert rows, not update them. B) It requires a target table and a source table. C) It is equivalent to a LEFT JOIN. D) It automatically creates indexes on the target table. Answer: B Explanation: MERGE combines INSERT, UPDATE, and DELETE actions based on matching rows between a target and source. Question 23. What is the purpose of the “EXPLAIN” keyword in PostgreSQL? A) To display the execution plan for a query B) To encrypt the result set C) To export query results to a file D) To create a view based on a SELECT statement Answer: A Explanation: EXPLAIN shows the planner’s estimated execution plan, helping with performance tuning. Question 24. Which of the following is a correct way to add a NOT NULL constraint to an existing column in Oracle? A) ALTER TABLE customers MODIFY (email NOT NULL); B) ALTER TABLE customers ADD CONSTRAINT nn_email NOT NULL (email); C) ALTER TABLE customers ALTER COLUMN email SET NOT NULL; D) ALTER TABLE customers CHANGE email email NOT NULL;
Professional Ultimate Exam
Answer: A Explanation: Oracle uses MODIFY to change column definitions, including adding NOT NULL. Question 25. In SQL Server, which function returns the number of rows in a table without scanning the table? A) COUNT(*) B) ROW_COUNT() C) sys.dm_db_partition_stats D) OBJECTPROPERTY() Answer: C Explanation: The dynamic management view sys.dm_db_partition_stats provides row counts from metadata, avoiding a full scan. Question 26. Which clause can be used to limit the number of rows returned in a SQL Server query? A) LIMIT B) FETCH FIRST C) TOP D) SAMPLE Answer: C Explanation: TOP n limits the result set in SQL Server; LIMIT is used in MySQL/PostgreSQL. Question 27. In a relational schema, what does the term “normal form” refer to? A) The physical storage layout of a table B) A set of rules that reduce redundancy and dependency anomalies C) The default collation of a database D) The maximum number of columns a table can have Answer: B Explanation: Normal forms (1NF, 2NF, 3NF, BCNF, etc.) are design principles to minimize redundancy and update anomalies.
Professional Ultimate Exam
C) Normalized dimension tables with many levels of hierarchy D) No foreign key constraints between tables Answer: B Explanation: A star schema has a denormalized design with a single fact table at the center and dimension tables radiating outward. Question 32. Which of the following statements about the SQL command “TRUNCATE TABLE” is FALSE? A) It removes all rows from a table. B) It can be rolled back if used inside a transaction in PostgreSQL. C) It fires DELETE triggers for each row removed. D) It resets identity/sequence values in most DBMS. Answer: C Explanation: TRUNCATE does not fire row-level DELETE triggers because it deallocates data pages directly. Question 33. Which ANSI SQL data type is best suited for storing large binary objects such as images? A) VARCHAR B) BLOB C) TEXT D) CLOB Answer: B Explanation: BLOB (Binary Large Object) stores binary data like images, audio, or video. Question 34. In MySQL, which keyword is used to create a temporary table that exists only for the duration of the session? A) TEMPORARY B) SESSION C) GLOBAL TEMPORARY D) VOLATILE Answer: A
Professional Ultimate Exam
Explanation: CREATE TEMPORARY TABLE creates a table that is automatically dropped when the session ends. Question 35. Which of the following is true about the “CHECK” constraint? A) It can reference columns from other tables. B) It ensures that a column value is unique. C) It validates data based on a Boolean expression. D) It automatically creates an index on the constrained column. Answer: C Explanation: CHECK enforces that each row satisfies a Boolean condition; it cannot reference other tables in most DBMS. Question 36. Which function would you use to concatenate strings in SQL Server? A) CONCATENATE() B) CONCAT() C) || (double pipe) D) STRING_JOIN() Answer: B Explanation: CONCAT() is the built-in function for string concatenation; the double pipe is used in Oracle/PostgreSQL. Question 37. In PostgreSQL, which clause allows you to return rows from the INSERT statement? A) RETURNING * B) OUTPUT * C) SELECT * FROM INSERTED D) GET RESULT * Answer: A Explanation: The RETURNING clause returns specified columns (or *) from the inserted rows. Question 38. Which isolation level provides the highest level of data consistency but the lowest level of concurrency?
Professional Ultimate Exam
D) Both B and C are true. Answer: D Explanation: Indexed views must be created WITH SCHEMABINDING and can speed up queries that reference the view, especially for aggregations. Question 42. What is the effect of the “ON DELETE CASCADE” clause in a foreign key definition? A) It prevents deletion of parent rows if child rows exist. B) It automatically deletes child rows when the parent row is deleted. C) It sets child foreign key values to NULL when the parent is deleted. D) It logs deletion events to a audit table. Answer: B Explanation: CASCADE propagates the delete operation from the parent to all referencing child rows. **Question 43. Which of the following is NOT a valid window frame specification? ** A) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW B) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING C) GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW D) ROWS BETWEEN 3 FOLLOWING AND UNBOUNDED FOLLOWING Answer: C Explanation: “GROUPS” is not a recognized frame type; valid types are ROWS and RANGE. Question 44. In MySQL, which storage engine supports full-text search indexes? A) MyISAM B) InnoDB (as of 5.6 and later) C) MEMORY D) Both A and B Answer: D Explanation: Both MyISAM and InnoDB (starting with MySQL 5.6) support FULLTEXT indexes.
Professional Ultimate Exam
Question 45. Which of the following is the correct syntax to rename a column in a PostgreSQL table? A) ALTER TABLE employees RENAME COLUMN salary TO salary_amount; B) ALTER TABLE employees CHANGE salary salary_amount NUMERIC; C) MODIFY COLUMN employees.salary TO salary_amount; D) UPDATE employees SET column_name = 'salary_amount' WHERE column_name = 'salary'; Answer: A Explanation: PostgreSQL uses ALTER TABLE … RENAME COLUMN … TO … for column renaming. Question 46. Which of the following statements about “soft deletes” is accurate? A) They physically remove rows from the table. B) They set a flag column (e.g., IsDeleted) to indicate logical deletion. C) They are implemented using the TRUNCATE command. D) They automatically purge data after 30 days. Answer: B Explanation: Soft delete marks rows as deleted via a status column, preserving the data for audit or recovery. Question 47. Which built-in function in Oracle returns the current system date and time? A) SYSDATE B) CURRENT_TIMESTAMP C) GETDATE() D) NOW() Answer: A Explanation: SYSDATE returns the server’s date and time; CURRENT_TIMESTAMP includes time-zone information. Question 48. In SQL Server, which clause is used to define a default value for a column?
Professional Ultimate Exam
D) pgagent Answer: B Explanation: PostGIS adds geometry and geography types and functions for spatial analysis. Question 52. Which of the following is a correct way to add a computed column that returns the total price (quantity * unit_price) in a SQL Server table? A) ALTER TABLE orders ADD total_price AS (quantity * unit_price); B) ALTER TABLE orders ADD total_price GENERATED ALWAYS AS (quantity * unit_price); C) ALTER TABLE orders ADD total_price PERSISTED (quantity * unit_price); D) Both A and B are valid syntax. Answer: A Explanation: In SQL Server, you define a computed column using AS (expression). Option B resembles MySQL syntax. Question 53. Which statement correctly describes the difference between DELETE and TRUNCATE in SQL Server? A) DELETE can be rolled back; TRUNCATE cannot. B) DELETE fires DELETE triggers; TRUNCATE does not. C) DELETE removes rows one by one; TRUNCATE deallocates pages. D) Both B and C are true. Answer: D Explanation: DELETE logs individual row deletions and fires triggers, while TRUNCATE deallocates whole data pages and bypasses row-level triggers. Question 54. Which of the following is a valid way to create a recursive Common Table Expression (CTE) in PostgreSQL? A) WITH RECURSIVE cte AS (SELECT 1 UNION ALL SELECT col+1 FROM cte WHERE col < 10) SELECT * FROM cte; B) WITH cte AS (SELECT 1 UNION ALL SELECT col+1 FROM cte WHERE col < 10) SELECT * FROM cte; C) CREATE RECURSIVE VIEW cte AS SELECT generate_series(1,10); D) RECURSIVE WITH cte AS SELECT * FROM mytable; Answer: A
Professional Ultimate Exam
Explanation: WITH RECURSIVE defines a recursive CTE; the SELECT inside references the CTE name. Question 55. In Oracle, which function returns the number of days between two DATE values? A) DATEDIFF(date1, date2) B) DATE_DIFF(date1, date2) C) date1 - date D) TIMESTAMPDIFF(DAY, date1, date2) Answer: C Explanation: Subtracting two DATE values yields the difference in days (including fractional parts). Question 56. Which of the following statements about the “ROW_NUMBER()” window function is FALSE? A) It always generates sequential numbers starting at 1 for each partition. B) It can be used without an ORDER BY clause inside the OVER clause. C) It is non-deterministic if the ORDER BY is omitted. D) It cannot be combined with PARTITION BY. Answer: D Explanation: ROW_NUMBER() can be combined with PARTITION BY to reset numbering for each partition. Question 57. Which SQL clause is used to combine rows from two tables based on a related column, returning all rows from both tables and filling with NULLs where there is no match? A) INNER JOIN B) LEFT OUTER JOIN C) RIGHT OUTER JOIN D) FULL OUTER JOIN Answer: D Explanation: FULL OUTER JOIN returns the union of left and right rows, inserting NULLs for non-matching sides.
Professional Ultimate Exam
B) CONSTRAINT email_regex REGEXP(email, '...') C) VALIDATE email USING REGEXP('...') D) ALTER TABLE users ADD REGEX(email, '...'); Answer: A Explanation: PostgreSQL uses the ~ (case-sensitive) or ~* (case-insensitive) operator for regex matching within a CHECK constraint. Question 62. In SQL Server, which dynamic management view returns information about currently executing requests? A) sys.dm_exec_sessions B) sys.dm_exec_requests C) sys.dm_os_performance_counters D) sys.dm_io_virtual_file_stats Answer: B Explanation: sys.dm_exec_requests provides details on each request that is currently executing. Question 63. Which of the following statements about “ON UPDATE SET NULL” in a foreign key definition is correct? A) It prevents updates on the parent key. B) It sets the child foreign key to NULL when the parent key is updated. C) It cascades the update to the child rows. D) It throws an error if the parent key is changed. Answer: B Explanation: ON UPDATE SET NULL replaces the referencing child column with NULL when the referenced parent value changes. Question 64. Which function in Oracle converts a string to a number, raising an error if conversion fails? A) TO_NUMBER() B) CAST(string AS NUMBER) C) CONVERT(string, NUMBER) D) NUMERIC(string)
Professional Ultimate Exam
Answer: A Explanation: TO_NUMBER parses a string according to the NLS numeric format and raises ORA-01722 if conversion fails. Question 65. In PostgreSQL, which clause allows you to lock selected rows for update? A) FOR UPDATE B) LOCK ROWS C) WITH (ROWLOCK) D) SELECT ... HOLD LOCK Answer: A Explanation: SELECT … FOR UPDATE acquires exclusive row locks on the returned rows. Question 66. Which of the following statements about “indexed columns” in MySQL is FALSE? A) A PRIMARY KEY automatically creates a unique index. B) A UNIQUE index allows multiple NULL values. C) An index can be created on an expression using generated columns. D) Indexes always improve query performance. Answer: D Explanation: Indexes can degrade performance for writes and may not help if the query does not use the indexed columns. Question 67. Which of the following is the correct syntax to create a trigger that fires AFTER INSERT on a table “orders” in PostgreSQL? A) CREATE TRIGGER trg_orders AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION fn_log(); B) CREATE TRIGGER trg_orders ON orders AFTER INSERT EXECUTE fn_log(); C) CREATE AFTER INSERT TRIGGER trg_orders ON orders CALL fn_log(); D) CREATE TRIGGER trg_orders AFTER INSERT ON orders EXECUTE PROCEDURE fn_log(); Answer: A Explanation: PostgreSQL syntax uses CREATE TRIGGER … AFTER INSERT ON … FOR EACH ROW EXECUTE FUNCTION.
