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 databases

You create the following PL/SQL block: DECLARE var1 CONSTANT NUMBER := 50; var2 NUMBER := 0; BEGIN SELECT acctno INTO var2 FROM bank_acct WHERE name = 'JORDAN'; var1 :=var2 + 2000; END; Which of the following lines in this block of PL/SQL code will produce an error? (Choose One)

  1. var2 NUMBER := 0;

  2. INTO var2

  3. WHERE name = 'JORDAN';

  4. var1 :=var2 + 2000;

  5. There are no errors in this PL/SQL block.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

var1 is declared as CONSTANT, which means its value cannot be changed after initialization. Attempting to assign a new value to var1 with 'var1 := var2 + 2000' will raise a compilation error. The other lines are valid PL/SQL syntax - declaring variables and using SELECT INTO are standard operations.

Multiple choice technology databases

Which of the following SQL functions can operate on any datatype?

  1. TO_CHAR

  2. LOWER

  3. LPAD

  4. MAX

  5. CEIL
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

LPAD can accept string or numeric arguments because Oracle implicitly converts numbers to strings when needed for padding operations. LOWER and TO_CHAR only work on character/string types, MAX is an aggregate function requiring a specific datatype context, and CEIL operates only on numbers. LPAD's signature allows it to handle inputs from multiple datatypes through implicit conversion.

Multiple choice technology databases

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

OR returns TRUE if any condition is true (like 'A OR B' is true when either A or B is true). AND returns TRUE only if all conditions are true (like 'A AND B' requires both to be true). This is fundamental logical operator behavior in SQL.

Multiple choice technology databases

Command to retrieve today’s date in the format DD/month/YY ?

  1. Select to_date(sysdate,’DD/MONTH/YY’) from dual;

  2. Select to_date(sysdate,’DD/MM/YY’) from dual;

  3. Select to_char(sysdate,’DD/MONTH/YY’) from dual;

  4. Select to_char(sysdate,’DD/MM/YY’) from dual;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

TO_CHAR converts a date to a string with the specified format. TO_DATE is used to convert a string to a date, which is the opposite of what's needed here. Option C correctly uses TO_CHAR with the format 'DD/MONTH/YY' to display the date with the full month name as the question specifies.

Multiple choice technology databases

select visible_parameter1 from kcrt_table_entries where request_id = '64457' and parameter_set_field_id = ( select parameter_set_field_id from kcrt_request_details reqd , knta_parameter_set_fields psf where reqd.request_id = '64457' and psf.parameter_set_context_id = reqd.parameter_set_context_id and psf.parameter_token = 'AXA_RM_RTF_TCRELDEM') and seq = '1' If the subselect returns more than one row. This query will run fine?

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The WHERE clause uses '=' (single row comparison operator) with a subquery that can return multiple rows. This causes Oracle error ORA-01427: single-row subquery returns more than one row. Should use IN or EXISTS instead.

Multiple choice technology databases

create or replace procedure "test" as a_string varchar(1000 char) := NULL; b_string varchar(2000 char) := NULL; fullname VARCHAR(200 char); edate VARCHAR(200 char); SG VARCHAR(1000 char); cursor c_username is select userid, username from knta_users where username like 'D%'; Begin open c_username; for i in c_username loop a_string := ''; a_string := a_string || ' ' ||i.userid; select full_name into fullname from knta_users where username = i.username ; a_string := a_string || ' ' ||fullname; DBMS_OUTPUT.PUT_LINE( ' ' ||a_string ); END LOOP; close c_username; end "test"; Examine the procedure and choose what will happen when this procedure is executed:

  1. This procedure will not compile successfully.

  2. This procedure will print the Userid and fullname of users whose username starts with ‘D’;

  3. This procedure will print just the fullname of the users.

  4. This procedure will print just the userid of the users.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The procedure declares a cursor for usernames starting with 'D', then uses a FOR loop to iterate through it. Inside the loop, it concatenates userid and fullname into a_string and prints it. This compiles and runs successfully, printing both userid and fullname.

Multiple choice technology databases

