Oracle SQL And PL/SQL Practice Quiz For Developers
Test your Oracle SQL and PL/SQL prowess with our Practice Quiz designed for developers. Dive into a challenging set of questions covering SQL queries, data manipulation, and procedural language concepts. Sharpen your skills in database development as you tackle real-world scenarios. Whether you're a seasoned developer or just starting, this quiz offers a valuable opportunity to refine your Oracle SQL and PL/SQL expertise. Gear up for an engaging experience that combines theoretical knowledge with practical application, perfect for those aiming to excel in Oracle database development. Take the quiz and elevate your proficiency in SQL and PL/SQL for effective database Read moremanagement.
- 1.
Which of the following statements contains an error?
- A.
SELECT * FROM emp WHERE empid = 493945;
- B.
SELECT empid FROM emp WHERE empid= 493945;
- C.
SELECT empid FROM emp;
- D.
SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
Correct Answer
D. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;Explanation
The error in the statement "SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;" is that it is missing the "FROM" keyword. In SQL syntax, the "FROM" keyword is used to specify the table from which the data is being retrieved. So, the correct statement should be "SELECT empid FROM emp WHERE empid = 56949 AND lastname = ‘SMITH’;".Rate this question:
-
- 2.
The command to remove rows from a table 'CUSTOMER' is:
- A.
REMOVE FROM CUSTOMER...
- B.
DROP FROM CUSTOMER...
- C.
DELETE FROM CUSTOMER WHERE...
- D.
UPDATE FROM CUSTOMER...
Correct Answer
C. DELETE FROM CUSTOMER WHERE...Explanation
The correct answer is "DELETE FROM CUSTOMER WHERE...". This is the correct command to remove rows from a table called 'CUSTOMER' in a database. The "DELETE" keyword is used to remove rows, the "FROM" keyword specifies the table from which the rows should be deleted, and the "WHERE" clause is used to specify the condition that determines which rows should be deleted.Rate this question:
-
- 3.
Which is an /SQL*Plus command?
- A.
DELETE
- B.
UPDATE
- C.
SELECT
- D.
ALTER
- E.
DESCRIBE
Correct Answer
E. DESCRIBEExplanation
The correct answer is DESCRIBE. DESCRIBE is an SQL*Plus command used to display the structure of a table, including its column names, data types, and constraints. It provides information about the table's structure without actually retrieving any data from the table. This command is useful for understanding the layout of a table and its columns before querying or modifying the data.Rate this question:
-
- 4.
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
- A.
Immediately after the SELECT clause
- B.
Before the WHERE clause
- C.
Before the FROM clause
- D.
After the ORDER BY clause
- E.
After the WHERE clause
Correct Answer
E. After the WHERE clauseExplanation
The GROUP BY clause is placed after the WHERE clause in a SELECT statement. The WHERE clause is used to filter the rows that will be included in the query result, and the GROUP BY clause is used to group the rows based on one or more columns. By placing the GROUP BY clause after the WHERE clause, it ensures that the filtering is applied first before the grouping is done. This allows for more specific and targeted grouping of the data.Rate this question:
-
- 5.
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
- A.
ORDER BY SALARY > 5000.
- B.
GROUP BY SALARY > 5000
- C.
HAVING SALARY > 5000
- D.
WHERE SALARY > 5000
Correct Answer
D. WHERE SALARY > 5000Explanation
The WHERE clause is used in a SELECT statement to specify a condition for filtering the rows returned by the query. In this case, the condition is "SALARY > 5000", which means that only the employees with a salary greater than 5000 will be displayed.Rate this question:
-
- 6.
You need to calculate the total of all salaries in the accounting department. Which group function should you use?
- A.
MAX
- B.
MIN
- C.
SUM
- D.
COUNT
- E.
TOTAL
- F.
LARGEST
Correct Answer
C. SUMExplanation
To calculate the total of all salaries in the accounting department, the appropriate group function to use is SUM. This function will add up all the salaries in the department and provide the total amount. MAX and MIN functions are used to find the highest and lowest values respectively, while COUNT is used to count the number of rows. TOTAL and LARGEST are not valid group functions.Rate this question:
-
- 7.
In Oracle SQL, which function can be used to replace null values in a column with a specified value?
- A.
NVL
- B.
DECODE
- C.
COALESCE
- D.
NULLIF
Correct Answer
A. NVLExplanation
The NVL function in Oracle SQL is used to replace null values in a column with a specified value. For example, NVL(column_name, 'default_value') will return 'default_value' if column_name is null. The other options, DECODE and COALESCE, also handle null values but in different contexts. NULLIF returns null if two expressions are equal.Rate this question:
-
- 8.
What does the TRUNCATE statement do?
- A.
Removes the table
- B.
Removes all rows from a table
- C.
Shortens the table to 10 rows
- D.
Removes all columns from a table
- E.
Removes foreign keys from a table
Correct Answer
B. Removes all rows from a tableExplanation
The TRUNCATE statement is used to remove all rows from a table. It is a fast and efficient way to delete all data from a table without logging individual row deletions. Unlike the DELETE statement, which removes rows one by one and generates a log for each deletion, TRUNCATE simply deallocates the data pages of the table, resulting in faster performance.Rate this question:
-
- 9.
Which two statements about creating constraints are true? (Choose two)
- A.
Constraint names must start with SYS_C.
- B.
All constraints must be defined at the column level.
- C.
Constraints can be created after the table is created.
- D.
Constraints can be created at the same time the table is created.
Correct Answer(s)
C. Constraints can be created after the table is created.
D. Constraints can be created at the same time the table is created.Explanation
The first statement is true because constraint names in Oracle must start with SYS_C. The second statement is false because constraints can also be defined at the table level. The third statement is true because constraints can be created after the table is created. The fourth statement is also true because constraints can be created at the same time the table is created.Rate this question:
-
- 10.
Which of the following statements is used to create users?
- A.
Create user user_name identified user_password;
- B.
Create user user_name user_password;
- C.
Create user user_name identified by user_password;
- D.
None of these;
Correct Answer
C. Create user user_name identified by user_password;Explanation
The correct answer is "Create user user_name identified by user_password;". This statement is the correct syntax for creating a user in a database. The "identified by" keyword is used to specify the password for the user. The other statements are incorrect as they do not use the correct syntax or do not include the "identified by" keyword.Rate this question:
-
- 11.
Which four are valid Oracle constraint types? (Choose four.)
- A.
CASCADE
- B.
UNIQUE
- C.
NONUNIQUE
- D.
CHECK
- E.
PRIMARY KEY
- F.
CONSTANT
- G.
NOT NULL
Correct Answer(s)
B. UNIQUE
D. CHECK
E. PRIMARY KEY
G. NOT NULLExplanation
The four valid Oracle constraint types are UNIQUE, CHECK, PRIMARY KEY, and NOT NULL. UNIQUE ensures that each value in a column is unique, CHECK specifies a condition that must be met for the data to be valid, PRIMARY KEY enforces a unique identifier for each row in a table, and NOT NULL ensures that a column must have a value and cannot be left empty.Rate this question:
-
- 12.
Which one of the following sorts rows in SQL?
- A.
GROUP BY
- B.
ORDER BY
- C.
SORT BY
- D.
HAVING BY
Correct Answer
B. ORDER BYExplanation
ORDER BY is used in SQL to sort the rows in a result set based on one or more columns. It arranges the rows in ascending or descending order according to the specified column(s). This allows for the data to be presented in a more organized and meaningful way. The other options, GROUP BY, SORT BY, and HAVING BY, are not valid SQL keywords for sorting rows.Rate this question:
-
- 13.
What is true about the WITH GRANT OPTION clause?
- A.
It allows a grantee DBA privileges.
- B.
It is required syntax for object privileges.
- C.
It allows privileges on specified columns of tables.
- D.
It is used to grant an object privilege on a foreign key column.
- E.
It allows the grantee to grant object privileges to other users and roles.
Correct Answer
E. It allows the grantee to grant object privileges to other users and roles.Explanation
The WITH GRANT OPTION clause allows the grantee to grant object privileges to other users and roles. This means that the grantee can pass on the privileges they have been granted to other entities. This clause is commonly used in database systems to delegate authority and control over certain objects to other users or roles, allowing for a more flexible and customizable access control system.Rate this question:
-
- 14.
Which three statements about subqueries are true? (Choose three.)
- A.
A main query can have more than one subquery.
- B.
A subquery can have more than one main query.
- C.
The subquery and main query must retrieve data from the same table.
- D.
The subquery and main query can retrieve data from different tables.
- E.
Only one column or expression can be compared between the subquery and main query.
- F.
Multiple columns or expressions can be compared between the subquery and main query.
Correct Answer(s)
A. A main query can have more than one subquery.
D. The subquery and main query can retrieve data from different tables.
F. Multiple columns or expressions can be compared between the subquery and main query.Explanation
A main query can have more than one subquery: A main query can include multiple subqueries in various parts of the query, such as in the SELECT, WHERE, and FROM clauses.
The subquery and main query can retrieve data from different tables: Subqueries can retrieve data from different tables than the main query, allowing for complex queries that combine information from multiple sources.
Multiple columns or expressions can be compared between the subquery and main query: Subqueries can involve multiple columns or expressions for comparison, which can be used for complex conditions in the main query.Rate this question:
-
- 15.
What is true about sequences?
- A.
The start value of the sequence is always 1.
- B.
A sequence always increments by 1.
- C.
The minimum value of an ascending sequence defaults to 1.
- D.
The maximum value of a descending sequence defaults to 1.
Correct Answer
C. The minimum value of an ascending sequence defaults to 1.Explanation
In databases, when a sequence is created without specifying a start value, increment, minimum value, or maximum value, it often defaults to starting at 1 and incrementing by 1. However, the start value and increment can be explicitly set to different values. The minimum value of an ascending sequence typically defaults to 1 unless specified otherwise. This means that the sequence will not generate values below 1 unless a different minimum value is defined.Rate this question:
-
- 16.
Which one is a system privilege?
- A.
DROP
- B.
DELETE
- C.
TRUNCATE
- D.
ALTER TABLE
- E.
CREATE TABLE
Correct Answer
E. CREATE TABLEExplanation
A system privilege is a permission that allows a user to perform certain actions on the database system itself, rather than on specific tables or data. In this case, the "CREATE TABLE" option is the correct answer because it grants the privilege to create new tables in the database. The other options, such as "DROP," "DELETE," "TRUNCATE," and "ALTER TABLE," are not system privileges but rather actions that can be performed on existing tables or data within the tables.Rate this question:
-
- 17.
Which four are types of functions available in SQL? (Choose 4)
- A.
String
- B.
Integer
- C.
Calendar
- D.
Numeric
- E.
Translation
- F.
Date
- G.
Conversion
Correct Answer(s)
A. String
D. Numeric
F. Date
G. ConversionExplanation
In SQL, functions are categorized based on the type of data they operate on or the purpose they serve. From the list provided, the types of functions available in SQL that you can choose include:
String (or Character) - These functions operate on string (text) data, manipulating and processing text.
Numeric - These functions are used to perform calculations and operations on numeric data types.
Date - Date functions are used to handle, format, and calculate data involving dates and times.
Conversion - Conversion functions are used to convert data from one type to another, such as from a string to a number or from a date to a string.Rate this question:
-
- 18.
The primary key is selected from the:
- A.
Composite keys.
- B.
Determinants.
- C.
Candidate keys.
- D.
Foreign keys.
Correct Answer
C. Candidate keys.Explanation
The primary key is selected from the candidate keys. Candidate keys are the minimal set of attributes that can uniquely identify each tuple in a relation. The primary key is chosen from these candidate keys to serve as the unique identifier for each tuple in the relation. Composite keys are keys that are made up of multiple attributes, determinants are attributes that determine the values of other attributes, and foreign keys are attributes that refer to the primary key of another relation.Rate this question:
-
- 19.
Which is a valid CREATE TABLE statement?
- A.
Create table emp add(id number(3));
- B.
Create table emp (id number(3));
- C.
Create table emp modified (id number(3));
- D.
Create table emp (id numbers(3));
Correct Answer
B. Create table emp (id number(3));Explanation
The correct answer is "Create table emp (id number(3));". This is a valid CREATE TABLE statement because it follows the correct syntax for creating a table named "emp" with a column named "id" of data type "number" and a maximum length of 3 digits.Rate this question:
-
- 20.
A tuple is a?
- A.
Row of a table.
- B.
Key of a table.
- C.
Column of a table.
- D.
Two-dimensional table
Correct Answer
A. Row of a table.Explanation
A tuple is a row of a table. In database management systems, a tuple represents a single record or row in a table. It contains a collection of related data fields, each representing a specific attribute or column of the table. Tuples are used to store and organize data in a structured manner, allowing for efficient retrieval and manipulation of information within a database.Rate this question:
-
- 21.
The DROP TABLE statement:
- A.
Deletes the table structure only.
- B.
Deletes the table structure along with the table data.
- C.
Works whether or not referential integrity constraints would be violated.
- D.
Is not an SQL statement.
Correct Answer
B. Deletes the table structure along with the table data.Explanation
The DROP TABLE statement is used to delete a table from a database. It not only deletes the table structure but also removes all the data stored in that table. This means that all the rows and columns within the table will be permanently deleted. It is a valid SQL statement that can be used to remove tables from a database. Referential integrity constraints, which ensure the consistency and integrity of data, are not a factor in the execution of the DROP TABLE statement.Rate this question:
-
- 22.
How do you select all the records from a table named "EMP" where the value of the column "Last_Name" is "KING"?
- A.
SELECT [all] FROM EMP WHERE Last_Name ='KING'
- B.
SELECT * FROM EMP WHERE Last_Name ='KING'
- C.
SELECT * FROM EMP WHERE Last_Name LIKE 'KING'
- D.
SELECT [all] FROM EMP WHERE Last_Name LIKE 'KING'
Correct Answer
C. SELECT * FROM EMP WHERE Last_Name LIKE 'KING'Explanation
The asterisk (*) symbol is used to select all columns from the table.
The "FROM" clause specifies the table name "EMP" from which to retrieve the records.
The "WHERE" clause filters the records based on the condition that the value of the "Last_Name" column must be equal to "KING".Rate this question:
-
- 23.
Which SQL statement is used to insert new data in a database?
- A.
ADD RECORD
- B.
ADD NEW
- C.
INSERT INTO
- D.
INSERT NEW
Correct Answer
C. INSERT INTOExplanation
The SQL statement "INSERT INTO" is used to insert new data into a database. This statement allows you to specify the table name and the values to be inserted into the corresponding columns. It is the standard SQL syntax for inserting new records into a database table.Rate this question:
-
- 24.
The SQL ALTER statement can be used to:
- A.
Change the table structure.
- B.
Change the table data.
- C.
Add rows to the table.
- D.
Delete rows from the table.
Correct Answer
A. Change the table structure.Explanation
The SQL ALTER statement is used to modify the structure of a table in a database. It allows for changes such as adding or deleting columns, modifying data types, and altering constraints or indexes. This statement does not directly affect the table data or manipulate rows within the table, but rather focuses on modifying the structure or schema of the table itself.Rate this question:
-
- 25.
Which command is used to remove the table 'DEPTT'?
- A.
REMOVE FROM DEPTT
- B.
DROP TABLE DEPTT
- C.
DELETE FROM DEPTT
- D.
UPDATE FROM DEPTT
Correct Answer
B. DROP TABLE DEPTTExplanation
The correct answer is "DROP TABLE DEPTT". This command is used to remove a table from the database. The "REMOVE FROM DEPTT" command does not exist in SQL. The "DELETE FROM DEPTT" command is used to remove rows from a table, not the entire table. The "UPDATE FROM DEPTT" command is used to modify data in a table, not remove the table itself.Rate this question:
-
- 26.
Can you commit inside a Trigger?
- A.
Yes, always
- B.
No, never
- C.
Yes, in autonomous transactions
- D.
Except for autonomous transactions
Correct Answer
C. Yes, in autonomous transactionsExplanation
In Oracle, it is possible to commit inside a trigger using autonomous transactions. Autonomous transactions allow a trigger to perform separate transactions independent of the main transaction. This means that any changes made within the autonomous transaction can be committed or rolled back independently of the main transaction. Therefore, it is possible to commit inside a trigger using autonomous transactions.Rate this question:
-
- 27.
Maximum characters allowed in dbms_out.put_line()
- A.
256 chars
- B.
255 chars
- C.
512 chars
- D.
511 chars
- E.
None of the above
Correct Answer
B. 255 charsExplanation
The correct answer is 255 chars because the maximum characters allowed in the dbms_out.put_line() function is limited to 255 characters. This means that any string passed to this function should not exceed 255 characters in length.Rate this question:
-
- 28.
Can you delete a column in a table with data in Oracle?
- A.
Yes, always
- B.
Yes, but not always
- C.
No, never
Correct Answer
A. Yes, alwaysExplanation
Yes, always. In Oracle, it is possible to delete a column from a table even if it contains data. However, it is important to note that deleting a column will permanently remove the data stored in that column. Therefore, it is crucial to carefully consider the implications and potential consequences before proceeding with the deletion.Rate this question:
-
- 29.
Maximum number of columns in a table or view in Oracle 9i?
- A.
250
- B.
500
- C.
1000
- D.
5000
- E.
Infinite
Correct Answer
C. 1000Explanation
In Oracle 9i, the maximum number of columns that can be present in a table or view is 1000. This means that a table or view can have a maximum of 1000 columns defined.Rate this question:
-
- 30.
What is the Datatype of NULL in Oracle?
- A.
Char(0)
- B.
Char(1)
- C.
NULL
- D.
None of the above
Correct Answer
A. Char(0)Explanation
In Oracle, the datatype of NULL is not char(0), char(1), or any other specific datatype. NULL is a special value that represents the absence of data or an unknown value. It is not associated with any particular datatype. Therefore, the correct answer is "None of the above".Rate this question:
-
- 31.
How to check the version of Oracle?
- A.
Select * from $version;
- B.
Select * from v$version;
- C.
Select * from users_version;
- D.
Select * from v_version;
Correct Answer
B. Select * from v$version;Explanation
The correct answer is "select * from v$version;". This query is used to check the version of Oracle. The "v$version" view contains information about the version of the Oracle database. By running this query, the user can retrieve the version details and confirm the version of Oracle they are using.Rate this question:
-
- 32.
Maximum levels of subqueries in the WHERE clause of an SQL statement?
- A.
127
- B.
255
- C.
511
- D.
Unlimited
Correct Answer
B. 255Explanation
The maximum levels of subqueries in the WHERE clause of an SQL statement is 255. This means that we can have up to 255 nested subqueries within the WHERE clause of an SQL statement. Subqueries are used to retrieve data from one table and use it in another query as a condition. Having a high limit of 255 allows for complex and nested queries to be constructed, providing more flexibility in querying and manipulating data.Rate this question:
-
- 33.
Maximum length of all Oracle Objects in 9i:
- A.
12
- B.
15
- C.
25
- D.
30
Correct Answer
D. 30Explanation
In Oracle 9i, the maximum length of all Oracle objects is 30. This means that any object, such as a table name, column name, or variable name, cannot exceed this length. This limit is important to consider when designing and naming database objects in order to ensure compatibility and avoid any potential issues or errors.Rate this question:
-
- 34.
Oracle UTL_FILE Package is used :
- A.
To Create Tables
- B.
To Create triggers
- C.
In Autonomous Transactions
- D.
To Read write from and to External files
Correct Answer
D. To Read write from and to External filesExplanation
The Oracle UTL_FILE Package is used to read from and write to external files. It provides procedures and functions that allow users to manipulate files on the operating system level. This package is commonly used in Oracle databases to import data from external sources or export data to external files. It provides a convenient way to interact with files outside of the database environment.Rate this question:
-
- 35.
How to Kill a Session in Oracle?
- A.
ALTER SYSTEM KILL SESSION 'sid,serial#'
- B.
ALTER KILL SESSTION 'sid,serial#'
- C.
ALTER TABLE KILL SESSION 'sid,serial#'
- D.
ALTER SYSTEM KILL SESSIONS 'sid,serial#'
Correct Answer
A. ALTER SYSTEM KILL SESSION 'sid,serial#'Explanation
The correct answer is "ALTER SYSTEM KILL SESSION 'sid,serial#'". This statement is used to kill a specific session in Oracle. The 'sid' refers to the session identifier and the 'serial#' refers to the serial number of the session. By executing this command, the specified session will be terminated and all the associated resources will be released.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Jun 28, 2024Quiz Edited by
ProProfs Editorial Team
Expert Reviewed by
Samy Boulos -
Feb 22, 2013Quiz Created by
Maarsh
Back to top