SQL.txt

#> Installation MYSQL
#> SELECT, FROM
#> DISTINCT, WHERE, BETWEEN, IN
#> AND, OR, ORDER BY, ASC, DESC
#> INSERT, INTO, VALUES, UPDATE, SET, DELETE
#> NULL, LIKE, TOP, LIMIT, ROWNUM, PERCENT
#> IN
#> Tables and Data-Types
#> Primary und Foreign Keys, not null
#> Change Tables, Autoincrement
#> Joins
#> SQL Built In Funktionen



###### Installation MYSQL
https://www.youtube.com/watch?v=GIRcpjg-3Eg     => Installation 



###### SELECT, FROM
select * from customer                      => Outputs all columns from the table customer (automatically sorted by id)
select firstname from customer              => Outputs only the column firstname (no sorting)
select firstname, lastname from customer    => Outputs 2 columns firstname and lastname



###### DISTINCT, WHERE, BETWEEN, IN
select distinct firstname from customer     => With distinct the output occurs only one time if the firstname is the same
select distinct id,firstname from customer  => Everything will be outputed cause the id is unique
select distinct firstname,lastname          => Only when firstname and lastname are equal there will be no outputs

=> Outputs the row with the id = 0
""
select * from customer
    where id = 0       
""

=> Outputs all rows where the firstname is Janet
""
select * from customer
    where firstname = 'Janet'        
""

=> Outputs all streets which are in the city of Oslo
""
select street from customer
    where city = 'Oslo'        
""

...where id >= 40       => all IDs >= 40
...where id <> 40       => all IDs except 40 (in some DBs you have to use instead !=)
...where id between 40 and 50   => everything between 40 and 50
...where id in (43,44,45)       => some values are checked for id



###### AND, OR, ORDER BY, ASC, DESC
=> Outputs all rows where id >= 40 and firstname is Robert
""
select * from customer
where id >= 40
and firstname = "Robert"        
""

...where id >= 40 or firstname = "Robert"       => One of the both conditions have to be fullfilled
...where id >= 40 or firstname = "Robert" and lastname = "Fuller"       => combinaton of logical or and and (better to use parantheses)

=> Outputs rows >=40 sorted ascending for firstname and lastname
""
select * from customer
where id >= 40
order by firstname, lastname asc    
""

...order by firstname, lastname desc    =>  Output in descending order



###### INSERT, INTO, VALUES, UPDATE, SET, DELETE
=> Inserts row in the table with specific values for the columns
""
insert into customer
values (50, 'James', 'Karsen', '107 Upland Pl.', 'Dallas')
""

=> Inserts row  with streetname = NULL (this is not working for key-columns like id)
""
insert into customer (id, firstname, lastname, city)
values (51, 'James', 'Karsen', 'Dallas')
""

=> Update the row with the id = 51 regarding street
""
update customer
set street = '547 Seventh Av.'
where id = 51
""

...where firstname = 'James' and lastname = 'Karsen'    => Update the strett where firstname James and lastname Karsen

=> Delete row with the id = 51
""
delete from customer
where id = 51
""



###### NULL, LIKE, TOP, LIMIT, ROWNUM, PERCENT
select top 5 * from customer    => Outputs the first 5 results (depending on the sorting)

=> in some DBs also in the following form - oututs the first 10 results
""
select * from customer
limit 10
""

=> and also in this form in some DBs
""
select * from customer
where rownum <= 10  
""

select top 10 percent * from customer       => Outputs 10 top percent of the results

=> "_" will be replaced with one char - so the output will be done for eg. Oslo or Oxlo
""
select * from computer
where city like 'O_lo'  
""

... where city like '%lo'   => Outputs all entries which end with "lo" for the column city
... where city like '__l%'  => Outputs all entries which start with 2 chars and then the char "l"
... where city like '__l\%' => When there sould be a serach for the percent sign "%" - this has to be masked with \%

=> Outputs all rows where lastname is NULL
""
select * from computer
where lastname is NULL   
""

where lastname is NOT NULL  => Outputs all rows where there is some entry in the column lastname



###### IN
=> IDs will be selected from the rechnungen-table and will be used for the aboth where-clausel for select *
""
select * from customer
where id IN
        (select id from rechnungen      
        where date <=3)                
""



###### Tables and Data-Types
=> CREATE, TABLE, INT, VARCHAR, BINARY, BOOLEAN, VARBINARY, SMALLINT, BIGINT, DECIMAL, NUMERIC, DATE, TIME

=> Create a new table with name Rechnung
""
create table Rechnung       
    (
    RechnungsID int,
    CustomerID, int,
    Betrag int
    )
""
    
insert into Rechnung values (1,0,50)     => Insert a row in the new table Rechnung with specific values
value INT   => Integer
name VARCHAR(255)   => variable Character
bin BINARY (255)    => Bits
bool BOOLEAN        => Bool (TRUE / FALSE)
VARBINARY(255)      => variable Bits
SMALLINT            => SmallInt
BIGINT              => BigInt
DECIMAL (5,2)       => Decimal Number (total,decimal places) - 3 beforce decimal places, 2 decimal places
NUMERIC (5,2)       => Numeric Number
FLOAT (10)          => Floating Number
DOUBLE PRECISION    => Double Precision
DATE                => Date value
TIME                => Time value
TIMESTAMP           => Timestamp value



###### Primary und Foreign Keys, not null
=> DROP, UNIQUE, NOT NULL, PRIMARY KEY, FOREIGN KEY, REFERENCES
drop table Rechnung IF EXISTS   => Drop whole table

=> RechnungID is a mandatory field - must be allways available and not empty with NULL - with UNIQUE the ID has to be unique
=> Define the Primary Key with PRIMARY KEY
=> Define the Foreign Key with FOREIN KEY (Primary Key in anderer Tabelle) - is referencing to the id in the customer table
""
create table Rechnung
    (
    RechnungsID int NOT NULL UNIQUE        
    CustomerID int NOT NULL
    Betrag int NOT NULL
    PRIMARY KEY(RechnungsID)        
    FOREIGN KEY(CustomerID) REFERENCES Customer(ID)
    )
""


###### Change Tables, Autoincrement
=> AUTO_INCREMENT, DEFAULT, IDENTITY, ALTER TABLE, ADD, DROP COLUMN, ALTER COLUMN
=> With AUTO_INCREMENT the id will be given automatically - starts with 1 and then ascending (e.g. MYSQL)
=> In some DBs with: RechnungsID int IDENTITY(0,1) (e.g. MSSQL)
=> In some DBs with: RechnungsID int IDENTITY (e.g. HSQL)
=> Using DEFAULT - when something is inserted without specific value - 50 will be used as default value
""
create table Rechnung
    (
    RechnungsID int NOT NULL AUTO_INCREMENT         
    CustomerID int NOT NULL,
    Betrag int DEFAULT 50,
    PRIMARY KEY(RechnungsID),
    FOREIGN KEY(CustomerID) REFERENCES Customer(ID)
    )
""

alter table Rechnung        => change table Rechnung
add Datum Date              => add datum-column with datatype date
drop column betrag          => drop column betrag from the table
alter column VARCHAR(255)   => change column to VARCHAR(255)



###### Joins
=> INNER JOIN, ON, LEFT JOIN, RIGTH JOIN, FULL JOIN
=> Outputs firstname + lastname from all customers which exists in the rechnung table
""
select firstname, lastname from customer
where id in (select customerID from rechnung)
""

=> Outputs all attributes from the customers, which have a rechnung (same as aboth - but for all attributes)
""
select *
from customer
inner join Rechnung
on customer.ID = rechnung.customerID
""

...left join Rechnung   => Outputs all elements from the left table (customer) - where in the table Rechnung something is inside it will be outputed
...right join Rechnung  => Outputs alle elements from the right table (Rechnung) - with informations from both tables
...full join Rechnung   => Outputs everything from both sides


###### SQL Built In Funktionen
=> AVG, COUNT, TOP,  FIRST, LIMIT, LAST, UCASE, UPPER, LCASE, LOWER
select avg(betrag) from rechnung    => Outputs the average
select count(rechnungID) from rechnung  => Returns the count of the rows

=> Outputs the count of the rechnungIDs where the betrag is >= the average in the rechnungs table
""
select count(rechnungID) from rechnung
where betrag >= (select AVB(betrag) from rechnung)
""

select max(betrag) from rechnung        => Outputs maximum betrag from the table
select min(betrag) from rechnung        => Outputs the minimum betrag from the table
select sum(betrag) from rechnung        => Outputs the sum betrag from the table
select ucase(firstname) from customer   => Outputs the firstname as uppercase - sometimes also upper(firstname)
select lcase(firstname) from customer   => Outputs the firstname as lowercase - sometimes also lower(firstname)
select len(firstname) from customer     => Outputs the length of the string
select now() from  customer             => Outputs the actual date + time