1 / 25

SQL

SQL. SeQueL -Structured Query Language SQL1 -1986 SQL2 - 1992 better support for Algebraic operations SQL3 - 1999 Post-Relational row and column types, stored procedures, triggers, references, large objects. SQL v. Access Query Builder.

sebastian-rowland
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL • SeQueL • -Structured Query Language • SQL1 -1986 • SQL2 - 1992 • better support for Algebraic operations • SQL3 - 1999 Post-Relational • row and column types, stored procedures, triggers, references, large objects

  2. SQL v. Access Query Builder • ‘Standard’ Database language used in all commercial DBMS’s • GUI query-builder in Access cannot build complex SQL • SQL is a textural language - store, edit, generate • There are jobs in it!

  3. SQL • DATA MANIPULATION (DML) - factbase • QUERY • SELECT • UPDATE • UPDATE • DELETE • INSERT • DATA DEFINITION (DDL) -schema • CREATE, ALTER, DROP • DATA CONTROL (DCL) - access control • GRANT,REVOKE

  4. PROJECT - the columns • SELECT dname FROM dept; • Computed columns • SELECT ename,sal*12 FROM emp; • Renamed columns • SELECT ename, sal*12 as annualSalary • FROM emp;

  5. RESTRICT - the rows • SELECT * • FROM emp • WHERE job=‘Analyst’; • complex conditions: • SELECT * • FROM emp • WHERE job=‘Analyst’ or dept=20;

  6. RESTRICT and PROJECT • in SQL: • SELECT Mgr FROM Emp • WHERE job=‘Analyst’ or deptno=10; • may produce duplicate rows: • SELECT DISTINCT Mgr FROM Emp • WHERE job=‘Analyst’ or deptno=10;

  7. FUNCTIONS • STRINGS • LIKE • DATE • SYSDATE.. • STATISTICAL FUNCTIONS • COUNT, AVG, MIN, MAX, SUM • GENERATE AGGREGATE VALUES • SELECT SUM(sal) FROM emp; • shows total salary over all emps

  8. GROUP BY • OFTEN WANT AGGREGATE GROUPS OF ROWS • SELECT deptno, sum(sal) FROM emp • GROUP BY deptno; • Here the rows are first grouped together with their common values, then any aggregation is done on each subgroup.

  9. HAVING • Restrict Groups shown: • SELECT deptno, sum(sal) FROM emp • GROUP BY deptno • HAVING SUM(sal) > 10000; • After the groups have been aggregated, these new rows can be further selected.

  10. SUBQUERY • List all departments employing analysts • select distinct deptno from emp • where job = ‘analyst’ • List the names of departments employing analysts • SELECT Dname FROM Dept • WHERE Deptno IN ( • select distinct deptno from emp • where job = ‘analyst’);

  11. No Subquery? • MySQL does not support subqueries (until version 4.1) • How can we do the same query without subqueries • Select distinct dname • From dept natural join emp • Where job=‘analyst’;

  12. UNION • SELECT deptno FROM dept • WHERE dname like ‘%ale%’ • UNION • SELECT deptno FROM emp • WHERE sal > 3000;

  13. PRODUCT • SELECT * from dept,emp; • GENERATES N * M ROWS SO GENERALLY TOO LARGE • (4 * 14 = 56 rows on the emp data) • but • BASIS OF ALL JOINS

  14. PRODUCT THEN RESTRICT • SELECT * • FROM Dept, emp • WHERE dept.deptno = emp.deptno • called an EQUI-JOIN • Now only matching rows in both tables appear • since every emp has a non NULL deptno, there will be as many rows in the join as there are rows in the emp table

  15. Product, project, restrict • Can use SQL-2 operators in Access or MySQL • SELECT dname, ename • FROM dept inner join emp • on dept.deptno=emp.deptno; • Combine with restriction: • SELECT dname, ename • FROM dept inner join emp • on dept.deptno=emp.deptno • where job=‘analyst’;

  16. JOIN or SUBQUERY? • SELECT dname • FROM dept inner join emp • on dept.deptno=emp.deptno • where job=‘analyst’ • often several ways to express same query • Which is ‘better’? • What is ‘better’?

  17. Outer Join • Inner join returns only matching rows • We can get non-matching rows too • A LEFT JOIN B • includes all the non matching rows in A as well • A RIGHT JOIN B • includes all the non matching rows in B as well • a FULL JOIN B • includes non matching rows from both tables

  18. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M ,F • 23 fred 23 sue • 23 fred 30 alice • 23 fred 34 julie • 20 joe 23 sue • 20 joe 30 alice • 20 joe 34 julie • 34 bill 23 sue • 34 bill 30 alice • 34 bill 34 julie

  19. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M inner join F on M.age = F.age • 23 fred 23 sue • 23 fred 30 alice • 23 fred 34 julie • 20 joe 23 sue • 20 joe 30 alice • 20 joe 34 julie • 34 bill 23 sue • 34 bill 30 alice • 34 bill 34 julie

  20. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M left join on M.age = F.age • 23 fred 23 sue • 34 bill 34 julie

  21. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M right join on M.age = F.age • 23 fred 23 sue • 34 bill 34 julie

  22. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M full join on M.age = F.age • 23 fred 23 sue • 34 bill 34 julie

  23. M F • 23 fred 23 sue • 20 joe 30 alice • 34 bill 34 julie • Select * from M, F on M.age > F.age • 23 fred 23 sue • 23 fred 30 alice • 23 fred 34 julie • 20 joe 23 sue • 20 joe 30 alice • 20 joe 34 julie • 34 bill 23 sue • 34 bill 30 alice • 34 bill 34 julie

  24. Inner Join (natural Join) • Select * • from M inner join F on M.age=F.age; • Left (Outer) Join • Select * • from M left join F on M.age=F.age; • Right (Outer) Join • Select * • from M right join F on M.age=F.age; • Full (outer) Join • Select * • from M full join F on M.age=F.age;

  25. Show all departments without employees • Select dname • from dept left join emp • on dept.deptno = emp.deptno • shows all the department names • where ename IS NULL; • shows only the departments without employees

More Related