Important Sql complex queries interview questions

Introduction: In this tutorial, I am going to explain the SQL complex queries which is asked in every interview questions. So let's start today's tutorial. So let's start SQL complex queries fo practice with answers.


Important Sql complex queries interview questions


1. To add new 
Alter table employee add gender varchar(5)

2.Drop a column
Alter table employee drop column gender

                       Some Ms. Sql commands with examples

3.Or operator  in SQL
SELECT * from employee where employeeid=or employeeid=1

4.IN function in SQL 
SELECT * from employee where employeeid in(2,3)

5. Find record Not in SQL table using  IN function
SELECT * from employee where employeeid not in (1,2)

6.Between function in SQL
SELECT * from employee where employeeid between 1 and 2

7.Select commands with order by clause
SELECT * from employee order by name 
SELECT * from employee order by name desc
SELECT * from employee order by name asc

8.Max Function
SELECT max(name) from employee
SELECT max(salary) from employee

9.Max Function
SELECT min(salary) from employee
SELECT min(name) from employee

10.Avg Function
SELECT AVG(salary) from emp

11.LEFT Function
SELECT LEFT('Manish',3)
Result:Man


12.RIGHT Function
SELECT RIGHT('Manish',3)
Result:ish

13.CHARINDEX Function- Is use to find the index(position) of any character
SELECT CHARINDEX('i','Manish')
Result:4

14.LEN Function- Is use to find the length of character
SELECT LEN('Manish')
Result:6


15.GETDATE ()
Returns a datetime data type which contains current system data and time: 2018-09-24 09:51:36.568.
SELECT GETDATE()

16.Use of AND Operator
SELECT * from employee where employeeid=2 and employeeid=5
SELECT * from employee where employeeid=2 and salary=600

17.Use of OR Operator
SELECT * from employee where employeeid=2 or employeeid=5
SELECT * from employee where employeeid=2 or salary=4000

18.Use of IN Operator
SELECT * from employee where employeeid in (2,5,8)

19.Use of Between Operator
SELECT * from employee where employeeid between 3 and 7

20.Use of WildCard Operator
SELECT * from employee where name not like 'a%'
SELECT * from employee where name like '%a'
SELECT * from employee where name like '%m%'
SELECT * from employee where name like '_a%'
SELECT * from employee where name like '%a_'
SELECT * from employee where name like '[a,b]%'
SELECT * from employee where name like '[a-p]%'

21.Use of SUBSTRING() Function
SELECT SUBSTRING('Mohan',1,3)
Result:Moh





Read More

Easy Way:How to find 3rd higest salary in sql server

Find 3rd highest salary in SQL server

In this tutorial, I am going to show how we can find the 3rd highest salary in SQL Server. In below, the method I want to show you a very easy method so it will help to understand better. The method will take three step so please understand every step carefully.




create table EmployeeSalary 
( 
   ID int identity primary key, 
   Name varchar(20), 
   Salary Decimal  
) 

Insert into EmployeeSalary(Name,Salary)values('Ram',21000) 
Insert into EmployeeSalary(Name,Salary)values('Jhon',50000) 
Insert into EmployeeSalary(Name,Salary)values('Simant',40000) 
Insert into EmployeeSalary(Name,Salary)values('Tom',35000) 
Insert into EmployeeSalary(Name,Salary)values('Alia',25000) 
Insert into EmployeeSalary(Name,Salary)values('Mohan',22000) 
Insert into EmployeeSalary(Name,Salary)values('Raj',23000)





#Step 1:
In this query, I execute to show the record of  EmployeeSalary table



select
 * from EmployeeSalary



How to find 3rd higest salary in sql server
3rd higest salary in sql server
                                                         





#Step 2: Now, Firstly find the top 3 salaries with descending order


Select  top 3 Name,Salary from EmployeeSalary order by salary desc



How to find 3rd higest salary in sql server
3rd higest salary
                                



