Tags: #concept #database #SQL #DML 

Status:🟩

---

SQL Select Statements

Summary

The SELECT statement is used to query and retrieve data from one or more tables in a database. It allows you to specify which columns to fetch and conditions to filter the data. The result is a multiset (bag) of rows, meaning duplicates may exist, and the order of results is not guaranteed unless explicitly sorted using ORDER BY.

Details

The SELECT statement offers flexible and powerful ways to query and retrieve data from a database. It supports simple queries for basic data retrieval, as well as more complex operations like nested and correlated subqueries, comparisons with ALL and ANY, existence checks with EXISTS, and combining results using set operations.

The Select statement

SELECT component
FROM component
(WHERE component)
(GROUP component)
(HAVING component)
(ORDER BY component)

Simple Queries

Simple queries are statements that retrieve data from only one table, which means that the FROM component contains only one table name.

SELECT * FROM students;

See joins for queries with multiple tables. See also: subqueries.

It it also possible to only select a few columns

SELECT name,age FROM students;

The DISTINCT clause removes duplicated and only shows distinct values

SELECT DISTINCT name FROM students;

We can use the AS clause to give the result a different attribute name

SELECT name, id AS student_id, age FROM students;

We can use simple arithmetic on the results

SELECT name, age+2 AS age_in_two_years FROM students;

WHERE clause

A WHERE clause specifies the conditions to indicate which table rows should be selected.

-- Comparison operator
SELECT name FROM student WHERE city = "Copenhagen";
 
-- Boolean operator (AND)
SELECT name FROM student WHERE city = "Copenhagen" AND age > 20;
 
-- BETWEEN operator
SELECT name FROM student WHERE age BETWEEN 18 AND 30;
 
-- IN operator (study_line is either SWY or DS)
SELECT name FROM student WHERE study_line IN ('SWU', 'DS');
 
-- LIKE operator (name constains MA, so Mathias, Marcus & etc.)
SELECT name FROM student WHERE name LIKE '%MA%';
 
-- NULL operator
SELECT name FROM student WHERE study_line IS NULL;

Aggregate Functions

Several expressions can be added to ask for more specific information. Using aggregate functions can summarize information from database record like:

-- COUNT
SELECT COUNT(*) from students;
 
-- SUM
SELECT SUM(age) AS total_age FROM students;
 
-- AVG
SELECT AVG(age) AS avg_age FROM students;
 
-- VARIANCE
SELECT VARIANCE(age) AS age_variance FROM students;
 
-- MIN/MAX
SELECT MIN(age) AS youngest_age, MAX(age) AS OLDEST_age FROM students;
 
-- STDEV: Standard deviation
SELECT STDEV(age) AS age_stddev FROM students;

GROUP BY & HAVING

When using GROUP BY, the data is divided into groups based on shared values in one or more columns. The HAVING clause filters the groups by only showing those that meet a certain condition. A bit like the WHERE clause, however HAVING is used together with GROUP BY

-- Find cities with more than 3 students and show their total number of students
SELECT city, COUNT(*) AS num_students FROM student
GROUP BY city HAVING COUNT(*) > 3;

ORDER BY

ORDER BY can be used to order the rows in the result of a query by the value of one or more columns.

SELECT name FROM students ORDER BY age ASC;