today I have tried some PostgreSQL using some SQL queries and this is what I have tried with an example of a zoo database with animal, diet, taxonomy, order name and each one have columns as follows

diet

  • species — the name of a species (example: 'hyena')
  • food — the name of a food that species eats (example: 'meat')

animals

  • name — the animal's name (example: 'George')
  • species — the animal's species (example: 'gorilla')
  • birthdate — the animal's date of birth (example: '1998-05-18')

order-names

  • t_order — the taxonomic order name (e.g. 'Cetacea')
  • name — the common name (e.g. 'whales and dolphins')

taxonomy

  • name — the common name of the species (e.g. 'jackal')
  • species — the taxonomic species name (e.g. 'Aureus')
  • genus — the taxonomic genus name (e.g. 'Canis')
  • family — the taxonomic family name (e.g. 'Canidae')
  • t_order — the taxonomic order name (e.g. 'Carnivora'

each table is empty and created like that

create table animals (  
       name text,
       species text,
       birthdate date);
create table diet (
       species text,
       food text);  
create table taxonomy (
       name text,
       species text,
       genus text,
       family text,
       t_order text); 

create table ordernames (
       t_order text,
       name text);

here also I tried some queries and here they are with a simple description for each query

select name, birthdate from animals where species = 'gorilla'; 
->it will select table animals and select name & birthdate columns that match species = 'gorilla'
'*' -> this symbol mean match all columns to species ='gorilla'
'--' this mean comment or ruby's comment with '#'
--------------------------------------------------------------------------------------------------------------
for PostgreSQL
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.
--------------------------------------------------------------------------------------------------------------
->also SQL supports <,>,<=,>=,!=,=,and,or,not
->to list your table + columns in a standard way it will differ from a database system to another and you can ask this
query from a database console or  special administrative console
for PostgreSQL -> \dt and \t table name
for My-SQL ->show tables and describe table-name
for sqlite->.tables and .schema table-name
--------------------------------------------------------------------------------------------------------------
->to create a table with name animals with three columns with names -> name, species, birthdate with types text, text, date respectively also note that we always put
string and date values inside single quotes in SQL queries
create table animals (
       name text,
       species text,
       birthdate date);
for zoo database here are some queries and their meaning
-> select * from animals limit 10; <<->> will select the first 10 elements in the table
->select * from animals where species = 'orangutan' order by birthdate; <<->> will select all columns with species='orangutan' ordered by birthdate from less to high
->select name from animals where species = 'orangutan' order by birthdate desc; <<->>will select the only name with species='orangutan' decrease ordered birthdate from
 high to less but only show the name
->select max(name) from animals; <<->>starting from the last row it will select the name with last alphabetical order 'z' and min will select 'A' names
-> as <<->> used to give the column a name
->limit 10 offset 20 <<->> return 10 rows , starting with the 20st row
->order by species, name <<->>sort result in rows first by the species columns then within each species sort by the name columns
->Group by columns <<->>which columns to use as groupings when aggregating
->select name, count(*) as num from animals group by name; <<->>to figure how different counts this name is in the zoo use this

+++++++++++++the most important clauses++++++++++++++++++++++++
->where


The where clause expresses restrictions — filtering a table for rows that follow a particular rule. where supports equalities, inequalities, and boolean operators
(among other things):where species = 'gorilla' — return only rows that have 'gorilla' as the value of the species column.
where name >= 'George' — return only rows where the name column is alphabetically after 'George'.
where species != 'gorilla' and name != 'George' — return only rows where species isn't 'gorilla' and name isn't 'George'.

->limit / offset

The limit clause sets a limit on how many rows to return in the result table. The optional offset clause says how far to skip ahead into the results. So
limit 10 offset 100 will return 10 results starting with the 101st.

->order by

The order by clause tells the database how to sort the results — usually according to one or more columns. So order by species, the name says to sort results first
by the species column, then by name within each species. Ordering happens before limit/offset so you can use them together to extract pages of alphabetized results.
(Think of the pages of a dictionary.)The optional desc modifier tells the database to order results in descending order — for instance from large numbers to small
ones, or from Z to A.

->group by

The group by clause is only used with aggregations, such as max or sum. Without a group by clause, a select statement with an aggregation will aggregate over the
whole selected table(s), returning only one row. With a group by clause, it will return one row for each distinct value of the column or expression in the group by
clause.
-----------------------------------------------------------------------------------------------------------------
SQL database is more reliable than python or any other language to fetch and order data because it is faster and consume less speed
-----------------------------------------------------------------------------------------------------------------
-> insert statement
this statement adds an element into the table as a row data with default none specifier if we didn't determine that insertion
example:-
insert into table ( column1, column2, ... ) values ( val1, val2, ... );
    table name (column names)              (added strings value or numbers);
-----------------------------------------------------------------------------------------------------------------
->animals.name <<->> select name column from animals table
->animals.species = diet.species <<->>match species column from animals table to species column from diet table
->select animals.name from animals, a diet where animals.species = diet.species and food='fish';
this SQL query will show us a name column from animals table after joining animals and diet tables then match column called species in each table and have a food fish
->where >>>is a restriction for the source table
->having >>>is restriction after aggregation
-----------------------------------------------------------------------------------------------------------------
-> Find the one food that is eaten by only one animal, The animals table has columns (name, species, birthdate), The diet table has columns (species, food)


select food, count(*) as num          ->will return food and num columns
from animals join a diet                  ->will join animals and diet table
on animals.species = diet.species  ->will match the species columns
group by food                                  ->will group by food
having num = 1; ->selects one number food eaten by only one 









Comments

Post a Comment