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 mainframe

WHICH OF THE FOLLOWING IS NOT ASSOCIATED WITH WRITE STATEMENT

  1. ADD

  2. UPDATE

  3. MOVE

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

The WRITE statement in COBOL is used to output records. Associated statements include ADD, UPDATE, and DELETE which are data manipulation operations. MOVE is a data transfer statement that moves data between variables, not directly associated with WRITE operations in the context of file or database operations.

Multiple choice technology databases
  1. Evalute the CREAT TABLE Statement: CREATE TABLE products (prod_id NUMBER(6) CONSTRAINT PROD_ID_PK PRIMARY KEY, product_name VARCHAR2(10)) ; Which statement is true regarding the PROD_ID_PK constraint?

  1. It would be created only if a unique index is manually created first.

  2. It would be created and would ues an automatically created no unique index

  3. It would be created and would use an automatically created unique index

  4. It would be created and reamins in a disabled state because no index is specified in the command
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When a PRIMARY KEY constraint is defined in Oracle, it automatically creates a unique index to enforce uniqueness. This is default behavior - no manual index creation is required. The index is created automatically and maintained by the database to support the constraint.

Multiple choice technology databases

OE and SCOTT are the users in the DB.the ORDERS tables is owned by OE.Evalute the statements by the DBA in the following sequence: CREATE ROLE r1; GRANT SELECT ,INSERT ON OE.ORDERS TO r1; GRANT r1 TO SCOTT; GRANT SELECT ON OE.ORDERS TO SCOTT; REVOKE SELECT ON OE.ORDERS FROM SCOTT; what is the output after executing the statements?

  1. SCOTT would be not able to query the OE.ORDERS tables

  2. SCOTT would be able to query the OE.ORDERS tables

  3. The revoke statment would remove the SELECT privilege from SCOTT as well as from the role r1

  4. The revoke statment would give an error because the select privilege has been granted to the role r1
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SCOTT retains the SELECT privilege because it is still granted via the role r1 which is assigned to him. Revoking the direct SELECT privilege from SCOTT does not affect the privilege inherited through the active role, and it does not alter the role itself.

Multiple choice technology databases

Evalute the follwing SQL statements? ALTER TABLE HR.EMP SET UNUSED ( MGR_ID ); which statements is true ?

  1. Any constraints defined on the MGR_ID column would be removed by the above command

  2. Any views created on the EMP table that include the MGR_ID column would have to bve dropped and re-created

  3. Any index created on the MGR_ID column would continue to exists until the DROP UNUSED COLUMNS command is executed.

  4. Any synonym existing on the EMP table would have to be re-created
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology databases

In which sceneria would you use the ROLLUP operator for expresiion or column with in a GROUPBY clause?

  1. To find the groups forming the subtotal in a row

  2. To create group-wise totals for the groups specified within a GROUPBY clause

  3. To create grouping for expressions or columns specified within a GROUPBY clause in one direction,from right to left for calculating the subtotals.

  4. To create grouping for expressions or columns specified within a GROUPBY clause in all possible direction,which is cross-tabular report for calculating the subtotals
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ROLLUP creates subtotals in a right-to-left direction through the GROUP BY columns. For example, ROLLUP(A,B,C) creates subtotals for (A,B,C), then (A,B), then (A), then the grand total. This differs from CUBE which creates all possible combinations.

Multiple choice technology databases

Which statement best describes the GROUPING function?

  1. used to set the order for the groups to be used for calculating the grand totals and subtotals

  2. used to form various groups to caculate totals and subtotals created using ROLLUP and CUBE operators

  3. used to specify if the NULLL value in an expresion is a stored NULL value or created by ROLLUP and CUBE

  4. used to specify the concatenated groups expressions to be used for calculating the grand totals and subtotals
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The GROUPING function determines whether a NULL value in a row's result set is a naturally stored NULL value or a NULL created by a ROLLUP or CUBE operation to represent a subtotal or grand total, returning 1 for generated NULLs and 0 otherwise.

Multiple choice technology databases

