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

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.

Multiple choice

In Oracle, commit and savepoint commands come under

  1. Data Definition Language

  2. Data Manipulation Language

  3. Transaction Control Language

  4. Object Oriented Language

  5. Procedure Oriented Language
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle, commit and savepoint commands control the transactions specified in the form of queries. Hence, this is the correct option.

Multiple choice

_________ are Transaction Control Language commands in SQL.

  1. Grant and Commit

  2. Savepoint and Revoke

  3. Grant and Revoke

  4. Commit and Rollback
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Transaction Control Language (TCL) commands in SQL manage transactions: COMMIT saves changes permanently, ROLLBACK undoes changes. GRANT and REVOKE are Data Control Language (DCL) commands for permissions. SAVEPOINT is a TCL command but paired with ROLLBACK TO, not GRANT/REvoke.