Jump to content

User:Ghosts of Europa/sandbox/SQL

From Wikipedia, the free encyclopedia

SQL is a programming language for interacting with relational databases

Overview

[edit]

-Summarize what a relational database is, mention ACID

History

[edit]

Syntax and features

[edit]

-Overview of tables and data types

-Data types

-Basic Select/From/Where queries

-Insert/Update/Alter

-Aggregations and Having clauses

-Window functions and Qualify clauses

-Nulls

-Transactions

Joins

[edit]

This code will produce the cartesian product of all pairs of rows from both tables:[1]

select *
from table_1, table_2

This full cartesian product can also be expressed as "cross join".[2]

A join is a subset of this cartesian product, filtered to only the rows that match a given condition.[1] The following two queries are equivalent:[citation needed]

select *
from table_1
<inner> join table_2
on table_1.abc = table_2.xyz
select *
from table_1, table_2
where table_1.abc = table_2.xyz


An outer join is equivalent to first performing an inner join, then adding each unmatched row from the first table with NULLS for the columns of the second table, then each unmatched row from the second table with NULLs for all the columns of the first table. Every row will be "preserved" at least once.[3]. A left outer join will only preserve unmatched rows from the first table, while a right outer join will only preserve unmatched rows from the second table.[4]


Differences from the Relational Model

[edit]

-Order -Nulls

Implementations

[edit]

-SQLite, Postgres, Snowflake, etc -Note some of the incompatibilities

Criticism & alternatives

[edit]

References

[edit]

Bibliography

[edit]

Weinberg, Paul N.; Groff, James R.; Oppel, Andrew J. (2010). SQL, The Complete Reference (3. ed.). New York, NY: McGraw-Hill. ISBN 978-0-07-159255-0.


[edit]