Computer Knowledge

Database and SQL

3,929 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

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

  1. SELECT * FROM Persons SORT 'FirstName' DESC ;

  2. SELECT * FROM Persons ORDER BY FirstName DESC;

  3. SELECT * FROM Persons ORDER FirstName DESC ;

  4. SELECT * FROM Persons SORT BY 'FirstName' DESC ;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL, sorting is performed using the ORDER BY clause, and descending order is specified with the DESC keyword. Therefore, ORDER BY FirstName DESC is correct. The other options use non-existent or invalid SQL keywords like SORT or omit the required BY keyword.

Multiple choice technology databases

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

  1. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen';

  2. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen ;

  3. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen' ;

  4. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen' ;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

UPDATE table SET column=value WHERE condition is the standard SQL syntax. MODIFY and INTO are not valid keywords in UPDATE statements.

Multiple choice technology databases

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

  1. DELETE FROM Persons WHERE FirstName = 'Peter' ;

  2. DELETE FirstName='Peter' FROM Persons;

  3. DELETE ROW FirstName='Peter' FROM Persons ;

  4. TRUNCATE FROM Persons WHERE FirstName='Peter'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the basic syntax for deleting records in SQL, including the correct use of the DELETE command and WHERE clause.

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

A. DELETE FROM Persons WHERE FirstName = 'Peter' ; This option is correct. The DELETE command is used to delete records from a table in SQL. The WHERE clause is used to specify the condition that must be met for a record to be deleted. In this case, we want to delete all records where the FirstName is Peter, so we use the WHERE FirstName = 'Peter' condition.

B. DELETE FirstName='Peter' FROM Persons; This option is incorrect. The correct syntax for the DELETE command is DELETE FROM table_name, followed by the WHERE clause. In this option, the syntax is incorrect because the table name is missing, and the condition is in the wrong place.

C. DELETE ROW FirstName='Peter' FROM Persons ; This option is incorrect. The correct syntax for the DELETE command is DELETE FROM table_name, followed by the WHERE clause. In this option, the syntax is incorrect because the ROW keyword is not used in the correct way.

D. TRUNCATE FROM Persons WHERE FirstName='Peter' This option is incorrect. The TRUNCATE command is used to delete all records from a table, not specific records that meet a certain condition. Also, the syntax is incorrect because the WHERE clause is used in the wrong place.

The Answer is: A. DELETE FROM Persons WHERE FirstName = 'Peter' ;

Multiple choice technology databases

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

  1. SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson' ;

  2. SELECT * FROM Persons WHERE FirstName LIKE 'Peter' AND LastName LIKE 'Jackson';

  3. SELECT FirstName='Peter', LastName='Jackson' FROM Persons

  4. SELECT FirstName LIKE 'Peter', LastName LIKE 'Jackson' FROM Persons
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Use WHERE with AND to combine multiple exact match conditions. LIKE is for pattern matching, not exact values. Option C incorrectly puts conditions in the SELECT clause.

Multiple choice technology databases

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

  1. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen' ;

  2. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' ;

  3. SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons ;

  4. SELECT LastName LIKE'%Hansen%' AND LastName LIKE '%Pettersen%' FROM Persons ;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

BETWEEN is the correct operator for range selection and is inclusive of both endpoints. The > and < operators in option A would exclude the boundary values.

Multiple choice technology databases

With SQL, how can you insert a new record into the "Persons" table?

  1. INSERT ('Jimmy', 'Jackson') INTO Persons ;

  2. INSERT VALUES ('Jimmy', 'Jackson') INTO Persons ;

  3. INSERT INTO Persons VALUES ('Jimmy', 'Jackson') ;

  4. INSERT VALUES ('Jimmy', 'Jackson') INTO Table Persons ;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

INSERT INTO table VALUES (...) is the correct syntax. Options A and B have incorrect word order, and D incorrectly adds 'Table' keyword.

Multiple choice technology databases

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

  1. INSERT INTO Persons ('Olsen') INTO LastName ;

  2. INSERT INTO Persons (LastName) VALUES ('Olsen');

  3. INSERT ('Olsen') INTO Persons (LastName) ;

  4. INSERT INTO Table Persons (LastName) VALUES ('Olsen');
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The standard SQL syntax for inserting a value into a specific column is INSERT INTO table_name (column_name) VALUES (value);. The correct option matches this syntax perfectly. The other options violate standard SQL syntax constraints by using incorrect keywords or incorrect placement of values.

Multiple choice technology databases

What does SQL stand for?

  1. Structured Query Language

  2. Strong Question Language

  3. Structured Question Language

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

SQL stands for Structured Query Language, the standard language for relational database management systems.

Multiple choice technology databases

Which of the following does not hold true for the subqueries?

  1. Can be the object of an IN or NOT IN clause

  2. Can be the object of EXISTS or NOT EXISTS

  3. Support quantifiers ALL, ANY, SOME

  4. Support LIKE or NOT LIKE used with a

  5. Cannot specify more than one column to match
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The statement "Cannot specify more than one column to match" does not hold true generally, as subqueries can return multiple columns when used with operators like EXISTS or in column comparison lists (e.g., (A, B) IN (SELECT X, Y...)). Other options describe correct standard behaviors of subqueries.

Multiple choice technology databases

Which of the following is not a DDL statement?

  1. CREATE

  2. DROP

  3. MODIFY

  4. ALTER

  5. GRANT

  6. REPLACE
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

DDL (Data Definition Language) statements define database structures - these include CREATE, ALTER, DROP, TRUNCATE, and RENAME. GRANT is a DCL (Data Control Language) statement used to give user permissions on database objects, not to define database structures. Since the question asks which is NOT a DDL statement, GRANT is the correct answer.