Basic Query

SQL SELECT statement has the widest variety of query options, which are used to control the way data is returned.

ORDER BY—A clause that returns the result set in a sorted order based on specified columns.
example:
SELECT * FROM Contacts ORDER BY first_name;
You are free to use ORDER BY with any select statement that might return multiple rows. You can also use it in conjunction with other clauses:

SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN ‘a’ AND ‘k’ ORDER BY last_name;

When you use DISTINCT, it applies to all requested columns. If you want a list of all the salespeople in your table and the companies they represent but not every sales entry, you can use the following statement. Note that this may return several entries from the same company, etc. DISTINCT applies to the entire requested result set.

DISTINCT—A keyword that returns only unique rows within a result set

SELECT DISTINCT company, last_name, first_name FROM Sales;

COUNT—A function that returns a numeric value which equals the number of rows matching your query

The COUNT function tells you how many rows are in a result set. As with all functions, it accepts one parameter. This basic example will tell you how many rows are in your table:
SELECT COUNT(*) FROM Sales;

You can also use it to count the number of rows in any result set.
SELECT COUNT(*) FROM Sales WHERE net_amount > 100;

AVG—A function that returns the numeric value that equals the average of the numbers in a specified column

AVG returns the average of all the fields in a column with a numeric data type. It accepts one column name as its parameter, and it will return “0” if it's used on a non-numeric column.

SELECT AVG(net_amount) FROM Sales;
SELECT AVG(net_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’;

SUM—A function that adds the numbers in a specified column
SUM works just like AVG, except it returns the sum of values in all fields in the result set.
SELECT SUM(net_amount) FROM Sales WHERE net_amount > 100;

MIN—A function that returns the lowest non-null value in a column
MIN returns the lowest, non-null value in the specified column. If the column is a numeric data type, the result will be the lowest number. If it's a string data type, it will return the value that comes first alphabetically.
SELECT MIN(net_amount) FROM Sales WHERE last_name = “Smith”;
SELECT MIN(last_name) FROM Sales;

MAX—A function that returns the largest value in a column
MAX works just like MIN, only it returns the highest non-null value. It too can be used on strings or numbers.
SELECT MAX(net_amount) FROM Sales;
SELECT MAX(company) FROM Sales WHERE net_amount > 100;
The MAX function is sometimes used on columns containing an auto-incremented key field to determine what the next entry’s key ID will be. Unless you’re running a nonpublic database, be wary of using this information to insert the next entry, in case another user beats you to the punch.

GROUP BY— Group by keyword is used to give records in same column rows in grouped manner. Group by can be used on one column name and more than one column name in SQL query

Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
Joins
A join in SQL is a clause that allows` merging of records from one or more than one tables in database. The records from the tables are fetched based on some values that are common to each.

SELECT * from Employees e JOIN Salary s ON e.employeeid=s.employeeid;
Inner JoinTwo tables getting join result set display which is matched records only
Explicit type
SELECT *from shooters s INNER JOIN guntypes g ON s.guntype = g.guntype ;
Implicit type
SELECT *from shooters s , guntypes g WHERE s.guntype = g.guntype ;
Cross join
Is also called Cartesian join. Result of joining each row of the table with each row of the other table
Left outer join - unmatched left table records and matched right table records.
SELECT *from shooters s LEFT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Right outer join – unmatched right table and matched left table
SELECT *from shooters s RIGHT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Full outer join merged the result fetched from left and right
SELECT *from shooters s FULL OUTER JOIN guntypes g ON s.guntype = g.guntype ;

It is a simple sql join condition which uses the equal sign as the comparison operator.
Equi joins are SQL Outer join and SQL Inner join.
SQL Non Equi Join:
A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >
SELECT first_name, last_name, subject
FROM student_details
WHERE subject != 'Maths