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