SQL interview questions

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

Which of the following is not true about complex views? A - They derive data from more than one table. B - They contain no functions or grouping. C - You cannot perform DML operations through a complex view. D - All of the above are true.

B - They contain no functions or grouping.

What's wrong in the following query? SELECT student_code, name FROM students WHERE marks = (SELECT MAX(marks) FROM students GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.

What do you understand by a subquery? When is it used?

A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

an you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.

Q 10 - Which of the following code would create a role named student_admin?

C - CREATE ROLE student_admin;

Which of the following is not true about USING clause? A - When more than one column has the same name, USING clause is used for specifying the column to be joined by equijoin. B - It is used for matching one column only. C - You can use a table name or alias in the referenced columns. D - The NATURAL JOIN and the USING clauses are mutually exclusive.

C - You can use a table name or alias in the referenced columns.

Which of the following is not true about SQL statements? A - SQL statements are not case sensitive. B - SQL statements can be written on one or more lines. C - Keywords cannot be split across lines. D - Clauses must be written on separate lines.

D - Clauses must be written on separate lines.

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL

What is returned by SUBSTR('TUTORIALS POINT', -1, 1)?

T

Which SQL statement is used to add, modify or drop columns in a database table?

The ALTER TABLE statement.

What's wrong in the following query? SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. The HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement. Using CASE expression Using the DECODE function

What is the difference between VARCHAR2 AND CHAR datatypes?

VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.

Say True or False. Give explanation if False. A multiple row subquery returns more than one row from the inner SELECT statement.

true

Say True or False. Give explanation if False. All group functions ignore null values.

true

Say True or False. Give explanation if False. Multiple column subqueries return more than one column from the inner SELECT statement.

true

Say True or False. Give explanation if False. The DISTINCT keyword allows a function consider only non-duplicate values.

true

when is a Cartesian product formed?

when a join condition is ommitted

Which statement allows conditional update, or insertion of data into a table simultaneously? A - INSERT statement B - MERGE statement C - UPDATE statement D - None of the above

B MERGE

Consider the following schema − HONOURS_SUBJECT(subject_code, subject_name, department_head); LOCATIONS(subject_code, department_name, location_id, city); Select the right query for retrieving records from the tables HONOURS_SUBJECT and LOCATIONS with a full outer join A - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(h.subject_code = l.subject_code); B - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(subject_code); C - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l where (h.subject_code = l.subject_code); D - None of the above.

A select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(h.subject_code = l.subject_code);

Which of the following code will create an index named stu_marks_ind on the columns student_code and percentage_of_marks of the STUDENTS table. Now which will delete the index stu_marks_ind. Which of the following code will delete an index stu_marks_ind on the columns student_code and percentage_of_marks of the STUDENTS table? A - drop index stu_marks_ind; B - delete index stu_marks_ind; C - drop stu_marks_ind; D - drop index stu_marks_ind on students;

A - drop index stu_marks_ind;

You want to calculate the sum of commissions earned by the employees of an organisation. If an employee doesn't receive any commission, it should be calculated as zero. Which will be the right query to achieve this? A - select sum(nvl(commission, 0)) from employees; B - select sum(commission, 0) from employees; C - select nvl(sum(commission), 0) from employees; D - None of the above.

A - select sum(nvl(commission, 0)) from employees;

What is a view? why should you use it?

A view is a logical snapshot based on a table or another view. It is used for − Restricting access to data; Making complex queries simple; Ensuring data independency; Providing different views of same data.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Which of the following is not true about inserting news rows to a table? A - The INSERT INTO statement is used for inserting new rows to a table B - You cannot insert rows with NULL values to a table. C - You may insert a new row containing values for each column. D - All of the above.

B - You cannot insert rows with NULL values to a table.

Say True or False. Give explanation if False. A single row subquery returns only one row from the outer SELECT statement

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give explanation if False. COUNT(*) returns the number of columns in a table.

False. COUNT(*) returns the number of rows in a table.

Say True or False. Give explanation if False. Group functions cannot be nested.

False. Group functions can be nested to a depth of two.

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

How do you search for a value in a database table when you don't have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called 'wildcard' search.

What's wrong in the following query? SELECT subject_code, count(name) FROM students;

It doesn't have a GROUP BY clause. The subject_code should be in the GROUP BY clause. SELECT subject_code, count(name) FROM students GROUP BY subject_code;

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways − Implicitly by omitting the column from the column list. Explicitly by specifying the NULL keyword in the VALUES clause.

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, .... expn) It returns the first non-null expression given in the parameter list.

What is the pupose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.