protein-ligand.sql

-- More than one chain is possible for each code
CREATE TABLE IF NOT EXISTS smallest
                  (
                      id INTEGER PRIMARY KEY NOT NULL,
                      code TEXT NOT NULL,
                      chain TEXT NOT NULL,
                      timestamp TEXT NOT NULL
                  );

CREATE TABLE IF NOT EXISTS ligands
                  (
                      id TEXT PRIMARY KEY NOT NULL,
                      name TEXT NOT NULL,
                      n INTEGER NOT NULL,
                      timestamp TEXT NOT NULL
                  );

CREATE TABLE IF NOT EXISTS pdb_lig
                  (
                      id INTEGER PRIMARY KEY NOT NULL,
                      ligand TEXT NOT NULL,
                      pdb TEXT NOT NULL,
                      chain TEXT NOT NULL,
                      timestamp TEXT NOT NULL,
                      FOREIGN KEY(ligand) REFERENCES ligands(id),
                      FOREIGN KEY(pdb) REFERENCES smallest(code)
                  );


-- The following three statements have the same intent
-- Find entries in smallest with no corresponding entries in pdb_lig
-- Find all proteins that do not contain a ligand

SELECT code, smallest.chain FROM smallest 
LEFT OUTER JOIN pdb_lig ON smallest.code=pdb_lig.pdb and smallest.chain=pdb_lig.chain 
WHERE pdb_lig.pdb IS NULL;

SELECT * FROM smallest WHERE NOT EXISTS (SELECT 1 FROM pdb_lig WHERE smallest.code=pdb_lig.pdb);

SELECT code, chain FROM smallest WHERE code NOT IN (SELECT pdb FROM pdb_lig);

-- Find the max number of atoms (ligands.n) for each unique PDB
SELECT smallest.id as id, code, smallest.chain as chain, max(n) 
FROM pdb_lig
JOIN ligands ON pdb_lig.ligand=ligands.id 
JOIN smallest on pdb_lig.pdb=smallest.code AND pdb_lig.chain=smallest.chain 
GROUP BY smallest.id;

SELECT smallest.id as id, code, smallest.chain as chain, max(n) 
FROM pdb_lig
WHERE smallest.mc_skip=0
JOIN ligands ON pdb_lig.ligand=ligands.id 
JOIN smallest on pdb_lig.pdb=smallest.code AND pdb_lig.chain=smallest.chain 
GROUP BY smallest.id
HAVING max(n)<=5;

-- Find lines in pdb_go with uniprotkbac that doesn't exist in pdb (but FK?!)
SELECT * FROM pdb_go
WHERE NOT EXISTS (SELECT 1 FROM smallest WHERE pdb_go.uniprotkbac=smallest.uniprotkbac)

SELECT smallest.id as id, code, smallest.chain as chain, max(n)
FROM pdb_lig
JOIN ligands ON pdb_lig.ligand=ligands.id
JOIN smallest on pdb_lig.pdb=smallest.code AND pdb_lig.chain=smallest.chain
WHERE smallest.mc_skip=0
GROUP BY smallest.id, code, smallest.chain
HAVING max(n)<=5;