Use of LIKE Operator in SQL

·         Use of LIKE Operator:

--   Using LIKE Operator which prints empname starts from r

select * from Employee where EmployeeCode  LIKE 'r%'

--   Using LIKE Operator which prints empname ends with h

select * from Employee where EmployeeCode LIKE '%d'

--   it shows the names starts with m and r.

select *from employee where EmployeeCode LIKE '[m-r]%'

--   Using LIKE operator which prints empname which contains ra in their name

select * from Employee where EmployeeCode LIKE '%ra%'

--THE LIKE operator which prints empname which Doesnt contains ra in their name

select * from Employee where EmployeeCode NOT LIKE '%ra%'

DATE PART function Examples in SQL

·         DATE PART function Examples:

select getdate() as Currentdate

--   Returns current date.

select datepart(month,getdate()) as monthnumber

--   Returns month number(m,mm)

select datename(month,'2/12/2008')as monthname  

--   Returns name of the month.

select datepart(m,0),datepart(d,0),datepart(yy,0)

--   returns default date 1 jan 1900

select DATEPART(ww,'jan 24, 2008')

--   returns no of weeks.(ww/wk)

select DATEPART(dw, '2/5/2009')

--   returns day of the week considers sunday=1 and saturday=7

select DATEPART(yy,'12/29/80')

--   returns year....50 it shows 1950

select DATEPART(qq, '10/29/2009')

--   returns the qurter number in a year for present date (q).

select DATEPART(dy, '1/5/2008')

--   Returns the day of year i.e from 1 to 365 (dy/y)

select DATEPART(dd, '12/29/2008')

--   Returns the specified date.(dd/d)

select DATEPART(hh, '12/29/2008 19:25:45:44')

--   returns no. of hours.

select DATEPART(mi, '12/29/2008 19:25:45:44')

--   returns no.of minutes.(mi/n)

select DATEPART(ss, '12/29/2008 19:25:45:44')

--   returns no.of seconds.(ss/s)

select DATEPART(hh, getdate())

--   returns no.of miliseconds.(ms)

Some SQL Queries asked in Interview.

Query for getting column count of Table in SQL

select  COUNT(*) from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='EmployeeMaster')

Find same columns within two tables

select b.name

from sys.tables a, sys.columns b

where a.name = 'Employeemaster'

and a.object_id = b.object_id

INTERSECT

select b.name

from sys.tables a, sys.columns b

where a.name = 'SalaryMaster'

and a.object_id = b.object_id

Interchange the values of Gender column/Field.  Wherever ‘F’ is there in the Gender column/field it should be changed to ‘M’ and wherever ‘M’ is there is the gender column/field it should be changed to ‘F’.

UPDATE

Employee

SET

gender  =

(

CASE

WHEN

(gender = 'M')

THEN

'F'

WHEN

(gender = 'F')

THEN

'M'

END

)      

Write a query to make exact replica of an existing table EMP in the same database

select * into employee_replica from employee

Finding the nth highest salary of an employee.

- To find the highest salary as:-

Select max (Salary) from Employee

-To find the 3rd highest salary as:-

Select min (Salary) from Employee where Salary in

(Select distinct top 3 Salary from Employee order by Salary desc)

-To find the nth highest salary, replace the top 3 with top n (n being an integer 1, 2, 3 etc.)

Select min (Salary) from Employee where Salary in

(Select distinct top n Salary from Employee order by Salary desc)

Simple Cursor Example in SQL

·         Cursors

 

DECLARE @EmployeeCode varchar(10)

DECLARE @EmployeeDesc varchar(10)

DECLARE c3 CURSOR READ_ONLY   --define or create new cursor.

FOR select EmployeeCode,EmployeeDesc from Employee

open c3       --opens and populates cursor by executing  select statement..

FETCH NEXT FROM c3      --retrives a row from cursor.

INTO @EmployeeCode,@EmployeeDesc

WHILE @@FETCH_STATUS=0        --will be 1 till it gets the record else returns 0

BEGIN

               

                print @EmployeeCode + ' ' + @EmployeeDesc

                        FETCH NEXT FROM c3

                INTO @EmployeeCode,@EmployeeDesc            

               

END

CLOSE c3

DEALLOCATE c3   --Destroys the cursor and releses Resources

Statements for declaring the cursor using join

Declare @EmployeeCode varchar(10),@EmployeeDesc varchar(10);

DECLARE cursor1 cursor

FOR

SELECT Employee.EmployeeCode,Employee.EmployeeDesc

FROM Employee JOIN Department ON employee.DeptCode=Department.DeptCode

open cursor1

FETCH NEXT FROM cursor1

INTO @EmployeeCode,@EmployeeDesc

WHILE @@FETCH_STATUS=0

BEGIN

            print Cast(@EmployeeCode AS varChar(max))+ ' '+ @EmployeeDesc

             

        FETCH NEXT FROM cursor1

        INTO @EmployeeCode,@EmployeeDesc

END

CLOSE cursor1;

DEALLOCATE cursor1;

·         USE OF UPDATE STATEMENT WITHIN CURSOR

DECLARE c3 CURSOR             --define or create new cursor.

FOR SELECT EmployeeCode,EmployeeDesc

FROM  Employee 

open c3                 --opens and populates cursor by executing select statement..

FETCH NEXT FROM c3      --retrives a row from cursor.

WHILE @@FETCH_STATUS=0

BEGIN      

       

        update  Employee set  Employee.EmployeeDesc = 'Manager' where EmployeeCode='aaa'       

       

                FETCH NEXT FROM c3

               

END

CLOSE c3

DEALLOCATE c3