Oracle Introduction to Oracle9i SQL Set 1

Question - 15 Maximum mark : - 28

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

Examine the data in the EMPLOYEES and DEPARTMENTS tables.

EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
Bill 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration

You want to retrieve all employees, whether or not they have matching departments in the departments table.
Which query would you use?

SELECT last_name, department_nameFROM employees e LEFT OUTER
JOIN departments d ON (e. department _ id = d. department _id);

✘

SELECT last_name, department_nameFROM employees JOIN departments(+);

✘

SELECT last_name, department_nameON (e. department_ id = d. departments_id);
FROM employees(+) e JOIN departments d

✘

SELECT last_name, department_nameFROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

✔

SELECT last_name, department_nameFROM employees(+) , departments
ON (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.

You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the
STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to
others.
Which statement accomplishes this?

GRANT select, insert, update
ON student_grades
TO manager

✘

GRANT select, insert, updateON student_grades
TO ROLE manager

✘

GRANT select, insert, modifyON student_grades
TO manager
WITH GRANT OPTION;

✘

GRANT select, insert, updateON student_grades
TO manager
WITH GRANT OPTION;

✔

GRANT select, insert, updateON student_grades
TO ROLE manager
WITH GRANT OPTION;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

The database administrator of your company created a public synonym called HR for the
HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a
user of the database, you created a table called HR in your schema. What happens when you execute this
query?

SELECT *
FROM HR;

You obtain the results retrieved from the public synonym HR created by the database administrator. ✘ You obtain the results retrieved from the HR table that belongs to your schema. ✘ You get an error message because you cannot retrieve from a table that has the same name as a publicsynonym. ✘ You obtain the results retrieved from both the public synonym HR and the HR table that belongs to yourschema, as a Cartesian product. ✔ You obtain the results retrieved from both the public synonym HR and the HR table that belongs to yourschema, as a FULL JOIN. ✔

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 VARCHARD2(30)
SALARY NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?

SELECT dept_id, job_cat, MAX(salary)
FROM employees
WHERE salary > MAX (salary);

✘

SELECT dept_id, job_cat, MAX(salary)FROM employees
GROUP BY dept_id,job_cat;

✘

SELECT dept_id, job_cat, MAX(salary)FROM employees;

✘

SELECT dept_id, job_cat, MAX(salary)FROM employees
GROUP BY dept_id;

✔

SELECT dept_id, job_cat, MAX(salary)FROM employees
GROUP BY dept _ id job _ cat salary;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP
table. The EMP table contains these columns:

LAST NAME VARCNAR2(35) NOT NULL
SALARY NUMBER(9,2) NOT NULL
COMMISION_PCT NUMBER(4,2)

Which statement ensures that a value is displayed in the calculated columns for all employees?

SELECT last_name, 12*salary* commission_pct
FROM emp;

✘

SELECT last_name, 12*salary* (commission_pct,0)FROM emp;

✘

SELECT last_name, 12*salary*(nvl(commission_pct,0))FROM emp;

✘

SELECT last_name, 12*salary*(decode(commission_pct,0))FROM emp;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which syntax turns an existing constraint on?

ALTER TABLE table_name
ENABLE constraint_name;

✘

ALTER TABLE table_nameSTATUS = ENABLE CONSTRAINT constraint _ name;

✘

ALTER TABLE table_nameENABLE CONSTRAINT constraint _ name;

✘

ALTER TABLE table_nameSTATUS = ENABLE CONSTRAINT constraint _ name;

✔

ALTER TABLE table_nameTURN ON CONSTRAINT constraint _ name;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

The EMPLOYEE tables has these columns:

LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(5,2)

You want to display the name and annual salary multiplied by the commission_pct for all employees. For
records that have a NULL commission_pct, a zero must be displayed against the calculated column.
Which SQL statement displays the desired results?

SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;

✘

SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

✘

SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)FROM EMPLOYEES;

✘

SELECT last_name, (salary * 12) * NVL(commission_pct, 0)FROM EMPLOYEES;

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the data from the ORDERS and CUSTOMERS table.

ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAN-2000 15 10000
09-MAR-
101 40 8000
09-MAR-
102 35 12500
15-MAR-
103 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
04-AUG-
108 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the
same day that Martin places his orders?

SELECT ord_id, cust_id, ord_total
FROM orders, customers
WHERE cust_name='Mating'
AND ord _ date IN ('18-JUL-2000','21-JUL-2000');

✘

SELECT ord_id, cust_id, ord_totalFROM orders
Where ord_date IN (SELECT ord_date
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name =
'MARTIN'));

✘

SELECT ord_id, cust_id, ord_totalFROM orders
Where ord_date IN (SELECT ord_date
FROM orders, customers
Where cust _ name = 'Martin');

✘

SELECT ord_id, cust_id, ord_totalFROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE cust name = 'Martin');

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

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?

ALTER TABLE students
ADD PRIMARY KEY student_id;

✘

ALTER TABLE studentsADD CONSTRAINT PRIMARY KEY (student _ id);

✘

ALTER TABLE studentsADD CONSTRAINT stud_id_pk PRIMARY KEY (student _ id);

✘

ALTER TABLE studentsADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

✔

ALTER TABLE studentsMODIFY CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

Evaluate the SQL statement:

1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4. FROM employees
5 GROUP BY dept_id) b
6 WHERE a.dept_id = b.dept_id
7 AND a. asl < b. maxsal;

What is the result of the statement?

The statement produces an error at line 1. ✘ The statement produces an error at line 3. ✘ The statement produces an error at line 6. ✘ The statement returns the employee name, salary, department ID, and maximum salary earned in thedepartment of the employee for all departments that pay less salary then the maximum salary paid in the
company. ✔ The statement returns the employee name, salary, department ID, and maximum salary earned in thedepartment of the employee for all employees who earn less than the maximum salary in their department. ✔

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