1 / 57

SQL Basics

SQL Basics. What is a database?. Information file – better than plain text Structured Manipulable . . . What is a database?. QUERY. How does it work?. Database File Tables Attributes Rows Relationships between tables. How does it work?. DATABASE. Table 1. Table 2. Table N.

uriel
Download Presentation

SQL Basics

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 Basics

  2. What is a database? • Information file – better than plain text • Structured • Manipulable • . . .

  3. What is a database? QUERY

  4. How does it work? • Database File • Tables • Attributes • Rows • Relationships between tables

  5. How does it work? DATABASE Table 1 Table 2 Table N row 1 row 1 row 1 . . . row 2 row 2 row 2 row 3 row 3 row 3 … … … row N row M row X

  6. Relationships Company DATABASE

  7. Any question?

  8. SQL Start Programs MySQL MySQL Query Browser

  9. Connecting

  10. Connecting Server Host: 10.100.34.37 Username: workshop Password: sql08

  11. Selecting a Database show databases;

  12. Selecting a Database use SQL_workshop;

  13. Showing Tables show tables;

  14. Showing Tables Snps Samples Genotypes

  15. Let’s get our hands dirty

  16. Showing Tables Describe snps;

  17. Showing Tables Describe samples;

  18. Showing Tables Describe genotypes;

  19. Showing Tables SQL_workshop DATABASE

  20. Querying a Table SELECT cols_name, ... FROM tbl_name ;

  21. Our first query SELECT id_snp FROM snps ;

  22. Our first query SELECT id_snp, chromosome FROM snps ;

  23. Our first query SELECT * FROM snps ;

  24. Our first query Exercices: • Get the id_sample and sex of all the samples • Get the genotype of all the genotypes • Get all the attributes of all the samples

  25. Our first query SELECT * FROM samples ; SELECT id_sample, sex FROM samples ; SELECT genotype FROM genotypes ;

  26. WHERE SELECT cols_name, ... FROM tbl_name WHERE condition ;

  27. WHERE SELECT * FROM snps WHERE chromosome=“12” ;

  28. WHERE SELECT * FROM snps WHERE position>2720000 ;

  29. WHERE SELECT * FROM snps WHEREchromosome=“12” AND position>2720000 ;

  30. WHERE Exercices: • Get all the female samples • Get all the CG genotypes • Get all the samples whose phenotype is “lung cancer” or “psoriasis”

  31. WHERE SELECT * FROM samples WHERE phenotype=“lung cancer” OR phenotype=“psoriasis”; SELECT * FROM samples WHERE sex=“F”; SELECT * FROM genotypes WHERE genotype=“CG”;

  32. Order SELECT cols_name, ... FROM tbl_name WHERE condition ORDER BY cols_name [ASC|DESC], ... ;

  33. Order SELECT * FROM snps ORDER BY position ASC ;

  34. Order SELECT * FROM snps ORDER BY position DESC ;

  35. Order SELECT * FROM genotypes ORDER BY id_snp ASC, id_sample DESC ;

  36. Relationships SQL_workshop DATABASE

  37. Relationships SELECT cols_name, ... FROM tbl_name_1, ... WHERE joining_condition, ... ;

  38. Relationships SELECT * FROM snps, genotypes ;

  39. Relationships SELECT * FROM snps, genotypes WHERE snps.id_snp=genotypes.id_snp ;

  40. Relationships SELECT * FROM snps s, genotypes g WHERE s.id_snp=g.id_snp ;

  41. Relationships Exercices: • Get all the genotypes of all the MALE samples • Get all the information (snps, sample & genotype) of all the genotypes • Get all the information (snps, sample & genotype) of all the genotypes of the chromosome 12 of female samples

  42. Relationships • Get all the genotypes of all the MALE samples SELECT * FROM samples s, genotypes g WHERE s.id_sample=g.id_sample AND s.sex=“M”; SELECT g.id_snp, s.id_sample, s.sex,s.age, s.phenotype, g.genotype FROM samples s, genotypes g WHERE s.id_sample=g.id_sample AND s.sex=“M”;

  43. Relationships • Get all the information (snps, sample & genotype) of all the genotypes SELECT * FROM snps s, samples p, genotypes g WHERE s.id_snp=g.id_snp AND p.id_sample=g.id_sample; SELECT s.*, p.*, g.genotype FROM snps s, samples p, genotypes g WHERE s.id_snp=g.id_snp AND p.id_sample=g.id_sample;

  44. Relationships • Get all the information (snps, sample & genotype) of all the genotypes of the chromosome 12 of female samples SELECT * FROM snps s, samples p, genotypes g WHERE s.id_snp=g.id_snp AND p.id_sample=g.id_sample AND s.chromosome=“12” AND p.sex=“F”; SELECT s.*, p.*, g.genotype FROM snps s, samples p, genotypes g WHERE s.id_snp=g.id_snp AND p.id_sample=g.id_sample AND s.chromosome=“12” AND p.sex=“F”;

  45. Time for HARDCORE

  46. Functions • Several SQL Functions • Not only OLD information • Dynamic information

  47. Functions • COUNT • MIN / MAX • AVG • Numeric operators (+ - * / )

  48. Functions • How many snps do I have? SELECT COUNT(*) FROM snps; SELECT COUNT(id_snp) FROM snps; SELECT COUNT(*) AS number_of FROM snps;

  49. Functions • What is the min/max/average position of my snps? SELECT MIN(position) FROM snps; SELECT MAX(position) FROM snps; SELECT AVG(position) FROM snps;

  50. Grouping things • How many snps do I have in each chromosome? SELECT chromosome, COUNT(*) FROM snps GROUP BY chromosome;

More Related