LIKE operator [wildcard characters]

 

Wildcard	Description
-    (Underscore)	Matches any single character
%	Matches a string of one or more characters
[ ]	Matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).
[^]	Matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).

                                                 Figure:suryagroops_0005 (tbl_emp)

Examples

                                               

Emp_id	Emp_name
1	Suryagroops
2	Suresh
3	Mayank dixit
4	Suryagroops
5	Ramesh
6	Ramesh
7	Shalini
8	Sushil

       

Find all Emp_Name ends with ‘sh’

SELECT        emp_name

FROM            tbl_emp

WHERE        (emp_name LIKE '%sh')

Result-

Emp_Name

Suresh

Ramesh

Ramesh

Find all Emp_Name includes ‘sh’ anywhere in the name

SELECT        emp_name

FROM            tbl_emp

WHERE        (emp_name LIKE '% sh %')

Result-

Emp_Name

Suresh

Ramesh

Ramesh

Shalini

Sushil

Important Queries

Emp_id	Emp_name	Emp_mob	Emp_bonus
1	Suryagroops	9532771738	500
2	Suresh	9452358456	1000
3	Mayank dixit	9896564556	1500
4	Suryagroops	9878522132	9000
5	Ramesh	8004677781	5000
6	Ramesh	8785455852	3000

                                                                Figure:suryagroops_0004 (tbl_emp)                                      

Calculating Bonus for each employee

SELECT        Emp_Name, SUM (Emp_Bonus)
FROM            tbl_emp
GROUP BY   Emp_Name;

Result-

                                                                 

Emp_name	Emp_bonus
Suryagroops	9500
Suresh	1000
Mayank dixit	1500
Ramesh	8000

Now, we want to find out Emp_Name who get Emp_Bonus more than 2000

SELECT               Emp_Name,SUM(Emp_Bonus)
FROM                  tbl_emp
GROUP BY         Emp_Name
HAVING SUM    (Emp_Bonus) > 2000;

Result-

                                                 

Emp_name	Emp_bonus
Suryagroops	9500
Ramesh	8000

Some Important Queries

How can I get system date in SQL

                                            

 SELECT GETDATE () AS DateTime;

                                           

 Result-

                                      

DateTime

31-Dec-13 8:24:12 AM

COUNT Number of Employee in tbl_emp

 SELECT     COUNT (Emp_name) AS Employee_Number
 FROM         tbl_emp;

             

 Result- 

                       

Employee_Number

6

  

Find Out Year from GETDATE function

SELECT YEAR (GETDATE ()) AS Year;

Result-

Year

2013

Find Out MONTH from GETDATE() function

SELECT MONTH (GETDATE ()) AS Month;

               

Result- 

                                                 

Month

12