intro to Data bases with Python - Ahmed-Ayman/My-2017-Vacation GitHub Wiki
here are some notes from the course
- a Data base at least one table.
- for each data in the table ..it has data type each row has a meaning.
- with related tables .. we join them to get sense of one of the tables that depend on the other.
- there has to be some sort of unique column.
- the joins again : we use them to do some aggregation [collection of data according to some criteria].
data types in SQL
Text and string types
- text — a string of any length, like Python str or unicode types.
- char(n) — a string of exactly n characters.
- varchar(n) — a string of up to n characters.
Numeric types
- integer — an integer value, like Python int.
- real — a floating-point value, like Python float. Accurate up to six decimal places.
- double precision — a higher-precision floating-point value. Accurate up to 15 decimal places.
- decimal — an exact decimal value.
Date and time types
- date — a calendar date; including year, month, and day.
- time — a time of day.
- timestamp — a date and time together.
- to check equality we need just one equal mark.
- conditions :
where not (col1 ='x' and/or col2 ='y') ;less or greater >= <= .
you can get MAX(column) from table 0- 9 or a-z.
you can limit the query ... limit 10;
you can order by column ... order by birthdate. DESC descending.
offset used with a limit to get set of the limited data ... select name from animals order by name limit 10 offset 20; will return the elements from 21 30.
limit : how many to return
offset : how many to skip.
mapping: select species, min(birthdate) from animals group by species;
or group by birthdate. (the minimum)