Oracle Database and PL/SQL Fundamentals
Test your knowledge of Oracle database concepts including PL/SQL programming, SQL queries, views, cursors, functions, and database administration fundamentals.
Questions
Which initialization parameter specifies the location of the alert log file
- UTL_FILE_DIR
- USER_DUMP_DEST
- LOG_ARCHIVE_DEST
- BACKGROUND_DUMP_DEST
GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;
- MANAGER must be a role.
- It allows the MANAGER to pass the specified privileges on to other users.
- It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
- It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
- It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
- It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.
Which best describes an inline view?
- A Schema Object
- A subquery that can contain an ORDER BY clause
- Another name for a view that contains group functions
- A subquery that is part of the FROM clause of another query
Which three are true?
- A MERGE statement is used to merge the data of one table with data from another.
- A MERGE statement replaces the data of one table with that of another.
- A MERGE statement can be used to insert new rows into a table.
- A MERGE statement can be used to update existing rows in a table.
Which four are types of functions available in SQL?
- String
- Character
- Integer
- Date
- Numeric
- Conversion
Find the error in the line for the query written below?
- Line 1
- Line 2
- Line 3
- Line 4
Which of the following are correct for a view
- a. View is a named SQL query stored in data dictionary
- b. View is a database object which stores data
- c. View is used for data hiding
- d. Views doesn’t occupy memory
Can we apply update, insert or delete on a view?
- True
- False
How many columns are there in dual table?
- 1
- 2
- >1
- N
There are 10 rows in Table A and 0 Rows in table B. Select * from A,B How many rows will be retrieved.
- 1
- 0
- 10
- None of the above
There is a % sign in one field of a column. Say for example table test_chk has a column name1 which contains a value “desdf%dsf”. What will be the queries to find it?
- SELECT * FROM test_chk ;
- select * from test_chk where name1 like '%%%' escape ''
- select * from test_chk where name1 like ' \ ' escape '';
- select * from test_chk where name1 like '%!%%' escape '!'
There is a % sign in one field of a column. Say for example table test_chk has a column name1 which contains a value “desdf%dsf”. What will be the queries to find it?
- SELECT * FROM test_chk ;
- select * from test_chk where name1 like '%%%' escape ''
- select * from test_chk where name1 like ' \ ' escape '';
- select * from test_chk where name1 like '%!%%' escape '!'
Can a function have an out parameter?
- True
- False
Can you add not null column to a table already containing data ?
- True
- False
Say for e.g. we declare x char(20): = 'name' and y varchar2(20):='name' If we compare x and y then what should be the result?
- Both values will be equal
- Both values will not be equal
- You can’t compare these two values
- Both will be NULL
In PL/SQL if we write select statement with INTO clause which of the following exceptions are returned
- NO_DATA_FOUND
- ROWTYPE_MISMATCH
- TOO_MANY_ROW
- DUP_VAL_ON_INDEX
Without closing the cursor, If you want to open it what will happen. If error, get what is the error?
- INVALID_CURSOR
- CURSOR_ALREADY_OPEN
- TIMEOUT_ON_RESOURCE
- SYS_INVALID_ROWID