Home › Quizzes › Oracle

Oracle

ORACLE Practice, ORACLE Certification Questions

20 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

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
Question 2 Multiple Choice (Single Answer)

Which of the following requires the usage of wild card characters?

  1. BETWEEN
  2. IN
  3. EXISTS
  4. LIKE
  5. UNION
Question 3 Multiple Choice (Single Answer)

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
Question 4 Multiple Choice (Single Answer)

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.
Question 5 Multiple Choice (Single Answer)

Which of the following is not the valid type of JOIN in ORACLE.

A. INNER JOIN
B. LEFT and RIGHT JOINS
C. SELF JOIN
D. MIDDLE JOIN

  1. A
  2. B
  3. C
  4. D
  5. All are valid types of JOINS in ORACLE
Question 6 Multiple Choice (Single Answer)

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.
Question 7 Multiple Choice (Single Answer)

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.
Question 8 Multiple Choice (Single Answer)

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
Question 9 Multiple Choice (Single Answer)

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
Question 10 Multiple Choice (Single Answer)

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
Question 11 Multiple Choice (Single Answer)

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
Question 12 Multiple Choice (Single Answer)

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.
Question 13 Multiple Choice (Single Answer)

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
Question 14 Multiple Choice (Single Answer)

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.
Question 15 Multiple Choice (Single Answer)

Write a query to retrieve all the students details whose id's starting from 1001 to 1050.

  1. SELECT *
    FROM STUDENTS
    WHERE STUDENT_ID BETWEEN 1001 AND 1050
  2. SELECT *
    FROM STUDENTS
    WHERE STUDENT_ID >= 1001
    AND STUDENT_ID <= 1050
  3. SELECT *
    FROM STUDENTS
    WHERE STUDENT_ID > 1001
    AND STUDENT_ID < 1050
  4. Both option 1 and option 2 are correct.
  5. None of the options are correct
Question 16 Multiple Choice (Single Answer)

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
Question 17 Multiple Choice (Single Answer)

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.
Question 18 Multiple Choice (Single Answer)

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
Question 19 Multiple Choice (Single Answer)

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. sql SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS LEFT JOIN BRANCH WHERE BR_ID = BRANCH_ID
  2. sql SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS, BRANCH WHERE BR_ID = BRANCH_ID ON LEFT
  3. sql SELECT STUDENT_ID, STUDENT_NAME, BRANCH_ID, BRANCH_NAME, MARKS FROM STUDENTS LEFT, BRANCH WHERE BR_ID = BRANCH_ID
  4. sql 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.
Question 20 Multiple Choice (Single Answer)

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.
← Back to Quizzes