surrogate key
a key with no meaning in the real world, EG a random number

natural key
a key with meaning in the real word, EG an SSN

primary key
unique key for a table, within that table

foreign key
unique key for another table

composite key
a uniquely-identifying key comprised of multiple columns

data query language - run queries
dql

data definition language - define schemas
ddl

data control language - control access to the database
dcl

data manipulation language - crud
dml

crud
create, retrieve, update and delete

create table student(name varchar(40));
create a student table with column name

insert into student(name) values('fred');
add fred to the student table

drop table student;
remove the student table

ddl: not null
constraint on a column: be nonempty

ddl: unique
constraint on a column: occur exactly once

ddl: primary key
constraint on a column: implies not null and unique

ddl: default 'value'
constraint on a column: specify a default value

ddl: auto_increment
constraint on a column: count up from 1 with unique integer values

rename fred to frederick in student table
update student set name='frederick' where name='fred';

delete fred from student table
delete from student where name='fred';

get all rows and columns from student table
select * from student;

get all rows and columns from student table sorted descending order by name
select * from student order by name desc;

get first 2 rows and all columns from student table
select * from student limit 2;

get all rows and columns from student table with name of fred
select * from student where name = 'fred';

sql comment
--

sql not equal
<>

get all rows and columns from student table with name of Claire, Kate or Mike
select * from student where name in ('Claire', 'Kate', 'Mike');

foreign key(mgr_id) references employee(emp_id)
ddl: create a foreign key mgr_id that can join on employee table's emp_id

change employee table to have a foreign key branch_id that can join on branch table's branch_id
alter table employee add foreign key(branch_id) references branch(branch_id);

select last_name as surname from student;
select last names from student table, column renamed to surname

select distinct sex from student;
select kinds of sex from student table - 1 row per sex

select count(student_id) from student;
count the number of students in the student table

select count(student_id) from student where sex = 'F';
count the number of female student in the student table

select avg(salary) from employee;
get the mean salary in the employee table

select sum(salary) from employee;
get the total salary in the employee table

select count(sex), sex from student group by sex;
get the number of males and females in the student table

select sum(sales), emp_id from works_with group by emp_id;
get the total sales for each employee from the works_with table

select * from student where first_name like '%a%';
find all students whose first name contains an a

select * from student where first_name like '%a_b%';
find all students whose first name contains an a, then any character, and then a b

select * from student where first_name like '%cRaZy%' collate utf8_bin;
get students with cRaZy first name, case sensitive

select * from student where first_name like '%crazy%';
get students with cRaZy first name, case insensitive

select first_name from student union select first_name from employee;
get names from student and employee tables - must have same number of columns and same types

select first_name from employee union select branch_name from branch;
get a single report of first_name in employee table and branch_name in branch table

select employee.emp_id, employee.first_name, branch.branch_name from employee join branch on employee.emp_id = branch.mgr_id;
get employee id and first name from employee table, combined with branch_name from branch table

join, left join, right join, full outer join
kinds of joins

find first_name's in employee table who work with a customer who has total_sales over 50000
select employee.first_name from employee where employee.emp_id in (select works_with.emp_id from works_with where works_with.total_sales > 50000);

defined in DDL on foreign key to set to null other relevant fields in other tables
on delete set null

defined in DDL on foreign key to delete other relevant fields in other tables, vital for primary keys
on delete cascade

purpose of triggers
watch for a change in one table, apply a change to another