1.5) Syntax of SQL [LIMIT, MIN/MAX, COUNT/AVG/SUM, Subquery]

Summary of Important SQL commands

SELECT	extracts data from a DB
UPDATE	updates data in a DB
DELETE	deletes data from a DB
INSERT INTO	inserts new data into a DB
CREATE DATABASE	creates a new DB
ALTER DATABASE	modifies a DB
CREATE TABLE	creates a new table
ALTER TABLE	modifies a table
DROP TABLE	deletes a table
CREATE INDEX	creates an index (search key)
DROP INDEX	deletes an index

---

LIMIT

used to specify the number of records to return & it's useful on large tables with thousands of records

SELECT col_name
FROM table_name
WHERE condition
LIMIT number

MIN & MAX

returns the smallest/largest value of the selected column

SELECT MIN(col_name)|MAX(col_name)
FROM table_name
WHERE condition

COUNT, AVG & SUM

COUNT(): returns the number of rows that matches a specified criterion

SELECT COUNT(col_name)
FROM table_name
WHERE condition

AVG(): returns the average value of a numeric column

SELECT AVG(col_name)
FROM table_name
WHERE condition

SUM(): returns the total sum of a numeric column

SELECT SUM(col_name)
FROM table_name
WHERE condition

Subquery

a SQL query nested inside a larger query

 

It may occur in:

• SELECT clause
• FROM clause
• WHERE clause

A subquery can be used in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks:

• Compare an expression to the result of the query
• Determine if an expression is included in the results of the query
• Check whether the query selects any rows

The subquery is executed before the main query executes, and the main query uses the result of the subquery.

SELECT col_name
FROM table_name
WHERE condition operator
		(SELECT col_name
                FROM table_name)

 

 

Subquery Examples

1. Subquery with FROM

SELECT name, ROUND(gdp_per_capita)
FROM (SELECT name, gdp/population AS gdp_per_capita
	FROM bbc) AS X
WHERE gdp_per_capital > 20000

 

 

2. Subquery with IN

 

? Find the countries in the same region as Bhutan

SELECT name
FROM bbc
WHERE region IN 
	(SELECT region FROM bbc
            WHERE name = 'Bhutan')

 

 

3. Correlated Subquery

 

! If a value from the outer query appears in the inner query = correlated subquery

? Show the countries where the population is greater than 5 times the average for its region

SELECT name
FROM bbc AS b1
WHERE population > 
	5 * (SELECT AVG(population) FROM bbc
    		WHERE region = b1.region)