Top 25 SQL Interview Questions and Answers for freshers

1. What is SQL?
SQL stands for Structured Query Language, and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Basically, it is a database language that is used for the creation and deletion of databases, and it can be used to fetch and modify the rows of a table and also for multiple other things.

2. What are the keys in SQL?

       A Key is a data item that exclusively identifies a record

SQL Server supports various types of keys, which are listed below:

1. Candidate Key
2. Primary Key
3. Unique Key
4. Alternate Key
5. Composite Key
6. Super Key
7. Foreign Key

primary key
A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL. 



unique key
A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.
  
 

foreign key

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.



Composite key
A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Super Key

A superkey or super-key is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.

Alternate keys

Contain one or more columns whose combined values uniquely identify every row in a table.

candidate key

A candidate key is a set of attributes (or attribute) which uniquely identify the tuples in relation or table

4. What is a * in SQL? 
 In SQL * means All record, not only in SQL in other programming languages * is called as wild card character which means all present record.

SELECT * FROM TABLE_NAME;

5. What does <> mean SQL?

             <> is standard ANSI SQL and stands for not equal or !=

6. What are triggers in SQL?

      A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

   7.  What is Normalisation in SQL?

              normalization is a way of organizing the data in the database. 

   8.  What is 1nf 2nf 3nf?

  Types of Normal Forms

A relation is in 1NF if it contains an atomic value.

 2NF. A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.

 3NF. A relation will be in 3NF if it is in 2NF and no transition dependency exists.

9. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

10. What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

11. What is a Join? List its different types.

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

There are different types of joins are below:

• INNER JOIN: The INNER JOIN keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

• LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

      

• RIGHT JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

• FULL JOIN: The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

• SELF JOIN: A self JOIN is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

12. What is stored procedure?
stored procedures are used to group one or more Transact-SQL statements into logical units. The stored procedure are stored as named objects in the SQL Server Database Server.

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

13. What is Function in SQL?
Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements. Functions compile every time. Functions must return a value or result. Functions only work with input parameters.




14. What is the difference between function and stored procedure?
 The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters.

15. What is the difference between procedure and trigger?
Trigger and Procedure both perform a specified task on their execution. The fundamental difference between Trigger and Procedure is that the Trigger executes automatically on occurrences of an event whereas, the Procedure is executed when it is explicitly invoked.

16. What are the different types of triggers?

In SQL Server we can create four types of triggers Data Definition Language (DDL) triggers,

 Data Manipulation Language (DML) triggers,

 CLR triggers,

 and Logon triggers.

17. What are instead of triggers?

An INSTEAD OF trigger is a trigger that allows you to skip an INSERT , DELETE , or UPDATE statement to a table or a view and execute other statements defined in the trigger instead.

18. What is compound trigger?

A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points: Before the firing statement. Before each row that the firing statement affects. After each row that the firing statement affects.

19. What is the main purpose of triggers in database?

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

20. What is an index in a database?

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

21. What is the view in SQL and its type?

 A view is a virtual table based on the result-set of an SQL statement.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

There are 2 types of Views in SQL:

 Simple View and Complex View. Simple views can only contain a single base table. Complex views can be constructed on more than one base table. In particular, complex views can contain: join conditions, a group by clause, a order by clause.

22. What is SQL injection used for?

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

23. What is the SQL command?

SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data

24. What is the aliase in SQL?

SQL aliases are used to give a table, or a column in a table, a temporary name.

SELECT column_name AS alias_name
FROM table_name;

25. What is union operator?

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;