GROUP BY & HAVING

GROUP BY The GROUP BY statement groups rows that have the same values into summary rows. This statement is often used with aggregate functions like COUNT(), MAX() , MIN() , SUM() , AVG() to group the result-set by one or more columns. Syntax: SELECT <column name(s)> FROM table name WHERE condition GROUP BY <column name(s)>...

UNION

The MySQL UNION operator is used to combine the result-set of two or more SELECT statements. To apply the UNION operator one must remember following things: Every SELECT statement within UNION must have the same number of columnsThe columns must also have similar data typesThe columns in every SELECT statement must also be in the same order Syntax: SELECT <column name(s)> FROM table1...

JOINS

The JOIN clause is used to combine rows from two or more tables, based on a related column between them. JOINS are of five types. INNER JOINLEFT JOINRIGHT JOINCROSS JOINSELF JOIN INNER JOIN The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax: SELECT <column name(s)> FROM table1 INNER...

Column Aliases

The column that you select in a query can be given a different name i.e. column alias name for output purposes. For instance consider a table named school and it has a column class, so the name of the column class can be changed to any other name like Standard and can be displayed as...

SUM, AVG & COUNT

SUM SUM() is used to calculate the total numeric sum of a column, i.e. all the numeric values will be added and will be displayed as result. SYNTAX select SUM(columnname) from tablename where condition; EXAMPLE select SUM(Score) from students; AVG AVG() returns the average of the numeric values of a specific column. SYNTAX select AVG(columnname)...

MAX, MIN & LIMIT

MAX() is used to find out the maximum value of a selected column. Where as MN() is used to find the minimum value. The use of both max and min is very simple in MYSQL, even a child can learn to use it! SYNTAX: MAX() : Select MAX(columnname) From tablename Where condition; MIN(): Select MIN(columnname)...

DELETE , ALTER & DROP Command

DELETE While working with tables, one may reach a situation where he/she no longer needs some rows of data. In such a case one would like to remove such rows. This can be done by using the DELETE command. The DELETE command removes rows from a table. This removes the entire rows, not individual field...

UPDATE Table

UPDATE Sometimes one needs to change some or all the values in a particular row that is already existing. This can be done using the UPDATE command. It specifies the rows to be changed using the WHERE clause, and the new data using the SET keyword. The new data can be a specified constant, an...

Handling NULLS

The empty values are represented as NULLS in a table. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. A NULL value is different from ZERO. NULL...

ORDER BY Clause

Whenever a SELECT query is executed, the resulting rows emerge in a pre-decided order. One can sort the results or a query in a specific order using the 'ORDER BY' clause. The ORDER BY clause allows string of query results by one or more columns. The sorting can be done either in ascending order or...

AND , OR & NOT

AND, OR & NOT are used when the conditions for selecting an entry are more specific. These keywords are used along with WHERE. They are the logical operators. They can also be used with these symbols. AND (&&) OR (||) NOT (!) Syntax: 1. SELECT column name...[column names] from Table name WHERE(condition 1 AND condition...