SQL Questions for Developers

Below are some questions of SQL for Developers and Testers

What is the difference Between Function and Procedure?

i) Functions are used in cases where it must return a value .
ii)Functions can be called from sql statements where as procedures can not be called, but functions with DML statements can not be called in query . iii)Functions are used generally in computation where as procedures are used in implementing business logic.
 iv)Functions return only 1 value but procedures can return multiple values.
v)Procedures support differed name resolution where as functions don't .
vi)Stored procedures always return integer value by default 0 where as functions return type could be scalar or table or table values .
vii)Stored procedures are per-complied execution plan where as functions are not .

Can we have DML statements in function ?

Yes , we can but not in select query .

Write a query to get 2nd highest salary from an employee details table?

select max(b.SALARY) from EMPLOYEE_DETAILS AS b where b.SALARY (select max(a.SALARY)  from EMPLOYEE_DETAILS AS a);

Write a sql query to get nth or 8th highest salary?

SELECT *
FROM Employee Emp1 WHERE 8 = SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

What is RDBMS ?

RDBMS is a database management system based on relational model defined by E.F.Codd. Data is stored in the form of rows and columns. The relations among tables are also stored in the form of the table.  Features: - Provides data to be stored in tables
- Persists data in the form of rows and columns
- Provides facility primary key, to uniquely identify the rows
- Creates indexes for quicker data retrieval
- Provides a virtual table creation in which sensitive data can be stored and simplified query can be applied.(views)
- Sharing a common column in two or more tables(primary key and foreign key)
- Provides multi user accessibility that can be controlled by individual users

What is Coalesce function and what is the use of it?

The SQL Coalesce function returns the 1st non-null value from the argument list.

Also, it evaluates integer first, and then the character, as below example will return 5.
SELECT COALESCE(NULL, NULL, NULL, 'test', 5,NULL);

Sql find common elements between 2 table and their count?

As below example : Table A has a,b,c and Table B has b,c,d then return should be a1, b2,c2,d1 Sql Union operator can be used for same , A union B should return above result.

What is the difference between varchar, Nvarchar and varchar2 ?

Varchar stores data 1 byte per character and varchar stores upto 8000 character.Varchar data type can store non-Unicode string data.
Nvarchar stores unicode code and 2 byte per charcter, so it can store total 4000 character.
Varchar2 is a oracle standard for varchar in ANSISql.Extra spaces in varchar 2 are truncated where as in varchar it is kept to right side. In Varchar both null and empty String are identified separately where as in varchar2 it is same.

What steps are part of each sql query gets executed?

Previous
Next Post »

Pages