SQL Interview Questions

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.

PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

Following are various DDL or Data Definition Language commands in SQL −

• CREATE − it creates a new table, a view of a table, or other object in database.

• ALTER − it modifies an existing database object, such as a table.

• DROP − it deletes an entire table, a view of a table or other object in the database.

Following are various DML or Data Manipulation Language commands in SQL −

• SELECT − it retrieves certain records from one or more tables.

• INSERT − it creates a record.

• UPDATE − it modifies records.

• DELETE − it deletes records.

Following are various DCL or Data Control Language commands in SQL −

• GRANT − it gives a privilege to user.

• REVOKE − it takes back privileges granted from user.

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

A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

True.

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.

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.

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

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

SQL functions have the following uses −

• Performing calculations on data

• Modifying individual data items

• Manipulating the output

• Formatting dates and numbers

• Converting data types

LOWER, UPPER, INITCAP

The MOD function returns the remainder in a division operation.

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

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.

The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

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

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)

It returns the first non-null expression given in the parameter list.

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.

• Using CASE expression

• Using the DECODE function

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

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.

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.

By default, group functions consider all values including the duplicate values.

True.

True.

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

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;

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;

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

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.

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

True.

True.

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

IN, ANY, ALL.

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.

The INSERT INTO statement.

True.

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.

False. INSERT statement allows to add rows to a table copying rows from an existing table.

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.

All the rows in the table are modified.

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

False. The DELETE statement is used for removing existing rows from a table.

All the rows in the table are deleted.

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

True.

True.

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.

True.

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

False. A DROP TABLE statement cannot be rolled back.

The ALTER TABLE statement.

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.

True.