SQLite SQL IDE
http://www.tutorialspoint.com/execute_sql_online.php
Script file extension is .sql
/* Comment */
STATEMENT keywords: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, DESC, ALTER, DROP, CREATE, USE, SHOW, COMMIT, ROLLBACK, ...
STATEMENTS are not case-sensitive and they end with
; (STATEMENTS operate on tables, index, databases)
CLAUSES: distinct, where, and/or, in, between, like, order by, group by, count, having,
* for all selection
% to match zero or more characters
_ to match exact character
space for selected few
Use your schema (blueprint)
e.g. use mycobacteria;
----------------------------------
Basic syntax:
select column, from table, where condition is
SELECT column1, column2...........columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];
e.g.SELECT column1, column2
FROM table1, table2
WHERE column2='value';
last_name= 'Patel'
e.g. SELECT book_id, title, description
FROM books
WHERE genre = 'novel';
e.g. SELECT book_id, title, pub_year,
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Smith';
----------------------------------
Some examples:
SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';
SELECT First_Name, Nickname FROM Friends WHERE Nickname LIKE '%brain%';
SELECT * FROM Friends WHERE First_Name LIKE '_en';
#dst, pacbio_snps, sanger_snps, discrepant_snps, gene_list, lineage are tables
select * from discrepant_snps;
select * from gene_list;
select * from lineage;
select Miru from lineage;
select Spoligo, Miru from lineage;
select * from ncbi_snps;*/
select * from ncbi_snps where gene like '%gyrase%';*/#to serach the pattern 'gyrase’;
select Isolate_id, lineage_name from lineage*/mv scr;
select * from tb_genes
select gene_name from tb_genes
select gene_end from tb_genes
select * from dst_original
select* from pacbio_snps
select * from dst_original where inh ='R' or inh = 'S'
select count(d.isolate_id) from dst_original d where isolate_id in (select s.Isolate_Id from sanger_sequenced_list s
where inhA_promoter='Y') and isolate_id in (select distinct isolate_id from seq_data) and d.inh in ('R','S')
select count(distinct s.isolate_id) from sanger_sequenced_list s, dst_original d where inh in ('R','S') and katG='Y'
select count(distinct s.isolate_id) from seq_data s, dst_original d where inh in ('R','S')
select * from true_ra_snps
Select distinct p.isolate_id from pacbio_snps p, dst d where p.isolate_id=d.isolate_id and d.fq='R'
select * from country;
SELECT count(distinct position) FROM pacbio_snps;
SELECT count(distinct position, mutation) FROM pacbio_snps;
SELECT count(*) FROM pacbio_snps WHERE position = 761110;
SELECT count(*)FROM pacbio_snps WHERE position = 761110 AND mutation='T';
SELECT count(*) FROM pacbio_snps WHERE position = 761110 AND mutation='G';
SELECT count(pacbio_snps.isolate_id) FROM pacbio_snps, dst WHERE pacbio_snps.isolate_id = dst.isolate_id AND dst.rif = 'R' AND pacbio_snps.position=761110;
SELECT position, count(p.isolate_id), rif from pacbio_snps p, dst d WHERE p.isolate_id = d.isolate_id AND position in max(count(position)) /*AND rif = 'R'*/
GROUP BY rif;
SELECT p.isolate_id #(select isolate)
FROM pacbio_snps p, dst d #(from table1 and table2)
WHERE p.isolate_id=d.isolate_id #(those satisfy the condition the isolates are same)
AND d.DRUG_NAME='R'; # (and are resistant)
select count(distinct s.isolate_id)
from sanger_sequenced_list s, dst_original d
where inh in ('R','S')
and inhA_promoter='Y';
select count(sanger_sequenced_list. isolate_id, pacbio_snps.isolate_id)
from sanger_sequenced_list, pacbio_snps, dst_original
where sanger_sequenced_list.isolate_id = dst_original.isolate_id
and pacbio_snps.isolate_id = dst_original.isolate_id
and inh is not null
select distinct position, #(select count(isolate_id)
from pacbio_snps_original p
where exists (select1
from dst d
where p.isolate_id=d.isolate_id
and rif = 'r')
and p.position = s.position
and position not in(761155)) num_res,
(select count(isolate_id)
From pacbio_snps_original p
where exists (select 1 from dst d where p.isolate_id=d.isolate_id and rif = 's')
and p.position = s.position)
num_sus (#no. susceptible)
from pacbio_snps_original s
order by num_sus, num_res desc
limit 50;
--------------------------------
1|Mango
2|Pineapple
1|Mango
----------------------------------
#If it says table exists, delete the pre-formed database and execute the script again.
BEGIN TRANSACTION;
/* Create a table CUSTOMERS */
CREATE TABLE CUSTOMERS (ID integer PRIMARY KEY, Name text, Age integer, Address text, Salary float);
/* Create records in the table made*/
INSERT INTO CUSTOMERS VALUES(1,'Rick', 34, 'Paris', 200.00);
INSERT INTO CUSTOMERS VALUES(2,'Alice', 28, 'Chicago', 500.00);
INSERT INTO CUSTOMERS VALUES(3,'Judy', 24, 'Seattle', 300.00);
INSERT INTO CUSTOMERS VALUES(4,'Nicole', 19, 'Fresno', 100.00);
INSERT INTO CUSTOMERS VALUES(5,'Kelly', 26, 'Boise', 150.00);
INSERT INTO CUSTOMERS VALUES(6,'Nicole', 37, 'Augusta', 250.00);
COMMIT;
/* Display all the records from the table */
SELECT * FROM CUSTOMERS;
1|Rick|34|Paris|200.0
2|Alice|28|Chicago|500.0
3|Judy|24|Seattle|300.0
4|Nicole|19|Fresno|100.0
5|Kelly|26|Boise|150.0
6|Nicole|37|Augusta|250.0
SELECT * FROM CUSTOMERS WHERE SALARY = 100;
4|Nicole|19|Fresno|100.0
SELECT * FROM CUSTOMERS WHERE SALARY > 200;
2|Alice|28|Chicago|500.0
3|Judy|24|Seattle|300.0
6|Nicole|37|Augusta|250.0
/* To perform any mathematical operation in any query */
SELECT numerical_expression as OPERATION_NAME
FROM table_name
WHERE CONDITION;
SELECT (15 + 6) AS ADDITION
FROM CUSTOMERS
WHERE Age = 28;
21
SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
6
SELECT CURRENT_TIMESTAMP;
2015-12-05 06:27:20
Database creation and table insertion into it (yet to make it work)
CREATE DATABASE testDB;
CREATE DATABASE bioPedia;
SHOW DATABASES;
DROP DATABASE testDB;
SHOW DATABASES;
USE bioPedia;
CREATE TABLE EMPLOYEES(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
DESC EMPLOYEES;
/*DROP TABLE EMPLOYEES;*/
DESC EMPLOYEES;
INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Cindy', 37, 'CA', 700.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Ryan', 42, 'MT', 1200.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Rob', 60, 'MA', 600.00 );
-------------------------------------------------------
Database creation..............
mysql -u root -p
mysql> CREATE DATABASE testdb;
mysql> GRANT ALL PRIVILEGES ON testdb.* TO Seema@localhost IDENTIFIED BY 'pass';
$ mysql -u Seema -p
mysql> SHOW DATABASES;
-------------------------------------------------------
#Printing server version and current database name
mysql> SELECT VERSION(), DATABASE();
mysql> use database_name;
#Select all names except Smith
mysql> SELECT * FROM database_name WHERE user !=Smith;
#Use of AND and OR operators
mysql> SELECT * FROM database_name WHERE user = Carl AND root = Mike
mysql> SELECT * FROM database_name WHERE user != Carl OR root != Mike
#To see only certain rows from a result set
mysql> SELECT * FROM database_name LIMIT 1;
mysql> SELECT * FROM database_name LIMIT 5;
mysql> SELECT * FROM database_name ORDER BY column_name LIMIT 1;
mysql> SELECT CURRENT_DATE();
#To export tables as an XML file in MySQL
mysql -u USER_NAME –xml -e 'SELECT * FROM table_name' > table_name.xml
mysql -u root -p
MariaDB [(none)] > CREATE DATABASE my_db;
MariaDB is RDBMS and it is one of the most popular database servers
#Install
sudo apt install default-mysql-
sudo apt install mariadb-server
#Check installation
mysql -u root -p
#You will greeted with MariaDB mysql prompt. You can type sql commands to see databases, version and much more:
MariaDB [(none)]> show databases;
MariaDB [(none)]> exit
-------------------------------------------
#Connect to mysql database of ucsc
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A
#Extract Similar Genes From Ucsc Via Mysql Command
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -e 'select * from knownGene' -N | awk -f script.awk
#script.awk
BEGIN {
FS="\t";
}
{
split($9,exonStarts,",");
split($10,exonEnds,",");
geneSize=1.0*int($5)-int($4);
exonCount=int($8);
if(exonCount<2)
{
next;
}
if($3=="+")
{
printf("%f\t%s\n",(exonStarts[2]-exonEnds[1])/geneSize,$0);
}
else
{
printf("%f\t%s\n",(exonStarts[exonCount]-exonEnds[exonCount-1])/geneSize,$0);
}
}
#Download genome annotations from UCSC's MySQL database
echo -e "chr1\t10000\t20000\nchr1\t30000\t40000" |\
awk -F '\t' '{printf("select \"%s\",\"%s\",\"%s\", G.name,G.txStart,G.txEnd from refGene as G where chrom=\"%s\" and not(%s>txEnd or %s<txStart);\n",$1,$2,$3,$1,$2,$3);}' |\
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -N -D hg38
