1 / 40

SQL Structured Query Language

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.

hubert
Télécharger la présentation

SQL Structured Query Language

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 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 Indicate relationship with only one element in table Indicate relationship with zero, one or more elements in table

  4. Table Content Patient Sample Sequence

  5. Web Page Access site: http://malariadb.ime.usp.br/sqlManager

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

  12. 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))

  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 • 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)

  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, ‘>AA0001 Patient1 Sample1 ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA’)

  20. Example Data • Command Syntax: INSERT INTO TableName(Col 1, ..., Col N) VALUES (Val 1, ..., Val N) Patient Sample Sequence

  21. 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.

  22. 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

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

  25. Change Database Access site http://malariadb.ime.usp.br/sqlManager/orclicK on

  26. 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>]

  27. 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>]

  28. 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>]

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

  31. 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

  32. 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

  33. 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.

  34. 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>]

  35. 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>]

  36. 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>]

  37. 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.

  38. Remove data from tables • Example: Delete from Sequence where Size > 200 This example will remove from table Sequence all Sequence with Size > 200

  39. 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.

  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

More Related