Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Programming & Software

Oracle SQL and PL/SQL quiz: test queries, joins, and procedures

Quick SQL quiz online to check your Oracle skills. Instant feedback and score.

Editorial: Review CompletedCreated By: Ionut MicuUpdated Aug 26, 2025
Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
paper art illustration of code snippets and quiz icons on golden yellow background representing Oracle SQL practice quiz

This quiz helps you check your Oracle SQL and PL/SQL skills with real-world questions. Work through joins, subqueries, and procedures, then see instant feedback to spot gaps before an interview or exam. Want more practice? Try database functions practice, a broader database quiz, or an MS Access quiz.

What is the default file extension for SQL Developer SQL worksheets?
.sqlw
.txt
.sql
.sqld
SQL Developer saves worksheets by default with a .sql extension. This format is recognized by the IDE to execute SQL scripts directly. Users can open or save scripts with this extension across different sessions.
Which clause is used to filter rows in a SELECT statement?
HAVING
WHERE
GROUP BY
ORDER BY
The WHERE clause is used to specify conditions that filter rows returned by a SELECT statement. It evaluates each row before grouping, sorting, or aggregation. HAVING filters groups rather than individual rows.
Which function replaces NULL with a specified value?
IFNULL
COALESCE
REPLACE
NVL
NVL(expr1, expr2) returns expr2 when expr1 is NULL; otherwise it returns expr1. COALESCE can handle multiple expressions but NVL is specific to two arguments. REPLACE is for string substitution, not NULL handling. Oracle NVL Function
How do you retrieve unique values from a column?
FILTER
GROUP BY
DISTINCT
UNIQUE
DISTINCT is the SQL keyword to eliminate duplicate rows in the result set. GROUP BY can group rows for aggregation but does not directly filter duplicates in all contexts. UNIQUE is not a SQL clause for retrieving distinct rows.
Which SQL keyword sorts the result set?
SORT BY
ORDER BY
FILTER BY
GROUP BY
ORDER BY arranges the rows of the result set based on specified columns in ascending or descending order. SQL does not support SORT BY or FILTER BY as valid sorting clauses. GROUP BY aggregates rows rather than ordering them.
What pseudocolumn returns a unique number for each row returned by a query?
LEVEL
ROW_NUMBER
ROWID
ROWNUM
ROWNUM is an Oracle pseudocolumn that returns a sequential number to each row in the result set. ROW_NUMBER() is an analytic function, not a pseudocolumn. ROWID identifies the physical storage address of a row.
Which of the following is the default join type in Oracle?
CROSS JOIN
INNER JOIN
OUTER JOIN
FULL JOIN
INNER JOIN returns rows when there is a match in both tables and is the default join type if the JOIN keyword is used without specifying OUTER. OUTER and FULL joins include non-matching rows with NULLs. CROSS JOIN returns a Cartesian product.
How do you start a PL/SQL anonymous block?
PROC
BLOCK
START
BEGIN
A PL/SQL anonymous block begins with the BEGIN keyword and ends with END. DECLARE is optional if you have declarations. START and PROC are not valid PL/SQL block keywords.
What symbol is used for single-line comments in SQL Developer?
//
#
/* */
--
Oracle SQL supports -- for single-line comments. /* */ is used for block comments spanning multiple lines. # and // are not standard comment markers in Oracle SQL.
Which feature in SQL Developer provides a graphical tool for designing database schemas?
Connections
Reports
Query Builder
Data Modeler
Data Modeler in SQL Developer offers a graphical interface to create and modify database designs. Query Builder helps visually construct SELECT statements. Reports and Connections panels serve different purposes.
What panel in SQL Developer displays the output of DBMS_OUTPUT.PUT_LINE?
Messages
Output
DBMS Output
Logs
The DBMS Output panel in SQL Developer must be enabled to view messages from DBMS_OUTPUT.PUT_LINE. Messages and Logs panes show different diagnostic information. The generic Output panel does not capture PL/SQL debug prints.
Which command describes the structure of a table in SQL Developer?
DESCR
SHOW TABLE
DICT
DESC
DESC or DESCRIBE followed by a table name shows its column definitions and data types. SHOW TABLE is not a valid Oracle SQL Developer command. DICT and DESCR are not recognized commands.
Which clause is used to filter groups after aggregation in Oracle SQL?
WHERE
ORDER BY
HAVING
GROUP BY
HAVING is used to apply conditions to aggregated groups after GROUP BY is processed. WHERE filters rows before aggregation, and ORDER BY sorts the final result set.
Which analytic function assigns a unique number to each row within a partition?
DENSE_RANK()
RANK()
ROW_NUMBER()
NTILE()
ROW_NUMBER() returns a sequential integer starting at 1 for each partition based on ORDER BY. RANK() and DENSE_RANK() can produce ties, and NTILE() distributes rows into buckets. Oracle ROW_NUMBER Function
Which keyword begins a Common Table Expression (CTE) in Oracle SQL?
SUBQUERY
DEFINE
CTE
WITH
A CTE is introduced by the WITH clause followed by a query name and definition. It improves readability and can be referenced in the main SELECT. SUBQUERY and DEFINE are not CTE keywords.
Which operator is generally more efficient than IN for checking existence in large subqueries?
EXISTS
ALL
LIKE
ANY
EXISTS stops processing once a matching row is found, making it efficient for large subqueries. IN compares all values, which can be slower in certain scenarios. ANY and ALL serve different purposes, while LIKE is for pattern matching.
Which clause is used for hierarchical queries in Oracle?
START WITH
CONNECT BY
SYS_CONNECT_BY_PATH
LEVEL
CONNECT BY defines parent-child relationships in hierarchical queries. START WITH specifies the root row for the hierarchy. LEVEL is a pseudocolumn indicating depth, while SYS_CONNECT_BY_PATH shows the path.
Which command changes the default schema for the current session in SQL Developer?
ALTER SCHEMA
SET SCHEMA
USE SCHEMA
ALTER SESSION SET CURRENT_SCHEMA
ALTER SESSION SET CURRENT_SCHEMA changes the default schema context for the session without requiring a new login. USE SCHEMA and SET SCHEMA are not valid Oracle commands. ALTER SCHEMA modifies schema-level objects.
Which pane in SQL Developer shows all database objects like tables and views?
Data Viewer
Reports
SQL History
Connections (tree)
The Connections pane displays a hierarchical tree of schemas and their objects such as tables, views, and procedures. Reports shows predefined or custom reports, and SQL History displays past commands. Data Viewer opens query results for editing.
How do you declare a bind variable in a SQL Developer worksheet?
ACCEPT variable_name
VARIABLE variable_name datatype
DEFINE variable_name
LET variable_name
Use VARIABLE name datatype to declare a bind variable in SQL Developer scripts. DEFINE is used by SQL*Plus for substitution variables. LET and ACCEPT are also SQL*Plus commands, not ideal for bind variables.
Which statement inserts rows from another table into a target table?
LOAD DATA
MERGE INTO target_table
INSERT INTO target_table SELECT ...
COPY
The INSERT INTO ... SELECT syntax allows inserting rows selected from another table. MERGE is for upsert operations, LOAD DATA is SQL*Loader related, and COPY is Oracle-specific tool command.
Which database object generates sequential unique numbers in Oracle?
Constraint
Trigger
Synonym
Sequence
A Sequence object produces a series of unique numbers which can be used for primary keys. Triggers can invoke sequences but do not generate numbers by themselves. Constraints enforce rules, and synonyms are aliases.
Which section is mandatory in every PL/SQL anonymous block?
DECLARE
IS
BEGIN ... END
EXCEPTION
Every PL/SQL anonymous block must contain a BEGIN ... END section to execute procedural logic. DECLARE and EXCEPTION sections are optional. IS is used in named blocks and object definitions.
Which exception is raised when a SELECT INTO returns no rows?
VALUE_ERROR
INVALID_CURSOR
NO_DATA_FOUND
TOO_MANY_ROWS
NO_DATA_FOUND is raised when a SELECT INTO statement returns zero rows. TOO_MANY_ROWS is raised when it returns more than one row. VALUE_ERROR occurs on conversion issues, and INVALID_CURSOR on improper cursor usage.
Which clause is used to rotate rows into columns in Oracle SQL?
PIVOT
ROTATE
TRANSFORM
UNPIVOT
PIVOT transforms row data into columns, aggregating values as needed. UNPIVOT reverses this operation. TRANSFORM and ROTATE are not Oracle SQL clauses.
Which view shows refresh information for materialized views in your schema?
USER_MVIEWS
ALL_MVIEWS
DBA_MVIEWS
USER_MVIEW_REFRESH_TIMES
USER_MVIEW_REFRESH_TIMES displays timestamps of materialized view refresh operations for the current user. ALL_MVIEWS and DBA_MVIEWS list definitions, not refresh history. USER_MVIEWS provides basic view info.
Which optimizer hint forces a full table scan on a table alias t?
/*+ FULL(t) */
/*+ NO_INDEX(t) */
/*+ INDEX(t) */
/*+ USE_NL(t) */
The FULL hint directs the optimizer to use a full table scan on the specified alias. INDEX hints direct index usage. NO_INDEX prevents index access, and USE_NL requests nested loops join.
Which hint instructs the optimizer to use a specific index idx1 on table t?
/*+ INDEX(t idx1) */
/*+ USE_HASH(t) */
/*+ NO_INDEX(t) */
/*+ FULL(t) */
The INDEX hint specifies the index name to be used on a table. FULL forces full scan, NO_INDEX prevents index use, and USE_HASH is for join method selection.
Which partitioning strategy divides data based on column value ranges?
COMPOSITE
HASH
RANGE
LIST
RANGE partitioning assigns rows to partitions based on specified value ranges of a column. HASH uses a hash function, LIST uses discrete value lists, and COMPOSITE combines strategies.
Which PL/SQL collection type is indexed by arbitrary keys like VARCHAR2?
Nested table
Associative array
VARRAY
REF CURSOR
Associative arrays (index-by tables) can be indexed by VARCHAR2 or BINARY_INTEGER. Nested tables and VARRAYs are indexed by numeric offsets. REF CURSOR is a cursor variable, not a collection.
Which PL/SQL feature allows opening a cursor into which query text can be dynamically passed?
CURSOR FOR LOOP
SYS_REFCURSOR
OPEN-FOR
REF CURSOR
REF CURSOR is a cursor variable type that allows dynamic queries at runtime. SYS_REFCURSOR is a predefined weak REF CURSOR subtype. CURSOR FOR LOOP is static, and OPEN-FOR is not a PL/SQL feature.
Which pragma directive makes a PL/SQL procedure run in its own transaction?
PRAGMA AUTONOMOUS_TRANSACTION
PRAGMA TRANSACTION
PRAGMA ATOMIC_COMMIT
PRAGMA SEPARATE
PRAGMA AUTONOMOUS_TRANSACTION allows a PL/SQL block to commit or rollback independently of the main transaction. No other pragma provides this behavior. Atomic commit is default behavior.
Which clause improves performance by fetching multiple rows into a collection in one operation?
FORALL
SAMPLE
FETCH FIRST
BULK COLLECT
BULK COLLECT fetches query results into PL/SQL collections in bulk, reducing context switches. FORALL is used for bulk DML. FETCH FIRST limits rows returned, and SAMPLE returns random subset.
Which Oracle feature implements row-level security by appending predicates automatically?
Virtual Private Database
Label Security
Fine-Grained Auditing
Oracle Vault
Virtual Private Database uses PL/SQL policies to enforce row-level security transparently. Label Security is multi-level security, Auditing logs events, and Oracle Vault isolates keys and privileged users.
Which analytic function returns the value of a column from the next row in the result set?
LAST_VALUE
LEAD
FIRST_VALUE
LAG
LEAD(expr, offset) gives the value from a following row relative to the current one. LAG returns a preceding row's value. FIRST_VALUE and LAST_VALUE return boundary values within a window. Oracle LEAD Function
Which optimizer feature rewrites queries to use materialized views automatically?
Query rewrite
Result cache
Flashback
Parallel query
Query rewrite enables the optimizer to transparently redirect queries to use materialized views for improved performance. Result cache stores results of functions, Flashback deals with data recovery, and Parallel query affects execution parallelism.
Which feature allows creation of a column whose values are derived from an expression?
Generated column
Virtual column
Formula column
Computed column
Virtual columns are defined by an expression and do not store data physically. Computed or formula columns are common in other databases but Oracle uses virtual columns. Generated column is terminology in other systems.
Which Oracle feature enables online upgrades by allowing multiple versions of objects to co-exist?
Object versioning
Session edition
Flashback edition
Edition-based redefinition
Edition-based redefinition allows online application upgrades by using editions that isolate object versions. Flashback edition provides point-in-time views, session edition is not a concept, and object versioning is not an Oracle feature.
In Oracle Sharding, which key determines the shard placement for a row?
Placement key
Sharding key
Distribution key
Partitioning key
The sharding key is a column or set of columns used to map data to specific shards in an Oracle Sharded database. Partitioning key relates to local partitioning, and distribution or placement keys are not standard terms.
0
{"name":"What is the default file extension for SQL Developer SQL worksheets?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What is the default file extension for SQL Developer SQL worksheets?, Which clause is used to filter rows in a SELECT statement?, Which function replaces NULL with a specified value?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Apply Complex SQL Query Techniques -

    Use Oracle SQL Developer to write advanced queries featuring joins, subqueries, and aggregations, ensuring precise data retrieval.

  2. Construct and Debug PL/SQL Procedures -

    Develop, execute, and troubleshoot PL/SQL routines to automate database operations and reinforce procedural programming skills.

  3. Analyze Query Results and Performance -

    Interpret the output of various SQL queries to understand data relationships and optimize execution speed for improved efficiency.

  4. Identify and Resolve SQL Errors -

    Spot common syntax and logic mistakes in your SQL code, then apply corrective strategies to ensure error-free execution.

  5. Evaluate SQL Skills with Free Online Quiz -

    Participate in the oracle sql developer practice online free quiz to receive instant feedback and a detailed score report, highlighting areas for improvement.

  6. Implement Best Practices in Oracle SQL Developer -

    Adopt industry-standard techniques for writing efficient, maintainable SQL queries and scripts that enhance readability and performance.

Cheat Sheet

  1. CRUD Essentials -

    In the oracle sql developer practice online free environment, mastering the four basic DML commands - CREATE (INSERT), READ (SELECT), UPDATE, DELETE - is crucial. Remember the mnemonic "CRUD" to quickly recall operations and always test your WHERE clauses to avoid unintended data changes. Official Oracle docs (docs.oracle.com) provide syntax and examples for each command.

  2. Joins and Subqueries -

    When tackling complex joins in a sql query quiz online, understand INNER, LEFT, RIGHT, and FULL JOIN behaviors and how correlated subqueries differ from nested ones. For instance, use ON for join conditions and avoid WHERE for join logic to prevent cross-join pitfalls. Academic resources like Stanford's "Introduction to Databases" offer clear diagrams and examples.

  3. PL/SQL Block Structure -

    Before hitting "Run" in a sql programming online test, be comfortable with anonymous and named PL/SQL blocks: DECLARE, BEGIN, EXCEPTION, END. Practice exception handling with RAISE_APPLICATION_ERROR for custom errors and use %TYPE for strong typing. The Oracle PL/SQL User's Guide (docs.oracle.com) is the authoritative reference.

  4. Performance Tuning & Indexes -

    In your next sql quiz online test, use EXPLAIN PLAN to visualize how Oracle executes queries and identify full table scans. Create B-tree or bitmap indexes on frequently filtered or joined columns, but watch for index maintenance overhead on heavy DML tables. Research from the Oracle Optimizer team details cost-based strategies for index selection.

  5. Oracle SQL Developer Features -

    Leverage the SQL Worksheet, Data Modeler, and integrated debugger in Oracle SQL Developer to streamline development workflows. Use the GUI's code snippets and SQL History to replay past commands, and employ the Autotrace panel to benchmark performance instantly. Industry blogs like Oracle-Base.com showcase productivity tips and best practices.

Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Programming & Software Quizzes

Powered by: Quiz Maker