Computer Knowledge

Database and SQL

3,929 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 of the following is not correct about a View ?

  1. To protect some of the columns of a table from other users

  2. Occupies data storage space

  3. To hide complexity of a query

  4. To hide complexity of a calculations
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Views are virtual tables that store only the query definition, not the actual data. Option B correctly identifies what is NOT true about views - they do NOT occupy separate data storage space. The view data is dynamically retrieved from underlying tables when queried. Options A, C, and D are all valid uses of views: column security, simplifying complex queries, and abstracting calculations.

Multiple choice technology databases

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

  1. SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

  2. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

  3. SELECT dept_id, job_cat, MAX(salary) FROM employees;

  4. SELECT dept_id, job_cat, MAX(salary) FROM employees v

  5. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat, salary;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to know SQL syntax for selecting data from a table, using aggregate functions like MAX(), and grouping data using GROUP BY.

Option A: This option is incorrect because it includes a comparison statement with MAX(salary), which is not valid. The MAX() function can only be used with aggregate functions or in GROUP BY queries.

Option B: This option is correct. It selects the department ID, job category, and maximum salary for each group of department ID and job category. The GROUP BY clause is used to group the data by department ID and job category.

Option C: This option is incorrect because it does not include a GROUP BY clause, so it would return the overall maximum salary in the entire table, rather than the maximum salary for each job category in each department.

Option D: This option is incorrect because it selects the same data as option B, but uses an alias for the table name. This is not necessary and does not affect the query.

Option E: This option is incorrect because it includes the salary column in the GROUP BY clause, which would group the data by salary as well. This would result in multiple rows for each department ID and job category, with the same maximum salary value.

Therefore, the correct answer is: B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

Multiple choice technology databases

Which is not part of the Data Definiton Language

  1. CREATE

  2. ALTER

  3. ALTER SESSION

  4. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Data Definition Language (DDL) commands define database structures and include CREATE, ALTER, DROP, TRUNCATE, RENAME, and COMMENT. ALTER SESSION is not a DDL command - it's a session control command that modifies database session parameters like language, date format, or optimizer settings. Options A and B (CREATE and ALTER) are core DDL statements.

Multiple choice technology databases

If an UNIQUE KEY constraint on DATE column is created, will it accept the rows that are inserted with SYSDATE ?

  1. Will

  2. Wont

  3. NA

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

When SYSDATE is inserted into a DATE column, multiple rows inserted on the same day will have the same date value (time component may be truncated). This violates the UNIQUE constraint which requires all values to be distinct. The question tests understanding that SYSDATE-derived values can conflict when only the date portion is considered. Options C and D are not meaningful responses.

Multiple choice technology databases

Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?

  1. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  2. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.

  3. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  4. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym.

  5. Scott cannot create a synonym because synonyms can be created only for tables.

  6. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to know about synonyms in Oracle and how they can be used to eliminate the need to qualify the view name with the owner name.

Option A is incorrect because CREATE PRIVATE SYNONYM is not a valid command in Oracle.

Option B is correct because Scott can create a public synonym for the EMP_DEPT_LOC_VU view using the CREATE SYNONYM command. Once the synonym is created, he can use it to reference the view without specifying the owner name each time.

Option C is incorrect because CREATE LOCAL SYNONYM is not a valid command in Oracle.

Option D is incorrect because the syntax of the CREATE SYNONYM command is incorrect. The correct syntax is CREATE SYNONYM synonym_name FOR object_name, where object_name is the name of the view or table.

Option E is incorrect because synonyms can be created not only for tables but also for views.

Option F is incorrect because Scott can create a public synonym for the view. Mary does not need to create a private synonym.

Therefore, the correct answer is:

The Answer is: B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.

Multiple choice technology databases

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e.department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?

  1. SELECT employee_id, department_id, department_name, Salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

  2. SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

  3. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

  4. SELECT employee_id, department_id, department_name, salary FROM employees JOIN departments USING (e.department_id, d.department_id);
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The original query uses Oracle's old-style comma join syntax with a WHERE clause to join tables. Option C converts this to explicit ANSI JOIN syntax with an ON clause, which produces identical results - both return employee data with matching department information.

Multiple choice technology databases

In which scenario would an index be most useful?

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Indexes are most effective on columns with high cardinality - many distinct values relative to total rows. Low cardinality columns (like gender with only 2-3 values) see little benefit from indexing because each index entry points to many rows.

Multiple choice technology databases

When should you create a role? (Choose two.)

  1. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

  2. to grant a group of related privileges to a user

  3. when the number of people using the database is very high

  4. to simplify the process of granting and revoking privileges

  5. to simplify profile maintenance for a user who is constantly traveling
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Roles bundle related privileges together. You grant a role to users rather than granting individual privileges. This simplifies privilege management - grant or revoke once on the role, not per user per privilege. Roles are the proper abstraction for permission management.

Multiple choice technology databases

Which two statements about sequences are true? (Choose two.)

  1. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

  2. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

  3. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

  4. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

  5. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

  6. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
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

NOT IN is a multi-row operator designed to compare a value against a list returned by a multi-row subquery. Operators like =, LIKE, BETWEEN, IS, and <> are single-row operators and will raise a runtime SQL error if the subquery returns multiple rows.

Multiple choice technology databases

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id,employee_name, department_name,manager_id FROM employees e,departments d WHERE e.department_id = d.department_id;

  4. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

  5. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id=d.department_id;
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Oracle doesn't support ALTER VIEW to modify a view's query definition. To modify a view, you must use CREATE OR REPLACE VIEW, which preserves dependent objects and privileges while changing the underlying query.

Multiple choice technology databases

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?

  1. UPDATE new_employees SET name = (SELECT last_name|| first_ name FROM employees WHERE employee_id =180) WHERE employee_id =180;

  2. UPDATE new_employees SET name = (SELECT last_name ||first_name FROM employees ) WHERE employee_ id =180;

  3. UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

  4. UPDATE new_employees SET name = (SELECT last name || first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A uses a correlated subquery with proper WHERE clause in both outer and inner queries. The subquery returns exactly one row (employee_id=180), so the scalar subquery can be used in the SET clause without error.