|
Clause |
Description |
|
FROM |
It is
used for selecting a table name in a database. |
|
WHERE |
It
specifies which rows to retrieve. |
|
GROUP BY |
It is
used to arrange the data into groups. |
|
HAVING |
It
selects among the groups defined by the GROUP BY clause. |
|
ORDER BY |
It
specifies an order in which to return the rows. |
|
AS |
It
provides an alias which can be used to temporarily rename tables or columns. |
SELECT - GROUP BY
query lists the number of cars of the same price:
SELECT COUNT (Car_Name), Car_Price FROM Cars_Details GROUP BY Car_Price;
===========================================================================================
SELECT - UNIQUE
SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.
SELECT UNIQUE job
FROM employee;
===========================================================================================
SELECT - DISTINCT
The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.
SELECT distinct job,salary
FROM employee;
===========================================================================================
SELECT COUNT
The SQL COUNT() is a function that returns the number of records of the table in the output.
select count(salary) as cnt_salary
from employee;
select count(distinct(job)) as dis_cnt_job
from employee;
===========================================================================================
SELECT - TOP
The SELECT TOP statement in SQL shows the limited number of records or rows from the database table.
The TOP clause in the statement specifies how many rows are returned.
Note:
Not all database systems support the SELECT TOP clause.
MySQL supports the LIMIT clause to select a limited number of records,
while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.
Oracle:
SELECT * FROM employee FETCH FIRST 3 ROWS ONLY;
SELECT * FROM employee FETCH FIRST 50 percent ROWS ONLY;
(for SQL Server/MS Access):
SELECT TOP 3 * FROM Customers;
MY-SQL:
SELECT * FROM Customers
LIMIT 3;
===========================================================================================
SELECT IN
SQL IN is an operator used in a SQL query to help reduce the need to use multiple SQL "OR" conditions.
SELECT *
FROM students
WHERE students_name IN ( Amit , Raghav, Rajeev)
===========================================================================================
select from multiple tables:
Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.
SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2
FROM product AS p
LEFT JOIN customer1 AS c1
ON p.cus_id=c1.cus_id
LEFT JOIN customer2 AS c2
ON p.cus_id = c2.cus_id
