Intro SQL

SQL:

Structured query language.

For interacting with data stored in a relational data base:

• Relational data base: collection of tables. Set of row and col.

🡪 SELECT col1,col2 AS <alias>

        🡪 instead of using the names of the col we can use * to select all the columns available

🡪 FROM <table_name>

🡪 LIMIT <#>

🡪 max number to get rows from table

🡪 SELECT DISTINCT col1

        🡪 only gets diff values

🡪 SELECT COUNT(*) FROM <table_name>

        🡪 returns the number of registries.

🡪 WHERE col1 =/<>/</>/<=/>=

+ ADD second condition

+…

+ OR another condition

+ LIKE <’string to match’>

+NOT LIKE

🡪 BETWEEN <val1> AND <val2>

        🡪 is inclusive []

🡪 IN

        🡪 list of elements

🡪 IS NULL

        🡪 missing value

🡪 CASE:

        🡪 it can be used with WHEN, THEN, ELSE and END to define a new grouping field. Adding a new columns based in some conditions.

          [pic 1][pic 2]

We can use INTO to sve the result of the previous query as a certain name.

Functions:

• Count
• Avg
• Max
• Sum
• Min

Actions:

• ORDER BY col DESC/ASC
• GROUP BY col1, col2
• HAVING funct(col)>#

Joins:

Use SQL to join 2 or more SQL tables.

🡪 INNER JOIN: the key is in both tables. Such as 1 and 4, and this is what is returned.

[pic 3]

[pic 4]

dof

[pic 5]

🡪 INNER JOIN via USING

Its not necessary to select which is the right or left column. By using the clause USING you can identify the name of the field of each table to match to join.

[pic 6]

🡪 SELF JOIN

Used to compare values in a field to other values of the same field from within the same table.

...