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 softskills teamwork

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

  1. DROP emp_dept_vu;

  2. DELETE emp_dept_vu;

  3. REMOVE emp_dept_vu;

  4. DROP VIEW emp_dept_vu;

  5. DELETE VIEW emp_dept_vu;

  6. REMOVE VIEW emp_dept_vu;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The DROP VIEW statement removes a view from the schema by deleting its definition from the data dictionary. DELETE removes data from tables, not schema objects. REMOVE is not a valid SQL command. The syntax requires both DROP VIEW and the view name.

Multiple choice softskills teamwork

Which is an iSQL*Plus command?

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DESCRIBE is a command used in iSQL*Plus and SQL*Plus to display the structure of a table. INSERT, UPDATE, SELECT, DELETE, and RENAME are standard SQL (DML/DDL) statements, not specific environment commands like DESCRIBE, SET, or SPOOL.

Multiple choice softskills teamwork

Which are DML statements? (Choose all that apply.)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

DML (Data Manipulation Language) statements manipulate data in tables: MERGE combines insert/update/delete operations, UPDATE modifies existing data, DELETE removes rows. CREATE, DROP, and COMMIT are DDL and TCL commands respectively. COMMIT is transaction control, not data manipulation.

Multiple choice softskills teamwork

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

  1. ALTER TABLE students ADD PRIMARY KEY student_id;

  2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

  3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

  4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY

  5. ALTER TABLE studentsMODIFY CONSTRAINT stud_id_pk PRIMARY KEY
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D shows correct syntax to add a named primary key constraint to an existing table. The ADD CONSTRAINT clause requires a constraint name (stud_id_pk), the constraint type (PRIMARY KEY), and the column in parentheses. Option A is missing CONSTRAINT keyword and parentheses. Option B is missing the constraint name. Option C has wrong column syntax (no parentheses). Option E uses MODIFY incorrectly.

Multiple choice softskills teamwork

Which two statements complete a transaction? (Choose two.)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. GRANT SELECT ON employees TO SCOTT;

  5. ALTER TABLE employeesSET UNUSED COLUMN sal;

  6. SELECT MAX(sal) FROM employees WHERE department_id = 20;
Reveal answer Fill a bubble to check yourself
D,E Correct answer
Explanation

In Oracle SQL, Data Definition Language (DDL) and Data Control Language (DCL) statements like ALTER and GRANT issue an implicit COMMIT, thereby completing the current transaction. DELETE and SELECT are DML/Query statements that do not end a transaction, and ROLLBACK TO SAVEPOINT only partially reverts it.

Multiple choice technology databases

With SQL, how do you select a column named 'FirstName' from a table named 'Persons'?

  1. SELECT FirstName FROM Persons

  2. EXTRACT FirstName FROM Persons

  3. SELECT Persons.FirstName

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

The SELECT statement followed by the column name and the FROM clause with the table name is the standard SQL syntax to retrieve specific data. 'SELECT FirstName FROM Persons' correctly targets the desired column and table.

Multiple choice technology databases

With SQL, how do you select all the columns from a table named 'Persons'?

  1. SELECT [all] FROM Persons

  2. SELECT * FROM Persons

  3. SELECT *.Persons

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

To select all the columns from a table named 'Persons' using SQL, the user needs to use the following query:

B. SELECT * FROM Persons

Option A is incorrect because the SQL keyword for selecting all columns is not 'all,' but rather '*'.

Option C is incorrect because the '*' should come before the table name, and not after it.

Option D is incorrect because 'Persons' alone does not specify which columns to select from the table.

Therefore, the correct answer is option B.

Multiple choice technology databases

With SQL, how do you select all the records from a table named 'Persons' where the value of the column 'FirstName' is 'Peter'?

  1. SELECT * FROM Persons WHERE FirstName <> 'Peter'

  2. SELECT [all] FROM Persons WHERE FirstName='Peter'

  3. SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

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

