MySQL having clause with group by clause

MySQL having clause with group by clause :

Syntax :


select * from <table_name> group by <column_name> having <condition>;

Example :


select * from employee group by salary having salary>45000;

Advertisements

MySQL sorting data from table

MySQL sorting data from table :

MySQL allow us to view data from table in a sorted order. The rows retrieved from the table will be sorted in either ascending order or descending order depending on given condition.
Note : to sort data as ascending use “asc” and descending use “desc”.

Syntax :


select * from <table_name> order by <column_name> <[sorting_order]>;

Example :

Suppose you want to view data from table as salary in descending order. So you will have to write query as follows…


select * from employee order by salary desc;

 

MySQL eliminating duplicate rows with select statement :

MySQL eliminating duplicate rows with select statement :

A table may contain duplicate rows and you need to view only unique row then you can use distinct keyword with
select statement.

Syntax :


select distinct * from <table_name>;

Example :


select distinct * from employee;

MySQL Filtering table data

MySQL Filtering table data :

In database, when you need to view data from table it’s rare required to view all data each time. So sql give us some
method of filtering data that not required.

There are three ways to filter any table :

  • selected columns and all rows
  • selected rows and all columns
  • selected columns and selected rows

selected columns and all rows :

In this way you will write query as selected column name as your need.

Syntax :


select <column_name1>, <column_name2>....<column_name_n> from <table_name>;

Example :


select fname, lname from employee;

selected rows and all columns :

Sql provide where clause to filter row from table.

Syntax :

 


select * from <table_name> where <condition>;

In above query, sql engine compares each records in the table with the condition given in the where clause. Data display
only those records that satisfy the given condition.

Example :

 


select * from employee where salary='20000';

Note : With above condition you can use all standard operators like logical, arithmetic etc.

selected columns and selected rows :

SQL provide facilities to select column as well as row.

Syntax :

 


select <column_name1>, <column_name2>....<column_name_n> from <table_name> where <condition>;

Example :

 


select fname, lname from employee where salary='20000';


					

MySQL insert operation

MySQL insert operation :

When you need to write any data in a table you have to wite insert query in mysql.

Syntax :


insert into <table_name> (<column_name1>, <column_name2>....) values(<value1>, <value2>,......);

Example :


insert into employee(id, name, salary) values('101', 'Pradeep', 45000);

If you want to not specify column name then it’s sure that each column value must write.

Example :


insert into employee values('101', 'Pradeep', 45000); 

Here it is clear that you will not write all column value then arise an error.
Example :


insert into employee values('101');

The above query does not compile because MySQL engine not able to specify which column value query taken. But if you will write query with column name then it’s ok.

Example :


insert into employee(id) values('101');

In above query all remaining column value will insert null.