Database Programming with SQL Mid Semester 1 - Oracle iLearning

Section 1
(Answer all questions in this section)

1. There is only one kind of software used by all computers. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct.


2. What command can be used to create a new row in a table in the database? Mark for Review
(1) Points


INSERT (*)


NEW


CREATE


ADD



Correct Correct.


3. What is a NULL value? Mark for Review
(1) Points


A blank space


A known value less than zero


A perfect zero


An unknown value (*)



Correct Correct


4. In a SQL statement, which clause specifies one or more columns to be returned by the query? Mark for Review
(1) Points


SELECT (*)


FROM


WHERE


Any of the above options; you can list columns wherever you want to in a SELECT statement.



Correct Correct.


5. The basic storage structure in a Relational Database is a _________: Mark for Review
(1) Points


Table (*)


Row


Key


Field



Correct Correct
(Answer all questions in this section)

6. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct.


7. Which of the following WHERE clauses would not select the number 10? Mark for Review
(1) Points


WHERE hours IN (8,9,10)


WHERE hours <= 10


WHERE hours BETWEEN 10 AND 20


WHERE hours <>10 (*)



Correct Correct


8. If the EMPLOYEES table has the following columns, and you want to write a SELECT statement to return the employee last name and department number for employee number 176, which of the following SQL statements should you use?
Name Type Length
EMPLOYEE_ID NUMBER 22
FIRST_NAME VARCHAR2 20
LAST_NAME VARCHAR2 25
EMAIL VARCHAR2 25
PHONE_NUMBER VARCHAR2 20
SALARY NUMBER 22
COMMISSION_PCT NUMBER 22
MANAGER_ID NUMBER 22
DEPARTMENT_ID NUMBER 22
 Mark for Review
(1) Points


SELECT last_name, employee_id
FROM employees
WHERE employee_id equals 176;


SELECT last_name, department_id
FROM employees
WHERE employee_id equals 176;


SELECT first_name, employee_id
FROM employees
WHERE employee_id = 176;


SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
(*)




Correct Correct.


9. Which of the following are true? (Choose Two) Mark for Review
(1) Points

(Choose all correct answers)


Date values are enclosed in single quotation marks (*)


Character strings are enclosed in double quotation marks


Date values are format-sensitive (*)


Character values are not case-sensitive



Correct Correct


10. You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use? Mark for Review
(1) Points


ISNULL


"= NULL"


NULL!


IS NULL (*)



Correct Correct.
(Answer all questions in this section)

11. The structure of the table can be displayed with the _________ command: Mark for Review
(1) Points


Describe


Desc


Dis


Desc and the Describe (*)



Correct Correct




Section 3
(Answer all questions in this section)

12. Which of the following is true of the ORDER BY clause:? (Choose Two) Mark for Review
(1) Points

(Choose all correct answers)


Defaults to an ascending order (ASC) (*)


Must be the last clause of the SQL statement (*)


Displays the fetched rows in no particular order


Defaults to a descending order (DESC)



Correct Correct


13. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE salary > 30000
AND department_id = 10
OR email IS NOT NULL;

Which statement is true?

 Mark for Review
(1) Points


The OR condition will be evaluated before the AND condition.


The AND condition will be evaluated before the OR condition. (*)


The OR and AND conditions have the same precedence and will be evaluated from left to right


The OR and AND conditions have the same precedence and will be evaluated from right to left



Correct Correct.


14. Which of the following are TRUE regarding the logical AND operator? Mark for Review
(1) Points


TRUE AND FALSE return TRUE


TRUE AND FALSE return FALSE (*)


FALSE AND TRUE return NULL


TRUE AND TRUE return FALSE



Correct Correct.


15. The function COUNT is a single row function. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct
(Answer all questions in this section)

16. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL; Mark for Review
(1) Points


46


45.9


50 (*)


None of the above



Correct Correct


17. Which query would return a whole number if the sysdate is 26-May-2004? Mark for Review
(1) Points


SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'19-Mar-1979') /12)
AS YEARS
FROM DUAL;
(*)



SELECT TRUNC(YEARS_BETWEEN(SYSDATE,'19-Mar-1979') /12)
AS YEARS
FROM DUAL;


SELECT MONTHS_BETWEEN(SYSDATE,'19-Mar-1979') /12
AS YEARS
FROM DUAL;


None of the above



Correct Correct


18. The PRICE table contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604

You query the database and return the value 95. Which script did you use?

 Mark for Review
(1) Points


SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;


SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
(*)



SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;


SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;



Correct Correct


19. Evaluate this SELECT statement:
SELECT LENGTH(email)
FROM employee;

What will this SELECT statement display?

 Mark for Review
(1) Points


The email address of each employee in the EMPLOYEE table


The number of characters for each value in the EMAIL column in the employees table (*)


The longest e-mail address in the EMPLOYEE table


The maximum number of characters allowed in the EMAIL column



Correct Correct
Section 5
(Answer all questions in this section)

20. Which of the following is a conditional expression used in SQL? Mark for Review
(1) Points


CASE (*)


DESCRIBE


WHERE


NULLIF



Correct Correct

