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 four are correct guidelines for naming database tables? (Choose four.)

  1. Must begin with either a number or a letter

  2. Must be 1-30 characters long

  3. Should not be an Oracle Server reserved word

  4. Must contain only A-Z, a-z, 0-9, _, *, and #
  5. Must contain only A-Z, a-z, 0-9, _, $, and #

  6. Must begin with a letter
Reveal answer Fill a bubble to check yourself
B,C,E,F Correct answer
Explanation

Oracle table names must be 1-30 characters (B), cannot be reserved words (C), contain only A-Z, a-z, 0-9, _, $, and # (E), and must begin with a letter (F). Option A is wrong because names cannot begin with numbers. Option D incorrectly includes * and omits $. These are standard Oracle naming conventions.

Multiple choice technology databases

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

  1. SELECT ename, salary*12 'Annual Salary' FROM employees;

  2. SELECT ename, salary*12 "Annual Salary" FROM employees;

  3. SELECT ename, salary*12 AS Annual Salary FROM employees;

  4. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL, column aliases containing spaces must be enclosed in double quotes. Option B correctly uses "Annual Salary" with double quotes. Option A uses single quotes (string literal syntax, not alias syntax). Option C's AS clause without quotes would fail due to the space. Option D unnecessarily uses INITCAP function and incorrect alias syntax.

Multiple choice technology databases

Which operator can be used with a multiple-row subquery?

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. Is

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

Multiple-row subqueries return multiple values, requiring operators that handle sets: IN, NOT IN, ANY, ALL. Single-row operators like =, <>, and LIKE cannot compare against multiple values. NOT IN correctly filters out rows matching any value in the subquery result set. BETWEEN is a range operator and 'Is' is not valid SQL.

Multiple choice technology databases

You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?

  1. SELECT last_name FROM EMP WHERE last_name LIKE '_A%';

  2. SELECT last_name FROM EMP WHERE last name ='*A%'

  3. SELECT last_name FROM EMP WHERE last name ='_A%';

  4. SELECT last_name FROM EMP WHERE last name LIKE '*A%'
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know about the SQL SELECT statement and the LIKE operator. The LIKE operator is used to match a character pattern in a string. The underscore (_) represents a single character, while the percent sign (%) represents any number of characters.

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

A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%'; This option is correct. It selects the last names of employees from the EMP table where the last name has any character as the first character and "A" as the second character, followed by any number of characters.

B. SELECT last_name FROM EMP WHERE last name ='A%' This option is incorrect because the equal sign (=) is used for exact matches, and the asterisk () is not a valid wildcard character in SQL.

C. SELECT last_name FROM EMP WHERE last name ='A%'; This option is incorrect because the equal sign (=) is used for exact matches, and the underscore () represents only a single character, not any number of characters.

D. SELECT last_name FROM EMP WHERE last name LIKE 'A%' This option is incorrect because the percent sign (%) is used to match any number of characters, not the asterisk ().

Therefore, the correct answer is:

The Answer is: A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%';

Multiple choice technology web technology

