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

Find the odd man out.

  1. PRIMARY KEY Constraint
  2. UNIQUE KEY Constraint
  3. CHECK Constraint
  4. FOREIGN KEY Constraint
  5. NOT NULL Constraint Note: While finding the odd man out from the above choices, consider the general scenarios, ignore the rare scenario.

  1. PRIMARY KEY Constraint

  2. UNIQUE KEY Constraint

  3. CHECK Constraint

  4. FOREIGN KEY Constraint

  5. NOT NULL Constraint
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

All the Constraints can be applied when you have only one table. But to apply FOREIGN KEY Constraint, two tables are required. Option 4 is the correct choice.

FYI - Rare scenario is A Foreign Key can be applied on single table on its primary key, which is very rare in reality.

Multiple choice

Which of the following is TRUE about PRIMARY KEY Constraint

  1. A Table can have more than one primary key.
  2. PRIMARY KEY should be defined along with the creation of the Table.
  3. PRIMARY KEY can be added after creating the Table using ALTER .
  4. PRIMARY KEY can also be dropped in case it is not useful.
  5. To define a PRIMARY KEY on any column, the column should ensure not to contain any NULL values.

  1. 1, 2, 4 are correct

  2. 1, 3 and 5 are correct

  3. 3, 4 and 5 are correct

  4. 2, 3 and 4 are correct

  5. All are correct
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation
  1. No, A Table can have only one primary key.
    1. No, It is not mandatory. It is one of the ways to define a PRIMARY KEY.
    2. Yes, PRIMARY KEY can be added after creating the Table using ALTER .
    3. Yes, using ALTER statement.
    4. Yes, can use NOT NULL constraint.
Multiple choice

Which of the following is the correct way of defining the CHECK constraint?

  1. CREATE TABLE STUDENTS( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS CHAR (25) , MARKS INT, PRIMARY KEY (STUDENT_ID));

  2. CREATE TABLE STUDENTS( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL , ADDRESS CHAR (25) , MARKS INT, PRIMARY KEY (STUDENT_ID)); ALTER TABLE STUDENTS MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );

  3. CREATE TABLE STUDENTS( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL , ADDRESS CHAR (25) , MARKS INT, PRIMARY KEY (STUDENT_ID));ALTER TABLE STUDENTS ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);

  4. Check constraint has introduced only from ORACLE 10g onwards.

  5. All Option 1, Option 2 and Option 3 are correct.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, All three options are correct.

Multiple choice

Which of the following can produces the NULL values in the resultant tables? Note: The source tables have no NULL values in them.

A. INNER JOIN B. LEFT & RIGHT JOINS C. SELF JOIN

  1. A

  2. B

  3. C

  4. All of the JOINS produce NULL values to every result table

  5. JOINS never produce NULL values in the result tables when the source tables have no NULL values.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Only LEFT & RIGHT JOINs produce NULL values, while joining two tables as they result the non-matching rows by filling NULL values.

Multiple choice

Which of the following is the correct way of defining a DEFAULT constraint for a table?

  1. CREATE TABLE STUDENTS( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , MARKS INT DEFAULT 35,
    PRIMARY KEY (STUDENT_ID) );

  2. CREATE TABLE STUDENTS( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , PRIMARY KEY (STUDENT_ID) ); ALTER TABLE STUDENTS ADD MARKS INT DEFAULT 35;

  3. Both option 1 and option 2 are correct.

  4. DEFAULT is not a valid constraint in ORACLE.

  5. DEFAULT constraint was discontinued from ORACLE 9i onwards.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Yes, both option 1 and option 2 are correct.

Option 1 is the example for defining a DEFAULT constraint using CREATE statement.

Option 2 is the example for defining a DEFAULT constraint using an ALTER statement after the table is created using CREATE.

Multiple choice

Assume there are 20 rows in the STUDENTS table and 5 rows in BRANCH table. Take a look at the Query below.

STUDENT Table has columns - STUDENT_ID, STUDENT_NAME, BR_ID, AGE, MARKS BRANCH Table has columns - BRANCH_ID, BRANCH_NAME

SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME FROM STUDENTS, BRANCH

What is the name of the join used in the above Query.

  1. INNER JOIN

  2. OUTER JOIN

  3. NORMAL JOIN

  4. CROSS JOIN

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

Look at the Query: Query has 2 different tables STUDENTS and BRANCH. The Query does not have any JOIN predicate, i.e. it evaluates to TRUE for every in tables. It results in all possible combinations of rows from both the tables. This kind of JOIN is called CROSS JOIN or CARTESIAN JOIN.

Multiple choice

Assume there are 20 rows in the STUDENTS table and 5 rows in BRANCH table. Take a look at the Query below.

STUDENT Table has columns - STUDENT_ID, STUDENT_NAME, BR_ID, AGE, MARKS BRANCH Table has columns - BRANCH_ID, BRANCH_NAME

SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME FROM STUDENTS, BRANCH

How many number of rows does the above query results?

  1. 20 rows only

  2. 20 + 5 = 25 rows

  3. 20 * 5 = 100 rows

  4. 20 - 5 = 15 rows

  5. 20 / 5 = 4 rows
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The Query is an example for CROSS JOIN. So it produces 20 * 5 = 100 rows.