· 6 years ago · Nov 07, 2019, 04:20 AM
1Section 2 Quiz
2 (Answer all questions in this section)
31. Which of the following are true? (Choose Two) Mark for Review
4(1) Points
5 (Choose all correct answers)
6 Character values are not case-sensitive
7 Date values are format-sensitive (*)
8 Date values are enclosed in single quotation marks (*)
9 Character strings are enclosed in double quotation marks
10
112. Which of the following would be returned by this SELECT statement:
12SELECT last_name, salary
13FROM employees
14WHERE salary < 3500;
15
16 Mark for Review
17(1) Points
18
19LAST_NAME SALARY
20King 5000
21
22LAST_NAME SALARY
23Rajas 3500
24
25LAST_NAME SALARY
26Davies 3100
27(*)
28
29All of the above
30
313. How can you write "not equal to" in the WHERE-clause? Mark for Review
32(1) Points
33 !=
34 ^=
35 <>
36 All of the above (*)
37
384. Which query would give the following result?
39LAST_NAME FIRST_NAME DEPARTMENT_ID
40King Steven 90
41 Mark for Review
42(1) Points
43
44 SELECT last_name, first_name, department_id
45FROM employees
46WHERE last_name LIKE 'k%';
47
48 SELECT last_name, first_name, department_id
49FROM employees
50WHERE last_name LIKE 'KING';
51
52 SELECT last_name, first_name, department_id
53FROM employees
54WHERE last_name = 'King';
55(*)
56
57 SELECT last_name, first_name, department_id
58FROM employees C
59WHERE last_name = 'KING';
60
615. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review
62(1) Points
63 SELECT DISTINCT manager_id, department_id FROM employees;
64 SELECT manager_id, DISTINCT department_id FROM employees;
65 SELECT manager_id, department_id FROM employees; (*)
66 SELECT manager_id, department_id DISTINCT FROM employees;
67
686. Where in a SQL statement can you not use arithmetic operators? Mark for Review
69(1) Points
70 WHERE
71 SELECT
72 NONE
73 FROM (*)
74
757. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10? Mark for Review
76(1) Points
77 WHERE (*)
78 FROM
79 SELECT
80 IS
81
828. The concatenation operator ... Mark for Review
83(1) Points
84 Brings together columns or character strings into other columns
85 Creates a resultant column that is a character expression
86 Is represented by two vertical bars ( || )
87 All of the above (*)
88
899. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
90(1) Points
91 LIKE
92 BETWEEN...AND... (*)
93 IN
94 IS NULL
95
9610. Which comparison condition would you use to select rows that match a character pattern? Mark for Review
97(1) Points
98 LIKE (*)
99 IN
100 ALMOST
101 SIMILAR
102
10311. Which of the following WHERE clauses would not select the number 10? Mark for Review
104(1) Points
105
106 WHERE hours <= 10
107 WHERE hours IN (8,9,10)
108 WHERE hours <>10 (*)
109 WHERE hours BETWEEN 10 AND 20
110
11112. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False? Mark for Review
112(1) Points
113 True (*)
114 False
115
11613. Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
117(1) Points
118 =, >, <, <=, >=, <>
119 between ___ and ___
120 in (..,..,.. )
121 like
122 is null
123 All of the above (*)
124
12514. 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
126(1) Points
127 True
128 False (*)
129
13015. The EMPLOYEES table contains these columns:
131LAST_NAME VARCHAR2(25)
132FIRST_NAME VARCHAR2(25)
133EMAIL VARCHAR2(50)
134
135You are writing a SELECT statement to retrieve the names of employees that have an email address.
136
137SELECT last_name||', '||first_name "Employee Name"
138FROM employees;
139
140Which WHERE clause should you use to complete this statement?
141
142 Mark for Review
143(1) Points
144 WHERE email != NULL;
145 WHERE email IS NOT NULL; (*)
146 WHERE email = NULL;
147 WHERE email IS NULL;
148
1491. You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for the DEPARTMENT_ID column. Which operator should you use in the WHERE clause to compare the DEPARTMENT_ID column to this specific list of values? Mark for Review
150(1) Points
151 BETWEEN..AND..
152 !=
153 =
154 IN (*)
155
1562. You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result? Mark for Review
157(1) Points
158 IN
159 BETWEEN
160 AND
161 LIKE (*)
162
1633. Evaluate this SELECT statement:
164SELECT *
165FROM employees
166WHERE department_id IN(10, 20, 30)
167AND salary > 20000;
168
169Which values would cause the logical condition to return TRUE?
170
171 Mark for Review
172(1) Points
173 DEPARTMENT_ID = 20 and SALARY = 20000
174 DEPARTMENT_ID = 10 and SALARY = 20000
175 DEPARTMENT_ID = 10 and SALARY = 20001 (*)
176 DEPARTMENT_ID = null and SALARY = 20001
177
1784. How can you write "not equal to" in the WHERE-clause? Mark for Review
179(1) Points
180 !=
181 ^=
182 <>
183 All of the above (*)
184
1855. Which of the following are true? (Choose Two) Mark for Review
186(1) Points
187 (Choose all correct answers)
188 Date values are enclosed in single quotation marks (*)
189 Character strings are enclosed in double quotation marks
190 Date values are format-sensitive (*)
191 Character values are not case-sensitive
192
1936. 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
194(1) Points
195 True
196 False (*)
197
1987. The EMPLOYEES table contains these columns:
199LAST_NAME VARCHAR2(25)
200FIRST_NAME VARCHAR2(25)
201EMAIL VARCHAR2(50)
202
203You are writing a SELECT statement to retrieve the names of employees that have an email address.
204
205SELECT last_name||', '||first_name "Employee Name"
206FROM employees;
207
208Which WHERE clause should you use to complete this statement?
209
210 Mark for Review
211(1) Points
212 WHERE email IS NOT NULL; (*)
213 WHERE email IS NULL;
214 WHERE email = NULL;
215 WHERE email != NULL;
216
2178. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False? Mark for Review
218(1) Points
219 True (*)
220 False
221
2229. Which of the following WHERE clauses would not select the number 10? Mark for Review
223(1) Points
224 WHERE hours <= 10
225 WHERE hours <>10 (*)
226 WHERE hours BETWEEN 10 AND 20
227 WHERE hours IN (8,9,10)
228
22910. Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500? (Choose two) Mark for Review
230(1) Points
231 (Choose all correct answers)
232 WHERE salary BETWEEN 3500 AND 2500
233 WHERE salary BETWEEN 2500 AND 3500 (*)
234 WHERE salary <=2500 AND salary >= 3500
235 WHERE salary >= 2500 AND salary <= 3500 (*)
236
23711. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use? Mark for Review
238(1) Points
239 "=>"
240 !=
241 >
242 >= (*)
243
24412. The concatenation operator ... Mark for Review
245(1) Points
246 Brings together columns or character strings into other columns
247 Creates a resultant column that is a character expression
248 Is represented by two vertical bars ( || )
249 All of the above (*)
250
25113. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
252(1) Points
253 BETWEEN...AND... (*)
254 IN
255 IS NULL
256 LIKE
257
25814. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause? Mark for Review
259(1) Points
260 DISTINCTROW
261 ONLY
262 UNIQUEONE
263 DISTINCT (*)
264
26515. You need to display employees whose salary is in the range of 10000 through 25000 for employees in department 50 . What does the WHERE clause look like? Mark for Review
266(1) Points
267 WHERE department_id < 50
268AND salary BETWEEN 10000 AND 25000
269
270 WHERE department_id = 50
271AND salary BETWEEN 25001 AND 10001
272
273 WHERE department_id = 50
274AND salary BETWEEN 10000 AND 25000
275(*)
276
277 WHERE department_id > 50
278
279AND salary BETWEEN 10000 AND 25000
280
281--------------
282Section 3 Quiz
283 (Answer all questions in this section)
284
2851. The PLAYERS table contains these columns:
286PLAYERS TABLE:
287LAST_NAME VARCHAR2 (20)
288FIRST_NAME VARCHAR2 (20)
289SALARY NUMBER(8,2)
290TEAM_ID NUMBER(4)
291MANAGER_ID NUMBER(9)
292POSITION_ID NUMBER(4)
293
294You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?
295
296 Mark for Review
297(1) Points
298
299 SELECT last_name, first_name, team_id, salary
300FROM players
301WHERE salary BETWEEN 24999.99 AND 100000.01
302AND team_id BETWEEN 1200 AND 1500
303ORDER BY team_id DESC, salary DESC;
304
305 SELECT last_name, first_name, team_id, salary
306FROM players
307WHERE salary BETWEEN 25000 AND 100000
308AND team_id BETWEEN 1200 AND 1500
309ORDER BY team_id, salary DESC;
310(*)
311
312 SELECT last_name, first_name, team_id, salary
313FROM players
314WHERE (salary > 25000 OR salary < 100000)
315AND team_id BETWEEN 1200 AND 1500
316ORDER BY team_id, salary;
317
318 SELECT last_name, first_name, team_id, salary
319FROM players
320WHERE salary > 24999.99 AND salary < 100000
321AND team_id BETWEEN 1200 AND 1500
322ORDER BY team_id ASC, salary DESC;
323
3242. Evaluate this SQL statement:
325SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
326FROM employees e, employees m
327ORDER BY e.last_name, e.first_name
328WHERE e.employee_id = m.manager_id;
329
330This statement fails when executed. Which change will correct the problem?
331
332 Mark for Review
333(1) Points
334 Remove the table aliases in the WHERE clause.
335 Reorder the clauses in the query. (*)
336 Include a HAVING clause.
337 Remove the table aliases in the ORDER BY clause.
338
3393. Will the following statement return one row?
340SELECT MAX(salary), MIN(Salary), AVG(SALARY)
341FROM employees;
342
343 Mark for Review
344(1) Points
345 No, it is illegal. You cannot use more than one multi-row function in a SELECT statement.
346 Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. (*)
347 Yes, it will return the highest salary from each employee.
348 Yes, it will return the average salary from the employees table.
349
3504. The function COUNT is a single row function. True or False? Mark for Review
351(1) Points
352 True
353 False (*)
354
3555. The EMPLOYEES table contains these columns:
356EMPLOYEE_ID NUMBER(9) PK
357LAST_NAME VARCHAR2(25)
358FIRST_NAME VARCHAR2(25)
359DEPARTMENT_ID NUMBER(9)
360Compare these two SQL statements:
361
3621.
363SELECT DISTINCT department_id DEPT, last_name, first_name
364FROM employees
365ORDER BY department_id;
366
3672.
368SELECT department_id DEPT, last_name, first_name
369FROM employees
370ORDER BY DEPT;
371
372How will the results differ?
373
374 Mark for Review
375(1) Points
376
377 One of the statements will return a syntax error.
378 One of the statements will eliminate all duplicate DEPARTMENT_ID values.
379 There is no difference in the result between the two statements.
380 The statements will sort on different column values. (*)
381
3826. Which columns can be added to the ORDER BY clause in the following SELECT statement? (Choose Three)
383SELECT first_name, last_name, salary, hire_date
384FROM employees
385WHERE department_id = 50
386ORDER BY ?????;
387
388 Mark for Review
389(1) Points
390
391 (Choose all correct answers)
392
393 last_name, first_name (*)
394 Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list (*)
395 All the columns in the database
396 All columns in the EMPLOYEES table (*)
397 The table name, EMPLOYEES, which would then automatically sort by all columns in the table
398
3997. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? Mark for Review
400(1) Points
401
402 SELECT product_id, product_name
403FROM products
404GROUP BY price < 50;
405
406 SELECT product_id, product_name
407FROM products
408HAVING price < 50;
409
410 SELECT product_id, product_name
411FROM products
412WHERE price < 50;
413(*)
414
415 SELECT product_id, product_name
416FROM products
417WHERE price < 50.00
418GROUP BY price;
419
420 SELECT product_id, product_name
421FROM products
422WHERE price <= 50;
423
4248. Evaluate this SQL statement:
425SELECT product_id, product_name, price
426FROM products
427ORDER BY product_name, price;
428
429What occurs when the statement is executed?
430
431 Mark for Review
432(1) Points
433
434 The results are sorted alphabetically and then numerically. (*)
435 The results are sorted numerically only.
436 The results are sorted numerically and then alphabetically.
437 The results are sorted alphabetically only.
438
4399. Evaluate this SELECT statement:
440SELECT last_name, first_name, salary
441FROM employees;
442
443How will the results of this query be sorted?
444
445 Mark for Review
446(1) Points
447
448 The results will be sorted ascending by LAST_NAME and FIRST_NAME only.
449 The database will display the rows in whatever order it finds it in the database, so no particular order. (*)
450 The results will be sorted ascending by the LAST_NAME column only.
451 The results will be sorted ascending by LAST_NAME, FIRST_NAME, and SALARY.
452
45310. Evaluate this SELECT statement:
454SELECT *
455FROM employees
456WHERE department_id = 34
457OR department_id = 45
458OR department_id = 67;
459
460Which operator is the equivalent of the OR conditions used in this SELECT statement?
461
462 Mark for Review
463(1) Points
464
465 IN (*)
466 AND
467 BETWEEN AND ...
468 LIKE
469
47011. From left to right, what is the correct order of Precedence? Mark for Review
471(1) Points
472
473 Arithmetic, Concatenation, Comparison, OR (*)
474 Arithmetic, NOT, Logical, Comparison
475 NOT, AND, OR, Arithmetic
476 Arithmetic, NOT, Concatenation, Logical
477
47812. What will be the results of the following selection?
479SELECT *
480FROM employees
481WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%'
482
483 Mark for Review
484(1) Points
485
486 No rows will be returned. There is a syntax error
487 All last names that do not begin with A or B (*)
488 All rows will be returned
489 All last names that begin with A or B
490
49113. The ORDER BY clause always comes last. True or False? Mark for Review
492(1) Points
493
494 True (*)
495 False
496
49714. Which symbol in the WHERE clause means "Not Equal To"? (Choose Two) Mark for Review
498(1) Points
499 (Choose all correct answers)
500 =+
501 <> (*)
502 ><
503 NOT IN (..) (*)
504
50515. Which comparison condition means "Less Than or Equal To"? Mark for Review
506(1) Points
507
508 ">="
509 "+<"
510 "<=" (*)
511 "=)"
512
5131. You attempt to query the database with this SQL statement:
514SELECT product_id "Product Number", category_id "Category", price "Price"
515FROM products
516WHERE "Category" = 5570
517ORDER BY "Product Number";
518
519This statement fails when executed. Which clause contains a syntax error?
520
521 Mark for Review
522(1) Points
523
524 WHERE "Category" = 5570 (*)
525 SELECT product_id "Product Number", category_id "Category", price "price"
526 FROM products
527 ORDER BY "Product Number";
528
5292. Which of the following is true of the ORDER BY clause:? (Choose Two) Mark for Review
530(1) Points
531 (Choose all correct answers)
532
533 Displays the fetched rows in no particular order
534 Must be the last clause of the SQL statement (*)
535 Defaults to a descending order (DESC)
536 Defaults to an ascending order (ASC) (*)
537
5383. Evaluate this SELECT statement:
539SELECT first_name, last_name, email
540FROM employees
541ORDER BY last_name;
542
543Which statement is true?
544
545 Mark for Review
546(1) Points
547
548 The rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME values
549 The rows will be sorted in reverse alphabetical order by the LAST_NAME values.
550 The rows will not be sorted.
551 The rows will be sorted alphabetically by the LAST_NAME values. (*)
552
5534. Evaluate this SELECT statement:
554SELECT last_name, first_name, email
555FROM employees
556ORDER BY email;
557
558If the EMAIL column contains null values, which statement is true?
559
560 Mark for Review
561(1) Points
562
563 Null email values will be displayed first in the result.
564 Null email values will be displayed last in the result. (*)
565 Null email values will not be displayed in the result.
566 The result will not be sorted.
567
5685. Which columns can be added to the ORDER BY clause in the following SELECT statement? (Choose Three)
569SELECT first_name, last_name, salary, hire_date
570FROM employees
571WHERE department_id = 50
572ORDER BY ?????;
573
574 Mark for Review
575(1) Points
576 (Choose all correct answers)
577 All columns in the EMPLOYEES table (*)
578 The table name, EMPLOYEES, which would then automatically sort by all columns in the table
579 Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list (*)
580 last_name, first_name (*)
581 All the columns in the database
582
5836. The following statement represents a multi-row function. True or False?
584SELECT MAX(salary)
585FROM employees
586
587 Mark for Review
588(1) Points
589
590 True (*)
591 False
592
5937. Evaluate this SQL statement:
594SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
595FROM employees e, employees m
596ORDER BY e.last_name, e.first_name
597WHERE e.employee_id = m.manager_id;
598
599This statement fails when executed. Which change will correct the problem?
600
601 Mark for Review
602(1) Points
603
604 Include a HAVING clause.
605 Remove the table aliases in the WHERE clause.
606 Reorder the clauses in the query. (*)
607 Remove the table aliases in the ORDER BY clause.
608
6098. The conversion function TO_CHAR is a single row function. True or False? Mark for Review
610(1) Points
611
612 True (*)
613 False
614
6159. The following statement represents a multi-row function. True or False?
616SELECT UPPER(last_name)
617FROM employees;
618
619 Mark for Review
620(1) Points
621
622 True
623 False (*)
624
62510. The function COUNT is a single row function. True or False? Mark for Review
626(1) Points
627
628 True
629 False (*)
630
63111. Which of the following statements best describes the rules of precedence when using SQL? Mark for Review
632(1) Points
633
634 The order in which the columns are displayed
635 The order in which the expressions are sorted
636 The order in which the operators are returned
637 The order in which the expressions are evaluated and calculated (*)
638 All of the above
639
64012. Which of the following best describes the meaning of the LIKE operator? Mark for Review
641(1) Points
642
643 Match a character pattern. (*)
644 To test for values in a list.
645 Display rows based on a range of values.
646 To find Null values.
647
64813. Which statement about the ORDER BY clause is true? Mark for Review
649(1) Points
650
651 You can use a column alias in the ORDER BY clause. (*)
652 The default sort order of the ORDER BY clause is descending.
653 The ORDER BY clause can only contain columns that are included in the SELECT list.
654 The ORDER BY clause should immediately precede the FROM clause in a SELECT statement
655
65614. From left to right, what is the correct order of Precedence? Mark for Review
657(1) Points
658
659 Arithmetic, NOT, Concatenation, Logical
660 Arithmetic, NOT, Logical, Comparison
661 Arithmetic, Concatenation, Comparison, OR (*)
662 NOT, AND, OR, Arithmetic
663
66415. Find the clause that will give the same results as:
665SELECT *
666FROM d_cds
667WHERE cd_id NOT IN(90, 91, 92);
668
669 Mark for Review
670(1) Points
671
672 WHERE cd_id <=90 and cd_id >=92;
673 WHERE cd_id NOT LIKE (90, 91, 92);
674 WHERE cd_id != 90 or cd_id != 91 or cd_id!= 92;
675 WHERE cd_id != 90 and cd_id != 91 and cd_id != 92; (*)
676
6771. The function COUNT is a single row function. True or False? Mark for Review
678(1) Points
679
680 True
681 False (*)
682
6832. The PLAYERS table contains these columns:
684PLAYERS TABLE:
685LAST_NAME VARCHAR2 (20)
686FIRST_NAME VARCHAR2 (20)
687SALARY NUMBER(8,2)
688TEAM_ID NUMBER(4)
689MANAGER_ID NUMBER(9)
690POSITION_ID NUMBER(4)
691
692You want to display all players' names with position 6900 or greater.
693You want the players names to be displayed alphabetically by last name and then by first name.
694Which statement should you use to achieve the required results?
695
696 Mark for Review
697(1) Points
698
699 SELECT last_name, first_name
700FROM players
701WHERE position_id > 6900
702ORDER BY last_name, first_name;
703
704 SELECT last_name, first_name
705FROM players
706WHERE position_id >= 6900
707ORDER BY last_name DESC, first_name;
708
709 SELECT last_name, first_name
710FROM players
711WHERE position_id >= 6900
712ORDER BY last_name, first_name;
713(*)
714
715 SELECT last_name, first_name
716FROM players
717WHERE position_id <= 6900
718ORDER BY last_name, first_name;
719
7203. The EMPLOYEES table contains these columns:
721EMPLOYEE_ID NUMBER(9) PK
722LAST_NAME VARCHAR2(25)
723FIRST_NAME VARCHAR2(25)
724DEPARTMENT_ID NUMBER(9)
725Compare these two SQL statements:
726
7271.
728SELECT DISTINCT department_id DEPT, last_name, first_name
729FROM employees
730ORDER BY department_id;
731
7322.
733SELECT department_id DEPT, last_name, first_name
734FROM employees
735ORDER BY DEPT;
736
737How will the results differ?
738
739 Mark for Review
740(1) Points
741
742 One of the statements will return a syntax error.
743 There is no difference in the result between the two statements.
744 One of the statements will eliminate all duplicate DEPARTMENT_ID values.
745 The statements will sort on different column values. (*)
746
7474. The following statement represents a multi-row function. True or False?
748SELECT MAX(salary)
749FROM employees
750
751 Mark for Review
752(1) Points
753
754 True (*)
755 False
756
7575. The PLAYERS table contains these columns:
758PLAYERS TABLE:
759LAST_NAME VARCHAR2 (20)
760FIRST_NAME VARCHAR2 (20)
761SALARY NUMBER(8,2)
762TEAM_ID NUMBER(4)
763MANAGER_ID NUMBER(9)
764POSITION_ID NUMBER(4)
765
766You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?
767
768 Mark for Review
769(1) Points
770
771 SELECT last_name, first_name, team_id, salary
772FROM players
773WHERE salary BETWEEN 25000 AND 100000
774AND team_id BETWEEN 1200 AND 1500
775ORDER BY team_id, salary DESC;
776(*)
777
778 SELECT last_name, first_name, team_id, salary
779FROM players
780WHERE (salary > 25000 OR salary < 100000)
781AND team_id BETWEEN 1200 AND 1500
782ORDER BY team_id, salary;
783
784 SELECT last_name, first_name, team_id, salary
785FROM players
786WHERE salary > 24999.99 AND salary < 100000
787AND team_id BETWEEN 1200 AND 1500
788ORDER BY team_id ASC, salary DESC;
789
790 SELECT last_name, first_name, team_id, salary
791FROM players
792WHERE salary BETWEEN 24999.99 AND 100000.01
793AND team_id BETWEEN 1200 AND 1500
794ORDER BY team_id DESC, salary DESC;
795
7966. Which statement about the ORDER BY clause is true? Mark for Review
797(1) Points
798
799 The ORDER BY clause can only contain columns that are included in the SELECT list.
800 You can use a column alias in the ORDER BY clause. (*)
801 The default sort order of the ORDER BY clause is descending.
802 The ORDER BY clause should immediately precede the FROM clause in a SELECT statement
803
8047. Which of the following best describes the meaning of the LIKE operator? Mark for Review
805(1) Points
806
807 To find Null values.
808 Match a character pattern. (*)
809 Display rows based on a range of values.
810 To test for values in a list.
811
8128. Which of the following are TRUE regarding the logical AND operator? Mark for Review
813(1) Points
814
815 TRUE AND FALSE return FALSE (*)
816 TRUE AND TRUE return FALSE
817 TRUE AND FALSE return TRUE
818 FALSE AND TRUE return NULL
819
8209. Which statement about the default sort order is true? Mark for Review
821(1) Points
822
823 Character values are displayed in reverse alphabetical order.
824 The lowest numeric values are displayed last.
825 The earliest date values are displayed first. (*)
826 Null values are displayed first.
827
82810. What will be the results of the following selection?
829SELECT *
830FROM employees
831WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%'
832
833 Mark for Review
834(1) Points
835
836 All last names that begin with A or B
837 All last names that do not begin with A or B (*)
838 No rows will be returned. There is a syntax error
839 All rows will be returned
840
84111. Evaluate this SELECT statement:
842SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
843FROM employees
844WHERE salary IS NOT NULL
845ORDER BY last_name, 3;
846
847Which clause contains an error?
848
849 Mark for Review
850(1) Points
851
852 FROM employees
853 SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)
854 WHERE salary IS NOT NULL
855 ORDER BY last_name, 3;
856
85712. Which of the following is true of the ORDER BY clause:? (Choose Two) Mark for Review
858(1) Points
859 (Choose all correct answers)
860
861 Defaults to an ascending order (ASC) (*)
862 Must be the last clause of the SQL statement (*)
863 Defaults to a descending order (DESC)
864 Displays the fetched rows in no particular order
865
86613. A column alias can be specified in an ORDER BY Clause. True or False? Mark for Review
867(1) Points
868
869 True (*)
870 False
871
87214. Evaluate this SELECT statement:
873SELECT last_name, first_name, email
874FROM employees
875ORDER BY email;
876
877If the EMAIL column contains null values, which statement is true?
878
879 Mark for Review
880(1) Points
881
882 Null email values will be displayed last in the result. (*)
883 The result will not be sorted.
884 Null email values will not be displayed in the result.
885 Null email values will be displayed first in the result.
886
88715. You attempt to query the database with this SQL statement:
888SELECT product_id "Product Number", category_id "Category", price "Price"
889FROM products
890WHERE "Category" = 5570
891ORDER BY "Product Number";
892
893This statement fails when executed. Which clause contains a syntax error?
894
895 Mark for Review
896(1) Points
897
898 WHERE "Category" = 5570 (*)
899 ORDER BY "Product Number";
900 FROM products
901
902 SELECT product_id "Product Number", category_id "Category", price "price"
903
904
905------------
906Section 4 Quiz
907 (Answer all questions in this section)
9081. A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by: Mark for Review
909(1) Points
910
911 Creating a message to be printed on every bill that reminds the customer to pay within ten days.
912
913 Making the relationship between CUSTOMER and PAYMENT fully mandatory and 1:1 on both sides.
914
915 Creating additional programming code to identify and report accounts past due. (*)
916
917 Making the payment attribute mandatory.
918
9192. A business rule such as "We only ship goods after customers have completely paid any outstanding balances on their account" is best enforced by: Mark for Review
920(1) Points
921
922 Creating additional programming code to verify no goods are shipped until the account has been settled in full. (*)
923 Making the payment attribute null.
924 We need to trust our customers, and we know they will pay some day.
925 Making the payment attribute optional.
926
9273. How should you handle constraints that cannot be modeled on an ER diagram? Mark for Review
928(1) Points
929 Always let the network architect handle them
930 Explain them to the users so they can enforce them
931 All constraints must be modeled and shown on the ER diagram
932 List them on a separate document to be handled programmatically (*)
933
9344. Which of the following is an example of a structural business rule? Mark for Review
935(1) Points
936 All employees must belong to at least one department. (*)
937 All products will have a selling price no less than 30 % greater than wholesale.
938 All overdue payments will have an added 10 % late fee.
939 Buildings to be purchased by the business must be current with earthquake building code.
940
9415. Can all constraints be modeled on an ER diagram? Mark for Review
942(1) Points
943
944 No, and those that cannot be modeled should be listed on a separate document to be handled programmatically (*)
945
946 No, in which case you should let the database administrator handle them
947 Yes, all constraints must be modeled and shown on the ER diagram
948 No, but you just explain them to the users so they can enforce them
949
9506. Why is it important to identify and document business rules? Mark for Review
951(1) Points
952 It allows you to create a complete data model and then check it for accuracy. (*)
953 It allows you to improve the client's business.
954 It ensures that the data model will automate all manual processes.
955 None of the above
956
9577. How would you model a business rule that states that girls and boys may not attend classes together? Mark for Review
958(1) Points
959
960 Supertype STUDENT has two subtypes BOY and GIRL which are related to GENDER, which is related to CLASS (*)
961 Use a supertype
962 Make the attribute Gender optional
963 Make the attribute Gender mandatory
964
9658. A subtype can have a relationship not shared by the supertype. True or False? Mark for Review
966(1) Points
967 True (*)
968 False
969
9709. A subtype is drawn on an ERD as an entity inside the "softbox" of the supertype. True or False? Mark for Review
971(1) Points
972 True (*)
973 False
974
97510. A subtype is shown on an ERD as an entity with a one to many relationship to the supertype. True or False? Mark for Review
976(1) Points
977 True
978 False (*)
979
98011. Which of the following is true about subtypes? Mark for Review
981(1) Points
982 One instance of a supertype may belong to two subtypes.
983 Subtypes should not be exhaustive.
984 Subtypes must be mutually exclusive. (*)
985 Subtypes must not be mutually exclusive.
986
98712. All instances of a subtype must be an instance of the supertype. True or False? Mark for Review
988(1) Points
989 True (*)
990 False
991
99213. A supertype can only have two subtypes and no more. True or False? Mark for Review
993(1) Points
994 True
995 False (*)
996
99714. You can only create relationships to a Supertype, not to a Subtype. True or False? Mark for Review
998(1) Points
999 True
1000 False (*)
1001
100215. A Supertype can have only one subtype. True or False? Mark for Review
1003(1) Points
1004 True
1005 False (*)
1006
10071. How would you model a business rule that states that on a student's birthday, he does not have to attend his classes? Mark for Review
1008(1) Points
1009 Use a supertype
1010 Make the attribute Birthdate mandatory
1011 You cannot model this. You need to document it (*)
1012 Use a subtype
1013
10142. Why is it important to identify and document business rules? Mark for Review
1015(1) Points
1016 It allows you to create a complete data model and then check it for accuracy. (*)
1017 It allows you to improve the client's business.
1018 It ensures that the data model will automate all manual processes.
1019 None of the above
1020
10213.A new system would have a mixture of both Procedural and Structural Business Rules as part of the documentation of that new system. True or False? Mark for Review
1022(1) Points
1023 True (*)
1024 False
1025
10264. A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by: Mark for Review
1027(1) Points
1028 Creating additional programming code to identify and report accounts past due. (*)
1029
1030 Making the relationship between CUSTOMER and PAYMENT fully mandatory and 1:1 on both sides.
1031 Making the payment attribute mandatory.
1032 Creating a message to be printed on every bill that reminds the customer to pay within ten days.
1033
10345. How should you handle constraints that cannot be modeled on an ER diagram? Mark for Review
1035(1) Points
1036 Always let the network architect handle them
1037 List them on a separate document to be handled programmatically (*)
1038 All constraints must be modeled and shown on the ER diagram
1039 Explain them to the users so they can enforce them
10406. Why is it important to identify and document structural rules? Mark for Review
1041(1) Points
1042 Ensures we know what data to store and how that data works together. (*)
1043
1044 Ensures nothing. There are no benefits to be gained from documenting your Structural Business Rules. We need to concentrate on the Procedural Business Rules only.
1045
1046 Ensures we know what processes are in place and how to program them.
1047
1048 All of the Above.
1049
10507. How would you model a business rule that states that girls and boys may not attend classes together? Mark for Review
1051(1) Points
1052
1053 Supertype STUDENT has two subtypes BOY and GIRL which are related to GENDER, which is related to CLASS (*)
1054 Make the attribute Gender mandatory
1055 Make the attribute Gender optional
1056 Use a supertype
1057
10588. A supertype can only have two subtypes and no more. True or False? Mark for Review
1059(1) Points
1060 True
1061 False (*)
1062
10639. All instances of the supertype must be an instance of one of the subtypes. True or False? Mark for Review
1064(1) Points
1065 True (*)
1066 False
1067
106810. A subtype is shown on an ERD as an entity with a one to many relationship to the supertype. True or False? Mark for Review
1069(1) Points
1070 True
1071 False (*)
1072
107311. Which of the following is true about subtypes? Mark for Review
1074(1) Points
1075 Subtypes should not be exhaustive.
1076 Subtypes must not be mutually exclusive.
1077 Subtypes must be mutually exclusive. (*)
1078 One instance of a supertype may belong to two subtypes.
1079
108012. You can only create relationships to a Supertype, not to a Subtype. True or False? Mark for Review
1081(1) Points
1082 True
1083 False (*)
1084
108513. A subtype can have a relationship not shared by the supertype. True or False? Mark for Review
1086(1) Points
1087 True (*)
1088 False
1089
109014. A Supertype can have only one subtype. True or False? Mark for Review
1091(1) Points
1092 True
1093 False (*)
1094
109515. All instances of a subtype must be an instance of the supertype. True or False? Mark for Review
1096(1) Points
1097 True (*)
1098 False
1099
11001. How would you model a business rule that states that girls and boys may not attend classes together? Mark for Review
1101(1) Points
1102 Use a supertype
1103 Supertype STUDENT has two subtypes BOY and GIRL which are related to GENDER, which is related to CLASS (*)
1104 Make the attribute Gender optional
1105 Make the attribute Gender mandatory
1106
11072. A business rule such as "We only ship goods after customers have completely paid any outstanding balances on their account" is best enforced by: Mark for Review
1108(1) Points
1109 Making the payment attribute null.
1110
1111 Creating additional programming code to verify no goods are shipped until the account has been settled in full. (*)
1112
1113 We need to trust our customers, and we know they will pay some day.
1114
1115 Making the payment attribute optional.
1116
11173. A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by: Mark for Review
1118(1) Points
1119
1120 Making the relationship between CUSTOMER and PAYMENT fully mandatory and 1:1 on both sides.
1121 Making the payment attribute mandatory.
1122 Creating a message to be printed on every bill that reminds the customer to pay within ten days.
1123 Creating additional programming code to identify and report accounts past due. (*)
1124
11254. Can all constraints be modeled on an ER diagram? Mark for Review
1126(1) Points
1127
1128 No, and those that cannot be modeled should be listed on a separate document to be handled programmatically (*)
1129
1130 No, in which case you should let the database administrator handle them
1131 Yes, all constraints must be modeled and shown on the ER diagram
1132 No, but you just explain them to the users so they can enforce them
1133
11345. How would you model a business rule that states that on a student's birthday, he does not have to attend his classes? Mark for Review
1135(1) Points
1136 Use a supertype
1137 You cannot model this. You need to document it (*)
1138 Make the attribute Birthdate mandatory
1139 Use a subtype
1140
11416. A new system would have a mixture of both Procedural and Structural Business Rules as part of the documentation of that new system. True or False? Mark for Review
1142(1) Points
1143 True (*)
1144 False
1145
11467. Why is it important to identify and document business rules? Mark for Review
1147(1) Points
1148 It allows you to create a complete data model and then check it for accuracy. (*)
1149 It allows you to improve the client's business.
1150 It ensures that the data model will automate all manual processes.
1151 None of the above
1152
11538. All instances of a subtype may be an instance of the supertype but does not have to. True or False? Mark for Review
1154(1) Points
1155 True
1156 False (*)
1157
11589. A subtype can have a relationship not shared by the supertype. True or False? Mark for Review
1159(1) Points
1160 True (*)
1161 False
1162
116310. A subtype is shown on an ERD as an entity with a one to many relationship to the supertype. True or False? Mark for Review
1164(1) Points
1165 True
1166 False (*)
1167
116811. You can only create relationships to a Supertype, not to a Subtype. True or False? Mark for Review
1169(1) Points
1170 True
1171 False (*)
1172
117312. All ER diagrams must have one of each of the following: (Choose two) Mark for Review
1174(1) Points
1175 (Choose all correct answers)
1176 At least one supertype and subtype
1177 One or more Entities (*)
1178 Relationships between entities (*)
1179 Arcs
1180
118113. Which of the following is true about subtypes? Mark for Review
1182(1) Points
1183 Subtypes should not be exhaustive.
1184 Subtypes must be mutually exclusive. (*)
1185 Subtypes must not be mutually exclusive.
1186 One instance of a supertype may belong to two subtypes.
1187
118814. A Supertype can have only one subtype. True or False? Mark for Review
1189(1) Points
1190 True
1191 False (*)
1192
119315. All instances of the supertype must be an instance of one of the subtypes. True or False? Mark for Review
1194(1) Points
1195 True (*)
1196
1197 False
1198
1199=-----------
1200
1201Section 5 Quiz
1202 (Answer all questions in this section)
1203
12041. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review
1205(1) Points
1206
1207 True (*)
1208 False
1209
12102. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
1211DATA:( King, null
1212Kochhar, 100
1213De Haan, 100
1214Hunold, 102
1215Ernst, 103)
1216SELECT last_name,
1217DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
1218FROM employees
1219
1220 Mark for Review
1221(1) Points
1222
1223 King, A N Other
1224Kochhar, King
1225De Haan, King
1226Hunold, A N Other
1227Ernst, A N Other
1228(*)
1229
1230 King, A N Other
1231Kochhar, King
1232De Haan, King
1233Hunold, Kochhar
1234Ernst, De Haan
1235
1236 Invalid statement.
1237
1238 King, Null
1239Kochhar, King
1240De Haan, King
1241Hunold, A N Other
1242Ernst, A N Other
1243
12443. Which of the following is a conditional expression used in SQL? Mark for Review
1245(1) Points
1246
1247 NULLIF
1248 WHERE
1249 CASE (*)
1250 DESCRIBE
1251
12524. A table has the following definition: EMPLOYEES(
1253EMPLOYEE_ID NUMBER(6) NOT NULL,
1254NAME VARCHAR2(20) NOT NULL,
1255MANAGER_ID VARCHAR2(6))
1256
1257and contains the following rows:
1258
1259(1001, 'Bob Bevan', '200')
1260(200, 'Natacha Hansen', null)
1261
1262Will the folloiwng query work?
1263
1264SELECT *
1265FROM employees
1266WHERE employee_id = manager_id; Mark for Review
1267(1) Points
1268
1269 Yes, Oracle will perform implicit dataype conversion, and the query will return one row of data.
1270
1271 Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)
1272
1273 No.ﾠ You will have to re-wirte the statement and perform explicit datatype conversion.
1274
1275 No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.
1276
12775. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result? Mark for Review
1278(1) Points
1279
1280 1901
1281 2017 (*)
1282 2001
1283 1917
1284
12856. Which arithmetic operation will return a numeric value? Mark for Review
1286(1) Points
1287
1288 SYSDATE + 30 / 24
1289 NEXT_DAY(hire_date) + 5
1290 SYSDATE - 6
1291 TO_DATE('01-Jun-2004') - TO_DATE('01-Oct-2004') (*)
1292
12937. Which SQL Statement should you use to display the prices in this format: "$00.30"? Mark for Review
1294(1) Points
1295
1296 SELECT TO_CHAR(price, '$99,900.99')
1297FROM product;
1298(*)
1299
1300 SELECT TO_NUMBER(price, '$99,900.99')
1301FROM product;
1302
1303 SELECT TO_CHAR(price, '$99,990.99')
1304FROM product;
1305
1306 SELECT TO_CHAR(price, '$99,999.99')
1307FROM product;
1308
13098. Which two statements concerning SQL functions are true? (Choose two.) Mark for Review
1310(1) Points
1311 (Choose all correct answers)
1312 Single-row functions manipulate groups of rows to return one result per group of rows.
1313 Conversion functions convert a value from one data type to another data type. (*)
1314 Not all date functions return date values. (*)
1315 Character functions can accept numeric input.
1316 Number functions can return number or character values.
1317
13189. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use? Mark for Review
1319(1) Points
1320
1321 SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
1322FROM employees;
1323
1324 SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
1325FROM employees;
1326
1327 SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
1328FROM employees;
1329(*)
1330
1331 SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
1332FROM employees;
1333
133410. Which function compares two expressions? Mark for Review
1335(1) Points
1336
1337 NVL
1338 NVL2
1339 NULL
1340 NULLIF (*)
1341
134211. Which of the following General Functions will return the first non-null expression in the expression list? Mark for Review
1343(1) Points
1344
1345 NULLIF
1346 COALESCE (*)
1347 NVL2
1348 NVL
1349
135012. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review
1351(1) Points
1352
1353 SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
1354FROM student_accounts;
1355(*)
1356
1357 SELECT tuition_balance + housing_balance
1358FROM student_accounts;
1359
1360 SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
1361FROM student_accounts;
1362
1363 SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
1364FROM student_accounts;
1365
136613. Which statement about group functions is true? Mark for Review
1367(1) Points
1368
1369 NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
1370 COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
1371 NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
1372 NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
1373
137414. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
1375DATA:
1376King, null, null
1377Kochhar, null, 100
1378Vargas, null, 124
1379Zlotkey, .2, 100
1380What is the result of the following statement:
1381SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
1382FROM employees ;
1383
1384 Mark for Review
1385(1) Points
1386
1387 King, null
1388Kochhar, 100
1389Vargas, 124
1390Zlotkey, .2
1391
1392 King, -1
1393Kochhar, 100
1394Vargas, 124
1395Zlotkey, .2
1396(*)
1397
1398 Statement will fail
1399
1400 King, -1
1401Kochhar, 100
1402Vargas, 124
1403Zlotkey, 100
1404
140515. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use? Mark for Review
1406(1) Points
1407
1408 TO_NUMBER and NULLIF
1409 TO_CHAR and NULLIF
1410 TO_CHAR and NULL
1411 TO_CHAR and NVL (*)
1412
14131. All Human Resources data is stored in a table named EMPLOYEES. You have been asked to create a report that displays each employee's name and salary. Each employee's salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result? Mark for Review
1414(1) Points
1415
1416 TO_DATE
1417 TO_CHAR (*)
1418 TO_NUMBER
1419 CHARTOROWID
1420
14212. The EMPLOYEES table contains these columns:
1422EMPLOYEE_ID NUMBER(9)
1423LAST_NAME VARCHAR2 (25)
1424FIRST_NAME VARCHAR2 (25)
1425HIRE_DATE DATE
1426
1427You need to display HIRE_DATE values in this format:
1428
1429January 28, 2000
1430
1431Which SQL statement could you use?
1432
1433 Mark for Review
1434(1) Points
1435
1436 SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
1437FROM employees;
1438
1439 SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
1440FROM employees;
1441(*)
1442
1443 SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
1444FROM employees;
1445
1446 SELECT TO_CHAR(hire_date, Month DD, YYYY)
1447FROM employees;
1448
14493. A table has the following definition: EMPLOYEES(
1450EMPLOYEE_ID NUMBER(6) NOT NULL,
1451NAME VARCHAR2(20) NOT NULL,
1452MANAGER_ID VARCHAR2(6))
1453
1454and contains the following rows:
1455
1456(1001, 'Bob Bevan', '200')
1457(200, 'Natacha Hansen', null)
1458
1459Will the folloiwng query work?
1460
1461SELECT *
1462FROM employees
1463WHERE employee_id = manager_id; Mark for Review
1464(1) Points
1465
1466 Yes, Oracle will perform implicit dataype conversion, and the query will return one row of data.
1467
1468 No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.
1469
1470 Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)
1471
1472 No.ﾠ You will have to re-wirte the statement and perform explicit datatype conversion.
1473
14744. Which statement is true about SQL functions? Mark for Review
1475(1) Points
1476
1477 Functions can convert values or text to another data type.
1478 Functions can round a number to a specified decimal place.
1479 Functions can convert upper case characters to lower case characters.
1480 a, b and c are true. (*)
1481 None of the above statements are true.
1482
14835. The following script will run successfully. True or False?
1484SELECT TO_CHAR(TO_DATE('25-Dec-2004','dd-Mon-yyyy'))
1485FROM dual Mark for Review
1486(1) Points
1487
1488 True (*)
1489 False
1490
14916. Which three statements concerning explicit data type conversions are true? (Choose three.) Mark for Review
1492(1) Points
1493 (Choose all correct answers)
1494
1495 Use the TO_NUMBER function to convert a character string of digits to a number. (*)
1496 Use the TO_CHAR function to convert a number or date value to a character string. (*)
1497 Use the TO_DATE function to convert a character string to a date value. (*)
1498 Use the TO_DATE function to convert a date value to a character string or number.
1499 Use the TO_NUMBER function to convert a number to a character string.
1500
15017. Which function compares two expressions? Mark for Review
1502(1) Points
1503
1504 NULL
1505 NVL2
1506 NVL
1507 NULLIF (*)
1508
15098. The following statement returns 0 (zero). True or False?
1510SELECT 121/NULL
1511FROM dual; Mark for Review
1512(1) Points
1513
1514 True
1515 False (*)
1516
15179. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review
1518(1) Points
1519
1520 SELECT tuition_balance + housing_balance
1521FROM student_accounts;
1522
1523 SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
1524FROM student_accounts;
1525
1526 SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
1527FROM student_accounts;
1528
1529 SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
1530FROM student_accounts;
1531(*)
1532
153310. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use? Mark for Review
1534(1) Points
1535
1536 TO_NUMBER and NULLIF
1537 TO_CHAR and NULLIF
1538 TO_CHAR and NULL
1539 TO_CHAR and NVL (*)
1540
154111. Which statement about group functions is true? Mark for Review
1542(1) Points
1543 NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
1544 NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
1545 NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
1546 COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
1547
154812. With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
1549DATA:
1550King, null, null
1551Kochhar, null, 100
1552Vargas, null, 124
1553Zlotkey, .2, 100
1554SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
1555FROM employees ;
1556
1557 Mark for Review
1558(1) Points
1559
1560 King, -1
1561Kochhar, -1
1562Vargas, -1
1563Zlotkey, 100
1564(*)
1565
1566 Statement will fail.
1567
1568 King, -1
1569Kochhar, -1
1570Vargas, -1
1571Zlotkey, .2
1572
1573 King, -1
1574Kochhar, 100
1575Vargas, 124
1576Zlotkey, .2
1577
157813. Which statement will return a listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value? Mark for Review
1579(1) Points
1580
1581 SELECT last_name,salary,
1582(CASE WHEN salary<5000 o:p="" ow="" then="">
1583 WHEN salary<10000 edium="" o:p="" then="">
1584 WHEN salary<20000 o:p="" ood="" then="">
1585 ELSE 'Excellent'
1586END) qualified_salary
1587FROM employees;
1588(*)
1589
1590 SELECT last_name,sal,
1591(CASE WHEN sal<5000 o:p="" ow="" then="">
1592 WHEN sal<10000 edium="" o:p="" then="">
1593 WHEN sal<20000 o:p="" ood="" then="">
1594 ELSE 'Excellent'
1595END) qualified_salary
1596FROM employees;
1597
1598 SELECT last_name,salary,
1599(RATING WHEN salary<5000 o:p="" ow="" then="">
1600 WHEN salary<10000 edium="" o:p="" then="">
1601 WHEN salary<20000 o:p="" ood="" then="">
1602 ELSE 'Excellent'
1603END) qualified_salary
1604FROM employees;
1605
1606 SELECT last_name,salary,
1607(CASE WHEN salary<5000 o:p="" ow="" then="">
1608 WHEN sal <10000 edium="" o:p="" then="">
1609 WHEN sal <20000 o:p="" ood="" then="">
1610 ELSE 'Excellent'
1611END) qualified_salary
1612FROM employees;
1613
161414. Which of the following is a conditional expression used in SQL? Mark for Review
1615(1) Points
1616
1617 CASE (*)
1618 NULLIF
1619 DESCRIBE
1620 WHERE
1621
162215. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
1623DATA:( King, null
1624Kochhar, 100
1625De Haan, 100
1626Hunold, 102
1627Ernst, 103)
1628SELECT last_name,
1629DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
1630FROM employees
1631
1632 Mark for Review
1633(1) Points
1634
1635 King, A N Other
1636Kochhar, King
1637De Haan, King
1638Hunold, A N Other
1639Ernst, A N Other
1640(*)
1641
1642 Invalid statement.
1643
1644 King, Null
1645Kochhar, King
1646De Haan, King
1647Hunold, A N Other
1648Ernst, A N Other
1649
1650 King, A N Other
1651Kochhar, King
1652De Haan, King
1653Hunold, Kochhar
1654Ernst, De Haan
1655
16561. Sysdate is 12-May-2004.
1657You need to store the following date: 7-Dec-89
1658Which statement about the date format for this value is true? Mark for Review
1659(1) Points
1660
1661 The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)
1662
1663 Both the YY and RR date formats will interpret the year as 2089
1664
1665 The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989
1666
1667 Both the YY and RR date formats will interpret the year as 1989
1668
16692. Which functions allow you to perform explicit data type conversions? Mark for Review
1670(1) Points
1671
1672 TO_CHAR, TO_DATE, TO_NUMBER (*)
1673 LENGTH, SUBSTR, LPAD, TRIM
1674 NVL, NVL2, NULLIF
1675 ROUND, TRUNC, ADD_MONTHS
1676
16773. Which statement will return the salary (for example, the salary of 6000) from the Employees table in the following format? $6000.00 Mark for Review
1678(1) Points
1679
1680 SELECT TO_CHAR(sal, '$99999.00') SALARY
1681FROM employees
1682
1683 SELECT TO_CHAR(salary, '$99999') SALARY
1684FROM employees
1685
1686 SELECT TO_CHAR(salary, '99999.00') SALARY
1687FROM employees
1688
1689 SELECT TO_CHAR(salary, '$99999.00') SALARY
1690FROM employees
1691(*)
1692
16934. The EMPLOYEES table contains these columns:
1694EMPLOYEE_ID NUMBER(9)
1695LAST_NAME VARCHAR2 (25)
1696FIRST_NAME VARCHAR2 (25)
1697HIRE_DATE DATE
1698
1699You need to display HIRE_DATE values in this format:
1700
1701January 28, 2000
1702
1703Which SQL statement could you use?
1704
1705 Mark for Review
1706(1) Points
1707
1708 SELECT TO_CHAR(hire_date, Month DD, YYYY)
1709FROM employees;
1710
1711 SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
1712FROM employees;
1713
1714 SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
1715FROM employees;
1716(*)
1717
1718 SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
1719FROM employees;
1720
17215. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result? Mark for Review
1722(1) Points
1723
1724 2017 (*)
1725 1901
1726 1917
1727 2001
1728
17296. Which three statements concerning explicit data type conversions are true? (Choose three.) Mark for Review
1730(1) Points
1731
1732 (Choose all correct answers)
1733
1734 Use the TO_DATE function to convert a character string to a date value. (*)
1735 Use the TO_CHAR function to convert a number or date value to a character string. (*)
1736 Use the TO_DATE function to convert a date value to a character string or number.
1737 Use the TO_NUMBER function to convert a character string of digits to a number. (*)
1738 Use the TO_NUMBER function to convert a number to a character string.
1739
17407. Which statement will return a listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value? Mark for Review
1741(1) Points
1742
1743 SELECT last_name,salary,
1744(CASE WHEN salary<5000 o:p="" ow="" then="">
1745 WHEN salary<10000 edium="" o:p="" then="">
1746 WHEN salary<20000 o:p="" ood="" then="">
1747 ELSE 'Excellent'
1748END) qualified_salary
1749FROM employees;
1750(*)
1751
1752 SELECT last_name,salary,
1753(RATING WHEN salary<5000 o:p="" ow="" then="">
1754 WHEN salary<10000 edium="" o:p="" then="">
1755 WHEN salary<20000 o:p="" ood="" then="">
1756 ELSE 'Excellent'
1757END) qualified_salary
1758FROM employees;
1759
1760 SELECT last_name,salary,
1761(CASE WHEN salary<5000 o:p="" ow="" then="">
1762 WHEN sal <10000 edium="" o:p="" then="">
1763 WHEN sal <20000 o:p="" ood="" then="">
1764 ELSE 'Excellent'
1765END) qualified_salary
1766FROM employees;
1767
1768 SELECT last_name,sal,
1769(CASE WHEN sal<5000 o:p="" ow="" then="">
1770 WHEN sal<10000 edium="" o:p="" then="">
1771 WHEN sal<20000 o:p="" ood="" then="">
1772 ELSE 'Excellent'
1773END) qualified_salary
1774FROM employees;
1775
17768. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
1777DATA:( King, null
1778Kochhar, 100
1779De Haan, 100
1780Hunold, 102
1781Ernst, 103)
1782SELECT last_name,
1783DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
1784FROM employees
1785
1786 Mark for Review
1787(1) Points
1788
1789 King, A N Other
1790Kochhar, King
1791De Haan, King
1792Hunold, A N Other
1793Ernst, A N Other
1794(*)
1795
1796 King, Null
1797Kochhar, King
1798De Haan, King
1799Hunold, A N Other
1800Ernst, A N Other
1801
1802 Invalid statement.
1803
1804 King, A N Other
1805Kochhar, King
1806De Haan, King
1807Hunold, Kochhar
1808Ernst, De Haan
1809
18109. Which of the following is a conditional expression used in SQL? Mark for Review
1811(1) Points
1812
1813 DESCRIBE
1814 CASE (*)
1815 NULLIF
1816 WHERE
1817
181810. Which function compares two expressions? Mark for Review
1819(1) Points
1820
1821 NVL
1822 NULL
1823 NULLIF (*)
1824 NVL2
1825
182611. Which statement about group functions is true? Mark for Review
1827(1) Points
1828
1829 NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
1830 COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
1831 NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
1832 NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
1833
183412. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review
1835(1) Points
1836
1837 SELECT tuition_balance + housing_balance
1838FROM student_accounts;
1839
1840 SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
1841FROM student_accounts;
1842(*)
1843
1844 SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
1845FROM student_accounts;
1846
1847 SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
1848FROM student_accounts;
1849
185013. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use? Mark for Review
1851(1) Points
1852
1853 TO_NUMBER and NULLIF
1854 TO_CHAR and NULL
1855 TO_CHAR and NULLIF
1856 TO_CHAR and NVL (*)
1857
185814. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
1859DATA:
1860King, null, null
1861Kochhar, null, 100
1862Vargas, null, 124
1863Zlotkey, .2, 100
1864What is the result of the following statement:
1865SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
1866FROM employees ;
1867
1868 Mark for Review
1869(1) Points
1870
1871 King, -1
1872Kochhar, 100
1873Vargas, 124
1874Zlotkey, 100
1875
1876 Statement will fail
1877
1878 King, null
1879Kochhar, 100
1880Vargas, 124
1881Zlotkey, .2
1882
1883 King, -1
1884Kochhar, 100
1885Vargas, 124
1886Zlotkey, .2
1887(*)
1888
188915. The STYLES table contains this data:
1890STYLE_ID STYLE_NAME CATEGORY COST
1891895840 SANDAL 85940 12.00
1892968950 SANDAL 85909 10.00
1893869506 SANDAL 89690 15.00
1894809090 LOAFER 89098 10.00
1895890890 LOAFER 89789 14.00
1896857689 HEEL 85940 11.00
1897758960 SANDAL 86979
1898Evaluate this SELECT statement:
1899
1900SELECT style_id, style_name, category, cost
1901FROM styles
1902WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
1903ORDER BY category, cost;
1904
1905Which result will the query provide?
1906
1907 Mark for Review
1908(1) Points
1909
1910STYLE_ID STYLE_NAME CATEGORY COST
1911968950 SANDAL 85909 10.00
1912895840 SANDAL 85940 12.00
1913758960 SANDAL 86979
1914(*)
1915
1916
1917STYLE_ID STYLE_NAME CATEGORY COST
1918895840 SANDAL 85909 12.00
1919968950 SANDAL 85909 10.00
1920758960 SANDAL 86979
1921869506 SANDAL 89690 15.00
1922
1923STYLE_ID STYLE_NAME CATEGORY COST
1924895840 SANDAL 85940 12.00
1925968950 SANDAL 85909 10.00
1926758960 SANDAL 86979
1927
1928STYLE_ID STYLE_NAME CATEGORY COST
1929895840 SANDAL 85909 12.00
1930968950 SANDAL 85909 10.00
1931869506 SANDAL 89690 15.00
1932
1933758960 SANDAL 86979
1934
1935------------------------------
1936Section 6 Quiz
1937 (Answer all questions in this section)
1938
19391. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
1940EMPLOYEES Table:
1941Name Null? Type
1942EMPLOYEE_ID NOT NULL NUMBER (6)
1943FIRST_NAME VARCHAR2 (20)
1944LAST_NAME NOT NULL VARCHAR2 (25)
1945EMAIL NOT NULL VARCHAR2 (25)
1946PHONE_NUMBER VARCHAR2 (20)
1947HIRE_DATE NOT NULL DATE
1948JOB_ID NOT NULL VARCHAR2 (10)
1949SALARY NUMBER (8,2)
1950COMMISSION_PCT NUMBER (2,2)
1951MANAGER_ID NUMBER (6)
1952DEPARTMENT_ID NUMBER (4)
1953JOBS Table:
1954Name Null? Type
1955JOB_ID NOT NULL VARCHAR2 (10)
1956JOB_TITLE NOT NULL VARCHAR2 (35)
1957MIN_SALARY NUMBER (6)
1958MAX_SALARY NUMBER (6)
1959 Mark for Review
1960(1) Points
1961
1962 SELECT first_name, last_name, job_id, min_salary, max_salary
1963FROM employees
1964NATURAL JOIN jobs;
1965(*)
1966
1967 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
1968FROM employees e
1969NATURAL JOIN jobs j
1970USING (job_id);
1971
1972 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
1973FROM employees e
1974NATURAL JOIN jobs j;
1975
1976 SELECT first_name, last_name, job_id, min_salary, max_salary
1977FROM employees e
1978FULL JOIN jobs j (job_id);
1979
1980 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
1981FROM employees e
1982NATURAL JOIN jobs j ON (e.job_title = j.job_title);
1983
19842. What is another name for a simple join or an inner join? Mark for Review
1985(1) Points
1986
1987 Equijoin (*)
1988 Outer Join
1989 Nonequijoin
1990 Self Join
1991
19923. You need to join the EMPLOYEE_HIST and EMPLOYEES tables. The EMPLOYEE_HIST table will be the first table in the FROM clause. All the matched and unmatched rows in the EMPLOYEES table need to be displayed. Which type of join will you use? Mark for Review
1993(1) Points
1994
1995 An inner join
1996 A right outer join (*)
1997 A cross join
1998 A left outer join
1999
20004. EMPLOYEES Table:
2001Name Null? Type
2002EMPLOYEE_ID NOT NULL NUMBER(6)
2003FIRST_NAME VARCHAR2(20)
2004LAST_NAME NOT NULL VARCHAR2(25)
2005DEPARTMENT_ID NUMBER (4)
2006DEPARTMENTS Table:
2007Name Null? Type
2008DEPARTMENT_ID NOT NULL NUMBER 4
2009DEPARTMENT_NAME NOT NULL VARCHAR2(30)
2010MANAGER_ID NUMBER (6)
2011A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
2012
2013 Mark for Review
2014(1) Points
2015
2016 SELECT d.department_id, e.first_name, e.last_name
2017FROM employees e
2018RIGHT OUTER JOIN departments d
2019ON (e.employee_id = d.manager_id);
2020(*)
2021
2022 SELECT d.department_id, e.first_name, e.last_name
2023FROM employees e, departments d
2024WHERE e.employee_id
2025RIGHT OUTER JOIN d.manager_id;
2026
2027 SELECT d.department_id, e.first_name, e.last_name
2028FROM employees e
2029FULL OUTER JOIN departments d
2030ON (e.employee_id = d.manager_id);
2031
2032 SELECT d.department_id, e.first_name, e.last_name
2033FROM employees e
2034LEFT OUTER JOIN departments d
2035WHERE (e.department_id = d.department_id);
2036
20375. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers? Mark for Review
2038(1) Points
2039
2040 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2041FROM employees w , employees w
2042WHERE w.manager_id = w.employee_id
2043AND w.hire_date < w.hire_date
2044
2045 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2046FROM employees w , employees m
2047WHERE w.manager_id = m.employee_id
2048AND w.hire_date < m.hire_date
2049(*)
2050
2051 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2052FROM employees w , employees m
2053WHERE w.manager_id = m.employee_id
2054AND w.hire_date > m.hire_date
2055
2056 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2057FROM employees w , employees m
2058WHERE w.manager_id != m.employee_id
2059AND w.hire_date < m.hire_date
2060
20616. Which statement about a self join is true? Mark for Review
2062(1) Points
2063
2064 A self join must be implemented by defining a view.
2065 Table aliases must be used to qualify table names. (*)
2066 The NATURAL JOIN clause must be used.
2067 Table aliases cannot be used to qualify table names.
2068
20697. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
2070(1) Points
2071
2072 Arc
2073 Recursive Relationship (*)
2074 Non-Transferability
2075 Supertype
2076
20778. Evaluate this SELECT statement:
2078SELECT *
2079FROM employee e, employee m
2080WHERE e.mgr_id = m.emp_id;
2081Which type of join is created by this SELECT statement?
2082
2083 Mark for Review
2084(1) Points
2085
2086 a full outer join
2087 a left outer join
2088 a cross join
2089 a self join (*)
2090
20919. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review
2092(1) Points
2093
2094 True
2095 False (*)
2096
209710. A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
2098(1) Points
2099 (Choose all correct answers)
2100
2101 Full outer join
2102 Equijoin (*)
2103 Simple join (*)
2104 Self join (*)
2105 Nonequijoin
2106
210711. A NATURAL JOIN is based on: Mark for Review
2108(1) Points
2109
2110 Columns with the same name
2111 Columns with the same datatype and width
2112 Columns with the same name and datatype (*)
2113 Tables with the same structure
2114
211512. 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? Mark for Review
2116(1) Points
2117
2118 Self-join
2119 Cross join
2120 Outer join
2121 Natural join (*)
2122
212313. For which condition would you use an equijoin query with the USING keyword? Mark for Review
2124(1) Points
2125
2126 The CUSTOMER and ORDER tables have no columns with identical names.
2127
2128 You need to perform a join of the CUSTOMER and ORDER tables but limit the number of columns in the join condition. (*)
2129
2130 The ORDER table contains a column that has a referential constraint to a column in the PRODUCT table.
2131
2132 The CUSTOMER and ORDER tables have a corresponding column, CUST_ID. The CUST_ID column in the ORDER table contains null values that need to be displayed.
2133
213414. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False? Mark for Review
2135(1) Points
2136
2137 True
2138 False (*)
2139
214015. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes. Mark for Review
2141(1) Points
2142
2143 NATURAL ON
2144 USING (*)
2145 ON
2146 WHEN
2147
21481. 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? Mark for Review
2149(1) Points
2150
2151 Natural join (*)
2152 Self-join
2153 Cross join
2154 Outer join
2155
21562. A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
2157(1) Points
2158 (Choose all correct answers)
2159
2160 Self join (*)
2161 Full outer join
2162 Nonequijoin
2163 Simple join (*)
2164 Equijoin (*)
2165
21663. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
2167(1) Points
2168
2169 If the columns having the same names have different data types, then an error is returned. (*)
2170 When you attempt to write it as an equijoin.
2171 When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.
2172 If it selects rows from the two tables that have equal values in all matched columns.
2173
21744. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
2175EMPLOYEES Table:
2176Name Null? Type
2177EMPLOYEE_ID NOT NULL NUMBER (6)
2178FIRST_NAME VARCHAR2 (20)
2179LAST_NAME NOT NULL VARCHAR2 (25)
2180EMAIL NOT NULL VARCHAR2 (25)
2181PHONE_NUMBER VARCHAR2 (20)
2182HIRE_DATE NOT NULL DATE
2183JOB_ID NOT NULL VARCHAR2 (10)
2184SALARY NUMBER (8,2)
2185COMMISSION_PCT NUMBER (2,2)
2186MANAGER_ID NUMBER (6)
2187DEPARTMENT_ID NUMBER (4)
2188JOBS Table:
2189Name Null? Type
2190JOB_ID NOT NULL VARCHAR2 (10)
2191JOB_TITLE NOT NULL VARCHAR2 (35)
2192MIN_SALARY NUMBER (6)
2193MAX_SALARY NUMBER (6)
2194 Mark for Review
2195(1) Points
2196
2197 SELECT first_name, last_name, job_id, min_salary, max_salary
2198FROM employees
2199NATURAL JOIN jobs;
2200(*)
2201
2202 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2203FROM employees e
2204NATURAL JOIN jobs j
2205USING (job_id);
2206
2207 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2208FROM employees e
2209NATURAL JOIN jobs j ON (e.job_title = j.job_title);
2210
2211 SELECT first_name, last_name, job_id, min_salary, max_salary
2212FROM employees e
2213FULL JOIN jobs j (job_id);
2214
2215 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2216FROM employees e
2217NATURAL JOIN jobs j;
2218
22195. Which query represents the correct syntax for a left outer join? Mark for Review
2220(1) Points
2221
2222 SELECT companyname, orderdate, total
2223FROM customers c
2224LEFT JOIN orders o
2225ON c.cust_id = o.cust_id;
2226
2227 SELECT companyname, orderdate, total
2228FROM customers c
2229LEFT OUTER orders o
2230ON c.cust_id = o.cust_id;
2231
2232 SELECT companyname, orderdate, total
2233FROM customers c
2234OUTER JOIN orders o
2235ON c.cust_id = o.cust_id;
2236
2237 SELECT companyname, orderdate, total
2238FROM customers c
2239LEFT OUTER JOIN orders o
2240ON c.cust_id = o.cust_id;
2241(*)
2242
22436. Which query will retrieve all the rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table? Mark for Review
2244(1) Points
2245
2246 SELECT e.last_name, e.department_id, d.department_name
2247FROM employees e
2248RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
2249
2250 SELECT e.last_name, e.department_id, d.department_name
2251FROM employees e
2252JOIN departments d USING (e.department_id = d.department_id);
2253
2254 SELECT e.last_name, e.department_id, d.department_name
2255FROM employees e
2256LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
2257(*)
2258
2259 SELECT e.last_name, e.department_id, d.department_name
2260FROM employees e
2261NATURAL JOIN departments d;
2262
22637. Which type of join returns rows from one table that have NO direct match in the other table? Mark for Review
2264(1) Points
2265
2266 Equijoin
2267 Self join
2268 Outer join (*)
2269 Natural join
2270
22718. The primary advantages of using JOIN ON is: (Select two) Mark for Review
2272(1) Points
2273 (Choose all correct answers)
2274
2275 The join happens automatically based on matching column names and data types.
2276 It permits columns that donﾒt have matching data types to be joined. (*)
2277 It permits columns with different names to be joined. (*)
2278 It will display rows that do not meet the join condition.
2279
22809. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False? Mark for Review
2281(1) Points
2282
2283 True
2284 False (*)
2285
228610. The primary advantage of using JOIN ON is: Mark for Review
2287(1) Points
2288
2289 It easily produces a Cartesian product between the tables in the statement.
2290 The join happens automatically based on matching column names and data types.
2291 It permits columns that donﾒt have matching data types to be joined.
2292 It permits columns with different names to be joined. (*)
2293 It will display rows that do not meet the join condition.
2294
229511. Evaluate this SELECT statement:
2296SELECT *
2297FROM employee e, employee m
2298WHERE e.mgr_id = m.emp_id;
2299Which type of join is created by this SELECT statement?
2300
2301 Mark for Review
2302(1) Points
2303
2304 a self join (*)
2305 a full outer join
2306 a left outer join
2307 a cross join
2308
230912. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers? Mark for Review
2310(1) Points
2311
2312 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2313FROM employees w , employees m
2314WHERE w.manager_id != m.employee_id
2315AND w.hire_date < m.hire_date
2316
2317 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2318FROM employees w , employees m
2319WHERE w.manager_id = m.employee_id
2320AND w.hire_date > m.hire_date
2321
2322 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2323FROM employees w , employees m
2324WHERE w.manager_id = m.employee_id
2325AND w.hire_date < m.hire_date
2326(*)
2327
2328 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2329FROM employees w , employees w
2330WHERE w.manager_id = w.employee_id
2331AND w.hire_date < w.hire_date
2332
233313. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
2334(1) Points
2335
2336 Arc
2337 Supertype
2338 Recursive Relationship (*)
2339 Non-Transferability
2340
234114. Which statement about a self join is true? Mark for Review
2342(1) Points
2343
2344 Table aliases must be used to qualify table names. (*)
2345 Table aliases cannot be used to qualify table names.
2346 The NATURAL JOIN clause must be used.
2347 A self join must be implemented by defining a view.
2348
234915. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review
2350(1) Points
2351
2352 True
2353 False (*)
2354
23551. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers? Mark for Review
2356(1) Points
2357
2358 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2359FROM employees w , employees m
2360WHERE w.manager_id != m.employee_id
2361AND w.hire_date < m.hire_date
2362
2363 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2364FROM employees w , employees w
2365WHERE w.manager_id = w.employee_id
2366AND w.hire_date < w.hire_date
2367
2368 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2369FROM employees w , employees m
2370WHERE w.manager_id = m.employee_id
2371AND w.hire_date < m.hire_date
2372(*)
2373
2374 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2375FROM employees w , employees m
2376WHERE w.manager_id = m.employee_id
2377AND w.hire_date > m.hire_date
2378
23792. Which of the following database design concepts is implemented with a self join? Mark for Review
2380(1) Points
2381
2382 Supertype
2383 Arc
2384 Non-Transferability
2385 Recursive Relationship (*)
2386
23873. Which statement about a self join is true? Mark for Review
2388(1) Points
2389
2390 Table aliases cannot be used to qualify table names.
2391 A self join must be implemented by defining a view.
2392 The NATURAL JOIN clause must be used.
2393 Table aliases must be used to qualify table names. (*)
2394
23954. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? Mark for Review
2396(1) Points
2397
2398 True (*)
2399 False
2400
24015. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
2402(1) Points
2403
2404 Supertype
2405 Non-Transferability
2406 Recursive Relationship (*)
2407 Arc
2408
24096. EMPLOYEES Table:
2410Name Null? Type
2411EMPLOYEE_ID NOT NULL NUMBER(6)
2412FIRST_NAME VARCHAR2(20)
2413LAST_NAME NOT NULL VARCHAR2(25)
2414DEPARTMENT_ID NUMBER (4)
2415DEPARTMENTS Table:
2416Name Null? Type
2417DEPARTMENT_ID NOT NULL NUMBER 4
2418DEPARTMENT_NAME NOT NULL VARCHAR2(30)
2419MANAGER_ID NUMBER (6)
2420A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
2421
2422 Mark for Review
2423(1) Points
2424
2425 SELECT d.department_id, e.first_name, e.last_name
2426FROM employees e, departments d
2427WHERE e.employee_id
2428RIGHT OUTER JOIN d.manager_id;
2429
2430 SELECT d.department_id, e.first_name, e.last_name
2431FROM employees e
2432LEFT OUTER JOIN departments d
2433WHERE (e.department_id = d.department_id);
2434
2435 SELECT d.department_id, e.first_name, e.last_name
2436FROM employees e
2437FULL OUTER JOIN departments d
2438ON (e.employee_id = d.manager_id);
2439
2440 SELECT d.department_id, e.first_name, e.last_name
2441FROM employees e
2442RIGHT OUTER JOIN departments d
2443ON (e.employee_id = d.manager_id);
2444(*)
2445
24467. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
2447EMPLOYEES Table:
2448Name Null? Type
2449EMPLOYEE_ID NOT NULL NUMBER (6)
2450FIRST_NAME VARCHAR2 (20)
2451LAST_NAME NOT NULL VARCHAR2 (25)
2452EMAIL NOT NULL VARCHAR2 (25)
2453PHONE_NUMBER VARCHAR2 (20)
2454HIRE_DATE NOT NULL DATE
2455JOB_ID NOT NULL VARCHAR2 (10)
2456SALARY NUMBER (8,2)
2457COMMISSION_PCT NUMBER (2,2)
2458MANAGER_ID NUMBER (6)
2459DEPARTMENT_ID NUMBER (4)
2460JOBS Table:
2461Name Null? Type
2462JOB_ID NOT NULL VARCHAR2 (10)
2463JOB_TITLE NOT NULL VARCHAR2 (35)
2464MIN_SALARY NUMBER (6)
2465MAX_SALARY NUMBER (6)
2466 Mark for Review
2467(1) Points
2468
2469 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2470FROM employees e
2471NATURAL JOIN jobs j;
2472
2473 SELECT first_name, last_name, job_id, min_salary, max_salary
2474FROM employees
2475NATURAL JOIN jobs;
2476(*)
2477
2478 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2479FROM employees e
2480NATURAL JOIN jobs j ON (e.job_title = j.job_title);
2481
2482 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2483FROM employees e
2484NATURAL JOIN jobs j
2485USING (job_id);
2486
2487 SELECT first_name, last_name, job_id, min_salary, max_salary
2488FROM employees e
2489FULL JOIN jobs j (job_id);
2490
24918. Which query represents the correct syntax for a left outer join? Mark for Review
2492(1) Points
2493
2494 SELECT companyname, orderdate, total
2495FROM customers c
2496LEFT OUTER orders o
2497ON c.cust_id = o.cust_id;
2498
2499 SELECT companyname, orderdate, total
2500FROM customers c
2501LEFT JOIN orders o
2502ON c.cust_id = o.cust_id;
2503
2504 SELECT companyname, orderdate, total
2505FROM customers c
2506LEFT OUTER JOIN orders o
2507ON c.cust_id = o.cust_id;
2508(*)
2509
2510 SELECT companyname, orderdate, total
2511FROM customers c
2512OUTER JOIN orders o
2513ON c.cust_id = o.cust_id;
2514
25159. The following statement is an example of what kind of join?
2516SELECT car.vehicle_id, driver.name
2517FROM car
2518LEFT OUTER JOIN driver ON (driver_id) ;
2519
2520 Mark for Review
2521(1) Points
2522
2523 Inner Join
2524 Optimal Join
2525 Equijoin
2526 Outer Join (*)
2527
252810. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
2529(1) Points
2530
2531 When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.
2532
2533 If it selects rows from the two tables that have equal values in all matched columns.
2534 When you attempt to write it as an equijoin.
2535 If the columns having the same names have different data types, then an error is returned. (*)
2536
253711. 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? Mark for Review
2538(1) Points
2539 Natural join (*)
2540 Self-join
2541 Cross join
2542 Outer join
2543
254412. A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
2545(1) Points
2546 (Choose all correct answers)
2547 Full outer join
2548 Nonequijoin
2549 Equijoin (*)
2550 Self join (*)
2551 Simple join (*)
2552
255313. The following is a valid SQL statement.
2554SELECT e.employee_id, e.last_name, d.location_id, department_id
2555FROM employees e JOIN departments d
2556USING (department_id) ;
2557
2558True or False?
2559
2560 Mark for Review
2561(1) Points
2562 True (*)
2563 False
2564
256514. Which keyword in a SELECT statement creates an equijoin by specifying a column name common to both tables? Mark for Review
2566(1) Points
2567 The FROM clause
2568 A USING clause (*)
2569 A HAVING clause
2570 The SELECT clause
2571
257215. Below find the structures of the PRODUCTS and VENDORS tables:
2573PRODUCTS
2574PRODUCT_ID NUMBER
2575PRODUCT_NAME VARCHAR2 (25)
2576VENDOR_ID NUMBER
2577CATEGORY_ID NUMBER
2578
2579VENDORS
2580VENDOR_ID NUMBER
2581VENDOR_NAME VARCHAR2 (25)
2582ADDRESS VARCHAR2 (30)
2583CITY VARCHAR2 (25)
2584REGION VARCHAR2 (10)
2585POSTAL_CODE VARCHAR2 (11)
2586
2587You want to create a query that will return an alphabetical list of products, including the product name and associated vendor name, for all products that have a vendor assigned.
2588
2589Which two queries could you use?
2590
2591 Mark for Review
2592(1) Points
2593 (Choose all correct answers)
2594
2595 SELECT p.product_name, v.vendor_name
2596FROM products p
2597JOIN vendors v
2598ON (vendor_id)
2599ORDER BY p.product_name;
2600
2601 SELECT p.product_name, v.vendor_name
2602FROM products p
2603NATURAL JOIN vendors v
2604ORDER BY p.product_name;
2605(*)
2606
2607 SELECT p.product_name, v.vendor_name
2608FROM products p
2609JOIN vendors v
2610USING (p.vendor_id)
2611ORDER BY p.product_name;
2612
2613 SELECT p.product_name, v.vendor_name
2614FROM products p
2615JOIN vendors v
2616USING (vendor_id)
2617ORDER BY p.product_name;
2618(*)
2619
2620 SELECT p.product_name, v.vendor_name
2621FROM products p
2622LEFT OUTER JOIN vendors v
2623ON p.vendor_id = v.vendor_id
2624ORDER BY p.product_name;
2625
26261. A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
2627(1) Points
2628 (Choose all correct answers)
2629 Equijoin (*)
2630 Full outer join
2631 Self join (*)
2632 Nonequijoin
2633 Simple join (*)
2634
26352. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create? Mark for Review
2636(1) Points
2637 An inner join
2638 A full outer join
2639 A cross join (*)
2640 An equijoin
2641
26423. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
2643(1) Points
2644
2645 When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.
2646 If it selects rows from the two tables that have equal values in all matched columns.
2647 When you attempt to write it as an equijoin.
2648 If the columns having the same names have different data types, then an error is returned. (*)
2649
26504. The primary advantages of using JOIN ON is: (Select two) Mark for Review
2651(1) Points
2652 (Choose all correct answers)
2653 It permits columns that donﾒt have matching data types to be joined. (*)
2654 The join happens automatically based on matching column names and data types.
2655 It permits columns with different names to be joined. (*)
2656 It will display rows that do not meet the join condition.
2657
26585. For which condition would you use an equijoin query with the USING keyword? Mark for Review
2659(1) Points
2660
2661 You need to perform a join of the CUSTOMER and ORDER tables but limit the number of columns in the join condition. (*)
2662
2663 The CUSTOMER and ORDER tables have a corresponding column, CUST_ID. The CUST_ID column in the ORDER table contains null values that need to be displayed.
2664
2665 The ORDER table contains a column that has a referential constraint to a column in the PRODUCT table.
2666
2667 The CUSTOMER and ORDER tables have no columns with identical names.
2668
26696. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes. Mark for Review
2670(1) Points
2671
2672 USING (*)
2673 WHEN
2674 ON
2675 NATURAL ON
2676
26777. Which statement about a self join is true? Mark for Review
2678(1) Points
2679 The NATURAL JOIN clause must be used.
2680 Table aliases cannot be used to qualify table names.
2681 Table aliases must be used to qualify table names. (*)
2682 A self join must be implemented by defining a view.
2683
26848. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
2685(1) Points
2686 Recursive Relationship (*)
2687 Supertype
2688 Non-Transferability
2689 Arc
2690
26919. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? Mark for Review
2692(1) Points
2693 True (*)
2694 False
2695
269610. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review
2697(1) Points
2698 True
2699 False (*)
2700
270111. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers? Mark for Review
2702(1) Points
2703
2704 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2705FROM employees w , employees m
2706WHERE w.manager_id != m.employee_id
2707AND w.hire_date < m.hire_date
2708
2709 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2710FROM employees w , employees w
2711WHERE w.manager_id = w.employee_id
2712AND w.hire_date < w.hire_date
2713
2714 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2715FROM employees w , employees m
2716WHERE w.manager_id = m.employee_id
2717AND w.hire_date > m.hire_date
2718
2719 SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
2720FROM employees w , employees m
2721WHERE w.manager_id = m.employee_id
2722AND w.hire_date < m.hire_date
2723(*)
2724
272512. Which type of join returns rows from one table that have NO direct match in the other table? Mark for Review
2726(1) Points
2727
2728 Natural join
2729 Outer join (*)
2730 Equijoin
2731 Self join
2732
273313. What types of joins will return the unmatched values from both tables in the join? Mark for Review
2734(1) Points
2735
2736 Full outer joins (*)
2737 Left outer joins
2738 Natural joins
2739 Right outer joins
2740
274114. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
2742EMPLOYEES Table:
2743Name Null? Type
2744EMPLOYEE_ID NOT NULL NUMBER (6)
2745FIRST_NAME VARCHAR2 (20)
2746LAST_NAME NOT NULL VARCHAR2 (25)
2747EMAIL NOT NULL VARCHAR2 (25)
2748PHONE_NUMBER VARCHAR2 (20)
2749HIRE_DATE NOT NULL DATE
2750JOB_ID NOT NULL VARCHAR2 (10)
2751SALARY NUMBER (8,2)
2752COMMISSION_PCT NUMBER (2,2)
2753MANAGER_ID NUMBER (6)
2754DEPARTMENT_ID NUMBER (4)
2755JOBS Table:
2756Name Null? Type
2757JOB_ID NOT NULL VARCHAR2 (10)
2758JOB_TITLE NOT NULL VARCHAR2 (35)
2759MIN_SALARY NUMBER (6)
2760MAX_SALARY NUMBER (6)
2761 Mark for Review
2762(1) Points
2763
2764 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2765FROM employees e
2766NATURAL JOIN jobs j
2767USING (job_id);
2768
2769 SELECT first_name, last_name, job_id, min_salary, max_salary
2770FROM employees
2771NATURAL JOIN jobs;
2772(*)
2773
2774 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2775FROM employees e
2776NATURAL JOIN jobs j ON (e.job_title = j.job_title);
2777
2778 SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
2779FROM employees e
2780NATURAL JOIN jobs j;
2781
2782 SELECT first_name, last_name, job_id, min_salary, max_salary
2783FROM employees e
2784FULL JOIN jobs j (job_id);
2785
278615. What is another name for a simple join or an inner join? Mark for Review
2787(1) Points
2788
2789 Equijoin (*)
2790 Self Join
2791 Nonequijoin
2792
2793 Outer Join
2794
2795-----------------
2796
2797Section 7 Quiz Oracle Database Programming with SQL
2798Section 7 Quiz
2799 (Answer all questions in this section)
2800
28011. Evaluate this SQL statement:
2802SELECT e.employee_id, e.last_name, e.first_name, d.department_name
2803FROM employees e, departments d
2804WHERE e.department_id = d.department_id AND employees.department_id > 5000
2805ORDER BY 4;
2806
2807Which clause contains a syntax error?
2808
2809 Mark for Review
2810(1) Points
2811
2812 FROM employees e, departments d
2813 SELECT e.employee_id, e.last_name, e.first_name, d.department_name
2814 ORDER BY 4;
2815 WHERE e.department_id = d.department_id
2816 AND employees.department_id > 5000 (*)
2817
28182. When must column names be prefixed by table names in join syntax? Mark for Review
2819(1) Points
2820
2821 When the more than two tables participate in the join
2822 Never
2823 When the same column name appears in more than one table of the query (*)
2824 Only when query speed and database performance is a concern
2825
28263. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables? Mark for Review
2827(1) Points
2828
2829 5
2830 15
2831 10
2832 50 (*)
2833
28344. You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
2835EMPLOYEES
2836EMP_ID NUMBER(10) PRIMARY KEY
2837LAST_NAME VARCHAR2(20)
2838FIRST_NAME VARCHAR2(20)
2839DEPTARTMENT_ID VARCHAR2(20)
2840HIRE_DATE DATE
2841SALARY NUMBER(10)
2842
2843SALES_DEPT
2844SALES_ID NUMBER(10) PRIMARY KEY
2845SALES NUMBER(20)
2846QUOTA NUMBER(20)
2847MANAGER VARCHAR2(30)
2848BONUS NUMBER(10)
2849EMPLOYEE_ID NUMBER(10) FOREIGN KEY
2850
2851Which SELECT statement will accomplish this task?
2852
2853 Mark for Review
2854(1) Points
2855
2856 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
2857WHERE e.employee_id = s.employee_id
2858FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
2859ORDER BY sales DESC;
2860
2861 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
2862FROM employees e, sales_dept s
2863ORDER BY sales DESC
2864WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
2865
2866 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
2867FROM employees e, sales_dept s
2868WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
2869ORDER BY sales DESC;
2870(*)
2871
2872 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
2873ORDER BY sales DESC
2874FROM employees e, sales_dept s
2875WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
2876
28775. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? Mark for Review
2878(1) Points
2879
2880 3
2881 0
2882 2 (*)
2883 1
2884
28856. What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax? Mark for Review
2886(1) Points
2887
2888 An equijoin
2889 A Cartesian product (*)
2890 A self-join
2891 An outer join
2892
28937. The CUSTOMERS and SALES tables contain these columns:
2894CUSTOMERS
2895CUST_ID NUMBER(10) PRIMARY KEY
2896COMPANY VARCHAR2(30)
2897LOCATION VARCHAR2(20)
2898
2899SALES
2900SALES_ID NUMBER(5) PRIMARY KEY
2901CUST_ID NUMBER(10) FOREIGN KEY
2902TOTAL_SALES NUMBER(30)
2903
2904Which SELECT statement will return the customer ID, the company and the total sales?
2905
2906 Mark for Review
2907(1) Points
2908
2909 SELECT c.cust_id, c.company, s.total_sales
2910FROM customers c, sales s
2911WHERE c.cust_id = s.cust_id;
2912(*)
2913
2914 SELECT cust_id, company, total_sales
2915FROM customers, sales
2916WHERE cust_id = cust_id;
2917
2918 SELECT cust_id, company, total_sales
2919FROM customers c, sales s
2920WHERE c.cust_id = s.cust_id;
2921
2922 SELECT c.cust_id, c.company, s.total_sales
2923FROM customers c, sales s
2924WHERE c.cust_id = s.cust_id (+);
2925
29268. You have the following EMPLOYEES table:
2927EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
2928FIRST_NAME VARCHAR2(25)
2929LAST_NAME VARCHAR2(25)
2930ADDRESS VARCHAR2(35)
2931CITY VARCHAR2(25)
2932STATE VARCHAR2(2)
2933ZIP NUMBER(9)
2934TELEPHONE NUMBER(10)
2935DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
2936
2937The BONUS table includes the following columns:
2938
2939BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
2940ANNUAL_SALARY NUMBER(10)
2941BONUS_PCT NUMBER(3, 2)
2942EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
2943
2944You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?
2945
2946 Mark for Review
2947(1) Points
2948
2949 SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
2950FROM employees e, bonus b
2951WHERE e.employee_id = b.employee_id;
2952(*)
2953
2954 SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
2955FROM employees e, bonus b
2956WHERE e.employee_id = b.employee_id;
2957
2958 SELECT first_name, last_name, annual_salary * bonus_pct
2959FROM employees, bonus NATURAL JOIN;
2960
2961 SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
2962FROM employees, bonus
2963WHERE e.employee_id = b.employee_id;
2964
29659. The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data? Mark for Review
2966(1) Points
2967
2968 Self join
2969 Nonequi-Join
2970 Outer join (*)
2971 Equijoin
2972
297310. Evaluate this SELECT statement:
2974SELECT p.player_id, m.last_name, m.first_name, t.team_name
2975FROM player p
2976LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
2977LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
2978
2979Which join is evaluated first?
2980
2981 Mark for Review
2982(1) Points
2983
2984 The join between the player table and the team table on MANAGER_ID
2985 The self-join of the player table (*)
2986 The join between the player table and the team table on TEAM_ID
2987 The join between the player table and the team table on PLAYER_ID
2988
298911. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)? Mark for Review
2990(1) Points
2991
2992 OR and =
2993 AND and = (*)
2994 IN and =
2995 BETWEEN...AND... and IN
2996
299712. You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create? Mark for Review
2998(1) Points
2999
3000 A full outer join
3001 It is not possible to join these two tables.
3002 A non-equijoin (*)
3003 An equijoin
3004
300513. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join? Mark for Review
3006(1) Points
3007
3008 *
3009 (+) (*)
3010 =
3011 +
3012
301314. Which statement about outer joins is true? Mark for Review
3014(1) Points
3015
3016 The OR operator cannot be used to link outer join conditions. (*)
3017 The tables must be aliased.
3018 Outer joins are always evaluated before other types of joins in the query.
3019 The FULL, RIGHT, or LEFT keyword must be included.
3020
302115. The following is a valid outer join statement:
3022SELECT c.country_name, d.department_name
3023FROM countries c, departments d
3024WHERE c.country_id (+) = d.country_id (+)
3025
3026True or False?
3027
3028 Mark for Review
3029(1) Points
3030
3031 True
3032 False (*)
3033
30341. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? Mark for Review
3035(1) Points
3036
3037 0
3038 3
3039 1
3040 2 (*)
3041
30422. What is the result of a query that selects from two tables but includes no join condition? Mark for Review
3043(1) Points
3044
3045 A syntax error
3046 A Cartesian product (*)
3047 A selection of matched rows from both tables
3048 A selection of rows from the first table only
3049
30503. Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true? Mark for Review
3051(1) Points
3052
3053 The FROM clause represents the join criteria.
3054 The JOIN keyword must be included.
3055 The WHERE clause represents the join criteria. (*)
3056 The ON keyword must be included.
3057
30584. You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
3059Which query should you issue? Mark for Review
3060(1) Points
3061
3062 SELECT first_name, last_name, sales
3063FROM employees e, sales s
3064WHERE e.employee_id = s.employee_id AND revenue > 100000;
3065
3066 SELECT e.first_name, e.last_name, s.sales
3067FROM employees, sales
3068WHERE e.employee_id = s.employee_id AND revenue >= 100000;
3069
3070 SELECT e.first_name, e.last_name, s.sales
3071FROM employees e, sales s
3072WHERE e.employee_id = s.employee_id AND revenue > 100000;
3073
3074 SELECT e.first_name, e.last_name, s.sales
3075FROM employees e, sales s
3076WHERE e.employee_id = s.employee_id AND revenue >= 100000;
3077(*)
3078
30795. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a equi-join on those two tables? Mark for Review
3080(1) Points
3081
3082 It depends on how many rows have matching data in each of the two tables. (*)
3083 5
3084 50
3085 10
3086
30876. You have the following EMPLOYEES table:
3088EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
3089FIRST_NAME VARCHAR2(25)
3090LAST_NAME VARCHAR2(25)
3091ADDRESS VARCHAR2(35)
3092CITY VARCHAR2(25)
3093STATE VARCHAR2(2)
3094ZIP NUMBER(9)
3095TELEPHONE NUMBER(10)
3096DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
3097
3098The BONUS table includes the following columns:
3099
3100BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
3101ANNUAL_SALARY NUMBER(10)
3102BONUS_PCT NUMBER(3, 2)
3103EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
3104
3105You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?
3106
3107 Mark for Review
3108(1) Points
3109
3110 SELECT first_name, last_name, annual_salary * bonus_pct
3111FROM employees, bonus NATURAL JOIN;
3112
3113 SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
3114FROM employees, bonus
3115WHERE e.employee_id = b.employee_id;
3116
3117 SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
3118FROM employees e, bonus b
3119WHERE e.employee_id = b.employee_id;
3120
3121 SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
3122FROM employees e, bonus b
3123WHERE e.employee_id = b.employee_id;
3124(*)
3125
31267. The PATIENTS and DOCTORS tables contain these columns:
3127PATIENTS
3128PATIENT_ID NUMBER(9)
3129LAST_NAME VARCHAR2 (20)
3130FIRST_NAME VARCHAR2 (20)
3131
3132DOCTORS
3133DOCTOR_ID NUMBER(9)
3134LAST_NAME VARCHAR2 (20)
3135FIRST_NAME VARCHAR2 (20)
3136
3137You issue this statement:
3138SELECT patient_id, doctor_id
3139FROM patients, doctors;
3140
3141Which result will this statement provide?
3142
3143 Mark for Review
3144(1) Points
3145
3146 A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values (*)
3147 A report containing each patient's id value and his doctor's id value
3148 A report with NO duplicate PATIENT_ID or DOCTOR_ID values
3149 A syntax error
3150
31518. You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
3152EMPLOYEES
3153EMP_ID NUMBER(10) PRIMARY KEY
3154LAST_NAME VARCHAR2(20)
3155FIRST_NAME VARCHAR2(20)
3156DEPTARTMENT_ID VARCHAR2(20)
3157HIRE_DATE DATE
3158SALARY NUMBER(10)
3159
3160SALES_DEPT
3161SALES_ID NUMBER(10) PRIMARY KEY
3162SALES NUMBER(20)
3163QUOTA NUMBER(20)
3164MANAGER VARCHAR2(30)
3165BONUS NUMBER(10)
3166EMPLOYEE_ID NUMBER(10) FOREIGN KEY
3167
3168Which SELECT statement will accomplish this task?
3169
3170 Mark for Review
3171(1) Points
3172
3173 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
3174WHERE e.employee_id = s.employee_id
3175FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
3176ORDER BY sales DESC;
3177
3178 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
3179ORDER BY sales DESC
3180FROM employees e, sales_dept s
3181WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
3182
3183 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
3184FROM employees e, sales_dept s
3185ORDER BY sales DESC
3186WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
3187
3188 SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
3189FROM employees e, sales_dept s
3190WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
3191ORDER BY sales DESC;
3192(*)
3193
31949. Which statement about joining tables with a non-equijoin is false? Mark for Review
3195(1) Points
3196
3197 A WHERE clause must specify a column in one table that is compared to a column in the second table (*)
3198
3199 The number of join conditions required is always one less than the number of tables being joined
3200
3201 The columns being joined must have compatible data types
3202 None of the above
3203
320410. The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data? Mark for Review
3205(1) Points
3206
3207 Self join
3208 Nonequi-Join
3209 Equijoin
3210 Outer join (*)
3211
321211. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)? Mark for Review
3213(1) Points
3214
3215 IN and =
3216 BETWEEN...AND... and IN
3217 AND and = (*)
3218 OR and =
3219
322012. To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
3221SELECT d.department_name, e.last_name
3222FROM employees e, departments d
3223WHERE
3224
3225 Mark for Review
3226(1) Points
3227
3228 e.department_id = d.department_id
3229 e.department_id(+) = d.department_id(+)
3230 e.department_id = d.department_id(+)
3231 e.department_id(+) = d.department_id (*)
3232
323313. The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
3234The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
3235Which type of join should you use to display the data? Mark for Review
3236(1) Points
3237
3238 Natural join
3239 Self-join
3240 Equijoin
3241 Outer join (*)
3242
324314. Which operator is typically used in a nonequijoin? Mark for Review
3244(1) Points
3245
3246 >=, <=, or BETWEEN ...AND (*)
3247 NOT
3248 *
3249 OR
3250 IN
3251
325215. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join? Mark for Review
3253(1) Points
3254
3255 (+) (*)
3256 =
3257 +
3258 *
3259
32601. Will the following statement work?
3261SELECT department_name, last_name
3262FROM employees, departments
3263WHERE department_id = department_id;
3264
3265 Mark for Review
3266(1) Points
3267
3268 No, Oracle will return a Column Ambiguously Defined error. (*)
3269 Yes, Oracle will resolve which department_id colum comes from which table.
3270 Yes, there are no syntax errors in that statement
3271 No, Oracle will not allow joins in the WHERE clause
3272
32732. When must column names be prefixed by table names in join syntax? Mark for Review
3274(1) Points
3275
3276 Only when query speed and database performance is a concern
3277 When the more than two tables participate in the join
3278 When the same column name appears in more than one table of the query (*)
3279 Never
3280
32813. Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False? Mark for Review
3282(1) Points
3283
3284 True (*)
3285 False
3286
32874. What is the minimum number of join conditions required to join 5 tables together? Mark for Review
3288(1) Points
3289
3290 4 (*)
3291 3
3292 5
3293 One more than the number of tables
3294
32955. What happens when you create a Cartesian product? Mark for Review
3296(1) Points
3297
3298 All rows from one table are joined to all rows of another table (*)
3299 The table is joined to another equal table
3300 All rows that do not match in the WHERE clause are displayed
3301 The table is joined to itself, one column to the next column, exhausting all possibilities
3302
33036. Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true? Mark for Review
3304(1) Points
3305
3306 The FROM clause represents the join criteria.
3307 The WHERE clause represents the join criteria. (*)
3308 The ON keyword must be included.
3309 The JOIN keyword must be included.
3310
33117. What is the result of a query that selects from two tables but includes no join condition? Mark for Review
3312(1) Points
3313
3314 A selection of matched rows from both tables
3315 A selection of rows from the first table only
3316 A Cartesian product (*)
3317 A syntax error
3318
33198. The CUSTOMERS and SALES tables contain these columns:
3320CUSTOMERS
3321CUST_ID NUMBER(10) PRIMARY KEY
3322COMPANY VARCHAR2(30)
3323LOCATION VARCHAR2(20)
3324
3325SALES
3326SALES_ID NUMBER(5) PRIMARY KEY
3327CUST_ID NUMBER(10) FOREIGN KEY
3328TOTAL_SALES NUMBER(30)
3329
3330Which SELECT statement will return the customer ID, the company and the total sales?
3331
3332 Mark for Review
3333(1) Points
3334
3335 SELECT cust_id, company, total_sales
3336FROM customers c, sales s
3337WHERE c.cust_id = s.cust_id;
3338
3339 SELECT c.cust_id, c.company, s.total_sales
3340FROM customers c, sales s
3341WHERE c.cust_id = s.cust_id;
3342(*)
3343
3344 SELECT c.cust_id, c.company, s.total_sales
3345FROM customers c, sales s
3346WHERE c.cust_id = s.cust_id (+);
3347
3348 SELECT cust_id, company, total_sales
3349FROM customers, sales
3350WHERE cust_id = cust_id;
3351
33529. You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create? Mark for Review
3353(1) Points
3354
3355 A non-equijoin (*)
3356 It is not possible to join these two tables.
3357 An equijoin
3358 A full outer join
3359
336010. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join? Mark for Review
3361(1) Points
3362
3363 (+) (*)
3364 +
3365 *
3366 =
3367
336811. The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
3369The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
3370Which type of join should you use to display the data? Mark for Review
3371(1) Points
3372
3373 Natural join
3374 Self-join
3375 Equijoin
3376 Outer join (*)
3377
337812. To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
3379SELECT d.department_name, e.last_name
3380FROM employees e, departments d
3381WHERE
3382
3383 Mark for Review
3384(1) Points
3385
3386 e.department_id = d.department_id(+)
3387 e.department_id(+) = d.department_id(+)
3388 e.department_id = d.department_id
3389 e.department_id(+) = d.department_id (*)
3390
339113. Nonequijoins are normally used with which of the following? (Choose two) Mark for Review
3392(1) Points
3393 (Choose all correct answers)
3394
3395 Ranges of numbers (*)
3396 Ranges of rowids
3397 Ranges of text
3398 ranges of columns
3399 Ranges of dates (*)
3400
340114. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)? Mark for Review
3402(1) Points
3403
3404 BETWEEN...AND... and IN
3405 IN and =
3406 AND and = (*)
3407 OR and =
3408
340915. Which statement about joining tables with a non-equijoin is false? Mark for Review
3410(1) Points
3411
3412 A WHERE clause must specify a column in one table that is compared to a column in the second table (*)
3413
3414 The number of join conditions required is always one less than the number of tables being joined
3415
3416 The columns being joined must have compatible data types
3417
3418
3419 None of the above
3420
3421----------------------------
3422
3423Section 8 Quiz
3424 (Answer all questions in this section)
3425
34261. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following? Mark for Review
3427(1) Points
3428
3429 All except numeric
3430 Only numeric data types (*)
3431 Integers only
3432 Any data type
3433
34342. Which aggregate function can be used on a column of the DATE data type? Mark for Review
3435(1) Points
3436
3437 MAX (*)
3438 STDDEV
3439 AVG
3440 SUM
3441
34423. The VENDORS table contains these columns:
3443VENDOR_ID NUMBER Primary Key
3444NAME VARCHAR2(30)
3445LOCATION_ID NUMBER
3446ORDER_DT DATE
3447ORDER_AMOUNT NUMBER(8,2)
3448
3449Which two clauses represent valid uses of aggregate functions for this table?
3450
3451 Mark for Review
3452(1) Points
3453 (Choose all correct answers)
3454
3455 SELECT SUM(order_dt)
3456 FROM MAX(order_dt)
3457 SELECT SUM(order_amount) (*)
3458 WHERE MAX(order_dt) = order_dt
3459 SELECT MIN(AVG(order_amount)) (*)
3460
34614. Group functions return a value for ________________ and ________________ null values in their computations. Mark for Review
3462(1) Points
3463
3464 a row set, ignore (*)
3465 each row, include
3466 each row, ignore
3467 a row set, include
3468
34695. Which group function would you use to display the lowest value in the SALES_AMOUNT column? Mark for Review
3470(1) Points
3471
3472 COUNT
3473 MAX
3474 AVG
3475 MIN (*)
3476
34776. Given the following data in the employees table (employee_id, salary, commission_pct)
3478DATA: (143, 2600, null
3479144, 2500, null
3480149, 10500, .2
3481174, 11000, .3
3482176, 8600, .2
3483178, 7000, .15)
3484
3485What is the result of the following statement:
3486
3487SELECT SUM(commission_pct), COUNT(salary)
3488FROM employees
3489WHERE employee_id IN( 143,144,149,174,176,178);
3490
3491 Mark for Review
3492(1) Points
3493
3494 SUM = 1.85 and COUNT =4
3495 SUM = 1.85 and COUNT = 6
3496 SUM = .85 and COUNT = 4
3497 SUM = .85 and COUNT = 6 (*)
3498
34997. The TRUCKS table contains these columns:
3500TRUCKS:
3501TYPE VARCHAR2(30)
3502YEAR DATE
3503MODEL VARCHAR2(20)
3504PRICE NUMBER(10)
3505
3506Which SELECT statement will return the average price for the 4x4 model?
3507
3508 Mark for Review
3509(1) Points
3510
3511 SELECT AVG(price), model
3512FROM trucks
3513WHERE model IS '4x4';
3514
3515 SELECT AVG(price)
3516FROM trucks
3517WHERE model IS 4x4;
3518
3519 SELECT AVG(price)
3520FROM trucks
3521WHERE model IS '4x4';
3522
3523 SELECT AVG(price)
3524FROM trucks
3525WHERE model = '4x4';
3526(*)
3527
35288. You need to compute the total salary amount for all employees in department 10. Which group function will you use? Mark for Review
3529(1) Points
3530
3531 COUNT
3532 SUM (*)
3533 VARIANCE
3534 MAX
3535
35369. Evaluate this SELECT statement:
3537SELECT COUNT(*)
3538FROM products;
3539
3540Which statement is true?
3541
3542 Mark for Review
3543(1) Points
3544
3545 The number of rows in the table is displayed. (*)
3546 An error occurs due to an error in the SELECT clause.
3547 An error occurs because no WHERE clause is included in the SELECT statement.
3548 The number of unique PRODUCT_IDs in the table is displayed.
3549
355010. The EMPLOYEES table contains these columns:
3551EMPLOYEE_ID NUMBER(9)
3552LAST_NAME VARCHAR2(20)
3553FIRST_NAME VARCHAR2(20)
3554SALARY NUMBER(7,2)
3555DEPARTMENT_ID NUMBER(9)
3556
3557You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?
3558
3559 Mark for Review
3560(1) Points
3561
3562 SELECT COUNT(*)
3563FROM employees
3564WHERE salary < 50000;
3565
3566 SELECT * FROM employees
3567WHERE salary < 50000;
3568
3569 SELECT COUNT(*)
3570FROM employees
3571WHERE salary > 50000
3572GROUP BY employee_id, last_name, first_name, salary, department_id;
3573
3574 SELECT COUNT(*)
3575FROM employees
3576WHERE salary > 50000;
3577(*)
3578
3579 SELECT * FROM employees
3580WHERE salary > 50000;
3581
358211. Evaluate this SQL statement:
3583SELECT COUNT (amount)
3584FROM inventory;
3585
3586What will occur when the statement is issued?
3587
3588 Mark for Review
3589(1) Points
3590
3591 The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)
3592
3593 The statement will return the total number of rows in the AMOUNT column.
3594
3595 The statement will replace all NULL values that exist in the AMOUNT column.
3596 The statement will return the greatest value in the INVENTORY table.
3597
359812. To include null values in the calculations of a group function, you must: Mark for Review
3599(1) Points
3600
3601 Group functions can never use null values
3602 Convert the null to a value using the NVL( ) function (*)
3603 Count the number of null values in that column using COUNT
3604 Precede the group function name with NULL
3605
360613. Which statement about the COUNT function is true? Mark for Review
3607(1) Points
3608
3609 The COUNT function always ignores null values by default. (*)
3610 The COUNT function can be used to determine the number of unique, non-null values in a column.
3611 The COUNT function can be used to find the maximum value in each column.
3612 The COUNT function ignores duplicates by default.
3613
361414. Which SELECT statement will calculate the number of rows in the PRODUCTS table? Mark for Review
3615(1) Points
3616
3617 SELECT ROWCOUNT FROM products;
3618 SELECT COUNT(products);
3619 SELECT COUNT (*) FROM products; (*)
3620 SELECT COUNT FROM products;
3621
362215. Using your existing knowledge of the employees table, would the following two statements produce the same result?
3623SELECT COUNT(*)
3624FROM employees;
3625
3626SELECT COUNT(commission_pct)
3627FROM employees;
3628 Mark for Review
3629(1) Points
3630
3631 The second statement is invalid
3632 The first statement is invalid
3633 Yes
3634 No (*)
3635
36361. The TRUCKS table contains these columns:
3637TRUCKS:
3638TYPE VARCHAR2(30)
3639YEAR DATE
3640MODEL VARCHAR2(20)
3641PRICE NUMBER(10)
3642
3643Which SELECT statement will return the average price for the 4x4 model?
3644
3645 Mark for Review
3646(1) Points
3647
3648 SELECT AVG(price), model
3649FROM trucks
3650WHERE model IS '4x4';
3651
3652 SELECT AVG(price)
3653FROM trucks
3654WHERE model IS 4x4;
3655
3656 SELECT AVG(price)
3657FROM trucks
3658WHERE model IS '4x4';
3659
3660 SELECT AVG(price)
3661FROM trucks
3662WHERE model = '4x4';
3663(*)
3664
36652. Which group function would you use to display the highest salary value in the EMPLOYEES table? Mark for Review
3666(1) Points
3667
3668 MIN
3669 MAX (*)
3670 AVG
3671 COUNT
3672
36733. You need to compute the total salary amount for all employees in department 10. Which group function will you use? Mark for Review
3674(1) Points
3675
3676 SUM (*)
3677 MAX
3678 VARIANCE
3679 COUNT
3680
36814. You need to calculate the average salary of employees in each department. Which group function will you use? Mark for Review
3682(1) Points
3683
3684 AVG (*)
3685 AVERAGE
3686 MEDIAN
3687 MEAN
3688
36895. Examine the data in the PAYMENT table:
3690PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
369186590586 8908090 10-Jun-2003 BASIC 859.00
369289453485 8549038 15-Feb-2003 INTEREST 596.00
369385490345 5489304 20-Mar-2003 BASIC 568.00
3694You need to determine the average payment amount made by each customer in January, February, and March of 2003.
3695Which SELECT statement should you use?
3696
3697 Mark for Review
3698(1) Points
3699
3700 SELECT AVG(payment_amount)
3701FROM payment
3702WHERE payment_date
3703BETWEEN '01-Jan-2003' AND '31-Mar-2003';
3704(*)
3705
3706 SELECT AVG(payment_amount)
3707FROM payment;
3708
3709 SELECT AVG(payment_amount)
3710FROM payment
3711WHERE TO_CHAR(payment_date) IN (Jan, Feb, Mar);
3712
3713 SELECT SUM(payment_amount)
3714FROM payment
3715WHERE payment_date BETWEEN '01-Jan-2003' and '31-Mar-2003';
3716
37176. Which group function would you use to display the lowest value in the SALES_AMOUNT column? Mark for Review
3718(1) Points
3719
3720 MIN (*)
3721 MAX
3722 COUNT
3723 AVG
3724
37257. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following? Mark for Review
3726(1) Points
3727
3728 Integers only
3729 Only numeric data types (*)
3730 Any data type
3731 All except numeric
3732
37338. Which group function would you use to display the total of all salary values in the EMPLOYEES table? Mark for Review
3734(1) Points
3735
3736 COUNT
3737 MAX
3738 AVG
3739 SUM (*)
3740
37419. Evaluate this SELECT statement:
3742SELECT COUNT(*)
3743FROM products;
3744
3745Which statement is true?
3746
3747 Mark for Review
3748(1) Points
3749
3750 The number of unique PRODUCT_IDs in the table is displayed.
3751 An error occurs due to an error in the SELECT clause.
3752 The number of rows in the table is displayed. (*)
3753 An error occurs because no WHERE clause is included in the SELECT statement.
3754
375510. Evaluate this SQL statement:
3756SELECT COUNT (amount)
3757FROM inventory;
3758
3759What will occur when the statement is issued?
3760
3761 Mark for Review
3762(1) Points
3763
3764 The statement will return the greatest value in the INVENTORY table.
3765 The statement will replace all NULL values that exist in the AMOUNT column.
3766 The statement will return the total number of rows in the AMOUNT column.
3767 The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)
3768
376911. Group functions can avoid computations involving duplicate values by including which keyword? Mark for Review
3770(1) Points
3771
3772 DISTINCT (*)
3773 SELECT
3774 UNLIKE
3775 NULL
3776
377712. The EMPLOYEES table contains these columns:
3778EMPLOYEE_ID NUMBER(9)
3779LAST_NAME VARCHAR2(20)
3780FIRST_NAME VARCHAR2(20)
3781SALARY NUMBER(7,2)
3782DEPARTMENT_ID NUMBER(9)
3783
3784You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?
3785
3786 Mark for Review
3787(1) Points
3788
3789 SELECT * FROM employees
3790WHERE salary > 50000;
3791
3792 SELECT COUNT(*)
3793FROM employees
3794WHERE salary > 50000;
3795(*)
3796
3797 SELECT * FROM employees
3798WHERE salary < 50000;
3799
3800 SELECT COUNT(*)
3801FROM employees
3802WHERE salary > 50000
3803GROUP BY employee_id, last_name, first_name, salary, department_id;
3804
3805 SELECT COUNT(*)
3806FROM employees
3807WHERE salary < 50000;
3808
380913. Which statement about the COUNT function is true? Mark for Review
3810(1) Points
3811
3812 The COUNT function always ignores null values by default. (*)
3813 The COUNT function can be used to find the maximum value in each column.
3814 The COUNT function ignores duplicates by default.
3815 The COUNT function can be used to determine the number of unique, non-null values in a column.
3816
381714. Which SELECT statement will calculate the number of rows in the PRODUCTS table? Mark for Review
3818(1) Points
3819
3820 SELECT COUNT FROM products;
3821 SELECT ROWCOUNT FROM products;
3822 SELECT COUNT (*) FROM products; (*)
3823 SELECT COUNT(products);
3824
382515. The STYLES table contains this data:
3826STYLE_ID STYLE_NAME CATEGORY COST
3827895840 SANDAL 85940 12.00
3828968950 SANDAL 85909 10.00
3829869506 SANDAL 89690 15.00
3830809090 LOAFER 89098 10.00
3831890890 LOAFER 89789 14.00
3832857689 HEEL 85940 11.00
3833758960 SANDAL 86979
3834You issue this SELECT statement:
3835
3836SELECT COUNT(category)
3837FROM styles;
3838
3839Which value is displayed?
3840
3841 Mark for Review
3842(1) Points
3843
3844 7 (*)
3845 6
3846 The statement will NOT execute successfully.
3847
3848 0
3849-----------------------
3850
3851
3852Section 9 Quiz
3853 (Answer all questions in this section)
3854
38551. 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
3856(1) Points
3857
3858 HAVING
3859 ROLLUP
3860 CUBE (*)
3861 GROUP BY ALL COLUMNS
3862
38632. Examine the following statement:
3864SELECT department_id, manager_id, job_id, SUM(salary)
3865FROM employees
3866GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
3867
3868What data will this query generate?
3869
3870 Mark for Review
3871(1) Points
3872
3873 Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
3874 Sum of salaries for (department_id, job_id, manager_id)
3875 Subtotals for (job_id, manager_id)
3876 The statement will fail.
3877
38783. Examine the following statement:
3879SELECT department_id, manager_id, job_id, SUM(salary)
3880FROM employees
3881GROUP BY ROLLUP(department_id, manager_id)
3882
3883What extra data will this query generate?
3884
3885 Mark for Review
3886(1) Points
3887
3888 Subtotals for department_id, and grand totals for salary
3889 Subtotals for department_id, job_id and grand totals for salary
3890 Subtotals for department_id, job_id, manager_id and grand totals for salary
3891 The statement will fail. (*)
3892
38934. You use ROLLUP to: Mark for Review
3894(1) Points
3895
3896 produce subtotal values (*)
3897 cross-tabulate values
3898 produce a single result set
3899
39005. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
3901(1) Points
3902
3903 GROUP BY (*)
3904 WHERE
3905 SELECT
3906
39076. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
3908(1) Points
3909
3910 True (*)
3911 False
3912
39137. Which of the following are correct SET operators? (choose two) Mark for Review
3914(1) Points
3915 (Choose all correct answers)
3916 MINUS, PLUS
3917 UNION, MINUS (*)
3918 UNION ALL, PLUS ALL
3919 UNION ALL, INTERSECT (*)
3920
39218. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
3922(1) Points
3923 UNION ALL
3924 UNION (*)
3925 MINUS
3926 INTERSECT
3927
39289. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query. Mark for Review
3929(1) Points
3930 ONCE; LAST (*)
3931 IN ALL; LAST
3932 ONCE; FIRST
3933 TWICE; FIRST
3934
393510. If a select list contains both a column as well as a group function then what clause is required? Mark for Review
3936(1) Points
3937 HAVING clause
3938 GROUP BY clause (*)
3939 ORDER BY clause
3940 JOIN clause
3941
394211. Is the following statement correct?
3943SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
3944FROM employees
3945WHERE department_id = 50
3946GROUP BY last_name, first_name, department_id;
3947
3948 Mark for Review
3949(1) Points
3950 Yes
3951 No, because the statement is missing salary in the GROUP BY clause (*)
3952 Yes, because Oracle will correct any mistakes in the statement itself
3953 No, beause you cannot have a WHERE-clause when you use group functions.
3954
395512. What will the following SQL Statement do?
3956SELECT job_id, COUNT(*)
3957FROM employees
3958GROUP BY job_id;
3959
3960 Mark for Review
3961(1) Points
3962
3963 Displays each job id and the number of people assigned to that job id (*)
3964 Displays all the jobs with as many people as there are jobs
3965 Displays only the number of job_ids
3966 Displays all the employees and groups them by job
3967
396813. The PLAYERS table contains these columns:
3969PLAYER_ID NUMBER PK
3970PLAYER_NAME VARCHAR2 (30)
3971TEAM_ID NUMBER
3972HIRE_DATE DATE
3973SALARY NUMBER (8,2)
3974
3975Which clauses represent valid uses of aggregate functions? (Choose three.)
3976
3977 Mark for Review
3978(1) Points
3979
3980 (Choose all correct answers)
3981 SELECT AVG(NVL(salary, 0)) (*)
3982 GROUP BY MAX(salary)
3983 ORDER BY AVG(salary) (*)
3984 WHERE hire_date > AVG(hire_date)
3985 HAVING MAX(salary) > 10000 (*)
3986
398714. Evaluate this SELECT statement:
3988SELECT MIN(hire_date), department_id
3989FROM employees
3990GROUP BY department_id;
3991
3992Which values are displayed?
3993
3994 Mark for Review
3995(1) Points
3996
3997 The hire dates in the EMPLOYEES table that contain NULL values
3998 The latest hire date in the EMPLOYEES table
3999 The earliest hire date in each department (*)
4000 The earliest hire date in the EMPLOYEES table
4001
400215. The EMPLOYEES table contains these columns:
4003ID_NUMBER NUMBER Primary Key
4004NAME VARCHAR2 (30)
4005DEPARTMENT_ID NUMBER
4006SALARY NUMBER (7,2)
4007HIRE_DATE DATE
4008
4009Evaluate this SQL statement:
4010
4011SELECT id_number, name, department_id, SUM(salary)
4012FROM employees
4013WHERE salary > 25000
4014GROUP BY department_id, id_number, name
4015ORDER BY hire_date;
4016
4017Why will this statement cause an error?
4018
4019 Mark for Review
4020(1) Points
4021 The HAVING clause is missing.
4022 The SALARY column is NOT included in the GROUP BY clause.
4023 The WHERE clause contains a syntax error.
4024 The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
4025
40261. Which statement about group functions is true? Mark for Review
4027(1) Points
4028 Group functions can only be used in a SELECT list.
4029 A query that includes a group function in the SELECT list must include a GROUP BY clause.
4030 Group functions ignore null values. (*)
4031 Group functions can be used in a WHERE clause.
4032
40332. What is the best explanation as to why this SQL statement will NOT execute?
4034SELECT department_id "Department", AVG (salary)"Average"
4035FROM employees
4036GROUP BY Department;
4037
4038 Mark for Review
4039(1) Points
4040 The GROUP BY clause must have something to GROUP.
4041 Salaries cannot be averaged as not all the numbers will divide evenly.
4042 You cannot use a column alias in the GROUP BY clause. (*)
4043 The department id is not listed in the departments table.
4044
40453. The PRODUCTS table contains these columns:
4046PROD_ID NUMBER(4)
4047PROD_NAME VARCHAR(20)
4048PROD_CAT VARCHAR2(15)
4049PROD_PRICE NUMBER(5)
4050PROD_QTY NUMBER(4)
4051
4052You need to identify the minimum product price in each product category.
4053Which statement could you use to accomplish this task?
4054
4055 Mark for Review
4056(1) Points
4057
4058 SELECT prod_price, MIN (prod_cat)
4059FROM products
4060GROUP BY prod_cat;
4061
4062 SELECT prod_cat, MIN (prod_price)
4063FROM products
4064GROUP BY prod_price;
4065
4066 SELECT prod_cat, MIN (prod_price)
4067FROM products
4068GROUP BY prod_cat;
4069(*)
4070
4071 SELECT MIN (prod_price), prod_cat
4072FROM products
4073GROUP BY MIN (prod_price), prod_cat;
4074
40754. Evaluate this statement:
4076SELECT department_id, AVG(salary)
4077FROM employees
4078WHERE job_id <> 69879
4079GROUP BY job_id, department_id
4080HAVING AVG(salary) > 35000
4081ORDER BY department_id;
4082
4083Which clauses restricts the result? Choose two.
4084
4085 Mark for Review
4086(1) Points
4087
4088 (Choose all correct answers)
4089
4090 GROUP BY job_id, department_id
4091 WHERE job_id <> 69879 (*)
4092 SELECT department_id, AVG(salary)
4093 HAVING AVG(salary) > 35000 (*)
4094
40955. The PRODUCTS table contains these columns:
4096PRODUCT_ID NUMBER(9) PK
4097CATEGORY_ID VARCHAR2(10)
4098LOCATION_ID NUMBER(9)
4099DESCRIPTION VARCHAR2(30)
4100COST NUMBER(7,2)
4101PRICE NUMBER(7,2)
4102QUANTITY NUMBER
4103
4104You display the total of the extended costs for each product category by location.
4105You need to include only the products that have a price less than $25.00.
4106The extended cost of each item equals the quantity value multiplied by the cost value.
4107Which SQL statement will display the desired result?
4108
4109 Mark for Review
4110(1) Points
4111
4112 SELECT SUM(cost * quantity) TOTAL
4113FROM products
4114WHERE price < 25.00;
4115
4116 SELECT category_id, SUM(cost * quantity) TOTAL,location_id
4117FROM products
4118WHERE price > 25.00
4119GROUP BY category_id, location_id;
4120
4121 SELECT SUM(cost * quantity) TOTAL, location_id
4122FROM products
4123WHERE price < 25.00
4124GROUP BY location_id;
4125
4126 SELECT category_id, SUM(cost * quantity) TOTAL, location_id
4127FROM products
4128WHERE price < 25.00
4129GROUP BY category_id, location_id;
4130(*)
4131
41326. Evaluate this SELECT statement:
4133SELECT COUNT(employee_id), department_id
4134FROM employees
4135GROUP BY department_id;
4136
4137You only want to include employees who earn more than 15000.
4138Which clause should you include in the SELECT statement?
4139
4140 Mark for Review
4141(1) Points
4142
4143 HAVING salary > 15000
4144 HAVING SUM(salary) > 15000
4145 WHERE salary > 15000 (*)
4146 WHERE SUM(salary) > 15000
4147
41487. You use GROUPING functions to: Mark for Review
4149(1) Points
4150
4151 Produce subtotal and cross-tabulated values
4152 Identify the extra row values created by either a ROLLUP or CUBE operation (*)
4153 Aggregate rows using SUM, MIN, MAX, and COUNT
4154
41558. Examine the following statement:
4156SELECT department_id, manager_id, job_id, SUM(salary)
4157FROM employees
4158GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
4159
4160What data will this query generate?
4161
4162 Mark for Review
4163(1) Points
4164
4165 Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
4166 Sum of salaries for (department_id, job_id, manager_id)
4167 Subtotals for (job_id, manager_id)
4168 The statement will fail.
4169
41709. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
4171(1) Points
4172 SELECT
4173 GROUP BY (*)
4174 WHERE
4175
417610. You use ROLLUP to: Mark for Review
4177(1) Points
4178
4179 cross-tabulate values
4180 produce subtotal values (*)
4181 produce a single result set
4182
418311. GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False? Mark for Review
4184(1) Points
4185
4186 True (*)
4187 False
4188
418912. 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
4190(1) Points
4191
4192 ROLLUP
4193 GROUP BY ALL COLUMNS
4194 HAVING
4195 CUBE (*)
4196
419713. Which of the following are correct SET operators? (choose two) Mark for Review
4198(1) Points
4199 (Choose all correct answers)
4200 UNION, MINUS (*)
4201 UNION ALL, PLUS ALL
4202 MINUS, PLUS
4203 UNION ALL, INTERSECT (*)
4204
420514. The difference between UNION and UNION ALL is Mark for Review
4206(1) Points
4207
4208 There is no difference; you get exactly the same result from both.
4209 UNION ALL is more like a NATURAL JOIN.
4210 UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)
4211 UNION is a synomym for UNION ALL.
4212
421315. When using SET operators, the names of the matching columns must be identical in all of the SELECT statements used in the query. True or False? Mark for Review
4214(1) Points
4215
4216 True
4217 False (*)
4218
42191. How would you alter the following query to list only employees where two or more employees have the same last name?
4220SELECT last_name, COUNT(employee_id)
4221FROM EMPLOYEES
4222GROUP BY last_name;
4223
4224 Mark for Review
4225(1) Points
4226
4227 SELECT last_name, COUNT(employee_id)
4228FROM EMPLOYEES
4229WHERE COUNT(*) > 1
4230GROUP BY last_name
4231
4232 SELECT last_name, COUNT(last_name)
4233FROM EMPLOYEES
4234GROUP BY last_name
4235HAVING COUNT(last_name) > 1;
4236(*)
4237
4238 SELECT last_name, COUNT(last_name)
4239FROM EMPLOYEES
4240GROUP BY last_name
4241EXISTS COUNT(last_name) > 1;
4242
4243 SELECT employee_id, DISTINCT(last_name)
4244FROM EMPLOYEES
4245GROUP BY last_name
4246HAVING last_name > 1;
4247
42482. Which statement about the GROUP BY clause is true? Mark for Review
4249(1) Points
4250
4251 To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)
4252
4253 You can use a column alias in a GROUP BY clause.
4254 You must use the HAVING clause with the GROUP BY clause.
4255 By default, rows are not sorted when a GROUP BY clause is used.
4256
42573. The PRODUCTS table contains these columns:
4258PRODUCT_ID NUMBER(9) PK
4259CATEGORY_ID VARCHAR2(10)
4260LOCATION_ID NUMBER(9)
4261DESCRIPTION VARCHAR2(30)
4262COST NUMBER(7,2)
4263PRICE NUMBER(7,2)
4264QUANTITY NUMBER
4265
4266You display the total of the extended costs for each product category by location.
4267You need to include only the products that have a price less than $25.00.
4268The extended cost of each item equals the quantity value multiplied by the cost value.
4269Which SQL statement will display the desired result?
4270
4271 Mark for Review
4272(1) Points
4273
4274 SELECT SUM(cost * quantity) TOTAL, location_id
4275FROM products
4276WHERE price < 25.00
4277GROUP BY location_id;
4278
4279 SELECT category_id, SUM(cost * quantity) TOTAL,location_id
4280FROM products
4281WHERE price > 25.00
4282GROUP BY category_id, location_id;
4283
4284 SELECT category_id, SUM(cost * quantity) TOTAL, location_id
4285FROM products
4286WHERE price < 25.00
4287GROUP BY category_id, location_id;
4288(*)
4289
4290 SELECT SUM(cost * quantity) TOTAL
4291FROM products
4292WHERE price < 25.00;
4293
42944. Evaluate this SELECT statement:
4295SELECT SUM(salary), department_id, manager_id
4296FROM employees
4297GROUP BY department_id, manager_id;
4298
4299Which SELECT clause allows you to restrict the rows returned, based on a group function?
4300
4301 Mark for Review
4302(1) Points
4303
4304 HAVING salary > 100000
4305 HAVING SUM(salary) > 100000 (*)
4306 WHERE salary > 100000
4307 WHERE SUM(salary) > 100000
4308
43095. The PLAYERS and TEAMS tables contain these columns:
4310PLAYERS
4311PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
4312LAST_NAME VARCHAR2 (30) NOT NULL
4313FIRST_NAME VARCHAR2 (25) NOT NULL
4314TEAM_ID NUMBER
4315POSITION VARCHAR2 (25)
4316
4317TEAMS
4318TEAM_ID NUMBER NOT NULL, PRIMARY KEY
4319TEAM_NAME VARCHAR2 (25)
4320
4321You need to create a report that lists the names of each team with more than three goal keepers.
4322Which SELECT statement will produce the desired result?
4323
4324 Mark for Review
4325(1) Points
4326
4327 SELECT t.team_name, COUNT(p.player_id)
4328FROM players p, teams t
4329ON (p.team_id = t.team_id)
4330WHERE UPPER(p.position) = 'GOAL KEEPER'
4331GROUP BY t.team_name
4332HAVING COUNT(p.player_id) > 3;
4333
4334 SELECT t.team_name, COUNT(p.player_id)
4335FROM players p
4336JOIN teams t ON (p.team_id = t.team_id)
4337WHERE UPPER(p.position) = 'GOAL KEEPER'
4338GROUP BY t.team_name
4339HAVING COUNT(p.player_id) > 3;
4340(*)
4341
4342 SELECT t.team_name, COUNT(p.player_id)
4343FROM players
4344JOIN teams t ON (p.team_id = t.team_id)
4345WHERE UPPER(p.position) = 'GOAL KEEPER'
4346HAVING COUNT(p.player_id) > 3;
4347
4348 SELECT t.team_name, COUNT(p.player_id)
4349FROM players p, teams t
4350ON (p.team_id = t.team_id)
4351WHERE UPPER(p.position) = 'GOAL KEEPER'
4352GROUP BY t.team_name;
4353
43546. Evaluate this statement:
4355SELECT department_id, AVG(salary)
4356FROM employees
4357WHERE job_id <> 69879
4358GROUP BY job_id, department_id
4359HAVING AVG(salary) > 35000
4360ORDER BY department_id;
4361
4362Which clauses restricts the result? Choose two.
4363
4364 Mark for Review
4365(1) Points
4366
4367 (Choose all correct answers)
4368 WHERE job_id <> 69879 (*)
4369 SELECT department_id, AVG(salary)
4370 HAVING AVG(salary) > 35000 (*)
4371 GROUP BY job_id, department_id
4372
43737. Examine the following statement:
4374SELECT department_id, manager_id, job_id, SUM(salary)
4375FROM employees
4376GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
4377
4378What data will this query generate?
4379
4380 Mark for Review
4381(1) Points
4382
4383 Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
4384 Sum of salaries for (department_id, job_id, manager_id)
4385 Subtotals for (job_id, manager_id)
4386 The statement will fail.
4387
43888. You use GROUPING functions to ______ database rows from tabulated rows. Mark for Review
4389(1) Points
4390
4391 COMPUTE
4392 COUNT
4393 DISTINGUISH (*)
4394 CREATE
4395
43969. Examine the following statement:
4397SELECT department_id, manager_id, job_id, SUM(salary)
4398FROM employees
4399GROUP BY GROUPING SETS(.......);
4400
4401Select the correct GROUP BY GROUPING SETS clause from the following list:
4402
4403 Mark for Review
4404(1) Points
4405
4406 GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)
4407
4408 GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))
4409
4410 GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)
4411
4412 GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
4413
441410. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
4415(1) Points
4416
4417 True (*)
4418 False
4419
442011. GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False? Mark for Review
4421(1) Points
4422 True (*)
4423 False
4424
442512. 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
4426(1) Points
4427 HAVING
4428 ROLLUP
4429 CUBE (*)
4430 GROUP BY ALL COLUMNS
4431
443213. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
4433(1) Points
4434 MINUS
4435 UNION ALL
4436 UNION (*)
4437 INTERSECT
4438
443914. MINUS will give you rows from the first query that are not present in the second query. (True or False?) Mark for Review
4440(1) Points
4441 True (*)
4442 False
4443
444415. When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False. Mark for Review
4445(1) Points
4446 True (*)
4447
4448 False
4449
4450-----------------------
4451Section 10 Quiz
4452 (Answer all questions in this section)
44531. A multiple-row operator expects how many values? Mark for Review
4454(1) Points
4455 One or more (*)
4456 Only one
4457 Two or more
4458 None
4459
44602. The salary column of the f_staffs table contains the following values:
44614000
44625050
44636000
446411000
446523000
4466Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?
4467
4468 Mark for Review
4469(1) Points
4470
4471 SELECT last_name, first_name
4472FROM f_staffs
4473WHERE salary IN
4474(SELECT last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">
4475
4476 SELECT last_name, first_name
4477FROM f_staffs
4478WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000);
4479
4480 SELECT last_name, first_name
4481FROM f_staffs
4482WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);
4483(*)
4484
4485 SELECT last_name, first_name
4486FROM f_staffs
4487WHERE salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
4488
44893. Examine the data in the PAYMENT table:
4490
4491PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
449286590586 8908090 10-Jun-2003 BASIC 859.00
449389453485 8549038 15-Feb-2003 INTEREST 596.00
449485490345 5489304 20-Mar-2003 BASIC 568.00
4495This statement fails when executed:
4496
4497SELECT customer_id, payment_type
4498FROM payment
4499WHERE payment_id =
4500 (SELECT payment_id
4501 FROM payment
4502 WHERE payment_amount = 596.00 OR payment_date = '20-Mar-2003');
4503
4504Which change could correct the problem?
4505
4506 Mark for Review
4507(1) Points
4508 Change the outer query WHERE clause to 'WHERE payment_id IN'. (*)
4509 Remove the parentheses surrounding the nested SELECT statement.
4510 Change the comparison operator to a single-row operator.
4511 Remove the quotes surrounding the date value in the OR clause.
4512
45134. Which of the following statements contains a comparison operator that is used to restrict rows based on a list of values returned from an inner query? Mark for Review
4514(1) Points
4515
4516 SELECT description
4517FROM d_types
4518WHERE code
4519IN (SELECT type_code FROM d_songs);
4520
4521 SELECT description
4522FROM d_types
4523WHERE code = ANY (SELECT type_code FROM d_songs);
4524
4525 SELECT description
4526FROM d_types
4527WHERE code <> ALL (SELECT type_code FROM d_songs);
4528
4529 All of the above. (*)
4530
45315. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False? Mark for Review
4532(1) Points
4533
4534 True (*)
4535 False
4536
45376. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of which three comparison operators? Mark for Review
4538(1) Points
4539 IN, ANY, and EVERY
4540 IN, ALL, and EVERY
4541 IN, ANY, and EQUAL
4542 IN, ANY, and ALL (*)
4543
45447. Which statement about the <> operator is true? Mark for Review
4545(1) Points
4546 The <> operator can be used when a single-row subquery returns only one row. (*)
4547 The <> operator is NOT a valid SQL operator.
4548 The <> operator CANNOT be used in a single-row subquery.
4549 The <> operator returns the same result as the ANY operator in a subquery.
4550
45518. Single row subqueries may not include this operator: Mark for Review
4552(1) Points
4553 >
4554 <>
4555 ALL (*)
4556 =
4557
45589. Examine the structure of the EMPLOYEE, DEPARTMENT, and ORDERS tables.
4559EMPLOYEE:
4560EMPLOYEE_ID NUMBER(9)
4561LAST_NAME VARCHAR2(25)
4562FIRST_NAME VARCHAR2(25)
4563DEPARTMENT_ID NUMBER(9)
4564
4565DEPARTMENT:
4566DEPARTMENT_ID NUMBER(9)
4567DEPARTMENT_NAME VARCHAR2(25)
4568CREATION_DATE DATE
4569
4570ORDERS:
4571ORDER_ID NUMBER(9)
4572EMPLOYEE_ID NUMBER(9)
4573DATE DATE
4574CUSTOMER_ID NUMBER(9)
4575
4576You want to display all employees who had an order after the Sales department was established. Which of the following constructs would you use?
4577
4578 Mark for Review
4579(1) Points
4580 The HAVING clause
4581 A group function
4582 A single-row subquery (*)
4583 A MERGE statement
4584
458510. If you use the equality operator (=) with a subquery, how many values can the subquery return? Mark for Review
4586(1) Points
4587 Only 1 (*)
4588 Up to 2
4589 Up to 5
4590 Unlimited
4591
459211. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
4593TEACHERS
4594TEACHER_ID NUMBER(5) Primary Key
4595NAME VARCHAR2 (25)
4596SUBJECT_ID NUMBER(5)
4597CLASS_ID NUMBER(5)
4598
4599CLASS_ASSIGNMENTS
4600CLASS_ID NUMBER (5) Primary Key
4601TEACHER_ID NUMBER (5)
4602DATE
4603MAX_CAPACITY NUMBER (3)
4604
4605All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)
4606
4607 Mark for Review
4608(1) Points
4609
4610 (Choose all correct answers)
4611
4612 SELECT *
4613FROM teachers
4614WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);
4615
4616 SELECT *
4617FROM teachers
4618WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
4619(*)
4620
4621 SELECT *
4622FROM class_assignments
4623max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
4624
4625 SELECT *
4626FROM teachers
4627WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
4628
4629 SELECT *
4630FROM class_assignments
4631WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
4632(*)
4633
463412. Which operator can be used with a multiple-row subquery? Mark for Review
4635(1) Points
4636
4637 LIKE
4638 IN (*)
4639 =
4640 <>
4641
464213. Which answer is INCORRECT? The parent statement of a correlated subquery can be: Mark for Review
4643(1) Points
4644 A SELECT statement
4645 A DELETE statement
4646 An UPDATE statement
4647 An INSERT statement (*)
4648
464914. Oracle allows you to write named subqueries in one single statement, as long as you start your statement with the keyword WITH. True or False? Mark for Review
4650(1) Points
4651 True (*)
4652 False
4653
465415. The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False? Mark for Review
4655(1) Points
4656 True (*)
4657 False
4658
46591. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False? Mark for Review
4660(1) Points
4661 True
4662 False (*)
4663
46642. Subqueries are limited to four per SQL transaction. True or False? Mark for Review
4665(1) Points
4666 True
4667 False (*)
4668
46693. Which statement about the <> operator is true? Mark for Review
4670(1) Points
4671 The <> operator can be used when a single-row subquery returns only one row. (*)
4672 The <> operator is NOT a valid SQL operator.
4673 The <> operator CANNOT be used in a single-row subquery.
4674 The <> operator returns the same result as the ANY operator in a subquery.
4675
46764. Which statement about subqueries is true? Mark for Review
4677(1) Points
4678 Subqueries should be enclosed in double quotation marks.
4679 Subqueries generally execute last, after the main or outer query executes.
4680 Subqueries cannot contain group functions.
4681 Subqueries are often used in a WHERE clause to return values for an unknown conditional value. (*)
4682
46835. You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use? Mark for Review
4684(1) Points
4685
4686 SELECT product_name
4687FROM products
4688WHERE cost > AVG(cost);
4689
4690 SELECT product_name
4691FROM products
4692WHERE cost > (SELECT AVG(cost)
4693FROM products);
4694(*)
4695
4696 SELECT AVG(cost), product_name
4697FROM products
4698WHERE cost > AVG(cost)
4699GROUP by product_name;
4700
4701 SELECT product_name
4702FROM (SELECT AVG(cost) FROM product)
4703WHERE cost > AVG(cost);
4704
47056. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
4706TEACHERS
4707TEACHER_ID NUMBER(5) Primary Key
4708NAME VARCHAR2 (25)
4709SUBJECT_ID NUMBER(5)
4710CLASS_ID NUMBER(5)
4711
4712CLASS_ASSIGNMENTS
4713CLASS_ID NUMBER (5) Primary Key
4714TEACHER_ID NUMBER (5)
4715DATE
4716MAX_CAPACITY NUMBER (3)
4717
4718All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)
4719
4720 Mark for Review
4721(1) Points
4722
4723 (Choose all correct answers)
4724
4725 SELECT *
4726FROM class_assignments
4727max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
4728
4729 SELECT *
4730FROM teachers
4731WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
4732(*)
4733
4734 SELECT *
4735FROM teachers
4736WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);
4737
4738 SELECT *
4739FROM class_assignments
4740WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
4741(*)
4742
4743 SELECT *
4744FROM teachers
4745WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
4746
47477. When creating a report of all employees earning more than the average salary for their department, a __________ ____________ can be used to first calculate the average salary of each department, and then compare the salary for each employee to the average salary of that employeeﾒs department. Mark for Review
4748(1) Points
4749 WITH CLAUSE
4750 GROUP BY
4751 CORRELATED SUBQUERY (*)
4752
47538. Which statement is false? Mark for Review
4754(1) Points
4755 The WITH clause retrieves the results of one or more query blocks.
4756 The WITH clause stores the results for the user who runs the query.
4757 The WITH clause decreases performance. (*)
4758 The WITH clause makes the query simple to read.
4759
47609. Correlated Subqueries must reference the same tables in both the inner and outer queries. (True or False?) Mark for Review
4761(1) Points
4762 True
4763 False (*)
4764
476510. 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: Mark for Review
4766(1) Points
4767 All rows that were selected by the inner query including the null values
4768 No rows returned (*)
4769 All rows that were selected by the inner query minus the null values
4770 A list of Nulls
4771
477211. Examine the structures of the PARTS and MANUFACTURERS tables:
4773PARTS:
4774PARTS_ID VARCHAR2(25) PK
4775PARTS_NAME VARCHAR2(50)
4776MANUFACTURERS_ID NUMBER
4777COST NUMBER(5,2)
4778PRICE NUMBER(5,2)
4779
4780MANUFACTURERS:
4781ID NUMBER PK
4782NAME VARCHAR2(30)
4783LOCATION VARCHAR2(20)
4784
4785Assume that the tables have been populated with data including 100 rows in the PARTS table, and 20 rows in the MANUFACTURERS table. Which SQL statement correctly uses a subquery?
4786
4787 Mark for Review
4788(1) Points
4789
4790 SELECT parts_name, price, cost
4791FROM parts
4792WHERE manufacturers_id !=
4793 (SELECT id
4794 FROM manufacturers
4795 WHERE LOWER(name) = 'cost plus');
4796
4797 SELECT parts_name, price, cost
4798FROM parts
4799WHERE manufacturers_id IN
4800 (SELECT id
4801 FROM manufacturers m
4802 JOIN parts p
4803 ON (m.id = p.manufacturers_id));
4804(*)
4805
4806 UPDATE parts SET price = price * 1.15
4807WHERE manufacturers_id =
4808 (SELECT id
4809 FROM manufacturers
4810 WHERE UPPER(location) IN("ATLANTA", "BOSTON", "DALLAS"));
4811
4812 SELECT parts_name
4813FROM (SELECT AVG(cost) FROM manufacturers)
4814WHERE cost > AVG(cost);
4815
481612. You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use? Mark for Review
4817(1) Points
4818 =, <, and >
4819 IN, ANY, and ALL (*)
4820 LIKE
4821 BETWEENﾅANDﾅ
4822
482313. You are looking for Executive information using a subquery.
4824What will the following SQL statement display?
4825SELECT department_id, last_name, job_id
4826FROM employees
4827WHERE department_id IN
4828 (SELECT department_id FROM departments WHERE department_name = 'Executive');
4829
4830 Mark for Review
4831(1) Points
4832
4833 The department ID, department name, and last name for every employee in the Executive department
4834
4835 The department ID, last name, and department name for every Executive in the employees table
4836
4837 The department ID, last name, and job ID from departments for Executive employees
4838 The department ID, last name, and job ID for every employee in the Executive department (*)
4839
484014. Evaluate the structure of the EMPLOYEES and DEPART_HIST tables:
4841EMPLOYEES
4842EMPLOYEE_ID NUMBER(9)
4843LAST_NAME VARCHAR2(25)
4844FIRST_NAME VARCHAR2(25)
4845DEPARTMENT_ID NUMBER(9)
4846MANAGER_ID NUMBER(9)
4847SALARY NUMBER(7,2)
4848
4849DEPART_HIST:
4850EMPLOYEE_ID NUMBER(9)
4851OLD_DEPT_ID NUMBER(9)
4852NEW_DEPT_ID NUMBER(9)
4853CHANGE_DATE DATE
4854
4855You want to generate a list of employees who are in department 10, but used to be in department 15. Which query should you use?
4856
4857 Mark for Review
4858(1) Points
4859
4860 SELECT employee_id, last_name, first_name, department_id
4861FROM employees
4862WHERE (employee_id, department_id) =
4863 (SELECT employee_id, new_dept_id
4864 FROM depart_hist
4865 WHERE new_dept_id = 15);
4866
4867 SELECT employee_id, last_name, first_name, department_id
4868FROM employees
4869WHERE (employee_id, department_id) IN
4870 (SELECT employee_id, dept_id
4871 FROM employees
4872 WHERE old_dept_id = 15);
4873
4874 SELECT employee_id, last_name, first_name, department_id
4875FROM employees
4876WHERE (employee_id) IN
4877 (SELECT employee_id
4878 FROM employee_hist
4879 WHERE old_dept_id = 15);
4880
4881 SELECT employee_id, last_name, first_name, department_id
4882FROM employees
4883WHERE (employee_id, department_id) IN
4884 (SELECT employee_id, new_dept_id
4885 FROM depart_hist
4886 WHERE old_dept_id = 15) AND new_dept_id = 10;
4887(*)
4888
488915. Which of the following best describes the meaning of the ANY operator? Mark for Review
4890(1) Points
4891 Equal to any member in the list
4892 Equal to each value in the list
4893 Compare value to the first value returned by the subquery
4894 Compare value to each value returned by the subquery (*)
4895
48961. Table aliases must be used when you are writing correlated subqueries. (True or false?) Mark for Review
4897(1) Points
4898 True
4899 False (*)
4900
49012. Oracle allows you to write named subqueries in one single statement, as long as you start your statement with the keyword WITH. True or False? Mark for Review
4902(1) Points
4903 True (*)
4904 False
4905
49063. Which statement is false? Mark for Review
4907(1) Points
4908 The WITH clause decreases performance. (*)
4909 The WITH clause makes the query simple to read.
4910 The WITH clause stores the results for the user who runs the query.
4911 The WITH clause retrieves the results of one or more query blocks.
4912
49134. Subqueries can only be placed in the WHERE clause. True or False? Mark for Review
4914(1) Points
4915 True
4916 False (*)
4917
49185. Which operator can be used with a multiple-row subquery? Mark for Review
4919(1) Points
4920 IN (*)
4921 LIKE
4922 <>
4923 =
4924
49256. Using a subquery in which clause will return a syntax error? Mark for Review
4926(1) Points
4927 WHERE
4928 FROM
4929 HAVING
4930 You can use subqueries in all of the above clauses. (*)
4931
49327. Which comparison operator can only be used with a single-row subquery? Mark for Review
4933(1) Points
4934 IN
4935 <> (*)
4936 ANY
4937 ALL
4938
49398. Subqueries are limited to four per SQL transaction. True or False? Mark for Review
4940(1) Points
4941 True
4942 False (*)
4943
49449. You need to produce a report that contains all employee-related information for those employees who have Brad Carter as a supervisor. However, you are not sure which supervisor ID belongs to Brad Carter. Which query should you issue to accomplish this task? Mark for Review
4945(1) Points
4946
4947 SELECT *
4948FROM employees
4949WHERE supervisor_id = (SELECT supervisor_id
4950 FROM employees
4951 WHERE last_name = 'Carter');
4952
4953 SELECT *
4954FROM supervisors
4955WHERE supervisor_id =
4956 (SELECT supervisor_id
4957 FROM employees
4958 WHERE last_name = 'Carter');
4959
4960 SELECT *
4961FROM employees
4962WHERE supervisor_id =
4963 (SELECT employee_id
4964 FROM employees
4965 WHERE last_name = 'Carter');
4966(*)
4967
4968 SELECT *
4969FROM supervisors
4970WHERE supervisor_id =
4971 (SELECT employee_id
4972 FROM supervisors
4973 WHERE last_name = 'Carter');
4974
497510. Which operator or keyword cannot be used with a multiple-row subquery? Mark for Review
4976(1) Points
4977 >
4978 = (*)
4979 ANY
4980 ALL
4981
498211. 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: Mark for Review
4983(1) Points
4984 A list of Nulls
4985 No rows returned (*)
4986 All rows that were selected by the inner query including the null values
4987 All rows that were selected by the inner query minus the null values
4988
498912. Evaluate this SQL statement:
4990SELECT employee_id, last_name, salary
4991FROM employees
4992WHERE department_id IN
4993 (SELECT department_id
4994 FROM employees
4995 WHERE salary > 30000 AND salary < 50000);
4996
4997Which values will be displayed?
4998
4999 Mark for Review
5000(1) Points
5001 Only employees who earn more than $30,000.
5002
5003 All employees who work in a department with employees who earn more than $30,000 and more than $50,000.
5004
5005 All employees who work in a department with employees who earn more than $30,000, but less than $50,000. (*)
5006
5007 Only employees who earn less than $50,000.
5008
500913. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False? Mark for Review
5010(1) Points
5011 True (*)
5012 False
5013
501414. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of which three comparison operators? Mark for Review
5015(1) Points
5016 IN, ANY, and ALL (*)
5017 IN, ANY, and EVERY
5018 IN, ALL, and EVERY
5019 IN, ANY, and EQUAL
5020
502115. Evaluate the structure of the EMPLOYEES and DEPART_HIST tables:
5022EMPLOYEES
5023EMPLOYEE_ID NUMBER(9)
5024LAST_NAME VARCHAR2(25)
5025FIRST_NAME VARCHAR2(25)
5026DEPARTMENT_ID NUMBER(9)
5027MANAGER_ID NUMBER(9)
5028SALARY NUMBER(7,2)
5029
5030DEPART_HIST:
5031EMPLOYEE_ID NUMBER(9)
5032OLD_DEPT_ID NUMBER(9)
5033NEW_DEPT_ID NUMBER(9)
5034CHANGE_DATE DATE
5035
5036You want to generate a list of employees who are in department 10, but used to be in department 15. Which query should you use?
5037
5038 Mark for Review
5039(1) Points
5040
5041 SELECT employee_id, last_name, first_name, department_id
5042FROM employees
5043WHERE (employee_id, department_id) =
5044 (SELECT employee_id, new_dept_id
5045 FROM depart_hist
5046 WHERE new_dept_id = 15);
5047
5048 SELECT employee_id, last_name, first_name, department_id
5049FROM employees
5050WHERE (employee_id, department_id) IN
5051 (SELECT employee_id, dept_id
5052 FROM employees
5053 WHERE old_dept_id = 15);
5054
5055 SELECT employee_id, last_name, first_name, department_id
5056FROM employees
5057WHERE (employee_id, department_id) IN
5058 (SELECT employee_id, new_dept_id
5059 FROM depart_hist
5060 WHERE old_dept_id = 15) AND new_dept_id = 10;
5061(*)
5062
5063 SELECT employee_id, last_name, first_name, department_id
5064FROM employees
5065WHERE (employee_id) IN
5066 (SELECT employee_id
5067 FROM employee_hist
5068 WHERE old_dept_id = 15);
5069
50701. The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False? Mark for Review
5071(1) Points
5072 True (*)
5073 False
5074
50752. The WITH clause is a way of creating extra tables in the database. (True or False?) Mark for Review
5076(1) Points
5077 True
5078 False (*)
5079
50803. In a correlated subquery, the outer and inner queries are joined on one or more columns. (True or False?) Mark for Review
5081(1) Points
5082 True (*)
5083 False
5084
50854. If a single-row subquery returns a null value and uses the equality comparison operator, what will the outer query return? Mark for Review
5086(1) Points
5087
5088 All the rows in the table
5089 No rows (*)
5090 A null value
5091 An error
5092
50935. Examine the structure of the EMPLOYEE, DEPARTMENT, and ORDERS tables.
5094EMPLOYEE:
5095EMPLOYEE_ID NUMBER(9)
5096LAST_NAME VARCHAR2(25)
5097FIRST_NAME VARCHAR2(25)
5098DEPARTMENT_ID NUMBER(9)
5099
5100DEPARTMENT:
5101DEPARTMENT_ID NUMBER(9)
5102DEPARTMENT_NAME VARCHAR2(25)
5103CREATION_DATE DATE
5104
5105ORDERS:
5106ORDER_ID NUMBER(9)
5107EMPLOYEE_ID NUMBER(9)
5108DATE DATE
5109CUSTOMER_ID NUMBER(9)
5110
5111You want to display all employees who had an order after the Sales department was established. Which of the following constructs would you use?
5112
5113 Mark for Review
5114(1) Points
5115 A MERGE statement
5116 A group function
5117 The HAVING clause
5118 A single-row subquery (*)
5119
51206. Single row subqueries may not include this operator: Mark for Review
5121(1) Points
5122 =
5123 >
5124 <>
5125 ALL (*)
5126
51277. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of which three comparison operators? Mark for Review
5128(1) Points
5129 IN, ANY, and EQUAL
5130 IN, ANY, and ALL (*)
5131 IN, ALL, and EVERY
5132 IN, ANY, and EVERY
5133
51348. Which of the following best describes the meaning of the ANY operator? Mark for Review
5135(1) Points
5136 Equal to each value in the list
5137 Equal to any member in the list
5138 Compare value to the first value returned by the subquery
5139 Compare value to each value returned by the subquery (*)
5140
51419. Which comparison operator would you use to compare a value to every value returned by a subquery? Mark for Review
5142(1) Points
5143 IN
5144 ANY
5145 SOME
5146 ALL (*)
5147
514810. A multiple-row operator expects how many values? Mark for Review
5149(1) Points
5150 One or more (*)
5151 Only one
5152 Two or more
5153 None
5154
515511. Evaluate this SELECT statement:
5156SELECT player_id, name
5157FROM players
5158WHERE team_id IN
5159 (SELECT team_id
5160 FROM teams
5161 WHERE team_id > 300 AND salary_cap > 400000);
5162
5163What would happen if the inner query returned a NULL value?
5164
5165 Mark for Review
5166(1) Points
5167 A syntax error in the outer query would be returned.
5168 No rows would be returned by the outer query. (*)
5169 A syntax error in the inner query would be returned.
5170 All the rows in the PLAYER table would be returned by the outer query.
5171
517212. You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use? Mark for Review
5173(1) Points
5174 LIKE
5175 BETWEENﾅANDﾅ
5176 IN, ANY, and ALL (*)
5177 =, <, and >
5178
517913. You need to display all the players whose salaries are greater than or equal to John Brown's salary. Which comparison operator should you use? Mark for Review
5180(1) Points
5181 =
5182 <=
5183 >= (*)
5184 >
5185
518614. What will the following statement return:
5187SELECT last_name, salary
5188FROM employees
5189WHERE salary < (SELECT salary
5190 FROM employees
5191 WHERE employee_id = 103);
5192
5193 Mark for Review
5194(1) Points
5195 A list of last_names and salaries of employees who make more than employee 103
5196 A list of last_names and salaries of employees who make less than employee 103 (*)
5197 A list of first_names and salaries of employees making less than employee 103
5198 Nothing. It is an invalid statement.
5199
520015. Which of the following is TRUE regarding the order of subquery execution? Mark for Review
5201(1) Points
5202 The subquery executes once after the main query.
5203 The result of the main query is used with the subquery.
5204 The subquery executes once before the main query. (*)
5205 The outer query is executed first.
5206
52071. Examine the structure of the EMPLOYEE, DEPARTMENT, and ORDERS tables.
5208EMPLOYEE:
5209EMPLOYEE_ID NUMBER(9)
5210LAST_NAME VARCHAR2(25)
5211FIRST_NAME VARCHAR2(25)
5212DEPARTMENT_ID NUMBER(9)
5213
5214DEPARTMENT:
5215DEPARTMENT_ID NUMBER(9)
5216DEPARTMENT_NAME VARCHAR2(25)
5217CREATION_DATE DATE
5218
5219ORDERS:
5220ORDER_ID NUMBER(9)
5221EMPLOYEE_ID NUMBER(9)
5222DATE DATE
5223CUSTOMER_ID NUMBER(9)
5224
5225You want to display all employees who had an order after the Sales department was established. Which of the following constructs would you use?
5226
5227 Mark for Review
5228(1) Points
5229 A group function
5230 The HAVING clause
5231 A single-row subquery (*)
5232 A MERGE statement
5233
52342. If a single-row subquery returns a null value and uses the equality comparison operator, what will the outer query return? Mark for Review
5235(1) Points
5236 No rows (*)
5237 A null value
5238 All the rows in the table
5239 An error
5240
52413. The result of this statement will be:
5242SELECT last_name, job_id, salary, department_id
5243FROM employees
5244WHERE job_id =
5245 (SELECT job_id
5246 FROM employees
5247 WHERE employee_id = 141) AND
5248 department_id =
5249 (SELECT department_id
5250 FROM departments
5251 WHERE location_id =1500);
5252 Mark for Review
5253(1) Points
5254 Only the employees whose job id matches employee 141 and who work in location 1500 (*)
5255 All employees from Location 1500 will be displayed
5256 All employees with the department id of 141
5257 An error since you canﾒt get data from two tables in the same subquery
5258
52594. Which answer is INCORRECT? The parent statement of a correlated subquery can be: Mark for Review
5260(1) Points
5261 A SELECT statement
5262 An UPDATE statement
5263 A DELETE statement
5264 An INSERT statement (*)
5265
52665. Table aliases must be used when you are writing correlated subqueries. (True or false?) Mark for Review
5267(1) Points
5268 True
5269 False (*)
5270
52716. 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
5272(1) Points
5273 True (*)
5274 False
5275
52767. Which of the following statements is a true guideline for using subqueries? Mark for Review
5277(1) Points
5278
5279 The outer and inner queries can reference more than one table. They can get data from different tables. (*)
5280
5281 Do not enclose the subquery in parentheses.
5282
5283 Only one WHERE clause can be used for a SELECT statement, and if specified, it must be the outer query.
5284
5285 Place the subquery on the left side of the comparison condition.
5286
52878. You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use? Mark for Review
5288(1) Points
5289
5290 SELECT product_name
5291FROM (SELECT AVG(cost) FROM product)
5292WHERE cost > AVG(cost);
5293
5294 SELECT AVG(cost), product_name
5295FROM products
5296WHERE cost > AVG(cost)
5297GROUP by product_name;
5298
5299 SELECT product_name
5300FROM products
5301WHERE cost > (SELECT AVG(cost)
5302FROM products);
5303(*)
5304
5305 SELECT product_name
5306FROM products
5307WHERE cost > AVG(cost);
5308
53099. You need to display all the players whose salaries are greater than or equal to John Brown's salary. Which comparison operator should you use? Mark for Review
5310(1) Points
5311 >= (*)
5312 =
5313 <=
5314 >
5315
531610. Evaluate this SELECT statement:
5317SELECT customer_id, name
5318FROM customer
5319WHERE customer_id IN
5320 (SELECT customer_id
5321 FROM customer
5322 WHERE state_id = 'GA' AND credit_limit > 500.00);
5323
5324What would happen if the inner query returned null?
5325
5326 Mark for Review
5327(1) Points
5328 Only the rows with CUSTOMER_ID values equal to null would be selected.
5329 No rows would be returned by the outer query. (*)
5330 An error would be returned.
5331 All the rows in the table would be selected.
5332
533311. Which statement about the ANY operator, when used with a multiple-row subquery, is true? Mark for Review
5334(1) Points
5335 The ANY operator is a synonym for the ALL operator.
5336 The ANY operator can be used with the LIKE and IN operators.
5337 The ANY operator compares every value returned by the subquery. (*)
5338 The ANY operator can be used with the DISTINCT keyword.
5339
534012. Multiple-row subqueries must have NOT, IN, or ANY in the WHERE clause of the inner query. True or False? Mark for Review
5341(1) Points
5342 True
5343 False (*)
5344
534513. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False? Mark for Review
5346(1) Points
5347 True (*)
5348 False
534914. The salary column of the f_staffs table contains the following values:
53504000
53515050
53526000
535311000
535423000
5355Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?
5356
5357 Mark for Review
5358(1) Points
5359
5360
5361 SELECT last_name, first_name
5362FROM f_staffs
5363WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000);
5364
5365
5366 SELECT last_name, first_name
5367FROM f_staffs
5368WHERE salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
5369
5370
5371 SELECT last_name, first_name
5372FROM f_staffs
5373WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);
5374(*)
5375
5376
5377
5378 SELECT last_name, first_name
5379FROM f_staffs
5380WHERE salary IN
5381(SELECT last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">
5382
5383
5384
5385Correct Correct
5386
5387
5388 15. You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use? Mark for Review
5389(1) Points
5390
5391
5392 LIKE
5393
5394
5395 IN, ANY, and ALL (*)
5396
5397
5398 =, <, and >
5399
5400
5401 BETWEENﾅANDﾅ