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

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.

Multiple choice

Write the missing code for the Foreign Key constraint definition. Missing code is represented by *************** below. Hint: Foreign Key is to define for BID column in STUDENTS table on BRANCH_ID column in BRANCH table. CREATE TABLE BRANCH(
BRANCH_ID INT NOT NULL,
BRANCH_NAME VARCHAR2(20) NOT NULL,
PRIMARY KEY (BRANCH_ID) );

CREATE TABLE STUDENTS(
STUDENT_ID INT NOT NULL,
STUDENT_NAME VARCHAR2(25) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR2(25) ,
MARKS INT,
BID INT *********************,
PRIMARY KEY (STUDENT_ID) );

  1. BID INT REFERENCES BRANCH(BRANCH_ID)

  2. BID INT FOREIGN KEY(BRANCH_ID)

  3. BID INT FOREIGN KEY CONSTRAINT (BRANCH_ID) TABLE BRANCH

  4. All the above 3 are correct

  5. Foreign Key Constraint or Referential integrity can not be defined along with the table definition
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Yes, it is correct.

Multiple choice

Write a query to enable (or TURN ON) a constraint on a table.

  1. ALTER TABLE STUDENTS STATUS = ENABLE CONSTRAINT constraint_name

  2. ALTER TABLE STUDENTS ENABLE constraint_name

  3. ALTER TABLE STUDENTS ENABLE CONSTRAINT constraint_name

  4. ALTER TABLE STUDENTS TURN ON CONSTRAINT constraint_name

  5. ALTER TABLE STUDENTS TURN ON constraint_name
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Yes, it is the correct answer

Multiple choice

How do you represent the missing values in a table in ORACLE?

  1. We represent the missing values with SPACES, i.e, ' '

  2. We represent the missing values with ZEROES, i.e. 0

  3. We represent the missing values with SPACES, ZEROES, etc. based on the datatype of the column.

  4. We represent the missing values with NULL.

  5. ORACLE tables should not have any missing values at any point of time.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Yes, correct.

Multiple choice

Which of the following is correct about NOT NULL constraint?

A. NOT NULL constraint demands a column not to accept NULL values. B. NOT NULL constraint demands a field to always contains some value. C. One cannot insert a new record with a NULL value, when NOT NULL constraint is specified.

  1. A is correct

  2. B is correct

  3. C is correct

  4. None of A, B and C are correct

  5. All A, B and C are correct
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

All A, B and C are correct. Option 5 is the correct answer.

Multiple choice

Which of the following are correct about the Constraints?

  1. Constraints can be defined along with the table definition using CREATE.

  2. Constraints can be imposed on the table columns using ALTER statement after the table is defined.

  3. Constraints can be enabled and/or disabled.

  4. Constraints can be dropped if not required using ALTER - DROP.

  5. All the above are correct.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, It is TRUE. But, take a look at the other choices before marking your answer.

Option 5 is the correct answer.

Multiple choice

Which of the following the correct way of defining the PRIMARY KEY constraint for a STUDENT table A. CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , MARKS INT,
PRIMARY KEY (ID) );

B. CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , MARKS INT,
PRIMARY KEY (ID, NAME) );

C. CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , MARKS INT,
); ALTER TABLE STUDENTS ADD CONSTRAINT PK_STUDENTID PRIMARY KEY (ID, NAME);

D. CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , MARKS INT,
); ALTER TABLE STUDENTS ADD CONSTRAINT PK_STUDENTID PRIMARY KEY (ID);

  1. A and C are correct

  2. B and D are correct

  3. A, B and C are correct

  4. A, B and D are correct

  5. All 4 are correct
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

A. Correct, defining the PRIMARY KEY along with the table creation for a single column. B. Correct, defining the PRIMARY KEY along with the table creation for multiple columns, this is called composite primary key. C. Correct, defining a PRIMARY KEY using ALTER statement for multiple column after the creation of the table. This is called composite primary key. D. Correct,  defining a PRIMARY KEY using ALTER statement for one column after the creation of the table.  

Multiple choice

Consider the data from the two table STUDENT and BRANCH below.

