intro to Data bases with Python

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)

count(*) : finding the length

group by () : aggregation the similars, could be used to count number of items.