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 technology programming languages

What is the effect when a clear statement is used on an internal table without header line?

  1. All the lines of the table are deleted

  2. The work area is initialized

  3. All the lines of the table are initialized

  4. Nothing
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When CLEAR is used on an internal table without a header line, it initializes all lines of the table body to their initial values. This is different from tables with header lines, where CLEAR might only initialize the header work area. Option C correctly states that all lines are initialized.

Multiple choice technology programming languages

Examine the following PL/SQL block of code: ... DELETE FROM orders WHERE pay_stat = ‘Overdue’; IF SQL%ROWCOUNT > 5 THEN RAISE out_of_bounds; END IF; ... What value is returned after the statement is run and the DELETE statement returned no rows?

  1. 0

  2. 1

  3. 5

  4. true

  5. false
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SQL%ROWCOUNT attribute returns the number of rows affected by the last SQL statement. If the DELETE statement did not find any matching rows to delete, SQL%ROWCOUNT returns 0.

Multiple choice technology programming languages

You run the following SQL statement: SQL> SELECT lname, deptid, sal, bonus FROM emp WHERE sal, bonus IN (SELECT sal, bonus FROM emp WHERE deptid=20); The statement resulted in an error. Which line is causing the error?

  1. sql>

  2. from emp

  3. WHERE sal, bonus IN

  4. WHERE deptid=20);
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The WHERE clause 'WHERE sal, bonus IN' is syntactically incorrect. When using multiple columns with the IN operator in SQL, you must use row value constructor syntax like 'WHERE (sal, bonus) IN (SELECT sal, bonus FROM...)'. The comma-separated form without parentheses is not valid SQL syntax.

Multiple choice technology programming languages

What must be included within a cursor when the WHERE CURRENT OF clause is used in PL/SQL?

  1. DELETE clause

  2. UPDATE clause

  3. ROWID pseudocolumn

  4. FOR UPDATE clause
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The WHERE CURRENT OF clause requires that the cursor was declared with a FOR UPDATE clause. This clause locks the rows that will be updated or deleted, ensuring that the data hasn't changed between when the cursor was opened and when the position-based operation occurs. Without FOR UPDATE, the database cannot guarantee data integrity for the positioned operation.

Multiple choice technology web technology

What command can you use to test a named PL/SQL block for compilation?

  1. ANALYZE_SCHEMA

  2. CREATE OR REPLACE

  3. SHOW ERRORS

  4. FORMAT_ERROR_STACK
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The SHOW ERRORS command displays compilation errors that occurred when a named PL/SQL block was last compiled. It's useful after attempting to create a procedure, function, or package to see any syntax or semantic errors that prevented successful compilation.

Multiple choice technology programming languages

What can be implemented to reuse a cursor definition in PL/SQL?

  1. Cursor FOR loop

  2. FETCH statement

  3. %ISOPEN attribute

  4. Parameters
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

PL/SQL cursors can accept parameters, making them reusable with different values each time they're opened. Parameterized cursors allow you to write the cursor definition once and open it multiple times with different parameter values, avoiding code duplication.

Multiple choice technology databases

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

  1. SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

  2. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

  3. SELECT dept_id, job_cat, MAX(salary) FROM employees;

  4. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To find maximum salary per job category within each department, you must GROUP BY both dept_id and job_cat. MAX(salary) is then calculated for each unique combination. Option A has invalid WHERE syntax, C lacks grouping, D only groups by department.

Multiple choice technology databases

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(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. COUNT(start_date)

  3. AVG(start_date, end_date)

  4. MIN(start_date)
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

COUNT() and MIN() are valid aggregate functions on DATE columns - COUNT returns non-null count, MIN returns earliest date. SUM() requires numeric data. AVG() on two dates is invalid syntax. Only two options are correct.

Multiple choice technology databases

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?

  1. Immediately after the SELECT clause

  2. After the ORDER BY clause

  3. After the WHERE clause

  4. Before the WHERE clause
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In a standard SQL SELECT statement, the clauses must follow a specific syntactic order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Therefore, the GROUP BY clause is placed immediately after the WHERE clause.

Multiple choice technology databases

Which SELECT statement will the result ‘ello world’ from the string ‘Hello World’?

  1. SELECT SUBSTR( ‘Hello World’,1) FROM dual;

  2. SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;

  3. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;

  4. SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know the basic SQL functions and how they can be applied to strings. The user must evaluate each option and determine which one will result in the desired output of 'ello world' from the given string 'Hello World'.

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

A. SELECT SUBSTR( ‘Hello World’,1) FROM dual;

This option selects a substring from the given string starting at the first character. Since the starting point is at the first character, the entire string 'Hello World' will be returned. Therefore, this option is incorrect.

B. SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;

This option capitalizes the first letter of the given string and removes any leading or trailing spaces. However, the second and third parameters of the TRIM function are invalid and will cause an error. Therefore, this option is also incorrect.

C. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;

This option removes the letter 'H' from the given string, converts it to lowercase, and removes any leading or trailing spaces. The resulting string will be 'ello world', which is the desired output. Therefore, this option is correct.

D. SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;

This option selects a substring from the given string starting at the second character and ending after one character. The resulting substring will be 'e'. This substring is then converted to lowercase. Therefore, this option is incorrect.

The Answer is: C. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;

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

SQL column aliases with spaces require double quotes. Option B correctly uses "Annual Salary". Option A uses single quotes (invalid for aliases), C is missing quotes around space-containing alias, D incorrectly wraps INITCAP function in quotes.

Multiple choice technology databases

Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?

  1. ORDER BY SALARY > 5000

  2. GROUP BY SALARY > 5000

  3. HAVING SALARY > 5000

  4. WHERE SALARY > 5000
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The WHERE clause filters rows before aggregation. To display only employees with salary > 5000, use WHERE SALARY > 5000. ORDER BY sorts, GROUP BY aggregates, HAVING filters aggregated results - none are appropriate here.

Multiple choice technology databases

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

  1. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

  2. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;

  3. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

  4. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the SQL syntax for extracting specific parts of a date and formatting date values. The user must use the correct function to extract the year from the system date and format it as "1998".

Option A is correct. This option uses the TO_CHAR function to convert the system date to a character string in the format specified by the second argument ('yyyy' in this case). This function extracts the year from the system date and formats it as a 4-digit string. The output will be "1998".

Option B is incorrect. This option uses the TO_DATE function, which is used to convert a character string to a date value. In this case, the first argument is already a date value, so using TO_DATE is unnecessary. Also, the second argument ('yyyy') specifies the format of the output, not the input.

Option C is incorrect. This option uses the DECODE function to extract the year from the system date. However, the syntax is incorrect. DECODE requires at least three arguments: the first is the value to be compared, the second is the comparison value, and the third is the result if the comparison is true. In this case, the first argument (SUBSTR(SYSDATE, 8)) is missing a second argument to compare it to.

Option D is incorrect. This option also uses the DECODE function, but the syntax is incorrect. The SUBSTR function extracts a substring from the system date starting at position 8, which is the day of the month. There is no 'year' substring in this position, so the output will not be correct.

The Answer is: A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;