|
Introduction to SQL
|
SQL allows us to select and group subsets of data, do math and other calculations, and combine data.
A relational database is made up of tables which are related to each other by shared keys.
Different database management systems (DBMS) use slightly different vocabulary, but they are all based on the same ideas.
|
|
Getting to know your data
|
We can import sqlite3 to work with relational database in python
Primary key uniquely identifies each rows in a table. A foreign key in one table refers to a primary key in another table.
Common data types in SQL are are integer, varchar, char, text, double, etc.
|
|
Basic Queries
|
It is useful to apply conventions when writing SQL queries to aid readability.
Use logical connectors such as AND or OR to create more complex queries.
Calculations using mathematical symbols can also be performed on SQL queries.
Adding comments in SQL helps keep complex queries understandable.
|
|
SQL Aggregation
|
Use the GROUP BY keyword to aggregate data.
Functions like MIN, MAX, AVERAGE, SUM, COUNT, etc. operate on aggregated data.
Aliases can help shorten long queries. To write clear and readible queries, use the AS keyword when creating aliases.
Use the HAVING keyword to filter on aggregate properties.
Use a VIEW to store the result of a query as though it was a new table.
|
|
Joins
|
Use the JOIN command to combine data from two tables—the ON or USING keywords specify which columns link the tables.
Regular JOIN returns cross product of the two tables. Other join commands provide different behavior, e.g., LEFT JOIN retains all rows of the table on the left side of the command.
IFNULL allows you to specify a value to use in place of NULL, which can help in joins
NULLIF can be used to replace certain values with NULL in results
Many other functions like IFNULL and NULLIF can operate on individual values.
|