Upwork Test Answers: Get all the correct answers of most recent and possible Upwork Tests A to Z (Updated on Jan, 2016)
Cover Letter Templates: These cover letter samples are not only for Upwork job, but also you will have some idea about your real life job
 
Freelance Profile Overviews: Different Profile samples and overviews of experts, advanced and intermediate level freelancers
For Newbie of Upwork: Upwork Help - How to apply for a job in Upwork with 10 most important articles about Upwork

A to Z View - All Upwork Test Answers

Upwork Oracle SQL 9i Test Answer

1. Which of the following constitute the correct guidelines for naming database tables?
 Answers: • Must be 1-30 characters long --or--- • Should not be an Oracle Server reserved word --or--- • Must contain only A-Z, a-z, 0-9, _, $, and # --or--- • Must begin with a letter


 2. Which of the following are DML statements?
 Answers: • MERGE ---- or ---- • UPDATE ---- or ---- • DELETE

 3. An outer join is used when:
 Answers: • The columns being joined have NULL values ---- or ---- • The tables being joined have both matched and unmatched data

 4. Which of the following components are required to run iSQL*plus on PC?
 Answers: • HTTP Server --- or --- • iSQL*PLUS Server

 5. Which of the following constitute the attributes of /SQL*Plus?
 Answers: • /SQL*Plus commands are accessed from a browser ----- or ----- • /SQL*Plus commands manipulate table definitions in the database

 6. Which component is a literal in the following select statement?
 Select 'Emp name :'||ename from emp where deptno=20;
 Answers: • 20 ---- or ---- • Emp name:

 7. Which of the following statements is a complete transaction?
 Answers: • ROLLBACK TO SAVEPOINT C; ---- or ----- • ALTER TABLE employees SET UNUSED COLUMN sal;

 8. Which of the following tasks can be performed by using the TO_CHAR function?
 Answers: • Convert 10 to '10' ---- or ---- • Convert a date to a character expression

 9. What kinds of commands can you enter at the command prompt in 9i Sql Plus?
 Answers: • PL/SQL Blocks ----- or ----- • SQL commands

 10. Which of the following statements are correct with regard to WHERE and HAVING clauses?
 Answers: • A WHERE clause can be used to restrict rows only --- or ---- • A HAVING clause can be used to restrict groups only

 11. Examine the description of the STUDENTS table:
 STD_ID NUMBER (4)
 COURSE_ID VARCHAR2 (10)
 START_DATE DATE
 END_DATE DATE


 The aggregate functions valid on the START_DATE columns are:
 Answers: • COUNT(start_date) ---- or ----- • MIN(start_date)

 12. 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?
 Answers: • SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); ----- OR ------- • SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

 13. 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?
 Answers: • SELECT last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;

====================================================

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?
Answers: • SELECT last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;

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?
Answers: • There will be no difference in the value displayed in the CALC_VALUE column

Which of the following is an iSQL*Plus command?
Answers: • DESCRIBE

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?
Answers: • It returns employees who have 50% commission rate or salary greater than $23,000:


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?
Answers: • The two statements produce identical results

What will happen if you query the emp table shown below:
select empno,DISTINCT ename,Salary from emp;
Answers: • No values will be displayed because the statement will return an error

How many join conditions should be there to avoid a Cartesion Join for joining three tables?
Answers: • 2

Which operator will be evaluated first in the statement:
select (2+3*4/2-8) from dual:
Answers: • *

Which of the following SQL statements returns a numeric value?
Answers: • SELECT sysdate-hire_date FROM EMP;

Which of the following substitution variables should be used to reuse the variable value without prompting the user each time?
Answers: • &&

Which of the following SELECT statements will get the result 'elloworld' from the string 'HelloWorld'?
Answers: • SELECT LOWER (TRIM ('H' FROM 'HelloWorld')) FROM dual;

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?
Answers: • SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5

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?
Answers: • 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 ;

Which of the following views should a user query to display the columns associated with the constraints on a table owned by the user?
Answers: • USER_CONS_COLUMNS

What does the TRUNCATE statement do?
Answers: • Removes all rows from a table

Top N analysis requires _____ and _____.
Answers: • an ORDER BY clause & An inline view and an outer query

Which data dictionary view holds information about the column in a view?
Answers: • USER_TAB_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"?
Answers: • SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;

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?
Answers: • 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 SQL statements accepts user input for the columns to be displayed, table name, and the WHERE condition?
Answers: • SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

Where is the GROUP BY clause statement placed in a SELECT statement that includes a WHERE clause?
Answers: • After the WHERE clause

Evaluate the SQL statement given below:

SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;

Which of the following values are displayed?
Answers: • 50 and 45.93

Which of the following data types stores data outside the Oracle database?
Answers: • BFILE

Which of the following statements are correct with regard to NULL values?
Answers: • In an ascending order sort, NULL values appear at the bottom of the result set

Which of the following operations cannot be performed using the ALTER TABLE statement?
Answers: • Rename column

Which of the following constraints can be defined only at the column level?
Answers: • NOT NULL

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
Answers: • CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Which of the following shows the correct use of the Trunc command on a date?
Answers: • SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " 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-:
Answers: • A left outer join

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?
Answers: • ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Which of the following SQL statements should be used to remove a view called EMP_DEPT_VU from the schema?
Answers: • DROP VIEW emp_dept_vu;

 _______ operator can be used with a multiple row subquery.
Answers: • NOT IN

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?

Answers: • SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;