SQL Operators and Functions

SQL Operators And Clauses-

   As all programming language have operator for conditions in the same way SQL have following operator

 1- AND
 2- OR
 3- NOT

 Top 100+ Sql Server Interview Questions

1- AND
    Operator is used when we want both the conditions must be true.
Syntax- Select * from table_name
where condition1 and condition2 and condition3


2- OR
    Operator is used when we want only single the conditions must be true.
Syntax- Select * from table_name
where condition1 or condition2 or condition3

3- NOT
    Operator is used when we want  conditions must not be true.
Syntax- Select * from table_name
where  Not condition1

SQL Clauses

1. FROM clause

1. WHERE clause

1. GROUP BY clause

1. HAVING clause

1. SELECT clause

1. ORDER BY clause

1. TOP clause

OR Operator

    Operator is used when we want only single the conditions must be true.
Syntax- Select * from table_name
where condition1 or condition2 or condition3

select * from itemmaster where it_code like '%ab%'   or  name like '%chauhan%'

AND Operator

    Operator is used when we want both the conditions must be true.
Syntax- Select * from table_name
where condition1 and condition2 and condition3


select * from ItemMaster where it_type='Consumables' and it_cateogry='QC Type'

NOT Operator SQL

    Operator is used when we want only single the conditions must be true.
Syntax- Select * from table_name
where NOT Condition

select * from Customer where NOT city='Delhi'

Order by

    Operator is used when we want short the data  particular  format
Syntax- Select * from table_name
where condition1 or condition2 or condition3

select * from itemmaster order  by item_group



SQL Count Function
We use count to calculate the no of rows on a specific condition

Syntax

SELECT COUNT(col_name)
FROM table_name
WHERE condition;

Query

SELECT COUNT(ID)
FROM Students;

SQL Count function :-Count()

We use AVG FUNCTION to calculate the average on a specific column

Syntax

SELECT AVG(col_name)
FROM table_name
WHERE condition;

Query

SELECT AVG(Marks)
FROM Students;

SQL SUM function()

We use sum  to calculate the total of all values in column

Syntax

SELECT SUM(col_name)
FROM table_name
WHERE condition;

Query

SELECT SUM(Fee)
FROM Students;

Max Function()

MAX() Used to get the highest value of the Column

Syntax-  SELECT MAX(Column Name) from Table name

Query

  Select Max(Salary) from employeemaster


SQL MIN Function()

Min() Used to get the LOWEST value of the Column

Syntax-  SELECT MIN(Column Name) from Table name

Query
  Select MIN(Salary) from employeemaster

Between Operator

IF WE WANT TO GET VALUE IN SPECIFIC RANGE.

SYNTAX

select * from table_name  where column_name BETWEEN Value1 AND Value2

QUERY-
Select Employee name from employee_master where joining_date between '01-07-2019' and           '10-07-2019'

Having Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions like avg,max etc.

SYNTAX-
SELECT col_name
FROM table_name
WHERE condition
GROUP BY column_name(s)HAVING conditionORDER BY column_name(s);

Query-

SELECT COUNT(StudentID), State
FROM Student
GROUP BY State
HAVING COUNT(StudentID) > 20;