Oracle Introduction to Oracle9i SQL Set 1

Question - 15 Maximum mark : - 27

Home
All Exams
Oracle Introduction to Oracle9i SQL Online Mock Tests
Oracle Introduction to Oracle9i SQL Set 1

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 DEPARMENT_ID = NULL;

What is true about this SQL statement?

The SQL statement displays the desired results. ✘ The column in the WHERE clause should be changed to display the desired results. ✘ The operator in the WHERE clause should be changed to display the desired results. ✘ The WHERE clause should be changed to use an outer join to display the desired results. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Evaluate the SQL statement:

SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;

What will be displayed?

1 ✘ An error statement ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the description of the MARKS table:

STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)

SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:

SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2)
ORDER BY total_ marks;

What is the result of the SELECT statement?

The statement executes successfully and returns the student ID and sum of all marks for each student who
obtained more than the average mark in each subject. ✘ The statement returns an error at the SELECT clause. ✘ The statement returns an error at the WHERE clause. ✘ The statement returns an error at the ORDER BY clause. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

Substitution variables ✘ Replacement variables ✘ Prompt variables ✘ Instead-of variables ✔

This feature cannot be implemented through /SQL*Plus.

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You want to display the titles of books that meet these criteria:

1. Purchased before January 21, 2001
2. Price is less then $500 or greater than $900

You want to sort the results by their data of purchase, starting with the most recently bought book.
Which statement should you use?

SELECT book_title
FROM books
WHERE price between 500 and 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase_date;

✘

SELECT book_titleFROM books
WHERE price IN (500,900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date ASC;

✘

SELECT book_titleFROM books
WHERE price < 500 or > 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;

✘

SELECT book_titleFROM books
WHERE (price < 500 OR price > 900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which statement explicitly names a constraint?

ALTER TABLE student_grades
ADD
FOREIGN KEY (student_id) REFERENCES students (student_id);

✘

ALTER TABLE student_gradesADD CONSTRAINT NAME = student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);

✘

ALTER TABLE student_gradesADD CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);

✘

ALTER TABLE student gradesADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);

✔

ALTER TABLE student gradesADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and
DEPARTMENTS tables:

EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME.
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key
DEPARTMENT_ID column of the DEPARTMENTS table.

You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the
EMPLOYEES tables.
How can you accomplish this task?

ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);

✘

MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER);

✘

ALTER VIEW emp_dept_vu ASSELECT 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 ASSELECT 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 ASSELECT employee_id, employee_name,
department_name, manager_id
FROM employees e, departments d
WHERE e.department _ id = d.department_id;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEESEMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHARD2(25)
LAST_NAME VARCHARD2(25)
HIRE_DATE DATE
NEW EMPLOYEESEMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)

Which UPDATE statement is valid?

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 = (SELECTlast_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;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the description of the EMPLOYEES table:

EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)

Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only
of the minimum salary is less then 5000 and the maximum salary is more than 15000?

SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) <5000 AND MAX (salary) > 15000;

✘

SELECT dept_id, MIN(salary), MAX(salary)FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;

✘

SELECT dept_id, MIN(salary), MAX(salary)FROM employees
HAVING MIN(salary) <5000 AND MAX (salary) > 15000;

✘

SELECT dept_id, MIN(salary), MAX(salary)FROM employees
GROUP BY dept_id
HAVING MIN (salary) < 5000 AND MAX(salary)

✔

SELECT dept_id, MIN(salary), MAX(salary)FROM employees
GROUP BY dept_id, salary
HAVING MIN (salary) <5000 AND MAX (salary) > 15000;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

Examine the structure if the EMPLOYEES table:

Column name Data Type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID
column of the DEPARTMENTS table

You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL
statement, when used to create the EMP_VU view, allows the user to insert rows?

CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120);

✘

CREATE VIEW emp_Vu ASSELECT employee_id, emp_name, job_id
department_id
FROM employees
WHERE mgr_id IN (102, 120);

✘

CREATE VIEW emp_Vu ASSELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_ id;

✘

CREATE VIEW emp_Vu ASSELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Oracle Introduction to Oracle9i SQL Set 1

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner