Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice

To obtain the structure of an Oracle table, the command used is _______.

  1. STRUCTURE [TableName]

  2. DESCRIBE [TableName]

  3. DESCRIBE STRUCTURE [TableName]

  4. DESC TABLE [TableName]
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The DESCRIBE command (or DESC for short) is the correct SQL*Plus command to display the structure of a table, showing column names, data types, nullability, and other metadata. Option C adds an unnecessary STRUCTURE keyword, and options A and D use incorrect syntax.

Multiple choice

For referential integrity to hold, any field in a table that is declared a super key can contain only values from a parent table's primary key or a candidate key.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The statement is incorrect. Referential integrity involves FOREIGN KEYs referencing PRIMARY KEYs or UNIQUE constraints, not super keys. A super key is a set of columns that uniquely identifies rows within a single table, not a parent-child relationship concept. The statement confuses different database concepts.

Multiple choice

The current password file allows for five entries. New DBAs have been hired and five more entries need to be added to the file, for a total of ten. How can the allowed number of entries be increased in the password file?

  1. Manually edit the password file and add the new entries

  2. Alter the current password file and resize if to be larger

  3. Add the new entries; the password file will automatically grow

  4. Drop the current password file, recreate it with the appropriate number of entries and add everyone again
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle password files have a fixed size determined at creation time. You cannot alter, resize, or edit the file after creation (options A and B are invalid operations). The file does not auto-grow (option C is wrong). To change the entry limit, you must recreate the password file with ORAPWD using a different ENTRIES parameter, then re-add all users (option D is correct, though cumbersome).

Multiple choice

Which type of join should be written to perform an outer join of tables A and B that returns all rows from B?

  1. Any outer join

  2. A left outer join

  3. A cross join

  4. A right outer join

  5. An inner join
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In a RIGHT OUTER JOIN, all rows from the right table (B) are returned, plus matching rows from the left table (A). Non-matching rows from A show NULL. A LEFT OUTER JOIN would return all from A, not B. An INNER JOIN only returns matching rows from both. A CROSS JOIN returns the Cartesian product (all combinations).

Multiple choice

Which of the following options is not true about modifying table columns?

  1. You can drop a column at any time.

  2. You can add a column at any time as long as it is a NULL column.

  3. You can increase the number of characters in character columns or the number of digits in numeric columns.

  4. You cannot increase or decrease the number of decimal places.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle does allow modifying decimal places in NUMBER columns under certain conditions: you can always increase decimal places, and you can decrease them if existing data fits without rounding errors. The statement claiming you cannot increase OR decrease decimal places is therefore false, making D the correct answer to the 'not true' question.

Multiple choice

The EMPLOYEES table has these columns:

LAST_NAME VARCHAR2 (35) SALARY NUMBER (8,2) HIRE_DATE DATEManagement wants to add a default value to the SALARY column. It was planned to alter the table by using the following SQL statement:ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);Which of the following options is true about the ALTER statement?

  1. Column definitions cannot be altered to add DEFAULT values.

  2. A change to the DEFAULT value affects only subsequent insertions to the table.

  3. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.

  4. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A change to the DEFAULT value affects only subsequent insertions to the table.

Multiple choice

What does SQL stand for?

  1. Strong Question Language

  2. Structured Question Language

  3. Structured Query Language

  4. Start up Language
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL stands for Structured Query Language, the standard language for relational database management systems used to query, manipulate, and define data. The other options are fabrications: 'Strong Question Language' and 'Structured Question Language' incorrectly use 'Question' instead of 'Query', while 'Start up Language' has no relation to database terminology.

Multiple choice

The DBA has been informed that two new columns have been added to the ORDERS table. The blocks in the Order Entry form were created manually. How can he use a wizard to modify the Orders block to include items that corresponds to these columns?

  1. He cannot use wizards to add a new item to add data block that was created manually.

  2. Select the last item in the Orders block, then invoke the Layout Wizard from the menu.

  3. Select the Order Block in the Object Navigator, then invoke the Data Block Wizard from the menu.

  4. Select the Data Block node in the Object Navigator, then invoke the Data Block Wizard from the menu.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To add items corresponding to new columns to an existing manually-created data block, you select the block in the Object Navigator and invoke the Data Block Wizard, which can modify the block to include new items. The Layout Wizard only handles visual layout, not data block structure, and you cannot select individual items to invoke the Data Block Wizard.

Multiple choice

The STUDENT_GRADES table has the following columns: STUDENT_ID NUMBER (12) SEMESTER_END DATE GPA NUMBER (4, 3) Which of the following statements finds the highest Grade Point Average (GPA) per semester?

  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The GROUP BY clause organizes rows into groups based on semester_end, then MAX(gpa) finds the highest GPA within each group. The WHERE clause filters NULL values before grouping occurs. Options A and B are incorrect because they lack GROUP BY. Option D has incorrect syntax with WHERE after GROUP BY. Option E places WHERE after GROUP BY, which is invalid syntax - WHERE must precede GROUP BY.

Multiple choice

MILLER is an administrator who has FULL DBA privileges. When he attempts to drop the DEFAULT profile as shown below, he receives the given error message. Which of the following options best explains this error?

SQL> drop profile SYS.DEFAULT; drop profile SYS.DEFAULT * ERROR at line 1: ORA-00950: invalid DROP option

  1. The DEFAULT profile cannot be dropped.

  2. MILLER requires the DROP PROFILE privilege.

  3. Profiles created by SYS cannot be dropped.

  4. The CASCADE option was not used in the DROP PROFILE command.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DEFAULT profile is a mandatory system profile that cannot be dropped from Oracle database - it must always exist. MILLER has full DBA privileges, so this is not a permission issue (option B is incorrect). Option C is incorrect because SYS can drop most profiles it creates, but DEFAULT is special. Option D is incorrect because CASCADE is used when dropping a profile assigned to users, but DEFAULT cannot be dropped regardless.

Multiple choice

Which script should be executed to create user JOY and give him the ability to connect to the database and the ability to create tables, sequences, and procedures in his schema?

  1. CREATE Joy IDENTIFIED BY Joy18; GRANT CREATE CONNECT, TABLE, SEQUENCE, PROCEDURE TO Joy

  2. CREATE USER Joy IDENTIFIED BY Joy18; GRANT CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO Joy

  3. CREATE OR REPLACE USER Joy IDENTIFIED BY Joy18 GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE

  4. CREATE USER Joy IDENTIFIED BY Joy18; GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO Joy
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

CREATE USER  Joy IDENTIFIED BY  Joy18; GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO Joy

Multiple choice

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2 (25)

LAST_NAME VARCHAR2 (25)

HIRE_DATE DATE

Which UPDATE statement is valid?

  1. UPDATE employees SET first_name = 'John' SET last_name='Smith' WHERE employee_id = 180;

  2. UPDATE employees SET first_name = 'John', SET last_name ='Smith' WHERE employee_id = 180;

  3. UPDATE employees SET first_name = 'John' AND last_name ='Smith' WHERE employee_id = 180;

  4. UPDATE employees SET first_name = 'John', last_name ='Smith' WHERE employee_id = 180;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D is correct because UPDATE statements use comma-separated column assignments in the SET clause. Options A and B incorrectly use multiple SET keywords. Option C incorrectly uses AND instead of a comma - AND is a logical operator for WHERE clauses, not for separating column assignments in SET. The correct syntax is SET column1 = value1, column2 = value2.

Multiple choice

SQL is pronounced as SEQEL, what does 'E' stands for ?

  1. Enquiry

  2. Element

  3. English

  4. Exclusive
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL was originally called SEQUEL (Structured English Query Language), emphasizing that it was designed to be readable like English. The 'E' stands for English. When the name was shortened to SQL due to trademark issues, the pronunciation 'sequel' persisted in common usage alongside the letter-by-letter 'S-Q-L'.