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

Which SQL function lets you substitute a value when a null value is returned from from a row ?

  1. NVL

  2. COUNT

  3. CASE

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

NVL (or COALESCE in standard SQL) substitutes a replacement value when NULL is encountered. NVL(expr, replacement) returns 'replacement' if 'expr' is NULL, otherwise returns 'expr'. COUNT counts rows, CASE is conditional logic, and MAX finds maximum values - none of these handle NULL substitution.

Multiple choice technology

For tables joined on the same distribution key as in the JOIN clause, Distribution is not required

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When tables share the same distribution key and are joined on that key, matching rows are already on the same SPU due to hash distribution. This collocated join eliminates the need for data redistribution across SPUs, making the join highly efficient. It's a key performance optimization in MPP databases like Netezza that avoids expensive data movement.

Multiple choice technology

Data cannot be inserted to External tables

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

External tables can accept data insertion in many database systems. For example, in Hive/Impala, you can insert data into external tables, though dropping the table won't delete the underlying data files. The statement claims data cannot be inserted, which is incorrect.

Multiple choice technology

Data cannot be deleted or truncated from an External table

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

External tables in most systems (including Netezza and Hive) do not support direct DELETE or TRUNCATE operations because they map to external files. You can't delete data from an external table without modifying the underlying file system directly.

Multiple choice technology databases

What does SQL stand for?

  1. Structured Question Language

  2. Structured Query Language

  3. Strong Question Language

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

SQL stands for Structured Query Language, which is the standard language for relational database management systems. It is used for querying, manipulating, and defining data in databases.

Multiple choice technology databases

Select the DDL Statement from below:

  1. SELECT

  2. UPDATE

  3. DELETE

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

DDL (Data Definition Language) statements define database structures. ALTER is a DDL command used to modify table structure. SELECT, UPDATE, and DELETE are DML (Data Manipulation Language) commands that operate on data within existing structures.

Multiple choice technology databases

SQL is case sensitive

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

SQL keywords are case-insensitive in most database systems. SELECT, Select, and select are all treated identically. However, string literals and identifier naming (depending on system) may be case-sensitive.

Multiple choice technology databases

The SQL statement used to delete a table:

  1. DROP

  2. DELETE

  3. TRUNCATE

  4. All the Above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The statement asks which SQL statement deletes a TABLE (not data). DROP TABLE completely removes the table structure and data from the database. DELETE removes rows but keeps the table, TRUNCATE removes all rows but keeps the table structure. Only DROP removes the table itself.

Multiple choice technology databases

The below Query is to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table. SELECT * FROM Persons WHERE FirstName LIKE '%la%'

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The query uses LIKE '%la%' which matches any string containing 'la' anywhere (startswith, middle, or end). To match names starting with any character followed by 'la' (like 'Clara', 'Flavia'), the correct pattern would be '_la%' where underscore matches exactly one character. '%la%' matches 'Flamingo' (contains 'la'), not just patterns like '_la'.

Multiple choice technology databases

The Correct Statement to select a column named "LastName" from a table named "Employee".

  1. SELECT LastName FROM Employee

  2. EXTRACT LastName FROM Employee

  3. SELECT Employee.LastName

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

The SELECT statement is the standard SQL command for retrieving data from a database. The syntax 'SELECT column_name FROM table_name' correctly specifies which column to retrieve from which table. EXTRACT is not a valid SQL keyword for column retrieval, and 'SELECT Employee.LastName' would only work if using qualified notation with a table alias, which is not shown in the options.

Multiple choice technology databases

The Correct Statement to select all the columns from a table named "Employee".

  1. SELECT *.Employee

  2. SELECT [all] FROM Employee

  3. SELECT * FROM Employee

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

The asterisk () in SQL acts as a wildcard that represents all columns in a table. 'SELECT * FROM table_name' is the standard syntax to retrieve every column from the specified table. Options A and B use incorrect syntax ('.Employee' places the wildcard incorrectly, and '[all]' is not valid SQL syntax). Option D is missing both the column specification and FROM clause.

Multiple choice technology

There is a table called Employee having columns are empno, ename , job , deptno,grade and salary. Which of the following is a correct query to change all emp salary with salary<10000 to 1.5 times and grade to ‘Promo’

  1. Update Employee set salary = salary * 1.5 and grade=’Promo’ where salary < 10000;

  2. Update Employee set salary = salary * 1.5, grade=’Promo’ where salary < 10000;

  3. Update table Employee set salary = salary * 1.5, grade=’Promo’ where salary < 10000;

  4. Update table Employee set salary = salary * 1.5 and grade=’Promo’ where salary < 10000;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SQL UPDATE statement uses comma-separated assignments to update multiple columns, not the AND keyword. Option B correctly updates both salary (multiplied by 1.5) and grade (set to 'Promo') for rows where salary is less than 10000. Options A and D incorrectly use AND between column assignments, and option C unnecessarily includes the TABLE keyword which is not valid SQL syntax.

Multiple choice technology

Which of the following is Data Definition Language?

  1. INSERT

  2. DELETE

  3. UPDATE

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

Data Definition Language (DDL) commands define database structures like tables, indexes, and schemas. CREATE is a DDL command used to create database objects. INSERT, DELETE, and UPDATE are Data Manipulation Language (DML) commands that manipulate data within existing structures.