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

Can a column with a UNIQUE constraint store nulls.

Yes

How well did you know this?

Not at all

Perfectly

Can a foreign key contain a null value?

Yes

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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

Yes

How well did you know this?

Not at all

Perfectly

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

NoNULL and NOT NULL are only defined at the column level

How well did you know this?

Not at all

Perfectly

Can a constraint prevent deletion of a table?

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

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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

Yes

How well did you know this?

Not at all

Perfectly

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)

an errorThe varchar2 needs a size value

How well did you know this?

Not at all

Perfectly

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))

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

How well did you know this?

Not at all

Perfectly

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))

5the CHAR field is right-padded with spaces

How well did you know this?

Not at all

Perfectly

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))

2The VARCHAR2 only uses the spaces needed

How well did you know this?

Not at all

Perfectly

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))

How well did you know this?

Not at all

Perfectly

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))

9.9The scale of 1 takes one value from the precision

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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))

38 digits

How well did you know this?

Not at all

Perfectly

What is the maximum size number allowed for a CHAR?

2000

How well did you know this?

Not at all

Perfectly

What is the maximum value for a date data type?

12-DEC-9999

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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

It will fail because PACKAGE is a keyword for Oracle

How well did you know this?

Not at all

Perfectly

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’)));

It will execute successfully

How well did you know this?

Not at all

Perfectly

What term is used to choose rows from a table?

Selection

How well did you know this?

Not at all

Perfectly

What term is used to choose columns from a table?

Projection

Can a column has more than on CHECK constraint?

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

Can a CHECK constraint reference SYSDATE?

Can a DEFAULT column value be SYSDATE?

Yes

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

Yes

What constraint only be defined at the column level?

NOT NULL

What happens to constraints if a table is renamed?

The constraints transfer

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

ALTER TABLE employeesMODIFY (salary DEFAULT 50)

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

past data is not affected but new nulls are added.

What is the syntax for renaming a column in a table

ALTER TABLE table1 RENAME columnold TO columnnew

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

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.

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

The index is updated

What is also created when a primary key is created?

A unique index.Primary keys cannot be duplicated

What is a composite index?

An index with 2 or more columns

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

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

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

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

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

FOR UPDATE

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

DESCRIBE tablename

What is left after a TRUNCATE table statement?

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

What is a Pseudocolumn?

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

What DDL is an efficient method of emptying a table?

TRUNCATE

Are delete triggers fired during a TRUNCATE?

What could block a TRUNCATE?

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

What is the maximum number of characters in a tablename?

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

1. _ (underscore)2. $3. #

By default are tablenames case sensitive?

What can be used to make tablesname case sensitive?

"Add double quotes before and after the tablename.""tablename"""

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

NOT NULL contraints

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

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

an Alias to name the column

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);

null

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

Yes

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

No. The default will be 1 character

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

LONG

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

LONG

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

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

Can any constraints be defined in a LONG column?

List 5 common data integrity constraints?

1. NOT NULL2. UNIQUE3. PRIMARY KEY4. FOREIGN KEY5. CHECK

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

SYS_CExample: SYS_C1234556

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

yes

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

columnname datatype CONTRAINT constraintname PRIMARY KEY

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

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

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

FOREIGN KEY

Can a primary key include more that one column?

Yes

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

table level

What happens if you forget to name a CONSTRAINT?

Oracle will automatically assign it a Number with the prefix SYS_C

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

UNIQUE Constraint

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

Are foreign key logical or physical pointers?

logical

What is the syntax of creating a foreign key?

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

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

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

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

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

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?

1. ON DELETE CASCADE2. ON DELETE SET NULL

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

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

Can a single column have more than one CHECK constraint?

yes

How is the UNIQUE constraint enforced?

By created an index on the unique key column or columns

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

READ ONLY

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

READ WRITE

What command will remove a table and all its contents?

DROP

What is also lost when a table is dropped?

Indexes and constraints

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

PURGE

What are the 2 types of indexes?

1. Unique2. nonunique

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

The update performs slower

What can improve performance for a select statement?

creating indexes on the column names used in the where clause

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

create an index

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

create an index

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

create an index

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

What is the syntax to remove an index?

DROP INDEX indexname

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

What is the maximum number of characters for a VARCHAR2?

4000

What is a SELECTION?

choose rows from a table

What is a PROJECTION?

choose column from a table

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

ALTER TABLE table1 DISABLE CONSTRAINT constraintname

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

The CHAR(5) is right padded with spaces

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

4 GB

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

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

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

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

Can SYSDATE be used as a default?

YES

Can SYSDATE be used as a check constraint?

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

a Unique key

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

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

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

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

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

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

Can a table have more than one foreign key?

Yes

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

ALTER TABLE students ADD CONSTRAINT stud_pk PRIMARY KEY (student_id)

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

USER_CONS_COLUMNS

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

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

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

ALTER TABLE Table1 DROP COLUMN (job1);

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

ALTER TABLE table1 RENAME job1 TO job2

What is the syntax to add column job1 to table1?

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

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

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

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

ALTER TABLE table1 READ ONLY;

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

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

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

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

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

ALTER TABLE table1 DROP CONSTRAINT constraint_name ;

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

-84 to 127

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

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

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

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

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

error

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

1234600 Rounded to the nearest hundred.

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

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

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

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

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

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

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

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

What is the default format for internally stored date?

numeric

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));

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

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

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

Can a Primary key contain a null?

No.

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)

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

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

Group functions on columns ignore NULL values

Can a Unique constraint have a null value?

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

List 5 types of constraints.

NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK

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

Yes

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

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

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

No.The characters are implicitly converted to numeric

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

No.HAVING and GROUP BY are interchangeable.

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));

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