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

What is the ABS SQL function used for?

  1. To return the absolute, positive value of a numeric expression.

  2. To return the average value of a numeric expression.

  3. To return the maximum value of a numeric expression.

  4. To return the minimum value of a numeric expression.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ABS() function in SQL returns the absolute (non-negative) value of a numeric expression. For example, ABS(-15) returns 15, and ABS(15) returns 15. It's commonly used for distance calculations or normalizing data.

Multiple choice technology databases

Which SQL keyword is used to retrieve only unique values?

  1. DISTINCTIVE

  2. UNIQUE

  3. DISTINCT

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

DISTINCT is used in SELECT statements to eliminate duplicate rows from the result set, returning only unique values. For example, SELECT DISTINCT city FROM customers returns a list of unique cities. UNIQUE is a constraint, not a query keyword.

Multiple choice technology databases

Which of the following is not a SQL keyword or SQL clause?

  1. UPDATE

  2. SELECT

  3. INSERT

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

To solve this question, the user needs to have basic knowledge of SQL keywords and clauses. SQL (Structured Query Language) is a standard language for managing relational databases. It is important to know which words and clauses are part of SQL and which are not.

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

A. UPDATE: This is a SQL keyword that is used to modify existing records in a table.

B. SELECT: This is a SQL keyword that is used to query data from a table.

C. INSERT: This is a SQL keyword that is used to insert new records into a table.

D. INVERT: This is not a SQL keyword or SQL clause. It is not used in SQL to perform any operation.

Therefore, the answer is: D. INVERT.

Multiple choice technology databases

There are triggers for…

  1. insert and delete only

  2. update and delete only

  3. update, delete and insert

  4. insert and update only
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SQL triggers can be defined for all three data manipulation operations: INSERT, UPDATE, and DELETE. These triggers execute automatically before or after the specified operation, allowing for validation, auditing, or automated data maintenance.

Multiple choice technology databases

Which SQL statement selects all rows from a table called Products and orders the result set by ProductID column?

  1. SELECT * FROM Products ORDER BY ProductID

  2. SELECT * FROM Products WHERE ProductID > 200

  3. SELECT * FROM Products ORDERED BY ProductID

  4. SELECT ProductID FROM Products
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A correctly uses SELECT * to retrieve all columns and ORDER BY to sort by ProductID. Option C uses ORDERED BY which is invalid SQL syntax. Option B filters by WHERE condition, and Option D only selects the ProductID column.

Multiple choice technology databases

What does the following SQL statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order) >5

  1. Selects all Customers from the Sales table

  2. Selects all customers from table Sales that have made more than 5 orders.

  3. Selects the total number of orders from the Sales table, if this number is greater than 5

  4. Selects no records
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The HAVING clause filters groups after the GROUP BY aggregation. This query first groups sales records by customer, counts orders per customer, then returns only those customers whose order count exceeds 5. Option A misses the HAVING filter. Option C incorrectly describes the aggregation scope. Option D is false because the query will return records for customers meeting the condition.

Multiple choice technology databases

What is a foreign key?

  1. The foreign key is a column that can have NULL values.

  2. The foreign key is a SQL locking mechanism.

  3. A foreign key is a key field (column) in a database table, which relates the table to another table where the key is a primary key. The primary - foreign key relations are used to cross-reference database tables.

  4. The foreign key is a column that should be primary key for that table.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To understand what a foreign key is, the user needs to have a basic understanding of database design.

The correct answer is:

C. A foreign key is a key field (column) in a database table, which relates the table to another table where the key is a primary key. The primary-foreign key relations are used to cross-reference database tables.

Explanation of options:

A. This option is incorrect because a foreign key cannot have NULL values. A foreign key must reference a valid primary key in another table.

B. This option is incorrect because a foreign key is not related to SQL locking mechanisms. SQL locking mechanisms are used to manage concurrent access to data.

D. This option is incorrect because a foreign key cannot be a primary key for that table. A primary key is a unique identifier for a table, while a foreign key is used to reference a primary key in another table.

