Section 1
(Bu bölümdeki tüm soruları yanıtlayın)
1.The SQL SELECT statement is capable of:
Selection and protection
Selection and projection (*)
Projection and updating
None of the above
2. You want to create a list of all albums that have been produced by the company. The list should include the title of the album, the artist's name, and the date the album was released. The ALBUMS table includes the following columns:
ALB_TITLE VARCHAR2(150) NOT NULL
ALB_ARTIST VARCHAR2(150) NOT NULL
ALB_DATE DATE NOT NULL
Which statement can you use to retrieve the necessary information?
SELECT *
FROM albums;
(*)
SELECT alb_title, alb_artist, alb_dates
FROM albums;
SELECT alb_title; alb_artist; alb_date
FROM albums;
SELECT alb_title, alb_artist, alb_dates
FROM album;
3. What command can be used to create a new row in a table in the database?
CREATE
ADD
NEW
INSERT (*)
4. What command do you use to add rows to a table
INSERT (*)
ADD_ROW
ADD
NEW_ROW
5. Databases are used in most countries and by most governments. Life, as we know it, would change drastically if we no longer had access to databases. True or False? G
Doğru (*)
Yanlış
Section 2
(Bu bölümdeki tüm soruları yanıtlayın)
6. When using the LIKE condition, which symbol represents any sequence of characters of any length--zero, one, or more characters?
#
_
&
% (*)
7. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
WHERE (*)
SELECT
FROM
IS
8. Which of the following WHERE clauses would not select the number 10?
WHERE hours <>10 (*)
WHERE hours IN (8,9,10)
WHERE hours BETWEEN 10 AND 20
WHERE hours <= 10
9. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
WHERE email IS NOT NULL; (*)
WHERE email != NULL;
WHERE email IS NULL;
WHERE email = NULL;
10. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"?
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;
SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)
SELECT last_name||","|| job_id "Employee and Title" FROM employees;
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;
Section 2
(Bu bölümdeki tüm soruları yanıtlayın)
11. Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express?
The heading will display with the first character capitalized and centered.
The heading will display as uppercase and centered. (*
The heading will display as uppercase and left justified.
The heading will display with the first character capitalized and left justified.
Section 3
(Bu bölümdeki tüm soruları yanıtlayın)
12. Evaluate this SELECT statement:
SELECT last_name, first_name, email
FROM employees
ORDER BY email;
If the EMAIL column contains null values, which statement is true?
Null email values will be displayed first in the result.
Null email values will not be displayed in the result.
The result will not be sorted.
Null email values will be displayed last in the result. (*)
13. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? G
SELECT product_id, product_name
FROM products
HAVING price < 50;
SELECT product_id, product_name
FROM products
GROUP BY price < 50;
SELECT product_id, product_name
FROM products
WHERE price <= 50;
SELECT product_id, product_name
FROM products
WHERE price < 50.00
GROUP BY price;
SELECT product_id, product_name
FROM products
WHERE price < 50;
(*)
14. The following statement represents a multi-row function. True or False?
SELECT UPPER(last_name)
FROM employees;
Doğru
Yanlış (*)
15. Which comparison condition means "Less Than or Equal To"?
"+<"
"<=" (*)
"=)"
">="
Section 4
(Bu bölümdeki tüm soruları yanıtlayın)
16. Which comparison operator retrieves a list of values?
IN (*)
IS NULL
LIKE
BETWEEN IN
17. Which three statements about functions are true? (Choose three.)
(Tüm doğru yanıtları seçin)
The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length. (*)
The CONCAT function can only be used on character strings, not on numbers.
The ROUND number function rounds a value to a specified decimal place or the nearest whole number. (*)
The SYSDATE function returns the Oracle Server date and time. (*)
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?
SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
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;
19. Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
The current date plus 30 months.
The current date plus 30 days. (*)
The current date plus 30 hours.
No value is returned because the SELECT statement generates an error.
Section 5
(Bu bölümdeki tüm soruları yanıtlayın)
20. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Section 5
(Bu bölümdeki tüm soruları yanıtlayın)
21. Which of the following General Functions will return the first non-null expression in the expression list? Gö
NVL
NULLIF
COALESCE (*)
NVL2
22. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
23. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?
SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;
(*)
SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
24. You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 ).
Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
SELECT companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
25. Which functions allow you to perform explicit data type conversions?
ROUND, TRUNC, ADD_MONTHS
LENGTH, SUBSTR, LPAD, TRIM
NVL, NVL2, NULLIF
TO_CHAR, TO_DATE, TO_NUMBER (*)
Section 6
(Bu bölümdeki tüm soruları yanıtlayın)
26. Which statement about a self join is true?
The NATURAL JOIN clause must be used.
A self join must be implemented by defining a view.
Table aliases must be used to qualify table names. (*)
Table aliases cannot be used to qualify table names.
27. Which SELECT statement implements a self join?
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, managers m
WHERE e.employee_id = m.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
NATURAL JOIN employees m;
28. Which query will retrieve all the rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d USING (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
(*)
29. Which type of join returns rows from one table that have NO direct match in the other table?
Self join
Outer join (*)
Equijoin
Natural join
30. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
Doğru
Yanlış (*)
Section 6
(Bu bölümdeki tüm soruları yanıtlayın)
31. You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns?
Self-join
Cross join
Natural join (*)
Outer join
32. Evaluate this SELECT statement:
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?
ON (b.physician_id = c.physician_id); (*)
JOIN admission c
ON (a.patient_id = c.patient_id)
JOIN physician b
Section 7
(Bu bölümdeki tüm soruları yanıtlayın)
33. The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
Doğru
Yanlış (*)
34. What is the minimum number of join conditions required to join 5 tables together?
One more than the number of tables
3
4 (*)
5
35. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause?
1
2 (*)
3
0
Section 8
(Bu bölümdeki tüm soruları yanıtlayın)
36. Which group function would you use to display the highest salary value in the EMPLOYEES table?
MAX (*)
MIN
AVG
COUNT
37. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
0.2125 (*)
1.2125
0.0425
This statement is invalid
38. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
SUM = 1.85 and COUNT = 4
SUM = .85 and COUNT = 4 (*)
SUM = .85 and COUNT = 6
SUM = 1.85 and COUNT = 6
Section 9
(Bu bölümdeki tüm soruları yanıtlayın)
39. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The hire dates in the EMPLOYEES table that contain NULL values
The latest hire date in the EMPLOYEES table
The earliest hire date in each department (*)
The earliest hire date in the EMPLOYEES table
40. Which of the following SQL statements could display the number of people with the same last name:
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Section 9
(Bu bölümdeki tüm soruları yanıtlayın)
41. The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK
PLAYER_NAME VARCHAR2 (30)
TEAM_ID NUMBER
HIRE_DATE DATE
SALARY NUMBER (8,2)
Which clauses represent valid uses of aggregate functions? (Choose three.)
(Tüm doğru yanıtları seçin)
SELECT AVG(NVL(salary, 0)) (*)
ORDER BY AVG(salary) (*)
HAVING MAX(salary) > 10000 (*)
WHERE hire_date > AVG(hire_date)
GROUP BY MAX(salary)
42. Which of the following are correct SET operators? (choose two)
(Tüm doğru yanıtları seçin)
UNION ALL, INTERSECT (*)
MINUS, PLUS
UNION, MINUS (*)
UNION ALL, PLUS ALL
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?
ROLLUP
CUBE (*)
HAVING
GROUP BY ALL COLUMNS
44. You use GROUPING functions to:
Identify the extra row values created by either a ROLLUP or CUBE operation (*)
Produce subtotal and cross-tabulated values
Aggregate rows using SUM, MIN, MAX, and COUNT
Section 10
(Bu bölümdeki tüm soruları yanıtlayın)
45. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_ID NUMBER(5)
CLASS_ID NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID NUMBER (5) Primary Key
TEACHER_ID NUMBER (5)
DATE
MAX_CAPACITY NUMBER (3)
All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)
(Tüm doğru yanıtları seçin)
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
(*)
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
SELECT *
FROM teachers
WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);
SELECT *
FROM class_assignments
WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)
SELECT *
FROM class_assignments
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
Section 10
(Bu bölümdeki tüm soruları yanıtlayın)
46. A correlated subquery is evaluated _____ for each row processed by the parent statement.
COMPLETELY
ONCE (*)
EVERY TIME
47. If the subquery returns no rows, will the outer query return any values?
Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it.
No, because you are not allowed to return empty values from a subquery.
No, because the subquery will be treated like a null value. (*)
Yes. It will just run and ignore the subquery.
48. When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns:
All rows that were selected by the inner query minus the null values
No rows returned (*)
A list of Nulls
All rows that were selected by the inner query including the null values
49. Which operator or keyword cannot be used with a multiple-row subquery?
ANY
= (*)
>
ALL
50. Evaluate this SELECT statement:
SELECT player_id, name
FROM players
WHERE team_id IN
(SELECT team_id
FROM teams
WHERE team_id > 300 AND salary_cap > 400000);
What would happen if the inner query returned a NULL value?
All the rows in the PLAYER table would be returned by the outer query.
A syntax error in the outer query would be returned.
No rows would be returned by the outer query. (*)
A syntax error in the inner query would be returned.
(Bu bölümdeki tüm soruları yanıtlayın)
1.The SQL SELECT statement is capable of:
Selection and protection
Selection and projection (*)
Projection and updating
None of the above
2. You want to create a list of all albums that have been produced by the company. The list should include the title of the album, the artist's name, and the date the album was released. The ALBUMS table includes the following columns:
ALB_TITLE VARCHAR2(150) NOT NULL
ALB_ARTIST VARCHAR2(150) NOT NULL
ALB_DATE DATE NOT NULL
Which statement can you use to retrieve the necessary information?
SELECT *
FROM albums;
(*)
SELECT alb_title, alb_artist, alb_dates
FROM albums;
SELECT alb_title; alb_artist; alb_date
FROM albums;
SELECT alb_title, alb_artist, alb_dates
FROM album;
3. What command can be used to create a new row in a table in the database?
CREATE
ADD
NEW
INSERT (*)
4. What command do you use to add rows to a table
INSERT (*)
ADD_ROW
ADD
NEW_ROW
5. Databases are used in most countries and by most governments. Life, as we know it, would change drastically if we no longer had access to databases. True or False? G
Doğru (*)
Yanlış
Section 2
(Bu bölümdeki tüm soruları yanıtlayın)
6. When using the LIKE condition, which symbol represents any sequence of characters of any length--zero, one, or more characters?
#
_
&
% (*)
7. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
WHERE (*)
SELECT
FROM
IS
8. Which of the following WHERE clauses would not select the number 10?
WHERE hours <>10 (*)
WHERE hours IN (8,9,10)
WHERE hours BETWEEN 10 AND 20
WHERE hours <= 10
9. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
WHERE email IS NOT NULL; (*)
WHERE email != NULL;
WHERE email IS NULL;
WHERE email = NULL;
10. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"?
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;
SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)
SELECT last_name||","|| job_id "Employee and Title" FROM employees;
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;
Section 2
(Bu bölümdeki tüm soruları yanıtlayın)
11. Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express?
The heading will display with the first character capitalized and centered.
The heading will display as uppercase and centered. (*
The heading will display as uppercase and left justified.
The heading will display with the first character capitalized and left justified.
Section 3
(Bu bölümdeki tüm soruları yanıtlayın)
12. Evaluate this SELECT statement:
SELECT last_name, first_name, email
FROM employees
ORDER BY email;
If the EMAIL column contains null values, which statement is true?
Null email values will be displayed first in the result.
Null email values will not be displayed in the result.
The result will not be sorted.
Null email values will be displayed last in the result. (*)
13. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? G
SELECT product_id, product_name
FROM products
HAVING price < 50;
SELECT product_id, product_name
FROM products
GROUP BY price < 50;
SELECT product_id, product_name
FROM products
WHERE price <= 50;
SELECT product_id, product_name
FROM products
WHERE price < 50.00
GROUP BY price;
SELECT product_id, product_name
FROM products
WHERE price < 50;
(*)
14. The following statement represents a multi-row function. True or False?
SELECT UPPER(last_name)
FROM employees;
Doğru
Yanlış (*)
15. Which comparison condition means "Less Than or Equal To"?
"+<"
"<=" (*)
"=)"
">="
Section 4
(Bu bölümdeki tüm soruları yanıtlayın)
16. Which comparison operator retrieves a list of values?
IN (*)
IS NULL
LIKE
BETWEEN IN
17. Which three statements about functions are true? (Choose three.)
(Tüm doğru yanıtları seçin)
The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length. (*)
The CONCAT function can only be used on character strings, not on numbers.
The ROUND number function rounds a value to a specified decimal place or the nearest whole number. (*)
The SYSDATE function returns the Oracle Server date and time. (*)
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?
SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
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;
19. Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
The current date plus 30 months.
The current date plus 30 days. (*)
The current date plus 30 hours.
No value is returned because the SELECT statement generates an error.
Section 5
(Bu bölümdeki tüm soruları yanıtlayın)
20. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Section 5
(Bu bölümdeki tüm soruları yanıtlayın)
21. Which of the following General Functions will return the first non-null expression in the expression list? Gö
NVL
NULLIF
COALESCE (*)
NVL2
22. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
23. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?
SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;
(*)
SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
24. You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 ).
Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
SELECT companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
25. Which functions allow you to perform explicit data type conversions?
ROUND, TRUNC, ADD_MONTHS
LENGTH, SUBSTR, LPAD, TRIM
NVL, NVL2, NULLIF
TO_CHAR, TO_DATE, TO_NUMBER (*)
Section 6
(Bu bölümdeki tüm soruları yanıtlayın)
26. Which statement about a self join is true?
The NATURAL JOIN clause must be used.
A self join must be implemented by defining a view.
Table aliases must be used to qualify table names. (*)
Table aliases cannot be used to qualify table names.
27. Which SELECT statement implements a self join?
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, managers m
WHERE e.employee_id = m.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
NATURAL JOIN employees m;
28. Which query will retrieve all the rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d USING (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
(*)
29. Which type of join returns rows from one table that have NO direct match in the other table?
Self join
Outer join (*)
Equijoin
Natural join
30. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
Doğru
Yanlış (*)
Section 6
(Bu bölümdeki tüm soruları yanıtlayın)
31. You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns?
Self-join
Cross join
Natural join (*)
Outer join
32. Evaluate this SELECT statement:
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?
ON (b.physician_id = c.physician_id); (*)
JOIN admission c
ON (a.patient_id = c.patient_id)
JOIN physician b
Section 7
(Bu bölümdeki tüm soruları yanıtlayın)
33. The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
Doğru
Yanlış (*)
34. What is the minimum number of join conditions required to join 5 tables together?
One more than the number of tables
3
4 (*)
5
35. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause?
1
2 (*)
3
0
Section 8
(Bu bölümdeki tüm soruları yanıtlayın)
36. Which group function would you use to display the highest salary value in the EMPLOYEES table?
MAX (*)
MIN
AVG
COUNT
37. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
0.2125 (*)
1.2125
0.0425
This statement is invalid
38. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
SUM = 1.85 and COUNT = 4
SUM = .85 and COUNT = 4 (*)
SUM = .85 and COUNT = 6
SUM = 1.85 and COUNT = 6
Section 9
(Bu bölümdeki tüm soruları yanıtlayın)
39. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The hire dates in the EMPLOYEES table that contain NULL values
The latest hire date in the EMPLOYEES table
The earliest hire date in each department (*)
The earliest hire date in the EMPLOYEES table
40. Which of the following SQL statements could display the number of people with the same last name:
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Section 9
(Bu bölümdeki tüm soruları yanıtlayın)
41. The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK
PLAYER_NAME VARCHAR2 (30)
TEAM_ID NUMBER
HIRE_DATE DATE
SALARY NUMBER (8,2)
Which clauses represent valid uses of aggregate functions? (Choose three.)
(Tüm doğru yanıtları seçin)
SELECT AVG(NVL(salary, 0)) (*)
ORDER BY AVG(salary) (*)
HAVING MAX(salary) > 10000 (*)
WHERE hire_date > AVG(hire_date)
GROUP BY MAX(salary)
42. Which of the following are correct SET operators? (choose two)
(Tüm doğru yanıtları seçin)
UNION ALL, INTERSECT (*)
MINUS, PLUS
UNION, MINUS (*)
UNION ALL, PLUS ALL
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?
ROLLUP
CUBE (*)
HAVING
GROUP BY ALL COLUMNS
44. You use GROUPING functions to:
Identify the extra row values created by either a ROLLUP or CUBE operation (*)
Produce subtotal and cross-tabulated values
Aggregate rows using SUM, MIN, MAX, and COUNT
Section 10
(Bu bölümdeki tüm soruları yanıtlayın)
45. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_ID NUMBER(5)
CLASS_ID NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID NUMBER (5) Primary Key
TEACHER_ID NUMBER (5)
DATE
MAX_CAPACITY NUMBER (3)
All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)
(Tüm doğru yanıtları seçin)
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
(*)
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
SELECT *
FROM teachers
WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);
SELECT *
FROM class_assignments
WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)
SELECT *
FROM class_assignments
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
Section 10
(Bu bölümdeki tüm soruları yanıtlayın)
46. A correlated subquery is evaluated _____ for each row processed by the parent statement.
COMPLETELY
ONCE (*)
EVERY TIME
47. If the subquery returns no rows, will the outer query return any values?
Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it.
No, because you are not allowed to return empty values from a subquery.
No, because the subquery will be treated like a null value. (*)
Yes. It will just run and ignore the subquery.
48. When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns:
All rows that were selected by the inner query minus the null values
No rows returned (*)
A list of Nulls
All rows that were selected by the inner query including the null values
49. Which operator or keyword cannot be used with a multiple-row subquery?
ANY
= (*)
>
ALL
50. Evaluate this SELECT statement:
SELECT player_id, name
FROM players
WHERE team_id IN
(SELECT team_id
FROM teams
WHERE team_id > 300 AND salary_cap > 400000);
What would happen if the inner query returned a NULL value?
All the rows in the PLAYER table would be returned by the outer query.
A syntax error in the outer query would be returned.
No rows would be returned by the outer query. (*)
A syntax error in the inner query would be returned.
Yorumlar
Yorum Gönder