Oracle Introduction to Oracle9i SQL Set 4

Question - 15 Maximum mark : - 29

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

Examine the structure of the EMP_DEPT_VU view:
Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table
EMP_NAME VARCHAR2(30) From the EMPLOYEES table
JOB_ID VARCHAR2(20) From the EMPLOYEES table
SALARY NUMBER From the EMPLOYEES table
DEPARTMENT_ID NUMBER From the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table

Which SQL statement produces an error?

SELECT *
FROM emp_dept_vu;

✘

SELECT department_id, SUM(salary)FROM emp_dept_vu
GROUP BY department _ id;

✘

SELECT department_id, job_id, AVG(salary)FROM emp_dept_vu
GROUP BY department _ id, job_id;

✘

SELECT job_id, SUM(salary)FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM (salary) > 20000

✔ None of the statements produce an error; all are valid. ✔

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:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)

Which MERGE statement is valid?

MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN
MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN
INSERT VALUES (e.employees_id, e.first_name ||','|| e.last_name);

✘

MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTSTHEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT
VALUES (e.employee_id, e.first_name ||','|| e.last_name);

✘

MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHENEXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN
INSERT VALUES (e.employees_id, e.first_name ||','|| e.last_name);

✘

MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHEDTHEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT
INTO new _ employees VALUES (e.employees_id, e.first_name ||','|| e.last_name);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in
the ENAME column, for those employees whose ENAME ends with a the letter "n"?

SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR
(ENAME, -1,1) = 'n';

✘

SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1,1) = 'n';

✘

SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1,1) = 'n';

✘

SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1,1) = 'n';

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which four are valid Oracle constraint types? (Choose four.)

NOT NULL UNIQUE NONUNIQUE CHECK PRIMARY KEY

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

DROP emp_dept_uv;

✘

DELETE emp_dept_uv;

✘

REMOVE emp_dept_uv;

✘

DROP VIEW emp_dept_uv;

✔

DELETE VIEW emp_dept_uv;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which is an iSQL*Plus command?

INSERT ✘ UPDATE ✘ SELECT ✘ DESCRIBE ✔ DELETE ✔

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)

Which statement produces the number of different departments that have employees with last name Smith?

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 UNIQE (dept_id) FROM employees WHERE last _name='smith';

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table
holds a value?

ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

✘

ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

✘

ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

✘

ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

✔

ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Evaluate this SQL statement:

SELECT ename, sal, 12* sal+100 FROM emp;

The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax
to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

No change is required to achieve the desired results. ✘

SELECT ename, sal, 12* (sal+100) FROM emp;

✘

SELECT ename, sal, (12* sal)+100 FROM emp;

✘

SELECT ename, sal +100,*12 FROM emp;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You are the DBA for an academic database. You need to create a role that allows a group of users to modify
existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?

CREATE ROLL registrar; GRANT MODIFY ON student_grant TO registrar; GRANT registrar to user 1,
user2, user3

✘

CREATE NEW ROLE registrar; GRANT ALL ON student_grant TO registrar; GRANT registrar to user 1,user2, user3

✘

CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT ROLE to user1, user2,user3

✘

CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1,user2, user3;

✔

CREATE registrar; GRANT CHANGE ON student_grant TO registrar; GRANT registrar;

✔

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 4

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