SQL – Using DDL Statements


SQL – Using DDL Statements Questions

”;


Previous

Next

1.What is the full form of DDL in Oracle DB?

  1. Data Deleting Language
  2. Data Definition Language
  3. Data Delegating Language
  4. Dummy Data Language

Answer: B. DDL is one of the categories of SQL which stands for Data Definition Language. Other SQL types are DML, DCL, and TCL.

2.DDL statements are used for which of the following Oracle database objects?

  1. Tables
  2. Sub-queries
  3. Rows
  4. Columns

Answer: A. DDL contains commands like CREATE, ALTER and ANALYZE which are used to CREATE TABLEs, view stored subprograms and packages in a database schema.

3.What is the basic unit of storage in Oracle Database that contains data?

  1. View
  2. Column
  3. Query
  4. Table

Answer: D. Table is the basic unit of physical storage of data in Oracle database.

4.Which of the below options best define a View?

  1. It is the shorter form of a table
  2. It is the logical representation of the subsets from one or more tables
  3. It has only one row and one column
  4. None of the above

Answer: B. View is a query which behaves like a window to format the data contained in one or more tables. Views do not contain any physical data but just a query which are created during runtime.

5. Which of the following are database objects?

  1. Table
  2. Sequence
  3. Synonym
  4. All of the above

Answer: D. Objects which are physically stored in database schema are database objects.

6. Which of the following database objects generate numeric values?

  1. Table
  2. View
  3. Index
  4. Sequence

Answer: D. Sequence are used to generate unique values starting with a definite value and incremented by a specified factor. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

7.Which of the following database objects gives an alternative name to an object?

  1. Synonym
  2. Sequence
  3. View
  4. Index

Answer: A. A synonym provides a permanent alias for a database object. A public synonym is available to any database user. A private synonym is available only to the user who created it. A synonym is created by using the CREATE SYNONYM command. A synonym is deleted by using the DROP SYNONYM command. Only a user with DBA privileges can drop a public synonym.

8.Which of the following database objects improves the performance of some queries?

  1. Table
  2. Synonym
  3. View
  4. Index

Answer: D.

9. When a table can be created?

  1. When the database is not being used by any user
  2. When the database is newly created
  3. It can be created any time, even when a user is using the database
  4. None of the above

Answer: C. An index can be created to speed up the query process. DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY KEY and UNIQUE constraints automatically. An explicit index is created with the CREATE INDEX command. An index can be used by Oracle 11g automatically if a query criterion or sort operation is based on a column or an expression used to create the index.

10. What is true about a table?

  1. It is not mandatory to specify the size of a table
  2. The size of each table is the same
  3. A table can be modified online
  4. None of the above

Answer: A, C.

11. A table named 123_A is created for storing the number of employees in an organization. What is wrong in the name of the table?

  1. The name of a table cannot start with a digit
  2. Nothing is wrong in this name.
  3. You cannot use an underscore while naming a table
  4. None of the above

Answer: A. As per the object naming conventions, table name must start with an alphabet.

12. What is the range of number of letters a table name can have?

  1. 1-20 characters
  2. 1-10 characters
  3. 1-30 characters
  4. 1-50 characters

Answer: C. A table name cannot exceed more than 30 characters.

13 Which of the following characters can be used to name a table?

  1. A to Z
  2. a to z
  3. 0 to 9
  4. All of the above

Answer: D. As per the standard naming convention in Oracle, object”s name can contain alphabets in any case. Mandatorily, first place is for letters while the rest can be mix of letters and digits.

14. Which of the following special characters can be used to name a table?

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

Answer: B, C, D. No other special character, except (#, $, _), are allowed while naming a table. Use of special characters in the table name is discouraged.

15. What is true about the name of a table?

  1. A table can have a name which is used by some other object owned by the same user
  2. A sequence and a table can have same names
  3. A view and a table can have the same name
  4. A table name must not duplicate the name of another object owned by the same user

Answer: D. By virtue of namespace, a table name cannot be same as any other schema objects. Schema objects which share the same namespace include tables, views, sequences, private synonyms, stored procedures, stored functions, packages, materialized views, and user-defined types.

16.You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

  1. The table will not be created
  2. The table will be created and an underscore will be added automatically to the name COUNT_
  3. An ORA error will be thrown
  4. The table COUNT will be created without any errors

Answer: A, C. You cannot create a table with the name same as an Oracle Server reserved word.

17. You create a table using quoted identifiers ” ”. How will you refer this table?

  1. ”table_name”
  2. “table_name”
  3. Either of A or B
  4. None of the above

Answer: B. If the table is created with the name having a quoted identifier, it must be addressed using double quotes. Using quoted identifiers is not recommended. Quoted identifiers are case-sensitive

18. You create a table named EMPLOYEES. What among the following is possible?

  1. It can be referred to as eMPLOYEES
  2. It can be referred to as EMPLoyees
  3. It can be referred to as employees
  4. All of the above

Answer: D. Unquoted objects names are not case-senstive in Oracle.

19. What among the following are the pre-requisites for creating a table?

  1. CREATE TABLE privilege
  2. Storage space
  3. Data in the table
  4. None of the above

Answer: A, B. A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.

20. What is the syntax for creating a table?

  1. CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] );
  2. CREATE TABLE INTO [schema.] table (column datatype [DEFAULT expr] [,..] );
  3. CREATE TABLE VALUES [schema.] table (column datatype [DEFAULT expr] [,..] );
  4. None of the above

Answer: A.

21. Pick the element which you must specify while creating a table.

  1. Column name
  2. Column Data type
  3. Column size
  4. All of the above

Answer: D. A table must have atleasr one column, its data type specification, and precision (if required).

22. A user named “Kevin” wants to access a table which is owned by another user named “Jonathan”. Which of the following will work for Kevin?

  1. Select * from Kevin.employees;
  2. Select * from jonathan.employees;
  3. Either of A or B
  4. None of the above

Answer: B.

23. What is true about a schema?

  1. A schema is owned by a database user and has the same name as that user
  2. Each user owns a single schema
  3. Schema objects include database links
  4. All of the above

Answer: D. The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.

24. What among the following is true about tables?

  1. A default value is given to a table
  2. A default value can be given to a column of a table during an INSERT statement
  3. Either of A or B
  4. None of the above

Answer: B. A default value can be specified for a column during the definition using the keyword DEFAULT.

25. Which of the following can be used with the DEFAULT option while creating a table?

  1. Strings
  2. Expressions
  3. SQL functions
  4. All of the above

Answer: D. The default value for a column can either be a literal or a derivative using SQL function.

26. Which of the following command is used to see the structure of a table?

  1. UPDATE
  2. SHOW
  3. DESCRIBE
  4. SPOOL

Answer: C. DESCRIBE is a SQL*Plus command to list the structure of the table.

27.What is the limit of CHECK constraints on a column?

  1. No limit
  2. 1
  3. 2
  4. 4

Answer: A. Oracle imposes no limit on the check constraints on a column.

28. Which of the following commands will drop table employees? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. DROP employees
  2. DROP TABLE employees
  3. TRUNCATE employees
  4. None of the above

Answer: B.

29. What is true about a namespace?

  1. It is a group of object types
  2. Within a namespace, all the object names should be uniquely identified by schema and name
  3. The same type of objects in different namespaces can share the same name
  4. All of the above

Answer: D. A namespace defines a group of object types,within which all names must be uniquely identified-by schema and name.Objects in different namespaces can share the same name.

30. Which of the following object types share the same namespace?

  1. Synonyms
  2. Table
  3. Views
  4. All of the above

Answer: D.

31. What among the following is true about a table and an index?

  1. An index and a table can have the same name in a schema
  2. An index and a table within a schema cannot have the same name
  3. Neither of A nor B
  4. None of the above

Answer: A. As the index and constraints share the same namespace, a table and an index can have the same name.

32. What is true about creating a table?

  1. While creating a table, each column should be assigned a data type
  2. Data type assignment to columns is not mandatory
  3. A data type has to be assigned to a table and not to a column
  4. None of the above

Answer: A. Each column must possess behavioral attributes like data types and precision in order to build the structure of the table.

33. Suppose you create a table as shown below:

CREATE TABLE employees
(emp_id NUMBER(4),
last_name VARCHAR2 (20)
);

How much space will Oracle allocate to the LAST_NAME column?

  1. If there are no rows, then Oracle will not allocate any space to the last_name column
  2. If rows are populated then Oracle will allocate unlimited space to the last_name column
  3. Neither of A nor B
  4. None of the above options

Answer: A.

34. What is the range of size that a VARCHAR2 data type can take?

  1. 1 byte to 2 byte
  2. 1 byte to 2000 bytes
  3. 1 byte to 4000 bytes
  4. None of the above

Answer: C. Until Oracle 11g Release 2, string data type VARCHAR2 can maximum contain 4000 bytes.

35.What is the range of size that a CHAR data type can take?

  1. 1 byte to 2 byte
  2. 1 byte to 2000 bytes
  3. 1 byte to 4000 bytes
  4. 1 byte to 3000 bytes

Answer: B. Until Oracle 11g Release 2, string data type CHAR can maximum contain 2000 bytes.

36. What is true about the CHAR data type?

  1. If the data is not the length of the column, then it will be replaced with NULL values
  2. If the data is not the length of the column, then it will be padded with spaces
  3. It is mandatory to have the data of the same size as mentioned in the CHAR size, else it throws an ORA error
  4. None of the above

Answer: B. CHAR provides a fixed length storage to a value while VARCHAR2 is flexible. If the data of length less than CHAR precision is inserted in a CHAR column, the remaining length will be padded to the column value.

37. Which of the following is a data type for variable length binary data?

  1. VARCHAR
  2. VARCHAR2
  3. RAW
  4. NVARCHAR2

Answer: C.

38. What is the precision allowed for the NUMBER data type?

  1. 1 to 20
  2. 1 to 4K
  3. 1 to 30
  4. 1 to 38 digits

Answer: D. Until Oracle 11g Release 2, primary data type NUMBER had the maximum precision of 38 digits.

39. What is the scale allowed for the NUMBER data type?

  1. 1 to 20
  2. -84 to 100
  3. -84 to 127
  4. None of the above

Answer: C.

40. Which of the following are the data types for date and time data?

  1. TIMESTAMP
  2. INTERVAL DAY TO SECOND
  3. TIMESTAMP WITH LOCAL TIMEZONE
  4. All of the above

Answer: D.

41. Which of the following data types are for large objects?

  1. CLOB
  2. BLOB
  3. RAW
  4. All of the above

Answer: A, B. LOB data types in SQL are BLOB, CLOB, and BFILE.

42. What will happen if the inserted value is of a smaller length as defined for a VARCHAR2 data type column?

  1. It will throw an ORA error
  2. It will get inserted successfully and the value will take up as much space as it needs.
  3. It will get inserted and the remaining space will be padded with spaces
  4. None of the above

Answer: B. VARCHAR2 contains variable length character data.

43. What does NUMBER (8, 2) in oracle mean?

  1. It means there are 8 digits in total, 6 digits before the decimal and 2 after the decimal
  2. It means there are 10 digits in total with 8 digits before the decimal and 2 after decimal
  3. It means there are 2 digits before the decimal and 8 after the decimal point
  4. None of the above

Answer: A. The p indicates precision,the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal.Example: NUMBER(7, 2) can store a numeric value up to 99999.99. If precision or scale isn”t specified, the column defaults to a precision of 38 digits.

44. Which of the following queries will create a table with no rows in it?

  1. CREATE TABLE emp AS SELECT 0 from dual;
  2. CREATE TABLE emp AS SELECT * from employees where 1=1;
  3. CREATE TABLE emp AS SELECT * from employees where 1=2;
  4. CREATE TABLE emp AS SELECT 0 from employees;

Answer: C. The direct path operation CTAS (CREATE TABLE .. AS SELECT..) can be used to copy the structure of an existing table without copying the data.

45. Which of the following statements would add a column to a table already created?

  1. ALTER TABLE table_name add column (job varchar2(20));
  2. ALTER TABLE table_name add job varchar2(20);
  3. ALTER TABLE table_name add (job varchar2(20));
  4. ALTER TABLE table_name add column (job);

Answer: C. The ALTER TABLE command allows a user to add a new column to a table.The same rules for creating a column in a new table apply to adding a column to an existing table.The new column must be defined by a column name and datatype (and width, if applicable).A default value can also be assigned. The difference is that the new column is added at the end of the existing table-it will be the last column.

46. Which of the following statements will modify the data type of an already existing column?

  1. ALTER TABLE table_name MODIFY (job varchar2(10) );
  2. ALTER TABLE table_name MODIFY job varchar2(10);
  3. ALTER TABLE table_name MODIFY column (job varchar2(10) );
  4. ALTER TABLE table_name MODIFY (job varchar2(10) );

Answer: A. The ALTER TABLE..MODIFY is used to modify column definition in a table. The admissible changes are increasing column precision, change datatype within a datatype family, or change the default value of the column.

47. Which of the following statements will remove a column from the table?

  1.  ALTER TABLE table_name DROP (job varchar2(10) );
  2. ALTER TABLE table table_name DROP COLUMN (job varchar2(10) );
  3. ALTER TABLE table table_name DROP COLUMN (job);
  4. ALTER TABLE table_name MODIFY (job varchar2(10) );

Answer: C. The ALTER TABLE..DROP COLUMN can be used to drop a column from the table.

48. Which of the following will rename the column emp_id to empno?

  1. ALTER TABLE employees RENAME column emp_id to empno;
  2. ALTER TABLE employees RENAME emp_id to empno;
  3. ALTER TABLE employees RENAME column emp_id to empno;
  4. None of the above;

Answer: A. The ALTER TABLE..RENAME can be used to rename an existing column in teh table.

49. You need to mark the table employees as read only. Which of the following statements will you execute to get the required result?

  1. ALTER TABLE employees set READ;
  2. ALTER TABLE employees READ ONLY;
  3. ALTER TABLE employees READ_ONLY;
  4. ALTER TABLE employees set READ ONLY;

Answer: B. A table can be marked read only to make it passive against the DML and DDL statements. The read only feature was introduced in Oracle 11g.

50. What among the following is true about DDL statements?

  1. DDL commands become the part of ongoing transaction
  2. DDL commands are auto commit and end the ongoing active transaction
  3. If the DDL command fails, the current transaction is still committed
  4. If the DDL command fails, the current transaction is rolled back

Answer: B. DDL commands are auto commit only if they are successfully executed without errors. If DDL command fails, the ongoing transaction is still active in the session and not committed into the database.

51. What happens if there is an active transaction against a table on which a DDL is issued?

  1. The transaction rolls back
  2. The transaction is committed and terminated
  3. Both A and B
  4. None of the above

Answer: B.

52. Which of the following commands will remove unused columns in an SQL statement?

  1. ALTER TABLE tablename DROP COLUMN column_name;
  2. ALTER TABLE tablename DROP unused columns;
  3. ALTER TABLE tablename set unused column;
  4. ALTER TABLE tablename DROP columns;

Answer: C. The SET UNUSED command drops only the un-used columns from a table and is faster

53. What happens when a table which is marked Read Only is attempted for drop?

  1. It will throw an error
  2. It will no longer remain Read Only but cannot be dropped either
  3. It will be dropped without errors
  4. It will remain un-touched

Answer: C. The DROP command affects the data dictionary definition of the tables which are not Read Only and hence dropping is possible

Consider the following statement and answer the questions 54 and 55 that follow:

CREATE TABLE departments 
(dept_id NUMBER (2),
 dept_name VARCHAR2(14),
 create_date DATE DEFAULT SYSDATE);

54. What will happen if the DEFAULT clause specification is removed from the statement?

  1. The script will throw error because DATE columns must be specified with a default value
  2. A system generated default value will be assigned to the column
  3. Table will be created with no default value for CREATE_DATE column
  4. None of the above

Answer: C.

55.What is true about the above statement?

  1. It will automatically commit the transaction in session
  2. It will create the table DEPARTMENTS in the schema
  3. It will set a default value for CREATE_DATE column
  4. None of the above

Answer: A, B, C.

56. Up to which limit can a BLOB data type column hold values?

  1. 1 KB
  2. 2 GB
  3. 4 GB
  4. 3 KB

Answer: C. As per Oracle 11g, the maximum size of data accomodated in a BLOB can be 4GB.

57.What is the difference between CLOB and BLOB data types? (Choose the most appropriate answer)

  1. CLOB is character data , BLOB is binary data
  2. CLOB is character data up to 2GB, BLOB is binary data up to 4 GB
  3. CLOB is character data up to 4 GB, BLOB is binary data up to 4 GB
  4. None of the above

Answer: C. CLOB is a character large object which is used to store character files like PDF, docs and text files while BLOB is a binary LOB used to store media files.

58.What among the following is a ROWID?

  1. It is a serial number given to a set of rows starting with 1
  2. It is an alphanumeric address given to a row in a table
  3. Both A and B
  4. None of the above

Answer: B. It is a base-64 system representing the unique address of a row in its table.

59.What is the data type used for storing Binary data stored in an external file (up to 4 GB)?

  1. BLOB
  2. CLOB
  3. CFILE
  4. BFILE

Answer: D. BFILE is an external LOB type which is used to refer external media files. Internal LOB types are BLOB and CLOB which are used for binary large files and character large files stored in the database.

60. What is true about a table created with a sub-query?

  1. A VARCHAR2 data type column is not copied when a table is created using a sub-query
  2. A CLOB data type column is not copied when a table is created using a sub-query
  3. A LONG column is not copied when a table is created using a sub-query
  4. None of the above

Answer: C. The CTAS method to create a table doesn”t copies the LONG column.

61. Which of the following data types cannot be used with a GROUP BY and an ORDER BY clause?

  1. CLOB
  2. VARCHAR2
  3. CHAR
  4. LONG

Answer: D. LONG data types cannot be used in GROUP BY and ORDER BY clause.

62. How many LONG columns can a table contain?

  1. None
  2. Maximum 2
  3. Minimum 2
  4. Only one

Answer: D. A table can contain maximum one column of LONG type.

63.Which of the following data types cannot be constrained in SQL?

  1. VARCHAR2
  2. LONG
  3. CHAR
  4. DATE

Answer: B. Constraints cannot be created on LONG type columns.

64. Which of the following data types can you use if you want a date with fractional seconds?

  1. DATE
  2. VARCHAR2
  3. TIMESTAMP
  4. None of the above

Answer: C. The TIMESTAMP data type provides additional precised information of date values. It provides fractional seconds and time zone information.

65. You need to store an interval of days, hours, minutes and seconds in a column. Which of the data type would help?

  1. TIMESTAMP
  2. INTERVAL YEAR TO MONTH
  3. INTERVAL DAY TO SECOND
  4. None of the above

Answer: C.

66.You need to find how many employees were hired in June, 2011 and June, 2012. Which of the following data types will help?

  1. INTERVAL DAY TO SECOND
  2. TIMESTAMP
  3. DATE
  4. INTERVAL YEAR TO MONTH

Answer: D.

67. What is true about constraints?

  1. They enforce rules at the row level
  2. They enforce rules at the table level
  3. It is mandatory to have constraints created while creating a table
  4. None of the above

Answer: B. A constraint is a rule applied to data being added to a table. It represents business rules, policies, or procedures.Data violating the constraint isn”t added to the table.A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command. A constraint based on composite columns (more than one column) must be created by using the table-level approach.

68. How are constraints helpful?

  1. They limit the storage capacity of a table and hence save DB space
  2. They prevent the modification of a table
  3. They prevent deletion of a table if there are dependencies
  4. None of the above

Answer: C. A constraint is a rule applied to data being added to a table.It represents business rules, policies, or procedures.Data violating the constraint isn”t added to the table.

69.A RAW data type column can store variable-length binary strings up to what value?

  1. 10 GB
  2. 1 TB
  3. 2 GB
  4. 4 GB

Answer: C.

70. Which of the following are valid constraints in Oracle?

  1. INDEX
  2. GENERAL
  3. UNIQUE
  4. PRIMARY KEY

Answer: C, D. A NOT NULL constraint can be created only with the column-level approach. A PRIMARY KEY constraint doesn”t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table. A FOREIGN KEY constraint requires that the column entry match a referenced column entry in the table or be NULL. A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column. A CHECK constraint ensures that data meets a given condition before it”s added to the table.

71. Which of the below DML operations consider constraints on a column?

  1. INSERT
  2. UNION
  3. DELETE
  4. UPDATE

Answer: A, C, D. All the DML operations obey constraints on the columns of the table.

72. When can a constraint be created?

  1. While creating a table
  2. After creating a table
  3. Both A and B
  4. None of the above

Answer: C. A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command.

73 Where are constraints stored?

  1. In the SGA
  2. In a table
  3. In data dictionary
  4. None of the above

Answer: C.

74. You create a constraint but do not name it. What will be the default name given to the constraint?

  1. SYS_Cn
  2. SYS_constraint
  3. SYS_Const
  4. SYS_C0

Answer: A. By default, Oracle gives a generic name to the constraints SYS_Cn, where the n is an integer to keep the name of a constraint unique.

75. What is the functional difference between a column-level constraint and a table-level constraint?

  1. Column-level constraint applies to all the columns of a table
  2. Table-level constraint applies to all the columns of a table
  3. They both are functionally the same, only the syntax is different
  4. None of the above

Answer: C. Functionally, the table level constraints and column level constraints work similar. Composite constraints can be defined at table level only.

76. What is true about column-level constraints?

  1. They can be created before the creation of a table
  2. They can be created before the defining of a column
  3. They are included when the column is defined
  4. None of the above

Answer: C. Column level constraints are defined along with the column specification.

77. What is true about NOT NULL constraints in SQL?

  1. They should be defined at the table level
  2. They should be defined at the column level
  3. They should be defined only on one column
  4. They should be defined only on one row

Answer: B. A NOT NULL constraint can be created only with the column-level approach.

Consider the following statement and answer the questions 78 and 79 that follow:

CREATE TABLE employees (
emp_id NUMBER (6)  CONSTRAINT emp_emp_id_PK PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
hire_date DATE 
);

78.Which type of constraint is created in the above statement?

  1. Column level constraint
  2. Table level constraint
  3. Named constraint
  4. Specification constraint

Answer: A. A column level constraint is created along with the column definition.

79. What modification can be made to the above statement to give it a table level constraint?

  1. CONSTRAINT emp_emp_id_PK PRIMARY KEY
  2. CONSTRAINT emp_emp_id_PK PRIMARY KEY (EMP_ID)
  3. CONSTRAINT emp_emp_id_PK EMP_ID PRIMARY KEY
  4. CONSTRAINT PRIMARY KEY emp_emp_id_PK

Answer: B.

80. What is true about PRIMARY KEY constraint?

  1. It applies a NOT NULL constraint implicitly to the column on which it is defined
  2. It applies a UNIQUE KEY constraint implicitly to the column on which it is defined
  3. It applies a CHECK constraint implicitly to the column on which it is defined
  4. It applies a DEFAULT constraint implicitly to the column on which it is defined

Answer: A. A PRIMARY KEY constraint doesn”t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table.

81. What among the following is true regarding a UNIQUE KEY constraint?

  1. UNIQUE KEY constraint and PRIMARY KEY constraint are the same
  2. UNIQUE KEY constraint allows NULL values if there is no NOT NULL defined on the column(s)
  3. We can have two identical rows when a UNIQUE KEY constraint is defined on a column
  4. None of the above

Answer: B. A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column.

Consider the following statement and answer the questions 82 and 83 that follow:

CREATE TABLE employees (
emp_id NUMBER (6)
first_name VARCHAR2(20),
last_name VARCHAR2(20),
job VARCHAR2(20),
hire_date DATE 
CONSTRAINT emp_job_UK UNIQUE (job));

82. Which of the below statements interpret the above CREATE TABLE script?

  1. This table cannot have two identical Job IDs
  2. This table can have two or more identical Job IDs
  3. This table can have NULL values in the JOB column
  4. None of the above

Answer: A, C. A UNIQUE constraint on the JOB column will restrict duplicate value but allows nulls.

83. If the constraint emp_job_UK is modified as emp_job_PK PRIMARY KEY (job), what will be outcome?

  1. This change can happen only if there”s no NULL value in the JOB column
  2. This change can happen without any restrictions
  3. This change will change the values of the column JOB
  4. None of the above

Answer: A.

84. What is true about the UNIQUE key constraint?

  1. A unique key index is implicitly created when a UNIQUE constraint is defined on a column
  2. A PRIMARY KEY constraint is implicitly created when a UNIQUE constraint is defined on a column
  3. A NOT NULL constraint is implicitly created when a UNIQUE constraint is defined on a column
  4. None of the above

Answer: A. When a unique constraint is imposed on a table, Oracle internally creates a unique key index on the column to restrict the duplication of values.

85. Which of the following is true about indexes?

  1. If an UPDATE statement is executed on a table, the indexes need to be manually updated as well
  2. If a DELETE statement is executed on a table, the indexes need to manually deleted as well
  3. When a table is dropped, the indexes are automatically dropped
  4. If an UPDATE statement is executed on a table, the corresponding indexes are updated as well.

Answer: C, D.

86.Which of the following CREATE TABLE statements is valid?

  1. CREATE TABLE EMPLOYEES 
(emp_id NUMBER (2) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL); 
  2. CREATE TABLE EMPLOYEES 
(emp_id NUMBER (2) PRIMARY KEY NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL PRIMARY KEY); 
  3. CREATE TABLE EMPLOYEES 
(emp_id NUMBER (2) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL UNIQUE);
  4. CREATE TABLE EMPLOYEES 
(emp_id NUMBER (2),
first_name VARCHAR(20),
last_name VARCHAR(20),
hire_date DATE NOT NULL,
CONSTRAINT emp_emp_id_PK PRIMARY KEY (emp_id));

Answer: A, C, D. All the CREATE TABLE scripts are valid.

87. How many PRIMARY KEY constraints can a table have?

  1. 0
  2. Unlimited
  3. 2
  4. 1

Answer: D. A table can have one and only one primary key.

88. You want to put a CHECK constraint on the EMP_ID such that it should be equal to the current value of a Sequence through which it is getting its values. Which of the following statements will help you achieve this?

  1. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (emp_id = EMPNO.CURRVAL);
  2. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (emp_id = EMPNO.NEXTVAL);
  3. Emp_id NUMBER (10) CONSTRAINT emp_emp_id_chk CHECK (EMPNO.CURRVAL);
  4. None of the above

Answer: D. You cannot use CURRVAL, NEXTVAL, LEVEL and ROWNUM pseudo columns in the CHECK constraint

89. Which of the following commands will help in converting the foreign key values to NULL?

  1. ON DELETE CASCADE
  2. ON DELETE SET NULL
  3. CASCADE
  4. REFERENCES

Answer: B.

90. You need to add a constraint to the EMPLOYEES table which restricts the addition of those employees who have salaries less than 10000. Which of the following commands will give you the required results?

  1. ALTER TABLE employees ADD CONSTRAINT emp_emp_sal_CHECK CHECK (salary >= 10000); 
  2. ALTER TABLE employees ADD CHECK CONSTRAINT emp_emp_sal_CHECK (salary>10000); 
  3. ALTER TABLE employees ADD CONSTRAINT CHECK emp_emp_sal_CHECK (salary = 10000); 
  4. ALTER TABLE employees ADD CONSTRAINT emp_emp_sal_CHECK (salary < 10000);

Answer: A.

91. You need to add a constraint to the EMPLOYEES table which imposes a restriction that the HIRE_DATE for all the employees should be equal to SYSDATE-7. Which of the following statements will give you the required results?

  1. ALTER TABLE employees ADD CHECK CONSTRAINT emp_emp_sal_CHECK  ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7); 
  2. ALTER TABLE employees ADD CONSTRAINT CHECK emp_emp_sal_CHECK ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7); 
  3. ALTER TABLE employees ADD emp_emp_sal_CHECK CHECK ( to_char(hire_date,''DD-MON-YY'') = SYSDATE -7);
  4. None of the above

Answer: D. You cannot use SYSDATE, UID, USER and USERENV functions in the CHECK constraint.

Consider the following query and answer the questions 92 to 94 that follow:

CREATE TABLE EMPLOYEES 
(emp_id NUMBER (2),
first_name VARCHAR(20),
last_name VARCHAR(20),
dept_id NUMBER (10),
hire_date DATE DEFAULT SYSDATE 
CONSTRAINT emp_emp_id_PK PRIMARY KEY (emp_id, hire_date)
CONSTRAINT emp_dept_FK FOREIGN KEY (dept_id) 
REFERENCES departments (dept_id)
);

92. Which of the below statements interpret the CREATE TABLE script?

  1. A FOREIGN KEY constraint is defined at the table level on the column DEPT_ID
  2. The FOREIGN KEY constraint defined references the DEPT_ID from the DEPARTMENTS table
  3. Both A and B
  4. None of the above

Answer: C. The keywords FOREIGN KEY and REFERENCES are used when we define a FOREIGN KEY constraint for referential integrity.

93. You need to delete all the dependent rows in DEPARTMENTS table when you delete the EMPLOYEES table. Which of the following command will solve the purpose? (Consider the table structures as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
  1. ON DELETE SET NULL
  2. ON DELETE CASCADE
  3. DELETE ALL
  4. FOR UPDATE

Answer: B. If ON DELETE CASCADE is included in the constraint definition and a record is deleted from the parent table,any corresponding records in the child table are also deleted automatically.

94. The EMPLOYEES table as shown below, has 5 employees who work in department 10. An executive from admin department issues the below query.

DELETE FROM departments
WHERE dept_id = 10;

What will be the outcome of this query? (Assume the table structures as shown)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
  1. Integrity constraint error
  2. Successful execution
  3. Neither of A nor B
  4. None of the above

Answer: A. The DEPT_ID from DEPARTMENTS is the foreign key in the table EMPLOYEES and there are employees in department 10 ,hence a value cannot be deleted from the parent table unless the child record is found.



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *