An Interest In:
Web News this Week
- April 1, 2024
- March 31, 2024
- March 30, 2024
- March 29, 2024
- March 28, 2024
- March 27, 2024
- March 26, 2024
April 20, 2021 11:41 pm GMT
Original Link: https://dev.to/marcegarba/what-is-sql-part-4-9c1
What is SQL - Part 4
Completing the basics
This is part 4 of a four-part article which explains SQL
Content:
NULL values
NULL
values are special and treated differently. Look at these comparisons:
comparison | result |
---|---|
NULL = '' | NULL |
NULL = 0 | NULL |
NULL <> '' | NULL |
NULL <> 0 | NULL |
NULL = NULL | NULL |
NULL <> NULL | NULL |
NULL IS NULL | true |
NULL IS NOT NULL | false |
- If a column admits
NULL
values, or the result of aLEFT JOIN
orRIGHT JOIN
query (like missing rows in a related table) produceNULL
values, those then need to be treated and compared using theIS
orIS NOT
clauses; as all other comparisons returnNULL
as shown in the chart above
CASE WHEN
- In standard SQL the CASE clause helps to create results using a simple syntax
- Here's an example
SELECT id, name, CASE WHEN name LIKE '%Sport%' THEN 'Sports' ELSE 'Formal' END AS typeFROM productsORDER BY id;
- This query will produce the following results, using the DB schema defined so far:
id | name | type |
---|---|---|
1 | Sport shoes A | Sports |
2 | Sport watch B | Sports |
3 | Suit C | Formal |
- Several
WHEN ... THEN ...
parts can be concatenated using the sameCASE
clause - After those, the
ELSE
part goes; if there is no ELSE and none of the WHEN parts match, the result for the column is aNULL
- The
END
part must be the last one of the clause
Views
- A View could be created using the previous SELECT
- Here is the view named
products_type
:
CREATE OR REPLACE VIEW products_type ASSELECT id, name, CASE WHEN name LIKE '%Sport%' THEN 'Sports' ELSE 'Formal' END AS typeFROM productsORDER BY id;
- The syntax CREATE OR REPLACE is not supported by all DBMS, so consider using instead
CREATE
and if in need to update it, delete it first withDROP VIEW
- This View could be used almost like a table. Consider:
- It adds a calculated column,
type
- It already provides an
ORDER BY
- It adds a calculated column,
SELECT *FROM products_typeWHERE type = 'Sports';
id | name | type |
---|---|---|
1 | Sport shoes A | Sports |
2 | Sport watch B | Sports |
- Also, a different
ORDER BY
could be used - Some DBMS allow views to
INSERT
,UPDATE
orDELETE
the underlying table(s), if at all possible, given the query
Aggregate functions at work
- How to obtain the total quantity sold, per month, per product, for year 2020, in just one query?
- Here is the result of the query
id | name | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Sport shoes A | 5 | 0 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | Sport watch B | 1 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 |
3 | Suit C | 0 | 1 | 0 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 2 | 0 |
- The syntax vary a little among DBMS engines, due to differences in the treatment of DATE types
PostgreSQL
SELECT s.id_product AS id, p.name, SUM(CASE WHEN date_part('month', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan, SUM(CASE WHEN date_part('month', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb, SUM(CASE WHEN date_part('month', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar, SUM(CASE WHEN date_part('month', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr, SUM(CASE WHEN date_part('month', s.date) = '05' THEN s.quantity ELSE 0 END) AS may, SUM(CASE WHEN date_part('month', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun, SUM(CASE WHEN date_part('month', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul, SUM(CASE WHEN date_part('month', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug, SUM(CASE WHEN date_part('month', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep, SUM(CASE WHEN date_part('month', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct, SUM(CASE WHEN date_part('month', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov, SUM(CASE WHEN date_part('month', s.date) = '12' THEN s.quantity ELSE 0 END) AS decFROM sales s JOIN products p ON s.id_product = p.idWHERE date_part('year', s.date) = '2020'GROUP BY s.id_product, p.nameORDER BY id_product;
MySQL / MariaDB
SELECT s.id_product AS id, p.name, SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '01' THEN s.quantity ELSE 0 END) AS "jan", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '02' THEN s.quantity ELSE 0 END) AS "feb", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '03' THEN s.quantity ELSE 0 END) AS "mar", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '04' THEN s.quantity ELSE 0 END) AS "apr", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '05' THEN s.quantity ELSE 0 END) AS "may", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '06' THEN s.quantity ELSE 0 END) AS "jun", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '07' THEN s.quantity ELSE 0 END) AS "jul", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '08' THEN s.quantity ELSE 0 END) AS "aug", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '09' THEN s.quantity ELSE 0 END) AS "sep", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '10' THEN s.quantity ELSE 0 END) AS "oct", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '11' THEN s.quantity ELSE 0 END) AS "nov", SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '12' THEN s.quantity ELSE 0 END) AS "dec"FROM sales s JOIN products p ON s.id_product = p.idWHERE EXTRACT(YEAR FROM s.date) = '2020'GROUP BY s.id_product, p.nameORDER BY s.id_product;
SQLite
SELECT s.id_product AS id, p.name, SUM(CASE WHEN strftime('%m', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan, SUM(CASE WHEN strftime('%m', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb, SUM(CASE WHEN strftime('%m', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar, SUM(CASE WHEN strftime('%m', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr, SUM(CASE WHEN strftime('%m', s.date) = '05' THEN s.quantity ELSE 0 END) AS may, SUM(CASE WHEN strftime('%m', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun, SUM(CASE WHEN strftime('%m', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul, SUM(CASE WHEN strftime('%m', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug, SUM(CASE WHEN strftime('%m', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep, SUM(CASE WHEN strftime('%m', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct, SUM(CASE WHEN strftime('%m', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov, SUM(CASE WHEN strftime('%m', s.date) = '12' THEN s.quantity ELSE 0 END) AS decFROM sales s JOIN products p ON s.id_product = p.idWHERE strftime('%Y', date) = '2020'GROUP BY id_productORDER BY id_product;
- In this example, two features of DML are combined together:
GROUP BY
, to summarize row valuesCASE WHEN
to obtain calculated values for each month of the year, replacing the quantity with a zero when the month in the date doesn't match the column it's calculated for
Final words
It's my hope that this brief introduction to SQL has piqued your interest.
It's a very powerful Domain Specific Language.
Having a basic notion of the SQL fundamentals, in my view, is essential for improving our use of it.
Glossary
Term | a.k.a. | What it is |
---|---|---|
Check Constraint | Check | A Constraint where the values of one or more columns are limited to a specific set |
Constraint | Set of restrictions in a DB which make the DB consistent; there are several types of constraints, such as: Primary Key, Foreign Keys, Unique Keys, NULL , Check Constraints | |
Data Base | DB | A combination of tables, rules, constraints, triggers and stored procedures which is managed by a DBMS |
Data Base Management System | DBMS | The software that administer Data Bases; in this context, an SQL DB |
Data Definition Language | DDL | Defines the schema in a DB |
Data Manipulation Language | DML | Operates on tables in a DB |
Foreign Key | FK | A combination of one or more columns in a table, which point to the PK in a table |
Primary Key | PK | It's a unique composition of values in a row, which make the row unique; serves as an identification, and no NULL columns are allowed |
Relational Data Base Management | RDBMS | a DBMS which follows the Relational math principles |
Schema | The complete definition of a set of tables, constraints and other objects in a DB | |
Table | Relation | A matrix formed of tuples, each of them consists of the same type of values (each positioned element); if the table has a PK, then it might be considered a Set, from Set Theory, in the sense that each element of the set is a tuple, and there is no more than one tuple contained in the set with the same values |
Trigger | Code that the DBMS executes whenever changes occur on the DB; this code is configured within the DB, and it could be written in SQL or a procedural language within the DBMS; this usually implies difference between DBMS vendors or products | |
Unique Key | UK | Similar to PK, a unique combination of columns in each row, except that NULL values are accepted |
View | A projection of one or more tables which produce a table-like result from a SELECT statement; there are different types of views: Read Only, Writable and Materialized |
Original Link: https://dev.to/marcegarba/what-is-sql-part-4-9c1
Share this article:
Tweet
View Full Article
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To