Module10_SQL_Primter Flashcards
what are the 3 numeric data types
Numeric: number with presision
integer:
serial: automatic generating INT starts with 1 + 1
what are the 3 charcter data types
char:
varchar:
text: like clob for big data
what are the 4 date / time data types?
Timestamp
date
time
interval
what are DDL commands
create
alter
drop
truncate
what are DML commands
insert
update
delete
select
what are DCL command?
Data control language like GRANT & REVOKE
what are TCL commands?
Transaction control language like:
Commit
rollback
savepoint
set transaction
what are the 5 constraints
not null
check
unique
primary key
foreign key
when to use a domain
if you want to create your own data type with optional constraint.
Like Domain: City
with allowed values: Amsterdam and Paris.
This is like a check constraint but a domain can be used in many table where check constraint needs to be created per table.
what is an inner join
returns only matching rows from both table
what is an left outer join
returns all matching rows and rows from left table
what is an right outer join
return all matching rows and all rows from right
what is an full outer join
A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query’s where clause, and in cases where the on condition can’t be satisfied for those rows it puts null values in for the unpopulated fields.
what is an cross join
A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you’re just joining everything to everything.
how do we quote a ‘ or “ in postgres
by setting $$ at the beginnen and end $$
or
by setting $any_text$ at the beginnen and end $any_text$
when quoting a table with “Case” it makes the table_name case sensitive
