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

The result of below statement will be - Select * from employee where EMPID = 100 AND CITY = "NEW YORK"

  1. All Employees data

  2. Employees data whose employee ID is 100 and City is NEW YORK

  3. Employees data whose employee ID is either 100 or city is NEW YORK

  4. Employees data whose employee ID lies between 1000 and 100 and city is NEW YORK
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The AND operator requires both conditions to be true - employee ID must be 100 AND city must be 'NEW YORK'. Option A returns all data, option C uses OR (either condition), option D is nonsense with 'between'.

Multiple choice technology programming languages

The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is

  1. EMP

  2. The table containing the column values

  3. DUAL

  4. An Oracle-defined table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DUAL table is a special one-row, one-column table automatically created by Oracle. It is used to perform arithmetic calculations or select values not derived from any specific table, such as system variables or constants, when no physical table is relevant.

Multiple choice technology databases

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ROWID access is fastest because ROWID is the physical disk address of the row - direct access with no lookup needed. Primary key and unique index require an index search first to get the ROWID, then access the row. Full table scan reads every row sequentially and is the slowest method.

Multiple choice technology databases

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

  1. ALTER TABLE students ADD PRIMARY KEY student_id;

  2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

  3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

  4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

  5. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct SQL syntax for adding a primary key constraint to an existing table is ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name). Parentheses are required around the column list, and MODIFY is not used for adding constraints.

Multiple choice technology databases

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

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

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

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

  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DDL statements like CREATE TABLE issue an implicit COMMIT before and after execution in Oracle. The table is created and committed before the ROLLBACK executes, so ROLLBACK has no effect on the DDL. DESCRIBE will successfully show the table structure. ROLLBACK doesn't free DDL storage space.

Multiple choice technology databases

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

  1. USER_TAB_PRIVS_MADE

  2. USER_TAB_PRIVS

  3. USER_COL_PRIVS_MADE

  4. USER_COL_PRIVS
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

USER_COL_PRIVS shows column-level privileges granted TO the current user. The _MADE variants (USER_COL_PRIVS_MADE, USER_TAB_PRIVS_MADE) show privileges granted BY the current user. Table-level privileges are in USER_TAB_PRIVS, not column-specific.

Multiple choice technology databases

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement?

  1. The SQL statement displays the desired results.

  2. The column in the WHERE clause should be changed to display the desired results.

  3. The operator in the WHERE clause should be changed to display the desired results.

  4. The WHERE clause should be changed to use an outer join to display the desired results.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

NULL requires special handling in SQL - it cannot be compared with = or != operators. You must use 'IS NULL' or 'IS NOT NULL' instead. The query 'WHERE DEPARTMENT_ID = NULL' will never match any row because NULL = NULL evaluates to UNKNOWN, not TRUE.

Multiple choice technology databases

Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?

  1. 0

  2. 1

  3. 0.00

  4. An error statement
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To evaluate the SQL statement, we need to understand the functions ROUND, TRUNC and MOD.

ROUND: rounds a number to a specified number of decimal places. If the second argument is negative, it rounds to the left of the decimal point. For example, ROUND(123.456, 2) = 123.46, ROUND(123.456, -1) = 120. TRUNC: truncates a number to a specified number of decimal places. If the second argument is negative, it truncates to the left of the decimal point. For example, TRUNC(123.456, 2) = 123.45, TRUNC(123.456, -1) = 120. MOD: returns the remainder of a division operation. For example, MOD(7, 3) = 1, MOD(1600, 10) = 0. Using these functions, we can evaluate the SQL statement step by step:

MOD(1600,10) returns 0, since there is no remainder when dividing 1600 by 10. TRUNC(0,-1) returns 0, since there is nothing to truncate to the left of the decimal point. ROUND(0,2) returns 0, since there is nothing to round to the right of the decimal point. Therefore, the final result displayed by the SQL statement is 0.

Multiple choice technology databases

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE KEY
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

PRIMARY KEY and UNIQUE constraints both create unique indexes implicitly to enforce uniqueness. NOT NULL doesn't require an index (it's just a check constraint). FOREIGN KEY and CHECK constraints don't create unique indexes - FK creates non-unique indexes only if specified, and CHECK is a rule validation.