Given below is the SQL statements executed in a user session: CREATE TABLE product (pcode NUMBER(2) ,pname VARCHAR2(10)); INSERT INTO product VALUES (1,'PEN'); INSERT INTO product VALUES (2,'PENCIL'); SAVEPOINT A; UPDATE product SET pcode=10 where pcode=1; SAVEPOINT B; DELETE FROM product where pcode=2; COMMIT; DELETE FROM product where pcode=10; ROLLBACK TO SAVEPOINT a; which statement describes the consequences?

  1. both the DELETE statements would be rolledback

  2. No SQL statements would be rolledback

  3. only the second DELETE statements would be rolledback

  4. both the DELETE statements and the update statements would be rolledback
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Once a COMMIT statement is executed, the transaction is finalized, making all previous changes permanent and destroying all active savepoints. The subsequent ROLLBACK TO SAVEPOINT A fails with an error, meaning no SQL statements are rolled back by it.

Multiple choice technology databases

which madatory clause has to be added to the following statements successfully create an external table called EMPDET? CREATE TABLE EMPDET( empno CHAR(2) ,ename CHAR(5) ,deptno N?UMBER(4)) ORGANIZATION EXTERNAL ( LOCATION('emp.dat'));

  1. TYPE

  2. REJECT LIMIT

  3. ACESS PARAMETERS

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

External tables in Oracle require a DEFAULT DIRECTORY clause to specify where the external data file is located. The LOCATION parameter points to the file, but DEFAULT DIRECTORY tells Oracle which directory object to use. Without it, Oracle cannot access the external file.

Multiple choice technology databases

which two statements are true about the GROUPING function ?

  1. used to find the groups forming the subtotal in a row

  2. used to identify the NULL value in the aggregate function.

  3. use to form the group sets involved in generating the totals and subtotals

  4. only be used with ROLLUP and CUBE operators specified in the GROUP BY clause
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

The GROUPING function returns 1 if the column value in a row is a NULL generated by a ROLLUP or CUBE operation (representing a subtotal/total group), and 0 otherwise. It can only be used with ROLLUP/CUBE in the GROUP BY clause. It does not identify standard stored NULL values.

Multiple choice technology databases

the following are the steps for a correlated subquery ,listed in a random order: 1)the where clause of the outer query is evaluted 2)The candiadate row is fetched from the table specified in the outer query 3) The procedure is repeated for the subsequent rows of the table,till all the rows are processed. 4)Rows are returned by the inner query ,after being evaluted with the value from the candiadate row in the outer query. Identify the option that contains the steps in the correct sequences in which the oracle server evalutes a correlated subquery ?

  1. 4,2,1,3

  2. 4,1,2,3

  3. 2,4,1,3

  4. 2,1,4,3
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle evaluates a correlated subquery by first fetching a candidate row from the outer query (2), executing the inner query using that candidate row's value (4), evaluating the outer query's WHERE clause based on the inner query result (1), and repeating for all remaining rows (3).

Multiple choice technology

Number of records in a file can be counted using rollup component with key parameter as

  1. All fields in the record

  2. NULL

  3. {}

  4. By selecting unique field.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To count all records without grouping, Rollup uses an empty key specification denoted as {}. This tells Rollup to treat all records as one group and apply count() aggregation. Using all fields would group by every unique record combination. NULL is not valid syntax, and selecting a unique field would group by that field's values.

Multiple choice technology platforms and products

Which is the full text-based query language

  1. Entity SQL

  2. LINQ to Entities

  3. LINQ to SQL

  4. LINQ to Entity SQL
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Entity SQL is a full text-based query language for Entity Framework, similar to SQL but operates on the conceptual model rather than directly on database tables. Unlike LINQ to Entities (which uses C#/VB.NET syntax), Entity SQL provides a string-based, SQL-like query syntax that can be dynamically constructed and executed at runtime.

Multiple choice technology platforms and products

Default method name created by Entity Framework to insert an entity in database is (Note: will be replaced by the name of actual Entity)

  1. InsertTo<EntityName>

  2. AddTo<EntityName>

  3. Insert<EntityName>

  4. Add<EntityName>
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In older versions of Entity Framework (such as ObjectContext in EF4), the generated code included methods named AddTo (e.g., AddToCustomers) to add entities. Newer versions use DbSet.Add().