1.
Select all records from the Customers table:
SELECT * FROM Customers;
2. The SELECT statement is used to select data from a database.
Return data from the Customers table:
SELECT CustomerName, City FROM Customers;
3. The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
ExampleGet your own SQL Server
Select all the different countries from the "Customers" table:
SELECT DISTINCT Country FROM Customers;
4. Count Distinct
By using the DISTINCT keyword in a function called COUNT, we can return the
number of different countries.
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
5. The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like
"find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
ExampleGet your own SQL Server
i) SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
ii) SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
The SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE [Link] =
[Link] AND Price < 20);
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE [Link] =
[Link] AND Price = 22);