Database Questions | DB Questions

Database Interview Question

• Primary vs Unique Key
• Truncate vs Delete
•  Function vs Procedure
• Having clause vs Where Clause
• Query :- Find out the duplicate records in a Table
• Query :- Delete the Duplicate records
• Query :- Find the ID by using the Self Join
• SQL Injection
• Query

1):-   Primary vs Unique Key

Primary Key:-

• It is used to uniquely identified the unique records
• It doesn’t allow Null values
• PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT
• A table can have only one PRIMARY KEY Column[s]
• Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.

Unique Key:-

• It is used to uniquely identified the unique records
• It  allow Null value. But only one null value allowed.
• A table can have more then one Unique keys
• By default, Unique key is a unique non-clustered index.

2):- Truncate vs Delete

DELETE

• DELETE is a DML Command.
• We can delete one records or all records
• DELETE statement is executed using a row lock, each row in the table is locked for deletion.
• We can specify filters in where clause
• It deletes specified data if where condition exists.
• Delete activates a trigger because the operation are logged individually.
• Slower than truncate because, it keeps logs.
• Rollback is possible.

TRUNCATE

• TRUNCATE is a DDL command.
• We can not single records but can delete all records
• Cannot use Where Condition.
• It Removes all the data.
• TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
• Faster in performance wise, because it doesn't keep any logs.
• Rollback is not possible.
  
  

3):-Functions VS Procedure

Stored Procedure

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called.

Function

But Function is compiled and executed every time when it is called.

• Return Type :- Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Input/Output Parameters:- Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Calling from Each Other :- Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
• Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
• Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• We can go for Transaction Management in Procedure whereas we can't go in Function.

4):- Difference between having and where clause

• The where clause can not be used with aggregates, but the having clause can use with aggregater functions. One way to think of it is that the having clause is an additional filter to the where clause.
• WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL
• if WHERE and HAVING clause is used together, first WHERE clause is applied to filter rows and only after grouping HAVING clause is applied.

SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY

FROM Employee e,Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING avg(e.EMP_SALARY) > 7000;

Query :-

Q):- Findout the duplicate records in a Table

SELECT orderid, COUNT(*) TotalCount

FROM masterorder

GROUP BY orderid

HAVING COUNT(*) > 1

Q. In table_user.gender column change 'male to female' and 'female to male' in one SQL statement.

Ans.==> 

UPDATE table_user

SET gender =

CASE gender

WHEN 'male' THEN 'female'

WHEN 'female' THEN 'male'

ELSE gender

END

Q. Delete Duplicate Records from tbl_emp where employee name and city should not be

repeated. i.e if emp_name and city are same are same, it should be concidered duplicate

A.==>

DELETE FROM MyTable

WHERE ID NOT IN

(SELECT MAX(ID) FROM MyTable

GROUP BY emp_name, city)

Q. How will you find the duplicate records in a table? Give Query, also show number of occurrence.

A.

Select col1, count(*) as occurrenceCount From tablename

group by col1

having count(col1)>1

Q. How will you find out 7th highest salary in a table?

A.

SELECT * FROM

(SELECT ROW_NUMBER() OVER(ORDER BY sal_amount DESC) row_number, emp_id,

sal_amount

FROM Salary ) a

WHERE row_number = 7

Q):- SQL Injection

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 

SELECT top 10 * FROM NCCardHolder WHERE NCFirstname = '' OR '1'='1';

Q):- How to use ROW_NUMBER() to enumerate and partition records in SQL Server

Here’s an example table:

PersonID	FamilyID	FirstName	LastName	DateOfBirth
1	1	Joe	Johnson	2000-10-23 13:00:00
2	1	Jim	Johnson	2001-12-15 05:45:00
3	2	Karly	Matthews	2000-05-20 04:00:00
4	2	Kacy	Matthews	2000-05-20 04:02:00
5	2	Tom	Matthews	2001-09-15 11:52:00

Ans

SELECT

    [PersonID]

   ,[FamilyID]

   ,[FirstName]

   ,[LastName]

   ,[DateOfBirth]

   ,ROW_NUMBER() over (ORDER BY DateOfBirth) AS Number

FROM

    People

ORDER BY

    PersonID

PersonID	FamilyID	FirstName	LastName	DateOfBirth	Number
1	1	Joe	Johnson	2000-10-23 13:00:00	3
2	1	Jim	Johnson	2001-12-15 05:45:00	5
3	2	Karly	Matthews	2000-05-20 04:00:00	1
4	2	Kacy	Matthews	2000-05-20 04:02:00	2
5	2	Tom	Matthews	2001-09-15 11:52:00	4

SELECT

      [PersonID]

    [FamilyID]

     ,[FirstName]

     ,[LastName]

     ,[DateOfBirth]

     ,ROW_NUMBER() over(PARTITION BY FamilyID,

                        CONVERT(NVARCHAR(25), DateOfBirth, 111)

                        ORDER BY DateOfBirth AS ) TwinCode

FROM [People]

ORDER BY   PersonID

PersonID	FamilyID	FirstName	LastName	DateOfBirth	TwinCode
1	1	Joe	Johnson	2000-10-23 13:00:00	1
2	1	Jim	Johnson	2001-12-15 05:45:00	1
3	2	Karly	Matthews	2000-05-20 04:00:00	1
4	2	Kacy	Matthews	2000-05-20 04:02:00	2
5	2	Tom	Matthews	2001-09-15 11:52:00	1

TwinCode coloumn for those who have the DOB on the same day.

Note:-

• We can use multiple coloumn with partition.
• Partition used with Over.
• RowNumber used with Over