Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DSO SQL > Oracle__3. Oracle 1Z0-051 Exam - Table Objects > Flashcards

Oracle__3. Oracle 1Z0-051 Exam - Table Objects Flashcards

1
Q

Can a column with a UNIQUE constraint store nulls.

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Can a foreign key contain a null value?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Is a constraint only enforced by an INSERT operation on the table?

A

NoA constraint is also enforced by an UPDATE operation on a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Can a constraint be disabled even if the column contains data?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Can all constraints be defined at the column level as well as the table level?

A

NoNULL and NOT NULL are only defined at the column level

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Can a constraint prevent deletion of a table?

A

YesIf there are dependencies. A parent cannot be deleted if a child exists.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Can a table from another schema in the same database be viewed be used in a different schema?

A

Yesadd prefix to the tablename to access that table from the other schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Can the ALTER TABLE statement be used to modify a constraint after a table is created?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is returned from this statement? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR, trans_Valid VARCHAR2)

A

an errorThe varchar2 needs a size value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How many characters can be store in the cust_name field? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR, trans_Valid VARCHAR2(5))

A

only 1 character because the size was not identified to the default of one is used.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is the length of the cust_name field if the value is ‘Ac’? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_Valid VARCHAR2(5))

A

5the CHAR field is right-padded with spaces

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the length of the trans_valid field if the value is ‘Ac’? CREATE TABLE order (trans_id Number(6) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

A

2The VARCHAR2 only uses the spaces needed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the largest value that can be stored in trans_id? CREATE TABLE order (trans_id Number(2) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_Valid VARCHAR2(5))

A

99

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the largest value that can be stored in trans_id? CREATE TABLE order (trans_id Number(2,1) NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

A

9.9The scale of 1 takes one value from the precision

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

In defining a numeric field, precision is defined as what?

A

The total number of decimal digits allowed in the field, either left or right of the decimal.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

If defining a numeric field, scale is defined as what?

A

The total number of digit to the right of the decimal point.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the precision of trans_id? CREATE TABLE order (trans_id Number NOT NULL, trans_date DATE NOT NULL, cust_name CHAR(5), trans_valid VARCHAR2(5))

A

38 digits

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the maximum size number allowed for a CHAR?

A

2000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the maximum value for a date data type?

A

12-DEC-9999

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What will result after the execution of this statement? CREATE TABLE EMP9$#_A as (empid number(2))

A

It will create a table name EMP9$#_A with one numeric field named empid.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What will result after the execution of this statement? CREATE TABLE EMP*123 as (empid number(2))

A

It will fail because there is an arithmetic expression in the table name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What will result after the execution of this statement? CREATE TABLE package as (empid number(2))

A

It will fail because PACKAGE is a keyword for Oracle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What will result after the execution of this statement? CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_pd PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min CHECK (ord_amount >=50), ord_status VARCHAR2(15) CONSTRAINT order_status_chk CHECK(ord_status IN (‘Shipped’,’Not Shipped’)) ord_pay_ode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode in (‘Check’,’Credit Card’,’Cash’)));

A

It will execute successfully

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What term is used to choose rows from a table?

A

Selection

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What term is used to choose columns from a table?

A

Projection

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Can a column has more than on CHECK constraint?

A

Yes. A single column can have multiple check constraints and there is no limit.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Can a CHECK constraint reference SYSDATE?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Can a DEFAULT column value be SYSDATE?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Can a column in a table be part of the Primary key and part of a Foreign key?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What constraint only be defined at the column level?

A

NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What happens to constraints if a table is renamed?

A

The constraints transfer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What is the syntax to add a default value of 50 for a column named salary in table employees?

A

ALTER TABLE employeesMODIFY (salary DEFAULT 50)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What happens to when a column constraint NOT NULL is added to a column and there are null values already in the column?

A

past data is not affected but new nulls are added.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is the syntax for renaming a column in a table

A

ALTER TABLE table1 RENAME columnold TO columnnew

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is the syntax of an insert statement with the values clause?

A

INSERT INTO table1 (field1, field3, field4)VALUES (expression1,expression2,expression3)The number of field names must match the number of values. The data types must also match.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What happens to an index if a DML operation is performed?

