Basics of SQL Database Part 4

 SQL Operators - Filters

Where clause

- Used to specify a condition while fetching the data from a single table or by joining with multiple tables 

- Not only used in the select statement but it is also used in the update, delete statement etc.

syntax: select * from table name where condition;

Exp: select * from emp where emp_id= 101;

SQL Operators - Logical

AND: (5<2) and (5>3) - False

OR.  : (5<2) or (5>3) - True

NOT: not (5<2) - True

Exp: select * from emp where first_name ='steven' and salary =15000;

         select * from emp where first_name ='steven' or salary =15000;

         select * from emp where first_name ='steven' and salary !=15000;

SQL Operators - Comparison

> : Greater than

>= : Greater than or Equal to

< : Less than

<= : Less than or Equal to

<> or ! : Not Equal to

= : Equal to

SQL Operators - Special

Between: Checks an attribute value within range.

Like: Checks an attribute value matches a given string pattern.

Is Null: Check an attribute value is Null.

In: Checks an attribute value matches any value within a value list.

Distinct: Limits values to unique values.

Exp: select * from emp where salary between 1000 and 2000;

        select * from emp where first_name like '%steven%';

  

        select * from emp where salary is null;

        select * from emp where salary in ( 1000, 2000, 3000);

       select distinct (first_name) from emp;

SQL Operators - Aggregation

avg() : Returns the average value from specified columns

count() : Returns number of table rows

max() : Returns largest value among the records

min() : Returns smallest value among the records

sum() : Returns the sum of specified column values

Exp: select avg(salary) from emp;

         select count(*) from emp;

    

         select min(salary) from emp;

        select max(salary) from emp;

        select sum(salary) from emp;

SQL Group By clause

- Arrange identical data into groups

Exp: select max(salary), dept_id from emp group by dept_id;

SQL Having clause

- Used with aggregated functions due to its non-performance in the where clause.

- Must follow the group by clause in a query and must also precede the order by clause if used.

Exp: select avg(salary), dept_id from emp group by dept_id having count(dept_id)>=2;

SQL order by clause

- Used to sort the output of the select statement

- Default is to sort in ASC (ascending)

- Can sort in reverse (descending) order with DESC after the column name.

Exp: select * from emp order by salary DESC;

 #data #sql #like #operator #day4

Mail id: arpitmishra1629.ftp@gmail.com

Contact no: 8127430702

Linkedin: https://www.linkedin.com/in/arpit-mishra-9319b5131