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

CREATE USER sh IDENTIFIED BY sh; GRANT CREATE ANY MATERIALIZED VIEW CREATE ANY DIMENSION , DROP ANY DIMENSION , QUERY REWRITE , GLOBAL QUERY REWRITE TO dw_manager WITH ADMIN OPTION; GRANT dw_manager TO sh WITH ADMIN OPTION; Which action the user SH cannot perform?

  1. Create and drop a materialized view

  2. Grant and revoke the role to and from other users

  3. Enable the role and exercise any privileges in the role's privilege domain

  4. Select from a table
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The user SH has been granted privileges to create/drop materialized views and dimensions, and can grant/revoke the dw_manager role with ADMIN OPTION. However, SH has not been granted SELECT privileges on any tables, so cannot select from tables (option D is correct - SH cannot perform this action).

Multiple choice technology databases

You need to create an index on the PASSPORT_RECORDS table. It contains 10 million rows of data. The key columns have low cardinality. The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator. Which type of index would be best for this type of table?

  1. Bitmap

  2. Btree

  3. Unique

  4. partitioned
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Bitmap indexes are highly efficient for columns with low cardinality (few unique values) and are exceptionally well-suited for queries that combine multiple WHERE conditions using logical operators like OR and AND, as they allow fast bitwise operations.

Multiple choice technology platforms and products

Which non key record field accepts null

  1. Character

  2. Number

  3. Date

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

Among the non-key record field types listed, Date is the only one that commonly accepts null values in database systems. Character and Number fields typically require values, while Binary fields store actual data content. Date fields can be nullable to represent unknown or optional dates.

Multiple choice technology databases

Which is a valid CREATE TABLE statement?
  1. CREATE TABLE EMP9$# AS (empid number(2));

  2. CREATE TABLE EMP*123 AS (empid number(2));

  3. CREATE TABLE PACKAGE AS (packid number(2));

  4. CREATE TABLE 1EMP_TEST AS (empid number(2));
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL table names cannot start with numbers (1EMP_TEST is invalid) or contain most special characters like * or $# without specific database escaping. PACKAGE is a reserved SQL keyword but is the only option without obvious syntax violations - some databases allow reserved words as table names with proper quoting.

Multiple choice technology databases

A SELECT statement can be to perform these three functions: -Choose rows from a table -Choose columns from a table -Bring together data that is stored in different tables by creating a link between them. Which set of keywords describe these capabilities?

  1. difference,projection,join

  2. selection.projection,join

  3. selection,intersection,join

  4. intersection,projection,join

  5. difference,projection,product
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In relational algebra, the three core capabilities of SELECT are: selection (choosing rows based on criteria), projection (choosing specific columns), and join (combining data from multiple tables based on related columns).

Multiple choice technology databases

Which four are types of functions available in SQL?

  1. string

  2. character

  3. integer

  4. numeric

  5. date

  6. conversion
Reveal answer Fill a bubble to check yourself
B,D,E,F Correct answer
Explanation

SQL functions are categorized into character (string manipulation), numeric (mathematical calculations), date (date manipulation), and conversion (changing data types) functions. 'String' and 'integer' are data types rather than standard SQL function categories.

Multiple choice technology databases

Which object privileges can be granted on a VIEW?

  1. DELETE,INSERT,SELECT

  2. ALTER,DELETE,INSERT,SELECT

  3. DELETE,INSERT,SELECT,UPDATE

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

Views support DML operations (DELETE, INSERT, UPDATE, SELECT) when they are updatable - typically simple views without aggregates, DISTINCT, GROUP BY, or joins. The ALTER privilege applies to tables, not views. Option C lists all four DML privileges that can be granted on an updatable view.

Multiple choice technology databases

Which iSQL*PLUS feature can be used to replace values in the WHERE clause?

  1. Substitution variables

  2. Replacement variables

  3. Prompt variables

  4. Instead-of variables

  5. This feature cannot be implemented through iSQL*PLUS
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to have knowledge about iSQL*PLUS and its features.

The answer is A. Substitution variables.

Explanation:

