Lecture 37
April 7

Today:
	Multi-table queries
	Views
	Variables 

Multi-table Queries

Last class we  looked for  Canadian A-students using  two tables:

Student Info                                                    
                                                                                
ID	Name	Country	

Grades

ID	Name	Grade	

How do multiple tables work?

	First,  we create a table with combination of rows from two tables:

 Student Info                       Grades
ID       Name Country        ID Name  Grade
Row1                                  Row1
row1                                   Row 2
row1                                   Row3
row2                                   Row1
row 2                                  Row 2
	Then, by applying WHERE, we can  remove  the rows which contain data about students who are not Canadian and   maintain the  rows  with data  for Canadian A-students.
	see the terminal to view the tables created.

Examples
	
	There are  GeneInfo, GeneExperiments, and GeneReg tables:
            GeneInfo (Gene ID, Location, and Function)
            GeneExpts ( ExpID, Gene ID,  Expressed)
            Gene Reg (Regulator and Regulatee)  
	Q: Return genes which  are expressed?
	
            SELECT GeneID FROM GeneExpts WHERE Expressed ='Yes';
	
	Q: Return genes involved in metabolism?
            
             SELECT GeneID FROM GeneInfo WHERE Function='Metabolism';

	Q: Genes that  are expressed and involved in metabolism?
             
             SELECT GeneInfo.GeneID FROM GeneInfo, GeneExpts
             WHERE GeneInfo.GeneID =GeneExpts.GeneID AND GeneInfo.Function='Metabolism'
             AND GeneExpts.Expressed='Yes';

	Q: Return locations of all expressed genes? 
            
          SELECT DISTINCT GeneInfo.Location FROM  GeneInfo, GeneExpts 
	WHERE GeneExpts.Expressed='Yes' AND GeneExpts.GeneID=GeneInfo.GeneID;
	DISTINCT  returns to the table and, if there is a duplicate, it will remove the duplicate from the table

	Q: Return Experiments (IDs) where gene expressed and gene acts in the nucleus?

            SELECT GeneExptsID FROM GeneExpts, GeneInfo
            WHERE GeneExpts.Location ='Nucleus'
            AND GeneExpts.Expressed ='Yes'
            AND GeneInfo.GeneID=GeneExpts.GeneID;