Let suppose the above record is a table, now we need to find a salary from current table record.

#Step 3: Find the top 1 record in ascending order from the current record 




select top 1 Name,Salary from (select  top 3 Name,Salary from EmployeeSalary order by salary desc) as T  order by salary asc


How to find 3rd higest salary in sql server
3rd highest salary 
                                


Finally, we find the 3rd highest salary from SQL Server table


READ MORE - KNOW MORE





Read More

OOPS Logical questions in c#,Interview Question


Introduction:- Today I am here with OOPS Logical questions in c#,which is mostly ask in every interview question. It will help you to crack C# OOPS interview.


OOPS Logical questions in c#,INTERVIEW QUESTION
OOPS Logical questions in c#



Q1:What is object ?
An object is a instance of class and also it is reference pointer of a class. It allocated the memory to access the member of class. If we want to access the class member we need to create object of that particular class.

  Syntax for Object
  ClassName objectname=new ClassName();
  e.g Employee emp=new Employee();

In the above syntax object emp is a reference pointer for the Employee class.

Q.2: What is naming convention?
Naming convention is a set of or a business logic rule by which we chose the character sequence to be used for function,class,textbox or any other entities in our source code so that later on we can easily  remember these sequence.

Q.3:Can we create class name with number?
No,We can't start class name with number only we can use number in between i.e.

Public class Emp123
{

}

Q.4:Can we use special character to create the class?
Yes only underScore (_)

Q.5:By default class follow which access modifier?
Internl

Q.6:Types of Access specifier/modifier ?
There are 5 type of access modifier.
1.Public
2.Private
3.Protected
4.Internal
5.Protected Internal

Q.7: Can we create class as private/protected/protected internal ?
No,By default class is Internal and you can mark class as Public only.

Q.8: By default class members are, means access modifier?
Private

Q.9: Difference b/w public ,private and internal access modifier?
Difference Public/private/Internal/Protected/Protected internal
1.Public:                 Accessible in any project, but you have to add the reference
2.Private:                Member can be accessed in same class only.
3.Internal:               Same Project.same Library,same assembly only.
4.Protected:            Only available in child class.
5.Procted Internal:   It behaves like procted + internal

Q.10: Diffrence between protected and protected internal ?

Q.11:Can we mark method as a sealed.
Yes.

Q.12:Is it possible to overload static constructor
No its not.

Q.13:Why object of abstract class can’t be instantiate
An abstract class has a protected constructor (by default) allowing derived types to initialize it.
An abstract type is defined largely as one that can't be created. You can create subtypes of it, but not of that type itself. The CLI will not let you do this.

Q.14:Does Enum Supports Inharitence
Enums do not supports inheritance since they are value type and therefor are sealed.Enum is like struct.it is having the nature of sealed class.so it doesn't support inheritance.

Q.15:Where does the value of variables in structure get stored in memory ?
Its store on Stack because Structure are value type.

Q.16:Can we use virtual keyword with functions in interface
This is no need to use virtual keyword with functions of interface as it is by default made to override.  


Q.17:Is it possible to change access specifier of members of interface.
No,We cannot make change in method of interface just why because interface is publicly exposed for construction,so interface methods are public by default.
If you want to change access specifier of members may you have to go for abstract class instead of interface.





Read More

Diffrence between protected and protected internal in c#

In this tutorial we will talk about actual diffrence between protected and protected internal.

Diffrence between protected and protected internal

Protected:

1.In case of protected access is limited to the containing class or types derived from the containing class.

2.This can be accessed within same project as well as outside(by inheritance)   of the project,only access in child class(by inheritance) simply call the   method.

3.The protected member can not be accessed by creating the object of class.



Protected Internal:

1.Access is limited to the current assembly or type derive from the containing class.

2.This can also be accessed within same as well as outside the project but also can be accessed as internal.

3.Protected internal member can be accessed by creating the object in same project only.

4.It behaves like Protected+Internal.


Read More