Home › Quizzes › Oracle SQL Fundamentals

Oracle SQL Fundamentals

Test your knowledge of Oracle SQL including views, joins, subqueries, functions, constraints, and data types.

20 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Multiple Answers)

Select invalid variable types

  1. VARCHAR2
  2. VARCHAR1
  3. CHAR
  4. CHAR2
  5. INTEGER
  6. NUMBER
Question 2 Multiple Choice (Single Answer)

SELECT ROUND (-123.39953,-2) FROM DUAL;

  1. -123.40
  2. -100
  3. -120
  4. -123.30
Question 3 Multiple Choice (Single Answer)

SELECT ROUND(-123.39953,-3) FROM DUAL;

  1. 0
  2. 100
  3. 123
  4. -123
Question 4 Multiple Choice (Single Answer)

SELECT CONCAT(SUBSTR('MINDWORKS',-5,6),SUBSTR('KNOWMAX',-3,4)) FROM DUAL;

  1. KNOWMIND
  2. WORKSM
  3. WORKSMAX
  4. MAXWORKS
Question 5 Multiple Choice (Single Answer)

SELECT DECODE('MINDWORKS','WORKS','MINDWORK','KNOWMAX') FROM DUAL;

  1. KNOWMAX
  2. MINDWORKS
  3. WORKS
  4. MINDWORK
Question 6 Multiple Choice (Single Answer)

How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

  1. 1
  2. 2
  3. 3
  4. 0
  5. 4
Question 7 Multiple Choice (Single Answer)

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access
  2. Access via unique index
  3. Table access by ROWID
  4. Full table scan
Question 8 Multiple Choice (Single Answer)

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

  1. ALTER TABLE students ADD PRIMARY KEY student_id;
  2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
  3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
  4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  5. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Question 9 Multiple Choice (Multiple Answers)

Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)

  1. TIMESTAMP
  2. INTERVAL MONTH TO DAY
  3. INTERVAL DAY TO SECOND
  4. INTERVAL YEAR TO MONTH
  5. TIMESTAMP WITH DATABASE TIMEZONE
Question 10 Multiple Choice (Multiple Answers)

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

  1. The DESCRIBE DEPT statement displays the structure of the DEPT table.
  2. The ROLLBACK statement frees the storage space occupies by the DEPT table.
  3. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Question 11 Multiple Choice (Single Answer)

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

  1. USER_TAB_PRIVS_MADE
  2. USER_TAB_PRIVS
  3. USER_COL_PRIVS_MADE
  4. USER_COL_PRIVS
Question 12 Multiple Choice (Single Answer)

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement?

  1. The SQL statement displays the desired results.
  2. The column in the WHERE clause should be changed to display the desired results.
  3. The operator in the WHERE clause should be changed to display the desired results.
  4. The WHERE clause should be changed to use an outer join to display the desired results.
Question 13 Multiple Choice (Single Answer)

Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?

  1. 0
  2. 1
  3. 0.00
  4. An error statement
Question 14 Multiple Choice (Multiple Answers)

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

  1. NOT NULL
  2. PRIMARY KEY
  3. FOREIGN KEY
  4. CHECK
  5. UNIQUE KEY
Question 15 Multiple Choice (Multiple Answers)

Which two are true about aggregate functions? (Choose two.)

  1. You can use aggregate functions in any clause of a SELECT statement.
  2. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
  3. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
  4. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
  5. You can use aggregate functions on a table, only by grouping the whole table as one single group.
  6. You cannot group the rows of a table by more than one column while using aggregate functions.
Question 16 Multiple Choice (Multiple Answers)

Which two statements about subqueries are true? (Choose two.)

  1. A single row subquery can retrieve data from only one table.
  2. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.
  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.
  4. A single row subquery can retrieve data from more than one table.
  5. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
  6. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.
Question 17 Multiple Choice (Single Answer)

You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

  1. MERGE
  2. INSERT
  3. UPDATE
  4. ADD
  5. ENTER
  6. You cannot enter the phone numbers for the existing employee records.
Question 18 Multiple Choice (Single Answer)

In which case would you use a FULL OUTER JOIN?

  1. Both tables have NULL values.
  2. You want all unmatched data from one table.
  3. You want all matched data from both tables.
  4. You want all unmatched data from both tables.
  5. One of the tables has more data than the other.
  6. You want all matched and unmatched data from only one table.
Question 19 Multiple Choice (Multiple Answers)

Which two statements accurately describe a role? (Choose two.)

  1. A role can be given to a maximum of 1000 users.
  2. A user can have access to a maximum of 10 roles.
  3. A role can have a maximum of 100 privileges contained in it.
  4. Privileges are given to a role by using the CREATE ROLE statement.
  5. A role is a named group of related privileges that can be granted to the user.
  6. A user can have access to several roles, and several users can be assigned the same role.
Question 20 Multiple Choice (Single Answer)

What is necessary for your query on an existing view to execute successfully?

  1. The underlying tables must have data.
  2. You need SELECT privileges on the view.
  3. The underlying tables must be in the same schema.
  4. You need SELECT privileges only on the underlying tables.
← Back to Quizzes