create or replace procedure "test" as a_string varchar(1000 char) := NULL; b_string varchar(2000 char) := NULL; fullname VARCHAR(200 char); edate VARCHAR(200 char); SG VARCHAR(1000 char); cursor c_username is select userid, username from knta_users where username like 'D%'; Begin open c_username; for i in c_username loop a_string := ''; a_string := a_string || ' ' ||i.userid; select full_name into fullname from knta_users where username = i.username ; DBMS_OUTPUT.PUT_LINE( ' ' ||a_string ); END LOOP; close c_username; end "test"; Examine the procedure and choose what will happen when this procedure is executed:

  1. This procedure will print the Userid and fullname of users whose username starts with ‘D’;

  2. This procedure will not compile successfully.

  3. This procedure will print just the fullname of the users.

  4. This procedure will print just the userid of the users.
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology databases

Which query will have a better performance? a) SELECT count(), request_type_name FROM KCRT_REQUEST_TYPES WHERE request_type_id IN (SELECT request_type_id FROM kcrt_requests) GROUP BY request_type_name; OR b) SELECT count(), request_type_name FROM kcrt_requests kr,kcrt_request_types krt WHERE kr.request_type_id = krt.request_type_id GROUP BY request_type_name ;

  1. a

  2. b

  3. Can't tell

  4. both will perform the same
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Query (b) uses an explicit JOIN which is generally more efficient than the IN subquery in (a). The JOIN allows the optimizer to choose better execution plans and avoids the overhead of the subquery. IN clauses can be less efficient with large datasets.

Multiple choice technology databases

SELECT count(*),status_id,status_name FROM kcrt_statuses WHERE STATUS_ID IN (SELECT STATUS_ID FROM kcrt_request_type_statuses) GROUP BY status_id; What will be the output?

  1. Returns the count of rows grouped by statuses

  2. Error - Incorrect use of COUNT. It has to have a column name.

  3. Error - because of the GROUP BY Clause or Selected columns

  4. Does not return the count, just displays the other two fields.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The query selects status_name, but status_name is not listed in the GROUP BY clause. In standard SQL, any non-aggregated column in the SELECT list must be included in the GROUP BY clause, which causes a syntax error.

Multiple choice technology databases

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES ---------- EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES -------------- EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(25) Which DELETE Statement is valid?

  1. Delete from employees where employee_id = (select employee_id from employees);

  2. Delete * from employees where employee_id = (select employee_id from new_employees);

  3. Delete from employees where employee_id in (select employee_id from new_employees where name = 'Carrey');

  4. Delete * from Employees where employee_id in (select employee_id from new_employees where last_name = 'Carrey');
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The correct SQL syntax for a delete statement is 'DELETE FROM table_name WHERE condition'. Option C uses this syntax with a subquery checking 'employee_id in'. Options B and D incorrectly use 'DELETE * FROM', which is invalid in SQL.

Multiple choice technology databases

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements insert a row in to the table? (Choose three). A - INSERT INTO employees VALUES (NULL, 'John', 'Smith'); B - INSERT INTO employees(first_name, last_name) VALUES ('John', 'Smith'); C - INSERT INTO employees VALUES ('1000', 'John', NULL); D - INSERT INTO employees(first_name, last_name, employee_id) VALUES ('1000', 'John', 'Smith'); E - INSERT INTO employees(employee_id) VALUES (1000); F - INSERT INTO employees(employee_id,first_name,last_name) VALUES (1000,'John',' ');

  1. A, B, D

  2. C, D, E

  3. C, E, F

  4. B, D, F
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Option C inserts NULL for employee_id (allowed since it's only PK if defined as NOT NULL), E inserts only employee_id (other columns default to NULL), and F inserts employee_id, first_name='John', and a space for last_name. Options A (NULL for PK with NOT NULL), B (missing employee_id), and D (wrong column order) fail.

Multiple choice technology databases

The Student_Grades table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date. Which statement will accomplish this? A. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa DESC; B. SELECT Student_id, semester_end, gpa from student_grades order by semester_end, gpa ASC order by semester_end DESC, gpa DESC; C. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa ASC; D. SELECT Student_id, semester_end, gpa from student_grades order by semester_end ASC, gpa ASC; E. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa ASC;

  1. A

  2. B

  3. C

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