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 two of the following statements are true?

  1. A view can be created before creating the base table

  2. A view cannot be created before creating the base table

  3. A view will become invalid if the base table's column referred to in the view is altered

  4. A view will become invalid if any column in the base table is altered
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Views can be created before their base tables exist in Teradata due to late binding - the view definition is stored but not validated until the view is used. However, if columns specifically referenced in the view definition are altered or dropped, the view becomes invalid and cannot be used.

Multiple choice technology databases

The following statement will raise an exception on which line?

  1. select dept_name, avg(all salary), count(*) “number of employees”

  2. where deptno = dept_no

  3. and count(*) > 5

  4. group by dept_name
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Aggregate functions like COUNT(*) cannot be used in a WHERE clause - they require HAVING after GROUP BY. The WHERE clause filters rows before grouping, while HAVING filters after grouping.

Multiple choice technology web technology

Which response is faster to sort the data in datagrid?

  1. DataView

  2. SqlQuery

  3. Stored procedure

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

DataView sorting is performed in-memory after data is retrieved, making it faster for subsequent sorts without additional database round trips. Sorting via DataView uses indexes and doesn't require re-executing SQL queries. This approach is efficient when you need to re-sort data already loaded into the DataGrid.

Multiple choice technology databases

An existing sales catalog database structure exists on a system in your company. The company sells inventory from a single warehouse location that is across town from where the computer systems are located. The product table has been created with a nonclustered index, based on the product ID, which is also the primary key. Nonclustered indexes exist on the product category column and also the storage location column. Most of the reporting done is ordered by storage location. How would you change the existing index structure?

  1. Change the definition of the primary key so that it is a clustered index.

  2. Create a new clustered index, based on the combination of storage location and product category.

  3. Change the definition of the product category so that it is a clustered index.

  4. Change the definition of the storage location so that it is a clustered index.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Because most of the reporting is ordered by storage location, setting it as the clustered index physically sorts the table data by this column, optimizing query performance. The primary key on product ID can be changed to nonclustered.

Multiple choice technology databases

You are designing an application that will provide data entry clerks the capability of updating the data in several tables. You would like to ease entry and provide common input so the clerks do not need to enter data into all fields or enter redundant values. What types of technologies could you use to minimize the amount of input needed? (Select all that apply.)

  1. Foreign key

  2. Cascading update

  3. Identity column

  4. Default

  5. NULL

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

Cascading updates (B) automatically propagate changes to related tables, eliminating redundant data entry. Identity columns (C) auto-generate sequential values, removing manual input for keys. Default constraints (D) automatically supply common values when no explicit value is provided, reducing typing. NULL (E) allows fields to be optional, but it's more about absence of input than a technology that minimizes required input.

Multiple choice technology databases

You are the database developer for a leasing company. Your database includes a table that is defined as shown here: CREATE TABLE Lease (Id Int IDENTITY NOT NULL CONSTRAINT pk_lease_id PRIMARY KEY NONCLUSTERED, Lastname varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, SSNo char(9) NOT NULL, Rating char(10) NULL, Limit money NULL) Each SSNo must be unique. You want the data to be physically stored in SSNo sequence. Which constraint should you add to the SSNo column on the Lease table?

  1. The UNIQUE CLUSTERED constraint

  2. The UNIQUE UNCLUSTERED constraint

  3. The PRIMARY KEY CLUSTERED constraint

  4. The PRIMARY KEY UNCLUSTERED constraint
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To store data physically in SSNo sequence, you need a clustered index on that column. Since SSNo must be unique (as stated: 'Each SSNo must be unique'), you need a UNIQUE CLUSTERED constraint. The PRIMARY KEY is already defined on Id as NONCLUSTERED, so you can't make SSNo the primary key. A clustered index determines physical storage order.

Multiple choice technology databases

You are preparing a new index on a table that has 1,500 rows. 10 rows are added to this table every day. The table already has a primary key, and the new index does not represent the order in which data in the table is to be stored. Updates to the table occur periodically but are infrequent. Which type of index would you create under this situation?

  1. Use a clustered index with a high FILLFACTOR setting

  2. Use a clustered index with a low FILLFACTOR setting

  3. Use a nonclustered index with a high FILLFACTOR setting

  4. Use a nonclustered index with a low FILLFACTOR setting
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

With only 1500 rows and slow growth (10/day), a nonclustered index is appropriate because the table already has a primary key (which is typically clustered). Low FILLFACTOR leaves space on leaf pages for the new rows being added, reducing page splits. Updates are infrequent, so the wasted space from low fill factor is acceptable. A clustered index would be redundant since one likely exists for the primary key.

Multiple choice technology databases

You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task?

  1. UPDATE, DELETE, and INSERT TRiggers

  2. Just UPDATE and INSERT triggers

  3. INSTEAD OF TRiggers

  4. Triggers cannot be used in this circumstance.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

INSTEAD OF triggers fire before the constraint check and can modify or cancel the operation. By using an INSTEAD OF trigger, you can intercept problematic INSERT/UPDATE operations that would violate CHECK constraints and either fix the data or prevent the violation from occurring. AFTER triggers (like UPDATE/INSERT) fire after constraints are checked, so they can't prevent constraint violations.

Multiple choice technology databases

You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation?

  1. A stored procedure and a trigger

  2. A batch and a trigger

  3. An UPDATE TRigger and an INSERT trigger

  4. One trigger by itself
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A single trigger for both INSERT and UPDATE operations can validate that required columns have appropriate values whenever data is modified. The trigger can check both operations using the inserted table, which is available for both INSERT and UPDATE triggers. Combining both operations in one trigger is simpler than maintaining separate triggers.

Multiple choice technology databases

Which statement is valid when removing procedures?

  1. Use a drop procedure statement to drop a standalone procedure

  2. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package specification

  3. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package body

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DROP PROCEDURE statement is used to remove standalone procedures in Oracle. For procedures that are part of a package, you cannot drop them individually - you must drop or recompile the entire package. The ALTER PROCEDURE statement with REUSE SETTINGS is used for recompiling, not for dropping procedures.

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 correct view to query for information about compiled objects including procedures, functions, and packages. It contains the LAST_DDL_TIME column which shows when an object was last compiled or modified. USER_PROCEDURES doesn't exist in Oracle, and the other options are not standard views.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID, LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In PL/SQL, a procedure is invoked by name with parameters in parentheses, without any command keyword. EXECUTE is SQL*Plus syntax, not PL/SQL. RUN and START are SQL*Plus commands for executing scripts, not for calling procedures. The correct syntax is simply the procedure name with arguments.

Multiple choice technology databases

Which type of argument passes a value from a procedure to the calling environment?

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

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

OUT parameters are used to pass values from a procedure back to the calling environment. IN parameters pass values into the procedure, while OUT parameters return results. VARCHAR2 and BOOLEAN are data types, not parameter modes. The OUT mode specifically enables returning computed values.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT ‘AUSTIN’, V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus?

  1. EXECUTE INSERT_TEAM;

  2. EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);

  3. EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);

  4. EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY :=‘AUSTIN’);
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

Option B uses named notation with the EXECUTE command (SQL*Plus syntax), and Option C uses positional notation with EXECUTE. Option A fails because the first parameter V_ID is required (no DEFAULT). Option D uses incorrect assignment syntax (:= instead of => for named parameters in SQL*Plus).