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

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHAR2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two.)

  1. SUM(start_date)

  2. AVG(start_date)

  3. COUNT(start_date)

  4. AVG(start_date, end_date)

  5. MIN(start_date)

  6. MAXIMUM(start_date)
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

For DATE columns in Oracle, only COUNT, MIN, and MAX are valid aggregate functions because they work with ordered data and counting. SUM and AVG require numeric values to calculate totals and averages, which cannot be performed on dates. MAXIMUM (option F) is incorrect Oracle syntax - it must be MAX.

Multiple choice softskills teamwork

You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

  1. SELECT TO_DATE (SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;

  2. SELECT TO_CHAR (SYSDATE, 'FMDD, DY Month, YYYY') FROM dual;

  3. SELECT TO_CHAR (SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

  4. SELECT TO_CHAR (SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

  5. SELECT TO_DATE (SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

TO_CHAR is used to format dates into strings. 'FMDay' ensures the day is capitalized and removes extra padding, while 'DD Month, YYYY' provides the day number, full month name, and year. TO_DATE is incorrect because it converts a string to a date, not a date to a formatted string.

Multiple choice softskills teamwork

Which four are valid Oracle constraint types? (Choose four.)

  1. CASCADE

  2. UNIQUE

  3. NONUNIQUE

  4. CHECK

  5. PRIMARY KEY

  6. NOT NULL
Reveal answer Fill a bubble to check yourself
B,D,E,F Correct answer
Explanation

Oracle constraint types are PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL. Options B, D, E, and F are all valid constraint types that enforce data integrity rules. CASCADE is a referential action, not a constraint type itself. NONUNIQUE is not a standard Oracle constraint.

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.