Multiple choice technology databases

Which SQL statement inserts data into a table called Projects?

  1. INSERT Projects VALUES ('Content Development', 'Website content development project')

  2. INSERT Projects ('Content Development', 'Website content development project')

  3. INSERT INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')

  4. SAVE INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

INSERT INTO is the correct SQL syntax for adding records. Option C specifies the target table (Projects), lists columns to receive values (ProjectName, ProjectDescription), then provides the corresponding data in the VALUES clause. Options A and B omit required INTO keyword or column specification. Option D uses non-existent SAVE command.

Multiple choice technology databases

Which of the following SQL statements is correct?

  1. SELECT FROM Sales WHERE Date BETWEEN ('10/12/2005', '01/01/2006')

  2. SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'

  3. SELECT FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'

  4. SELECT * FROM Sales WHERE Date BETWEEN ('10/12/2005', '01/01/2006')
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The proper SQL BETWEEN syntax uses BETWEEN value1 AND value2 with a single column name and an asterisk to select all columns. Hence SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006' is correct; the other forms misuse parentheses or omit the asterisk.

Multiple choice technology databases

If you join a table to itself, what kind of join are you using?

  1. Self Join

  2. Selective Join.

  3. Outer Join

  4. You can't join a table to itself.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A self-join occurs when a table is joined to itself, typically to compare rows within the same table or establish hierarchical relationships. The table appears in both the FROM and JOIN clauses, often with different aliases. Option B's 'Selective Join' is not standard SQL terminology. Option C (Outer Join) preserves unmatched rows but isn't specific to self-joins.

Multiple choice technology databases

Which of the following is a reserved SQL keyword?

  1. GROWN

  2. FROM

  3. UPDATING

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

To solve this question, the user needs to know what a reserved SQL keyword is. A reserved SQL keyword is a word that has a special meaning in SQL and cannot be used as a name for a table, column, or other database object.

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

A. GROWN: This option is incorrect because "GROWN" is not a reserved SQL keyword. It is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

B. FROM: This option is correct. "FROM" is a reserved SQL keyword that is used to specify the table or tables from which to retrieve data in a SELECT statement. It is a commonly used SQL keyword and cannot be used as a name for a table, column, or other database object.

C. UPDATING: This option is incorrect because "UPDATING" is not a reserved SQL keyword. While "UPDATE" is a reserved SQL keyword that is used to modify data in a table, "UPDATING" is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

D. DELETING: This option is incorrect because "DELETING" is not a reserved SQL keyword. While "DELETE" is a reserved SQL keyword that is used to remove rows from a table, "DELETING" is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

The Answer is: B

Multiple choice technology programming languages

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384';

  2. select * from EMP where EMPNO = '59384';

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384';

  4. select 1 from EMP where EMPNO = '59834';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct answer is A. When a column is wrapped in a function like NVL(EMPNO, '00000'), the database cannot use the index on EMPNO directly because the indexed value is transformed. This is a classic example of a function preventing index usage. Options B, C, and D all use EMPNO = 'value' with no function wrapping, so they would use the index (note: C and D have typos in values but would still use indexes).

Multiple choice technology programming languages

Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

  1. 03-JUL-00

  2. 10-JUL-00

  3. 11-JUL-00

  4. 17-JUL-00
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

NEXT_DAY(sysdate, 'MONDAY') finds the first Monday strictly after the current date. Since today is Monday, July 10, 2000, the next Monday is July 17, 2000. The to_char format 'DD-MON-RR' produces 17-JUL-00. Note that NEXT_DAY finds the NEXT occurrence, not the current day - if today is Monday, it returns the following Monday.

Multiple choice technology programming languages

How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

  1. 0

  2. 1

  3. 3

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

The SELECT concatenates three fields into a single expression and assigns it the alias "Adress". This yields one column in the result set. Options claiming 0, 2, or 3 columns are wrong because the concatenation produces exactly one output column.