430 likes | 607 Views
SQL Structured Query Language. João Eduardo Ferreira Luciano Vieira de Araújo Márcio Katsumi Oikawa. Example of Pacient Database. Tables (set of records with same attributes). Relationship between tables. Indicate relationship with only one element in table.
E N D
SQL Structured Query Language João Eduardo Ferreira Luciano Vieira de Araújo Márcio Katsumi Oikawa
Example of Pacient Database • Tables (set of records with same attributes)
Relationship between tables Indicate relationship with only one element in table Indicate relationship with zero, one or more elements in table
Table Content Patient Sample Sequence
Web Page Access site: http://malariadb.ime.usp.br/sqlManager
Incorrect syntax Error
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
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
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 -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. • smallint - Integer (whole number) data from -2^15 (-32,768) through 2^15 - 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 2^31 - 1 (2,147,483,647) characters.
Create Table login or username Create Table Patient_bio19 ( Id_Patient int not null primary key, Age int, Sex char(1), City char(50), Country char(50)) Create Table Patient_login ( Id_Patient int not null primary key, Age int, Sex char(1), City char(50), Country char(50))
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)
Relationship between tables Patient Sample Sequence
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
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) )
Add data into tables • Example: insert into Patient_login (Id_Patient, Age, Sex, City, Country) values (1, 18,’M’, ‘São Paulo’,‘Brasil’) • Command Syntax: INSERT INTO TableName(Col 1, ..., Col N) VALUES (Val 1, ..., Val N)
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’)
Insert into Sequence_login • Example: insert into Sequence_login (Id_Sequence, Id_Sample,GenomeRegion, Size, FastaFormat) values (1, 1,’Env’, 200, ‘>AA0001 Patient1 Sample1 ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA’)
Example Data • Command Syntax: INSERT INTO TableName(Col 1, ..., Col N) VALUES (Val 1, ..., Val N) Patient Sample Sequence
Select Statement SELECT <attribute list> FROM <table-list> [WHERE <condition>] [] – Square brackets indicates that the term is optional • <attribute list> is a list of attributes name whose contents will be shown in query result. • <table-list> is a list of table names required to process the query. • <condition>is a condicional expression that filters the data that will be retrieved by the query.
Visualizing the data inserted SELECT * FROM Patient_login SELECT * FROM Sample_login SELECT * FROM Sequence_login SELECT * Asterisk indicate all attributes of table FROM TableName
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’
Avoid Syntax Errors Difference between symbols Normal SQL Symbols = <> >= <= • Use comma to separate table names and attributes name. Select Id_Patient,Age, Sex Select Id_Patient AgeSex • Use quotations marks to indicate strings (character values) Where City = ‘SãoPaulo’ Where City = São Paulo
Change Database Access site http://malariadb.ime.usp.br/sqlManager/orclicK on
Basics Queries in SQL SELECT Id_Patient, Age, Sex, City FROM Patient • Find identifier, age, sex and city of all pacient. SELECT <attribute list> FROM <table-list> [WHERE <condition>]
Basics Queries in SQL SELECT Age,City FROM Patient WHERE Age > 17 • Find age and city of all pacient with age above 17 years old. SELECT <attribute list> FROM <table-list> [WHERE <condition>]
Basics Queries in SQL SELECT Age,City FROM Patient WHERE Age > 17andSex = ‘F’ • Find age and city of all female pacient with age above 17 years old. SELECT <attribute list> FROM <table-list> [WHERE <condition>]
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
Basics Queries in SQL - Table Join SELECT Patient.Country FROM Patient, Sample WHERE Sample.Id_Sample= 2and Patient.Id_Patient=Sample.Id_Patient Join condition • Find the country of patient who have sample # 2. SELECT <attribute list> FROM <table-list> [WHERE <condition>]
Basics Queries in SQL - Table Join SELECT Patient.Sex, Sample.CountryOrigin, Sequence.GenomeRegion FROM Patient, Sample, Sequence WHERE Sequence.Size > 200and Patient.Id_Patient =Sample.Id_Patientand Sample.Id_Sample =Sequence.Id_Sample • Find the Sex of Pacient, CountryOrigin of Sample and GenomeRegion of Sequence whose size be above 200 bp
Basics Queries in SQL - Table Join SELECT P.Sex, S.CountryOrigin, Se.GenomeRegion FROM PatientP, SampleS, SequenceSe WHERE Se.Size > 200and P.Id_Patient =S.Id_Patientand S.Id_Sample = Se.Id_Sample • Find the Sex of Pacient, CountryOrigin of Sample and GenomeRegion of Sequence whose size be above 200 bp P, S, Se are calledalias
Basics Queries in SQL – Order by SELECT Age,City FROM Patient WHERE Age > 17 ORDER BY Age SELECT Age,City FROM Patient WHERE Age > 17 ORDER BY City Alphabetical order by Field City Numerical order by Field Age 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.
Exercises - Basics Queries in SQL • Find age and country of all male patient. SELECT Age,Country FROM Patient WHERE Sex = ‘M’ SELECT <attribute list> FROM <table-list> [WHERE <condition>]
Exercises - Basics Queries in SQL • Find all sequence (FASTA ) from Pol with size above 200 bp SELECT Fasta FROM Sequence WHERE Size > 200 and GenomeRegion = ‘Pol’ SELECT <attribute list> FROM <table-list> [WHERE <condition>]
Exercises - Basics Queries in SQL • Find city and age of samples collected in 01/03/99, show it in alphabetical order by Pacient’s 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 <attribute list> FROM <table-list> [WHERE <condition>]
Remove data from tables • Example: It will remove all data from table Patient. Delete from Patient • Command Syntax: Delete from TableName [Where <condition>] Be careful!! Always is dangerous remove data. Pay attention on “delete” condition.
Remove data from tables • Example: Delete from Sequence where Size > 200 This example will remove from table Sequence all Sequence with Size > 200
Remove table from database • Example: It will remove the table Patient. Drop table Patient • Command Syntax: Drop table TableName Be careful!! Always is dangerous remove data. Pay attention on “delete” condition.
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