Structured Query Language

After going through SQL statements, this is a summary of the information that we should be able to annotate on ERDs:

  • Which entities are accessed?
    • SELECT … FROM table_name … — one entity (read).
    • DELETE FROM table_name WHERE … — one entity (delete).
    • INSERT INTO table_name … — one entity (add).
    • UPDATE table_name SET … — one entity (modify).
    • TRUNCATE TABLE table_name — one entity (modify).
    • SELECT … FROM table_name1 [INNER/LEFT/RIGHT/FULL/ JOIN table_name2 ON … — two or more entities (read).
    • SELECT … FROM table_name1 UNION ALL SELECT … FROM table_name2 … — two or more entities (read).
    • SELECT … FROM table_name1 UNION SELECT … FROM table_name2 … — two or more entities (read) + not all the rows.
    • SELECT … INTO new_table_nameFROM old_table_name — two entities (read-add).
    • SELECT DISTINCT … — not all the rows (read).
    • SELECT TOP number|percent … — not all the rows (read).
  • Which attributes are accessed?
    • SELECT column_name(s) FROM … — read.
    • SELECT aggregate_function(column_name) FROM … GROUP BY column_name … — read.
    • … WHERE column_name operator value … — filter rows.
    • … WHERE column_name BETWEEN value1 AND value2 … — filter rows.
    • … WHERE column_name LIKE pattern … – filter rows.
    • … GROUP BY column_name HAVING aggregate_function(column_name) operator value … — filter rows.
    • … WHERE column_name IN (value1,value2,..) … — filter rows.
    • INSERT INTO table_name VALUES (value1, value2, value3,….) — set values.
    • INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,….) — set values.
    • ORDER BY column_name [ASC|DESC] … — change order of rows.
    • GROUP BY column_name … — change order of rows.
  • Which relationships are accessed? This one is a merge between the first two when the table names or the column names correspond to relationships.
  • Different types of access: read, add, filter, …
  • Order in which the entities, attributes, relationships are accessed: sequential, parallel, nesting, multiple access at different points in time + procedural extensions.

See:

* SQL Quick Reference. (archive)

4 responses to this post.

  1. Don’t forget the clause HAVING (optional with teh Group by). This clause filters the “groups” to be returned with the query and may contain references to columns

    Responder

  2. I am planning to consider that clause as a way in which attributes (or relationships) can filter the rows.

    Responder

  3. […] paper, we didn’t try to extract ideas about any particular notation. Now that we specified what runtime information we should be able to annotate on ERDs, we will see some alternative notations to express order of access. To do this we will start from […]

    Responder

  4. […] back to the Structured Query Language post, lets add a few more SQL queries that are not similar to the previous examples. 25. DELETE […]

    Responder

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: