Oracle Test1

1. Evaluate the set of SQLst: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), 1oc VARCNAR2 (13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

The DESCRIBE DEPT statement displays the structure of the DEPT table.

The ROLLBACK statement frees the storage space occupies by the DEPT table.

The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

The correct answer is that the DESCRIBE DEPT statement displays the structure of the DEPT table. This is because the DESCRIBE statement is used to retrieve information about the columns, datatypes, and constraints of a table, and in this case, it is specifically used to describe the DEPT table.

Explanation

The correct answer is that the DESCRIBE DEPT statement displays the structure of the DEPT table. This is because the DESCRIBE statement is used to retrieve information about the columns, datatypes, and constraints of a table, and in this case, it is specifically used to describe the DEPT table.

2. Which line is incorrect in this query?

1.	CREATE TABLE ACTORS(
2.	ACTOR_ID NUMBER NOT NULL
3.	,NAME VARCHAR2(32) NOT NULL
4.	,GENDER NUMBER DEFAULT 'M' NOT NULL
5.	,TYPECAST VARCHAR2(64) NULL);

4

2

5

4,5

2,5

Line 4 is incorrect in this query. The data type 'NUMBER' is not compatible with the default value 'M', which is a character.

Explanation

Line 4 is incorrect in this query. The data type 'NUMBER' is not compatible with the default value 'M', which is a character.

3. Will an error be caused by this CREATE TABLE command? If so, what will cause the error?

CREATE TABLE TEST(A FLOAT, B CHAR, C VARCHAR2, D NUMBER , E DATE);

No error will be returned.

Yes. The table name TEST is invalid.

Yes. All columns but the date column must have declared sizes.

Yes. The length of the VARCHAR2 variable must be defined.

Yes. The CHAR column must have a declared size.

The CREATE TABLE command will cause an error because the length of the VARCHAR2 variable must be defined. In Oracle, when creating a table, the length of a VARCHAR2 column must be specified. Since the length is not defined for the column C in the given command, an error will be returned.

Explanation

The CREATE TABLE command will cause an error because the length of the VARCHAR2 variable must be defined. In Oracle, when creating a table, the length of a VARCHAR2 column must be specified. Since the length is not defined for the column C in the given command, an error will be returned.

4. Which commands will remove all rows from the table assuming that any filters filter out at least one row?

TRUNCATE TABLE TEST;

DELETE FROM TEST WHERE ID = 1;

TRUNCATE TABLE TEST WHERE ID = 1;

REMOVE TABLE TEST;

DELETE FROM TEST;

The correct answer is TRUNCATE TABLE TEST; and DELETE FROM TEST;. TRUNCATE TABLE TEST; removes all rows from the table TEST, effectively deleting all the data in the table. DELETE FROM TEST; also removes all rows from the table TEST, but it does so by using a DELETE statement with no WHERE clause, which means it will delete all rows in the table. Both commands achieve the same result of removing all rows from the table.

Explanation

The correct answer is TRUNCATE TABLE TEST; and DELETE FROM TEST;. TRUNCATE TABLE TEST; removes all rows from the table TEST, effectively deleting all the data in the table. DELETE FROM TEST; also removes all rows from the table TEST, but it does so by using a DELETE statement with no WHERE clause, which means it will delete all rows in the table. Both commands achieve the same result of removing all rows from the table.

Submit

5. Let's assume that a ROLLBACK command is executed after each of the SQL commands listed below. Which of these commands cause the ROLLBACK command to have no effect?

INSERT

CREATE TABLE

CREATE SYNONYM

MERGE

TRUNCATE

The ROLLBACK command is used to undo transactions and revert any changes made by the SQL commands. In this case, the INSERT and MERGE commands would have made changes to the database, so the ROLLBACK command would undo those changes. However, the CREATE TABLE, CREATE SYNONYM, and TRUNCATE commands do not make any changes to the existing data, so the ROLLBACK command would have no effect on them.

Explanation

The ROLLBACK command is used to undo transactions and revert any changes made by the SQL commands. In this case, the INSERT and MERGE commands would have made changes to the database, so the ROLLBACK command would undo those changes. However, the CREATE TABLE, CREATE SYNONYM, and TRUNCATE commands do not make any changes to the existing data, so the ROLLBACK command would have no effect on them.

Submit

6. Which of these datatypes are valid?

NUMBER(10,2)

NUMBER(4,5)

FLOAT(122,2)

DATE(8)

CHAR(2500)

The given question asks for valid datatypes. In Oracle, NUMBER(10,2) is a valid datatype which represents a number with a precision of 10 digits and a scale of 2 digits. Similarly, NUMBER(4,5) is also a valid datatype in Oracle, representing a number with a precision of 4 digits and a scale of 5 digits.

Explanation

The given question asks for valid datatypes. In Oracle, NUMBER(10,2) is a valid datatype which represents a number with a precision of 10 digits and a scale of 2 digits. Similarly, NUMBER(4,5) is also a valid datatype in Oracle, representing a number with a precision of 4 digits and a scale of 5 digits.

Submit

7. A datatype is to be forcibly restricted to contain a number with specific restrictions. The number can be a whole number. The number can also contain three digits to the left of the decimal and two digits to the right of the decimal. What is its correct definition?

NUMBER

FLOAT

NUMBER(3,2)

NUMBER(2,3)

None of the above

not-available-via-ai

Explanation

not-available-via-ai

8. Select any correct answers:

DML commands execute an implicit rollback.

DML commands require an explicit COMMIT to be rolled back.

DDL commands can be undone.

DDL commands change data in tables.

None of the above.

The correct answer is "None of the above" because DML commands do not execute an implicit rollback. DML commands, such as INSERT, UPDATE, and DELETE, do not automatically rollback changes if there is an error or if the transaction is not committed. Instead, they require an explicit COMMIT to be rolled back. DDL commands, on the other hand, are used to define or modify the structure of database objects and cannot be undone. Therefore, none of the statements provided accurately describe the behavior of DML or DDL commands.

Explanation

The correct answer is "None of the above" because DML commands do not execute an implicit rollback. DML commands, such as INSERT, UPDATE, and DELETE, do not automatically rollback changes if there is an error or if the transaction is not committed. Instead, they require an explicit COMMIT to be rolled back. DDL commands, on the other hand, are used to define or modify the structure of database objects and cannot be undone. Therefore, none of the statements provided accurately describe the behavior of DML or DDL commands.

9. Which of these statements are true with respect to datatypes?

. NUMBER and FLOAT can be a maximum of 38 bytes.

The DATE datatype stores date values with a timestamp

CHAR and VARCHAR2 default to 1 byte if no length is defined.

Both VARCHAR2 and CHAR can exceed 2,000 bytes.

VARCHAR2 can be a maximum size of 4,000 bytes; CHAR, 2,000 bytes

The given answer is correct because it accurately states that the DATE datatype stores date values with a timestamp, and VARCHAR2 can have a maximum size of 4,000 bytes while CHAR can have a maximum size of 2,000 bytes.

Explanation

The given answer is correct because it accurately states that the DATE datatype stores date values with a timestamp, and VARCHAR2 can have a maximum size of 4,000 bytes while CHAR can have a maximum size of 2,000 bytes.

Submit

10. Given the table shown, select the INSERT commands that will cause errors:

SQL> DESC TEST

 Name              Null?    Type

 ----------------- -------- ------------

 ID                NOT NULL NUMBER

 STR               NOT NULL VARCHAR2(32) DEFAULT 'Empty String'

INSERT INTO TEST(ID) VALUES(1);

INSERT INTO TEST VALUES(2, 'This is a string');

INSERT INTO TEST(ID, STR) VALUES(3, 'This is another string');

INSERT INTO TEST(STR) VALUES('This is another string still');

INSERT INTO TEST(ID) VALUES('This is yet another string');

The first INSERT command will cause an error because it does not provide a value for the STR column, which is defined as NOT NULL. The second INSERT command will also cause an error because it tries to insert a string value into the ID column, which is defined as a NUMBER.

Explanation

The first INSERT command will cause an error because it does not provide a value for the STR column, which is defined as NOT NULL. The second INSERT command will also cause an error because it tries to insert a string value into the ID column, which is defined as a NUMBER.

Submit

11. Assuming a table is defined by the CREATE TABLE statement

CREATE TABLE TEST(A NUMBER(8,3)); 
which of these INSERT commands will add a row to the table?

INSERT INTO TEST VALUES(10000.234);

INSERT INTO TEST VALUES(1000.2345);

INSERT INTO TEST VALUES(10000.2345);

INSERT INTO TEST VALUES(100000.2345);

INSERT INTO TEST VALUES(100000.24);

The INSERT commands that will add a row to the table are those that have a value that is within the range specified by the column definition. In this case, the column "A" is defined as NUMBER(8,3), which means it can hold a number with a maximum of 8 digits, with 3 digits after the decimal point. The values 10000.234, 1000.2345, and 10000.2345 all fall within this range and can be inserted into the table. The values 100000.2345 and 100000.24 exceed the maximum number of digits specified and cannot be inserted.

Explanation

The INSERT commands that will add a row to the table are those that have a value that is within the range specified by the column definition. In this case, the column "A" is defined as NUMBER(8,3), which means it can hold a number with a maximum of 8 digits, with 3 digits after the decimal point. The values 10000.234, 1000.2345, and 10000.2345 all fall within this range and can be inserted into the table. The values 100000.2345 and 100000.24 exceed the maximum number of digits specified and cannot be inserted.

Submit

12. Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement?

You cannot roll back this statement.

All pending transactions are committed

All views based on the DEPT table are deleted

All data in the table is deleted, and the table structure is also deleted

All data in the table is deleted, but the structure of the table is retained.

The SQL statement "DROP TABLE DEPT" cannot be rolled back, meaning that once it is executed, it cannot be undone. Additionally, all pending transactions are committed, meaning that any changes made in those transactions will be permanently saved. Furthermore, the statement deletes all data in the DEPT table, including the table structure itself. Therefore, after executing this statement, the DEPT table will no longer exist in the database.

Explanation

The SQL statement "DROP TABLE DEPT" cannot be rolled back, meaning that once it is executed, it cannot be undone. Additionally, all pending transactions are committed, meaning that any changes made in those transactions will be permanently saved. Furthermore, the statement deletes all data in the DEPT table, including the table structure itself. Therefore, after executing this statement, the DEPT table will no longer exist in the database.

Submit