SQL

1

• SQL
• Structured Query Language

João Eduardo Ferreira Luciano Vieira de
Araújo Márcio Katsumi Oikawa
2
Example of Pacient Database

• Tables (set of records with same attributes)

3
Relationship between tables
4
Table Content
Patient
Sample
Sequence
5
Web Page
Access site http//malariadb.ime.usp.br/sqlMana
ger
6
My Database
7
Query Result
8
Incorrect syntax
Error
9
Avoid Syntax Errors

• SQL is case sensitive for attribute list, table
  list and condition.

Id_Patient id_Patient ID_PATIENT
Id_Patient, Age
Id_patient, AGE

10
Create Table

• Command Syntax
• Create Table TableName
• ( Column1 DataType Not
  Null Primay Key,
• Column2 DataType Not
  Null,
• ...
• ColumnN DataType Not
  Null )
• 
  

Square brackets indicates that the term is
optional
11
Examples of data type

• char(n) - Fixed-length character data in
  single-byte character sets with length of n
  characters. n must be a value from 1 through 255.
  Storage size is n bytes.
• int - Integer (whole number) data from -231
  (-2,147,483,648) through 231 - 1
  (2,147,483,647). Storage size is 4 bytes.
• smallint - Integer (whole number) data from -215
  (-32,768) through 215 - 1 (32,767). Storage size
  is 2 bytes.
• datetime - Date and time data from January 1,
  1753, through December 31, 9999, with an accuracy
  of 1/300 of a second, or 3.33 milliseconds.
  Storage size is 8 bytes
• text - Variable-length character data with a
  maximum length of 231 - 1 (2,147,483,647)
  characters.

12
Create Table
Create Table Patient_login
( Id_Patient int not null primary key,
Age int,
Sex char(1),
City char(50),
Country char(50))
13
Create tables with relationship
The relationship between two tables is indicated
with an integrity constraint called Foreign Key.
Constraint ConstraintName ConstraintType
(AttributeName ) References TableName
(AttributeName)
Constraint Fk_IdPatient _login Foreign Key
(Id_Patient ) References Patient_login
(Id_Patient)
14
Relationship between tables
Patient
Sample
Sequence
15
Create tables with relationship

• Create Table Sample_login
• ( Id_Sample int not null primary key,
• Id_Patient int,
• Date DateTime,
• CountryOrigin char(50),
• BodyCompartment char(50),
• Constraint Fk_IdPatient _login Foreign Key
  (Id_Patient )
• References Patient_login (Id_Patient) )

It indicates that table-Sample have relationship
with table-Patient
16
Create tables with relationship

• Create Table Sequence_login
• ( Id_Sequence int not null primary key,
• Id_Sample int,
• GenomeRegion char(20),
• Size int,
• FastaFormat Text,
• Constraint Fk_IdSequence_login Foreign Key
  (Id_Sample )
• References Sample_login (Id_Sample) )

17
Add data into tables

• Command Syntax
• INSERT INTO TableName (Col 1, ..., Col N)
  VALUES (Val 1, ..., Val N)

18
Insert into Sample_login

• Command Syntax
• INSERT INTO TableName (Col 1, ..., Col N)
  VALUES (Val 1, ..., Val N)

• Example

insert into Sample_login (Id_Sample,Id_Patient,
Date,CountryOrigin, BodyCompartment) values
(1, 1,03/25/02, Brasil,Blood)
19
Insert into Sequence_login

• Example

insert into Sequence_login (Id_Sequence,
Id_Sample,GenomeRegion, Size, FastaFormat)
values (1, 1,Env, 200, gtAA0001 Patient1
Sample1 ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA A
CTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGA
ACTGAATCGAACTGAATCGAACTGAATCGA)
20
Example Data

• Command Syntax
• INSERT INTO TableName (Col 1, ..., Col
  N) VALUES (Val 1, ..., Val N)

Patient
Sample
Sequence
21
Select Statement
SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
Square brackets indicates that the term is
optional

• ltattribute listgt is a list of attributes name
  whose contents will be
• shown in query result.
• lttable-listgt is a list of table names
  required to
• process the query.
• ltconditiongt is a condicional expression that
• filters the data that will be retrieved by
  the
• query.

22
Visualizing the data inserted
SELECT
FROM TableName
Asterisk indicate all attributes of table
23
Avoid Syntax Errors

• SQL is case sensitive for attribute list, table
  list and condition.

Select Id_Patient Select id_Patient Select
ID_PATIENT
Select Id_Patient, Age From Patient Where City
São Paulo
Select Id_patient, AGE From patient Where City
São Paulo
24
Avoid Syntax Errors

• Use comma to separate table names and attributes
  name.

Select Id_Patient, Age, Sex
Select Id_Patient Age Sex

• Use quotations marks to indicate strings
  (character values)

Where City São Paulo Where City
São Paulo
25
Change Database
Access site http//malariadb.ime.usp.br/sqlManage
r/ or clicK on
26
Basics Queries in SQL

• Find identifier, age, sex and city of all
  pacient.

SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
27
Basics Queries in SQL

• Find age and city of all pacient with age above
  17 years old.

SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
28
Basics Queries in SQL

• Find age and city of all female pacient
• with age above 17 years old.

SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
29
Using Join

• The JOIN operation is used to combine information
  from two or more tables.
• The Join is represented as a condition in clause
  Where
• Table1.ConnectionAttribute
  Table2.ConnectionAttribute
• Example
• If is necessary to combine information from
  tables Pacient and Sample using connection
  attribute Id_Pacient, we will use
• Patient.Id_Patient Sample.Id_Patient

30
Basics Queries in SQL - Table Join

• Find the country of patient who have sample 2.

SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
31
Basics Queries in SQL - Table Join

• Find the Sex of Pacient, CountryOrigin of Sample
  and GenomeRegion of Sequence whose size be
  above 200 bp

32
Basics Queries in SQL - Table Join

• Find the Sex of Pacient, CountryOrigin of Sample
  and GenomeRegion of Sequence whose size be
  above 200 bp

P, S, Se are called alias
33
Basics Queries in SQL Order by
Some time, we wish to obtain the data in
different orders such as numerical or
alphabetical order. In SQL, it is possible using
the operator ORDER BY.
34
Exercises - Basics Queries in SQL

• Find age and country of all male patient.

SELECT Age,Country FROM Patient WHERE Sex
M
SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
35
Exercises - Basics Queries in SQL

• Find all sequence (FASTA ) from Pol with size
  above 200 bp

SELECT Fasta FROM Sequence WHERE Size gt 200
and GenomeRegion Pol
SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
36
Exercises - Basics Queries in SQL

• Find city and age of samples collected in
  01/03/99, show it in alphabetical order by
  Pacients Age

SELECT Patient.City, Patient.Age FROM
Patient, Sample WHERE Sample.Date
01/03/99 and
Patient.Id_Patient Sample.Id_Patient ORDER BY
Patient.Age
SELECT ltattribute listgt FROM lttable-listgt WHERE
ltconditiongt
37
Remove data from tables

• Command Syntax
• Delete from TableName
• Where ltconditiongt

Be careful!! Always is dangerous remove data.
Pay attention on delete condition.
38
Remove data from tables
39
Remove table from database

• Command Syntax
• Drop table TableName

Be careful!! Always is dangerous remove data.
Pay attention on delete condition.
40
Remove table from database

• Order to remove the table from our database

The integrity constraint will guarantee the
integrity of the data.
1) Drop table Sequence 2) Drop table Sample 3)
Drop table Patient