(Answer all questions in this section)

21. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
SALARY NUMBER(6)
You need to create a report to display the salaries of all employees. Which SQL Statement should you use to display the salaries in format: "$45,000.00"?

 Mark for Review
(1) Points


SELECT TO_CHAR(salary, '$999,999.00')
FROM employees;
(*)



SELECT TO_NUM(salary, '$999,990.99')
FROM employees;


SELECT TO_NUM(salary, '$999,999.00')
FROM employees;


SELECT TO_CHAR(salary, '$999,999')
FROM employees;



Correct Correct


22. Which statement concerning single row functions is true? Mark for Review
(1) Points


Single row functions return one or more results per row.


Single row functions cannot modify a data type.


Single row functions can accept only one argument, but can return multiple values.


Single row functions can be nested. (*)



Correct Correct


23. A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)

Will the folloiwng query work?

SELECT *
FROM employees
WHERE employee_id = manager_id; Mark for Review
(1) Points


Yes, Oracle will perform implicit dataype conversion, and the query will return one row of data.


No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.


No.ﾠ You will have to re-wirte the statement and perform explicit datatype conversion.


Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)



Correct Correct


24. Which function compares two expressions? Mark for Review
(1) Points


NVL2


NULLIF (*)


NULL


NVL



Correct Correct


25. With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
FROM employees ;

 Mark for Review
(1) Points


King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2


King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2


King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)



Statement will fail.



Correct Correct
(Answer all questions in this section)

26. Which type of join returns rows from one table that have NO direct match in the other table? Mark for Review
(1) Points


Equijoin


Self join


Outer join (*)


Natural join



Correct Correct


27. You need to display all the rows (both matching and non-matching) from both the EMPLOYEE and EMPLOYEE_HIST tables. Which type of join would you use? Mark for Review
(1) Points


A full outer join (*)


A left outer join


An inner join


A right outer join



Correct Correct


28. You can do nonequi-joins with ANSI-Syntax. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


29. Which SELECT statement implements a self join? Mark for Review
(1) Points


SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id =! t.product_id;


SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)



SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id;


SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id (+);



Correct Correct


30. Which SELECT statement implements a self join? Mark for Review
(1) Points


SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id;
(*)



SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;


SELECT e.employee_id, m.manager_id
FROM employees e, managers m
WHERE e.employee_id = m.manager_id;


SELECT e.employee_id, m.manager_id
FROM employees e
NATURAL JOIN employees m;



Correct Correct
Section 6
(Answer all questions in this section)

31. Which statement about a natural join is true? Mark for Review
(1) Points


Columns with the same names must have identical data types.


Columns with the same names must have the same precision and datatype. (*)


Columns with the same names cannot be included in the SELECT list of the query.


Columns with the same names must have compatible data types.



Correct Correct


32. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
(1) Points


If the columns having the same names have different data types, then an error is returned. (*)


When you attempt to write it as an equijoin.


If it selects rows from the two tables that have equal values in all matched columns.


When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.



Correct Correct




Section 7
(Answer all questions in this section)

33. The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)

SALES
SALES_ID NUMBER(5) PRIMARY KEY
CUST_ID NUMBER(10) FOREIGN KEY
TOTAL_SALES NUMBER(30)

Which SELECT statement will return the customer ID, the company and the total sales?

 Mark for Review
(1) Points


SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+);


SELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id;


SELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;


SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
(*)




Correct Correct


34. Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;

 Mark for Review
(1) Points


Yes, there are no syntax errors in that statement


No, Oracle will not allow joins in the WHERE clause


Yes, Oracle will resolve which department_id colum comes from which table.


No, Oracle will return a Column Ambiguously Defined error. (*)



Correct Correct


35. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)? Mark for Review
(1) Points


OR and =


BETWEEN...AND... and IN


AND and = (*)


IN and =



Correct Correct
Section 8
(Answer all questions in this section)

36. The TRUCKS table contains these columns:
TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)

Which SELECT statement will return the average price for the 4x4 model?

 Mark for Review
(1) Points


SELECT AVG(price), model
FROM trucks
WHERE model IS '4x4';


SELECT AVG(price)
FROM trucks
WHERE model IS '4x4';


SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)



SELECT AVG(price)
FROM trucks
WHERE model IS 4x4;



Correct Correct


37. Which group function would you use to display the average price of all products in the PRODUCTS table? Mark for Review
(1) Points


MAX


SUM


AVG (*)


COUNT



Correct Correct


38. Group functions can avoid computations involving duplicate values by including which keyword? Mark for Review
(1) Points


NULL


SELECT


DISTINCT (*)


UNLIKE



Correct Correct




Section 9
(Answer all questions in this section)

39. Evaluate this SELECT statement:
SELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;

Which clause of the SELECT statement contains a syntax error?

 Mark for Review
(1) Points


SELECT


GROUP BY (*)


WHERE


FROM



Correct Correct


40. The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)

TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25)

You need to create a report that lists the names of each team with more than three goal keepers.
Which SELECT statement will produce the desired result?

 Mark for Review
(1) Points


SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;


SELECT t.team_name, COUNT(p.player_id)
FROM players p
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
(*)



SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name;


SELECT t.team_name, COUNT(p.player_id)
FROM players
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
HAVING COUNT(p.player_id) > 3;
Section 9
(Answer all questions in this section)

41. Is the following statement correct?
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;

 Mark for Review
(1) Points


Yes, because Oracle will correct any mistakes in the statement itself


No, beause you cannot have a WHERE-clause when you use group functions.


No, because the statement is missing salary in the GROUP BY clause (*)


Yes



Correct Correct


42. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

What data will this query generate?

 Mark for Review
(1) Points


Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)


Sum of salaries for (department_id, job_id, manager_id)


Subtotals for (job_id, manager_id)


The statement will fail.



Correct Correct


43. If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause, you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points


ROLLUP


GROUP BY ALL COLUMNS


HAVING


CUBE (*)



Correct Correct


44. INTERSECT will give you the common rows found in both queries. (True or False?) Mark for Review
(1) Points


True (*)


False



Correct Correct




Section 10
(Answer all questions in this section)

45. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times. True or False? Mark for Review
(1) Points


True (*)


False
Section 10
(Answer all questions in this section)

46. Single row subqueries may not include this operator: Mark for Review
(1) Points


ALL (*)


=


>


<>



Correct Correct


47. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) = (SELECT department_id, job_id
     FROM employees
     WHERE employee_id = 103)

 Mark for Review
(1) Points


A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103. (*)


A list of last_names or salaries of employees that works in the same department and has the same job_id as that of employee 103.


A list of last_names and salaries of employees that works in the same department or has the same job_id as that of employee 103.


Nothing. It is an invalid statement.

48. Evaluate the structure of the EMPLOYEES and DEPART_HIST tables:
EMPLOYEES
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)

DEPART_HIST:
EMPLOYEE_ID NUMBER(9)
OLD_DEPT_ID NUMBER(9)
NEW_DEPT_ID NUMBER(9)
CHANGE_DATE DATE

You want to generate a list of employees who are in department 10, but used to be in department 15. Which query should you use?

 Mark for Review
(1) Points


SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) IN
    (SELECT employee_id, new_dept_id
     FROM depart_hist
     WHERE old_dept_id = 15) AND new_dept_id = 10;
(*)



SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) =
    (SELECT employee_id, new_dept_id
     FROM depart_hist
     WHERE new_dept_id = 15);


SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) IN
    (SELECT employee_id, dept_id
     FROM employees
     WHERE old_dept_id = 15);


SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id) IN
    (SELECT employee_id
     FROM employee_hist
     WHERE old_dept_id = 15);



Correct Correct


49. Which of the following best describes the meaning of the ANY operator? Mark for Review
(1) Points


Equal to any member in the list


Compare value to the first value returned by the subquery


Equal to each value in the list


Compare value to each value returned by the subquery (*)



Correct Correct


50. A multiple-row operator expects how many values? Mark for Review
(1) Points


One or more (*)


Only one


Two or more


None

Oracle Ilearning for Database Design

Pelajar yang menyelesaikan Desain & Pemrograman Database Oracle Academy dengan kursus SQL dapat sebagai gantinya (atau juga) memilih untuk mempersiapkan dan mengikuti ujian sertifikasi SQL Server SQL Expert. Namun, ujian Oracle Database SQL Expert berisi topik tambahan yang akan membutuhkan lebih banyak belajar mandiri dan berlatih sendiri untuk mempersiapkan.



Membutuhkan penyelesaian yang berhasil sebelum Ujian Sertifikasi Oracle Database SQL Expert, Pengantar Oracle Ujian Sertifikasi SQL, atau Oracle Database : Ujian Sertifikasi SQL Fundamentals.



Di atas adalah soal dan jawaban kuis oracle ilearning - oracle academy, sebagai penambah ilmu pengetahuan untuk kita semua, yang pada intinya jangan lupa untuk dipahami dan dikuasai.

Pembahasan didalam Oracle Ilearning - Oracle Academy

• Oracle Ilearning for Database Design


• Oracle Ilearning for Course Resources


• Oracle Ilearning for Introduction


• Oracle Ilearning for Entities and Attributes


• Oracle Ilearning for Relationship Basics


• Oracle Ilearning for Super/Sub Types and Business Rules


• Oracle Ilearning for Relationship Fundamentals


• Oracle Ilearning for UIDs and Normalization


• Oracle Ilearning for Arcs, Hierarchies, and Recursive Modeling


• Oracle Ilearning for Changes and Historical Modeling


• Oracle Ilearning for Mapping


• Oracle Ilearning for Creating Database Projects


• Oracle Ilearning for Presenting Database Projects


• Oracle Ilearning quiz answers for Database Design English Quizzes and Exams


• Oracle Ilearning quiz answers for Database Design Midterm Exam


• Oracle ilearning quiz answers for Database Design Final Exam

Selain Sebagai Penyedia Panduan Belajar Database dan Tutorial Pemrograman, Kami Juga Membagikan Kumpulan Source Code Program Aplikasi dan Ebook Pemrograman Terlengkap yang Bisa Anda Dapatkan Secara Gratis di Halaman :