Substitution variables can be used to replace values in the WHERE clause of an iSQL*PLUS query. Substitution variables are identified by an ampersand (&) followed by a variable name. When a query is executed, iSQL*PLUS prompts the user to enter a value for each substitution variable. The entered value is then substituted in place of the substitution variable in the query.

Option B, Replacement variables, is not a valid iSQL*PLUS feature.

Option C, Prompt variables, is not a valid iSQL*PLUS feature.

Option D, Instead-of variables, is not a valid iSQL*PLUS feature.

Option E is incorrect because substitution variables can be used in iSQL*PLUS to replace values in the WHERE clause.

Therefore, the correct answer is A. The Answer is: A

Multiple choice technology databases

You want to display the titles of books that meet these criteria: 1. Purchased before January 21,2001 2. Price is less than $500 and greater than $900 You want to sort the results by thier date of purchase, starting with the most recently bought book. Which statement should you use?

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND puchase_date < '21-JAN-2001' ORDER BY purchase_date;

  2. SELECT book_title FROM books WHERE price IN (500,900) AND puchase_date < '21-JAN-2001' ORDER BY purchase_date ASC;

  3. SELECT book_title FROM books WHERE price <500 or >900 AND puchase_date < '21-JAN-2001' ORDER BY purchase_date DESC;

  4. SELECT book_title FROM books WHERE (price<500 OR price>900) AND puchase_date < '21-JAN-2001' ORDER BY purchase_date DESC;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To find books with a price less than $500 or greater than $900, the conditions must be grouped with parentheses (price&lt;500 OR price&gt;900) so the AND operator does not take precedence. Sorting from most recent requires ORDER BY purchase_date DESC.

Multiple choice technology databases

Examine the SQL statements that creates ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN('CREDIT','CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id,order_date)); For which column would an index be automatically created when you execute the above SQL statement?(Choose two)

  1. SER_NO

  2. ORDER_ID

  3. STATUS

  4. PROD_ID

  5. ORD_TOTAL

  6. Composite index on ORDER_ID and ORDER_DATE
Reveal answer Fill a bubble to check yourself
A,F Correct answer
Explanation

Oracle automatically creates a unique index for columns defined with a UNIQUE constraint (such as SER_NO) and a unique or composite index for columns defined as a PRIMARY KEY (such as the composite key on ORDER_ID and ORDER_DATE).

Multiple choice technology databases

In a SELECT statement that includes a WHERE clause, where is teh GROUP BY clause placed in the SELECT statement?

  1. Immediately after the SELECT clause

  2. Before the WHERE clause

  3. Before the FROM clause

  4. After the ORDER BY clause

  5. After the WHERE clause
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The correct order of SQL clauses is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. Therefore, GROUP BY must come after the WHERE clause when both are present, but before ORDER BY.

Multiple choice technology databases

In which case would you use a FULL OUTER JOIN?

  1. Both tables have NULL values

  2. You want all unmatched data from one table

  3. You want all matched data from both tables

  4. You want all unmatched data from both tables

  5. One of the tables has more data than the other

  6. You want all matched and unmatched data from only one table
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A FULL OUTER JOIN returns all matched rows from both tables PLUS all unmatched rows from both tables (with NULLs where there's no match). It's the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN, useful when you need complete data from both sides regardless of matching.

Multiple choice technology databases

Wich one is the first or root element of hibernate mapping document ______________

  1. <hibernate-mapping> element

  2. <mapping-hibernate> element

  3. <hibernate-mapping-source> element

  4. <hibernate-mapping-object> element
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Hibernate XML mapping files (.hbm.xml), the root element is . All class, property, and other mapping definitions are nested within this element. The other options are not valid Hibernate mapping elements.

Multiple choice technology databases

Examine this TRUNCATE TABLE command: - 68 - TRUNCATE TABLE departments; Which statements are true about the command?

  1. All extents are released.

  2. All rows of the table are deleted.

  3. Any associated indexes are truncated.

  4. No undo data is generated for the table's rows.
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows from a table without generating undo data (it's minimally logged). Unlike DELETE, it doesn't generate undo logs for rollback, making it faster but non-reversible. It also truncates any associated indexes. However, extents are not immediately released back to the system - they remain allocated to the table for reuse. The correct answers are B, C, and D.