http 140 109 54 73course SQL VCF 2

教材 • http: //140. 109. 54. 73/course/ SQL VCF 2

What is SQL? • Structured Query Language o Access o Manipulate ANSI (American National Standards Institute) standard @#$!#. . ……? 3

What Can SQL do? • retrieve data • insert records • update records • delete records @#$!#. . • … ……? 4

• post-Ingres database – INteractive Graphics REtrieval System 5

Install Postgre. SQL • Google “postgresql” http: //www. postgresql. org/download/ 6

Connect to database IP: 140. 109. 54. 73 Port: 5432 7

Keywords in uppercase • Query editor > Keywords in uppercase 9

Query 10

Database Structure Database • Row (record) Tables o Information about a specific object such as a person. • Column (field) column o category of information • key o A unique value in a table row that is used to identify that particular object 11

Data ftp: //ftp-trace. ncbi. nih. gov/1000 genomes/ftp/release/20110521/ chr 22 Population Description Area Samples Rows CEU CEPH Utah residents (CEPH) with Northern and Western European EUR ancestry NA 12716, NA 12874, NA 11919 147790 YRI Yoruba in Ibadan, Nigeria AFR NA 19247, NA 18498, NA 19197 191173 JPT Japanese in Tokyo, Japan ASN NA 19082, NA 19003, NA 18992 146456 12

Select • SELECT column_name(s) FROM table_name • SELECT * FROM table_name o The asterisk is a quick way of selecting all columns select * from ceu; select chr, pos, id, ref, alt, gt, sample_id from ceu; not case sensitive. (SQL is SELECT is the same as select. ) 13

Distinct • SELECT DISTINCT column_name(s) FROM table_name o The DISTINCT keyword can be used to return only distinct (different) values. select distinct sample_id from ceu; 14

The WHERE Clause • SELECT column_name(s) FROM table_name WHERE column_name operator value select * from ceu where sample_id='NA 12716‘; (48830 rows retrieved. ) 15

The AND & OR Operators select * from ceu where sample_id='NA 12716' and gt='1|0' (15262 rows retrieved. ) select * from ceu where sample_id='NA 12716' and (gt='1|0' or gt='0|1') (31498 rows retrieved. ) 16

The ORDER BY Keyword • SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC select * from ceu order by pos; select * from ceu order by pos desc; select chr, pos, id, sample_id from ceu order by pos, sample_id; 17

The LIMIT clause • LIMIT count OFFSET start select * from ceu limit 10; select * from ceu limit 10 offset 5; 18

Create table • CREATE TABLE table_name ( column_name 1 data_type, column_name 2 data_type, column_name 3 data_type, . . ) 19

Constraints • NOT NULL • UNIQUE • PRIMARY KEY o Unique + Not Null • FOREIGN KEY • CHECK • DEFAULT 20

Create table • Right click on tables > new table… • Naming the table as ‘student’ 21

Schema • To organize database objects into logical groups to make them more manageable. • analogous to directories at the operating system level o except that schemas cannot be nested. 22

Add Columns 23

Add Columns 24

Primary Key 25

Create Table 26

Case Insensitive create table a. Bc ( id serial abc ) create table "a. Bc" ( id serial a. Bc ) 27

The INSERT INTO Statement • INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) • INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ) 28

Using Script 29

Using Script 30

View Data 31

The UPDATE Statement • UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value 32

The UPDATE Statement 33

The DELETE Statement • DELETE FROM table_name WHERE some_column=some_value DELETE FROM student WHERE name='mary'; 34

Edit Data Directly 35

The ALTER TABLE Statement • ALTER TABLE table_name ADD column_name datatype • ALTER TABLE table_name DROP COLUMN column_name 36

Copy • COPY table_name FROM file_name • COPY table_name TO file_name 37

CREATE TABLE myvcf ( chr character varying, pos integer, id character varying, ref character varying, alt character varying, qual double precision, filter character varying, info character varying, format character varying, sample_data character varying ); 38

Copy From copy myvcf from '\VBOXSVRdataNGS_tutorial1_149003461149460645. listunfiltered_noheader. vcf' 39

Summary • • • Select Insert into Delete Update Create table Alter table 40

Reference • http: //www. w 3 schools. com/sql/ 41

Thank You 42