Oracle__3. Oracle 1Z0-051 Exam - Table Objects Flashcards
Can a column with a UNIQUE constraint store nulls.
Yes
Can a foreign key contain a null value?
Yes
Is a constraint only enforced by an INSERT operation on the table?
NoA constraint is also enforced by an UPDATE operation on a table
Can a constraint be disabled even if the column contains data?
Yes
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
Can a constraint prevent deletion of a table?
YesIf there are dependencies. A parent cannot be deleted if a child exists.
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
Can the ALTER TABLE statement be used to modify a constraint after a table is created?
Yes
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 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.
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
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
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))
99
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
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.
If defining a numeric field, scale is defined as what?
The total number of digit to the right of the decimal point.
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
What is the maximum size number allowed for a CHAR?
2000
What is the maximum value for a date data type?
12-DEC-9999
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.
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
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
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
What term is used to choose rows from a table?
Selection
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?
No
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?
No
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?
30
Besides letters and number what 3 other symbols can be used in a tablename?
- _ (underscore)2. $3. #
By default are tablenames case sensitive?
No
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?
No
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?
No
List 5 common data integrity constraints?
- 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?
No
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?
- 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?
- 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?
No
What is the syntax to remove an index?
DROP INDEX indexname
What is the maximum significant digits for a number data type?
38
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?
No
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
