1 / 41

Introduction to SQL

Introduction to SQL. Ahmet Oezcimen. Agenda. 1. What is SQL? 2. SQL data types 3. Statement categories in SQL 4. Data modelling example 5. CREATE & DROP command structures 6. SELECT, INSERT, UPDATE & DELETE command structures 7. Operators 8. Functions 9. Basic SQL statements

kesler
Télécharger la présentation

Introduction to 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. Introduction to SQL Ahmet Oezcimen

  2. Agenda 1. What is SQL? 2. SQL data types 3. Statement categories in SQL 4. Data modelling example 5. CREATE & DROP command structures 6. SELECT, INSERT, UPDATE & DELETE command structures 7. Operators 8. Functions 9. Basic SQL statements 10. Advanced SQL statements 11. Tips and techniques for better SQL 12. Tools using SQL 13. SQL*Plus 14. Literature 15. Questions

  3. 1. What is SQL? • SQL stands for Structured Query Language • SQL is an ANSI (American National Standards Institute) standard language • SQL works with Oracle, Sybase, Informix, MS SQL Server, DB2, MySQL, etc.. • SQL is easy to learn

  4. 2. SQL data types • VARCHAR2 – contains text string up to 4,000b • NUMBER - contains numeric data • DATE – contains date data • BLOB – Large binary object up to 4G • CLOB – Large character-based object uo to 4G • BFILE – Large external file • Etc …

  5. 3. Statement categories in SQL • DDL ( Data Definition Language ) • CREATE, ALTER, DROP, etc… • DML ( Data Manipulation Language ) • SELECT, UPDATE, DELETE, INSERT • Transaction Control Commands • COMMIT, ROLLBACK, SAVEPOINT, etc… • Session Control Commands • ALTER SESSION • DCL (Data Control Language) • GRANT, REVOKE, etc… • Etc…

  6. 4. Data modelling example Department (deptno) Job (jobno) Interview (intno) Candidate (cno) Explanation: A deparment might have more than one job therefore also might invite more than one candidate and for one job might more than one interview and one candidate might be invited several times.

  7. 4. Data modelling example (cont.) Deparment : deptno number(5), deptname varchar2(25), depthead varchar2(35) Job : jobno number(5), jobtitle varchar2(25),salary number(5), deptno number(5) Candidate: cno number(5), cname varchar2(25), deptno number(5), ctel varchar2(20), position varchar(25) Interview: intno number(5), intdate date, jobno number(5), cno number(5)

  8. 5. CREATE & DROP command structures CREATE TABLE <table_name> (<column 1>,..,<column n>) TABLESPACE <tablespace_name>; DROP TABLE <table_name>;

  9. 6. SELECT, INSERT, UPDATE & DELETE command structures SELECT [distinct] <column 1>,..,<column n> FROM <table 1 or view 1> [alias],.., <table n or view n> [alias] WHERE <condition> GROUP BY <column 1>,..,<column n> HAVING <condition> CONNECT BY PRIOR <condition> START WITH <condition> ORDER BY <column 1>,..,<column n> [ASC or DESC]; DELETE FROM <table> WHERE <condition>; INSERT INTO <table> [(<column list>)] VALUES (value list); UPDATE <table> SET <column> = <value> [WHERE <condition>];

  10. 7. Operators • Arithmetical operators • /, *, +, -, =, !=, <>, >, <, >=, <= • Boolean operators • NOT, NOR, AND, OR • Logical operators • IN, EXISTS, LIKE

  11. 8. Functions • Text functions • Lpad(x,y [,z]), rpad(x,y [,z]), lower(x), upper(x), initcap(x), length(x), substr(x,y[,z]),instr(x,y), trim(), replace(x,y [,z]) • Math functions • Abs(x), ceil(x), floor(x), mod(x,y), round(x,y), sign(x), sqrt(x), trunc(x,y), vsize(x), Nvl(), decode(), greatest(x,y,…), least(x,y,…) • Date functions • Sysdate, add_months(x,y), last_day(x), months_between(x,y), new_time(x,y,z), next_day(x) • Conversion functions • To_char(x), to_number(x), to_date(x), etc… • Group functions • avg(x), count(x), max(x), min(x), sum(x), etc…

  12. 9. Basic SQL statement • The most easiest SQL is just to select a table • Select * from department; • select deptname, depthead from department; • Conditional Selection • Select * from department where depthead=‘Alvis Brazma’; • Relational operators • Select * from job where salary > 2000; • More complex conditions • Select * from job where deptno=1 or deptno=2; • Select * from job where deptno=3 and salary > 2000; • Using IN & BETWEEN • Select * from job where deptno in (1,2); • Select * from job where deptno between 1 and 3; • Using LIKE • Select * from department where depthead like ‘Alvis%’;

  13. 10. Advanced SQL statements • Joins & outer joins • Select * from department, job where department.deptno=job.deptno; • select * from department a, job b where a.deptno=b.deptno(+); • Group by • Select max(salary) from job; • Select jobtitle,max(salary) from job group by jobtitle; • Subqueries • select * from job where deptno in (select deptno from department); • Exists & all • select * from job where exists (select * from department); • select * from job where salary >= all (select salary from job); • Union • select deptno,deptname from department union select jobno,jobtitle from job; • Intersect & minus • select deptno from job minus select deptno from job where jobbtitle=‘DBA’; • select jobtitle from job where deptno=1 intersect select jobtitle from job where deptno=2;

  14. 11. Tips and techniques for better SQL Index will not be used when a function is performed on an indexed field. SELECT * FROM department WHERE SUBSTR(DEPTNAME, 1, 3) = ‘Arr’

  15. 11. Tips and techniques for better SQL (cont.) • If you cannot avoid using a function: • Use the INDEX hint. SELECT /*+ INDEX(DEPARTMENT DEPT_IDX_1) */* FROM DEPARTMENT WHERE SUBSTR(DEPTNAME, 1, 3) = ‘Arr’

  16. 11. Tips and techniques for better SQL (cont.) Replace SELECT * FROM job WHERE SALARY +1000 = :NEWSALARY with SELECT * FROM job WHERE SALARY = :NEWSALARY -1000

  17. 11. Tips and techniques for better SQL (cont.) Indexes are not used, and a full table scan is done when the WHERE clause contains: != (NOT EQUALS) LIKE '%SA%'

  18. 11. Tips and techniques for better SQL (cont.) Depending on the range of the numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index. SELECT * FROM job WHERE salary BETWEEN 2500 AND 3500

  19. 11. Tips and techniques for better SQL (cont.) When retrieving a large portion of the table’s data, full table scans are likely to offer the best performance. When retrieving small number of rows, avoid full table scans.

  20. 11. Tips and techniques for better SQL (cont.) Sometimes DO disable the index. SELECT * FROM job WHERE SALARY + 0 = '10000' SELECT * FROM department WHERE deptname || '' = ‘A'

  21. 11. Tips and techniques for better SQL (cont.) Select the smallest table or smallest result set first. SELECT a.deptname FROM department a, job b WHERE a.deptno=b.deptno (assume a.deptno and b.deptno are indexed)

  22. 11. Tips and techniques for better SQL (cont.) Small table should drive the large table. If job is a large table and department is a small one. Disable index on department. This changes the table driving path. Replace SELECT * FROM job large, department small WHERE large.deptno = small.deptno With SELECT * FROM job large, department small WHERE large.deptno = small.deptno || ''

  23. 11. Tips and techniques for better SQL (cont.) A table join is normally better than sub-query Replace: SELECT * FROM job a WHERE a.deptno IN (SELECT b.deptno FROM department b) With: SELECT A.* FROM job A, department B WHERE A.job = B.department

  24. 11. Tips and techniques for better SQL (cont.) These statements have the same result. Assume table job, department relationship is one to many. SELECT * FROM job WHERE deptno IN (SELECT deptno FROM department) SELECT * FROM job WHERE EXISTS (SELECT deptno FROM department WHERE job.deptno = department.deptno)

  25. 11. Tips and techniques for better SQL (cont.) Use IN operator IN checks all rows. Only use IN if the table in the subquery is extremely small. SELECT * FROM job WHERE deptno IN (SELECT deptno FROM department)

  26. 11. Tips and techniques for better SQL (cont.) Use EXISTS operator EXISTS makes use of the index on the deptno column in the department table. For large tables, it may be faster. SELECT * FROM job WHERE EXISTS (SELECT deptno FROM department WHERE job.deptno = department.deptno)

  27. 11. Tips and techniques for better SQL (cont.) If A.STATE and B.STATE have a unique index, Replace: SELECT a.deptno, b.deptno FROM department a, job b WHERE a.deptno = b.deptno (+) With: SELECT a.deptname, b.jobtitle FROM department a, job b WHERE a.deptno = b.deptno UNION ALL SELECT c.deptname, NULL FROM department c WHERE NOT EXISTS (SELECT 'X' FROM job d WHERE c.deptno = d.deptno)

  28. 11. Tips and techniques for better SQL (cont.) Replace: SELECT * FROM department a, job b, candidate c WHERE a.deptno=b.deptno and b.deptno=c.deptno and c.deptno=1 With: SELECT * FROM A,B,C FROM department a, job b, candidate c WHERE a.deptno=b.deptno and b.deptno=c.deptno and a.deptno=1 and b.deptno=1 and c.deptno=1

  29. 12. Tools using SQL • Oracle SQL*Plus • Oracle PL/SQL • Oracle Forms • Oracle Reports • Oracle ProC • Java-JDBC • Perl-CGI • etc …

  30. 13. SQL*Plus • Starting SQL*Plus • Sqlplus [<username>/<password>] [@<db name>] [@<script-name>] • Running a script • SQL>Start <script-name> • SQL>@<script-name> • Exiting SQL*Plus • exit • Some important SQL*Plus commands • Host or !, set, spool, rem, accept, prompt, @ or start, commit, rollback, help, desc, ttitle, btitle, break, etc …

  31. 14. Literature • Oracle8i The complete reference Autors:Kevin Loney, George Koch • Learning SQL Autors: Richard W.Earp,Sikha S.Bagui • Oracle SQL and PL/SQL Handbook Autors: John Adolph Palinski

  32. 15. QUESTIONS?

  33. Deptno Deptname Depthead 2 MSD Kim Henrick 3 Swiss-Prot Rolf Apweiler 4 TrEMBL Rolf Apweiler 1 ArrayExpress Alvis Brazma 5 Personnel Keith Wiliamson Glossary –Table: department

  34. Jobno Jobtitle Salary Deptno 6 Database coordinator 5000 2 7 Database curator 4000 2 8 Software Engineer 3000 2 9 WEB Developer 3500 2 10 DBA 2000 2 11 Database coordinator 5000 3 12 Database curator 4000 3 13 Software Engineer 3000 3 14 WEB Developer 3500 3 15 DBA 2000 3 16 Database coordinator 5000 4 17 Database curator 4000 4 18 Software Engineer 3000 4 19 WEB Developer 3500 4 20 DBA 2000 4 1 Database coordinator 5000 1 2 Database curator 4000 1 3 Software Engineer 3000 1 4 WEB Developer 3500 1 5 DBA 2000 1 Glossary - Table: job

  35. Cno Cname Ctel Position Deptno 1 Richard 0044-1243-123456 Database coordinator 1 2 Don 0044-1243-123456 Database coordinator 1 3 Donald 0044-1243-123456 Database coordinator 1 4 Marie 0044-1243-123456 Database coordinator 1 5 Elisa 0044-1243-123456 Database coordinator 1 Glossary - Table: candidate

  36. Intno Intdate Jobno Cno 1 2002-02-02 00:00:00.0 1 1 Glossary - Table: interview

  37. Exercises • Very simple queries • Queries by using WHERE condition • Queries by using ORDER BY • Queries by using functions • Queries by using joins • Queries by using subqueries • Queries by using GROUP BY • Using queries for any other database (ARRAYS, EXPERIMENTS, PROTOCOLS)

  38. Some AE examples -Show me arrays that use PCR products: Select i.identifier as array, o.value as technology From tt_identifiable i, tt_physicalarraydesign a, tt_featuregroup f, tt_ontologyentry o where i.id = a.id and a.id = f.t_arraydesign_id and f.technologytype_id = o.id and lower( o.value ) like '%pcr%'

  39. Some AE examples -Show me all the bibliographic references that are books: select b.title as Title, b.authors as Author from tt_bibliographicreference b, tt_parameters_t_bibliogra l, tt_ontologyentry o where b.id = l.t_bibliographicreference_id and l.parameters_id = o.id and lower( o.value ) like '%book%'

  40. Some AE examples -Produce a table of all the experiments and the arrays: select distinct ie.identifier as experiment, ia.identifier as arraydesign from tt_identifiable ie, tt_experiment e, tt_bioassays_t_experiment eb, tt_physicalbioassay pba, tt_bioassaycreation bac, tt_array a, tt_identifiable ia where ie.id = e.id and e.id = eb.t_experiment_id and eb.bioassays_id = pba.id and pba.bioassaycreation_id = bac.id and bac.array_id = a.id and ia.id = a.arraydesign_id order by ie.identifier asc

  41. Some AE examples • Produce a table of all the experiments and the species they study: • select distinct i.identifier as experiment, o.value as species • from tt_identifiable i, tt_biomaterials_experiments eb, • tt_characteris_t_biomateri bo, tt_ontologyentry o • where i.id = eb.experiments_id • and eb.biomaterials_id = bo.t_biomaterial_id • and bo.characteristics_id = o.id • and o.category like '%species%' • order by i.identifier

More Related