SQL Interview Questions – Set 02

by

What do you understand by case manipulation functions?

Case manipulation functions are the functions which convert the data from the state in which it is already stored in the table to upper, lower or mixed case.

Case manipulation function can be used in almost every part of the SQL statement.

Case manipulation functions are mostly used when you need to search for data, and you don?t have any idea that the data you are looking for is in lower case or upper case.

What is Left Join in SQL?

The left join is used to retrieve rows which are common between the tables and all rows of the Left-hand side table. It returns all the rows from the Left-hand side table even though there are no matches on the Right-hand side table.

What are the syntax and use of the COALESCE function?

The syntax of COALESCE function:

COALESCE(exp1, exp2, …. expn)
The COALESCE function is used to return the first non-null expression given in the parameter list.

What is a constraint? Tell me about its various levels.

Constraints are the rules and regulations which are applied to the table column which enforces yours to store valid data and prevents users to store irrelevant data. There are two levels :

  1. column level constraint
  2. table level constraint

What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. RDBMS stores the data into the collection of tables and links those table using the relational operators easily whenever required. It facilitates you to manipulate the data stored in the tables by using relational operators. Examples of the relational database management system are Microsoft Access, MySQL, SQLServer, Oracle database, etc.

What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate() which is used to return the current timestamp.

What are the subsets of SQL?

There is three significant subset of the SQL:

  1. Data definition language (DDL):DDL is used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc.
  2. Data manipulation language (DML):DML is used to manipulate already existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.
  3. Data control language (DCL):DCL is used to control access to data in the database and includes commands such as GRANT, REVOKE.

What is a foreign key?

A foreign key is specified as a key which is related to the primary key of another table. A relationship needs to be created between two tables by referencing foreign key with the primary key of another table. Foreign key acts like a cross-reference between tables as it refers to the primary key of other table and the primary key-foreign key relationship is a very crucial relationship as it maintains the ACID properties of database sometimes.

What is Clustered Index in SQL?

Clustered Index:

The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index. The Clustered index is the only index which has been automatically created when the primary key is generated. If moderate data modification needed to be done in the table then clustered indexes are preferred.

Which are the different case manipulation functions in SQL?

There are three case manipulation functions in SQL:

  • LOWER: converts character into Lowercase.
  • UPPER: converts character into uppercase.
  • INITCAP: converts character values to uppercase for the initials of each word.