InfyTQ Questions and Answers | DBMS Certification Round

Question 1 – Ravi Sastri is a good coach. He wants to join 2 relational tables related to sports. He also wants that all the matched and unmatched tuples of the right table should be present in the result but only the matched rows of the left table should be present in the result after the join operation. Which of the following joins he should use?

Options

1. Equi Join
2. Left outer join
3. Right outer join
4. Full outer join

Answer- Right outer join

Explanation – The result of right outer join contains all the matched and unmatched rows from the right table and only the matched rows from the left table

Question 2 – Joins can be visualize as _______ with ________ :

Options

1. Where , Groupby clause
2. Aggregate functions, some conditions 
3. Cartesian product , some condition 
4. None of the above

Answer- Cartesian product , some condition 

Explanation – With the help of cartesian products we can get all the possible combinations between the tables. With the help of adequate conditions we can perform any join operations

Question 3 – Kapil Dev is a good cricket administrator. He wants to join 2 tables using an equality operator.

Which type of join is this?

Options

1. Cross join
2. Equi join
3. Left join
4. Full join

Answer- Equi join

Explanation – If 2 tables are joined using equality operator then that join comes under the condition of Equi join.

Question 4 – What will be the output of the following function call in SQL? Select Replace(‘pqrstpqr’, ‘pqr’, ‘rst’) 

Options

1. pqpqr
2. rststrst
3. pqpq
4. st

Answer- rststrst

Explanation – Replace(‘pqrstpqr’, ‘pqr’, ‘rst’) Here all the ‘pqr’ substrings of  ‘pqrstpqr’ will be replaced by ‘rst’. So, the output will be ‘rststrst’ Hence the answer will be rststrst

Question 5 – Consider a directed line(->) from the relationship set mentor to both entity sets student and teacher. This indicates _________ cardinality

Options

1. One to many
2. Many to one
3. One to one
4. Many to many

Answer- one to one

Explanation – This indicates a teacher can mentor at most 1 student and a student can have at most 1 teacher as mentor.

Question 6 – Given are the columns in the STUDENTS table, Which of the following statements will you use to retrieve a string that contains ‘_abc_’ in the ‘STUDENT_ID’ column?  

STUDENTS table columns:

CLASS_ID NUMBER (4)

LAST_NAME VARCHAR2 (25)

STUDENT_ID VARCHAR2 (10)

Options

1. SELECT class_id, last_name, student_id FROM students WHERE student_id = %ABC%;
2. SELECT class_id, last_name, student_id FROM students WHERE student_id LIKE ABC_%;
3. SELECT class_id, last_name, student_id FROM students WHERE student_id LIKE %ABC%;
4. None of these

Answer- SELECT class_id, last_name, student_id FROM students WHERE student_id LIKE %ABC%;

Explanation – WHERE student_id LIKE ‘%_ABC_%’  selects all the job_ids that has ‘_ABC_’ substring in it (case insensitively)

Question 7 – Which of the following statements are true about LIKE operator in SQL?

• LIKE operators are case insensitive.

• LIKE ‘%abc’ finds all the strings end with abc

• LIKE ‘%ABC%’ finds all the strings which contain abc

• LIKE ‘_AC_’ find all the strings which have exactly 4 characters and the 2nd and 3rd characters are A,C respectively

Options

1. A,B,C,D
2. A,B,C
3. A,B,C
4. A,B

Answer- A,B,C,D

Explanation – All of the above is true about Like Operator

Question 8 – Which of the following lines in the given code contains an error?  

1. SELECT stid,f_name, l_name
2. FROM students
3. WHERE SUBSTR(l_name, 1, 1) > TO_NUMBER(‘P’)
4. AND stid > 2000
5. ORDER BY stid DESC, l_name ASC;

Options

1. No error
2. A
3. C
4. D

Answer- C

Explanation – TO_NUMBER function: The TO_NUMBER function converts a character value to a numeric datatype. If the string being converted contains nonnumeric characters, the function returns an error.

Question 9 – What will the following query return?  

SELECT *

FROM Products

WHERE Amount BETWEEN 10 AND 80

EXCEPT

SELECT *

FROM Products

WHERE Amount BETWEEN 50 AND 75;

Options

1. Unique record
2. None of the given option
3. Record that are not in query 2
4. Error

Answer- Record that are not in query 2

Explanation – The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Question 10  – The SQL statement select SUBSTR(‘mnopqrst’, INSTR(‘qpsptrcs’, ‘p’),3) from dual prints

Options

1. nop
2. mno
3. pqrs
4. pqr

Answer- public static synchronized final strictfp void main(String… args){}

Explanation – 

• INSTR Function:- The INSTR function in SQL is used to find the starting location of a pattern in a string. The syntax for the INSTR function is as follows:
• INSTR (str, pattern): Find the starting location of pattern in string str.
•  SUBSTR Function:- The Substring function in SQL is used to grab a portion of the stored data. The syntax for the SUBSTR function is as follows: 
• SUBSTR(str,pos,len): Starting with the position pos in string str select the characters upto the length len. 
  • In the above query INSTR(‘qpsptrcs’, ‘p’) outputs 2 as the starting location of the pattern So, now the query can be written as 
• SUBSTR(‘mnopqrst’,2,3)
  •  It will return the substring from position 2 to position (2+3-1)=4 of the string which is “nop”