The EMPLOYEES table 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;
Which is true about attributes of iSQL*Plus?
iSQL*Plus commands cannot be abbreviated.
iSQL*Plus commands are accesses from a browser.
iSQL*Plus commands are used to manipulate data in tables.
iSQL*Plus commands manipulate table definitions in the database.
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID NUMBER
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25)
DEPARTMENTS
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35)
LOCATION_ID NUMBER
You want to create a report displaying employee last names, department names, and locations. Which query should you use?
SELECT e.last_name, d. department_name, d.location_id
FROM employees e NATURAL JOIN departments D
USING department_id ;
SELECT last_name, department_name, location_id
FROM employees NATURAL JOIN departments
WHERE e.department_id =d.department_id;
SELECT e.last_name, d.department_name, d.location_id
FROM employees e NATURAL JOIN departments d;
SELECT e.last_name, d. department_name, d.location_id
FROM employees e NATURAL JOIN departments D
In which of the following case you would not use the USING clause?
You want to create a non equijoin.
The tables to be joined have multiple NULL columns.
The tables to be joined have columns with the same name and compatible data types.
You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;
Which statement is true regarding the default behavior of the ORDER BY clause?
Null values are displayed last for descending sequences.
Null values are left out of the sort.
Character values are displayed from Z to A.
Date values are displayed with the earliest value first.
What is true regarding sub queries?
The inner query always sorts the results of the outer query.
The outer query always sorts the results of the inner query.
The outer query must return a value to the inner query.
The inner query returns a value to the outer query.
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 form the HR table that belongs to your schema.
you get an error message because you cannot retrieve from a table that has the same as a public synonym
You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
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, update ON student_grades TO ROLE manager
GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;
GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION ;
Which syntax turns an existing constraint on?
ALTER TABLE table_name ENABLE constraint_name ;
ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint name ;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name ;
ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name ;
DATETIME data types can not be used when specifying which of the following column definitions?
TIMESTAMP
INTERVAL MONTH TO DAY
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS
Which / SQL* Plus feature can be used to replace values in the where clause?
Substitution variables
replacement variables
prompt variables
instead-of variables
For which constraint does the Oracle Server implicitly create a unique index?
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
before the WHERE clause
before the FROM clause
after the ORDER BY clause
after the WHERE clause
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?
MERGE
INSERT
UPDATE
ADD
Which statement accurately describes a role?
a user can have access to a maximum of 10 roles
A role can have a maximum of 100 privileges contained in it.
Privileges are given to a role by using the CREATE ROLE statement.
A role is a named group of related privileges that can be granted to the user