A

The index is updated

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is also created when a primary key is created?

A

A unique index.Primary keys cannot be duplicated

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is a composite index?

A

An index with 2 or more columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What is returned with this statement? ALTER TABLE customers MODIFY cust_name CONSTRAINT cust_name NOT NULL

A

A constraint is added to the cust_name column that does not allow nulls

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What is returned with this statement if the table is populated with records and the cust_name column already has null values? ALTER TABLE customers MODIFY cust_name CONSTRAINT cust_name NOT NULL

A

Nothing.Past values are not changed, but no new null values can be added to the cust_name column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What clause can be added to a select statement which will temporary lock records from other users for updating or deleting?

A

FOR UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What command can you use to display the structure of a table?

A

DESCRIBE tablename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What is left after a TRUNCATE table statement?

A

The table structure is left intact, but all data has been removed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is a Pseudocolumn?

A

values generated from commands that behave like a column of a table, but are not actually store in the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What DDL is an efficient method of emptying a table?

A

TRUNCATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Are delete triggers fired during a TRUNCATE?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

What could block a TRUNCATE?

A

if the table is a parent in a referential integrity constraint.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What is the maximum number of characters in a tablename?

A

30

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Besides letters and number what 3 other symbols can be used in a tablename?

A
  1. _ (underscore)2. $3. #
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

By default are tablenames case sensitive?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What can be used to make tablesname case sensitive?

A

“Add double quotes before and after the tablename.”“tablename”””

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

When an AS subquery is part of a CREATE table besides data types what else is passed to the new table?

A

NOT NULL contraints

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

When an AS subquery is part of a CREATE table will the primary key be passed to the new table?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What is needed in a create table if one of the columns is an expression?

A

an Alias to name the column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

If a table has 2 columns named ID and NAME what will be inserted into the name column with the following statement? INSERT INTO tablename (ID) VALUES (34);

A

null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

When creating a table with the VARCHAR2 datatype is the size parameter needed?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

When creating a table with the CHAR datatype is the size parameter needed?

A

No. The default will be 1 character

58
Q

What data type is not copied when using the subquery in a create table?

A

LONG

59
Q

What datatype cannot be used in a GROUP BY or an ORDER BY clause?

A

LONG

60
Q

How many column can have the data type of LONG in a single table?

A

Only one column of data type LONG can be in any table.

61
Q

Can any constraints be defined in a LONG column?

A

No

62
Q

List 5 common data integrity constraints?

A
  1. NOT NULL2. UNIQUE3. PRIMARY KEY4. FOREIGN KEY5. CHECK
63
Q

If you do not name your constraint Oracle will automatically create a number with a prefix, what is the prefix?

A

SYS_CExample: SYS_C1234556

64
Q

Functionally are table-level constraints and column-level constraints the same?

A

yes

65
Q

What is the syntax of a primary key defined at the column level?

A

columnname datatype CONTRAINT constraintname PRIMARY KEY

66
Q

What is the syntax of a primary key defined at the table level?

A

After the last column name is definedCONSTRAINT constraintname PRIMARY KEY (columnname)

67
Q

What constraint type is used to establish and enforce referential integrity?

A

FOREIGN KEY

68
Q

Can a primary key include more that one column?

A

Yes

69
Q

At what level must the primary key be defined if it includes more than one column?

A

table level

70
Q

What happens if you forget to name a CONSTRAINT?

A

Oracle will automatically assign it a Number with the prefix SYS_C

71
Q

What other constraint is automatically created on the columns in a primary key?

A

UNIQUE Constraint

72
Q

Can any of the field in a primary key contain a null?

A

No

73
Q

Are foreign key logical or physical pointers?

A

logical

74
Q

What is the syntax of creating a foreign key?

A

CONSTRAINT constraintname FOREIGN KEY (columnname) REFERENCES tablename(columnname)

75
Q

Can a foreign key be defined at the column level or at the table level?

A

Yes, but only if the foreign key is only one column

76
Q

When must a Primary key be defined at the table level?

A

When more than 1 column name is part of the primary key.

