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 technology databases

Which table should you query to determine when your procedure was last compiled?

  1. USER_PROCEDURES

  2. USER_PROCS

  3. USER_OBJECTS

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

USER_OBJECTS is the standard dictionary view containing LAST_DDL_TIME, which shows when any schema object (including procedures) was last compiled or modified. USER_PROCEDURES and USER_PROCS are not standard Oracle views. USER_PLSQL_UNITS may exist in some versions but USER_OBJECTS is the canonical source for compilation timestamps.

Multiple choice technology mainframe

What are the different types of JOINS?

  1. LEFT JOIN

  2. RIGHT JOIN

  3. FULL JOIN

  4. SELF JOIN
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

All four options are valid JOIN types in SQL. LEFT JOIN returns all rows from left table plus matching right table rows. RIGHT JOIN returns all rows from right table plus matching left table rows. FULL JOIN returns all rows when there's a match in either table. SELF JOIN joins a table to itself. All are standard SQL JOIN types.

Multiple choice technology mainframe

UNION selects only distinct value by default.

  1. True

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

The UNION operator in SQL combines the result sets of two or more SELECT statements and automatically removes duplicate rows, selecting only distinct values by default. To retain duplicates, UNION ALL must be used. Therefore, the statement is true.

Multiple choice technology mainframe

What do we call the PRIMARY KEY of a reference table?

  1. Alternate Key

  2. Foreign Key

  3. Unique Key

  4. Alias Key
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In relational database design, the primary key of a referenced table becomes a foreign key in the referencing table. The question is slightly ambiguous as the primary key itself is a primary key, but in the context of referencing, it acts as a foreign key. An alternate or alias key is incorrect.

Multiple choice technology programming languages

INDEX NAME MUST BE UNIQUE ?

  1. True

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

In database systems, index names must be unique within a schema or table to avoid conflicts. This allows the database to uniquely identify which index to maintain or query when referenced by name in DDL or DML statements.

Multiple choice technology databases

create table Purchaser{ name string, credit_card integer (primary key), address string } create table Product{ product_id integer (primary key), product_name string } create table Purchaser_Product{ credit_card integer, product_id integer, purchase_date date } Based on the sample code above, what is the primary key in the Purchaser_Product table?

  1. purchase_date

  2. credit_card

  3. null

  4. credit_card & product_id

  5. product_id
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Purchaser_Product is a junction table resolving a many-to-many relationship between Purchaser and Product. Its primary key must be the composite of both foreign keys (credit_card, product_id) to ensure each combination is unique and prevent duplicate purchaser-product associations. Neither credit_card nor product_id alone is sufficient.

Multiple choice technology databases

Which are DML statements? (Choose all that apply.)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

DML (Data Manipulation Language) statements manipulate data: MERGE combines insert/update operations, UPDATE modifies existing rows, and DELETE removes rows. COMMIT is a transaction control statement (TCL). CREATE and DROP are DDL (Data Definition Language) statements that define schema structures, not manipulate data.

Multiple choice technology databases

Which four are correct guidelines for naming database tables? (Choose four.)

  1. Must begin with either a number or a letter

  2. Must be 1-30 characters long

  3. Should not be an Oracle Server reserved word

  4. Must contain only A-Z, a-z, 0-9, _, *, and #
  5. Must contain only A-Z, a-z, 0-9, _, $, and #

  6. Must begin with a letter
Reveal answer Fill a bubble to check yourself
B,C,E,F Correct answer
Explanation

Oracle table names must be 1-30 characters (B), cannot be reserved words (C), contain only A-Z, a-z, 0-9, _, $, and # (E), and must begin with a letter (F). Option A is wrong because names cannot begin with numbers. Option D incorrectly includes * and omits $. These are standard Oracle naming conventions.

Multiple choice technology databases

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

  1. SELECT ename, salary*12 'Annual Salary' FROM employees;

  2. SELECT ename, salary*12 "Annual Salary" FROM employees;

  3. SELECT ename, salary*12 AS Annual Salary FROM employees;

  4. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL, column aliases containing spaces must be enclosed in double quotes. Option B correctly uses "Annual Salary" with double quotes. Option A uses single quotes (string literal syntax, not alias syntax). Option C's AS clause without quotes would fail due to the space. Option D unnecessarily uses INITCAP function and incorrect alias syntax.

Multiple choice technology databases

Which operator can be used with a multiple-row subquery?

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. Is

  6. <>
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Multiple-row subqueries return multiple values, requiring operators that handle sets: IN, NOT IN, ANY, ALL. Single-row operators like =, <>, and LIKE cannot compare against multiple values. NOT IN correctly filters out rows matching any value in the subquery result set. BETWEEN is a range operator and 'Is' is not valid SQL.

Multiple choice technology databases

You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?

  1. SELECT last_name FROM EMP WHERE last_name LIKE '_A%';

  2. SELECT last_name FROM EMP WHERE last name ='*A%'

  3. SELECT last_name FROM EMP WHERE last name ='_A%';

  4. SELECT last_name FROM EMP WHERE last name LIKE '*A%'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know about the SQL SELECT statement and the LIKE operator. The LIKE operator is used to match a character pattern in a string. The underscore (_) represents a single character, while the percent sign (%) represents any number of characters.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%'; This option is correct. It selects the last names of employees from the EMP table where the last name has any character as the first character and "A" as the second character, followed by any number of characters.

B. SELECT last_name FROM EMP WHERE last name ='A%' This option is incorrect because the equal sign (=) is used for exact matches, and the asterisk () is not a valid wildcard character in SQL.

C. SELECT last_name FROM EMP WHERE last name ='A%'; This option is incorrect because the equal sign (=) is used for exact matches, and the underscore () represents only a single character, not any number of characters.

D. SELECT last_name FROM EMP WHERE last name LIKE 'A%' This option is incorrect because the percent sign (%) is used to match any number of characters, not the asterisk ().

Therefore, the correct answer is:

The Answer is: A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%';

Multiple choice technology web technology

Which statement creates a new user?

  1. CREATE USER Susan;

  2. CREATE OR REPLACE USER Susan;

  3. CREATE NEW USER Susan DEFAULT;

  4. CREATE USER Susan IDENTIFIED BY blue;

  5. CREATE NEW USER Susan IDENTIFIED by blue;

  6. CREATE OR REPLACE USER Susan IDENTIFIED BY blue;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D shows the correct SQL syntax for creating a user with password authentication: CREATE USER username IDENTIFIED BY password. Options A and B are incomplete (no password). Options C, E, and F use invalid syntax (NEW/OR REPLACE don't apply to CREATE USER).