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

Top N analysis requires _____ and ______?(Choose two)

  1. the use of rowed

  2. a GROUP BY clause

  3. an ORDER BY clause

  4. only an inline view

  5. an inline view and an outer query
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

Top-N queries need sorted data (ORDER BY) and a way to limit results - typically using an inline view with ROWNUM (Oracle) or LIMIT/FETCH (other databases) plus an outer query. Inline views are standard; GROUP BY is for aggregation.

Multiple choice technology databases

What does the FORCE option for creating a view do?

  1. creates a view with constraints

  2. creates a view even if the underlying parent table has constraints

  3. creates a view in another schema even if you don't have privileges

  4. creates a view regardless of whether or not the base tables exist
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The FORCE option in SQL view creation allows creating a view even when the base tables don't exist yet. This is useful for defining view structures before the underlying data is available. Without FORCE, the view creation would fail if base tables are missing.

Multiple choice technology databases

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  1. GRANT select ON dept TO ALL USERS;

  2. GRANT select ON dept TO ALL;

  3. GRANT QUERY ON dept TO ALL USERS;

  4. GRANT select ON dept TO PUBLIC;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to know the SQL syntax for granting privileges to users and the difference between "ALL USERS" and "PUBLIC".

Now, let's go through each option and explain why it is right or wrong:

A. GRANT select ON dept TO ALL USERS; This option is incorrect because the correct keyword to grant privileges to all users is "PUBLIC" instead of "ALL USERS". Also, "QUERY" is not a valid keyword, and the correct keyword to grant query privileges is "SELECT". Therefore, option A is incorrect.

B. GRANT select ON dept TO ALL; This option is incorrect because the correct keyword to grant query privileges is "SELECT" instead of "QUERY". Also, "ALL" is not a valid keyword to grant privileges to all users, and the correct keyword is "PUBLIC". Therefore, option B is incorrect.

C. GRANT QUERY ON dept TO ALL USERS; This option is incorrect because "QUERY" is not a valid keyword to grant privileges, and the correct keyword is "SELECT". Also, "ALL USERS" is not a valid keyword to grant privileges to all users, and the correct keyword is "PUBLIC". Therefore, option C is incorrect.

D. GRANT select ON dept TO PUBLIC; This option is correct. The correct keyword to grant select (query) privileges is "SELECT". The keyword "PUBLIC" is used to grant privileges to all users. Therefore, option D is the correct answer.

The Answer is: D

Multiple choice technology databases

Which three are true?(Choose three)

  1. A MERGE statement is used to merge the data of one table with data from another

  2. A MERGE statement replaces the data of one table with that of another

  3. A MERGE statement can be used to insert new rows into a table

  4. A MERGE statement can be used to update existing rows in a table
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Multiple choice technology databases

What is true about Sequences?

  1. Once created, a sequence belongs to a specific schema

  2. Once created, a sequence is linked to a specific table

  3. Once created, a sequence is automatically available to all users

  4. Only the DBA can control which squence is used by certain a table

  5. Once created, a sequence is automatically in all INSERT and UPDATE statements
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Once created, a sequence is an independent database object that generates unique numeric values. It's not tied to any specific table or schema - it's a schema object but available to all users with privileges. Sequences must be explicitly referenced in statements - they're not automatically used.

Multiple choice technology web technology

Which method is used to Get the name of the specified column from SqlDataReader (or any ADO.NET DataReader)?"

  1. GetSqlValue ()

  2. GetOrdinal ()

  3. GetName ()

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

GetName(int ordinal) returns the column name for a given zero-based position ordinal in DataReader. GetOrdinal is the inverse (name -> ordinal). GetSqlValue gets value as SqlTypes. GetName() is specifically for getting column names.

Multiple choice technology web technology

How to add auto increment column in the DataTable?

  1. auto.AutoIncrementSeed = 1

  2. auto.AutoIncrement = true

  3. auto.Increment = true;

  4. auto.IncrementSeed = 1;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Setting AutoIncrement=true enables auto-increment for a DataColumn. The other options use incorrect property names or syntax. However, a complete implementation would also set AutoIncrementSeed and AutoIncrementStep properties. The question asks about basic enabling.

Multiple choice technology web technology

Syntax for creating a SqlDataReader Object

  1. SqlDataReader rdr = cmd.ExecuteReader();

  2. SqlDataReader rdr = ExecuteReader();

  3. SqlDataReader rdr = cmd.Execute();

  4. SqlDataReader rdr = cmd.ExecuteScalar();
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SqlDataReader is created by calling ExecuteReader() on a SqlCommand object. Option A correctly shows this pattern with cmd.ExecuteReader(). Options B, C, and D are incorrect: B omits the command object, C uses Execute() which returns a boolean, D uses ExecuteScalar() which returns a single value, not a reader.

Multiple choice technology packaged enterprise solutions

What is TRUE for Standard Extension Database Column

  1. Type is Data (Private)

  2. Always prefixed with ‘X_’

  3. Provided by Siebel for Developers use

  4. Cannot be edited
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Standard Extension database columns in Siebel are custom columns that developers can add to extend base tables. They are always prefixed with 'X_' to distinguish them from standard Siebel columns. Option B is correct: they are prefixed with 'X_'. Option A is incorrect (type is not private data), Option C is misleading (they are for developer use but not provided by Siebel), Option D is incorrect (they can be edited).

Multiple choice technology databases

John has created a procedure named SALARY_CALC. Which SQL query allows him to view the text of the procedure?

  1. SELECT text FROM user_source WHERE name ='SALARY_CALC';

  2. SELECT * FROM user_source WHERE source_name ='salary_calc';

  3. SELECT * FROM user_objects WHERE object_name = 'SALARY_CALC';

  4. SELECT text FROM user_source WHERE name='SALARY_CALC' AND owner ='JOHN';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the structure of the system tables in Oracle database where all the objects are stored.

Option A is correct. This query will return the text of the procedure SALARY_CALC. The user_source view contains all the source code for the stored procedures, functions, and triggers in the current user's schema. By specifying the name of the procedure in the WHERE clause, the query filters the result set to only show the source code for the SALARY_CALC procedure.

Option B is incorrect. There is no column named "source_name" in the user_source view. Therefore, this query will return an error.

Option C is incorrect. The user_objects view contains information about all objects in the current user's schema, including tables, views, indexes, and procedures. However, the view does not contain the source code for the procedures. Therefore, this query will not return any rows.

Option D is incorrect. The condition "AND owner='JOHN'" is unnecessary because the user_source view only contains source code for the objects owned by the current user. Therefore, this query will return the same result as option A, but with an unnecessary condition.

The Answer is: A. SELECT text FROM user_source WHERE name ='SALARY_CALC';

Multiple choice technology databases

Which constraint can be defined only at column level?

  1. unique

  2. not null

  3. check

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

The NOT NULL constraint can only be defined at the column level because it restricts individual column values. Constraints like UNIQUE, CHECK, and PRIMARY KEY can be defined at either column or table level, but NOT NULL is inherently column-specific.