SQL Interview Questions and Answers

What is DBMS?

DBMS refers to the Database Management System. It is a set of programs that enables you to store, modify, and retrieve the data from a database.
See more at: Database overview.

What is RDBMS?

RDBMS refers to the Relational Database Management System. It is a database management system that is based on the relational model as introduced by E. F. Codd. All modern database systems like MS SQL Server, Oracle, IBM DB2, MySQL, and Microsoft Access are based on RDBMS.
See more at: Database overview.

What is SQL?

SQL stands for Structured Query Language. SQL is a computer language for accessing databases. It is used to store, manipulate and retrieve the data stored in a database
See more at: SQL Overview.

What are various DDL commands in SQL?

1. CREATE – It is used to create database objects like table, view etc.
2. ALTER – It is used to alter the structure of the existing database object.
3. DROP – It is used to delete the existing objects from the database.
4. TRUNCATE – It remove all records from a table, including all spaces allocated for the records will be removed.
5. RENAME – It is used to rename an existing database object.

What are various DML commands in SQL?

1. SELECT – It is used to retrieve the records from the database.
2. INSERT – It is used to create a record.
3. UPDATE – It is used to modify an existing record.
4. DELETE – It is used to delete all records from a table, the space for the records remain.
5. MERGE– It is used to insert or update a record.
6. CALL – call a PL/SQL or Java subprogram.
7. LOCK TABLE – It is used to control concurrency.

What are various DCL commands in SQL?

1. GRANT – It gives privileges to user.
2. REVOKE – It withdraw access privileges from a user given with the GRANT command.

What is a primary key?

The PRIMARY KEY constraint is used to uniquely identify each row in a table. A PRIMARY KEY must contain unique values and it can’t contain a null value. A table can have only one primary key. We can use multiple columns or fields to define a primary key, such primary key is known as composite key.

What is a foreign key?

The FOREIGN KEY is used to define a relationship between two tables and a FOREIGN KEY in one table points to the PRIMARY KEY in another table.
See more at: SQL FOREIGN KEY.

What is a SQL UNIQUE Constraint?

The UNIQUE constraint is used to uniquely identify each row in a table. It is like primary key but it can contain one null value and a table can have more than one UNIQUE constraint.

Explain SQL DISTINCT keyword.

The DISTINCT keyword is used to retrieve the unique records by eliminating the all duplicate records.
See more at: SQL DISTINCT keyword.

Explain SQL LIKE operator.

The LIKE operator is used in a WHERE clause to retrieve all records of a table whose specified column values match a specified pattern. The percent sign (%) and underscore (_) are two wildcards used in the LIKE operator pattern. The underscore represents a single character and percent sign represents the multiple characters.
See more at: SQL LIKE operator.

Explain SELECT TOP in SQL.

The SELECT TOP statement is used to retrieve the top specified number of rows from a table.
Syntax: 
SELECT TOP n * FROM tableName
Where n is the number of rows.
See more at: SQL SELECT TOP.

Explain SQL ORDER BY Clause.

The ORDER BY Clause is used to sort the results either in ascending or descending order based on one or more columns.
Oracle and some other database sorts query results in ascending order by default.
See more at: SQL ORDER BY Clause.

Explain SQL GROUP BY clause.

The GROUP BY clause is used to group the identical data by one or more columns. It is used with the aggregate functions in the select statement.
See more at: SQL GROUP BY clause.

Explain SQL HAVING clause.

The HAVING clause is used with the GROUP BY clause and filter the groups created by the GROUP BY clause.
See more at: SQL HAVING clause.

Explain SQL ALIAS Statement.

The ALIAS statement is used to temporarily rename a table or column in a SQL statement.
See more at: SQL ALIAS Statement.

Can you sort a column using a column alias?

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

What is a join?

The JOIN are used to combine the records from two or more tables.

Explain SQL INNER JOIN.

The INNER JOIN returns the all records from the both tables for which the join condition is true. It is also known as EQUIJOIN.
See more at: SQL INNER JOIN.

Explain SQL LEFT OUTER JOIN.

The LEFT OUTER JOIN returns the all records of left table and matching records of right table. When no match is found right table columns will be return with the null values.
See more at: SQL LEFT OUTER JOIN.

Explain SQL RIGHT OUTER JOIN.

The LEFT RIGHT JOIN returns the all records of right table and matching records of left table. When no match is found left table columns will be return with the null values.
See more at: SQL RIGHT OUTER JOIN.

Explain FULL OUTER JOIN.

The FULL OUTER JOIN returns the result of the combination of left and right outer joins.
See more at: SQL FULL OUTER JOIN.

Explain SELF JOIN.

The SELF JOIN is used to join the table to itself that why it is known as SELF JOIN.
See more at: SQL SELF JOIN.

Explain CROSS JOIN.

The CARTESIAN JOIN or CROSS JOIN return the data by joining the every row of one table to every row of another table i.e it returns the Cartesian product of two tables.
See more at: SQL CROSS JOIN.

Is a NULL value same as zero or a blank space? If not then what is the difference?

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.

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.

No comments: