Introduction to sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 41

Introduction to SQL PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Introduction to sql

Introduction to SQL

Ahmet Oezcimen


Agenda

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


1 what is sql

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


2 sql data types

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 …


3 statement categories in sql

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…


4 data modelling example

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.


4 data modelling example cont

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)


5 create drop command structures

5. CREATE & DROP command structures

CREATE TABLE <table_name>

(<column 1>,..,<column n>)

TABLESPACE <tablespace_name>;

DROP TABLE <table_name>;


6 select insert update delete command structures

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>];


7 operators

7. Operators

  • Arithmetical operators

    • /, *, +, -, =, !=, <>, >, <, >=, <=

  • Boolean operators

    • NOT, NOR, AND, OR

  • Logical operators

    • IN, EXISTS, LIKE


8 functions

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…


9 basic sql statement

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%’;


10 advanced sql statements

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;


11 tips and techniques for better sql

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’


11 tips and techniques for better sql cont

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’


11 tips and techniques for better sql cont1

11. Tips and techniques for better SQL (cont.)

Replace

SELECT *

FROM job

WHERE SALARY +1000 = :NEWSALARY

with

SELECT *

FROM job

WHERE SALARY = :NEWSALARY -1000


11 tips and techniques for better sql cont2

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%'


11 tips and techniques for better sql cont3

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


11 tips and techniques for better sql cont4

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.


11 tips and techniques for better sql cont5

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'


11 tips and techniques for better sql cont6

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)


11 tips and techniques for better sql cont7

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 || ''


11 tips and techniques for better sql cont8

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


11 tips and techniques for better sql cont9

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)


11 tips and techniques for better sql cont10

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)


11 tips and techniques for better sql cont11

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)


11 tips and techniques for better sql cont12

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)


11 tips and techniques for better sql cont13

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


12 tools using sql

12. Tools using SQL

  • Oracle SQL*Plus

  • Oracle PL/SQL

  • Oracle Forms

  • Oracle Reports

  • Oracle ProC

  • Java-JDBC

  • Perl-CGI

  • etc …


13 sql plus

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 …


14 literature

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


Introduction to sql

15. QUESTIONS?


Glossary table department

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


Glossary table job

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


Glossary table candidate

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


Glossary table interview

Intno

Intdate

Jobno

Cno

1

2002-02-02 00:00:00.0

1

1

Glossary - Table: interview


Exercises

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)


Some ae examples

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%'


Some ae examples1

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%'


Some ae examples2

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


Some ae examples3

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


  • Login