STUDENT_ID STUDENT_NAME BR_ID MARKS
1001 STUDENT1 101 79
1002 STUDENT 2 102 92
1003 STUDENT 3 101 78
1004 STUDENT 4 102 69
1005 STUDENT 5 103 90
1006 STUDENT 6 104 87
1007 STUDENT 7 103 93
1008 STUDENT 8 102 89
1009 STUDENT 9 105 78
1010 STUDENT 10 106 76
BRANCH_ID BRANCH_NAME
101 COMPUTERS
102 ARTS
103 ELECTRONICS
104 CHEMICAL
105 MECHANICAL

Write a query to retrieve the STUDENT_ID, STUDENT_NAME, STUDENT_NAME, BRANCH_ID, BRANCH_NAME and MARKS by joining the table STUDENTS and BRANCH on BRANCH_ID column.

  1. SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS, BRANCH WHERE BR_ID = BRANCH_ID

  2. SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS INNER JOIN BRANCH WHERE BR_ID = BRANCH_ID

  3. SELECT S.STUDENT_ID, S.STUDENT_NAME, B.BRANCH_ID, B.BRANCH_NAME, S.MARKS FROM STUDENTS S, BRANCH B WHERE S.BR_ID = B.BRANCH_ID

  4. SELECT S.STUDENT_ID, S.STUDENT_NAME, B.BRANCH_ID, B.BRANCH_NAME, S.MARKS FROM STUDENTS S INNER JOIN BRANCH B WHERE S.BR_ID = B.BRANCH_ID

  5. All the above 4 queries are correct.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

All four queries are correct.

Multiple choice

Consider the AGENTS table with columns AGENT_ID, AGENT_NAME, SALARY, SALES_QTY, Commission_Paid

Write a query to display the agents details whose agents Commission_Paid is not equal to NULL.

  1. SELECT AGENT_ID, AGENT_NAME, SALARY, SALES_QTY, COMMISSION_PAID FROM AGENTS WHERE COMMISSION_PAID != NULL

  2. SELECT AGENT_ID, AGENT_NAME, SALARY, SALES_QTY, COMMISSION_PAID FROM AGENTS WHERE COMMISSION_PAID IS NOT NULL

  3. SELECT AGENT_ID, AGENT_NAME, SALARY, SALES_QTY, COMMISSION_PAID FROM AGENTS WHERE COMMISSION_PAID NOT NULL

  4. SELECT AGENT_ID, AGENT_NAME, SALARY, SALES_QTY, COMMISSION_PAID FROM AGENTS WHERE COMMISSION_PAID = NOT NULL

  5. None of the above are correct
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Developers should use IS NULL or   IS NOT NULL operators to check NULL values for any column in a table.

Multiple choice

Consider the data from the two table STUDENT and BRANCH below.

STUDENT_ID STUDENT_NAME BR_ID MARKS
1001 STUDENT1 101 79
1002 STUDENT 2 102 92
1003 STUDENT 3 101 78
1004 STUDENT 4 102 69
1005 STUDENT 5 103 90
1006 STUDENT 6 104 87
1007 STUDENT 7 103 93
1008 STUDENT 8 102 89
1009 STUDENT 9 105 78
1010 STUDENT 10 106 76
BRANCH_ID BRANCH_NAME
101 COMPUTERS
102 ARTS
103 ELECTRONICS
104 CHEMICAL
105 MECHANICAL

Write a query to retrieve the STUDENT_ID, STUDENT_NAME, STUDENT_NAME, BRANCH_ID, BRANCH_NAME and MARKS by joining the table STUDENTS and BRANCH on BRANCH_ID column. Also ensure that each row in the STUDENTS table should be present in the result table.

 
  1. SELECT STUDENT_ID, 
                 STUDENT_NAME, 
                 BRANCH_ID, 
                 BRANCH_NAME, 
                 MARKS
FROM STUDENTS
LEFT JOIN BRANCH
WHERE BR_ID = BRANCH_ID

  2. SELECT STUDENT_ID, 
                 STUDENT_NAME, 
                 BRANCH_ID, 
                 BRANCH_NAME, 
                 MARKS
FROM STUDENTS, BRANCH
WHERE BR_ID = BRANCH_ID
ON LEFT

  3. SELECT STUDENT_ID, 
                 STUDENT_NAME, 
                 BRANCH_ID, 
                 BRANCH_NAME, 
                 MARKS