To select all the records from the table named 'Persons' where the value of the column 'FirstName' is 'Peter', the correct SQL query is:

D. SELECT * FROM Persons WHERE FirstName='Peter'

Let's go through each option and explain why the other options are incorrect:

A. SELECT * FROM Persons WHERE FirstName <> 'Peter' This query selects all records where the 'FirstName' column is not equal to 'Peter'. It is the opposite of what we want, so this option is incorrect.

B. SELECT [all] FROM Persons WHERE FirstName='Peter' The use of '[all]' is not necessary in this query. It should be replaced with '*' to select all columns. Additionally, the operator '=' should be used instead of 'LIKE' since we are looking for an exact match. Therefore, this option is incorrect.

C. SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter' The 'LIKE' operator is used for pattern matching and allows the use of wildcards (% or _). Since we are looking for an exact match of 'Peter', the '=' operator should be used instead. Therefore, this option is incorrect.

D. SELECT * FROM Persons WHERE FirstName='Peter' This option correctly selects all records from the 'Persons' table where the 'FirstName' column is equal to 'Peter'.

So, the correct answer is: D. SELECT * 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 value of the column 'FirstName' starts with an 'a'?

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

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

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

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

The LIKE operator in SQL uses the '%' wildcard to represent zero or more characters. To find a value starting with 'a', the pattern 'a%' is used. '%a' would find values ending with 'a', and '%a%' would find 'a' anywhere in the string.

Multiple choice technology databases

Which SQL statement is used to return only different values?

  1. SELECT DISTINCT

  2. SELECT DIFFERENT

  3. SELECT UNIQUE

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

SELECT DISTINCT eliminates duplicate rows from the result set, returning only unique values. The other options (DIFFERENT, UNIQUE, ALL) are not valid SQL keywords for this purpose - SELECT UNIQUE exists in Oracle but is deprecated, while SELECT DIFFERENT is not standard SQL.

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 ORDER FirstName DESC

  2. SELECT * FROM Persons ORDER BY FirstName DESC

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

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

To solve this question, the user needs to know SQL syntax and the concept of sorting query results in a descending order based on a specific column.

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

A. SELECT * FROM Persons ORDER FirstName DESC This option is incorrect because the correct syntax for sorting query results in SQL requires the use of the "ORDER BY" clause followed by the column name to sort by, and then the keyword "DESC" to specify descending order. Therefore, the correct syntax should be "ORDER BY FirstName DESC" instead of "ORDER FirstName DESC".

B. SELECT * FROM Persons ORDER BY FirstName DESC This option is correct. The "ORDER BY" clause is used to sort the query results based on a specific column. In this case, we want to sort the results in descending order based on the "FirstName" column. Therefore, the correct syntax is "ORDER BY FirstName DESC".

C. SELECT * FROM Persons SORT BY 'FirstName' DESC This option is incorrect because there is no "SORT" keyword in SQL. The correct keyword to use is "ORDER BY". Additionally, the column name should not be enclosed in single quotes. Therefore, the correct syntax should be "ORDER BY FirstName DESC" instead of "SORT BY 'FirstName' DESC".

D. SELECT * FROM Persons SORT 'FirstName' DESC This option is incorrect because the correct keyword to use for sorting query results in SQL is "ORDER BY" rather than "SORT". Additionally, the column name should not be enclosed in single quotes. Therefore, the correct syntax should be "ORDER BY FirstName DESC" instead of "SORT 'FirstName' DESC".

The Answer is: B

Multiple choice technology programming languages

Which SQL statement selects all rows from table called Contest, with column ContestDate having values greater or equal to May 25, 20006?

  1. SELECT * FROM Contest WHERE ContestDate < '05/25/2006'

  2. SELECT * FROM Contest HAVING ContestDate >= '05/25/2006'

  3. SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'

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

SELECT with WHERE clause filters rows before returning them. Option C correctly uses WHERE with >= operator. Option B incorrectly uses HAVING (for aggregate filtering), and Option A has the wrong operator direction.