Upwork Test Categories
What
kinds of commands can you enter at the command prompt in 9i Sql Plus?
a. PL/SQL
Blocks
b. SQL*Plus
commands
c. Security
commands
d. SQL
commands
Which
of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO
column of the EMP table?
a. 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);
b. CREATE
TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT
emp_deptno_fk REFERENCES dept (deptno));
c. CRETE
TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL,
CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
d. CREATE
TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));
You
want to list names of the employees who have been with the company for more
than five years. Which of the following SQL statements
will display the required results?
a. SELECT
ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5
b. SELECT
ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5
c. SELECT
ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5
d. SELECT
ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5
Examine
the data in the EMPLOYEES table given below:
LAST_NAME DEPARTMENT_ID
SALARY
ALLEN 10 3000
MILLER 20 1500
King 20 2200
Davis 30 5000
Which
of the following Subqueries work?
a. SELECT
* FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY
department_id);
b. SELECT
* FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY
department_id);
c. SELECT
distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary)
FROM employees GROUP BY department_id);
d. SELECT
department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM
employees GROUP BY department_id);
e. SELECT
department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM
employees GROUP BY AVG(SALARY));
Examine
the code given below:
SELECT employee_id FROM employees WHERE
commission_pct=.5 OR salary > 23000
Which
of the following statement is correct with regard to this code?
a. It
returns employees who have 50% of the salary greater than $23,000:
b. It
returns employees who have 50% commission rate or salary greater than $23,000:
c. It
returns employees who have 50% of salary less than $23,000:
d. None
of the above
Top
N analysis requires _____ and _____.
a. The
use of rowed & Only an inline view
b. a
GROUP BY clause & Only an inline view
c. an
ORDER BY clause & An inline view and an outer query
d. None
of the above
Which
data dictionary view holds information about the column in a view?
a. USER_VIEWS
b. USER_VIEW_COLUMNS
c. USER_TAB_COLUMNS
d. USER_ALL_COLUMNS
Which
of the following SELECT statements should be used to extract the year from the
system date to display it in the format "2001"?
a. SELECT
TO_CHAR(SYSDATE, 'yyyy') FROM dual;
b. SELECT
TO_DATE(SYSDATE, 'yyyy') FROM dual;
c. SELECT
DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
d. SELECT
DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
e. SELECT
TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;
Which
of the following SQL statements returns a numeric value?
a. SELECT
ADD_MONTHS(MAX (hire_date), 6) FROM EMP;
b. SELECT
ROUND(hire_date)FROM EMP;
c. SELECT
sysdate-hire_date FROM EMP;
d. SELECT
TO_NUMBER(hire_date + 7)FROM EMP;
Evaluate
the SQL statement given below:
SELECT ROUND (45.953, -1), TRUNC (45.936, 2)
FROM dual;
Which
of the following values are displayed?
a. 46
and 45.93
b. 50
and 45.93
c. 50
and 45.9
d. 45
and 45.93
e. 45.95
and 45.93
Which
of the following constitute the attributes of /SQL*Plus?
a. /SQL*Plus
commands cannot be abbreviated
b. /SQL*Plus
commands are accessed from a browser
c. /SQL*Plus
commands are used to manipulate data in tables
d. /SQL*Plus
commands manipulate table definitions in the database
e. /SQL*Plus
is the Oracle proprietary interface for executing SQL statements
Which
of the following views should a user query to display the columns associated
with the constraints on a table owned by the user?
a. USER_CONSTRAINTS
b. USER_OBJECTS
c. ALL_CONSTRAINTS
d. USER_CONS_COLUMNS
e. USER_COLUMNS
What
does the TRUNCATE statement do?
a. Removes
the table
b. Removes
all rows from a table
c. Shortens
the table to 10 rows
d. Removes
all columns from a table
Which
of the following statements is a complete transaction?
a. DELETE
employees;
b. DESCRIBE
employees;
c. ROLLBACK
TO SAVEPOINT C;
d. GRANT
SELECT ON employees TO SCOTT;
e. ALTER
TABLE employees SET UNUSED COLUMN sal;
Examine
the structure of the STUDENTS table given below:
STUDENT_ID
NUMBER NOT
NULL, Primary Key
STUDENT_NAME VARCHAR2
(30)
COURSE_ID VARCHAR2
(10) NOT
NULL
MARKS
NUMBER
START_DATE DATE
FINISH_DATE DATE
You
need to create a report of ten students who achieved the highest ranking in the
course INT_SQL and completed the course in the year 1999.
Which
of the following SQL statements accomplishes this task?
a. SELECT
student_id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id =
'INT_SQL'ORDER BY marks DESC;
b. SELECT
student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id =
'INT_SQL'ORDER BY marks;
c. SELECT
student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM
students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND
'31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);
d. SELECT
student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM
students WHERE finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id =
'INT_SQL' ORDER BY marks DESC)WHERE ROWNUM <= 10 ;
e. SELECT
student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM
students ORDER BY marks) WHERE ROWNUM <= 10 AND finish_date BETWEEN
'01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL';
You
need to modify the STUDENTS table to add a primary key on the STUDENT_ID
column. The table is currently empty.
Which
of the following statements will accomplish this task?
a. ALTER
TABLE students ADD PRIMARY KEY student_id;
b. ALTER
TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
c. ALTER
TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
d. ALTER
TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
e. ALTER
TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Which
operator will be evaluated first in the statement:
select (2+3*4/2-8) from dual:
a. +
b. -
c. /
d. *
Which
of the following SQL statements should be used to remove a view called
EMP_DEPT_VU from the schema?
a. DROP
emp_dept_vu;
b. DELETE
emp_dept_vu;
c. REMOVE
emp_dept_vu;
d. DROP
VIEW emp_dept_vu;
e. DELETE
VIEW emp_dept_vu;
f. REMOVE
VIEW emp_dept_vu;
___________
operator can be used with a multiple row subquery.
a. =
b. LIKE
c. BETWEEN
d. NOT
IN
e. Is
Which
of the following are DML statements?
a. COMMIT
b. MERGE
c. UPDATE
d. DELETE
e. CREATE
f. DROP
Which
of the following shows the correct use of the Trunc command on a date?
a. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date"
from Dual;
b. Select
TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;
c. Date
=TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;
d. SELECT
TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
You
have to calculate the value 12* salary* commission_pct for all the employees in
the EMP table. Which of the following statements ensures that a value is displayed
in the calculated column for all the employees?
a. SELECT
last_name, 12 * salary* commission_pct FROM emp;
b. SELECT
last_name, 12 * salary* (commission_pct,0) FROM emp;
c. SELECT
last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;
d. SELECT
last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;
Which
component is a literal in the following select statement?
Select 'Emp name :'||ename from emp where
deptno=20;
a. ename
b. 20
c. Emp
name:
d. ||
Which
of the following operations cannot be performed using the ALTER TABLE
statement?
a. Rename
table
b. Rename
column
c. Drop
column
d. Drop
NOT NULL Constraint
Which
of the following SQL statements accepts user input for the columns to be
displayed, table name, and the WHERE condition?
a. SELECT
&1, "&2"FROM &3 WHERE last_name = '&4';
b. SELECT
&1, '&2' FROM &3 WHERE '&last_name = '&4'';
c. SELECT
&1, &2 FROM &3 WHERE last_name = '&4';
d. SELECT
&1, '&2' FROM EMP WHERE last_name = '&4';
Which
of the following constitute the correct guidelines for naming database tables?
a. Must
begin with either a number or letter
b. Must
be 1-30 characters long
c. Should
not be an Oracle Server reserved word
d. Must
contain only A-Z, a-z, 0-9, _,*, and #
e. Must
contain only A-Z, a-z, 0-9, _, $, and #
f. Must
begin with a letter
An
outer join is used when:
a. The
tables being joined have NOT NULL columns
b. The
tables being joined have only matched data.
c. The
columns being joined have NULL values
d. The
tables being joined have only unmatched data
e. The
tables being joined have both matched and unmatched data
Which
of the following is an iSQL*Plus command?
a. INSERT
b. UPDATE
c. SELECT
d. DESCRIBE
e. DELETE
Which
of the following statements are correct with regard to WHERE and HAVING clauses?
a. A
WHERE clause can be used to restrict both rows and groups
b. A
WHERE clause can be used to restrict rows only
c. A
HAVING clause can be used to restrict both rows and groups
d. A
HAVING clause can be used to restrict groups only
e. A
HAVING clause CANNOT be used in Subqueries
Evaluate
the following SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 *
e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM
employees e, sales s WHERE e.employee_id = s.emp_id;
What
will happen if all the parentheses are removed from the calculation?
a. The
value displayed in the CALC_VALUE column will be lower
b. The
value displayed in the CALC_VALUE column will be higher
c. There
will be no difference in the value displayed in the CALC_VALUE column
d. An
error will be reported
Which
of the following SELECT statements will get the result 'elloworld' from the
string 'HelloWorld'?
a. SELECT
SUBSTR ('HelloWorld',1) FROM dual;
b. SELECT
INITCAP(TRIM('HellowWorld', 1,1) FROM dual;
c. SELECT
LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual;
d. SELECT
LOWER (TRIM ('H' FROM 'HelloWorld')) FROM dual;
Which
type of join will you write to perform
an outer join of tables A and B that returns all rows from B-:
a. Any
outer join
b. A
left outer join
c. A
cross join
d. A
right outer join
e. An
inner join
What
will happen if you query the emp table shown below:
select empno,DISTINCT ename,Salary from emp;
a. EMPNO
,unique value of ENAME and then SALARY are Displayed.
b. EMPNO
,unique value of the two columns, ENAME and salary are displayed.
c. DISTINCT
is not a valid keyword in SQL.
d. No
values will be displayed because the statement will return an error
You
want to display the titles of books that meet the following criteria:
1. Purchased before Feb 21, 2002
2. Price is less than $500 or greater than
$900
You
want to sort the result by the date of purchase, starting with the most
recently bought book.
Which
of the following statements should you use?
a. SELECT
book_title FROM books WHERE price between 500 and 900 AND purchase_date <
'21-FEB-2002' ORDER BY purchase_date;
b. SELECT
book_title FROM books WHERE price IN (500, 900) AND purchase_date <
'21-FEB-2002' ORDER BY purchase date ASC;
c. SELECT
book_title FROM books WHERE price < 500 OR > 900 AND purchase_date DESC;
d. SELECT
Book_title FROM books WHERE (price < 500 OR price > 900) AND
purchase_date < '21-FEB-2002' ORDER BY purchase_date DESC;
Which
of the following data types stores data outside the Oracle database?
a. UROWID
b. BFILE
c. BLOB
d. NCLOB
e. EXTERNAL
Which
of the following components are required to run iSQL*plus on PC?
a. SQL*PLUS
installed on the PC
b. HTTP
Server
c. Oracle
net on PC
d. iSQL*PLUS
Server
How
many join conditions should be there to avoid a Cartesion Join for joining
three tables?
a. 1
b. 2
c. 3
d. None
of the above
Examine
the two SQL statements given below:
SELECT last_name, salary, hire_date FROM
EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM
EMPLOYEES ORDER BY 2 DESC;
What
is true about them?
a. The
two statements produce identical results
b. The
second statement returns a syntax error while first will be executed
successfully
c. The
second statement will return top 2 records having maximum salary, while first
statement will return all records
d. There
is no need to specify DESC because the results are sorted in descending order
by default
The
STUDENT_GRADES table has these columns:
STUDENT_ID
NUMBER (12)
SEMESTER_END DATE
GPA
NUMBER (4, 3)
Which
of the following statements finds the highest Grade Point Average (GPA) per
semester?
a. SELECT
MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
b. SELECT
(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
c. SELECT
MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
d. SELECT
MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
e. SELECT
MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Which
of the following tasks can be performed by using the TO_CHAR function?
a. Convert
'10'to 10
b. Convert
10 to '10'
c. Convert
'TEN' to 10
d. Convert
a date to a character expression
e. Convert
a character expression to a date