77
Q

What 2 options can be added to the parent table so that a row can be deleted even if it is referenced in a child table?

A
  1. ON DELETE CASCADE2. ON DELETE SET NULL
78
Q

Can a check constraint be added at the column level or at the table level?

A

Both, a check constraint can be added at either the column level or table level

79
Q

Can a single column have more than one CHECK constraint?

A

yes

80
Q

How is the UNIQUE constraint enforced?

A

By created an index on the unique key column or columns

81
Q

What keyword can be added to a table to make it read only?

A

READ ONLY

82
Q

What is the keyword to remove the READ ONLY on a table?

A

READ WRITE

83
Q

What command will remove a table and all its contents?

A

DROP

84
Q

What is also lost when a table is dropped?

A

Indexes and constraints

85
Q

What keyword is added to the DROP command to allow the space to be release back to the tablespace for use by another object?

A

PURGE

86
Q

What are the 2 types of indexes?

A
  1. Unique2. nonunique
87
Q

What can happen to a UPDATE statement on a table when indexes are created?

A

The update performs slower

88
Q

What can improve performance for a select statement?

A

creating indexes on the column names used in the where clause

89
Q

What can improve performance if a column has a large number of null values?

A

create an index

90
Q

What can improve performance on a column if the column contains a wide range of values?

A

create an index

91
Q

If a table is large and most queries return only 2% to 4% of the rows, what can be done to improve performance?

A

create an index

92
Q

If a column is references as part of an expression will an INDEX improve performance?

A

No

93
Q

What is the syntax to remove an index?

A

DROP INDEX indexname

94
Q

What is the maximum significant digits for a number data type?

A

38

95
Q

What is the maximum number of characters for a VARCHAR2?

A

4000

96
Q

What is a SELECTION?

A

choose rows from a table

97
Q

What is a PROJECTION?

A

choose column from a table

98
Q

How can you temporary disable a constraint without removing it from the table?

A

ALTER TABLE table1 DISABLE CONSTRAINT constraintname

99
Q

What is stored in a data type of CHAR(5) when a value of ‘AB’ is inserted?

A

The CHAR(5) is right padded with spaces

100
Q

The CLOB data type can have up to how many characters?

A

4 GB

101
Q

What else is created when you create a UNIQUE CONSTRAINT on a column?

A

A unique index is also created.But creating a unique index does not create a unique constraint

102
Q

What is the difference between a Unique Index and a Unique Constraint?

A

a unique index cannot be seen by the user. A unique constraint requires the values to be unique in the column.

103
Q

Can SYSDATE be used as a default?

A

YES

104
Q

Can SYSDATE be used as a check constraint?

A

No

105
Q

What is automatically created when a PRIMARY KEY or a UNIQUE constraint is created?

A

a Unique key

106
Q

What is the difference between a unique constraint and a unique index?

A

a unique constraint requires the values to be unique.unique indexes are not seen by the user

107
Q

What condition must exist before a primary key can be added to a table that already is populated with data?

A

No duplicate in the columns to be defined in the primary key.

108
Q

Can more than one index be created on the same column?

A

No. Only 1 single column index can be created on a column, but the column can be part of several multi-column indexes?

109
Q

Can a table have more than one foreign key?

A

Yes

110
Q

What is the syntax to create a primary key on Student_ID after the table students has been created?

A

ALTER TABLE students ADD CONSTRAINT stud_pk PRIMARY KEY (student_id)

111
Q

What is the name of the system view which displays the column associated with constraints on a table owned by the user?

A

USER_CONS_COLUMNS

112
Q

What is the correct syntax for a foreign key to the student_id column on the student table

A

CONSTRAINT student_pd FOREIGN KEY (student_id) REFERENCES students(student_id)

113
Q

What is the syntax to remove a column job1 from Table1?

A

ALTER TABLE Table1 DROP COLUMN (job1);

114
Q

What is the syntax to rename column job1 to job2 in table1?

A

ALTER TABLE table1 RENAME job1 TO job2

115
Q

What is the syntax to add column job1 to table1?

A

ALTER TABLE table1 ADD (job1 VARCHAR2(20));VARCHAR2(20) is just an example