FROM STUDENTS LEFT, BRANCH
WHERE BR_ID = BRANCH_ID

  4. SELECT STUDENT_ID, 
                 STUDENT_NAME, 
                 BRANCH_ID, 
                 BRANCH_NAME, 
                 MARKS
FROM STUDENTS INNER JOIN BRANCH
WHERE BR_ID = BRANCH_ID

  5. All the four queries are correct.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Yes, it is the correct representation of the LEFT JOIN between two tables.

Multiple choice

Consider the data from the two table STUDENT and BRANCH below.

STUDENT_ID STUDENT_NAME BR_ID MARKS
1001 STUDENT1 101 79
1002 STUDENT 2 102 92
1003 STUDENT 3 101 78
1004 STUDENT 4 102 69
1005 STUDENT 5 103 90
1006 STUDENT 6 104 87
1007 STUDENT 7 103 93
1008 STUDENT 8 102 89
1009 STUDENT 9 105 78
1010 STUDENT 10 106 76
BRANCH_ID BRANCH_NAME
101 COMPUTERS
102 ARTS
103 ELECTRONICS
104 CHEMICAL
105 MECHANICAL

Predict the output of the following query.

SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS LEFT JOIN BRANCH ON BR_ID = BRANCH_ID
  1. STUDENT_ID STUDENT_NAME BRANCH_ID BRANCH_NAME MARKS
    1001 STUDENT1 101 COMPUTERS 79
    1002 STUDENT 2 102 ARTS 92
    1003 STUDENT 3 101 COMPUTERS 78
    1004 STUDENT 4 102 ARTS 69
    1005 STUDENT 5 103 ELECTRONICS 90
    1006 STUDENT 6 104 CHEMICAL 87
    1007 STUDENT 7 103 ELECTRONICS 93
    1008 STUDENT 8 102 ARTS 89
    1009 STUDENT 9 105 MECHANICAL 78
    1010 STUDENT 10 NULL NULL 76
  2. STUDENT_ID STUDENT_NAME BRANCH_ID BRANCH_NAME MARKS
    1001 STUDENT1 101 COMPUTERS 79
    1002 STUDENT 2 102 ARTS 92
    1003 STUDENT 3 101 COMPUTERS 78
    1004 STUDENT 4 102 ARTS 69
    1005 STUDENT 5 103 ELECTRONICS 90
    1006 STUDENT 6 104 CHEMICAL 87
    1007 STUDENT 7 103 ELECTRONICS 93
    1008 STUDENT 8 102 ARTS 89
    1009 STUDENT 9 105 MECHANICAL 78
    1010 STUDENT 10 106 MECHANICAL 76
  3. STUDENT_ID STUDENT_NAME BRANCH_ID BRANCH_NAME MARKS
    1001 STUDENT1 101 COMPUTERS 79
    1002 STUDENT 2 102 ARTS 92
    1003 STUDENT 3 101 COMPUTERS 78
    1004 STUDENT 4 102 ARTS 69
    1005 STUDENT 5 103 ELECTRONICS 90
    1006 STUDENT 6 104 CHEMICAL 87
    1007 STUDENT 7 103 ELECTRONICS 93
    1008 STUDENT 8 102 ARTS 89
    1009 STUDENT 9 105 MECHANICAL 78
    1010 STUDENT 10 106 NULL 76
  4. STUDENT_ID STUDENT_NAME BRANCH_ID BRANCH_NAME MARKS
    1001 STUDENT1 101 COMPUTERS 79
    1002 STUDENT 2 102 ARTS 92
    1003 STUDENT 3 101 COMPUTERS 78
    1004 STUDENT 4 102 ARTS 69
    1005 STUDENT 5 103 ELECTRONICS 90
    1006 STUDENT 6 104 CHEMICAL 87
    1007 STUDENT 7 103 ELECTRONICS 93
    1008 STUDENT 8 102 ARTS 89
    1009 STUDENT 9 105 MECHANICAL 78
    1010 STUDENT 10 NULL NULL NULL

  5. Syntax error in the Query.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Yes, correct choice.