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

TRUNCATE IS USED TO?

  1. DROP TABLE

  2. DELETE ALL ROWS

  3. DELETE ALL COLUMNS

  4. DELETE ORACLE
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TRUNCATE is a DDL command that removes all rows from a table while keeping the table structure intact. Unlike DELETE (which is DML and can be rolled back), TRUNCATE deallocates data pages, making it faster for clearing large tables. It does not drop the table itself, delete columns, or affect the database software.

Multiple choice technology databases

SQL means

  1. simple questionary language

  2. simple query language

  3. structured questionary language

  4. structured query language
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

SQL stands for Structured Query Language, the standard language for relational database management systems. It is used for querying, manipulating, and defining data in databases. The other options - 'simple questionary language', 'simple query language', and 'structured questionary language' - are incorrect expansions.

Multiple choice technology databases

SQL statements are divided into?

  1. 2

  2. 3

  3. 4

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

SQL statements are traditionally divided into five categories: DDL (Data Definition Language), DML (Data Manipulation Language), DRL/DQL (Data Retrieval/Query Language), DCL (Data Control Language), and TCL (Transaction Control Language). This categorization helps organize different types of SQL operations based on their purpose.

Multiple choice technology databases

Which is a Valid table name:

  1. emp@_tab

  2. emp#_tab
  3. $emp_tab

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

Oracle table names can contain letters, numbers, underscore (_), and pound sign (#), but cannot start with a number or contain @ symbol. emp#_tab and Emp_delete are valid. emp@_tab is invalid (@ not allowed), $emp_tab is invalid ($ not allowed).

Multiple choice technology databases

Select employee name whose job is 'Manager' or 'Clerk' and Salary greater than 1000 and comm >100 and deptno is either 10 or 20

  1. Select ename from emp where job in ('MANAGER','CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO=(10,20);

  2. Select ename from emp where job in ('MANAGER' OR 'CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO IN (10,20);

  3. Select ename from emp where job in ('MANAGER','CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO IN (10,20);

  4. None
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology databases

What is the Output of the Following Program: declare n number:=2000; cursor c1 is select sal into n from emp where empno=7788; begin dbms_output.put_line(n); end; Data in Emp Table empno sal 123 1000

  1. Null

  2. It Gives Error

  3. 1000

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

The cursor is declared but never opened or fetched from in the execution block. The dbms_output.put_line(n) outputs the initialized variable value of 2000, not any value from the cursor. Without opening and fetching, the cursor query doesn't execute.

Multiple choice technology databases

What is the output of the Query: select * from emp where dept_no in (select dept_no from dept order by dept_no desc);

  1. It Executes Successfully

  2. It throws Error with message: missing right Parenthesis

  3. It throws Error

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

In Oracle SQL, a subquery used with the IN clause cannot contain an ORDER BY clause. The ORDER BY is meaningless in this context since IN only checks membership, not ordering. The parser treats this as a syntax error, typically reporting ORA-00907: missing right parenthesis.

Multiple choice technology databases

Which is a Valid table name:

  1. emp@_tab

  2. emp#_tab
  3. $emp_tab

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

Oracle table names must start with a letter, can contain letters, digits, and underscores only, and are case-insensitive by default. emp#_tab is valid (# was allowed in older Oracle versions), $emp_tab is invalid ($ is not a standard identifier character), and Emp_delete is valid (starts with letter, contains only underscore as special character).

Multiple choice technology databases

Select employee name whose job is 'Manager' or 'Clerk', Salary greater than 1000, comm >100 and deptno is either 10 or 20

  1. Select ename from emp where job in ('MANAGER','CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO=(10,20);

  2. Select ename from emp where job in ('MANAGER' OR 'CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO IN (10,20);

  3. Select ename from emp where job in ('MANAGER','CLERK') AND SAL>1000 AND COMM>100 AND DEPTNO IN (10,20);

  4. None
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology databases

What is the Output of the Following Program: declare n number := 2000; cursor c1 is select sal into n from emp where empno = 7788; begin dbms_output.put_line(n); end; Data in Emp Table empno sal 123 1000

  1. Null

  2. It Throws Error

  3. 1000

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

The cursor c1 is declared but never opened - only the declaration section contains the cursor definition. Since the cursor is not opened or fetched, the value of n remains unchanged at its initialized value of 2000. The dbms_output.put_line(n) displays 2000. The sal value from emp table (1000) is never retrieved because the cursor was never opened.

Multiple choice technology databases

The below query Executes Successfully without Error: select * from emp where dept_no in (select dept_no from dept order by dept_no desc);

  1. True

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

This query will fail because ORDER BY is not allowed in a subquery used with an IN clause. The IN operator only needs to check membership, not ordering. Oracle throws ORA-00907: missing right parenthesis or similar syntax error when encountering ORDER BY in this position.

Multiple choice technology databases

What happens if we execute the below create table query at first time in a database with other than sys user and the user is having create table privileges: create table dual(a number);

  1. It throws error with message: table or view already exists

  2. Insufficient privileges

  3. Table will get created successfully

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

In Oracle, the dual table is a built-in table, but only exists by default for SYS user. Other users can create their own table named dual if they have CREATE TABLE privileges. There is no restriction preventing a non-SYS user from creating a table with this name in their own schema. The table will be created successfully on first execution.

Multiple choice technology databases

What is the output of the below queries: if table A is having 16 rows: select count() from A where 10=10; select count() from A having 10=10;

  1. first gives error, 16

  2. 16, second gives error

  3. 16,16

  4. Both the queries will give error
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The first query with WHERE 10=10 is always true, so count() returns 16. The second query uses HAVING 10=10 on an aggregate - this operates on the single result row from count() and returns 16 since the condition is always true. Both return 16. HAVING without GROUP BY applies to the entire result set as a single group.