Which statement creates a new user?

  1. CREATE USER Susan;

  2. CREATE OR REPLACE USER Susan;

  3. CREATE NEW USER Susan DEFAULT;

  4. CREATE USER Susan IDENTIFIED BY blue;

  5. CREATE NEW USER Susan IDENTIFIED by blue;

  6. CREATE OR REPLACE USER Susan IDENTIFIED BY blue;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Option D shows the correct SQL syntax for creating a user with password authentication: CREATE USER username IDENTIFIED BY password. Options A and B are incomplete (no password). Options C, E, and F use invalid syntax (NEW/OR REPLACE don't apply to CREATE USER).

Multiple choice technology databases

Which clause should you use to exclude group results?

  1. WHERE

  2. HAVING

  3. RESTRICT

  4. GROUP BY

  5. ORDER BY
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

HAVING is used to filter groups after GROUP BY aggregation. WHERE filters individual rows before grouping. RESTRICT is not a valid SQL clause. GROUP BY creates groups, and ORDER BY sorts results. Only HAVING can exclude group results based on aggregate conditions.

Multiple choice technology databases

Which is an /SQL*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 proprietary command specific to the SQL*Plus environment used to view table structures, whereas SELECT, INSERT, UPDATE, DELETE, and RENAME are standard ANSI SQL statements.

Multiple choice technology databases

A subquery can be used to _________.

  1. create groups of data

  2. sort data in a specific order

  3. convert data to a different format

  4. Retrieve data based on an unknown condition
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To solve this question, the user needs to know the definition and purpose of a subquery.

A subquery is a query that is nested inside another query and returns data that will be used by the main query. It can be used to retrieve data based on an unknown condition and to filter data from a table.

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

A. create groups of data: This option is incorrect because creating groups of data is typically done using the GROUP BY clause, not a subquery.

B. sort data in a specific order: This option is incorrect because sorting data is typically done using the ORDER BY clause, not a subquery.

C. convert data to a different format: This option is incorrect because data conversion is typically done using functions like CAST or CONVERT, not a subquery.

D. Retrieve data based on an unknown condition: This option is correct. A subquery can be used to retrieve data based on an unknown condition, such as selecting all employees with salaries greater than the average salary.

Therefore, the answer is: D.

Multiple choice technology databases

What does the TRUNCATE statement do?

  1. Removes the table

  2. Removes all rows from a table

  3. shortens the table to 10 rows

  4. Removes all columns from a table

  5. Removes foreign keys from a table
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TRUNCATE removes all rows from a table quickly by deallocating data pages, unlike DELETE which logs individual row deletions. It does NOT remove the table structure (DROP does that), nor does it limit rows or remove columns/foreign keys. The table structure remains intact.

Multiple choice technology databases

Which two statements about views are true? (Choose two)

  1. A view can be created as read only

  2. A view can be created as a join on two or more tables.

  3. A view cannot have an ORDER BY clause in the SELECT statement

  4. A view cannot be created with a GROUP BY clause in the SELECT statement

  5. A view must have aliases defined for the column names in the SELECT statement
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Explanation: To answer this question, we need to understand the concept of views in the context of databases.

A. A view can be created as read-only - This statement is true. A view can be created with the SELECT statement that restricts the columns and rows that are returned. This can be used to ensure a user only has read access to certain data.

B. A view can be created as a join on two or more tables - This statement is true. A view can be created as a SELECT statement that combines data from two or more tables. This can be used to simplify queries by combining data that is frequently used together.

C. A view cannot have an ORDER BY clause in the SELECT statement - This statement is false. A view can have an ORDER BY clause in the SELECT statement to sort the data returned by the view.

D. A view cannot be created with a GROUP BY clause in the SELECT statement - This statement is false. A view can be created with a GROUP BY clause in the SELECT statement to group the data returned by the view.

E. A view must have aliases defined for the column names in the SELECT statement - This statement is false. A view does not require aliases for the column names in the SELECT statement, although it can be useful to provide more meaningful names for the columns.

Therefore, options A and B are both true statements, making the correct answer B.

Multiple choice technology databases

Management has asked you to calculate the value 12*salary*commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCHAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISSION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated column for all employees?

  1. SELECT last_name, 12*salary*commission_pct FROM emp;

  2. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;

  3. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp

  4. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Since commission_pct can contain null values, any direct multiplication would result in null. Using NVL(commission_pct, 0) replaces any null value with 0, ensuring a valid numeric result is calculated and displayed for all rows.

Multiple choice technology databases

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

COUNT works on any data type including dates - it counts non-null values. MIN and MAX can compare dates chronologically. SUM and AVG don't make sense on dates (can't add or average dates). MAXIMUM (F) is invalid - the correct function is MAX. Option D's AVG with two parameters is also invalid syntax.

Multiple choice technology databases

Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks; What is the result of the SELECT statement?

  1. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.

  2. The statement returns an error at the SELECT clause.

  3. The statement returns an error at the WHERE clause.

  4. The statement returns an error at the ORDER BY clause.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The SQL parser returns an error at the WHERE clause because group/aggregate functions like AVG cannot be used directly inside a WHERE clause; instead, a subquery or a HAVING clause must be used.

Multiple choice technology databases

Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)
  1. SELECT TO_CHAR(2000, '$#,###.##') FROM dual;
  2. SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
  3. SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
  4. SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
  5. SELECT TO_CHAR(2000, '$2,000.00') FROM dual;
  6. SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

TO_CHAR format '\$0,000.00' forces 4-digit with cents: \$2,000.00. '\$9,999.00' does the same (9 means optional digit, 0 means required). '\$9,999.99' would also display $2,000.00 (the 99 format doesn't force extra cents if not present). Options A, E, F are invalid: E is literal, F uses N instead of 9, A uses # instead of 9.