chapter 3

Structured Query Language (SQL)

In this chapter, the most common way of working with tables in a RDBMS is introduced. The SQL language provides ways to create tables, insert data, select data, delete data, update data, join tables, create table schemas, define functions, etc. SQL has many other features, not all of which are covered here. [view wordle] [hide wordle]

Page
  
22 
Create Schema achemcompany;
Create Table achemcompany.structure (
           smiles Text, 
           id Integer, 
           mw numeric(6,2), 
           added Timestamp(0));

23

 
Create Table "aChemCompany".structure ( 
           smiles Text, 
           "ID" Integer, 
           "MW" numeric(6,2), 
           added Timestamp(0));
Insert Into achemcompany.structure (id, smiles, mw, added)          
           Values (1001, 'CC(=O)OC', 74.09, current_timestamp);
24 
Select smiles,mw From achemcompany.structure Where mw < 100; 
Create Table hiv1.prot_inh ( 
           id Integer, 
           ic50 Float, 
           ki Float, 
           tested Timestamp(0)); 

Select smiles,ki 
           From achemcompany.structure Join hiv1.prot_inh 
           On hiv1.prot_inh.id = achemcompany.structure.id 
           Where ki < 0.5;
25 
Select smiles,ki From achemcompany.structure, hiv1.prot_inh          
      Where hiv1.prot_inh.id = achemcompany.structure.id And ki < 0.5; 
Update achemcompany.structure Set mw=103.14 where id=1003;
26 
Delete from achemcompany.structure where id=1002;
27 
Create Function convert.atm_to_kpa(Numeric) Returns Numeric          
      As 'Select $1 * 101.325;' Language SQL; 
Select bp, convert.atm_to_kpa(bp_press) as "kPa" from epa.properties; 
 Select ic50 From hiv_inh; 
Select avg(ic50) From hiv_inh; 
Select id, avg(ic50) From hiv_inh Group By id;
28 
Create Domain smiles As Text Check (valid(Value)); 
Create Trigger standardize Before Insert Or Update On atable          
    For Each Row Execute Procedure standardize();
Create View test_set (logp, temp) As
    Select logp, temp From literature_data Where ref Like 'Hansch%1995%' And temp Is Not Null;
29 
Select logp From logp Where temp = 25 And 
    ref Like '%Hansch%' Or ref Like '%Yalkowsky%';
Select logp From logp Where temp = 25 And 
     (ref Like '%Hansch%' Or ref Like '%Yalkowsky%');
Select logp,temp from logp Where ref Ilike '%Hansch%' And temp = 25 
     Union 
Select logp,temp From merck Where temp Is Not Null;