116
Q

What is the syntax to change the data type of column job1 in table1?

A

ALTER TABLE table1 MODIFY (job1 VARCHAR2(10) );VARCHAR2(20) is just an example

117
Q

What is the syntax to change table table1 to Read only?

A

ALTER TABLE table1 READ ONLY;

118
Q

What is the syntax for adding a constraint to table1 on column job1 where the value must be equal to or greater than 2000?

A

ALTER TABLE table1 ADD CONSTRAINT constraint_name CHECK (job1 >= 2000);

119
Q

What is the syntax for modifying a constraint to table1 on job1.

A

ALTER TABLE table1 MODIFY (job1 CONSTRAINT constraint_name NOT NULL);

120
Q

What is the syntax to remove a constraint constraint_name from table1?

A

ALTER TABLE table1 DROP CONSTRAINT constraint_name ;

121
Q

What is the range for the scale for the number data type?

A

-84 to 127

122
Q

What is the syntax for creating table2 data structure without the data from table1?

A

CREATE TABLE table2 AS SELECT * FROM table1 WHERE 1=2;

123
Q

What will be the result if 12345.12345 is enter into a data type of NUMBER(6,2)?

A

errorThe range of the integer part is only from -9999 to 9999.

124
Q

What will be the result is 123456 is enter into a data type of NUMBER(6,2)?

A

error

125
Q

What will be the result is 123456 is enter into a data type of NUMBER(5,-2)?

A

1234600 Rounded to the nearest hundred.

126
Q

What will be the result is 123456789 is enter into a data type of NUMBER(5,-2)?

A

Error. Because it is outside the range; can have only five digits, excluding the four trailing zeros.

127
Q

What will be the result if 0.1 is enter into a data type of NUMBER(4,5)?

A

errorRequires a zero after the decimal point (5 - 4 = 1).

128
Q

What will be the result if 12345.58 is enter into a data type of NUMBER(*,1)?

A

The use of * in the precision specifies the default limit (38).

129
Q

Literals are values that represent a fixed values (constant). What are the 4 types of literals?

A

Text (or character) Numeric (integer and number) Datetime Interval

130
Q

What is the default format for internally stored date?

A

numeric

131
Q

What is not needed in this statement? CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, CONSTRAINT Ord_non UNIQUE (ord_no), CONSTRAINT Ord_pk PRIMARY KEY (ord_no));

A

The Unique constraint is not need because it is automatically created with the primary key constraint.

132
Q

A unique index will get created when either of 2 things are created. What are they?

A

A unique index gets created with either a Primary key or a Unique key is created on a table.

133
Q

Can a Primary key contain a null?

A

No.

134
Q

Change the syntax so the Ord_no field will have no duplicate values; CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL)

A

Add keyword UNIQUECREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE,item_no NUMBER(3),ord_date DATE DEFAULT NOT NULL)

135
Q

What is true about group functions on columns and null values?

A

Group functions on columns ignore NULL values

136
Q

Can a Unique constraint have a null value?

A

No.Unique constraints and Primary keys do not allow null values.

137
Q

List 5 types of constraints.

A

NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK

138
Q

Is the following statement true? A table can have only one primary key but multiple foreign keys.

A

Yes

139
Q

Will this statement create an error? CREATE TABLE products ( prod_id Number, prod_name CHAR(30), CONSTRAINT prod_name NOT NULL)

A

Yes.You cannot create a NOT NULL constraint at the table level.

140
Q

Will this clause cause an error? WHERE inv_no BETWEEN ‘101’ AND ‘102’

A

No.The characters are implicitly converted to numeric

141
Q

Will this statement create an error? SELECT item_no, AVG(qty) FROM ord_items HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no

A

No.HAVING and GROUP BY are interchangeable.

142
Q

What is wrong with this statement? CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(2), ord_Date DATE DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE(ord_no), CONSTRAINT ord_pk PRIMARY KEY(ord_no));

A

The PRIMARY_KEY make each row unique so the unique is not needed, plus UNIQUE allow nulls and PRIMARY KEY does not

DSO SQL (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions