A non-violent introduction to Relational Databases

(part 2)

1.  Introduction to Relational Databases 2.  Fundamentals of Relational Databases 3.  Using Relational Databases (SQL) 4.  Using Relational Databases (DBI) 5.  Designing Relational Databases

sqlite

Command Description

.help help

.show <parameter_name> sqlite3 various settings

.databases database names and associated files

.tables database tables

.schema <table_name> schema of table

.header [ON|OFF] display/hide table headers on output

.mode <the_mode> mode of output table

.dump Dumpt database in SQL format

.quit quit

https://sqlite.org

Configure your sqlite

sqlite> select * from employee;

sqlite> .header ONsqlite> .mode columnsqlite> .show

sqlite> select * from employee;sqlite> select * from department;

DEPARTMENT

+--------+------------+----------+| DEPTNO | DNAME | LOC |+--------+------------+----------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON |+--------+------------+----------+

EMPLOYEE +-------+--------+-----------+------+-----------+------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+--------+-----------+------+-----------+------+------+--------+| 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | NULL | 20 || 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 || 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 || 7566 | JONES | MANAGER | 7839 | 2-Apr-81 | 2975 | NULL | 20 || 7654 | MARTIN | SALESMAN | 7698 | 28-Sep-81 | 1250 | 1400 | 30 || 7698 | BLAKE | MANAGER | 7839 | 1-May-81 | 2850 | NULL | 30 || 7782 | CLARK | MANAGER | 7839 | 9-Jun-81 | 2450 | NULL | 10 || 7788 | SCOTT | ANALYST | 7566 | 27-Jun-90 | 3000 | NULL | 20 || 7839 | KING | PRESIDENT | 0 | 17-Nov-81 | 5000 | NULL | 10 || 7844 | TURNER | SALESMAN | 7698 | 8-Sep-90 | 1500 | NULL | 30 || 7876 | ADAMS | CLERK | 7788 | 31-Jul-90 | 1100 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 3-Dec-81 | 950 | NULL | 30 || 7902 | FORD | ANALYST | 7566 | 3-Dec-81 | 3000 | NULL | 20 || 7934 | MILLER | CLERK | 7782 | 23-Jan-82 | 1300 | NULL | 10 |+-------+--------+-----------+------+-----------+------+------+--------+

standard SELECT syntax

SELECT column_names FROM table_names WHERE search_condition GROUP BY column_name HAVING group_selection_condition ORDER_BY column_names

For sqlite dialect of SQL go to: http://www.sqlite.org/lang.html

SQL conditionals n  Six basic conditionals!

n  = equaln  <> or != not equal n  < less than n  <= less t or ehanqual to n  > greater than n  >= greater than or equal to

n  Example! n  SELECT ename FROM emp WHERE sal > 2500;

DISTINCT, BETWEEN and NOT n  Example: Removing duplicate entries!

SELECT DISTINCT job FROM emp WHERE sal BETWEEN 1000 AND 2000;

n  Example: Negating a condition n  SELECT DISTINCT job FROM emp WHERE sal NOT BETWEEN 1000 AND 4000;

Ordering Rows of a query result n  Example: List all employees in dept. 10 by salary

SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = 10 ORDER BY sal;

n  Example: SELECT job, sal, ename

FROM emp WHERE job IN (‘CLERK’, ‘MANAGER’) ORDER BY job, sal DESC;

Arithmetic functions n  Arithmetic functions applied to individuals

SELECT ename, sal+commFROM empWHERE job = ‘SALESMAN’;

n  Main SQL arithmetic functions

+, -, *, /, %POWER, ROUND, TRUNC, ABS, SQRT

Handling NULL values

n  Employees who do not receive commissions

SELECT ename, sal, commFROM empWHERE (comm IS NULL) AND (deptno=30);

n  Employees who receive commissions

SELECT ename, sal, commFROM empWHERE (comm IS NOT NULL) AND (deptno=30);

Aggregation functions

n  Five main aggregation functions: SUM, AVG, MAX, MIN and COUNTn  Aggregagation functions are applied to sets of values

SELECT AVG(sal)FROM empWHERE job=‘CLERK’;

n  How many analysts are working for the company?

SELECT count(*)FROM empWHERE job =‘ANALYST’;

Aggregation functions

n  What are max, and min, total and number of commission paid by the company?

SELECT MIN(comm), MAX(comm),SUM(comm), COUNT(comm),FROM empWHERE job=‘SALESMAN;

n  MAX and MIN also work alphabetically

SELECT MIN(ename), MAX(ename)FROM emp;

GROUP BY

n  Sum the salaries of each department

SELECT DEPTNO, SUM(sal)FROM empGROUP BY deptno;

n  Average salary for each job

SELECT job, AVG(sal)FROM empGROUP BY job;

GROUP BY…HAVING

n  Used to select groupsn  Example: average salary of departments where nobody earns less than

1000

SELECT deptno, AVG(sal)FROM empGROUP BY deptno

HAVING MIN(sal) >= 1000;

MULTIPLE TABLES •  Single tables are difficult to maintain.•  Single tables are error prone. •  They don’t scale well.!

•  e.g., What if a different lecturer takes over a course?

OUTER JOIN

+--------+------------+----------+-------+--------+-----------+------+-----------+------+------+--------+| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+--------+------------+----------+-------+--------+-----------+------+-----------+------+------+--------+| 10 | ACCOUNTING | NEW YORK | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 30 | SALES | CHICAGO | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 40 | OPERATIONS | BOSTON | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 10 | ACCOUNTING | NEW YORK | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 20 | RESEARCH | DALLAS | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 40 | OPERATIONS | BOSTON | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 10 | ACCOUNTING | NEW YORK | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 | | 20 | RESEARCH | DALLAS | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 | | 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 |

outer product pairs up every row of DEPT with every row of EMP

DEPT EMP

INNER JOIN

+--------+------------+----------+-------+--------+-----------+------+-----------+------+------+--------+| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+--------+------------+----------+-------+--------+-----------+------+-----------+------+------+--------+| 10 | ACCOUNTING | NEW YORK | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 30 | SALES | CHICAGO | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 40 | OPERATIONS | BOSTON | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | 0 | 20 | | 10 | ACCOUNTING | NEW YORK | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 20 | RESEARCH | DALLAS | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 40 | OPERATIONS | BOSTON | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 10 | ACCOUNTING | NEW YORK | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 | | 20 | RESEARCH | DALLAS | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 | | 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 |

keep only the rows where the common field (DEPNO) is the same

DEPT EMP

Joins n  Example: Which employees are located in ‘DALLAS’ ?

SELECT enameFROM emp, deptWHERE emp.deptno = dept.deptno

AND loc = ‘DALLAS’;

n  Example: Where is ‘ALLEN’ ?

SELECT ename, locFROM emp, deptWHERE emp.deptno = dept.deptno

AND ename = ‘ALLEN;

Joining a table with itself n  Example: Employees whose salary exceeds their

managers

SELECT e.ename, e.salFROM emp as e, emp as mWHERE e.mgr = m.empnoAND e.sal > m.sal;