siFvd8vi

1Section 12 Quiz
2            (Answer all questions in this section)
3                                                           
41.         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
5(1) Points
6            True (*)
7            False
8
92.         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
10(1) Points
11
12            A great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified.
13
14            A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified.
15
16            Both a and b are correct.
17
18            A bad idea. The default value must match the DATE datatype of the column. (*)
19
203.         Aliases can be used with MERGE statements. True or False?  Mark for Review
21(1) Points
22            True (*)
23            False
24
254.         Multi-table inserts can be conditional or unconditional. True or False?           Mark for Review
26(1) Points
27            True (*)
28            False
29
305.         A multi-table insert statement can insert into more than one table. (True or False?)     Mark for Review
31(1) Points
32            True (*)
33            False
34
356.         Which of the following represents the correct syntax for an INSERT statement?        Mark for Review
36(1) Points
37            INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
38            INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '37777';
39
40            INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777'); (*)
41
42            INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;
43
447.         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:
45EMPLOYEE_ID NUMBER(5) PRIMARY KEY
46LAST_NAME VARCHAR2(20)
47FIRST_NAME VARCHAR2(20)
48ADDRESS VARCHAR2(30)
49PHONE NUMBER(10)
50
51Which DELETE statement will delete the appropriate record without deleting any additional records?
52
53 Mark for Review
54(1) Points
55
56            DELETE FROM employees
57WHERE employee_id = 348;
58(*)
59
60            DELETE FROM employees
61WHERE last_name = jones;
62
63            DELETE 'jones'
64FROM employees;
65
66            DELETE *
67FROM employees
68WHERE employee_id = 348;
69
708.         The EMPLOYEES table contains the following columns:
71EMPLOYEE_ID NUMBER(10) PRIMARY KEY
72LAST_NAME VARCHAR2(20)
73FAST_NAME VARCHAR2(20)
74DEPARTMENT_ID VARCHAR2(20)
75HIRE_DATE DATE
76SALARY NUMBER(9,2)
77BONUS NUMBER(9,2)
78
79You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?
80
81 Mark for Review
82(1) Points
83
84            UPDATE employees
85SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
86WHERE department_id = 10;
87(*)
88
89            UPDATE employees
90SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);
91
92            UPDATE employees
93SET salary = SELECT salary FROM employees WHERE employee_id = 89898;
94
95            UPDATE employees
96SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);
97
989.         If the subquery returns one row, how many rows will be deleted from the employees table?
99DELETE FROM employees
100WHERE department_id =
101    (SELECT department_id
102     FROM departments
103     WHERE department_name LIKE '%Public%');
104
105 Mark for Review
106(1) Points
107            One row will be deleted, as the subquery only returns one row.
108            All rows in the employees table will be deleted, no matter the department_id.
109            All rows in the employees table of employees who work in the given department will be deleted. (*)
110            No rows will be deleted.
111
11210.       What keyword in an UPDATE statement speficies the column that you want to change?        Mark for Review
113(1) Points
114            SET (*)
115            SELECT
116            WHERE
117            HAVING
118
11911.       To return a table summary on the customers table, which of the following is correct?            Mark for Review
120(1) Points
121            DEFINE customers, or DEF customers
122            SHOW customers, or SEE customers
123            DISTINCT customers, or DIST customers
124            DESCRIBE customers, or DESC customers (*)
125
12612.       The PRODUCTS table contains these columns:
127PRODUCT_ID NUMBER NOT NULL
128PRODUCT_NAME VARCHAR2 (25)
129SUPPLIER_ID NUMBER NOT NULL
130LIST_PRICE NUMBER (7,2)
131COST NUMBER (5,2)
132QTY_IN_STOCK NUMBER(4)
133LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
134
135Which INSERT statement will execute successfully?
136
137 Mark for Review
138(1) Points
139
140            INSERT INTO products(product_id, product_name)
141VALUES (2958, 'Cable');
142
143            INSERT INTO products(product_id, product_name, supplier_id
144VALUES (2958, 'Cable', 8690, SYSDATE);
145
146            INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
147VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)
148
149            INSERT INTO products
150VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
151
15213.       Assume all the column names are correct. The following SQL statement will execute which of the following?
153INSERT INTO departments
154(department_id, department_name, manager_id, location_id)
155VALUES (70, 'Public Relations', 100, 1700);
156
157 Mark for Review
158(1) Points
159            'Public Relations' will be inserted into the manager_name column.
160            70 will be inserted into the department_id column. (*)
161            1700 will be inserted into the manager_id column.
162            100 will be inserted into the department_id column.
163
16414.       You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task?       Mark for Review
165(1) Points
166            A subquery (*)
167            A function
168            A SET clause
169            An ON clause
170
17115.       The STUDENTS table contains these columns:
172STU_ID NUMBER(9) NOT NULL
173LAST_NAME VARCHAR2 (30) NOT NULL
174FIRST_NAME VARCHAR2 (25) NOT NULL
175DOB DATE
176STU_TYPE_ID VARCHAR2(1) NOT NULL
177ENROLL_DATE DATE
178
179You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement:
180
181INSERT INTO ft_students
182   (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
183FROM students
184WHERE UPPER(stu_type_id) = 'F');
185
186What is the result of executing this INSERT statement?
187
188 Mark for Review
189(1) Points
190            An error occurs because the INSERT statement does NOT contain a VALUES clause.
191            All full-time students are inserted into the FT_STUDENTS table. (*)
192            An error occurs because the FT_STUDENTS table already exists.
193            An error occurs because you CANNOT use a subquery in an INSERT statement.
194
1951.         Which of the following statements will add a new customer to the customers table in the Global Fast Foods database?          Mark for Review
196(1) Points
197
198            INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
199VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);
200(*)
201
202            INSERT IN customers (id, first_name, last_name, address, city, state, zip, phone_number);
203
204            INSERT INTO customers
205(id 145, first_name 'Katie', last_name 'Hernandez', address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number 8586667641);
206
207            INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
208VALUES ("145", 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', "98008", "8586667641");
209
2102.         Which statement about the VALUES clause of an INSERT statement is true? Mark for Review
211(1) Points
212            To specify a null value in the VALUES clause, use an empty string (" ").
213
214            Character, date, and numeric data must be enclosed within single quotes in the VALUES clause.
215            The VALUES clause in an INSERT statement is mandatory in a subquery.
216
217            If no column list is specified, the values must be listed in the same order that the columns are listed in the table. (*)
218
2193.         If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
220INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
221SELECT employee_id, first_name, last_name, salary, department_id
222FROM employees
223 Mark for Review
224(1) Points
225            No rows, as the SELECT statement is invalid.
226            10 rows will be created.
227            No rows, as you cannot use subqueries in an insert statement.
228            7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)
229
2304.         Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column?           Mark for Review
231(1) Points
232            It is not possible to implicitly insert a null value in a column.
233            Omit the column in the column list. (*)
234            Use the NULL keyword.
235            Use the ON clause
236
2375.         Insert statements can be combined with subqueries to create more than one row per statement. True or False?  Mark for Review
238(1) Points
239            True (*)
240            False
241
2426.         Which statement below will not insert a row of data into a table?        Mark for Review
243(1) Points
244
245            INSERT INTO student_table
246VALUES (143354, 'Roberts', 'Cameron', 6543);
247
248            INSERT INTO (id, lname, fname, lunch_num)
249VALUES (143354, 'Roberts', 'Cameron', 6543);
250(*)
251
252            INSERT INTO student_table (id, lname, fname, lunch_num)
253VALUES (143352, 'Roberts', 'Cameron', DEFAULT);
254
255            INSERT INTO student_table (id, lname, fname, lunch_num)
256VALUES (143354, 'Roberts', 'Cameron', 6543);
257
2587.         Aliases can be used with MERGE statements. True or False?  Mark for Review
259(1) Points
260            True (*)
261            False
262
2638.         Multi-table inserts are used when the same source data should be inserted into _____________ target table.    Mark for Review
264(1) Points
265            A very large
266            A data warehouse
267            Ten
268            More than one (*)
269
2709.         The DEFAULT keyword can be used in the following statements:      Mark for Review
271(1) Points
272            INSERT and UPDATE (*)
273            INSERT and DELETE
274            DELETE and UPDATE
275            All of the above
276
27710.       The default value must match the __________ of the column.           Mark for Review
278(1) Points
279            Datatype (*)
280            Table
281            Column name
282            Size
283
28411.       One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
285EMPLOYEE_ID NUMBER(10) PRIMARY KEY
286LAST_NAME VARCHAR2(20)
287FIRST_NAME VARCHAR2(20)
288DEPARTMENT_ID VARCHAR2 (20)
289HIRE_DATE DATE
290SALARY NUMBER(10)
291
292Which UPDATE statement will accomplish your objective?
293
294 Mark for Review
295(1) Points
296
297            UPDATE employees
298SET cooper = 'last_name'
299WHERE last_name = 'roper';
300
301            UPDATE employees last_name = 'cooper'
302WHERE last_name = 'roper';
303
304            UPDATE employees
305SET last_name = 'cooper'
306WHERE last_name = 'roper'; (*)
307
308            UPDATE employees
309SET last_name = 'roper'
310WHERE last_name = 'cooper';
311
31212.       To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?            Mark for Review
313(1) Points
314            True
315            False (*)
316
31713.       One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change?            Mark for Review
318(1) Points
319            INSERT INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
320            INSERT my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
321            UPDATE INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
322            UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; (*)
323
32414.       The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
325TEACHERS:
326TEACHER_ID NUMBER(5)
327NAME VARCHAR2(25)
328SUBJECT_ID NUMBER(5)
329HIRE_DATE DATE
330SALARY NUMBER(9,2)
331
332CLASS_ASSIGNMENTS:
333CLASS_ID NUMBER(5)
334TEACHER_ID NUMBER(5)
335START_DATE DATE
336MAX_CAPACITY NUMBER(3)
337
338Which scenario would require a subquery to return the desired results?
339
340 Mark for Review
341(1) Points
342            You need to display the start date for each class taught by a given teacher.
343            You need to create a report to display the teachers who were hired more than five years ago.
344            You need to display the names of the teachers who teach classes that start within the next week.
345            You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)
346
34715.       You need to remove a row from the EMPLOYEES table. Which statement would you use?   Mark for Review
348(1) Points
349            UPDATE with a WHERE clause
350            DELETE with a WHERE clause (*)
351            MERGE with a WHERE clause
352            INSERT with a WHERE clause
353
3541.         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
355(1) Points
356            True (*)
357            False
358
3592.         Multi-table inserts are used when the same source data should be inserted into _____________ target table.    Mark for Review
360(1) Points
361            A data warehouse
362            A very large
363            More than one (*)
364            Ten
365
3663.         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
367(1) Points
368
369            A great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified.
370
371            A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified.
372
373            Both a and b are correct.
374            A bad idea. The default value must match the DATE datatype of the column. (*)
375
3764.         Aliases can be used with MERGE statements. True or False?  Mark for Review
377(1) Points
378            True (*)
379            False
380
3815.         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
382(1) Points
383            True (*)
384            False
385
3866.         Assume all the column names are correct. The following SQL statement will execute which of the following?
387INSERT INTO departments
388(department_id, department_name, manager_id, location_id)
389VALUES (70, 'Public Relations', 100, 1700);
390
391 Mark for Review
392(1) Points
393            100 will be inserted into the department_id column.
394            1700 will be inserted into the manager_id column.
395            'Public Relations' will be inserted into the manager_name column.
396            70 will be inserted into the department_id column. (*)
397
3987.         To return a table summary on the customers table, which of the following is correct?            Mark for Review
399(1) Points
400            DESCRIBE customers, or DESC customers (*)
401            DISTINCT customers, or DIST customers
402            SHOW customers, or SEE customers
403            DEFINE customers, or DEF customers
404
4058.         The PRODUCTS table contains these columns:
406PRODUCT_ID NUMBER NOT NULL
407PRODUCT_NAME VARCHAR2 (25)
408SUPPLIER_ID NUMBER NOT NULL
409LIST_PRICE NUMBER (7,2)
410COST NUMBER (5,2)
411QTY_IN_STOCK NUMBER(4)
412LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
413
414Which INSERT statement will execute successfully?
415
416 Mark for Review
417(1) Points
418
419            INSERT INTO products
420VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
421
422            INSERT INTO products(product_id, product_name)
423VALUES (2958, 'Cable');
424
425            INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
426VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)
427
428            INSERT INTO products(product_id, product_name, supplier_id
429VALUES (2958, 'Cable', 8690, SYSDATE);
430
4319.         Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column?           Mark for Review
432(1) Points
433            It is not possible to implicitly insert a null value in a column.
434            Use the NULL keyword.
435            Omit the column in the column list. (*)
436            Use the ON clause
437
43810.       DML is an acronym that stands for:     Mark for Review
439(1) Points
440            Debit Markup Language
441            Data Markup Language
442            Data Manipulation Language (*)
443            Don't Manipulate Language
444
44511.       Using your knowledge of the employees table, what would be the result of the following statement:
446DELETE FROM employees;    Mark for Review
447(1) Points
448            Deletes employee number 100.
449            All rows in the employees table will be deleted if there are no constraints on the table. (*)
450            Nothing, no data will be changed.
451            The first row in the employees table will be deleted.
452
45312.       Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
454PLAYERS:
455PLAYER_ID NUMBER Primary Key
456LAST_NAME VARCHAR2 (30)
457FIRST_NAME VARCHAR2 (25)
458TEAM_ID NUMBER
459MGR_ID NUMBER
460SIGNING_BONUS NUMBER(9,2)
461SALARY NUMBER(9,2)
462
463MANAGERS:
464MANAGER_ID NUMBER Primary Key
465LAST_NAME VARCHAR2 (20)
466FIRST_NAME VARCHAR2 (20)
467TEAM_ID NUMBER
468
469TEAMS:
470TEAM_ID NUMBER Primary Key
471TEAM_NAME VARCHAR2 (20)
472OWNER_LAST_NAME VARCHAR2 (20)
473OWNER_FIRST_NAME VARCHAR2 (20)
474
475Which situation would require a subquery to return the desired result?
476
477 Mark for Review
478(1) Points
479
480            To display the names of each player on the Lions team
481            To display the maximum and minimum player salary for each team
482            To display the names of the managers for all the teams owned by a given owner (*)
483            To display each player, their manager, and their team name for all teams with an id value greater than 5000
484
48513.       You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
486UPDATE employee
487SET area_code = 770
488
489Which of the following should you include in your UPDATE statement to achieve the desired results?
490
491 Mark for Review
492(1) Points
493
494            WHERE city = 'Atlanta'; (*)
495            UPDATE city = Atlanta;
496            SET city = 'Atlanta';
497            LIKE 'At%';
498
49914.       One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
500EMPLOYEE_ID NUMBER(10) PRIMARY KEY
501LAST_NAME VARCHAR2(20)
502FIRST_NAME VARCHAR2(20)
503DEPARTMENT_ID VARCHAR2 (20)
504HIRE_DATE DATE
505SALARY NUMBER(10)
506
507Which UPDATE statement will accomplish your objective?
508
509 Mark for Review
510(1) Points
511
512            UPDATE employees last_name = 'cooper'
513WHERE last_name = 'roper';
514
515            UPDATE employees
516SET last_name = 'roper'
517WHERE last_name = 'cooper';
518
519            UPDATE employees
520SET cooper = 'last_name'
521WHERE last_name = 'roper';
522
523            UPDATE employees
524SET last_name = 'cooper'
525WHERE last_name = 'roper'; (*)
526
52715.       What would happen if you issued a DELETE statement without a WHERE clause?    Mark for Review
528(1) Points
529            All the rows in the table would be deleted. (*)
530            Only one row would be deleted.
531            No rows would be deleted.
532
533Section 13 Quiz
534            (Answer all questions in this section)
535
5361.         To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
537(1) Points
538            DATE
539            INTERVAL DAY TO SECOND
540            INTERVAL YEAR TO MONTH
541            TIMESTAMP (*)
542
5432.         The ELEMENTS column is defined as:
544NUMBER(6,4)
545How many digits to the right of the decimal point are allowed for the ELEMENTS column?
546
547 Mark for Review
548(1) Points
549            Four (*)
550            Zero
551            Six
552            Two
553
5543.         The TIMESTAMP data type allows what?       Mark for Review
555(1) Points
556            Time to be stored as an interval of years and months.
557            Time to be stored as a date with fractional seconds. (*)
558            Time to be stored as an interval of days to hours, minutes and seconds.
559            None of the above.
560
5614.         Which data types stores variable-length character data? Select two.    Mark for Review
562(1) Points
563                                    (Choose all correct answers)   
564            NCHAR
565            CHAR
566            CLOB (*)
567            VARCHAR2 (*)
568
5695.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
570(1) Points
571            True (*)
572            False
573
5746.         A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
575(1) Points
576            True
577            False (*)
578
5797.         You can use the ALTER TABLE statement to:            Mark for Review
580(1) Points
581            Add a new column
582            Modify an existing column
583            Drop a column
584            All of the above (*)
585
5868.         Evaluate this statement:
587ALTER TABLE inventory
588MODIFY backorder_amount NUMBER(8,2);
589
590Which task will this statement accomplish?
591
592 Mark for Review
593(1) Points
594            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
595            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)
596            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
597            Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)
598            Alters the definition of the BACKORDER_AMOUNT column to NUMBER
599
6009.         You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use?    Mark for Review
601(1) Points
602            TRUNCATE TABLE
603            DELETE TABLE
604            ALTER TABLE
605            DROP TABLE (*)
606
60710.       When you use ALTER TABLE to add a column, the new column:     Mark for Review
608(1) Points
609            Becomes the last column in the table (*)
610            Becomes the first column in the table
611            Will not be created because you cannot add a column after the table is created
612            Can be placed by adding a GROUP BY clause
613
61411.       It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?  Mark for Review
615(1) Points
616            True (*)
617            False
618
61912.       I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
620(1) Points
621
622            possible; our data will merge into one table, and we can more easily access our mutual friends information.
623
624            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
625
626            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
627
628            impossible; School_Friends is a reserved term in SQL.
629
63013.       Which statement about table and column names is true?         Mark for Review
631(1) Points
632            Table and column names cannot include special characters.
633            Table and column names can begin with a letter or a number.
634            Table and column names must begin with a letter. (*)
635            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.
636
63714.       You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?    Mark for Review
638(1) Points
639            commission_pct NUMBER(4,2) IS DEFAULT 0.10
640            commission_pct NUMBER(4,2) DEFAULT 0.10 (*)
641            commission_pct NUMBER(4,2) (DEFAULT, 0.10)
642            commission_pct NUMBER(4,2) DEFAULT = 0.10
643
64415.       Evaluate this CREATE TABLE statement:
6451. CREATE TABLE customer#1 (
6462. cust_1 NUMBER(9),
6473. sales$ NUMBER(9),
6484. 2date DATE DEFAULT SYSDATE);
649
650Which line of this statement will cause an error?
651
652 Mark for Review
653(1) Points
654            3
655            1
656            4 (*)
657            2
658
6591.         The TIMESTAMP data type allows what?       Mark for Review
660(1) Points
661            Time to be stored as an interval of years and months.
662            Time to be stored as a date with fractional seconds. (*)
663            Time to be stored as an interval of days to hours, minutes and seconds.
664            None of the above.
665
6662.         Which statement about data types is true?       Mark for Review
667(1) Points
668            The TIMESTAMP data type is a character data type.
669            The VARCHAR2 data type should be used for fixed-length character data.
670            The BFILE data type stores character data up to four gigabytes in the database.
671            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. (*)
672
6733.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
674(1) Points
675            True (*)
676            False
677
6784.         You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?     Mark for Review
679(1) Points
680            CHAR
681            DATE (*)
682            INTERVAL YEAR TO MONTH
683            TIMESTAMP
684
6855.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?          Mark for Review
686(1) Points
687            True
688            False (*)
689
6906.         Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.  Mark for Review
691(1) Points
692
693            CREATE TABLE employee
694AS SELECT employee_id, first_name, last_name, salary, department_id
695FROM employees;
696
697            CREATE TABLE emp
698AS SELECT employee_id, first_name, last_name, salary, department_id
699FROM employees;
700(*)
701
702            CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);
703
704            CREATE TABLE emp
705SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);
706
7077.         Which CREATE TABLE statement will fail?   Mark for Review
708(1) Points
709            CREATE TABLE time_date (time NUMBER(9));
710            CREATE TABLE date_1 (date_1 DATE);
711            CREATE TABLE date (date_id NUMBER(9)); (*)
712            CREATE TABLE time (time_id NUMBER(9));
713
7148.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
715(1) Points
716
717            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
718
719            possible; our data will merge into one table, and we can more easily access our mutual friends information.
720
721            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
722
723            impossible; School_Friends is a reserved term in SQL.
724
7259.         Which statement about table and column names is true?         Mark for Review
726(1) Points
727            Table and column names must begin with a letter. (*)
728            Table and column names can begin with a letter or a number.
729            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.
730            Table and column names cannot include special characters.
731
73210.       CREATE TABLE student_table
733    (id NUMBER(6),
734     lname VARCHAR(20),
735     fname VARCHAR(20),
736     lunch_num NUMBER(4));
737Which of the following statements best describes the above SQL statement:
738
739 Mark for Review
740(1) Points
741            Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
742            Creates a table named student_table with four columns: lname, fname, lunch, num
743            Creates a table named student with four columns: id, lname, fname, lunch_num
744            Creates a table named student_table with four columns: lname, fname, lunch, num
745
74611.       A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
747(1) Points
748            True
749            False (*)
750
75112.       You want to issue the following command on a database that includes your company's inventory information:
752ALTER TABLE products SET UNUSED COLUMN color;
753What will be the result of issuing this command?
754
755 Mark for Review
756(1) Points
757            The column named COLOR in the table named PRODUCTS will be created.
758            The column named COLOR in the table named PRODUCTS will be assigned default values.
759            The column named COLOR in the table named PRODUCTS will be deleted.
760            The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)
761
76213.       The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue?         Mark for Review
763(1) Points
764            ALTER TABLE
765            DROP TABLE (*)
766            DELETE
767            TRUNCATE TABLE
768
76914.       The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
770(1) Points
771            True
772            False (*)
773
77415.       Evaluate this statement:
775Which statement about this TRUNCATE TABLE statement is true?    Mark for Review
776(1) Points
777            You can produce the same results by issuing the 'DROP TABLE employee' statement.
778            You can issue this statement to retain the structure of the employees table. (*)
779            You can reverse this statement by issuing the ROLLBACK statement.
780            You can produce the same results by issuing the 'DELETE employees' statement.
781
7821.         You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?     Mark for Review
783(1) Points
784            ALTER TABLE employees RENAME TO emp;
785            RENAME employees emp;
786            RENAME employees TO emp; (*)
787            ALTER TABLE employees TO emp;
788
7892.         You can use the ALTER TABLE statement to:            Mark for Review
790(1) Points
791            Add a new column
792            Modify an existing column
793            Drop a column
794            All of the above (*)
795
7963.         The TEAMS table contains these columns:
797TEAM_ID NUMBER(4) Primary Key
798TEAM_NAME VARCHAR2(20)
799MGR_ID NUMBER(9)
800
801The 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?
802
803 Mark for Review
804(1) Points
805
806            ALTER teams TABLE
807MODIFY COLUMN (mgr_id VARCHAR2(15));
808
809            ALTER TABLE teams
810REPLACE (mgr_id VARCHAR2(15));
811
812            ALTER teams
813MODIFY (mgr_id VARCHAR2(15));
814
815            ALTER TABLE teams
816MODIFY (mgr_id VARCHAR2(15));
817(*)
818
819            You CANNOT modify the data type of the MGR_ID column.
820
8214.         Evaluate the structure of the EMPLOYEE table:
822EMPLOYEE_ID NUMBER(9)
823LAST_NAME VARCHAR2(25)
824FIRST_NAME VARCHAR2(25)
825DEPARTMENT_ID NUMBER(9)
826MANAGER_ID NUMBER(9)
827SALARY NUMBER(7,2)
828
829Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records?
830
831 Mark for Review
832(1) Points
833
834            ALTER TABLE employee
835RENAME last_name VARCHAR2(35);
836
837            ALTER employee TABLE
838ALTER COLUMN (last_name VARCHAR2(35));
839
840            ALTER TABLE employee
841MODIFY (last_name VARCHAR2(35));
842(*)
843            You CANNOT increase the width of the LAST_NAME column.
844
8455.         Which statement about a column is NOT true?            Mark for Review
846(1) Points
847            You can convert a DATE data type column to a VARCHAR2 column.
848            You can increase the width of a CHAR column.
849            You can modify the data type of a column if the column contains non-null data. (*)
850            You can convert a CHAR data type column to the VARCHAR2 data type.
851
8526.         Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
853(1) Points
854            True (*)
855            False
856
8577.         Evaluate this CREATE TABLE statement:
8581. CREATE TABLE customer#1 (
8592. cust_1 NUMBER(9),
8603. sales$ NUMBER(9),
8614. 2date DATE DEFAULT SYSDATE);
862
863Which line of this statement will cause an error?
864
865 Mark for Review
866(1) Points
867            4 (*)
868            1
869            3
870            2
871
8728.         Which column name is valid?  Mark for Review
873(1) Points
874            NUMBER
875            1NUMBER
876            NUMBER_1$ (*)
877            1_NUMBER#
878
8799.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
880(1) Points
881            impossible; School_Friends is a reserved term in SQL.
882
883            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
884
885            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
886
887            possible; our data will merge into one table, and we can more easily access our mutual friends information.
888
88910.       DCL, which is the acronym for Data Control Language, allows:         Mark for Review
890(1) Points
891            The ALTER command to be used.
892            The TRUNCATE command to be used.
893            A Database Administrator the ability to grant privileges to users. (*)
894            The CONROL TRANSACTION statement can be used.
895
89611.       The TIMESTAMP data type allows what?       Mark for Review
897(1) Points
898            Time to be stored as an interval of years and months.
899            Time to be stored as a date with fractional seconds. (*)
900            Time to be stored as an interval of days to hours, minutes and seconds.
901            None of the above.
902
90312.       A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?         Mark for Review
904(1) Points
905            LONGRAW
906            LONG
907            NUMBER
908            BLOB (*)
909
91013.       Which of the following are valid Oracle datatypes?    Mark for Review
911(1) Points
912            TIMESTAMP, LOB, VARCHAR2, NUMBER
913            DATE, BLOB, LOB, VARCHAR2
914            DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
915            SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE
91614.       Which statement about data types is true?       Mark for Review
917(1) Points
918
919            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. (*)
920
921            The BFILE data type stores character data up to four gigabytes in the database.
922
923            The VARCHAR2 data type should be used for fixed-length character data.
924
925            The TIMESTAMP data type is a character data type.
926
92715.       Which data types stores variable-length character data? Select two.    Mark for Review
928(1) Points
929                                                           
930                                    (Choose all correct answers)   
931                                                           
932            NCHAR
933            VARCHAR2 (*)
934            CLOB (*)
935            CHAR
936
9371.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
938(1) Points
939            True (*)
940            False
941
9422.         You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?     Mark for Review
943(1) Points
944            TIMESTAMP
945            INTERVAL YEAR TO MONTH
946            CHAR
947            DATE (*)
948
9493.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?          Mark for Review
950(1) Points
951            True
952            False (*)
953
9544.         A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?         Mark for Review
955(1) Points
956            NUMBER
957            LONGRAW
958            BLOB (*)
959            LONG
960
9615.         Evaluate this CREATE TABLE statement:
962CREATE TABLE sales
963( sales_id NUMBER(9),
964customer_id NUMBER(9),
965employee_id NUMBER(9),
966description VARCHAR2(30),
967sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,
968sale_amount NUMBER(7,2));
969
970Which business requirement will this statement accomplish?
971
972 Mark for Review
973(1) Points
974            Description values can range from 0 to 30 characters so the column should be fixed in length.
975
976            All employee identification values are only 6 digits so the column should be variable in length.
977
978            Sales identification values could be either numbers or characters, or a combination of both.
979
980            Today's date should be used if no value is provided for the sale date. (*)
981
9826.         Examine the structure of the DONATIONS table.
983DONATIONS:
984PLEDGE_ID NUMBER
985DONOR_ID NUMBER
986PLEDGE_DT DATE
987AMOUNT_PLEDGED NUMBER (7,2)
988AMOUNT_PAID NUMBER (7,2)
989PAYMENT_DT DATE
990
991You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?
992
993 Mark for Review
994(1) Points
995            You must use the ADD OR REPLACE option to achieve these results.
996            You must drop and recreate the DONATIONS table to achieve these results.
997            Both changes can be accomplished with one ALTER TABLE statement. (*)
998            You CANNOT decrease the width of the AMOUNT_PLEDGED column.
999
10007.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?      Mark for Review
1001(1) Points
1002            MODIFY
1003            ALTER TABLE
1004            DROP TABLE
1005            TRUNCATE TABLE (*)
1006
10078.         You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?  Mark for Review
1008(1) Points
1009            The TRUNCATE TABLE statement (*)
1010            The ALTER TABLE statement
1011            The DROP TABLE statement
1012            The DELETE statement
1013
10149.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
1015(1) Points
1016            True
1017            False (*)
1018
101910.       You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use?    Mark for Review
1020(1) Points
1021            TRUNCATE TABLE
1022            DELETE TABLE
1023            ALTER TABLE
1024            DROP TABLE (*)
1025
102611.       Evaluate this CREATE TABLE statement:
1027CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
1028
1029You 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?
1030
1031 Mark for Review
1032(1) Points
1033            You created the table in the SYSDBA schema.
1034            You created the LINE_ITEM table in the SYS schema.
1035            You created the LINE_ITEM table in the public schema.
1036            You created the table in your schema. (*)
1037
103812.       CREATE TABLE bioclass
1039    (hire_date DATE DEFAULT SYSDATE,
1040     first_name varchar2(15),
1041     last_name varchar2(15));
1042The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name, and last_name column. True or False?
1043
1044 Mark for Review
1045(1) Points
1046            True (*)
1047            False
1048
104913.       I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
1050(1) Points
1051
1052            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
1053
1054            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
1055
1056            possible; our data will merge into one table, and we can more easily access our mutual friends information.
1057
1058            impossible; School_Friends is a reserved term in SQL.
1059
106014.       Given this employee table:
1061(employee_id NUMBER(10) NOT NULL,
1062first_name VARCHAR2(25) NOT NULL,
1063last_name VARCHAR2(30) NOT NULL,
1064hire_date DATE DEFAULT sysdate)
1065
1066What will be the result in the hire_date column following this insert statement:
1067
1068INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);
1069
1070 Mark for Review
1071(1) Points
1072                                                           
1073            Statement will fail, as you must list the columns into which you are inserting.
1074            The column for hire_date will be null.
1075
1076            Statement will work and the hire_date column will have the value of the date when the statement was run. (*)
1077
1078            The character string SYSDATE.
1079
108015.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
1081(1) Points
1082            True (*)
1083            False
1084
10851.         It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?  Mark for Review
1086(1) Points
1087            True (*)
1088            False
1089
10902.         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
1091(1) Points
1092            2001_PRODUCTS
1093            PRODUCTS_(2001)
1094            PRODUCTS_2001 (*)
1095            PRODUCTS--2001
1096
10973.         You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?           Mark for Review
1098(1) Points
1099                                   
1100            CREATE TABLE travel
1101(destination_id primary key, departure_date date, return_date date, emp_id REFERENCES employees (emp_id));
1102
1103            CREATE TABLE travel
1104(destination_id number primary key, departure_date date, return_date date, t.emp_id = e.emp_id);
1105
1106            CREATE TABLE travel
1107(destination_id number primary key, departure_date date, return_date date, JOIN emp_id number(10) ON employees (emp_id));
1108
1109            CREATE TABLE travel
1110(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));
1111(*)
1112
11134.         Which statement about table and column names is true?         Mark for Review
1114(1) Points
1115                                   
1116            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.
1117
1118            Table and column names can begin with a letter or a number.
1119            Table and column names cannot include special characters.
1120            Table and column names must begin with a letter. (*)
1121
11225.         DCL, which is the acronym for Data Control Language, allows:         Mark for Review
1123(1) Points
1124            The ALTER command to be used.
1125            The TRUNCATE command to be used.
1126            A Database Administrator the ability to grant privileges to users. (*)
1127            The CONROL TRANSACTION statement can be used.
1128
11296.         Evaluate this CREATE TABLE statement:
1130CREATE TABLE sales
1131(sales_id NUMBER,
1132customer_id NUMBER,
1133employee_id NUMBER,
1134sale_date TIMESTAMP WITH TIME ZONE,
1135sale_amount NUMBER(7,2));
1136
1137Which statement about the SALE_DATE column is true?
1138
1139 Mark for Review
1140(1) Points
1141            Data will be stored using a fractional seconds precision of 5.
1142            Data will be normalized to the client time zone.
1143            Data stored will not include seconds.
1144            Data stored in the column will be returned in the database's local time zone. (*)
1145
11467.         A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?           Mark for Review
1147(1) Points
1148            DATETIME
1149            INTERVAL YEAR TO MONTH
1150            TIMESTAMP
1151            INTERVAL DAY TO SECOND (*)
1152
11538.         You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
1154(1) Points
1155            NUMBER (*)
1156            VARCHAR2
1157            DATE
1158            CHAR
1159
11609.         To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
1161(1) Points
1162            DATE
1163            INTERVAL YEAR TO MONTH
1164            TIMESTAMP (*)
1165            INTERVAL DAY TO SECOND
1166
116710.       Evaluate this CREATE TABLE statement:
1168CREATE TABLE sales
1169( sales_id NUMBER(9),
1170customer_id NUMBER(9),
1171employee_id NUMBER(9),
1172description VARCHAR2(30),
1173sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,
1174sale_amount NUMBER(7,2));
1175
1176Which business requirement will this statement accomplish?
1177
1178 Mark for Review
1179(1) Points
1180            Description values can range from 0 to 30 characters so the column should be fixed in length.
1181
1182            All employee identification values are only 6 digits so the column should be variable in length.
1183
1184            Sales identification values could be either numbers or characters, or a combination of both.
1185
1186            Today's date should be used if no value is provided for the sale date. (*)
1187
118811.       RENAME old_name to new_name can be used to:     Mark for Review
1189(1) Points
1190            Rename a row.
1191            Rename a column.
1192            Rename a table. (*)
1193            All of the above.
1194
119512.       The data type of a column can never be changed once it has been created. True or False?     Mark for Review
1196(1) Points
1197            True
1198            False (*)
1199
120013.       When you use ALTER TABLE to add a column, the new column:     Mark for Review
1201(1) Points
1202            Can be placed by adding a GROUP BY clause
1203            Will not be created because you cannot add a column after the table is created
1204            Becomes the first column in the table
1205            Becomes the last column in the table (*)
1206
120714.       You want to issue the following command on a database that includes your company's inventory information:
1208ALTER TABLE products SET UNUSED COLUMN color;
1209What will be the result of issuing this command?
1210
1211 Mark for Review
1212(1) Points
1213            The column named COLOR in the table named PRODUCTS will be deleted.
1214
1215            The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)
1216
1217            The column named COLOR in the table named PRODUCTS will be created.
1218
1219            The column named COLOR in the table named PRODUCTS will be assigned default values.
1220
122115.       The TEAMS table contains these columns:
1222TEAM_ID NUMBER(4) Primary Key
1223TEAM_NAME VARCHAR2(20)
1224MGR_ID NUMBER(9)
1225
1226The 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?
1227
1228 Mark for Review
1229(1) Points
1230                                   
1231            ALTER TABLE teams
1232MODIFY (mgr_id VARCHAR2(15));
1233(*)
1234
1235            ALTER TABLE teams
1236REPLACE (mgr_id VARCHAR2(15));
1237
1238            You CANNOT modify the data type of the MGR_ID column.
1239
1240            ALTER teams
1241MODIFY (mgr_id VARCHAR2(15));
1242
1243            ALTER teams TABLE
1244MODIFY COLUMN (mgr_id VARCHAR2(15));
1245
12461.         You can use the ALTER TABLE statement to:            Mark for Review
1247(1) Points
1248            Add a new column
1249            Modify an existing column
1250            Drop a column
1251            All of the above (*)
1252
12532.         When should you use the SET UNUSED command?  Mark for Review
1254(1) Points
1255
1256            You should only use this command if you want the column to still be visible when you DESCRIBE the table.
1257            You should use it if you think the column may be needed again later.
1258
1259            You should use it when you need a quick way of dropping a column. (*)
1260
1261            Never, there is no SET UNUSED command.
1262
12633.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?      Mark for Review
1264(1) Points
1265            ALTER TABLE
1266            DROP TABLE
1267            TRUNCATE TABLE (*)
1268            MODIFY
1269
12704.         Comments on tables and columns can be stored for documentation by:          Mark for Review
1271(1) Points
1272            Using the ALTER TABLE CREATE COMMENT syntax
1273            Embedding /* comment */ within the definition of the table.
1274            Using an UPDATE statement on the USER_COMMENTS table
1275            Using the COMMENT ON TABLE or COMMENT on COLUMN (*)
1276
12775.         Evaluate this statement:
1278ALTER TABLE employees SET UNUSED (fax);
1279Which task will this statement accomplish?
1280
1281 Mark for Review
1282(1) Points
1283            Deletes the FAX column
1284            Frees the disk space used by the data in the FAX column
1285            Prevents a new FAX column from being added to the EMPLOYEES table
1286            Prevents data in the FAX column from being displayed, by performing a logical drop of the column (*)
1287
12886.         Examine this CREATE TABLE statement:
1289CREATE TABLE emp_load
1290(employee_number CHAR(5),
1291employee_dob CHAR(20),
1292employee_last_name CHAR(20),
1293employee_first_name CHAR(15),
1294employee_middle_name CHAR(15),
1295employee_hire_date DATE)
1296ORGANIZATION EXTERNAL
1297(TYPE ORACLE_LOADER
1298DEFAULT DIRECTORY def_dir1
1299ACCESS PARAMETERS
1300(RECORDS DELIMITED BY NEWLINE
1301FIELDS (employee_number CHAR(2),
1302      employee_dob CHAR(20),
1303      employee_last_name CHAR(18),
1304      employee_first_name CHAR(11),
1305      employee_middle_name CHAR(11),
1306      employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"))
1307LOCATION ('info.dat'));
1308
1309What kind of table is created here?
1310
1311 Mark for Review
1312(1) Points
1313            An external table with the data stored in a file outside the database. (*)
1314            A View.
1315            An external table with the data stored in a file inside the database.
1316            None. This is in invalid statement.
1317
13187.         Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.  Mark for Review
1319(1) Points
1320
1321            CREATE TABLE emp
1322AS SELECT employee_id, first_name, last_name, salary, department_id
1323FROM employees;
1324(*)
1325
1326            CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);
1327
1328            CREATE TABLE employee
1329AS SELECT employee_id, first_name, last_name, salary, department_id
1330FROM employees;
1331
1332            CREATE TABLE emp
1333SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);
1334
13358.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
1336(1) Points
1337
1338            possible; our data will merge into one table, and we can more easily access our mutual friends information.
1339
1340            impossible; School_Friends is a reserved term in SQL.
1341
1342            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
1343
1344            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
1345
13469.         When creating a new table, which of the following naming rules apply. (Choose three)         Mark for Review
1347(1) Points
1348                                    (Choose all correct answers)   
1349
1350            Can have the same name as another object owned by the same user
1351            Must begin with a letter (*)
1352            Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
1353            Must be between 1 to 30 characters long (*)
1354            Must be an Oracle reserved word
1355
135610.       Which CREATE TABLE statement will fail?   Mark for Review
1357(1) Points
1358            CREATE TABLE time_date (time NUMBER(9));
1359            CREATE TABLE time (time_id NUMBER(9));
1360            CREATE TABLE date_1 (date_1 DATE);
1361            CREATE TABLE date (date_id NUMBER(9)); (*)
1362
136311.       INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
1364(1) Points
1365            True (*)
1366            False
1367
136812.       Which of the following are valid Oracle datatypes?    Mark for Review
1369(1) Points
1370            DATE, BLOB, LOB, VARCHAR2
1371            TIMESTAMP, LOB, VARCHAR2, NUMBER
1372            DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
1373            SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE
1374
137513.       A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?           Mark for Review
1376(1) Points
1377            TIMESTAMP
1378            INTERVAL YEAR TO MONTH
1379            DATETIME
1380            INTERVAL DAY TO SECOND (*)
1381
138214.       You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
1383(1) Points
1384            CHAR
1385            NUMBER (*)
1386            DATE
1387            VARCHAR2
1388
138915.       Which data types stores variable-length character data? Select two.    Mark for Review
1390(1) Points
1391                                    (Choose all correct answers)   
1392            NCHAR
1393            CLOB (*)
1394            CHAR
1395            VARCHAR2 (*)
1396
13971.         You need to store the SEASONAL data in months and years. Which data type should you use?        Mark for Review
1398(1) Points
1399            INTERVAL YEAR TO MONTH (*)
1400            TIMESTAMP
1401            INTERVAL DAY TO SECOND
1402            DATE
1403
14042.         Evaluate this CREATE TABLE statement:
1405CREATE TABLE sales
1406( sales_id NUMBER(9),
1407customer_id NUMBER(9),
1408employee_id NUMBER(9),
1409description VARCHAR2(30),
1410sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,
1411sale_amount NUMBER(7,2));
1412
1413Which business requirement will this statement accomplish?
1414
1415 Mark for Review
1416(1) Points
1417            Today's date should be used if no value is provided for the sale date. (*)
1418            Sales identification values could be either numbers or characters, or a combination of both.
1419            All employee identification values are only 6 digits so the column should be variable in length.
1420            Description values can range from 0 to 30 characters so the column should be fixed in length.
1421
14223.         The TIMESTAMP data type allows what?       Mark for Review
1423(1) Points
1424            Time to be stored as an interval of years and months.
1425            Time to be stored as a date with fractional seconds. (*)
1426            Time to be stored as an interval of days to hours, minutes and seconds.
1427            None of the above.
1428
14294.         Which statement about data types is true?       Mark for Review
1430(1) Points
1431            The VARCHAR2 data type should be used for fixed-length character data.
1432            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. (*)
1433            The TIMESTAMP data type is a character data type.
1434            The BFILE data type stores character data up to four gigabytes in the database.
1435
14365.         You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
1437(1) Points
1438            CHAR
1439            DATE
1440            NUMBER (*)
1441            VARCHAR2
1442
14436.         The TEAMS table contains these columns:
1444TEAM_ID NUMBER(4) Primary Key
1445TEAM_NAME VARCHAR2(20)
1446MGR_ID NUMBER(9)
1447
1448The 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?
1449
1450 Mark for Review
1451(1) Points
1452
1453            ALTER TABLE teams
1454REPLACE (mgr_id VARCHAR2(15));
1455
1456            ALTER teams TABLE
1457MODIFY COLUMN (mgr_id VARCHAR2(15));
1458
1459            ALTER TABLE teams
1460MODIFY (mgr_id VARCHAR2(15));
1461(*)
1462
1463            You CANNOT modify the data type of the MGR_ID column.
1464
1465            ALTER teams
1466MODIFY (mgr_id VARCHAR2(15));
1467
14687.         Evaluate this statement:
1469ALTER TABLE inventory
1470MODIFY backorder_amount NUMBER(8,2);
1471
1472Which task will this statement accomplish?
1473
1474 Mark for Review
1475(1) Points
1476            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
1477            Alters the definition of the BACKORDER_AMOUNT column to NUMBER
1478            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
1479            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)
1480            Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)
1481
14828.         Which statement about decreasing the width of a column is true?       Mark for Review
1483(1) Points
1484
1485            You cannot decrease the width of a character column unless the table in which the column resides is empty.
1486
1487            When a character column contains data, you can decrease the width of the column if the
1488existing data does not violate the new size. (*)
1489
1490            When a character column contains data, you cannot decrease the width of the column.
1491
1492            When a character column contains data, you can decrease the width of the column without any restrictions.
1493
14949.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
1495(1) Points
1496            True
1497            False (*)
1498
149910.       A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
1500(1) Points
1501            True
1502            False (*)
1503
150411.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
1505(1) Points
1506            True (*)
1507            False
1508
150912.       CREATE TABLE student_table
1510    (id NUMBER(6),
1511     lname VARCHAR(20),
1512     fname VARCHAR(20),
1513     lunch_num NUMBER(4));
1514Which of the following statements best describes the above SQL statement:
1515
1516 Mark for Review
1517(1) Points
1518            Creates a table named student_table with four columns: lname, fname, lunch, num
1519            Creates a table named student with four columns: id, lname, fname, lunch_num
1520            Creates a table named student_table with four columns: lname, fname, lunch, num
1521            Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
1522
152313.       Which statement about creating a table is true?            Mark for Review
1524(1) Points
1525
1526            If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE
1527statement will fail.
1528
1529            With a CREATE TABLE statement, a table will always be created in the current user's schema.
1530
1531            If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)
1532
1533            If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.
1534
153514.       Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.  Mark for Review
1536(1) Points
1537
1538            CREATE TABLE emp
1539AS SELECT employee_id, first_name, last_name, salary, department_id
1540FROM employees;
1541(*)
1542
1543            CREATE TABLE employee
1544AS SELECT employee_id, first_name, last_name, salary, department_id
1545FROM employees;
1546
1547            CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);
1548
1549            CREATE TABLE emp
1550SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);
1551
155215.       Evaluate this CREATE TABLE statement:
15531. CREATE TABLE customer#1 (
15542. cust_1 NUMBER(9),
15553. sales$ NUMBER(9),
15564. 2date DATE DEFAULT SYSDATE);
1557
1558Which line of this statement will cause an error?
1559
1560 Mark for Review
1561(1) Points
1562            2
1563            4 (*)
1564            1
1565
1566            3
1567
1568
1569
15701.         Examine the structures of the PRODUCTS and SUPPLIERS tables.
1571PRODUCTS:
1572PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
1573PRODUCT_NAME VARCHAR2 (25)
1574SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
1575LIST_PRICE NUMBER (7,2)
1576COST NUMBER (7,2)
1577QTY_IN_STOCK NUMBER
1578QTY_ON_ORDER NUMBER
1579REORDER_LEVEL NUMBER
1580REORDER_QTY NUMBER
1581
1582SUPPLIERS:
1583SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
1584SUPPLIER_NAME VARCHAR2 (25)
1585ADDRESS VARCHAR2 (30)
1586CITY VARCHAR2 (25)
1587REGION VARCHAR2 (10)
1588POSTAL_CODE VARCHAR2 (11)
1589
1590Evaluate this statement:
1591
1592ALTER TABLE suppliers
1593DISABLE CONSTRAINT supplier_id_pk CASCADE;
1594
1595For which task would you issue this statement?
1596
1597 Mark for Review
1598(1) Points
1599
1600            To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
1601
1602            To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
1603
1604            To remove all constraint references to SUPPLIERS table
1605
1606            To drop the FOREIGN KEY constraint on the PRODUCTS table
1607
1608            To remove all constraint references to the PRODUCTS table
1609
16102.         The PO_DETAILS table contains these columns:
1611PO_NUM NUMBER NOT NULL, Primary Key
1612PO_LINE_ID NUMBER NOT NULL, Primary Key
1613PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table
1614QUANTITY NUMBER
1615UNIT_PRICE NUMBER(5,2)
1616
1617Evaluate this statement:
1618
1619ALTER TABLE po_details
1620DISABLE CONSTRAINT product_id_pk CASCADE;
1621
1622For which task would you issue this statement?
1623
1624 Mark for Review
1625(1) Points
1626
1627            To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)
1628
1629            To create a new PRIMARY KEY constraint on the PO_NUM column
1630            To drop and recreate the PRIMARY KEY constraint on the PO_NUM column
1631            To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
1632
16333.         Which of the following would definitely cause an integrity constraint error?  Mark for Review
1634(1) Points
1635
1636            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. (*)
1637
1638            Using the UPDATE command on rows based in another table.
1639            Using a subquery in an INSERT statement.
1640            Using the MERGE statement to conditionally insert or update rows.
1641
16424.         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
1643(1) Points
1644            CASCADE (*)
1645            ON DELETE SET NULL
1646            FOREIGN KEY
1647            REFERENCES
1648
16495.         All of a user's constraints can be viewed in the Oracle Data Dictionary view called:   Mark for Review
1650(1) Points
1651            CONSTRAINTS
1652            USER_CONSTRAINTS (*)
1653            TABLE_CONSTRAINTS
1654            USER_TABLES
1655
16566.         Evaluate the structure of the DONATIONS table.
1657DONATIONS:
1658PLEDGE_ID NUMBER NOT NULL, Primary Key
1659DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
1660PLEDGE_DT DATE
1661AMOUNT_PLEDGED NUMBER (7,2)
1662AMOUNT_PAID NUMBER (7,2)
1663PAYMENT_DT DATE
1664
1665Which CREATE TABLE statement should you use to create the DONATIONS table?
1666
1667 Mark for Review
1668(1) Points
1669
1670            CREATE TABLE donations
1671(pledge_id NUMBER PRIMARY KEY,
1672donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
1673pledge_date DATE,
1674amount_pledged NUMBER(7,2),
1675amount_paid NUMBER(7,2),
1676payment_dt DATE);
1677(*)
1678
1679            CREATE TABLE donations
1680pledge_id NUMBER PRIMARY KEY,
1681donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
1682pledge_date DATE,
1683amount_pledged NUMBER(7,2),
1684amount_paid NUMBER(7,2),
1685payment_dt DATE;
1686
1687            CREATE TABLE donations
1688(pledge_id NUMBER PRIMARY KEY NOT NULL,
1689donor_id NUMBER FOREIGN KEY donors(donor_id),
1690pledge_date DATE,
1691amount_pledged NUMBER(7,2),
1692amount_paid NUMBER(7,2),
1693payment_dt DATE);
1694
1695            CREATE TABLE donations
1696(pledge_id NUMBER PRIMARY KEY,
1697donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
1698pledge_date DATE,
1699amount_pledged NUMBER,
1700amount_paid NUMBER,
1701payment_dt DATE);
1702
17037.         Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
1704(1) Points
1705            CHECK CONSTRAINT part_cost_ck (cost > 1.00)
1706            CONSTRAINT CHECK cost > 1.00
1707            CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
1708            CONSTRAINT CHECK part_cost_ck (cost > 1.00)
1709
17108.         What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
1711(1) Points
1712
1713            A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.
1714
1715            A CHECK constraint must exist on the Parent table.
1716            An index must exist on the Parent table
1717            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
1718
17199.         Evaluate this CREATE TABLE statement:
1720CREATE TABLE part(
1721part_id NUMBER,
1722part_name VARCHAR2(25),
1723manufacturer_id NUMBER(9),
1724retail_price NUMBER(7,2) NOT NULL,
1725CONSTRAINT part_id_pk PRIMARY KEY(part_id),
1726CONSTRAINT cost_nn NOT NULL(cost),
1727CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
1728Which line will cause an error?
1729
1730 Mark for Review
1731(1) Points
1732            5
1733            6
1734            7 (*)
1735            8
1736
173710.       Which type of constraint by default requires that a column be both unique and not null?       Mark for Review
1738(1) Points
1739            UNIQUE
1740            FOREIGN KEY
1741            PRIMARY KEY (*)
1742            CHECK
1743
174411.       You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
1745Which type of constraint should you define on the LAST_NAME column?   Mark for Review
1746(1) Points
1747            CHECK (*)
1748            PRIMARY KEY
1749            UNIQUE
1750            NOT NULL
1751
175212.       Which constraint can only be created at the column level?      Mark for Review
1753(1) Points
1754            NOT NULL (*)
1755            UNIQUE
1756            FOREIGN KEY
1757            CHECK
1758
175913.       Evaluate this CREATE TABLE statement:
1760CREATE TABLE customers
1761    (customer_id NUMBER,
1762     customer_name VARCHAR2(25),
1763     address VARCHAR2(25),
1764     city VARCHAR2(25),
1765     region VARCHAR2(25),
1766     postal_code VARCHAR2(11),
1767     CONSTRAINT customer_id_un UNIQUE(customer_id),
1768     CONSTRAINT customer_name_nn NOT NULL(customer_name));
1769
1770Why does this statement fail when executed?
1771
1772 Mark for Review
1773(1) Points
1774            UNIQUE constraints must be defined at the column level.
1775            The NUMBER data types require precision values.
1776            The CREATE TABLE statement does NOT define a PRIMARY KEY.
1777            NOT NULL constraints CANNOT be defined at the table level. (*)
1778
177914.       You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column?       Mark for Review
1780(1) Points
1781            PRIMARY KEY
1782            NOT NULL (*)
1783            UNIQUE
1784            CHECK
1785
178615.       A table must have at least one not null constraint and one unique constraint. True or False?  Mark for Review
1787(1) Points
1788            True
1789            False (*)
1790
17911.         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
1792(1) Points
1793            REFERENCES
1794            FOREIGN KEY
1795            CASCADE (*)
1796            ON DELETE SET NULL
1797
17982.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
1799(1) Points
1800            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
1801            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
1802            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
1803            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
1804
18053.         What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?      Mark for Review
1806(1) Points
1807            Nothing extra is created when Primary Keys and Unique Keys are created
1808
1809            Unique key indexes are created in the background by Oracle when Primary key and Unique key constraints are created or enabled (*)
1810            Internal Pointers
1811            Ordered Lists
1812
18134.         You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue? Mark for Review
1814(1) Points
1815
1816            ALTER TABLE salary
1817ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
1818
1819            ALTER TABLE salary
1820ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
1821BETWEEN salary (employee_id) AND employees (employee_id);
1822
1823            ALTER TABLE salary
1824ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
1825REFERENCES employees (employee_id);
1826(*)
1827
1828            ALTER TABLE salary
1829FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);
1830
18315.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
1832(1) Points
1833
1834            ALTER TABLE table_name
1835DROP CONSTRAINT constraint_name;
1836
1837            DROP CONSTRAINT table_name (constraint_name);
1838
1839            ALTER TABLE table_name
1840DROP CONSTRAINT constraint_name CASCADE;
1841(*)
1842
1843            ALTER TABLE table_name
1844DROP CONSTRAINT FOREIGN KEY CASCADE;
1845
18466.         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
1847(1) Points
1848
1849            ALTER TABLE part
1850MODIFY COLUMN (cost part_cost_nn NOT NULL);
1851
1852            ALTER TABLE part
1853MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
1854(*)
1855
1856            ALTER TABLE part
1857ADD (cost CONSTRAINT part_cost_nn NOT NULL);
1858
1859            ALTER TABLE part
1860MODIFY (cost part_cost_nn NOT NULL);
1861
18627.         You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
1863(1) Points
1864            CHECK
1865            PRIMARY KEY
1866            UNIQUE (*)
1867            NOT NULL
1868
18698.         Which statement about the NOT NULL constraint is true?      Mark for Review
1870(1) Points
1871            The NOT NULL constraint requires a column to contain alphanumeric values.
1872            The NOT NULL constraint can be defined at either the column level or the table level.
1873            The NOT NULL constraint must be defined at the column level. (*)
1874            The NOT NULL constraint prevents a column from containing alphanumeric values.
1875
18769.         Which two statements about NOT NULL constraints are true? (Choose two)  Mark for Review
1877(1) Points
1878                                    (Choose all correct answers)   
1879            The NOT NULL constraint requires that every value in a column be unique.
1880            The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)
1881            A NOT NULL constraint can be defined at either the table or column level.
1882
1883            You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE
1884ADD CONSTRAINT statement. (*)
1885
1886            Columns with a NOT NULL constraint can contain null values by default.
1887
188810.       What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
1889(1) Points
1890            5
1891            10
1892            3
1893            You can have as many NOT NULL constraints as you have columns in your table. (*)
1894
189511.       Which line of the following code will cause an error:
1896CREATE TABLE clients
1897(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
1898first_name VARCHAR2(14),
1899last_name VARCHAR2(13),
1900hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
1901department_id VARCHAR(3),
1902CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
1903 Mark for Review
1904(1) Points
1905            Line 2
1906            Line 3
1907            Line 5 (*)
1908            Line 7
1909
191012.       Evaluate the structure of the DONATIONS table.
1911DONATIONS:
1912PLEDGE_ID NUMBER NOT NULL, Primary Key
1913DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
1914PLEDGE_DT DATE
1915AMOUNT_PLEDGED NUMBER (7,2)
1916AMOUNT_PAID NUMBER (7,2)
1917PAYMENT_DT DATE
1918
1919Which CREATE TABLE statement should you use to create the DONATIONS table?
1920Mark for Review
1921(1) Points
1922
1923            CREATE TABLE donations
1924(pledge_id NUMBER PRIMARY KEY,
1925donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
1926pledge_date DATE,
1927amount_pledged NUMBER,
1928amount_paid NUMBER,
1929payment_dt DATE);
1930
1931            CREATE TABLE donations
1932pledge_id NUMBER PRIMARY KEY,
1933donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
1934pledge_date DATE,
1935amount_pledged NUMBER(7,2),
1936amount_paid NUMBER(7,2),
1937payment_dt DATE;
1938
1939            CREATE TABLE donations
1940(pledge_id NUMBER PRIMARY KEY NOT NULL,
1941donor_id NUMBER FOREIGN KEY donors(donor_id),
1942pledge_date DATE,
1943amount_pledged NUMBER(7,2),
1944amount_paid NUMBER(7,2),
1945payment_dt DATE);
1946
1947            CREATE TABLE donations
1948(pledge_id NUMBER PRIMARY KEY,
1949donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
1950pledge_date DATE,
1951amount_pledged NUMBER(7,2),
1952amount_paid NUMBER(7,2),
1953payment_dt DATE);
1954(*)
1955
195613.       A Primary Key that is made up of more than one column is called a:  Mark for Review
1957(1) Points
1958            Multiple Primary Key
1959            Composite Primary Key (*)
1960            Double Key
1961            Primary Multi-Key
1962            None of the Above
1963
196414.       Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?    Mark for Review
1965(1) Points
1966            REFERENTIAL
1967            ON DELETE CASCADE
1968            REFERENCES (*)
1969            RESEMBLES
1970
197115.       What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
1972(1) Points
1973
1974            A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.
1975
1976            A CHECK constraint must exist on the Parent table.
1977            An index must exist on the Parent table
1978            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
1979
19801.         A composite primary key may only be defined at the table level. True or False?        Mark for Review
1981(1) Points
1982            True (*)
1983            False
1984
19852.         Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?    Mark for Review
1986(1) Points
1987            ON DELETE CASCADE
1988            REFERENCES (*)
1989            RESEMBLES
1990            REFERENTIAL
1991
19923.         You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?            Mark for Review
1993(1) Points
1994            NOT NULL
1995            UNIQUE
1996            PRIMARY KEY
1997            FOREIGN KEY (*)
1998
19994.         What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
2000(1) Points
2001            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
2002            A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.
2003            A CHECK constraint must exist on the Parent table.
2004            An index must exist on the Parent table
2005
20065.         Which constraint type enforces uniqueness?   Mark for Review
2007(1) Points
2008            NOT NULL
2009            PRIMARY KEY (*)
2010            CHECK
2011            FOREIGN KEY
2012
20136.         You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
2014(1) Points
2015            UNIQUE (*)
2016            PRIMARY KEY
2017            CHECK
2018            NOT NULL
2019
20207.         Which of the following is not a valid Oracle constraint type?  Mark for Review
2021(1) Points
2022            NOT NULL
2023            EXTERNAL KEY (*)
2024            UNIQUE KEY
2025            PRIMARY KEY
2026
20278.         Which statement about constraints is true?      Mark for Review
2028(1) Points
2029            NOT NULL constraints can only be specified at the column level. (*)
2030            A single column can have only one constraint applied.
2031            PRIMARY KEY constraints can only be specified at the column level.
2032            UNIQUE constraints are identical to PRIMARY KEY constraints.
2033
20349.         If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
2035(1) Points
2036            True (*)
2037            False
2038
203910.       You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
2040Which type of constraint should you define on the LAST_NAME column?   Mark for Review
2041(1) Points
2042            PRIMARY KEY
2043            NOT NULL
2044            CHECK (*)
2045            UNIQUE
2046
204711.       You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?           Mark for Review
2048(1) Points
2049            CHANGE
2050            MODIFY (*)
2051            ADD
2052            DISABLE
2053
205412.       You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
2055(1) Points
2056            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
2057            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
2058            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
2059            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
2060
206113.       You want to disable the FOREIGN KEY constraint that is defined in the EMPLOYEES table on the DEPARTMENT_ID column. The constraint is referenced by the name FK_DEPT_ID_01. Which statement should you issue?        Mark for Review
2062(1) Points
2063
2064            ALTER TABLE employees
2065DISABLE fk_dept_id_01;
2066
2067            ALTER TABLE employees
2068DISABLE CONSTRAINT fk_dept_id_01;
2069(*)
2070
2071            ALTER TABLE employees
2072DISABLE CONSTRAINT 'fk_dept_id_01';
2073
2074            ALTER TABLE employees
2075DISABLE 'fk_dept_id_01';
2076
207714.       All of a user's constraints can be viewed in the Oracle Data Dictionary view called:   Mark for Review
2078(1) Points
2079            CONSTRAINTS
2080            USER_TABLES
2081            USER_CONSTRAINTS (*)
2082            TABLE_CONSTRAINTS
2083
208415.       You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?     Mark for Review
2085(1) Points
2086
2087            ALTER TABLE employees
2088ADD CONSTRAINT PRIMARY KEY (emp_id);
2089
2090            ALTER TABLE employees
2091ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)
2092
2093            ALTER TABLE employees
2094MODIFY CONSTRAINT PRIMARY KEY (emp_id);
2095
2096            ALTER TABLE employees
2097MODIFY emp_id PRIMARY KEY;
2098
20991.         A Primary Key that is made up of more than one column is called a:  Mark for Review
2100(1) Points
2101            Multiple Primary Key
2102            Composite Primary Key (*)
2103            Double Key
2104            Primary Multi-Key
2105            None of the Above
2106
21072.         Evaluate the structure of the DONATIONS table.
2108DONATIONS:
2109PLEDGE_ID NUMBER NOT NULL, Primary Key
2110DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
2111PLEDGE_DT DATE
2112AMOUNT_PLEDGED NUMBER (7,2)
2113AMOUNT_PAID NUMBER (7,2)
2114PAYMENT_DT DATE
2115
2116Which CREATE TABLE statement should you use to create the DONATIONS table?
2117
2118 Mark for Review
2119(1) Points
2120
2121            CREATE TABLE donations
2122(pledge_id NUMBER PRIMARY KEY,
2123donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
2124pledge_date DATE,
2125amount_pledged NUMBER(7,2),
2126amount_paid NUMBER(7,2),
2127payment_dt DATE);
2128(*)
2129
2130            CREATE TABLE donations
2131(pledge_id NUMBER PRIMARY KEY NOT NULL,
2132donor_id NUMBER FOREIGN KEY donors(donor_id),
2133pledge_date DATE,
2134amount_pledged NUMBER(7,2),
2135amount_paid NUMBER(7,2),
2136payment_dt DATE);
2137
2138            CREATE TABLE donations
2139pledge_id NUMBER PRIMARY KEY,
2140donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
2141pledge_date DATE,
2142amount_pledged NUMBER(7,2),
2143amount_paid NUMBER(7,2),
2144payment_dt DATE;
2145
2146            CREATE TABLE donations
2147(pledge_id NUMBER PRIMARY KEY,
2148donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
2149pledge_date DATE,
2150amount_pledged NUMBER,
2151amount_paid NUMBER,
2152payment_dt DATE);
2153
21543.         To automatically delete rows in a child table when a parent record is deleted use:      Mark for Review
2155(1) Points
2156
2157            ON DELETE SET NULL
2158            ON DELETE ORPHAN
2159            ON DELETE CASCADE (*)
2160            None of the Above
2161
21624.         The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?        Mark for Review
2163(1) Points
2164            ON DELETE CASCADE
2165            ON DELETE SET NULL
2166            Neither A nor B (*)
2167            Both A and B
2168
21695.         Foreign Key Constraints are also known as:    Mark for Review
2170(1) Points
2171            Parental Key Constraints
2172            Child Key Constraints
2173            Referential Integrity Constraints (*)
2174            Multi-Table Constraints
2175
21766.         You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
2177ALTER TABLE employees
2178ENABLE employee_id_pk;
2179
2180Which statement is true?
2181
2182 Mark for Review
2183(1) Points
2184            The statement will NOT execute because it contains a syntax error. (*)
2185            The statement will execute, but will ensure that the new ID values are unique.
2186            The statement will execute, but will not verify that the existing values are unique.
2187            The statement will achieve the desired result.
21887.         You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue? Mark for Review
2189(1) Points
2190
2191            ALTER TABLE salary
2192ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
2193
2194            ALTER TABLE salary
2195FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);
2196
2197            ALTER TABLE salary
2198ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
2199BETWEEN salary (employee_id) AND employees (employee_id);
2200
2201            ALTER TABLE salary
2202ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
2203REFERENCES employees (employee_id);
2204(*)
2205
22068.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
2207(1) Points
2208
2209            ALTER TABLE table_name
2210DROP CONSTRAINT constraint_name;
2211
2212            ALTER TABLE table_name
2213DROP CONSTRAINT constraint_name CASCADE;
2214(*)
2215
2216            ALTER TABLE table_name
2217DROP CONSTRAINT FOREIGN KEY CASCADE;
2218
2219            DROP CONSTRAINT table_name (constraint_name);
2220
22219.         The LINE_ITEM table contains these columns:
2222LINE_ITEM_ID NUMBER PRIMARY KEY
2223PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
2224QUANTITY NUMBER(9)
2225UNIT_PRICE NUMBER(5,2)
2226
2227You need to disable the FOREIGN KEY constraint. Which statement should you use?
2228
2229 Mark for Review
2230(1) Points
2231
2232            ALTER TABLE line_item
2233DROP CONSTRAINT product_id_fk;
2234
2235            ALTER TABLE line_item
2236DISABLE CONSTRAINT product_id_fk;
2237(*)
2238
2239            ALTER TABLE line_item
2240ENABLE CONSTRAINT product_id_fk;
2241
2242            ALTER TABLE line_item
2243DELETE CONSTRAINT product_id_fk;
2244
224510.       This SQL command will do what?
2246ALTER TABLE employees
2247ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
2248
2249 Mark for Review
2250(1) Points
2251
2252            Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.
2253
2254            Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)
2255
2256            Alter the table employees and disable the emp_manager_fk constraint.
2257
2258            Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.
2259
226011.       You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
2261Which type of constraint should you define on the LAST_NAME column?   Mark for Review
2262(1) Points
2263            PRIMARY KEY
2264            NOT NULL
2265            CHECK (*)
2266            UNIQUE
2267
226812.       Which constraint can only be created at the column level?      Mark for Review
2269(1) Points
2270            CHECK
2271            UNIQUE
2272            FOREIGN KEY
2273            NOT NULL (*)
2274
227513.       Evaluate this CREATE TABLE statement:
2276CREATE TABLE customers
2277    (customer_id NUMBER,
2278     customer_name VARCHAR2(25),
2279     address VARCHAR2(25),
2280     city VARCHAR2(25),
2281     region VARCHAR2(25),
2282     postal_code VARCHAR2(11),
2283     CONSTRAINT customer_id_un UNIQUE(customer_id),
2284     CONSTRAINT customer_name_nn NOT NULL(customer_name));
2285
2286Why does this statement fail when executed?
2287
2288 Mark for Review
2289(1) Points
2290            NOT NULL constraints CANNOT be defined at the table level. (*)
2291            The CREATE TABLE statement does NOT define a PRIMARY KEY.
2292            The NUMBER data types require precision values.
2293            UNIQUE constraints must be defined at the column level.
2294
229514.       Which of the following is not a valid Oracle constraint type?  Mark for Review
2296(1) Points
2297            PRIMARY KEY
2298            UNIQUE KEY
2299            EXTERNAL KEY (*)
2300            NOT NULL
2301
230215.       What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
2303(1) Points
2304            5
2305            10
2306            3
2307            You can have as many NOT NULL constraints as you have columns in your table. (*)
2308
23091.         A unique key constraint can only be defined on a not null column. True or False?     Mark for Review
2310(1) Points
2311            True
2312            False (*)
2313
23142.         What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
2315(1) Points
2316            5
2317            10
2318            3
2319            You can have as many NOT NULL constraints as you have columns in your table. (*)
2320
23213.         A table can only have one unique key constraint defined. True or False?       Mark for Review
2322(1) Points
2323            True
2324            False (*)
2325
23264.         Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two)   Mark for Review
2327(1) Points
2328                                    (Choose all correct answers)   
2329            Dictionary
2330            Null Field
2331            Column (*)
2332            Table (*)
2333            Row
2334
23355.         You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
2336Which type of constraint should you define on the LAST_NAME column?   Mark for Review
2337(1) Points
2338            NOT NULL
2339            PRIMARY KEY
2340            CHECK (*)
2341            UNIQUE
2342
23436.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
2344(1) Points
2345            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
2346            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
2347            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
2348            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
2349
23507.         What actions can be performed on or with Constraints?          Mark for Review
2351(1) Points
2352            Add, Drop, Enable, Disable, Cascade (*)
2353            Add, Subtract, Enable, Cascade
2354            Add, Drop, Disable, Disregard
2355            Add, Minus, Enable, Disable, Collapse
2356
23578.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
2358(1) Points
2359
2360            ALTER TABLE table_name
2361DROP CONSTRAINT FOREIGN KEY CASCADE;
2362
2363            DROP CONSTRAINT table_name (constraint_name);
2364
2365            ALTER TABLE table_name
2366DROP CONSTRAINT constraint_name;
2367
2368            ALTER TABLE table_name
2369DROP CONSTRAINT constraint_name CASCADE;
2370(*)
2371
23729.         The command to 'switch off' a constraint is:    Mark for Review
2373(1) Points
2374            ALTER TABLE PAUSE CONSTRAINT
2375            ALTER TABLE STOP CONSTRAINTS
2376            ALTER TABLE DISABLE CONSTRAINT (*)
2377            ALTER TABLE STOP CHECKING
2378
237910.       Examine the structures of the PRODUCTS and SUPPLIERS tables.
2380PRODUCTS:
2381PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
2382PRODUCT_NAME VARCHAR2 (25)
2383SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
2384LIST_PRICE NUMBER (7,2)
2385COST NUMBER (7,2)
2386QTY_IN_STOCK NUMBER
2387QTY_ON_ORDER NUMBER
2388REORDER_LEVEL NUMBER
2389REORDER_QTY NUMBER
2390
2391SUPPLIERS:
2392SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
2393SUPPLIER_NAME VARCHAR2 (25)
2394ADDRESS VARCHAR2 (30)
2395CITY VARCHAR2 (25)
2396REGION VARCHAR2 (10)
2397POSTAL_CODE VARCHAR2 (11)
2398
2399Evaluate this statement:
2400
2401ALTER TABLE suppliers
2402DISABLE CONSTRAINT supplier_id_pk CASCADE;
2403
2404For which task would you issue this statement?
2405
2406 Mark for Review
2407(1) Points
2408            To remove all constraint references to the PRODUCTS table
2409            To remove all constraint references to SUPPLIERS table
2410
2411            To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
2412
2413            To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
2414
2415            To drop the FOREIGN KEY constraint on the PRODUCTS table
2416
241711.       Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
2418(1) Points
2419            CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
2420            CONSTRAINT CHECK cost > 1.00
2421            CHECK CONSTRAINT part_cost_ck (cost > 1.00)
2422            CONSTRAINT CHECK part_cost_ck (cost > 1.00)
2423
242412.       Which line of the following code will cause an error:
2425CREATE TABLE clients
2426(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
2427first_name VARCHAR2(14),
2428last_name VARCHAR2(13),
2429hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
2430department_id VARCHAR(3),
2431CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
2432 Mark for Review
2433(1) Points
2434            Line 2
2435            Line 3
2436            Line 5 (*)
2437            Line 7
2438
243913.       A composite primary key may only be defined at the table level. True or False?        Mark for Review
2440(1) Points
2441            True (*)
2442            False
2443
244414.       Evaluate this CREATE TABLE statement:
2445CREATE TABLE part(
2446part_id NUMBER,
2447part_name VARCHAR2(25),
2448manufacturer_id NUMBER(9),
2449retail_price NUMBER(7,2) NOT NULL,
2450CONSTRAINT part_id_pk PRIMARY KEY(part_id),
2451CONSTRAINT cost_nn NOT NULL(cost),
2452CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
2453Which line will cause an error?
2454
2455 Mark for Review
2456(1) Points
2457            5
2458            6
2459            7 (*)
2460            8
2461
246215.       What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
2463(1) Points
2464            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
2465            A CHECK constraint must exist on the Parent table.
2466
2467            A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.
2468
2469            An index must exist on the Parent table
2470
24711.         You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?            Mark for Review
2472(1) Points
2473            NOT NULL
2474
2475            FOREIGN KEY (*)
2476            PRIMARY KEY
2477            UNIQUE
2478
24792.         A Primary Key that is made up of more than one column is called a:  Mark for Review
2480(1) Points
2481            Multiple Primary Key
2482            Composite Primary Key (*)
2483            Double Key
2484            Primary Multi-Key
2485            None of the Above
2486
24873.         Which of the following best describes the function of a CHECK constraint?  Mark for Review
2488(1) Points
2489
2490            A CHECK constraint enforces uniqueness of the values that can be entered in a column or combination of columns.
2491
2492            A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*)
2493
2494            A CHECK constraint is created automatically when a PRIMARY KEY constraint is created.
2495            A CHECK constraint enforces referential data integrity.
2496
24974.         A composite primary key may only be defined at the table level. True or False?        Mark for Review
2498(1) Points
2499            True (*)
2500            False
2501
25025.         Evaluate this CREATE TABLE statement:
2503CREATE TABLE part(
2504part_id NUMBER,
2505part_name VARCHAR2(25),
2506manufacturer_id NUMBER(9),
2507retail_price NUMBER(7,2) NOT NULL,
2508CONSTRAINT part_id_pk PRIMARY KEY(part_id),
2509CONSTRAINT cost_nn NOT NULL(cost),
2510CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
2511Which line will cause an error?
2512
2513 Mark for Review
2514(1) Points
2515            5
2516            6
2517            7 (*)
2518            8
2519
25206.         Evaluate this statement:
2521ALTER TABLE employees
2522ADD CONSTRAINT employee_id PRIMARY KEY;
2523
2524Which result will the statement provide?
2525
2526 Mark for Review
2527(1) Points
2528            An existing constraint on the EMPLOYEES table will be overwritten.
2529            A constraint will be added to the EMPLOYEES table.
2530            A syntax error will be returned. (*)
2531            An existing constraint on the EMPLOYEES table will be enabled.
2532
25337.         The LINE_ITEM table contains these columns:
2534LINE_ITEM_ID NUMBER PRIMARY KEY
2535PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
2536QUANTITY NUMBER(9)
2537UNIT_PRICE NUMBER(5,2)
2538
2539You need to disable the FOREIGN KEY constraint. Which statement should you use?
2540
2541 Mark for Review
2542(1) Points
2543
2544            ALTER TABLE line_item
2545DELETE CONSTRAINT product_id_fk;
2546
2547            ALTER TABLE line_item
2548DISABLE CONSTRAINT product_id_fk;
2549(*)
2550
2551            ALTER TABLE line_item
2552ENABLE CONSTRAINT product_id_fk;
2553
2554            ALTER TABLE line_item
2555DROP CONSTRAINT product_id_fk;
2556
25578.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
2558(1) Points
2559            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
2560            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
2561            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
2562            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
2563
25649.         Evaluate this statement
2565ALTER TABLE employees
2566ENABLE CONSTRAINT emp_id_pk;
2567
2568For which task would you issue this statement?
2569
2570 Mark for Review
2571(1) Points
2572            To add a new constraint to the EMPLOYEES table
2573            To disable an existing constraint on the EMPLOYEES table
2574            To activate a new constraint while preventing the creation of a PRIMARY KEY index
2575
2576            To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)
2577
257810.       This SQL command will do what?
2579ALTER TABLE employees
2580ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
2581
2582 Mark for Review
2583(1) Points
2584
2585            Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.
2586
2587            Alter the table employees and disable the emp_manager_fk constraint.
2588
2589            Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.
2590
2591            Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)
2592
259311.       A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?       Mark for Review
2594(1) Points
2595            True
2596            False (*)
2597
259812.       You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column?       Mark for Review
2599(1) Points
2600            CHECK
2601            PRIMARY KEY
2602            NOT NULL (*)
2603            UNIQUE
2604
260513.       Which constraint can only be created at the column level?      Mark for Review
2606(1) Points
2607            UNIQUE
2608            CHECK
2609            NOT NULL (*)
2610            FOREIGN KEY
2611
261214.       You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
2613(1) Points
2614            CHECK
2615            NOT NULL
2616            PRIMARY KEY
2617            UNIQUE (*)
2618
261915.       Which statement about constraints is true?      Mark for Review
2620(1) Points
2621            UNIQUE constraints are identical to PRIMARY KEY constraints.
2622            PRIMARY KEY constraints can only be specified at the column level.
2623            NOT NULL constraints can only be specified at the column level. (*)
2624
2625            A single column can have only one constraint applied.
2626By Deni Ace at February 11, 2017   
2627
2628
2629Section 15 Quiz
2630                (Answer all questions in this section)
2631                                                                               
26321.            Evaluate this CREATE VIEW statement:
2633CREATE VIEW emp_view
2634AS SELECT SUM(salary)
2635FROM employees;
2636
2637Which statement is true?
2638
2639 Mark for Review
2640(1) Points
2641                You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*)
2642                You can update any data in the EMPLOYEES table using the EMP_VIEW view.
2643                You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.
2644                You can delete records from the EMPLOYEES table using the EMP_VIEW view.
2645
26462.            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
2647(1) Points
2648                FORCE (*)
2649                WITH READ ONLY
2650                NOFORCE
2651                OR REPLACE
2652
26533.            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
2654(1) Points
2655                True
2656                False (*)
2657
26584.            A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?            Mark for Review
2659(1) Points
2660                True
2661                False (*)
2662
26635.            In order to query a database using a view, which of the following statements applies?   Mark for Review
2664(1) Points
2665                You can never see all the rows in the table through the view.
2666                You can retrieve data from a view as you would from any table. (*)
2667                The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
2668                Use special VIEW SELECT keywords.
2669
26706.            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
2671(1) Points
2672                FORCE
2673                WITH CHECK OPTION (*)
2674                WITH READ ONLY
2675                WITH CONSTRAINT CHECK
2676
26777.            What is the purpose of including the WITH CHECK OPTION clause when creating a view?               Mark for Review
2678(1) Points
2679                To keep views form being queried by unauthorized persons
2680                To make sure that the parent table(s) actually exist
2681                To make sure that data is not duplicated in the view
2682
2683                To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
2684
26858.            Only one type of view exists. True or False?        Mark for Review
2686(1) Points
2687                True
2688                False (*)
2689
26909.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
2691(1) Points
2692                Prohibits changing rows not returned by the subquery in the view definition. (*)
2693                The view will allow the user to check it against the data dictionary
2694                Prohibits DML actions without administrator CHECK approval
2695                Allows for DELETES from other tables, including ones not listed in subquery
2696
269710.          You cannot insert data through a view if the view includes ______.         Mark for Review
2698(1) Points
2699                A join
2700                A WHERE clause
2701                A column alias
2702                A GROUP BY clause (*)
2703
270411.          When you drop a view, the data it contains is also deleted. True or False?             Mark for Review
2705(1) Points
2706                True
2707                False (*)
2708
270912.          Evaluate this CREATE VIEW statement:
2710CREATE VIEW sales_view
2711AS SELECT customer_id, region, SUM(sales_amount)
2712FROM sales
2713WHERE region IN (10, 20, 30, 40)
2714GROUP BY region, customer_id;
2715
2716Which statement is true?
2717
2718 Mark for Review
2719(1) Points
2720                You can modify data in the SALES table using the SALES_VIEW view.
2721                You can only insert records into the SALES table using the SALES_VIEW view.
2722                The CREATE VIEW statement generates an error.
2723                You cannot modify data in the SALES table using the SALES_VIEW view. (*)
2724
272513.          You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?         Mark for Review
2726(1) Points
2727                The AS keyword
2728                A GROUP BY clause (*)
2729                A WHERE clause
2730                The IN keyword
2731
273214.          How do you remove a view?      Mark for Review
2733(1) Points
2734                DELETE VIEW view_name
2735                REMOVE VIEW view_name
2736                DROP VIEW view_name (*)
2737                You cannot remove a view
2738
273915.          When you drop a table referenced by a view, the view is automatically dropped as well. True or False?  Mark for Review
2740(1) Points
2741                True
2742                False (*)
2743
27441.            Which statement about an inline view is true?   Mark for Review
2745(1) Points
2746                An inline view is a complex view.
2747                An inline view is a subquery in the FROM clause, often named with an alias. (*)
2748                An inline view is a schema object.
2749                An inline view can be used to perform DML operations.
2750
27512.            A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?         Mark for Review
2752(1) Points
2753                True (*)
2754                False
2755
27563.            Which of these Keywords is typically used with a Top-N Analysis?             Mark for Review
2757(1) Points
2758                Number
2759                Rowid
2760                Rownum (*)
2761                Sequence
2762
27634.            You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
2764You issue this statement:
2765CREATE OR REPLACE VIEW CUST_CREDIT_V
2766AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
2767FROM customers c, accounts a
2768WHERE c.account_id = a.account_id WITH READ ONLY;
2769
2770Which type of SQL command can be issued on the CUST_CREDIT_V view?
2771
2772 Mark for Review
2773(1) Points
2774
2775                SELECT (*)
2776                UPDATE
2777                DELETE
2778                INSERT
2779
27805.            Evaluate this CREATE VIEW statement:
2781CREATE VIEW sales_view
2782AS SELECT customer_id, region, SUM(sales_amount)
2783FROM sales
2784WHERE region IN (10, 20, 30, 40)
2785GROUP BY region, customer_id;
2786
2787Which statement is true?
2788
2789 Mark for Review
2790(1) Points
2791                You can only insert records into the SALES table using the SALES_VIEW view.
2792                You can modify data in the SALES table using the SALES_VIEW view.
2793                You cannot modify data in the SALES table using the SALES_VIEW view. (*)
2794                The CREATE VIEW statement generates an error.
2795
27966.            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
2797(1) Points
2798
2799                CREATE VIEW sales_view
2800   AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2801   FROM customers c, orders o
2802   WHERE c.custid = o.custid)
2803WITH READ ONLY;
2804(*)
2805
2806                CREATE VIEW sales_view
2807AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2808FROM customers c, orders o
2809WHERE c.custid = o.custid);
2810
2811                CREATE VIEW sales_view
2812   AS (SELECT companyname, city, orderid, orderdate, total
2813   FROM customers, orders
2814   WHERE custid = custid)
2815WITH READ ONLY;
2816
2817                CREATE VIEW sales_view
2818    (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2819    FROM customers c, orders o
2820    WHERE c.custid = o.custid)
2821WITH READ ONLY;
2822
28237.            You cannot insert data through a view if the view includes ______.         Mark for Review
2824(1) Points
2825                A WHERE clause
2826                A GROUP BY clause (*)
2827                A column alias
2828                A join
2829
28308.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
2831(1) Points
2832                Prohibits DML actions without administrator CHECK approval
2833                The view will allow the user to check it against the data dictionary
2834                Prohibits changing rows not returned by the subquery in the view definition. (*)
2835                Allows for DELETES from other tables, including ones not listed in subquery
2836
28379.            Which statement about performing DML operations on a view is true?  Mark for Review
2838(1) Points
2839
2840                You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.
2841
2842                You can perform DML operations on simple views. (*)
2843
2844                You can perform DML operations on a view that contains the WITH READ ONLY option.
2845
2846                You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause.
2847
284810.          Which option would you use when creating a view to ensure that no DML operations occur on the view?                Mark for Review
2849(1) Points
2850                NOFORCE
2851                FORCE
2852                WITH ADMIN OPTION
2853                WITH READ ONLY (*)
2854
285511.          Unlike tables, views contain no data of their own. True or False?               Mark for Review
2856(1) Points
2857                True (*)
2858                False
2859
286012.          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
2861(1) Points
2862                True
2863                False (*)
2864
286513.          A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?            Mark for Review
2866(1) Points
2867                True
2868                False (*)
2869
287014.          Which of the following statements is a valid reason for using a view?      Mark for Review
2871(1) Points
2872                Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
2873
2874                Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)
2875
2876                Views are not valid unless you have more than one user.
2877
2878                Views allow access to the data because the view displays all of the columns from the table.
2879
288015.          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
2881(1) Points
2882
2883                You can create the table and the view at the same time using the FORCE option.
2884                You must create the SALES table before creating the view.
2885
2886                You can use the FORCE option to create the view before the SALES table has been created. (*)
2887
2888                By default, the view will be created even if the SALES table does not exist.
2889
28901.            Which of the following keywords cannot be used when creating a view?               Mark for Review
2891(1) Points
2892                HAVING
2893                WHERE
2894                ORDER BY (*)
2895                They are all valid keywords when creating views.
2896
28972.            A view can contain a select statement with a subquery. True or False?   Mark for Review
2898(1) Points
2899                True (*)
2900                False
2901
29023.            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
2903(1) Points
2904                You must create the SALES table before creating the view.
2905                By default, the view will be created even if the SALES table does not exist.
2906                You can create the table and the view at the same time using the FORCE option.
2907
2908                You can use the FORCE option to create the view before the SALES table has been created. (*)
2909
29104.            The FACULTY table contains these columns:
2911FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY
2912FIRST_NAME VARCHAR2(20)
2913LAST_NAME VARCHAR2(20)
2914ADDRESS VARCHAR2(35)
2915CITY VARCHAR2(15)
2916STATE VARCHAR2(2)
2917ZIP NUMBER(9)
2918TELEPHONE NUMBER(10)
2919STATUS VARCHAR2(2) NOT NULL
2920
2921The COURSE table contains these columns:
2922
2923COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY
2924SUBJECT VARCHAR2(5)
2925TERM VARCHAR2(6)
2926FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY
2927
2928You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?
2929
2930 Mark for Review
2931(1) Points
2932
2933                CREATE VIEW pt_view AS
2934   (SELECT first_name, last_name, status, courseid, subject, term
2935   FROM faculty f, course c
2936   WHERE f.facultyid = c.facultyid);
2937(*)
2938
2939                CREATE VIEW pt_view IN    (SELECT first_name, last_name, status, courseid, subject, term
2940FROM faculty course);
2941
2942                CREATE VIEW
2943   (SELECT first_name, last_name, status, courseid, subject, term
2944   FROM faculty, course
2945   WHERE facultyid = facultyid);
2946
2947                CREATE VIEW pt_view
2948ON (SELECT first_name, last_name, status, courseid, subject, term
2949FROM faculty f and course c
2950WHERE f.facultyid = c.facultyid);
2951
29525.            What is one advantage of using views?  Mark for Review
2953(1) Points
2954                To be able to store the same data in more than one place
2955                To provide data dependence
2956                To provide restricted data access (*)
2957
29586.            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
2959(1) Points
2960
2961                CREATE VIEW sales_view
2962   AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2963   FROM customers c, orders o
2964   WHERE c.custid = o.custid)
2965WITH READ ONLY;
2966(*)
2967
2968                CREATE VIEW sales_view
2969   AS (SELECT companyname, city, orderid, orderdate, total
2970   FROM customers, orders
2971   WHERE custid = custid)
2972WITH READ ONLY;
2973
2974                CREATE VIEW sales_view
2975    (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2976    FROM customers c, orders o
2977    WHERE c.custid = o.custid)
2978WITH READ ONLY;
2979
2980                CREATE VIEW sales_view
2981AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
2982FROM customers c, orders o
2983WHERE c.custid = o.custid);
2984
29857.            You can create a view if the view subquery contains an inline view. True or False?            Mark for Review
2986(1) Points
2987                True (*)
2988                False
2989
29908.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
2991(1) Points
2992                Prohibits DML actions without administrator CHECK approval
2993                The view will allow the user to check it against the data dictionary
2994                Prohibits changing rows not returned by the subquery in the view definition. (*)
2995                Allows for DELETES from other tables, including ones not listed in subquery
2996
29979.            Only one type of view exists. True or False?        Mark for Review
2998(1) Points
2999                True
3000                False (*)
3001
300210.          What is the purpose of including the WITH CHECK OPTION clause when creating a view?               Mark for Review
3003(1) Points
3004                To make sure that data is not duplicated in the view
3005
3006                To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
3007
3008                To keep views form being queried by unauthorized persons
3009                To make sure that the parent table(s) actually exist
3010
301111.          The EMPLOYEES table contains these columns:
3012EMPLOYEE_ID NUMBER
3013LAST_NAME VARCHAR2(25)
3014FIRST_NAME VARCHAR2(25)
3015DEPARTMENT_ID NUMBER
3016JOB_ID NUMBER
3017MANAGER_ID NUMBER
3018SALARY NUMBER(9,2)
3019COMMISSOIN NUMBER(7,2)
3020HIRE_DATE DATE
3021
3022Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?
3023
3024 Mark for Review
3025(1) Points
3026
3027                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
3028FROM (SELECT last_name, first_name, salary
3029     FROM employees
3030     ORDER BY salary)
3031WHERE ROWNUM <=10 AND job_id LIKE 'CLERK' AND department_id = 70;
3032
3033                SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary"
3034FROM (SELECT last_name, first_name, salary, job_id
3035     FROM employees
3036     WHERE job_id LIKE 'CLERK' AND department_id = 70
3037     ORDER BY salary)
3038WHERE ROWNUM <=10;
3039(*)
3040
3041                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
3042FROM (SELECT last_name, first_name, salary, job_id, dept_id
3043     FROM employees
3044     WHERE ROWNUM <=10
3045     ORDER BY salary)
3046WHERE job_id LIKE 'CLERK' AND department_id = 70;
3047
3048                The only way is to use the data dictionary.
3049
305012.          Which of the following describes a top-N query?              Mark for Review
3051(1) Points
3052
3053                A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*)
3054
3055                A top-N query returns a result set that is sorted according to the specified column values.
3056                A top-N query returns the top 15 records from the specified table.
3057                A top-N query returns the bottom 15 records from the specified table.
3058
305913.          Which statement about an inline view is true?   Mark for Review
3060(1) Points
3061                An inline view is a subquery in the FROM clause, often named with an alias. (*)
3062                An inline view can be used to perform DML operations.
3063                An inline view is a complex view.
3064                An inline view is a schema object.
3065
306614.          Which of these is not a valid type of View?          Mark for Review
3067(1) Points
3068                INLINE
3069                COMPLEX
3070                SIMPLE
3071                ONLINE (*)
3072
307315.          You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
3074You issue this statement:
3075CREATE OR REPLACE VIEW CUST_CREDIT_V
3076AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
3077FROM customers c, accounts a
3078WHERE c.account_id = a.account_id WITH READ ONLY;
3079
3080Which type of SQL command can be issued on the CUST_CREDIT_V view?
3081
3082 Mark for Review
3083(1) Points
3084                UPDATE
3085                INSERT
3086                DELETE
3087
3088                SELECT (*)
3089
3090Section 16 Quiz Database Programming With SQL
3091Section 16 Quiz
3092            (Answer all questions in this section)
3093                                                           
30941.         Sequences can be used to: (Choose three)       Mark for Review
3095(1) Points
3096                                    (Choose all correct answers)   
3097            Generate a range of numbers and optionally cycle through them again (*)
3098            Set a fixed interval between successively generated numbers. (*)
3099            Ensure primary key values will be unique and consecutive
3100            Guarantee that no primary key values are unused
3101            Ensure primary key values will be unique even though gaps may exist (*)
3102
31032.         Evaluate this CREATE SEQUENCE statement:
3104CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;
3105
3106Which statement is true?
3107
3108 Mark for Review
3109(1) Points
3110            The statement will not execute successfully.
3111            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.
3112            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.
3113            The sequence will generate sequential descending values. (*)
3114
31153.         Examine the code for creating this sequence:
3116CREATE SEQUENCE track_id_seq
3117INCREMENT BY 10
3118START WITH 1000 MAXVALUE 10000
3119What are the first three values that would be generated by the sequence?
3120
3121 Mark for Review
3122(1) Points
3123            0, 1, 2
3124            1000, 1010, 1020 (*)
3125            1100, 1200, 1300
3126            100010011002
3127
31284.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review
3129(1) Points
3130
3131            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.
3132
3133            Specify a UNIQUE constraint on the CUSTOMER_ID column.
3134            Create a synonym.
3135            Create a sequence. (*)
3136
31375.         You need to retrieve the next available value for the SALES_IDX sequence.
3138Which would you include in your SQL statement?      Mark for Review
3139(1) Points
3140            sales_idx.CURRVAL
3141            sales_idx.NEXTVAL (*)
3142            sales_idx.NEXT
3143            sales_idx
3144
31456.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review
3146(1) Points
3147
3148            ALTER SEQUENCE emp_id_seq; (*)
3149            ALTER TABLE employees ;
3150            CREATE SEQUENCE emp_id_seq;
3151            ALTER SEQUENCE emp_id_seq.employee_id;
3152
31537.         Why do gaps in sequences occur?       Mark for Review
3154(1) Points
3155            A rollback is executed
3156            The system crashes
3157            The sequence is used in another table
3158            All of the above (*)
3159
31608.         You create a sequence with the following statement:
3161CREATE SEQUENCE my_emp_seq;
3162
3163Which of the following statements about this sequence are true? (Choose two)
3164
3165 Mark for Review
3166(1) Points
3167                                    (Choose all correct answers)   
3168            The sequence will not cache a range of numbers in memory.
3169            MAXVALUE is 10^27 for an ascending sequence. (*)
3170            When the sequence exceeds its maximum value it will continue to generate numbers starting with MINVALUE.
3171            MINVALUE is equal to 1. (*)
3172
31739.         The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?        Mark for Review
3174(1) Points
3175            ALTER INDEX ln_idx ON employees(first_name);
3176            ALTER INDEX ln_idx TO employees(first_name);
3177            ALTER INDEX ln_idx TO fn_idx ON employees(first_name);
3178            None of the above; you cannot ALTER an index. (*)
3179
3180‘;10.     User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
3181CREATE PUBLIC SYNONYM emp FOR mary.emp;
3182
3183User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use? (Choose two)
3184
3185 Mark for Review
3186(1) Points
3187                                                           
3188                                    (Choose all correct answers)   
3189            SELECT * FROM emp; (*)
3190            SELECT * FROM mary.emp; (*)
3191            CREATE SYNONYM marys_emp FOR mary(emp);
3192            SELECT * FROM emp.mary;
3193
319411.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review
3195(1) Points
3196            A view
3197            A synonym (*)
3198            An index
3199            A schema
3200
320112.       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
3202(1) Points
3203            USER_OBJECTS
3204            USER_INDEXES
3205            USER_IND_COLUMNS (*)
3206            USER_TABLES
3207
320813.       The following indexes exist on the EMPLOYEES table:
3209A unique index on the EMPLOYEE_ID primary key column
3210A non-unique index on the JOB_ID column
3211A composite index on the FIRST_NAME and LAST_NAME columns.
3212If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?
3213
3214 Mark for Review
3215(1) Points
3216            EMP_ID only
3217            JOB_ID only
3218            DEPT_ID only
3219            EMP_ID and JOB_ID
3220            All Indexes (*)
3221
322214.       Unique indexes are automatically created on columns that have which two types of constraints?       Mark for Review
3223(1) Points
3224            NOT NULL and UNIQUE
3225            UNIQUE and PRIMARY KEY (*)
3226            UNIQUE and FOREIGN KEY
3227            PRIMARY KEY and FOREIGN KEY
3228
322915.       The EMPLOYEES table contains these columns:
3230EMPLOYEE_ID NUMBER NOT NULL, Primary Key
3231LAST_NAME VARCHAR2 (20)
3232FIRST_NAME VARCHAR2 (20)
3233DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
3234HIRE_DATE DATE DEFAULT SYSDATE
3235SALARY NUMBER (8,2) NOT NULL
3236
3237On which column is an index automatically created for the EMPLOYEES table?
3238
3239 Mark for Review
3240(1) Points
3241            DEPARTMENT_ID
3242            HIRE_DATE
3243            EMPLOYEE_ID (*)
3244            LAST_NAME
3245            SALARY
3246
32471.         Evaluate this CREATE SEQUENCE statement:
3248CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
3249
3250Which statement is true?
3251
3252 Mark for Review
3253(1) Points
3254            The sequence will start with 1. (*)
3255            The sequence preallocates values and retains them in memory.
3256            The sequence has no maximum value.
3257            The sequence will continue to generate values after reaching its maximum value.
3258
32592.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review
3260(1) Points
3261            MAXVALUE
3262            CACHE
3263            NOCACHE (*)
3264
32653.         You need to retrieve the next available value for the SALES_IDX sequence.
3266Which would you include in your SQL statement?      Mark for Review
3267(1) Points
3268            sales_idx.CURRVAL
3269            sales_idx.NEXT
3270            sales_idx
3271            sales_idx.NEXTVAL (*)
3272
32734.         Evaluate this statement:
3274CREATE SEQUENCE sales_item_id_seq
3275START WITH 101 MAXVALUE 9000090 CYCLE;
3276
3277Which statement about this CREATE SEQUENCE statement is true?
3278
3279 Mark for Review
3280(1) Points
3281            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.
3282            The statement fails because no INCREMENT BY value is specified.
3283            The sequence will generate decrementing sequence numbers starting at 101.
3284            The sequence will reuse numbers and will start with 101. (*)
3285
32865.         You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
3287ALTER TABLE manufacturers
3288MODIFY (location_id NUMBER(6));
3289
3290Which statement about the LOCATION_ID_SEQ sequence is true?
3291
3292 Mark for Review
3293(1) Points
3294            The sequence is unchanged. (*)
3295            The current value of the sequence is reset to zero.
3296            The sequence is deleted and must be recreated.
3297            The current value of the sequence is reset to the sequence's START WITH value.
3298
32996.         Which dictionary view would you query to display the number most recently generated by a sequence?            Mark for Review
3300(1) Points
3301            USER_CURRVALUES
3302            USER_TABLES
3303            USER_SEQUENCES (*)
3304            USER_OBJECTS
3305
33067.         Why do gaps in sequences occur?       Mark for Review
3307(1) Points
3308            A rollback is executed
3309            The system crashes
3310            The sequence is used in another table
3311            All of the above (*)
3312
33138.         Nextval and Currval are known as column aliases. True or False?      Mark for Review
3314(1) Points
3315            True
3316            False (*)
3317
33189.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
3319(1) Points
3320            A PRIMARY KEY constraint
3321            A FOREIGN KEY constraint
3322            An index (*)
3323            A CHECK constraint
3324
332510.       Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review
3326(1) Points
3327            DROP INDEX last_name_idx(last_name);
3328
3329            ALTER TABLE employees
3330DROP INDEX last_name_idx;
3331
3332            DROP INDEX last_name_idx(employees.last_name);
3333
3334            DROP INDEX last_name_idx;(*)
3335
333611.       The following indexes exist on the EMPLOYEES table:
3337A unique index on the EMPLOYEE_ID primary key column
3338A non-unique index on the JOB_ID column
3339A composite index on the FIRST_NAME and LAST_NAME columns.
3340If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?
3341
3342 Mark for Review
3343(1) Points
3344            EMP_ID only
3345            JOB_ID only
3346            DEPT_ID only
3347            EMP_ID and JOB_ID
3348            All Indexes (*)
3349
335012.       All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review
3351(1) Points
3352            True
3353            False (*)
3354
335513.       Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?   Mark for Review
3356(1) Points
3357            CREATE UNRESTRICTED SYNONYM emp FOR EMPLOYEES
3358            CREATE PUBLIC SYNONYM emp FOR EMPLOYEES (*)
3359            CREATE SHARED SYNONYM emp FOR EMPLOYEES
3360            CREATE SYNONYM emp FOR EMPLOYEES
3361
336214.       What is the correct syntax for creating an index?        Mark for Review
3363(1) Points
3364            CREATE index_name INDEX ON table_name.column_name;
3365            CREATE INDEX ON table_name(column_name);
3366            CREATE OR REPLACE INDEX index_name ON table_name(column_name);
3367            CREATE INDEX index_name ON table_name(column_name); (*)
3368
336915.       The EMPLOYEES table contains these columns:
3370EMPLOYEE_ID NUMBER NOT NULL, Primary Key
3371LAST_NAME VARCHAR2 (20)
3372FIRST_NAME VARCHAR2 (20)
3373DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
3374HIRE_DATE DATE DEFAULT SYSDATE
3375SALARY NUMBER (8,2) NOT NULL
3376
3377On which column is an index automatically created for the EMPLOYEES table?
3378
3379 Mark for Review
3380(1) Points
3381            LAST_NAME
3382            EMPLOYEE_ID (*)
3383            DEPARTMENT_ID
3384            HIRE_DATE
3385            SALARY
3386
33871.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review
3388(1) Points
3389
3390            CREATE d_sum SYNONYM
3391FOR dept_sum_vu;
3392
3393            CREATE SYNONYM d_sum
3394FOR dept_sum_vu;(*)
3395
3396            CREATE SYNONYM d_sum
3397ON dept_sum_vu;
3398
3399            UPDATE dept_sum_vu
3400ON SYNONYM d_sum;
3401
34022.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review
3403(1) Points
3404            UNIQUE INDEX (*)
3405            NONUNIQUE INDEX
3406            INDEX
3407            Oracle cannot create indexes automatically.
3408
34093.         Indexes can be used to speed up queries. True or False?         Mark for Review
3410(1) Points
3411            True (*)
3412            False
3413
34144.         When creating an index on one or more columns of a table, which of the following statements are true?
3415(Choose two)   Mark for Review
3416(1) Points
3417                                    (Choose all correct answers)   
3418
3419            You should create an index if one or more columns are frequently used together in a join condition. (*)
3420
3421            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. (*)
3422
3423            You should create an index if the table is very small.
3424
3425            You should always create an index on tables that are frequently updated.
3426
34275.         The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?   Mark for Review
3428(1) Points
3429            CREATE PUBLIC SYNONYM cust ON mary.customers;
3430            CREATE PUBLIC SYNONYM cust FOR mary.customers;(*)
3431            CREATE SYNONYM cust ON mary.customers FOR PUBLIC;
3432            CREATE SYNONYM cust ON mary.customers;
3433GRANT SELECT ON cust TO PUBLIC;
3434
34356.         Which of the following best describes the function of an index?        Mark for Review
3436(1) Points
3437            An index can run statement blocks when DML actions occur against a table.
3438            An index can reduce the time required to grant multiple privileges to users.
3439            An index can prevent users from viewing certain data in a table.
3440            An index can increase the performance of SQL queries that search large tables. (*)
3441
34427.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
3443(1) Points
3444            A PRIMARY KEY constraint
3445            An index (*)
3446            A CHECK constraint
3447            A FOREIGN KEY constraint
3448
34498.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review
3450(1) Points
3451            ALTER TABLE employees ;
3452            CREATE SEQUENCE emp_id_seq;
3453            ALTER SEQUENCE emp_id_seq; (*)
3454            ALTER SEQUENCE emp_id_seq.employee_id;
3455
34569.         The ALTER SEQUENCE statement can be used to:    Mark for Review
3457(1) Points
3458            Change the maximum value to a lower number than was last used
3459            Change the amount a sequence increments each time a number is generated (*)
3460            Change the START WITH value of a sequence
3461            Change the name of the sequence
3462
346310.       A sequence is a database object. True or False?          Mark for Review
3464(1) Points
3465            True (*)
3466            False
3467
346811.       You issue this statement:
3469ALTER SEQUENCE po_sequence INCREMENT BY 2;
3470
3471Which statement is true?
3472
3473 Mark for Review
3474(1) Points
3475            Sequence numbers will be cached.
3476            Future sequence numbers generated will increase by 2 each time a number is generated. (*)
3477            If the PO_SEQUENCE sequence does not exist, it will be created.
3478            The statement fails if the current value of the sequence is greater than the START WITH value.
3479
348012.       Sequences can be used to: (Choose three)       Mark for Review
3481(1) Points
3482                                    (Choose all correct answers)   
3483            Generate a range of numbers and optionally cycle through them again (*)
3484            Guarantee that no primary key values are unused
3485            Set a fixed interval between successively generated numbers. (*)
3486            Ensure primary key values will be unique even though gaps may exist (*)
3487            Ensure primary key values will be unique and consecutive
3488
348913.       Evaluate this CREATE SEQUENCE statement:
3490CREATE SEQUENCE line_item_id_seq CYCLE;
3491
3492Which statement is true?
3493
3494 Mark for Review
3495(1) Points
3496            The sequence cannot be used with more than one table.
3497            The sequence preallocates values and retains them in memory.
3498            The sequence cannot generate additional values after reaching its maximum value.
3499            The sequence will continue to generate values after the maximum sequence value has been generated. (*)
3500
350114.       Evaluate this CREATE SEQUENCE statement:
3502CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
3503
3504Which statement is true?
3505
3506 Mark for Review
3507(1) Points
3508            The sequence will start with 1. (*)
3509            The sequence will continue to generate values after reaching its maximum value.
3510            The sequence has no maximum value.
3511            The sequence preallocates values and retains them in memory.
3512
351315.       You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
3514ALTER TABLE manufacturers
3515MODIFY (location_id NUMBER(6));
3516
3517Which statement about the LOCATION_ID_SEQ sequence is true?
3518
3519 Mark for Review
3520(1) Points
3521            The current value of the sequence is reset to the sequence's START WITH value.
3522            The sequence is deleted and must be recreated.
3523            The current value of the sequence is reset to zero.
3524            The sequence is unchanged. (*)
3525
35261.         Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review
3527(1) Points
3528            True
3529            False (*)
3530
35312.         Which of the following best describes the function of the NEXTVAL virtual column?          Mark for Review
3532(1) Points
3533
3534            The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.
3535
3536            The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.
3537
3538            The NEXTVAL virtual column increments a sequence by a predetermined value. (*)
3539
3540            The NEXTVAL virtual column displays only the physical locations of the rows in a table.
3541
35423.         Evaluate this CREATE SEQUENCE statement:
3543CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;
3544
3545Which statement is true?
3546
3547 Mark for Review
3548(1) Points
3549            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.
3550            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.
3551            The statement will not execute successfully.
3552            The sequence will generate sequential descending values. (*)
3553
35544.         Why do gaps in sequences occur?       Mark for Review
3555(1) Points
3556            A rollback is executed
3557            The system crashes
3558            The sequence is used in another table
3559            All of the above (*)
3560
35615.         When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review
3562(1) Points
3563            LOAD
3564            NOCYCLE
3565            NOCACHE
3566            CACHE (*)
3567            MEMORY
3568
35696.         Which pseudocolumn returns the latest value supplied by a sequence?           Mark for Review
3570(1) Points
3571            NEXT
3572            CURRENT
3573            NEXTVAL
3574            CURRVAL (*)
3575
35767.         Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review
3577(1) Points
3578            ALTER SEQUENCE emp_id_seq;
3579            DROP SEQUENCE emp_id_seq; (*)
3580            REMOVE SEQUENCE emp_id_seq;
3581            DELETE SEQUENCE emp_id_seq;
3582
35838.         Evaluate this statement:
3584CREATE SEQUENCE line_item_id_seq
3585MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;
3586
3587What will be the first five numbers generated by this sequence?
3588
3589 Mark for Review
3590(1) Points
3591            130120110100130
3592            The fifth number cannot be generated.
3593            100110120130100
3594            The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)
3595
35969.         The CLIENTS table contains these columns:
3597CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
3598LAST_NAME VARCHAR2(15)
3599FIRST_NAME VARCHAR2(10)
3600CITY VARCHAR2(15)
3601STATE VARCHAR2(2)
3602
3603You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:
3604
3605CREATE INDEX clients
3606ON address_index (city, state);
3607
3608Which result does this statement accomplish?
3609
3610 Mark for Review
3611(1) Points
3612            An index named CLIENTS is created on the CITY and STATE columns.
3613            An index named CLIENTS_INDEX is created on the CLIENTS table.
3614            An error message is produced, and no index is created. (*)
3615            An index named ADDRESS_INDEX is created on the CITY and STATE columns.
3616
361710.       In SQL what is a synonym?     Mark for Review
3618(1) Points
3619            A table with the same number of columns as another table
3620            A table with the same name as another view
3621            A different name for a table, view, or other database object (*)
3622            A table that must be qualified with a username
3623
362411.       Indexes can be used to speed up queries. True or False?         Mark for Review
3625(1) Points
3626            True (*)
3627            False
3628
362912.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review
3630(1) Points
3631            An index
3632            A view
3633            A synonym (*)
3634            A schema
3635
363613.       The EMPLOYEES table contains these columns:
3637EMPLOYEE_ID NUMBER NOT NULL, Primary Key
3638LAST_NAME VARCHAR2 (20)
3639FIRST_NAME VARCHAR2 (20)
3640DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
3641HIRE_DATE DATE DEFAULT SYSDATE
3642SALARY NUMBER (8,2) NOT NULL
3643
3644On which column is an index automatically created for the EMPLOYEES table?
3645
3646 Mark for Review
3647(1) Points
3648            EMPLOYEE_ID (*)
3649            LAST_NAME
3650            HIRE_DATE
3651            SALARY
3652            DEPARTMENT_ID
3653
365414.       The following indexes exist on the EMPLOYEES table:
3655A unique index on the EMPLOYEE_ID primary key column
3656A non-unique index on the JOB_ID column
3657A composite index on the FIRST_NAME and LAST_NAME columns.
3658If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?
3659
3660 Mark for Review
3661(1) Points
3662            EMP_ID only
3663            JOB_ID only
3664            DEPT_ID only
3665            EMP_ID and JOB_ID
3666            All Indexes (*)
3667
366815.       You want to speed up the following query by creating an index:
3669SELECT * FROM employees WHERE (salary * 12) > 100000;
3670
3671Which of the following will achieve this?
3672
3673 Mark for Review
3674(1) Points
3675            Create an index on (salary).
3676            Create a function_based index on ((salary * 12) > 100000).
3677            Create a composite index on (salary,12).
3678            Create a function-based index on (salary * 12). (*)
3679
36801.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review
3681(1) Points
3682
3683            SELECT index_name, table_name, uniqueness
3684FROM user_indexes
3685WHERE index = EMPLOYEES;
3686
3687            CREATE index_name, table_name, uniqueness
3688FROM user_indexes
3689WHERE table_name = 'EMPLOYEES';
3690
3691            SELECT index_name, table_name, uniqueness
3692FROM 'EMPLOYEES';
3693
3694            SELECT index_name, table_name, uniqueness
3695FROM user_indexes
3696WHERE table_name = 'EMPLOYEES';
3697(*)
3698
36992.         Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review
3700(1) Points
3701            DROP INDEX last_name_idx(last_name);
3702
3703            ALTER TABLE employees
3704DROP INDEX last_name_idx;
3705
3706            DROP INDEX last_name_idx(employees.last_name);
3707
3708            DROP INDEX last_name_idx;
3709(*)
3710
37113.         Indexes can be used to speed up queries. True or False?         Mark for Review
3712(1) Points
3713            True (*)
3714            False
3715
37164.         As user Julie, you issue this statement:
3717CREATE SYNONYM emp FOR sam.employees;
3718
3719Which task was accomplished by this statement?
3720
3721 Mark for Review
3722(1) Points
3723            You created a public synonym on the EMPLOYEES table owned by user Sam.
3724            You created a public synonym on the EMP table owned by user Sam.
3725            You created a private synonym on the EMPLOYEES table that you own.
3726            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)
37275.         For which column would you create an index?           Mark for Review
3728(1) Points
3729            A column that is updated frequently
3730            A column that is infrequently used as a query search condition
3731            A column which has only 4 distinct values.
3732            A column with a large number of null values (*)
3733
37346.         You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task? Mark for Review
3735(1) Points
3736
3737            CREATE INDEX fl_idx ON employees(first_name);
3738CREATE INDEX fl_idx ON employees(last_name);
3739
3740            CREATE INDEX fl_idx
3741ON employees(first_name || last_name);
3742
3743            CREATE INDEX fl_idx
3744ON employees(first_name), employees(last_name);
3745
3746            CREATE INDEX fl_idx
3747ON employees(first_name,last_name);
3748(*)
3749
37507.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review
3751(1) Points
3752            UNIQUE INDEX (*)
3753            NONUNIQUE INDEX
3754            INDEX
3755            Oracle cannot create indexes automatically.
3756
37578.         When creating a sequence, which keyword or option specifies the minimum sequence value?           Mark for Review
3758(1) Points
3759            CYCLE
3760            MINVALUE (*)
3761            MAXVALUE
3762            NOMAXVALUE
3763
37649.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review
3765(1) Points
3766            Specify a UNIQUE constraint on the CUSTOMER_ID column.
3767
3768            Create a sequence. (*)
3769
3770            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.
3771
3772            Create a synonym.
3773
377410.       Evaluate this statement:
3775CREATE SEQUENCE sales_item_id_seq
3776START WITH 101 MAXVALUE 9000090 CYCLE;
3777
3778Which statement about this CREATE SEQUENCE statement is true?
3779
3780 Mark for Review
3781(1) Points
3782
3783            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.
3784            The sequence will reuse numbers and will start with 101. (*)
3785            The statement fails because no INCREMENT BY value is specified.
3786            The sequence will generate decrementing sequence numbers starting at 101.
3787
378811.       Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review
3789(1) Points
3790            True
3791            False (*)
3792
379312.       When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review
3794(1) Points
3795            NOCYCLE
3796            NOCACHE
3797            CACHE (*)
3798            LOAD
3799            MEMORY
3800
380113.       A sequence is a database object. True or False?          Mark for Review
3802(1) Points
3803            True (*)
3804            False
3805
380614.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review
3807(1) Points
3808                                                           
3809                                   
3810            True (*)
3811            False
3812
381315.       CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?      Mark for Review
3814(1) Points
3815            True (*)
3816            False
3817
38181.         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
3819(1) Points
3820            USER_INDEXES
3821            USER_OBJECTS
3822            USER_TABLES
3823            USER_IND_COLUMNS (*)
3824
38252.         The CLIENTS table contains these columns:
3826CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
3827LAST_NAME VARCHAR2(15)
3828FIRST_NAME VARCHAR2(10)
3829CITY VARCHAR2(15)
3830STATE VARCHAR2(2)
3831
3832You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:
3833
3834CREATE INDEX clients
3835ON address_index (city, state);
3836
3837Which result does this statement accomplish?
3838
3839 Mark for Review
3840(1) Points
3841            An index named CLIENTS is created on the CITY and STATE columns.
3842            An index named CLIENTS_INDEX is created on the CLIENTS table.
3843            An index named ADDRESS_INDEX is created on the CITY and STATE columns.
3844            An error message is produced, and no index is created. (*)
3845
38463.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review
3847(1) Points
3848
3849            UPDATE dept_sum_vu
3850ON SYNONYM d_sum;
3851
3852            CREATE SYNONYM d_sum
3853ON dept_sum_vu;
3854
3855            CREATE d_sum SYNONYM
3856FOR dept_sum_vu;
3857
3858            CREATE SYNONYM d_sum
3859FOR dept_sum_vu;
3860(*)
3861
38624.         All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review
3863(1) Points
3864            True
3865            False (*)
3866
38675.         As user Julie, you issue this statement:
3868CREATE SYNONYM emp FOR sam.employees;
3869
3870Which task was accomplished by this statement?
3871
3872 Mark for Review
3873(1) Points
3874            You created a public synonym on the EMP table owned by user Sam.
3875            You created a private synonym on the EMPLOYEES table that you own.
3876            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)
3877            You created a public synonym on the EMPLOYEES table owned by user Sam.
3878
38796.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review
3880(1) Points
3881
3882            SELECT index_name, table_name, uniqueness
3883FROM 'EMPLOYEES';
3884
3885            SELECT index_name, table_name, uniqueness
3886FROM user_indexes
3887WHERE index = EMPLOYEES;
3888
3889            SELECT index_name, table_name, uniqueness
3890FROM user_indexes
3891WHERE table_name = 'EMPLOYEES';
3892(*)
3893
3894            CREATE index_name, table_name, uniqueness
3895FROM user_indexes
3896WHERE table_name = 'EMPLOYEES';
3897
38987.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
3899(1) Points
3900            An index (*)
3901            A CHECK constraint
3902            A PRIMARY KEY constraint
3903            A FOREIGN KEY constraint
3904
39058.         Examine the code for creating this sequence:
3906CREATE SEQUENCE track_id_seq
3907INCREMENT BY 10
3908START WITH 1000 MAXVALUE 10000
3909What are the first three values that would be generated by the sequence?
3910
3911 Mark for Review
3912(1) Points
3913            100010011002
3914            1000, 1010, 1020 (*)
3915            0, 1, 2
3916            1100, 1200, 1300
3917
39189.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review
3919(1) Points
3920            NOCACHE (*)
3921            CACHE
3922            MAXVALUE
3923
392410.       Which keyword is used to modify a sequence?           Mark for Review
3925(1) Points
3926            Alter (*)
3927            Update
3928            Change
3929            Create
3930
393111.       Evaluate this statement:
3932SELECT po_itemid_seq.CURRVAL
3933FROM dual;
3934
3935What does this statement accomplish?
3936
3937 Mark for Review
3938(1) Points
3939            It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
3940            It displays the next available value of the PO_ITEM_ID_SEQ sequence.
3941            It resets the current value of the PO_ITEM_ID_SEQ sequence.
3942            It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.
3943
394412.       Which is the correct syntax for specifying a maximum value in a sequence?  Mark for Review
3945(1) Points
3946            Maxval
3947            Max_value
3948            Maxvalue (*)
3949            Maximumvalue
3950
395113.       Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review
3952(1) Points
3953            DELETE SEQUENCE emp_id_seq;
3954            DROP SEQUENCE emp_id_seq; (*)
3955            REMOVE SEQUENCE emp_id_seq;
3956            ALTER SEQUENCE emp_id_seq;
3957
395814.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review
3959(1) Points
3960            True (*)
3961            False
3962
396315.       A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False?     Mark for Review
3964(1) Points
3965            True (*)
3966
3967            False
3968
3969Section 17 Quiz
3970            (Answer all questions in this section)
3971
39721.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
3973(1) Points
3974            True (*)
3975            False
3976
39772.         Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False?    Mark for Review
3978(1) Points
3979            True (*)
3980            False
3981
39823.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
3983(1) Points
3984            True (*)
3985            False
3986
39874.         A role can be granted to another role. True or False?   Mark for Review
3988(1) Points
3989            True (*)
3990            False
3991
39925.         Which of the following simplifies the administration of privileges?    Mark for Review
3993(1) Points
3994            A role (*)
3995            A trigger
3996            An index
3997            A view
3998
39996.         Which keyword would you use to grant an object privilege to all database users?       Mark for Review
4000(1) Points
4001            ADMIN
4002            PUBLIC (*)
4003            ALL
4004            USERS
4005
40067.         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
4007(1) Points
4008
4009            He is not required to take any action because, by default, all database users can automatically access views.
4010
4011            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
4012
4013            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
4014
4015            He should assign the SELECT privilege to all database users for the INVENTORY table.
4016
40178.         If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
4018(1) Points
4019            True
4020            False (*)
4021
40229.         Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
4023(1) Points
4024
4025            USER_SYSTEM_PRIVS
4026            USER_TAB_PRIVS
4027            USER_SYS_PRIVS (*)
4028            USER_SYSTEM_PRIVILEGES
4029
403010.       A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
4031(1) Points
4032            True (*)
4033            False
4034
403511.       The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco? Mark for Review
4036(1) Points
4037            CREATE OBJECT
4038            CREATE ANY TABLE
4039            CREATE TABLE (*)
4040            SELECT
4041
404212.       You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?      Mark for Review
4043(1) Points
4044
4045            CREATE USER jones IDENTIFIED BY mark;
4046GRANT CREATE SESSION TO jones;
4047
4048            CREATE USER jones IDENTIFIED BY mark;
4049GRANT CREATE SESSION TO jones;
4050GRANT CREATE TABLE TO jones;
4051(*)
4052
4053            CREATE USER jones IDENTIFIED BY mark;
4054GRANT CREATE TABLE TO jones;
4055
4056            GRANT CREATE SESSION TO jones;
4057GRANT CREATE TABLE TO jones;
4058
405913.       Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
4060(1) Points
4061            INSERT
4062            DELETE
4063            UPDATE
4064            SELECT (*)
4065
406614.       Object privileges are:   Mark for Review
4067(1) Points
4068            Required to gain access to the database.
4069            Required to manipulate the content of objects in the database. (*)
4070            Named groups of related privileges given to a user.
4071            A collection of objects, such as tables, views, and sequences.
4072
407315.       The following table shows some of the output from one of the data dictionary views. Which view is being queried?
4074USERNAME    PRIVILEGE     ADMIN_OPTION
4075USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
4076USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
4077USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
4078USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
4079USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
4080USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
4081 Mark for Review
4082(1) Points
4083            user_tab_privs_recd (lists object privileges granted to the user)
4084            role_sys_privs (lists system privileges granted to roles)
4085            role_tab_privs (lists table privileges granted to roles)
4086            user_sys_privs (lists system privileges granted to the user) (*)
4087
40881.         Which of the following best describes a role in an Oracle database?   Mark for Review
4089(1) Points
4090            A role is a type of system privilege.
4091            A role is a name for a group of privileges. (*)
4092            A role is the part that a user plays in querying the database.
4093            A role is an object privilege which allows a user to update a table.
4094
40952.         A schema is:    Mark for Review
4096(1) Points
4097            A named group of related privileges given to a user.
4098            A collection of objects, such as tables, views, and sequences. (*)
4099            Required to gain access to the database.
4100            Required to manipulate the content of objects in the database.
4101
41023.         System privileges are:  Mark for Review
4103(1) Points
4104            Required to manipulate the content of objects in the database.
4105            Named groups of related privileges given to a user.
4106            Required to gain access to the database. (*)
4107            A collection of objects, such as tables, views, and sequences.
4108
41094.         You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?  Mark for Review
4110(1) Points
4111            A user privilege
4112            An object privilege
4113            An access privilege
4114            A system privilege (*)
4115
41165.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
4117(1) Points
4118            One or more object privileges have been REVOKEd from Adam.
4119            ADAM's user account has been removed from the database.
4120            ADAM's CREATE USER privilege has been revoked.
4121            ADAM's CREATE SESSION privilege has been revoked. (*)
4122
41236.         Which of the following are system privileges?
4124(Choose two)   Mark for Review
4125(1) Points
4126                                    (Choose all correct answers)   
4127            INDEX
4128            UPDATE
4129            CREATE TABLE (*)
4130            CREATE SYNONYM (*)
4131
41327.         _________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching.         Mark for Review
4133(1) Points
4134            Reference checks
4135            Clip Art
4136            Alphanumeric values
4137            Meta characters (*)
4138
41398.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
4140(1) Points
4141            True (*)
4142            False
4143
41449.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
4145(1) Points
4146            True
4147            False (*)
4148
414910.       A role can be granted to another role. True or False?   Mark for Review
4150(1) Points
4151            True (*)
4152            False
4153
415411.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
4155(1) Points
4156            It allows the user to create new tables which contain the same data as the referenced table.
4157            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
4158            It allows a user to refer to the table in a SELECT statement.
4159            It allows a user to create foreign key constraints on the table. (*)
4160
416112.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
4162(1) Points
4163            True
4164            False (*)
4165
416613.       What Oracle feature simplifies the process of granting and revoking privileges?         Mark for Review
4167(1) Points
4168            Object
4169            Data dictionary
4170            Role (*)
4171            Schema
4172
417314.       Which of the following statements about granting object privileges is false?   Mark for Review
4174(1) Points
4175            Object privileges can only be granted through roles. (*)
4176
4177            An object owner can grant any object privilege on the object to any other user or role of the database.
4178
4179            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.
4180
4181            The owner of an object automatically acquires all object privileges on that object.
4182
418315.       When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user?         Mark for Review
4184(1) Points
4185            WITH GRANT OPTION (*)
4186            WITH ADMIN OPTION
4187            PUBLIC
4188            FORCE
4189
41901.         Which of the following best describes a role in an Oracle database?   Mark for Review
4191(1) Points
4192            A role is a name for a group of privileges. (*)
4193            A role is a type of system privilege.
4194            A role is an object privilege which allows a user to update a table.
4195            A role is the part that a user plays in querying the database.
4196
41972.         Which of the following are system privileges?
4198(Choose two)   Mark for Review
4199(1) Points
4200                                    (Choose all correct answers)   
4201            CREATE SYNONYM (*)
4202            UPDATE
4203            CREATE TABLE (*)
4204            INDEX
4205
42063.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
4207(1) Points
4208            ADAM's CREATE USER privilege has been revoked.
4209            One or more object privileges have been REVOKEd from Adam.
4210            ADAM's user account has been removed from the database.
4211            ADAM's CREATE SESSION privilege has been revoked. (*)
4212
42134.         Which of the following are object privileges? (Choose two)    Mark for Review
4214(1) Points
4215                                    (Choose all correct answers)   
4216            CREATE TABLE
4217            INSERT (*)
4218            SELECT (*)
4219            DROP TABLE
4220
42215.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
4222(1) Points
4223            True (*)
4224            False
4225
42266.         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
4227(1) Points
4228            CREATE PUBLIC SYNONYM customers FOR james.customers;
4229            GRANT SELECT ON customers TO ALL;
4230            GRANT SELECT ON customers TO PUBLIC; (*)
4231            GRANT customers(SELECT) TO PUBLIC;
4232
42337.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
4234(1) Points
4235            True
4236            False (*)
4237
42388.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
4239(1) Points
4240            True (*)
4241            False
4242
42439.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
4244(1) Points
4245            True
4246            False (*)
4247
424810.       Roles are:         Mark for Review
4249(1) Points
4250            Required to gain access to the database.
4251            Named groups of related privileges given to a user or another role. (*)
4252            A collection of objects, such as tables, views, and sequences.
4253            Required to manipulate the content of objects in the database.
4254
425511.       Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
4256(1) Points
4257            USER_SYS_PRIVS (*)
4258            USER_SYSTEM_PRIVS
4259            USER_SYSTEM_PRIVILEGES
4260            USER_TAB_PRIVS
4261
426212.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
4263(1) Points
4264            ALTER USER
4265            REMOVE
4266            DROP
4267            REVOKE (*)
4268
426913.       You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use? Mark for Review
4270(1) Points
4271
4272            GRANT SELECT ON employees TO bob;
4273            GRANT SELECT ON employees TO bob WITH ADMIN OPTION;
4274            GRANT SELECT ON employees TO bob WITH GRANT OPTION; (*)
4275            GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;
4276
427714.       If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
4278(1) Points
4279            True
4280            False (*)
4281
428215.       User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
4283GRANT SELECT ON employees TO mary WITH GRANT OPTION;
4284
4285Which of the following statements can MARY now execute successfully? (Choose two)
4286
4287 Mark for Review
4288(1) Points
4289                                    (Choose all correct answers)   
4290            DROP TABLE bob.employees;
4291            GRANT SELECT ON bob.employees TO PUBLIC; (*)
4292            REVOKE SELECT ON bob.employees FROM bob;
4293            SELECT FROM bob.employees; (*)
4294
42951.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
4296(1) Points
4297            True (*)
4298            False
4299
43002.         Select the correct REGULAR EXPRESSION functions: (Choose two) Mark for Review
4301(1) Points
4302                                    (Choose all correct answers)   
4303            REGEXP_INSTR, REGEXP_SUBSTR (*)
4304            REGEXP_LIKE, REGEXP_NEAR
4305            REGEXP_LIKE, REGEXP_REPLACE (*)
4306            REGEXP_REPLACE, REGEXP_REFORM
4307
43083.         Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?            Mark for Review
4309(1) Points
4310            REGEXP_LIKE
4311            REGEXP (*)
4312            REGEXP_REPLACE
4313            REGEXP_SUBSTR
4314
43154.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
4316(1) Points
4317            True (*)
4318            False
4319
43205.         You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?        Mark for Review
4321(1) Points
4322            User privileges
4323            Administrator privileges
4324            System privileges
4325            Object privileges (*)
4326
43276.         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
4328(1) Points
4329            ALTER SESSION
4330            OPEN SESSION
4331            CREATE SESSION (*)
4332            RESTRICTED SESSION
4333
43347.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
4335USERNAME    PRIVILEGE     ADMIN_OPTION
4336USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
4337USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
4338USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
4339USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
4340USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
4341USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
4342 Mark for Review
4343(1) Points
4344            user_sys_privs (lists system privileges granted to the user) (*)
4345            role_tab_privs (lists table privileges granted to roles)
4346            user_tab_privs_recd (lists object privileges granted to the user)
4347            role_sys_privs (lists system privileges granted to roles)
4348
43498.         Which of the following best describes a role in an Oracle database?   Mark for Review
4350(1) Points
4351            A role is the part that a user plays in querying the database.
4352            A role is a type of system privilege.
4353            A role is an object privilege which allows a user to update a table.
4354            A role is a name for a group of privileges. (*)
4355
43569.         You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?           Mark for Review
4357(1) Points
4358            GRANT SELECT ON employees AND employees_view TO audrey;
4359            GRANT SELECT ON employees_view TO public;
4360            GRANT SELECT ON employees_view TO audrey; (*)
4361            Do nothing. As a database user, Audrey's user account has automatically been granted the SELECT privilege for all database objects.
4362
436310.       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
4364(1) Points
4365            He should assign the SELECT privilege to all database users for the INVENTORY table.
4366            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
4367            He is not required to take any action because, by default, all database users can automatically access views.
4368            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
4369
437011.       Scott King owns a table called employees. He issues the following statement:
4371GRANT select ON employees TO PUBLIC;
4372Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
4373GRANT ï¾ select ON ï¾ scott_king.employees TO jennifer_cho;
4374
4375True or False: Allison's statement will fail.
4376
4377 Mark for Review
4378(1) Points
4379            True (*)
4380            False
4381
438212.       A role can be granted to another role. True or False?   Mark for Review
4383(1) Points
4384            True (*)
4385            False
4386
438713.       Which of the following statements about granting object privileges is false?   Mark for Review
4388(1) Points
4389            Object privileges can only be granted through roles. (*)
4390            An object owner can grant any object privilege on the object to any other user or role of the database.
4391            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.
4392            The owner of an object automatically acquires all object privileges on that object.
4393
439414.       Which keyword would you use to grant an object privilege to all database users?       Mark for Review
4395(1) Points
4396            ALL
4397            PUBLIC (*)
4398            ADMIN
4399            USERS
4400
440115.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
4402(1) Points
4403            True
4404            False (*)
4405
44061.         A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
4407(1) Points
4408            True (*)
4409            False
4410
44112.         Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
4412(1) Points
4413            INSERT
4414            UPDATE
4415            DELETE
4416            SELECT (*)
4417
44183.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
4419USERNAME    PRIVILEGE     ADMIN_OPTION
4420USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
4421USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
4422USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
4423USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
4424USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
4425USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
4426 Mark for Review
4427(1) Points
4428            role_sys_privs (lists system privileges granted to roles)
4429            user_sys_privs (lists system privileges granted to the user) (*)
4430            role_tab_privs (lists table privileges granted to roles)
4431            user_tab_privs_recd (lists object privileges granted to the user)
4432
44334.         What system privilege must be held in order to login to an Oracle database?  Mark for Review
4434(1) Points
4435            CREATE LOGIN
4436            CREATE SESSION (*)
4437            CREATE LOGON
4438            No special privilege is needed; if your username exists in the database, you can login.
4439
44405.         Which of the following best describes a role in an Oracle database?   Mark for Review
4441(1) Points
4442            A role is an object privilege which allows a user to update a table.
4443            A role is a name for a group of privileges. (*)
4444            A role is a type of system privilege.
4445            A role is the part that a user plays in querying the database.
4446
44476.         Which of the following Object Privileges can be granted on an individual column on a table? (Choose two)     Mark for Review
4448(1) Points
4449                                    (Choose all correct answers)   
4450            Delete
4451            Select
4452            Update (*)
4453            References (*)
4454
44557.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
4456(1) Points
4457            True (*)
4458            False
4459
44608.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
4461(1) Points
4462            True
4463            False (*)
4464
44659.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
4466(1) Points
4467            True
4468            False (*)
4469
447010.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
4471(1) Points
4472            It allows a user to create foreign key constraints on the table. (*)
4473            It allows a user to refer to the table in a SELECT statement.
4474            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
4475            It allows the user to create new tables which contain the same data as the referenced table.
4476
447711.       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
4478(1) Points
4479            He should assign the SELECT privilege to all database users for the INVENTORY table.
4480            He is not required to take any action because, by default, all database users can automatically access views.
4481            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
4482            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
4483
448412.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
4485            DROP
4486            REMOVE
4487            REVOKE (*)
4488            ALTER USER
4489
449013.       Roles are:         Mark for Review
4491(1) Points
4492            Named groups of related privileges given to a user or another role. (*)
4493            A collection of objects, such as tables, views, and sequences.
4494            Required to gain access to the database.
4495            Required to manipulate the content of objects in the database.
4496
449714.       Which of the following statements about granting object privileges is false?   Mark for Review
4498(1) Points
4499                                   
4500            An object owner can grant any object privilege on the object to any other user or role of the database.
4501            The owner of an object automatically acquires all object privileges on that object.
4502            Object privileges can only be granted through roles. (*)
4503            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.
4504
450515.       Which statement would you use to grant a role to users?         Mark for Review
4506(1) Points
4507            ASSIGN
4508            ALTER USER
4509            CREATE USER
4510
4511            GRANT (*)
4512By Deni Ace at February 11, 2017   
4513Labels: database, Database programming with SQL, programming, quiz, section 17, sql
45144 comments:
4515
4516Ordinary Oracle Student28 April 2017 at 08:48
4517Forgot these questions.
4518
45191. User CHANG has been granted SELECT, UPDATE, INSERT, and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this? Mark for Review 
4520(1) Points
4521
4522
4523REVOKE INSERT AND DELETE ON employees FROM chang;
4524
4525
4526REMOVE INSERT, DELETE ON employees FROM chang;
4527
4528
4529REVOKE ALL ON employees FROM chang;
4530
4531
4532REVOKE INSERT, DELETE ON employees FROM chang; (*)
4533
45342. Which of the following is NOT a database object? Mark for Review 
4535(1) Points
4536
4537
4538Table
4539
4540
4541Sequence
4542
4543
4544View
4545
4546
4547Subquery (*)
4548
45493.Which statement would you use to add privileges to a role? Mark for Review 
4550(1) Points
4551
4552
4553ASSIGN
4554
4555
4556ALTER ROLE
4557
4558
4559GRANT (*)
4560
4561
4562CREATE ROLE
4563
45644.Which of the following statements is true? Mark for Review 
4565(1) Points
4566
4567
4568Database Links allow users to work on remote database objects without having to log into the other database. (*)
4569
4570
4571Database Links can be created by any user of a database. You do not need any special privileges to create them.
4572
4573
4574Database Links are pointers to another schema in the same database.
4575
4576
4577Database Links are never used in the real world.
4578
4579Reply
4580Replies
4581
4582Deni Ace31 July 2017 at 11:04
4583thank you ^_^
4584
4585Reply
4586
4587Ordinary Oracle Student28 April 2017 at 09:08
4588Forgot these as well 
4589
45901.Which of the following are object privileges? (Choose two) Mark for Review 
4591(1) Points
4592
4593(Choose all correct answers) 
4594
4595
4596CREATE TABLE
4597
4598
4599INSERT (*)
4600
4601
4602DROP TABLE
4603
4604
4605SELECT (*)
4606
46072.What Oracle feature simplifies the process of granting and revoking privileges? Mark for Review 
4608(1) Points
4609
4610
4611Role (*)
4612
4613
4614Schema
4615
4616
4617Data dictionary
4618
4619
4620Object
4621
4622Section 18 Quiz
4623            (Answer all questions in this section)
4624
46251.         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:
4626UPDATE employees
4627SET salary = salary * 2
4628WHERE employee_id = 100;
4629COMMIT;
4630
4631UPDATE employees
4632SET salary = 30000
4633WHERE employee_id = 100;
4634
4635The user's database session now ends abnormally. What is now King's salary in the table?
4636
4637 Mark for Review
4638(1) Points
4639            78000
4640            30000
4641            24000
4642            48000 (*)
4643
46442.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
4645INSERT INTO mytab VALUES ('A');
4646INSERT INTO mytab VALUES ('B');
4647COMMIT;
4648INSERT INTO mytab VALUES ('C');
4649ROLLBACK;
4650
4651Which rows does the table now contain?
4652
4653 Mark for Review
4654(1) Points
4655            A, B, and C
4656            A and B (*)
4657            C
4658            None of the above
4659
46603.         If Oracle crashes, your changes are automatically rolled back. True or False? Mark for Review
4661(1) Points
4662            True (*)
4663            False
4664
46654.         Examine the following statements:
4666UPDATE employees SET salary = 15000;
4667SAVEPOINT upd1_done;
4668UPDATE employees SET salary = 22000;
4669SAVEPOINT upd2_done;
4670DELETE FROM employees;
4671
4672You want to retain all the employees with a salary of 15000; What statement would you execute next?
4673
4674 Mark for Review
4675(1) Points
4676            ROLLBACK;
4677            ROLLBACK TO SAVEPOINT upd1_done; (*)
4678            ROLLBACK TO SAVEPOINT upd2_done;
4679            ROLLBACK TO SAVE upd1_done;
4680            There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.
4681
46825.         COMMIT saves all outstanding data changes? True or False?  Mark for Review
4683(1) Points
4684            True (*)
4685            False
4686
46876.         Examine the following statements:
4688INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
4689SAVEPOINT Ins_Done;
4690DELETE employees; -- 107 rows deleted
4691SAVEPOINT Del_Done;
4692UPDATE emps SET last_name = 'Smith';
4693
4694How would you undo the last Update only?
4695
4696 Mark for Review
4697(1) Points
4698            ROLLBACK to SAVEPOINT Del_Done; (*)
4699            COMMIT Del_Done;
4700            There is nothing you can do.
4701            ROLLBACK UPDATE;
4702
47037.         Examine the following statements:
4704INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
4705SAVEPOINT Ins_Done;
4706CREATE INDEX emp_lname_idx ON employees(last_name);
4707UPDATE emps SET last_name = 'Smith';
4708
4709What happens if you issue a Rollback statement?
4710
4711 Mark for Review
4712(1) Points
4713
4714            The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)
4715
4716            Both the UPDATE and the INSERT will be rolled back.
4717            The INSERT is undone but the UPDATE is committed.
4718            Nothing happens.
4719
47208.         Which SQL statement is used to remove all the changes made by an uncommitted transaction?         Mark for Review
4721(1) Points
4722            REVOKE;
4723            ROLLBACK TO SAVEPOINT;
4724            ROLLBACK; (*)
4725            UNDO;
4726
47279.         You need not worry about controlling your transactions. Oracle does it all for you. True or False?    Mark for Review
4728(1) Points
4729            True
4730            False (*)
4731
473210.       When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
4733(1) Points
4734            True
4735            False (*)
4736
473711.       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:
4738SELECT COUNT(*) FROM bob.customers;
4739
4740What result will JANE see?
4741
4742 Mark for Review
4743(1) Points
4744            22
4745            20 (*)
4746            JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
4747            2
4748
474912.       Which of the following best describes the term "read consistency"?   Mark for Review
4750(1) Points
4751            It prevents other users from querying a table while updates are being executed on it
4752
4753            It prevents other users from seeing changes to a table until those changes have been committed (*)
4754
4755            It prevents users from querying tables on which they have not been granted SELECT privilege
4756            It ensures that all changes to a table are automatically committed
4757
475813.       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
4759(1) Points
4760            An object privilege
4761            An update statement
4762            A savepoint (*)
4763            A sequence
4764            A database link
4765
476614.       If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
4767(1) Points
4768            True (*)
4769            False
4770
477115.       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
4772(1) Points
4773            True
4774            False (*)
4775
47761.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
4777INSERT INTO mytab VALUES ('A');
4778INSERT INTO mytab VALUES ('B');
4779COMMIT;
4780INSERT INTO mytab VALUES ('C');
4781ROLLBACK;
4782
4783Which rows does the table now contain?
4784
4785 Mark for Review
4786(1) Points
4787            A, B, and C
4788            A and B (*)
4789            C
4790            None of the above
4791
47922.         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
4793(1) Points
4794            True
4795            False (*)
4796
47973.         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:
4798SELECT COUNT(*) FROM bob.customers;
4799
4800What result will JANE see?
4801
4802 Mark for Review
4803(1) Points
4804            JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
4805            2
4806            20 (*)
4807            22
4808
48094.         Examine the following statements:
4810INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
4811SAVEPOINT Ins_Done;
4812CREATE INDEX emp_lname_idx ON employees(last_name);
4813UPDATE emps SET last_name = 'Smith';
4814
4815What happens if you issue a Rollback statement?
4816
4817 Mark for Review
4818(1) Points
4819
4820            The update of last_name is undone, but the insert was committed by the CREATE INDEX
4821statement. (*)
4822            Both the UPDATE and the INSERT will be rolled back.
4823            The INSERT is undone but the UPDATE is committed.
4824            Nothing happens.
4825
48265.         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:
4827UPDATE employees
4828SET salary = salary * 2
4829WHERE employee_id = 100;
4830COMMIT;
4831
4832UPDATE employees
4833SET salary = 30000
4834WHERE employee_id = 100;
4835
4836The user's database session now ends abnormally. What is now King's salary in the table?
4837
4838 Mark for Review
4839(1) Points
4840            24000
4841            30000
4842            48000 (*)
4843            78000
4844
48456.         When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
4846(1) Points
4847            True
4848            False (*)
4849
48507.         Which of the following best describes the term "read consistency"?   Mark for Review
4851(1) Points
4852                                   
4853            It prevents users from querying tables on which they have not been granted SELECT privilege
4854
4855            It prevents other users from querying a table while updates are being executed on it
4856
4857            It prevents other users from seeing changes to a table until those changes have been committed (*)
4858            It ensures that all changes to a table are automatically committed
4859
48608.         Examine the following statements:
4861INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
4862SAVEPOINT Ins_Done;
4863DELETE employees; -- 107 rows deleted
4864SAVEPOINT Del_Done;
4865UPDATE emps SET last_name = 'Smith';
4866
4867How would you undo the last Update only?
4868
4869 Mark for Review
4870(1) Points
4871            ROLLBACK UPDATE;
4872            There is nothing you can do.
4873            COMMIT Del_Done;
4874            ROLLBACK to SAVEPOINT Del_Done; (*)
4875
48769.         If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
4877(1) Points
4878            True (*)
4879            False
4880
488110.       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
4882(1) Points
4883            An update statement
4884            A savepoint (*)
4885            A database link
4886            An object privilege
4887            A sequence
4888
488911.       You need not worry about controlling your transactions. Oracle does it all for you. True or False?    Mark for Review
4890(1) Points
4891            True
4892            False (*)
4893
489412.       If Oracle crashes, your changes are automatically rolled back. True or False? Mark for Review
4895(1) Points
4896            True (*)
4897            False
4898
489913.       Which SQL statement is used to remove all the changes made by an uncommitted transaction?         Mark for Review
4900(1) Points
4901            ROLLBACK; (*)
4902            UNDO;
4903            REVOKE;
4904            ROLLBACK TO SAVEPOINT;
4905
490614.       COMMIT saves all outstanding data changes? True or False?  Mark for Review
4907(1) Points
4908            True (*)
4909            False
4910
491115.       Examine the following statements:
4912UPDATE employees SET salary = 15000;
4913SAVEPOINT upd1_done;
4914UPDATE employees SET salary = 22000;
4915SAVEPOINT upd2_done;
4916DELETE FROM employees;
4917
4918You want to retain all the employees with a salary of 15000; What statement would you execute next?
4919
4920 Mark for Review
4921(1) Points
4922            ROLLBACK;
4923            ROLLBACK TO SAVEPOINT upd1_done; (*)
4924            ROLLBACK TO SAVEPOINT upd2_done;
4925            ROLLBACK TO SAVE upd1_done;