View on GitHub

presentations

Presentation notes from JMU Unix Users Group meetings

Databases


A brief history


Where we are today


Four essential acronyms - (R)DBMS


Four essential acronyms - ACID

Why use a database?


Atomicity

Consistency


Isolation

Isolation refers to the requirement that other operations cannot access or see data that has been modified during a transaction that has not yet completed.

Durability

The ability of the DBMS to recover the committed transaction updates against any kind of system failure (hardware or software). Durability is the DBMS’s guarantee that once the user has been notified of a transaction’s success, the transaction will not be lost.


Four essential acronyms - CRUD

The four essential SQL operations


Four essential acronyms - SQL


So how do databases store information?


bg contain


bg contain


Let’s do some queries

SELECT * FROM employees;
SELECT first_name, last_name from EMPLOYEES;
SELECT * FROM employees
WHERE employee_id = 100;
SELECT * FROM employees
ORDER BY hire_date DESC;

Let’s do some queries

SELECT count(*) FROM employees;
SELECT DISTINCT job_id FROM employees;
SELECT job_id, count(*) from EMPLOYEES
GROUP by job_id;
SELECT job_id, count(*) from EMPLOYEES
GROUP by job_id
HAVING count(*) > 5;

Let’s do some queries

SELECT * FROM jobs;
SELECT e.first_name, e.last_name, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
ORDER BY job_title;

Let’s do some queries

SELECT e.first_name, e.last_name,
d.department_name, l.state_province, c.country_name

FROM employees e, departments d, locations l, countries c

WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id

ORDER BY country_name, state_province;