· 8 years ago · Jan 28, 2018, 11:42 AM
1
2
3
4
5
6
7
8Test: Section 12 Quiz
9
10
11
12
13
14
15
16
17
18Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
19
20
21
22 Section 12 Quiz
23 (Answer all questions in this section)
24
25 1. Aliases can be used with MERGE statements. True or False? Mark for Review
26(1) Points
27
28
29 True (*)
30
31
32 False
33
34
35
36 Correct
37
38
39 2. The MERGE statement first tries to update one or more rows in a table that match the criteria; if no row matches the criteria for the update, a new row will automatically be inserted instead. True or False? Mark for Review
40(1) Points
41
42
43 True (*)
44
45
46 False
47
48
49
50 Correct
51
52
53 3. A column in a table can be given a default value. This option prevents NULL values from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False ? Mark for Review
54(1) Points
55
56
57 True (*)
58
59
60 False
61
62
63
64 Correct
65
66
67 4. Which statement below will not insert a row of data into a table? Mark for Review
68(1) Points
69
70
71 INSERT INTO (id, lname, fname, lunch_num)
72 VALUES (143354, 'Roberts', 'Cameron', 6543);
73(*)
74
75
76
77 INSERT INTO student_table
78 VALUES (143354, 'Roberts', 'Cameron', 6543);
79
80
81
82 INSERT INTO student_table (id, lname, fname, lunch_num)
83 VALUES (143354, 'Roberts', 'Cameron', 6543);
84
85
86
87 INSERT INTO student_table (id, lname, fname, lunch_num)
88 VALUES (143352, 'Roberts', 'Cameron', DEFAULT);
89
90
91
92
93 Correct
94
95
96 5. A multi-table insert statement can insert into more than one table. (True or False?) Mark for Review
97(1) Points
98
99
100 True (*)
101
102
103 False
104
105
106
107 Correct
108
109
110
111
112 Page 1 of 3 Next Summary
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128Test: Section 12 Quiz
129
130
131
132
133
134
135
136
137
138Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
139
140
141
142 Section 12 Quiz
143 (Answer all questions in this section)
144
145 6. One employee has the last name of 'King' in the employees table. How many rows will be deleted from the employees table with the following statement?
146DELETE FROM employees
147 WHERE last_name = 'king';
148 Mark for Review
149(1) Points
150
151
152 One will be deleted, as there exists one employee named King.
153
154
155 All the rows in the employees table will be deleted.
156
157
158 All rows with last_name = 'King' will be deleted.
159
160
161 No rows will be deleted, as no employees match the WHERE-clause. (*)
162
163
164
165 Correct
166
167
168 7. The PLAYERS table contains these columns:
169PLAYER_ID NUMBER NOT NULL
170 PLAYER_LNAME VARCHAR2(20) NOT NULL
171 PLAYER_FNAME VARCHAR2(10) NOT NULL
172 TEAM_ID NUMBER
173 SALARY NUMBER(9,2)
174
175You need to increase the salary of each player for all players on the Tiger team by 12.5 percent. The TEAM_ID value for the Tiger team is 5960. Which statement should you use?
176 Mark for Review
177(1) Points
178
179
180 UPDATE players (salary)
181 SET salary = salary * 1.125;
182
183
184
185 UPDATE players
186 SET salary = salary * .125
187 WHERE team_id = 5960;
188
189
190
191 UPDATE players
192 SET salary = salary * 1.125
193 WHERE team_id = 5960;
194(*)
195
196
197
198 UPDATE players (salary)
199 VALUES(salary * 1.125)
200 WHERE team_id = 5960;
201
202
203
204
205 Incorrect. Refer to Section 12 Lesson 2.
206
207
208 8. You need to update the expiration date of products manufactured before June 30th . In which clause of the UPDATE statement will you specify this condition? Mark for Review
209(1) Points
210
211
212 The SET clause
213
214
215 The USING clause
216
217
218 The ON clause
219
220
221 The WHERE clause (*)
222
223
224
225 Correct
226
227
228 9. When the WHERE clause is missing in a DELETE statement, what is the result? Mark for Review
229(1) Points
230
231
232 Nothing. The statement will not execute.
233
234
235 All rows are deleted from the table. (*)
236
237
238 An error message is displayed indicating incorrect syntax.
239
240
241 The table is removed from the database.
242
243
244
245 Correct
246
247
248 10. If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery? (True or False) Mark for Review
249(1) Points
250
251
252 True
253
254
255 False (*)
256
257
258
259 Correct
260
261
262
263
264 Previous Page 2 of 3 Next Summary
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280Test: Section 12 Quiz
281
282
283
284
285
286
287
288
289
290Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
291
292
293
294 Section 12 Quiz
295 (Answer all questions in this section)
296
297 11. Which statement about the VALUES clause of an INSERT statement is true? Mark for Review
298(1) Points
299
300
301 If no column list is specified, the values must be listed in the same order that the columns are listed in the table. (*)
302
303
304 Character, date, and numeric data must be enclosed within single quotes in the VALUES clause.
305
306
307 The VALUES clause in an INSERT statement is mandatory in a subquery.
308
309
310 To specify a null value in the VALUES clause, use an empty string (" ").
311
312
313
314 Correct
315
316
317 12. Which of the following statements will add a new customer to the customers table in the Global Fast Foods database? Mark for Review
318(1) Points
319
320
321 INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
322 VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);
323(*)
324
325
326
327 INSERT IN customers (id, first_name, last_name, address, city, state, zip, phone_number);
328
329
330
331 INSERT INTO customers
332 (id 145, first_name 'Katie', last_name 'Hernandez', address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number 8586667641);
333
334
335
336 INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
337 VALUES ("145", 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', "98008", "8586667641");
338
339
340
341
342 Correct
343
344
345 13. If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
346INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
347 SELECT employee_id, first_name, last_name, salary, department_id
348 FROM employees
349
350 Mark for Review
351(1) Points
352
353
354 No rows, as the SELECT statement is invalid.
355
356
357 10 rows will be created.
358
359
360 No rows, as you cannot use subqueries in an insert statement.
361
362
363 7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)
364
365
366
367 Correct
368
369
370 14. When inserting rows into a table, all columns must be given values. True or False? Mark for Review
371(1) Points
372
373
374 True
375
376
377 False (*)
378
379
380
381 Correct
382
383
384 15. You need to add a row to an existing table. Which DML statement should you use? Mark for Review
385(1) Points
386
387
388 UPDATE
389
390
391 INSERT (*)
392
393
394 DELETE
395
396
397 CREATE
398
399
400
401 Correct
402
403
404
405
406 Previous Page 3 of 3 Summary
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422Test: Section 13 Quiz
423
424
425
426
427
428
429
430
431
432Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
433
434
435
436 Section 13 Quiz
437 (Answer all questions in this section)
438
439 1. After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False? Mark for Review
440(1) Points
441
442
443 True (*)
444
445
446 False
447
448
449
450 Correct
451
452
453 2. You can use the ALTER TABLE statement to: Mark for Review
454(1) Points
455
456
457 Add a new column
458
459
460 Modify an existing column
461
462
463 Drop a column
464
465
466 All of the above (*)
467
468
469
470 Correct
471
472
473 3. The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False? Mark for Review
474(1) Points
475
476
477 True
478
479
480 False (*)
481
482
483
484 Correct
485
486
487 4. The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False? Mark for Review
488(1) Points
489
490
491 True
492
493
494 False (*)
495
496
497
498 Correct
499
500
501 5. The TEAMS table contains these columns:
502TEAM_ID NUMBER(4) Primary Key
503 TEAM_NAME VARCHAR2(20)
504 MGR_ID NUMBER(9)
505
506The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
507 Mark for Review
508(1) Points
509
510
511 ALTER teams
512 MODIFY (mgr_id VARCHAR2(15));
513
514
515
516 ALTER teams TABLE
517 MODIFY COLUMN (mgr_id VARCHAR2(15));
518
519
520
521 You CANNOT modify the data type of the MGR_ID column.
522
523
524 ALTER TABLE teams
525 MODIFY (mgr_id VARCHAR2(15));
526(*)
527
528
529
530 ALTER TABLE teams
531 REPLACE (mgr_id VARCHAR2(15));
532
533
534
535
536 Correct
537
538
539
540
541 Page 1 of 3 Next Summary
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557Test: Section 13 Quiz
558
559
560
561
562
563
564
565
566
567Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
568
569
570
571 Section 13 Quiz
572 (Answer all questions in this section)
573
574 6. Evaluate this CREATE TABLE statement:
575CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
576
577You are a member of the SYSDBA role, but are logged in under your own schema. You issue this CREATE TABLE statement. Which statement is true?
578 Mark for Review
579(1) Points
580
581
582 You created the table in your schema. (*)
583
584
585 You created the LINE_ITEM table in the SYS schema.
586
587
588 You created the table in the SYSDBA schema.
589
590
591 You created the LINE_ITEM table in the public schema.
592
593
594
595 Correct
596
597
598 7. Which statement about creating a table is true? Mark for Review
599(1) Points
600
601
602 If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE statement will fail.
603
604
605 If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.
606
607
608 With a CREATE TABLE statement, a table will always be created in the current user's schema.
609
610
611 If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)
612
613
614
615 Correct
616
617
618 8. Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth? Mark for Review
619(1) Points
620
621
622 CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);
623
624
625 CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);
626
627
628 CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)
629
630
631 CREATE table BIRTHDAYS (employee number, name, date of birth);
632
633
634
635 Correct
636
637
638 9. You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create? Mark for Review
639(1) Points
640
641
642 PRODUCTS_2001 (*)
643
644
645 2001_PRODUCTS
646
647
648 PRODUCTS--2001
649
650
651 PRODUCTS_(2001)
652
653
654
655 Correct
656
657
658 10. Evaluate this CREATE TABLE statement:
6591. CREATE TABLE customer#1 (
660 2. cust_1 NUMBER(9),
661 3. sales$ NUMBER(9),
662 4. 2date DATE DEFAULT SYSDATE);
663
664Which line of this statement will cause an error?
665 Mark for Review
666(1) Points
667
668
669 4 (*)
670
671
672 2
673
674
675 1
676
677
678 3
679
680
681
682 Correct
683
684
685
686
687 Previous Page 2 of 3 Next Summary
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703Test: Section 13 Quiz
704
705
706
707
708
709
710
711
712
713Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
714
715
716
717 Section 13 Quiz
718 (Answer all questions in this section)
719
720 11. You need to store the SEASONAL data in months and years. Which data type should you use? Mark for Review
721(1) Points
722
723
724 INTERVAL YEAR TO MONTH (*)
725
726
727 TIMESTAMP
728
729
730 INTERVAL DAY TO SECOND
731
732
733 DATE
734
735
736
737 Correct
738
739
740 12. The SPEED_TIME column should store a fractional second value.
741Which data type should you use?
742 Mark for Review
743(1) Points
744
745
746 INTERVAL DAY TO SECOND
747
748
749 DATETIME
750
751
752 DATE
753
754
755 TIMESTAMP (*)
756
757
758
759 Correct
760
761
762 13. The ELEMENTS column is defined as:
763 NUMBER(6,4)
764How many digits to the right of the decimal point are allowed for the ELEMENTS column?
765 Mark for Review
766(1) Points
767
768
769 Two
770
771
772 Six
773
774
775 Zero
776
777
778 Four (*)
779
780
781
782 Correct
783
784
785 14. Which data types stores variable-length character data? Select two. Mark for Review
786(1) Points
787
788 (Choose all correct answers)
789
790
791 VARCHAR2 (*)
792
793
794 CHAR
795
796
797 CLOB (*)
798
799
800 NCHAR
801
802
803
804 Correct
805
806
807 15. The BLOB datatype can max hold 128 Terabytes of data. True or False? Mark for Review
808(1) Points
809
810
811 True (*)
812
813
814 False
815
816
817
818 Correct
819
820
821
822
823 Previous Page 3 of 3 Summary
824
825
826
827
828
829
830
831
832
833
834
835
836
837Test: Section 14 Quiz
838
839
840
841
842
843
844
845
846
847Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
848
849
850
851 Section 14 Quiz
852 (Answer all questions in this section)
853
854 1. When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more? Mark for Review
855(1) Points
856
857
858 CONSTRAINT employee_salary_min CHECK (salary >= 1000) (*)
859
860
861 CHECK CONSTRAINT employee_salary_min (salary > 1000)
862
863
864 CONSTRAINT CHECK salary > 1000
865
866
867 CONSTRAINT employee_salary_min CHECK salary > 1000
868
869
870 CHECK CONSTRAINT (salary > 1000)
871
872
873
874 Correct
875
876
877 2. Evaluate the structure of the DONATIONS table.
878DONATIONS:
879 PLEDGE_ID NUMBER NOT NULL, Primary Key
880 DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
881 PLEDGE_DT DATE
882 AMOUNT_PLEDGED NUMBER (7,2)
883 AMOUNT_PAID NUMBER (7,2)
884 PAYMENT_DT DATE
885
886Which CREATE TABLE statement should you use to create the DONATIONS table?
887 Mark for Review
888(1) Points
889
890
891 CREATE TABLE donations
892 (pledge_id NUMBER PRIMARY KEY,
893 donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
894 pledge_date DATE,
895 amount_pledged NUMBER(7,2),
896 amount_paid NUMBER(7,2),
897 payment_dt DATE);
898(*)
899
900
901
902 CREATE TABLE donations
903 (pledge_id NUMBER PRIMARY KEY,
904 donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
905 pledge_date DATE,
906 amount_pledged NUMBER,
907 amount_paid NUMBER,
908 payment_dt DATE);
909
910
911
912 CREATE TABLE donations
913 pledge_id NUMBER PRIMARY KEY,
914 donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
915 pledge_date DATE,
916 amount_pledged NUMBER(7,2),
917 amount_paid NUMBER(7,2),
918 payment_dt DATE;
919
920
921
922 CREATE TABLE donations
923 (pledge_id NUMBER PRIMARY KEY NOT NULL,
924 donor_id NUMBER FOREIGN KEY donors(donor_id),
925 pledge_date DATE,
926 amount_pledged NUMBER(7,2),
927 amount_paid NUMBER(7,2),
928 payment_dt DATE);
929
930
931
932
933 Correct
934
935
936 3. The main reason that constraints are added to a table is: Mark for Review
937(1) Points
938
939
940 Constraints add a level of complexity
941
942
943 Constraints ensure data integrity (*)
944
945
946 Constraints gives programmers job security
947
948
949 None of the Above
950
951
952
953 Correct
954
955
956 4. Foreign Key Constraints are also known as: Mark for Review
957(1) Points
958
959
960 Multi-Table Constraints
961
962
963 Child Key Constraints
964
965
966 Parental Key Constraints
967
968
969 Referential Integrity Constraints (*)
970
971
972
973 Correct
974
975
976 5. Which statement about a FOREIGN KEY constraint is true? Mark for Review
977(1) Points
978
979
980 An index is automatically created for a FOREIGN KEY constraint.
981
982
983 A FOREIGN KEY constraint requires the constrained column to contain values that exist in the referenced Primary or Unique key column of the parent table. (*)
984
985
986 A FOREIGN KEY constraint allows that a list of allowed values be checked before a value can be added to the constrained column.
987
988
989 A FOREIGN KEY column can have a different data type from the primary key column that it references.
990
991
992
993 Correct
994
995
996
997
998 Page 1 of 3 Next Summary
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014Test: Section 14 Quiz
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1025
1026
1027
1028 Section 14 Quiz
1029 (Answer all questions in this section)
1030
1031 6. You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task? Mark for Review
1032(1) Points
1033
1034
1035 ALTER TABLE part
1036 MODIFY (cost part_cost_nn NOT NULL);
1037
1038
1039
1040 ALTER TABLE part
1041 MODIFY COLUMN (cost part_cost_nn NOT NULL);
1042
1043
1044
1045 ALTER TABLE part
1046 ADD (cost CONSTRAINT part_cost_nn NOT NULL);
1047
1048
1049
1050 ALTER TABLE part
1051 MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
1052(*)
1053
1054
1055
1056
1057 Correct
1058
1059
1060 7. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
1061(1) Points
1062
1063
1064 True
1065
1066
1067 False (*)
1068
1069
1070
1071 Correct
1072
1073
1074 8. Which statement about the NOT NULL constraint is true? Mark for Review
1075(1) Points
1076
1077
1078 The NOT NULL constraint requires a column to contain alphanumeric values.
1079
1080
1081 The NOT NULL constraint must be defined at the column level. (*)
1082
1083
1084 The NOT NULL constraint can be defined at either the column level or the table level.
1085
1086
1087 The NOT NULL constraint prevents a column from containing alphanumeric values.
1088
1089
1090
1091 Correct
1092
1093
1094 9. Which constraint can only be created at the column level? Mark for Review
1095(1) Points
1096
1097
1098 CHECK
1099
1100
1101 UNIQUE
1102
1103
1104 NOT NULL (*)
1105
1106
1107 FOREIGN KEY
1108
1109
1110
1111 Correct
1112
1113
1114 10. Which of the following is not a valid Oracle constraint type? Mark for Review
1115(1) Points
1116
1117
1118 PRIMARY KEY
1119
1120
1121 EXTERNAL KEY (*)
1122
1123
1124 NOT NULL
1125
1126
1127 UNIQUE KEY
1128
1129
1130
1131 Correct
1132
1133
1134
1135
1136 Previous Page 2 of 3 Next Summary
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152Test: Section 14 Quiz
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1163
1164
1165
1166 Section 14 Quiz
1167 (Answer all questions in this section)
1168
1169 11. When dropping a constraint, which keyword(s) specifies that all the referential integrity constraints that refer to the primary and unique keys defined on the dropped columns are dropped as well? Mark for Review
1170(1) Points
1171
1172
1173 ON DELETE SET NULL
1174
1175
1176 REFERENCES
1177
1178
1179 CASCADE (*)
1180
1181
1182 FOREIGN KEY
1183
1184
1185
1186 Correct
1187
1188
1189 12. What actions can be performed on or with Constraints? Mark for Review
1190(1) Points
1191
1192
1193 Add, Subtract, Enable, Cascade
1194
1195
1196 Add, Drop, Disable, Disregard
1197
1198
1199 Add, Drop, Enable, Disable, Cascade (*)
1200
1201
1202 Add, Minus, Enable, Disable, Collapse
1203
1204
1205
1206 Correct
1207
1208
1209 13. Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table? Mark for Review
1210(1) Points
1211
1212
1213 ALTER TABLE employees
1214 ADD FOREIGN KEY CONSTRAINT dept_id_fk ON (department_id) REFERENCES departments(department_id);
1215
1216
1217
1218 ALTER TABLE employees
1219 MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);
1220
1221
1222
1223 ALTER TABLE employees
1224 ADD FOREIGN KEY departments(department_id) REFERENCES (department_id);
1225
1226
1227
1228 ALTER TABLE employees
1229 ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);
1230(*)
1231
1232
1233
1234
1235 Correct
1236
1237
1238 14. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use? Mark for Review
1239(1) Points
1240
1241
1242 ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
1243
1244
1245 DROP CONSTRAINT EMP_FK_DEPT FROM employees;
1246
1247
1248 DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
1249
1250
1251 ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
1252
1253
1254
1255 Correct
1256
1257
1258 15. Which of the following would definitely cause an integrity constraint error? Mark for Review
1259(1) Points
1260
1261
1262 Using the DELETE command on a row that contains a primary key with a dependent foreign key declared without either an ON DELETE CASCADE or ON DELETE SET NULL. (*)
1263
1264
1265 Using the MERGE statement to conditionally insert or update rows.
1266
1267
1268 Using the UPDATE command on rows based in another table.
1269
1270
1271 Using a subquery in an INSERT statement.
1272
1273
1274
1275 Correct
1276
1277
1278
1279
1280
1281
1282
1283Test: Section 15 Quiz
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1294
1295
1296
1297 Section 15 Quiz
1298 (Answer all questions in this section)
1299
1300 1. Given the following view, which operations would be allowed on the emp_dept view?
1301CREATE OR REPLACE VIEW emp_dept
1302 AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
1303 e.salary,
1304 e.hire_date,
1305 d.department_name
1306 FROM employees e, departments d
1307 WHERE e.department_id = d.department_id
1308 AND d.department_id >=50;
1309 Mark for Review
1310(1) Points
1311
1312
1313 SELECT, UPDATE of all columns
1314
1315
1316 SELECT, DELETE
1317
1318
1319 SELECT, INSERT
1320
1321
1322 SELECT, UPDATE of some columns, DELETE (*)
1323
1324
1325
1326 Correct
1327
1328
1329 2. Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False? Mark for Review
1330(1) Points
1331
1332
1333 True
1334
1335
1336 False (*)
1337
1338
1339
1340 Correct
1341
1342
1343 3. Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively. Mark for Review
1344(1) Points
1345
1346
1347 CREATE OR REPLACE VIEW salary_vu
1348 AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
1349 FROM employees e, departments d, job_grades j
1350 WHERE e.department_id equals d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;
1351
1352
1353
1354 CREATE OR REPLACE VIEW salary_vu
1355 AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
1356 FROM employees e, departments d, job_grades j
1357 WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;
1358(*)
1359
1360
1361
1362 CREATE OR REPLACE VIEW salary_vu
1363 AS (SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
1364 FROM employees emp, departments d, job grades j
1365 WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal);
1366
1367
1368
1369 CREATE OR REPLACE VIEW salary_vu
1370 AS SELECT e.empid "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
1371 FROM employees e, departments d, job_grades j
1372 WHERE e.department_id = d.department_id NOT e.salary BETWEEN j.lowest_sal and j.highest_sal;
1373
1374
1375
1376
1377 Correct
1378
1379
1380 4. If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he include when creating the view? Mark for Review
1381(1) Points
1382
1383
1384 WITH READ ONLY
1385
1386
1387 FORCE
1388
1389
1390 WITH CONSTRAINT CHECK
1391
1392
1393 WITH CHECK OPTION (*)
1394
1395
1396
1397 Correct
1398
1399
1400 5. You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE, or DELETE operations. Which of the following statements should you issue? Mark for Review
1401(1) Points
1402
1403
1404 CREATE VIEW sales_view
1405 AS (SELECT companyname, city, orderid, orderdate, total
1406 FROM customers, orders
1407 WHERE custid = custid)
1408 WITH READ ONLY;
1409
1410
1411
1412 CREATE VIEW sales_view
1413 (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
1414 FROM customers c, orders o
1415 WHERE c.custid = o.custid)
1416 WITH READ ONLY;
1417
1418
1419
1420 CREATE VIEW sales_view
1421 AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
1422 FROM customers c, orders o
1423 WHERE c.custid = o.custid)
1424 WITH READ ONLY;
1425(*)
1426
1427
1428
1429 CREATE VIEW sales_view
1430 AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
1431 FROM customers c, orders o
1432 WHERE c.custid = o.custid);
1433
1434
1435
1436
1437 Correct
1438
1439
1440
1441
1442 Page 1 of 3 Next Summary
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458Test: Section 15 Quiz
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1469
1470
1471
1472 Section 15 Quiz
1473 (Answer all questions in this section)
1474
1475 6. Given the following CREATE VIEW statement, what data will be returned?
1476CREATE OR REPLACE VIEW emp_dept
1477 AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
1478 e.salary,
1479 e.hire_date,
1480 d.department_name
1481 FROM employees e, departments d
1482 WHERE e.department_id = d.department_id
1483 AND d.department_id >=50;
1484 Mark for Review
1485(1) Points
1486
1487
1488 First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_id of all employees working in department number 50 or higher.
1489
1490
1491 First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_name of all employees working in department number 50.
1492
1493
1494 First character from employee first_name concatenated to the last_name, the salary, the hire_date, and the department_name of all employees working in department number 50 or higher. (*)
1495
1496
1497 First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_id of all employees working in department number 50.
1498
1499
1500
1501 Correct
1502
1503
1504 7. Which statement about the CREATE VIEW statement is true? Mark for Review
1505(1) Points
1506
1507
1508 A CREATE VIEW statement CANNOT contain a function.
1509
1510
1511 A CREATE VIEW statement CAN contain a join query. (*)
1512
1513
1514 A CREATE VIEW statement CANNOT contain a GROUP BY clause.
1515
1516
1517 A CREATE VIEW statement CANNOT contain an ORDER BY clause.
1518
1519
1520
1521 Correct
1522
1523
1524 8. Which keyword(s) would you include in a CREATE VIEW statement to create the view whether or not the base table exists? Mark for Review
1525(1) Points
1526
1527
1528 WITH READ ONLY
1529
1530
1531 NOFORCE
1532
1533
1534 FORCE (*)
1535
1536
1537 OR REPLACE
1538
1539
1540
1541 Correct
1542
1543
1544 9. You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true? Mark for Review
1545(1) Points
1546
1547
1548 You can create the table and the view at the same time using the FORCE option.
1549
1550
1551 You can use the FORCE option to create the view before the SALES table has been created. (*)
1552
1553
1554 By default, the view will be created even if the SALES table does not exist.
1555
1556
1557 You must create the SALES table before creating the view.
1558
1559
1560
1561 Correct
1562
1563
1564 10. A view can contain group functions. True or False? Mark for Review
1565(1) Points
1566
1567
1568 True (*)
1569
1570
1571 False
1572
1573
1574
1575 Correct
1576
1577
1578
1579
1580 Previous Page 2 of 3 Next Summary
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596Test: Section 15 Quiz
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1607
1608
1609
1610 Section 15 Quiz
1611 (Answer all questions in this section)
1612
1613 11. The EMP_HIST_V view is no longer needed. Which statement should you use to the remove this view? Mark for Review
1614(1) Points
1615
1616
1617 DROP emp_hist_v;
1618
1619
1620 DROP VIEW emp_hist_v; (*)
1621
1622
1623 REMOVE emp_hist_v;
1624
1625
1626 DELETE emp_hist_v;
1627
1628
1629
1630 Correct
1631
1632
1633 12. A Top-N Analysis is capable of ranking a top or bottom set of results. True or False? Mark for Review
1634(1) Points
1635
1636
1637 True (*)
1638
1639
1640 False
1641
1642
1643
1644 Correct
1645
1646
1647 13. Evaluate this CREATE VIEW statement:
1648CREATE VIEW sales_view
1649 AS SELECT customer_id, region, SUM(sales_amount)
1650 FROM sales
1651 WHERE region IN (10, 20, 30, 40)
1652 GROUP BY region, customer_id;
1653
1654Which statement is true?
1655 Mark for Review
1656(1) Points
1657
1658
1659 You can only insert records into the SALES table using the SALES_VIEW view.
1660
1661
1662 The CREATE VIEW statement generates an error.
1663
1664
1665 You can modify data in the SALES table using the SALES_VIEW view.
1666
1667
1668 You cannot modify data in the SALES table using the SALES_VIEW view. (*)
1669
1670
1671
1672 Correct
1673
1674
1675 14. Evaluate this SELECT statement:
1676SELECT ROWNUM "Rank", customer_id, new_balance
1677 FROM (SELECT customer_id, new_balance
1678 FROM customer_finance
1679 ORDER BY new_balance DESC)
1680 WHERE ROWNUM <= 25;
1681
1682Which type of query is this SELECT statement?
1683 Mark for Review
1684(1) Points
1685
1686
1687 A complex view
1688
1689
1690 A Top-n query (*)
1691
1692
1693 A simple view
1694
1695
1696 A hierarchical view
1697
1698
1699
1700 Correct
1701
1702
1703 15. When you drop a table referenced by a view, the view is automatically dropped as well. True or False? Mark for Review
1704(1) Points
1705
1706
1707 True
1708
1709
1710 False (*)
1711
1712
1713
1714 Correct
1715
1716
1717
1718
1719 Previous Page 3 of 3 Summary
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735Test: Section 16 Quiz
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1746
1747
1748
1749 Section 16 Quiz
1750 (Answer all questions in this section)
1751
1752 1. The CLIENTS table contains these columns:
1753CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
1754 LAST_NAME VARCHAR2(15)
1755 FIRST_NAME VARCHAR2(10)
1756 CITY VARCHAR2(15)
1757 STATE VARCHAR2(2)
1758
1759You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:
1760
1761CREATE INDEX clients
1762 ON address_index (city, state);
1763
1764Which result does this statement accomplish?
1765 Mark for Review
1766(1) Points
1767
1768
1769 An index named ADDRESS_INDEX is created on the CITY and STATE columns.
1770
1771
1772 An index named CLIENTS_INDEX is created on the CLIENTS table.
1773
1774
1775 An error message is produced, and no index is created. (*)
1776
1777
1778 An index named CLIENTS is created on the CITY and STATE columns.
1779
1780
1781
1782 Correct
1783
1784
1785 2. You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query? Mark for Review
1786(1) Points
1787
1788
1789 USER_INDEXES
1790
1791
1792 USER_IND_COLUMNS (*)
1793
1794
1795 USER_OBJECTS
1796
1797
1798 USER_TABLES
1799
1800
1801
1802 Correct
1803
1804
1805 3. When creating an index on one or more columns of a table, which of the following statements are true?
1806 (Choose two) Mark for Review
1807(1) Points
1808
1809 (Choose all correct answers)
1810
1811
1812 You should create an index if the table is very small.
1813
1814
1815 You should always create an index on tables that are frequently updated.
1816
1817
1818 You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows. (*)
1819
1820
1821 You should create an index if one or more columns are frequently used together in a join condition. (*)
1822
1823
1824
1825 Correct
1826
1827
1828 4. User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
1829CREATE PUBLIC SYNONYM emp FOR mary.emp;
1830
1831User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use? (Choose two)
1832 Mark for Review
1833(1) Points
1834
1835 (Choose all correct answers)
1836
1837
1838 SELECT * FROM emp; (*)
1839
1840
1841 SELECT * FROM mary.emp; (*)
1842
1843
1844 CREATE SYNONYM marys_emp FOR mary(emp);
1845
1846
1847 SELECT * FROM emp.mary;
1848
1849
1850
1851 Correct
1852
1853
1854 5. What is the correct syntax for creating an index? Mark for Review
1855(1) Points
1856
1857
1858 CREATE index_name INDEX ON table_name.column_name;
1859
1860
1861 CREATE INDEX index_name ON table_name(column_name); (*)
1862
1863
1864 CREATE INDEX ON table_name(column_name);
1865
1866
1867 CREATE OR REPLACE INDEX index_name ON table_name(column_name);
1868
1869
1870
1871 Incorrect. Refer to Section 16 Lesson 2.
1872
1873
1874
1875
1876 Page 1 of 3 Next Summary
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892Test: Section 16 Quiz
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
1903
1904
1905
1906 Section 16 Quiz
1907 (Answer all questions in this section)
1908
1909 6. In SQL what is a synonym? Mark for Review
1910(1) Points
1911
1912
1913 A table that must be qualified with a username
1914
1915
1916 A table with the same name as another view
1917
1918
1919 A table with the same number of columns as another table
1920
1921
1922 A different name for a table, view, or other database object (*)
1923
1924
1925
1926 Correct
1927
1928
1929 7. What kind of INDEX is created by Oracle when you create a primary key? Mark for Review
1930(1) Points
1931
1932
1933 UNIQUE INDEX (*)
1934
1935
1936 NONUNIQUE INDEX
1937
1938
1939 INDEX
1940
1941
1942 Oracle cannot create indexes automatically.
1943
1944
1945
1946 Correct
1947
1948
1949 8. Which pseudocolumn returns the latest value supplied by a sequence? Mark for Review
1950(1) Points
1951
1952
1953 CURRENT
1954
1955
1956 NEXTVAL
1957
1958
1959 CURRVAL (*)
1960
1961
1962 NEXT
1963
1964
1965
1966 Correct
1967
1968
1969 9. To see the most recent value that you fetched from a sequence named my_seq you should reference: Mark for Review
1970(1) Points
1971
1972
1973 my_seq.currval (*)
1974
1975
1976 my_seq.nextval
1977
1978
1979 my_seq.(currval)
1980
1981
1982 my_seq.(lastval)
1983
1984
1985
1986 Correct
1987
1988
1989 10. Which dictionary view would you query to display the number most recently generated by a sequence? Mark for Review
1990(1) Points
1991
1992
1993 USER_CURRVALUES
1994
1995
1996 USER_TABLES
1997
1998
1999 USER_OBJECTS
2000
2001
2002 USER_SEQUENCES (*)
2003
2004
2005
2006 Correct
2007
2008
2009
2010
2011 Previous Page 2 of 3 Next Summary
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027Test: Section 16 Quiz
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2038
2039
2040
2041 Section 16 Quiz
2042 (Answer all questions in this section)
2043
2044 11. Which of the following best describes the function of the CURRVAL virtual column? Mark for Review
2045(1) Points
2046
2047
2048 The CURRVAL virtual column will increment a sequence by a specified value.
2049
2050
2051 The CURRVAL virtual column will display either the physical locations or the logical locations of the rows in the table.
2052
2053
2054 The CURRVAL virtual column will display the integer that was most recently supplied by a sequence. (*)
2055
2056
2057 The CURRVAL virtual column will return a value of 1 for a parent record in a hierarchical result set.
2058
2059
2060
2061 Incorrect. Refer to Section 16 Lesson 1.
2062
2063
2064 12. A sequence is a database object. True or False? Mark for Review
2065(1) Points
2066
2067
2068 True (*)
2069
2070
2071 False
2072
2073
2074
2075 Correct
2076
2077
2078 13. When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory? Mark for Review
2079(1) Points
2080
2081
2082 NOCYCLE
2083
2084
2085 MEMORY
2086
2087
2088 CACHE (*)
2089
2090
2091 NOCACHE
2092
2093
2094 LOAD
2095
2096
2097
2098 Correct
2099
2100
2101 14. What is the most common use for a Sequence? Mark for Review
2102(1) Points
2103
2104
2105 To logically represent subsets of data from one or more tables
2106
2107
2108 To give an alternative name for an object
2109
2110
2111 To improve the performance of some queries
2112
2113
2114 To generate primary key values (*)
2115
2116
2117
2118 Correct
2119
2120
2121 15. You issue this statement:
2122ALTER SEQUENCE po_sequence INCREMENT BY 2;
2123
2124Which statement is true?
2125 Mark for Review
2126(1) Points
2127
2128
2129 Sequence numbers will be cached.
2130
2131
2132 Future sequence numbers generated will increase by 2 each time a number is generated. (*)
2133
2134
2135 If the PO_SEQUENCE sequence does not exist, it will be created.
2136
2137
2138 The statement fails if the current value of the sequence is greater than the START WITH value.
2139
2140
2141
2142 Correct
2143
2144
2145
2146
2147 Previous Page 3 of 3 Summary
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163Test: Section 17 Quiz
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2174
2175
2176
2177 Section 17 Quiz
2178 (Answer all questions in this section)
2179
2180 1. Which of the following best describes a role in an Oracle database? Mark for Review
2181(1) Points
2182
2183
2184 A role is an object privilege which allows a user to update a table.
2185
2186
2187 A role is the part that a user plays in querying the database.
2188
2189
2190 A role is a type of system privilege.
2191
2192
2193 A role is a name for a group of privileges. (*)
2194
2195
2196
2197 Correct
2198
2199
2200 2. What system privilege must be held in order to login to an Oracle database? Mark for Review
2201(1) Points
2202
2203
2204 CREATE LOGIN
2205
2206
2207 CREATE SESSION (*)
2208
2209
2210 CREATE LOGON
2211
2212
2213 No special privilege is needed; if your username exists in the database, you can login.
2214
2215
2216
2217 Correct
2218
2219
2220 3. Which of these is NOT a System Privilege granted by the DBA? Mark for Review
2221(1) Points
2222
2223
2224 Create Session
2225
2226
2227 Create Sequence
2228
2229
2230 Create Index (*)
2231
2232
2233 Create Procedure
2234
2235
2236
2237 Correct
2238
2239
2240 4. Which of the following are system privileges?
2241 (Choose two) Mark for Review
2242(1) Points
2243
2244 (Choose all correct answers)
2245
2246
2247 UPDATE
2248
2249
2250 CREATE SYNONYM (*)
2251
2252
2253 INDEX
2254
2255
2256 CREATE TABLE (*)
2257
2258
2259
2260 Correct
2261
2262
2263 5. You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY? Mark for Review
2264(1) Points
2265
2266
2267 A user privilege
2268
2269
2270 An object privilege
2271
2272
2273 An access privilege
2274
2275
2276 A system privilege (*)
2277
2278
2279
2280 Correct
2281
2282
2283
2284
2285 Page 1 of 3 Next Summary
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301Test: Section 17 Quiz
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2312
2313
2314
2315 Section 17 Quiz
2316 (Answer all questions in this section)
2317
2318 6. User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this? Mark for Review
2319(1) Points
2320
2321
2322 GRANT customers(SELECT) TO PUBLIC;
2323
2324
2325 CREATE PUBLIC SYNONYM customers FOR james.customers;
2326
2327
2328 GRANT SELECT ON customers TO ALL;
2329
2330
2331 GRANT SELECT ON customers TO PUBLIC; (*)
2332
2333
2334
2335 Correct
2336
2337
2338 7. Parentheses are not used to identify the sub expressions within the expression. True or False? Mark for Review
2339(1) Points
2340
2341
2342 True
2343
2344
2345 False (*)
2346
2347
2348
2349 Correct
2350
2351
2352 8. REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False) Mark for Review
2353(1) Points
2354
2355
2356 True (*)
2357
2358
2359 False
2360
2361
2362
2363 Correct
2364
2365
2366 9. REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?) Mark for Review
2367(1) Points
2368
2369
2370 True
2371
2372
2373 False (*)
2374
2375
2376
2377 Correct
2378
2379
2380 10. User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table? Mark for Review
2381(1) Points
2382
2383
2384 Yes
2385
2386
2387 No (*)
2388
2389
2390
2391 Correct
2392
2393
2394
2395
2396 Previous Page 2 of 3 Next Summary
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412Test: Section 17 Quiz
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2423
2424
2425
2426 Section 17 Quiz
2427 (Answer all questions in this section)
2428
2429 11. User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
2430GRANT SELECT ON employees TO mary WITH GRANT OPTION;
2431
2432Which of the following statements can MARY now execute successfully? (Choose two)
2433 Mark for Review
2434(1) Points
2435
2436 (Choose all correct answers)
2437
2438
2439 DROP TABLE bob.employees;
2440
2441
2442 SELECT FROM bob.employees; (*)
2443
2444
2445 GRANT SELECT ON bob.employees TO PUBLIC; (*)
2446
2447
2448 REVOKE SELECT ON bob.employees FROM bob;
2449
2450
2451
2452 Correct
2453
2454
2455 12. Which statement would you use to add privileges to a role? Mark for Review
2456(1) Points
2457
2458
2459 GRANT (*)
2460
2461
2462 CREATE ROLE
2463
2464
2465 ALTER ROLE
2466
2467
2468 ASSIGN
2469
2470
2471
2472 Correct
2473
2474
2475 13. To join a table in your database to a table on a second (remote) Oracle database, you need to use: Mark for Review
2476(1) Points
2477
2478
2479 A remote procedure call
2480
2481
2482 An Oracle gateway product
2483
2484
2485 A database link (*)
2486
2487
2488 An ODBC driver
2489
2490
2491
2492 Correct
2493
2494
2495 14. User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform? Mark for Review
2496(1) Points
2497
2498
2499 He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
2500
2501
2502 He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
2503
2504
2505 He should assign the SELECT privilege to all database users for the INVENTORY table.
2506
2507
2508 He is not required to take any action because, by default, all database users can automatically access views.
2509
2510
2511
2512 Correct
2513
2514
2515 15. Which statement would you use to grant a role to users? Mark for Review
2516(1) Points
2517
2518
2519 ASSIGN
2520
2521
2522 GRANT (*)
2523
2524
2525 CREATE USER
2526
2527
2528 ALTER USER
2529
2530
2531
2532 Correct
2533
2534
2535
2536
2537 Previous Page 3 of 3 Summary
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553Test: Section 18 Quiz
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2564
2565
2566
2567 Section 18 Quiz
2568 (Answer all questions in this section)
2569
2570 1. Examine the following statements:
2571UPDATE employees SET salary = 15000;
2572 SAVEPOINT upd1_done;
2573 UPDATE employees SET salary = 22000;
2574 SAVEPOINT upd2_done;
2575 DELETE FROM employees;
2576
2577You want to retain all the employees with a salary of 15000; What statement would you execute next?
2578 Mark for Review
2579(1) Points
2580
2581
2582 ROLLBACK;
2583
2584
2585 ROLLBACK TO SAVEPOINT upd1_done; (*)
2586
2587
2588 ROLLBACK TO SAVEPOINT upd2_done;
2589
2590
2591 ROLLBACK TO SAVE upd1_done;
2592
2593
2594 There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.
2595
2596
2597
2598 Correct
2599
2600
2601 2. Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
2602UPDATE employees
2603 SET salary = salary * 2
2604 WHERE employee_id = 100;
2605 COMMIT;
2606
2607UPDATE employees
2608 SET salary = 30000
2609 WHERE employee_id = 100;
2610
2611The user's database session now ends abnormally. What is now King's salary in the table?
2612 Mark for Review
2613(1) Points
2614
2615
2616 30000
2617
2618
2619 48000 (*)
2620
2621
2622 24000
2623
2624
2625 78000
2626
2627
2628
2629 Correct
2630
2631
2632 3. If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False? Mark for Review
2633(1) Points
2634
2635
2636 True
2637
2638
2639 False (*)
2640
2641
2642
2643 Correct
2644
2645
2646 4. A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this? Mark for Review
2647(1) Points
2648
2649
2650 An update statement
2651
2652
2653 A database link
2654
2655
2656 A savepoint (*)
2657
2658
2659 A sequence
2660
2661
2662 An object privilege
2663
2664
2665
2666 Correct
2667
2668
2669 5. Which SQL statement is used to remove all the changes made by an uncommitted transaction? Mark for Review
2670(1) Points
2671
2672
2673 UNDO;
2674
2675
2676 REVOKE;
2677
2678
2679 ROLLBACK; (*)
2680
2681
2682 ROLLBACK TO SAVEPOINT;
2683
2684
2685
2686 Correct
2687
2688
2689
2690
2691 Page 1 of 3 Next Summary
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707Test: Section 18 Quiz
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2718
2719
2720
2721 Section 18 Quiz
2722 (Answer all questions in this section)
2723
2724 6. Examine the following statements:
2725INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
2726 SAVEPOINT Ins_Done;
2727 CREATE INDEX emp_lname_idx ON employees(last_name);
2728 UPDATE emps SET last_name = 'Smith';
2729
2730What happens if you issue a Rollback statement?
2731 Mark for Review
2732(1) Points
2733
2734
2735 The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)
2736
2737
2738 Both the UPDATE and the INSERT will be rolled back.
2739
2740
2741 The INSERT is undone but the UPDATE is committed.
2742
2743
2744 Nothing happens.
2745
2746
2747
2748 Correct
2749
2750
2751 7. COMMIT saves all outstanding data changes? True or False? Mark for Review
2752(1) Points
2753
2754
2755 True (*)
2756
2757
2758 False
2759
2760
2761
2762 Correct
2763
2764
2765 8. Which of the following best describes the term "read consistency"? Mark for Review
2766(1) Points
2767
2768
2769 It prevents other users from seeing changes to a table until those changes have been committed (*)
2770
2771
2772 It prevents users from querying tables on which they have not been granted SELECT privilege
2773
2774
2775 It prevents other users from querying a table while updates are being executed on it
2776
2777
2778 It ensures that all changes to a table are automatically committed
2779
2780
2781
2782 Correct
2783
2784
2785 9. You need not worry about controlling your transactions. Oracle does it all for you. True or False? Mark for Review
2786(1) Points
2787
2788
2789 True
2790
2791
2792 False (*)
2793
2794
2795
2796 Correct
2797
2798
2799 10. When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
2800(1) Points
2801
2802
2803 True
2804
2805
2806 False (*)
2807
2808
2809
2810 Correct
2811
2812
2813
2814
2815 Previous Page 2 of 3 Next Summary
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831Test: Section 18 Quiz
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2842
2843
2844
2845 Section 18 Quiz
2846 (Answer all questions in this section)
2847
2848 11. If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
2849(1) Points
2850
2851
2852 True (*)
2853
2854
2855 False
2856
2857
2858
2859 Correct
2860
2861
2862 12. Examine the following statements:
2863INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
2864 SAVEPOINT Ins_Done;
2865 DELETE employees; -- 107 rows deleted
2866 SAVEPOINT Del_Done;
2867 UPDATE emps SET last_name = 'Smith';
2868
2869How would you undo the last Update only?
2870 Mark for Review
2871(1) Points
2872
2873
2874 There is nothing you can do.
2875
2876
2877 COMMIT Del_Done;
2878
2879
2880 ROLLBACK to SAVEPOINT Del_Done; (*)
2881
2882
2883 ROLLBACK UPDATE;
2884
2885
2886
2887 Correct
2888
2889
2890 13. User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
2891SELECT COUNT(*) FROM bob.customers;
2892
2893What result will JANE see?
2894 Mark for Review
2895(1) Points
2896
2897
2898 22
2899
2900
2901 2
2902
2903
2904 JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
2905
2906
2907 20 (*)
2908
2909
2910
2911 Correct
2912
2913
2914 14. Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
2915INSERT INTO mytab VALUES ('A');
2916 INSERT INTO mytab VALUES ('B');
2917 COMMIT;
2918 INSERT INTO mytab VALUES ('C');
2919 ROLLBACK;
2920
2921Which rows does the table now contain?
2922 Mark for Review
2923(1) Points
2924
2925
2926 A, B, and C
2927
2928
2929 A and B (*)
2930
2931
2932 C
2933
2934
2935 None of the above
2936
2937
2938
2939 Correct
2940
2941
2942 15. If Oracle crashes, your changes are automatically rolled back. True or False? Mark for Review
2943(1) Points
2944
2945
2946 True (*)
2947
2948
2949 False
2950
2951
2952
2953 Correct
2954
2955
2956
2957
2958 Previous Page 3 of 3 Summary
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974Test: Database Programming with SQL Final Exam
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
2985
2986
2987
2988 Section 12
2989 (Answer all questions in this section)
2990
2991 1. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? Mark for Review
2992(1) Points
2993
2994
2995 Yes, you can just list as many rows as you want; just remember to separate the rows with commas.
2996
2997
2998 No, you can only create one row at a time when using the VALUES clause. (*)
2999
3000
3001 No, there is no such thing as INSERT ... VALUES.
3002
3003
3004
3005 Correct
3006
3007
3008 2. To return a table summary on the customers table, which of the following is correct? Mark for Review
3009(1) Points
3010
3011
3012 SHOW customers, or SEE customers
3013
3014
3015 DISTINCT customers, or DIST customers
3016
3017
3018 DESCRIBE customers, or DESC customers (*)
3019
3020
3021 DEFINE customers, or DEF customers
3022
3023
3024
3025 Correct
3026
3027
3028 3. A column in a table can be given a default value. This option prevents NULL values from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False ? Mark for Review
3029(1) Points
3030
3031
3032 True (*)
3033
3034
3035 False
3036
3037
3038
3039 Correct
3040
3041
3042 4. In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________. Mark for Review
3043(1) Points
3044
3045
3046 A great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified.
3047
3048
3049 A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified.
3050
3051
3052 Both a and b are correct.
3053
3054
3055 A bad idea. The default value must match the DATE datatype of the column. (*)
3056
3057
3058
3059 Correct
3060
3061
3062 5. You want to enter a new record into the CUSTOMERS table. Which two commands can be used to create new rows? Mark for Review
3063(1) Points
3064
3065
3066 INSERT, CREATE
3067
3068
3069 MERGE, CREATE
3070
3071
3072 INSERT, MERGE (*)
3073
3074
3075 INSERT, UPDATE
3076
3077
3078
3079 Correct
3080
3081
3082
3083
3084 Page 1 of 10 Next Summary
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100Test: Database Programming with SQL Final Exam
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3111
3112
3113
3114 Section 12
3115 (Answer all questions in this section)
3116
3117 6. You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns? Mark for Review
3118(1) Points
3119
3120
3121 The SET clause (*)
3122
3123
3124 The USING clause
3125
3126
3127 The ON clause
3128
3129
3130 The WHERE clause
3131
3132
3133
3134 Correct
3135
3136
3137 7. You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
3138EMPLOYEE_ID NUMBER(5) PRIMARY KEY
3139 LAST_NAME VARCHAR2(20)
3140 FIRST_NAME VARCHAR2(20)
3141 ADDRESS VARCHAR2(30)
3142 PHONE NUMBER(10)
3143
3144Which DELETE statement will delete the appropriate record without deleting any additional records?
3145 Mark for Review
3146(1) Points
3147
3148
3149 DELETE FROM employees
3150 WHERE last_name = jones;
3151
3152
3153
3154 DELETE FROM employees
3155 WHERE employee_id = 348;
3156(*)
3157
3158
3159
3160 DELETE 'jones'
3161 FROM employees;
3162
3163
3164
3165 DELETE *
3166 FROM employees
3167 WHERE employee_id = 348;
3168
3169
3170
3171
3172 Correct
3173
3174
3175 8. What would happen if you issued a DELETE statement without a WHERE clause? Mark for Review
3176(1) Points
3177
3178
3179 An error message would be returned.
3180
3181
3182 No rows would be deleted.
3183
3184
3185 All the rows in the table would be deleted. (*)
3186
3187
3188 Only one row would be deleted.
3189
3190
3191
3192 Incorrect. Refer to Section 12 Lesson 2.
3193
3194
3195
3196
3197 Section 13
3198 (Answer all questions in this section)
3199
3200 9. Which statement about creating a table is true? Mark for Review
3201(1) Points
3202
3203
3204 If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)
3205
3206
3207 If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.
3208
3209
3210 With a CREATE TABLE statement, a table will always be created in the current user's schema.
3211
3212
3213 If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE statement will fail.
3214
3215
3216
3217 Correct
3218
3219
3220 10. Which statement about table and column names is true? Mark for Review
3221(1) Points
3222
3223
3224 Table and column names can begin with a letter or a number.
3225
3226
3227 Table and column names cannot include special characters.
3228
3229
3230 If any character other than letters or numbers is used in a table or column name, the name must be enclosed in double quotation marks.
3231
3232
3233 Table and column names must begin with a letter. (*)
3234
3235
3236
3237 Correct
3238
3239
3240
3241
3242 Previous Page 2 of 10 Next Summary
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258Test: Database Programming with SQL Final Exam
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3269
3270
3271
3272 Section 13
3273 (Answer all questions in this section)
3274
3275 11. When creating a new table, which of the following naming rules apply. (Choose three) Mark for Review
3276(1) Points
3277
3278 (Choose all correct answers)
3279
3280
3281 Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
3282
3283
3284 Must be between 1 to 30 characters long (*)
3285
3286
3287 Must be an Oracle reserved word
3288
3289
3290 Can have the same name as another object owned by the same user
3291
3292
3293 Must begin with a letter (*)
3294
3295
3296
3297 Correct
3298
3299
3300 12. Which statement about data types is true? Mark for Review
3301(1) Points
3302
3303
3304 The VARCHAR2 data type should be used for fixed-length character data.
3305
3306
3307 The TIMESTAMP data type is a character data type.
3308
3309
3310 The BFILE data type stores character data up to four gigabytes in the database.
3311
3312
3313 The CHAR data type should be defined with a size that is not too large for the data it contains (or could contain) to save space in the database. (*)
3314
3315
3316
3317 Correct
3318
3319
3320 13. The SPEED_TIME column should store a fractional second value.
3321Which data type should you use?
3322 Mark for Review
3323(1) Points
3324
3325
3326 DATETIME
3327
3328
3329 TIMESTAMP (*)
3330
3331
3332 DATE
3333
3334
3335 INTERVAL DAY TO SECOND
3336
3337
3338
3339 Correct
3340
3341
3342 14. The data type of a column can never be changed once it has been created. True or False? Mark for Review
3343(1) Points
3344
3345
3346 True
3347
3348
3349 False (*)
3350
3351
3352
3353 Correct
3354
3355
3356 15. To do a logical delete of a column without the performance penalty of rewriting all the table datablocks, you can issue the following command: Mark for Review
3357(1) Points
3358
3359
3360 Alter table set unused (*)
3361
3362
3363 Alter table drop column
3364
3365
3366 Alter table modify column
3367
3368
3369 Drop column "columname"
3370
3371
3372
3373 Correct
3374
3375
3376
3377
3378 Previous Page 3 of 10 Next Summary
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394Test: Database Programming with SQL Final Exam
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3405
3406
3407
3408 Section 13
3409 (Answer all questions in this section)
3410
3411 16. Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table contains data that you need to keep. Which statement should you issue to accomplish this task? Mark for Review
3412(1) Points
3413
3414
3415 ALTER TABLE orders
3416 CHANGE DATATYPE amount TO DEFAULT 250;
3417
3418
3419
3420 DELETE TABLE orders;
3421 CREATE TABLE orders
3422 (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
3423 customerid varchar2(5) REFERENCES customers (customerid),
3424 orderdate date,
3425 amount DEFAULT 250)
3426
3427
3428
3429 ALTER TABLE orders
3430 MODIFY (amount DEFAULT 250);
3431(*)
3432
3433
3434
3435 DROP TABLE orders;
3436 CREATE TABLE orders
3437 (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
3438 customerid varchar2(5) REFERENCES customers (customerid),
3439 orderdate date,
3440 amount DEFAULT 250);
3441
3442
3443
3444
3445 Incorrect. Refer to Section 13 Lesson 3.
3446
3447
3448 17. When should you use the SET UNUSED command? Mark for Review
3449(1) Points
3450
3451
3452 You should use it when you need a quick way of dropping a column. (*)
3453
3454
3455 You should use it if you think the column may be needed again later.
3456
3457
3458 You should only use this command if you want the column to still be visible when you DESCRIBE the table.
3459
3460
3461 Never, there is no SET UNUSED command.
3462
3463
3464
3465 Correct
3466
3467
3468 18. After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False? Mark for Review
3469(1) Points
3470
3471
3472 True (*)
3473
3474
3475 False
3476
3477
3478
3479 Correct
3480
3481
3482 19. Which statement about a column is NOT true? Mark for Review
3483(1) Points
3484
3485
3486 You can convert a CHAR data type column to the VARCHAR2 data type.
3487
3488
3489 You can convert a DATE data type column to a VARCHAR2 column.
3490
3491
3492 You can modify the data type of a column if the column contains non-null data. (*)
3493
3494
3495 You can increase the width of a CHAR column.
3496
3497
3498
3499 Correct
3500
3501
3502
3503
3504 Section 14
3505 (Answer all questions in this section)
3506
3507 20. You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use? Mark for Review
3508(1) Points
3509
3510
3511 CHANGE
3512
3513
3514 MODIFY (*)
3515
3516
3517 ADD
3518
3519
3520 DISABLE
3521
3522
3523
3524 Correct
3525
3526
3527
3528
3529 Previous Page 4 of 10 Next Summary
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545Test: Database Programming with SQL Final Exam
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3556
3557
3558
3559 Section 14
3560 (Answer all questions in this section)
3561
3562 21. The command to 'switch off' a constraint is: Mark for Review
3563(1) Points
3564
3565
3566 ALTER TABLE STOP CONSTRAINTS
3567
3568
3569 ALTER TABLE DISABLE CONSTRAINT (*)
3570
3571
3572 ALTER TABLE STOP CHECKING
3573
3574
3575 ALTER TABLE PAUSE CONSTRAINT
3576
3577
3578
3579 Correct
3580
3581
3582 22. Which constraint can only be created at the column level? Mark for Review
3583(1) Points
3584
3585
3586 FOREIGN KEY
3587
3588
3589 CHECK
3590
3591
3592 NOT NULL (*)
3593
3594
3595 UNIQUE
3596
3597
3598
3599 Correct
3600
3601
3602 23. Which statement about constraints is true? Mark for Review
3603(1) Points
3604
3605
3606 PRIMARY KEY constraints can only be specified at the column level.
3607
3608
3609 NOT NULL constraints can only be specified at the column level. (*)
3610
3611
3612 A single column can have only one constraint applied.
3613
3614
3615 UNIQUE constraints are identical to PRIMARY KEY constraints.
3616
3617
3618
3619 Correct
3620
3621
3622 24. A unique key constraint can only be defined on a not null column. True or False? Mark for Review
3623(1) Points
3624
3625
3626 True
3627
3628
3629 False (*)
3630
3631
3632
3633 Correct
3634
3635
3636 25. The table that contains the Primary Key in a Foreign Key Constraint is known as: Mark for Review
3637(1) Points
3638
3639
3640 Mother and Father Table
3641
3642
3643 Child Table
3644
3645
3646 Parent Table (*)
3647
3648
3649 Detail Table
3650
3651
3652
3653 Correct
3654
3655
3656
3657
3658 Previous Page 5 of 10 Next Summary
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674Test: Database Programming with SQL Final Exam
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3685
3686
3687
3688 Section 14
3689 (Answer all questions in this section)
3690
3691 26. Foreign Key Constraints are also known as: Mark for Review
3692(1) Points
3693
3694
3695 Child Key Constraints
3696
3697
3698 Referential Integrity Constraints (*)
3699
3700
3701 Parental Key Constraints
3702
3703
3704 Multi-Table Constraints
3705
3706
3707
3708 Correct
3709
3710
3711
3712
3713 Section 15
3714 (Answer all questions in this section)
3715
3716 27. Examine the view below and choose the operation that CANNOT be performed on it.
3717CREATE VIEW dj_view (last_name, number_events) AS
3718 SELECT c.last_name, COUNT(e.name)
3719 FROM d_clients c, d_events e
3720 WHERE c.client_number = e.client_number
3721 GROUP BY c.last_name
3722
3723 Mark for Review
3724(1) Points
3725
3726
3727 SELECT last_name, number_events FROM dj_view;
3728
3729
3730 DROP VIEW dj_view;
3731
3732
3733 INSERT INTO dj_view VALUES ('Turner', 8); (*)
3734
3735
3736 CREATE OR REPLACE dj_view (last_name, number_events) AS
3737 SELECT c.last_name, COUNT (e.name)
3738 FROM d_clients c, d_events e
3739 WHERE c.client_number=e.client_number
3740 GROUP BY c.last_name;
3741
3742
3743
3744
3745 Correct
3746
3747
3748 28. Which of the following DML operations is not allowed when using a Simple View created with read only? Mark for Review
3749(1) Points
3750
3751
3752 INSERT
3753
3754
3755 UPDATE
3756
3757
3758 DELETE
3759
3760
3761 All of the above (*)
3762
3763
3764
3765 Correct
3766
3767
3768 29. You create a view on the EMPLOYEES and DEPARTMENTS tables to display salary information per department.
3769 What will happen if you issue the following statement?
3770CREATE OR REPLACE VIEW sal_dept
3771 AS SELECT SUM(e.salary) sal, d.department_name
3772 FROM employees e, departments d
3773 WHERE e.department_id = d.department_id
3774 GROUP BY d.department_name;
3775 Mark for Review
3776(1) Points
3777
3778
3779 A complex view is created that returns the sum of salaries per department. (*)
3780
3781
3782 A simple view is created that returns the sum of salaries per department, sorted by department name.
3783
3784
3785 A complex view is created that returns the sum of salaries per department, sorted by department id.
3786
3787
3788 Nothing, as the statement contains an error and will fail.
3789
3790
3791
3792 Incorrect. Refer to Section 15 Lesson 2.
3793
3794
3795 30. The CUSTOMER_FINANCE table contains these columns:
3796CUSTOMER_ID NUMBER(9)
3797 NEW_BALANCE NUMBER(7,2)
3798 PREV_BALANCE NUMBER(7,2)
3799 PAYMENTS NUMBER(7,2)
3800 FINANCE_CHARGE NUMBER(7,2)
3801 CREDIT_LIMIT NUMBER(7)
3802
3803You execute this statement:
3804
3805SELECT ROWNUM "Rank", customer_id, new_balance
3806 FROM (SELECT customer_id, new_balance FROM customer_finance)
3807 WHERE ROWNUM <= 25
3808 ORDER BY new_balance DESC;
3809
3810What statement is true?
3811 Mark for Review
3812(1) Points
3813
3814
3815 The statement failed to execute because an inline view was used.
3816
3817
3818 The 25 greatest new balance values were displayed from the highest to the lowest.
3819
3820
3821 The statement failed to execute because the ORDER BY clause does NOT use the Top-n column.
3822
3823
3824 The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY clause. (*)
3825
3826
3827
3828 Correct
3829
3830
3831
3832
3833 Previous Page 6 of 10 Next Summary
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849Test: Database Programming with SQL Final Exam
3850
3851
3852
3853
3854
3855
3856
3857
3858
3859Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3860
3861
3862
3863 Section 15
3864 (Answer all questions in this section)
3865
3866 31. Which of these is not a valid type of View? Mark for Review
3867(1) Points
3868
3869
3870 ONLINE (*)
3871
3872
3873 COMPLEX
3874
3875
3876 SIMPLE
3877
3878
3879 INLINE
3880
3881
3882
3883 Correct
3884
3885
3886 32. In order to query a database using a view, which of the following statements applies? Mark for Review
3887(1) Points
3888
3889
3890 The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
3891
3892
3893 You can never see all the rows in the table through the view.
3894
3895
3896 Use special VIEW SELECT keywords.
3897
3898
3899 You can retrieve data from a view as you would from any table. (*)
3900
3901
3902
3903 Correct
3904
3905
3906 33. You administer an Oracle database which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first? Mark for Review
3907(1) Points
3908
3909
3910 Create a subquery.
3911
3912
3913 Create a report for him.
3914
3915
3916 Create an index.
3917
3918
3919 Create a view. (*)
3920
3921
3922
3923 Correct
3924
3925
3926 34. Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False? Mark for Review
3927(1) Points
3928
3929
3930 True
3931
3932
3933 False (*)
3934
3935
3936
3937 Correct
3938
3939
3940
3941
3942 Section 16
3943 (Answer all questions in this section)
3944
3945 35. What would you create to make the following statement execute faster?
3946SELECT *
3947 FROM employees
3948 WHERE LOWER(last_name) = 'chang';
3949 Mark for Review
3950(1) Points
3951
3952
3953 A synonym
3954
3955
3956 An index, either a normal or a function_based index (*)
3957
3958
3959 A composite index
3960
3961
3962 Nothing; the performance of this statement cannot be improved.
3963
3964
3965
3966 Correct
3967
3968
3969
3970
3971 Previous Page 7 of 10 Next Summary
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
3985
3986
3987Test: Database Programming with SQL Final Exam
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
3998
3999
4000
4001 Section 16
4002 (Answer all questions in this section)
4003
4004 36. The EMPLOYEES table contains these columns:
4005EMPLOYEE_ID NUMBER NOT NULL, Primary Key
4006 LAST_NAME VARCHAR2 (20)
4007 FIRST_NAME VARCHAR2 (20)
4008 DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
4009 HIRE_DATE DATE DEFAULT SYSDATE
4010 SALARY NUMBER (8,2) NOT NULL
4011
4012On which column is an index automatically created for the EMPLOYEES table?
4013 Mark for Review
4014(1) Points
4015
4016
4017 HIRE_DATE
4018
4019
4020 SALARY
4021
4022
4023 DEPARTMENT_ID
4024
4025
4026 LAST_NAME
4027
4028
4029 EMPLOYEE_ID (*)
4030
4031
4032
4033 Correct
4034
4035
4036 37. You must use a synonym to access another users table. True or False? Mark for Review
4037(1) Points
4038
4039
4040 True
4041
4042
4043 False (*)
4044
4045
4046
4047 Correct
4048
4049
4050 38. To see the most recent value that you fetched from a sequence named my_seq you should reference: Mark for Review
4051(1) Points
4052
4053
4054 my_seq.nextval
4055
4056
4057 my_seq.(currval)
4058
4059
4060 my_seq.currval (*)
4061
4062
4063 my_seq.(lastval)
4064
4065
4066
4067 Correct
4068
4069
4070 39. Evaluate this statement:
4071CREATE SEQUENCE line_item_id_seq
4072 MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;
4073
4074What will be the first five numbers generated by this sequence?
4075 Mark for Review
4076(1) Points
4077
4078
4079 100110120130100
4080
4081
4082 The fifth number cannot be generated.
4083
4084
4085 The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)
4086
4087
4088 130120110100130
4089
4090
4091
4092 Correct
4093
4094
4095 40. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
4096(1) Points
4097
4098
4099 The NEXTVAL virtual column increments a sequence by a predetermined value. (*)
4100
4101
4102 The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.
4103
4104
4105 The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.
4106
4107
4108 The NEXTVAL virtual column displays only the physical locations of the rows in a table.
4109
4110
4111
4112 Correct
4113
4114
4115
4116
4117 Previous Page 8 of 10 Next Summary
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129Test: Database Programming with SQL Final Exam
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
4140
4141
4142
4143 Section 17
4144 (Answer all questions in this section)
4145
4146 41. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
4147(1) Points
4148
4149
4150 REGEXP (*)
4151
4152
4153 REGEXP_LIKE
4154
4155
4156 REGEXP_REPLACE
4157
4158
4159 REGEXP_SUBSTR
4160
4161
4162
4163 Correct
4164
4165
4166 42. Parentheses are not used to identify the sub expressions within the expression. True or False? Mark for Review
4167(1) Points
4168
4169
4170 True
4171
4172
4173 False (*)
4174
4175
4176
4177 Correct
4178
4179
4180 43. Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database? Mark for Review
4181(1) Points
4182
4183
4184 OPEN SESSION
4185
4186
4187 CREATE SESSION (*)
4188
4189
4190 ALTER SESSION
4191
4192
4193 RESTRICTED SESSION
4194
4195
4196
4197 Correct
4198
4199
4200 44. Evaluate this statement:
4201ALTER USER bob IDENTIFIED BY jim;
4202
4203Which statement about the result of executing this statement is true?
4204 Mark for Review
4205(1) Points
4206
4207
4208 A new password is assigned to user BOB. (*)
4209
4210
4211 The user BOB is assigned the same privileges as user JIM.
4212
4213
4214 A new user JIM is created from user BOB's profile.
4215
4216
4217 The user BOB is renamed and is accessible as user JIM.
4218
4219
4220
4221 Correct
4222
4223
4224 45. Which of the following statements about granting object privileges is false? Mark for Review
4225(1) Points
4226
4227
4228 To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.
4229
4230
4231 An object owner can grant any object privilege on the object to any other user or role of the database.
4232
4233
4234 Object privileges can only be granted through roles. (*)
4235
4236
4237 The owner of an object automatically acquires all object privileges on that object.
4238
4239
4240
4241 Correct
4242
4243
4244
4245
4246 Previous Page 9 of 10 Next Summary
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262Test: Database Programming with SQL Final Exam
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
4273
4274
4275
4276 Section 17
4277 (Answer all questions in this section)
4278
4279 46. User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table? Mark for Review
4280(1) Points
4281
4282
4283 Yes
4284
4285
4286 No (*)
4287
4288
4289
4290 Correct
4291
4292
4293 47. Which statement would you use to add privileges to a role? Mark for Review
4294(1) Points
4295
4296
4297 ASSIGN
4298
4299
4300 GRANT (*)
4301
4302
4303 CREATE ROLE
4304
4305
4306 ALTER ROLE
4307
4308
4309
4310 Correct
4311
4312
4313
4314
4315 Section 18
4316 (Answer all questions in this section)
4317
4318 48. Examine the following statements:
4319UPDATE employees SET salary = 15000;
4320 SAVEPOINT upd1_done;
4321 UPDATE employees SET salary = 22000;
4322 SAVEPOINT upd2_done;
4323 DELETE FROM employees;
4324
4325You want to retain all the employees with a salary of 15000; What statement would you execute next?
4326 Mark for Review
4327(1) Points
4328
4329
4330 ROLLBACK;
4331
4332
4333 ROLLBACK TO SAVEPOINT upd1_done; (*)
4334
4335
4336 ROLLBACK TO SAVEPOINT upd2_done;
4337
4338
4339 ROLLBACK TO SAVE upd1_done;
4340
4341
4342 There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.
4343
4344
4345
4346 Correct
4347
4348
4349 49. If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
4350(1) Points
4351
4352
4353 True (*)
4354
4355
4356 False
4357
4358
4359
4360 Correct
4361
4362
4363
4364
4365 Section 19
4366 (Answer all questions in this section)
4367
4368 50. Testing is done by programmers. True or False? Mark for Review
4369(1) Points
4370
4371
4372 True (*)
4373
4374
4375 False
4376
4377
4378
4379 Correct
4380
4381
4382
4383
4384 Previous Page 10 of 10 Summary
4385
4386
4387
4388
4389
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401 Previous Page 3 of 3 Summary