60 Top SQL Interview Question for Experience and Fresher Professional

SQL Interview Question for Experience and Fresher

1) Define Key and It's Type?

• Primary Key is table-column which consist unique value and cannot be NULL. A table can have only one primary key in it. Primary Key is in sorted Order by Default.

• Unique Key is a table-column which consist unique value and it can be NULL. A Database Table can have many Unique Column in it.

• Foreign Key is a table-column or group of Table-column which set the relation between two table. Foreign Key of one table have the reference to the Primary key of another Table.

• Alternate Key is table Column or Group of column which identify each row in a table uniquely and not be NULL, But it is not a primary key. Alternate Key is Unique Identifier in table. In 

• Super Key is set key of one or more table-column which identify the table row uniquely. Super Key set in below table is {REGNO}, {ROLLNO}, {REGNO,ROLLNO}     

ID	REGNO	ROLLNO	FULLNAME	DOB
1	2020001435	10011	M Kumar	07-04-2003
2	2020001437	10012	MOHAMMED ISHAQ  H	14-06-2002
3	2020001478	10013	ANBUSELVAM S	18-06-2004
4	2020001487	10014	MARTINPRIYADOSS J	08-03-2004

• Candidate Key is a column or group of column which consist unique value and not be Null. We select primary key from the group of candidate Key. We can say candidate Key is minimal set of Super Key with no duplicate record. {REGNO}, {ROLLNO}

Points To Remember :

1. Primary Key can act as a Unique Key, but Unique Key can't be Primary key because it Can contain Null Value. 
2. Candidate key is Minimal set of Super Key with no redundant record.

---

2)  How to use pattern matching operator in SQL Query?

LIKE is SQL Pattern matching operator which is used with Where Clause. '%' and '_' is used with LIKE.

• '%',  Percent is used for 0, 1 or multiple Characters.
• '_' , Underscore is used for single character.  

Consider Student_Table and Queris using Like Operator

FullName	DOB
M KUMAR	07-04-2003
MOHAMMED ISHAQ	14-06-2002
ANBUSELVAM S	18-06-2004
MORTINPRIYADOSS JAMES	08-03-2004
JAGAN J	10-04-2003
NAVEENKUMAR	02-06-2004
HARIKRISHNAN R	15-11-2003
VIJAY K	14-11-2002
SUTHAKAR	23-03-2004
KATHIRESAN P	15-11-2007

For Example : 

• SELECT  *  FROM Student_Table  WHERE  FullName  LIKE  "MO%"  (MO% it means FullName start with MO) 

FullName	DOB
M KUMAR	07-04-2003
MOHAMMED ISHAQ  H	14-06-2002

• SELECT * FROM Student_Table  WHERE  FullName  LIKE  "%MAR"  (%MAR it means FullName ends with MAR)

FullName	DOB
M Kumar	07-04-2003
NAVEENKUMAR	02-06-2004

• SELECT * FROM Student_Table  WHERE  FullName  LIKE  "%PRIYA%"  (%PRIYA% it means FullName which contain PRIYA)

FullName	DOB
MORTINPRIYADOSS JAMES	08-03-2004

---

3) What is Clause in Sql?

Clause in SQL is condition which limit the fetched record by applying condition. Its is like Filter some record from set of table record.

There are Five SQL Clause.

• ORDER BY Clause
• TOP Clause
• WHERE Clause
• GROUP BY Clause
• HAVING Clause 

Consider Student_Table for writing SQL Clause Example:

FullName	DOB	MARKS
M KUMAR	07-04-2003	60
MOHAMMED ISHAQ	14-06-2002	63
ANBUSELVAM S	18-06-2004	75
MORTINPRIYADOSS JAMES	08-03-2004	56
JAGAN J	10-04-2003	73
NAVEENKUMAR	02-06-2004	86
HARIKRISHNAN R	15-11-2003	45
VIJAY K	14-11-2002	32
SUTHAKAR	23-03-2004	94
KATHIRESAN P	15-11-2007	87

WHERE Clause Example:

⇛ SELECT * FROM Student_Table  WHERE  YEAR(DOB) = 2007

⇛ SELECT * FROM Student_Table  WHERE  MARKS BETWEEN 73 AND 90

ORDER BY Clause Example

⇛ SELECT * FROM Student_Table  ORDER BY MARKS DESC

⇛ SELECT * FROM Student_Table  WHERE  MARKS > 70 ORDER BY MARKS DESC

GROUP BY Clause Example

⇛ SELECT MARKS, COUNT(*) FROM Student_Table  GROUP BY MARKS

⇛ SELECT * FROM Student_Table WHERE YEAR('DOB') >2003  GROUP BY MARKS 

HAVING Clause Example

⇛ SELECT MARKS, COUNT(*) AS TOTALCOUNT FROM Student_Table  

GROUP BY MARKS HAVING TOTALCOUNT > 5

---

4) What is the Main Difference between WHERE Clause and HAVING Clause ?

• WHERE Clause and HAVING Clause is especially for filtering the set of record from the table based on the condition. 
• WHERE Clause cannot be used with SQL Aggregate Function Like COUNT(), SUM(), AVG() where HAVING Clause can be used.
• HAVING Clause filter the set of record from the group row not on Individual row, WHERE Clause filter the record Set from the Column row.

The Main difference WHERE Clause and HAVING Clause you can understand using with GROUP BY in the same query.

Query Example: 

⇛ SELECT * FROM Student_Table WHERE MARKS < 80 GROUP BY MARKS HAVING TOTALCOUNT > 1

In the above query first Student_Table record will be filter using WHERE Clause, then it will be grouped and again it will be filtered on the group row using HAVING.

---

5) What is Constraint?

Constraint are the rules and limitation we apply while creating and altering table column in the database which restrict the unwanted data.

Some Constraint we apply while creating the Table is below for your reference.

• NOT NULL this constraint ensure that NULL value can't be inserted.
• DEFAULT will automatically assign the value if no value has been assigned.
• UNIQUE this will ensure that all the entered value in the column will be unique.
• INDEX it created the index based on the column for faster retrieval of Data from the database.
• PRIMARY KEY Ensure that each record in the table must be unique and not null.   

---

6) What is View ?

View is Virtual Table which consist of rows and column like real table. We create view by selecting column field from one or more Database table.

It donot store any data of its own but it just display the data of other table.

View help us in data Security. Using View we can allow only limited access of data from the various table.

Below is the example for creating view :

CREATE VIEW `StudentInfo` AS SELECT `st.firstName`,`st.mobileNo`, 

`st.gender`,`sa.city`,`sa.pincode` FROM studentTable as st 

JOIN studentAddress as sa on st.id = sa.student_id;

---

7) Difference Between DELETE and TRUNCATE commands ?

Delete Command will delete the rows from the Database Table based on certain criteria and conditional Parameter Set. Delete statement allow us to perform Commit and Rollback.

Truncate Command will truncate all rows from the database table. It Cannot be rolled Back.

---

8) What is ACID property in a Database?

ACID stands for Atomicity, Consistency, Isolation, Durability.

Atomicity Ensure that either all the transaction must be succeeds or none in the database, It must not be in partial State. Atomicity ensures that partial Transaction must not happen in Database it must complete or  none of it.

Consistency Ensure that database transaction must happen in isolation. Here Isolation means concurrent database transaction process must not run.

Isolation Database Property ensure that once one transaction get complete then only other Database Transaction must start. Isolation means at a time only one Transaction process must execute. No Concurrent transaction process must run.

Durability Property ensures that once Transaction Process is completed successfully then in any case (Like System failure, power cut etc. ) the database must not get affect. Durability of Database must be maintain in all the condition.