ORACLE MOCK 3 is designed to assess skills in SQL and Oracle database management, covering SQL*Plus features, query formulation, view modification, and constraints. This quiz is ideal for learners aiming to enhance their database handling capabilities.
SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;
SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;
SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
Rate this question:
ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students(student_id);
ALTER TABLE student_grades ADD CONSTRAINT NAME = student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);
Rate this question:
ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);
MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;
MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.
Rate this question:
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK
UNIQUE
Rate this question:
UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;
UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees) WHERE employee_id =180;
UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);
UPDATE new_employees SET name = (SELECT last name|| first_name FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
Rate this question:
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;
Rate this question:
SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;
SELECT COUNT(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
SELECT MIN(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;
SELECT SUM(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
SELECT MEDIAN(gpa) FROM student_grades WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
Rate this question:
You can use aggregate functions in any clause of a SELECT statement.
You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
You can use aggregate functions on a table, only by grouping the whole table as one single group.
You cannot group the rows of a table by more than one column while using aggregate functions.
Rate this question:
Immediately after the SELECT clause
Before the WHERE clause
Before the FROM clause
After the ORDER BY clause
After the WHERE clause
Rate this question:
CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
Rate this question:
SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Rate this question:
SELECT &1,
SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';
SELECT &1, &2 FROM &3 WHERE last_name = '&4';
SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
Rate this question:
The two statements produce identical results.
The second statement returns a syntax error.
There is no need to specify DESC because the results are sorted in descending order by default.
The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Rate this question:
The indexed column is declared as NOT NULL.
The indexed columns are used in the FROM clause.
The indexed columns are part of an expression.
The indexed column contains a wide range of values.
Rate this question:
SELECT COUNT(*) FROM employees WHERE last_name='Smith';
SELECT COUNT(dept_id) FROM employees WHERE last_name='Smith';
SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
Rate this question:
Creates a view with constraints
Creates a view even if the underlying parent table has constraints
Creates a view in another schema even if you don't have privileges
Creates a view regardless of whether or not the base tables exist
Rate this question:
You can join a maximum of two tables through an equijoin.
You can join a maximum of two columns through an equijoin.
You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Rate this question:
A WHERE clause can be used to restrict both rows and groups.
A WHERE clause can be used to restrict rows only.
A HAVING clause can be used to restrict both rows and groups.
A HAVING clause can be used to restrict groups only.
A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
A HAVING clause CANNOT be used in subqueries.
Rate this question:
At the beginning of the list.
At the end of the list
In the middle of the list.
At the same location they are listed in the unordered table.
Rate this question:
At the beginning of the list.
At the end of the list
In the middle of the list.
At the same location they are listed in the unordered table.
Rate this question:
The statement calculates the finance charge incorrectly.
The statement calculates the current balance incorrectly.
The statement returns only accounts that have NO previous balance.
The statement returns only accounts that have new purchases, previous balance, and
Rate this question:
SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') From dual;
SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') From dual;
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') From dual;
SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') From dual;
SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') From dual;
Rate this question:
SUM(start_date)
AVG(start_date)
COUNT(start_date)
AVG(start_date, end_date)
MIN(start_date)
MAXIMUM(start_date)
SINH
TO_NUMBER.
SQRT.
ROUND.
Rate this question:
SQRT
DECODE
NEW_TIME
ROWIDTOCHAR.
Rate this question:
SELECT employee_id, department_id, department_name,salary.FROM employees WHERE department_id IN (SELECT department_id FROM departments);
SELECT employee_id, department_id, department_name,salary FROM employees NATURAL JOIN departments;
SELECT employee_id, d.department_id, department_name,salary FROM employees e JOIN departments d ON e.department_id =d.department_id;
SELECT employee_id, department_id, department_name,Salary FROM employees JOIN departments USING (e.department_id,d.department_id);
Rate this question:
Both tables have NULL values.
You want all unmatched data from one table.
You want all matched data from both tables.
You want all unmatched data from both tables.
One of the tables has more data than the other.
You want all matched and unmatched data from only one table.
Rate this question:
You cannot use IN operator in a condition that involves an outerjoin.
You use (+) on both sides of the WHERE condition to perform an outerjoin.
You use (*) on both sides of the WHERE condition to perform an outerjoin.
You use an outerjoin to see only the rows that do not meet the join condition.
In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.
Rate this question:
=
OR
AND
IN
Rate this question:
8
2
3
4
5
There is no such criteria
Rate this question:
The PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 and have a QTY_IN_STOCK value greater than 100.
The PRODUCT_ID , PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100.
The PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100 and have a supplier.
The PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100.
Rate this question:
A subquery CANNOT reference a table that is not included in the outer query's FROM clause.
Subqueries can be nested up to 5 levels.
A subquery must be placed on the right side of the comparison operator.
Subqueries can return multiple columns.
Rate this question:
The main query executes with the first value returned by the subquery.
The main query executes with the last value returned by the subquery.
The main query executes with all the values returned by the subquery.
The main query fails because the multiple-row subquery cannot be used with the comparison Operator.
You cannot define a multiple-row subquery in the WHERE clause of a SQL query.
Rate this question:
Quiz Review Timeline (Updated): Mar 22, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
Wait!
Here's an interesting quiz for you.