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

Which of the following can be a valid column name?

  1. Column

  2. 1966_Invoices

  3. Catch_#22

  4. #Invoices

  5. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SQL, identifiers starting with numbers or containing special characters like '#' are generally invalid unless quoted. However, 'Catch_#22' is often permitted in specific SQL dialects, while 'Column' is a reserved word and '#Invoices' is often used for temporary tables.

Multiple choice technology databases

A trigger is…

  1. a special type of store procedure, executed when certain event occurs

  2. a special type of table

  3. a special type of view

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

A database trigger is a special type of stored procedure that automatically executes or fires when a specific database event occurs, such as an insert, update, or delete. It is not a table or a view.

Multiple choice technology databases

We refer to a join as a self-join when…

  1. we are joining more than 2 tables

  2. we are joining table to itself

  3. we are using left and right join together

  4. none of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A self-join occurs when a table is joined to itself, which is commonly used in hierarchical data structures like organizational charts or category trees. Joining multiple different tables or combining left and right joins does not define a self-join.

Multiple choice technology databases

What does the HAVING clause do?

  1. The HAVING keyword specifies a search condition for an aggregate or a group

  2. The HAVING keyword is used to select distinct values

  3. The HAVING keyword is used to join 2 or more tables.

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

The HAVING clause in SQL is used to specify search conditions for groups or aggregate functions, typically following a GROUP BY clause. The WHERE clause filters individual rows, whereas HAVING filters aggregated results.

Multiple choice technology databases

What does follow after the SQL SELECT clause?

  1. The JOIN SQL clause.

  2. List of columns that will be selected or the * symbol.

  3. The name of the table we are selecting from.

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

In a standard SQL query, the SELECT keyword is immediately followed by the list of columns to be retrieved or the asterisk symbol representing all columns. The table name comes later after the FROM clause.

Multiple choice technology databases

What is an index?

  1. An index is the same as alias.

  2. An index is a special way to join 2 or more tables.

  3. An index is a database table attribute, which speeds-up data search within a table.

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

An index is a database structure associated with a table attribute that improves the speed of data retrieval operations. It does not replace aliases or define table joins.

Multiple choice technology databases

What is a view?

  1. A view is a special stored procedure executed when certain event occurs.

  2. A view is a database diagram.

  3. A view is a virtual table which results of executing a pre-compiled query. A view is not part of the physical database schema, while the regular tables are

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

A view is a virtual table in a database whose contents are defined by a stored query. It does not store physical data itself in the schema like regular tables do, but instead runs its underlying query when accessed.

Multiple choice technology web technology

The JOIN is a SQL keyword used to ...

  1. delete data from database table.

  2. select data from 2 or more tables related by common attribute (table column).

  3. verify that the inserted data is correct.

  4. update database table.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The JOIN keyword in SQL is used to combine and select rows from two or more database tables based on a related column between them. Data deletion and updates are handled by DELETE and UPDATE statements respectively.

Multiple choice technology databases

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

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

  2. MODIFY Persons SET LastName='Hansen' WHERE LastName='Nilsen'

  3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

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

To change "Hansen" into "Nilsen" in the "LastName" column in the Persons table, the user needs to use SQL UPDATE statement.

UPDATE statement is used to modify the existing records in a table.

Now, let's go through each option:

A. UPDATE Persons SET LastName='Hansen' WHERE LastName='Nilsen': This option is incorrect because it will change the LastName to "Hansen" where LastName is "Nilsen". This will not change "Hansen" to "Nilsen".

B. MODIFY Persons SET LastName='Hansen' WHERE LastName='Nilsen': This option is incorrect because MODIFY is not a valid keyword in SQL. Also, this statement will change LastName to "Hansen" where LastName is "Nilsen". This will not change "Hansen" to "Nilsen".

C. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen': This option is correct because it will change LastName to "Nilsen" where LastName is "Hansen". This will change "Hansen" into "Nilsen" in the LastName column.

D. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen': This option is incorrect because MODIFY is not a valid keyword in SQL. Also, this statement will change LastName to "Nilsen" where LastName is "Hansen". This will change "Hansen" into "Nilsen" in the LastName column.

The Answer is: C

Multiple choice technology databases

how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"

  1. SELECT * FROM Persons WHERE FirstName='%a%'

  2. SELECT * FROM Persons WHERE FirstName='a'

  3. SELECT * FROM Persons WHERE FirstName LIKE '%a'

  4. SELECT * FROM Persons WHERE FirstName LIKE 'a%'
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The SQL LIKE operator with wildcards is used for pattern matching. The '%' wildcard matches any sequence of characters. To match names starting with 'a', use 'a%' where 'a' matches the first character and '%' matches any following characters. Option D correctly uses: 'SELECT * FROM Persons WHERE FirstName LIKE 'a%'. Option A ('%a%') matches names containing 'a' anywhere. Option B uses exact equality instead of pattern matching. Option C ('%a') matches names ending with 'a'.

Multiple choice technology databases

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',LastName='Jackson'

  2. SELECT * FROM Persons WHERE FirstName='Peter' OR LastName='Jackson'

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

  4. SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To filter results based on multiple criteria that must all be met, the SQL AND operator is used. Using a comma in a WHERE clause is syntactically incorrect, and the OR operator would return records matching either name, not necessarily both.

Multiple choice technology databases

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

  1. SELECT * FROM Persons ORDER BY FirstName DESC

  2. SELECT * FROM Persons SORT BY 'FirstName' DESC

  3. SELECT * FROM Persons SORT 'FirstName' DESC

  4. SELECT * FROM Persons ORDER FirstName DESC
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To sort results in SQL, use the ORDER BY clause followed by ASC (ascending) or DESC (descending). For descending order by FirstName, the correct syntax is 'ORDER BY FirstName DESC'. Option A correctly uses: 'SELECT * FROM Persons ORDER BY FirstName DESC'. Options B and C incorrectly use 'SORT' instead of 'ORDER BY'. Option D is missing the 'BY' keyword after ORDER.

Multiple choice technology databases

Which SQL statement is used to return only different values

  1. SELECT DIFFERENT

  2. SELECT DISTINCT

  3. SELECT COUNT

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

SELECT DISTINCT returns only unique (different) values from the specified columns, eliminating duplicate rows. SELECT DIFFERENT and SELECT UNIQUE are not valid SQL syntax. SELECT COUNT returns the count of rows, not distinct values.