Sample Queries

SQL> /* SAMPLE QUERIES */

/* ************************************************************************* */

SQL> /* 1. MEMBER INFORMATION-----Write a query that shows the name of the members who are both a scientist and a moderator  */

SQL> SELECT FIRST_NAME||' '||LAST_NAME MEMBER_NAME
  2  FROM   MEMBER,SCIENTIST
  3  WHERE  MEMBER.MEMBER_ID=SCIENTIST.MEMBER_ID
  4  INTERSECT
  5  SELECT FIRST_NAME||' '||LAST_NAME MEMBER_NAME
  6  FROM   MEMBER,MODERATOR
  7  WHERE  MEMBER.MEMBER_ID=MODERATOR.MEMBER_ID;

MEMBER_NAME                                                                                         
-------------------------------                                                                     
David Auerbach                                                                                      
Eric Maass                                                                                          
Harry Adam                                                                                          
Kevin Reed              

/* ************************************************************************* */
                                                                         
SQL> /* 2 WEB RESOURCES-----Show the information of web resouces, including URL and its description,  that relate to astronomy*/
SQL> SELECT URL,LINK.DESCRIPTION DESCRIPTION
  2  FROM   LINK,AREA
  3  WHERE  LINK.AREA_NO=AREA.AREA_NO
  4  AND    AREA.NAME='Astronomy';
URL                                                                                                 
----------------------------------------------------------------------------------------------------
DESCRIPTION                                                                                         
----------------------------------------------------------------------------------------------------
http://www.nasa.gov/                                                                                
The NASA site is filled with pictures and information concerning shuttle missions, the Hubble space 
telescope and other ongoing projects.        

/* ************************************************************************* */                                                       
                                                                                                   
SQL> /* 3. SEARCH QUESTIONS-----Search for questions that fit the following conditions: gradlevel- 10-12, area-Botany, directory-archives. Please show the subject of the question and tell us who asked the question. */
SQL> SELECT DISTINCT FIRST_NAME||' '||LAST_NAME USER_NAME,SUBJECT
  2  FROM   QUESTION,AREA,GLEVEL,DIRECTORY,USERS,PASS,ANSWER
  3  WHERE  USERS.USER_ID=QUESTION.USER_ID
  4   AND   QUESTION.GLEVEL_NO=GLEVEL.GLEVEL_NO
  5   AND   QUESTION.AREA_NO=AREA.AREA_NO
  6   AND   QUESTION.DIR_NO=DIRECTORY.DIR_NO
  7   AND   GLEVEL.NAME='10-12'
  8   AND   AREA.NAME='Botany'
  9   AND   DIR_NAME='archives';
USER_NAME                                                                                           
-------------------------------                                                                     
SUBJECT                                                                                             
----------------------------------------------------------------------------------------------------
Eve Hsu                                                                                             
How can I change the colour of the tulips?                     

/* ************************************************************************* */
                                                                                                    
SQL> /* 4. EXPERIMENT---- Create a view "Caution" to srore which experiments need adults present */
SQL> drop view	Caution;
View dropped.
SQL> CREATE VIEW Caution AS
  2  SELECT EXP_NO,OVERVIEW
  3  FROM   EXPERIMENT
  4  WHERE  ADULT_PRESENCE='Y';

View created.

SQL> SELECT * FROM Caution;
EXP_                                                                                                
----                                                                                                
OVERVIEW                                                                                            
----------------------------------------------------------------------------------------------------
1                                                                                                   
Take a blood pressure.. correlate the blood pressure to the hearts pumping of blood.       



/* ************************************************************************* */         
                                                                                                    
SQL> /* 5. FIND A GOOD SCIENTIST TO ANSWER A QUESTION-----Show the scientists whose answers have all accepted(none is rejected) and their answers are all ranked higher than average. */

SQL> SELECT SCIENTIST.MEMBER_ID ID,FIRST_NAME||' '||LAST_NAME		    SCIENTIST_NAME,AVG(ACCEPT.RANK_NO) AVERAGE_RANK
  2  FROM   MEMBER,SCIENTIST,PASS,ANSWER,ACCEPT,RANK
  3  WHERE  MEMBER.MEMBER_ID=SCIENTIST.MEMBER_ID
  4  AND    SCIENTIST.MEMBER_ID=PASS.SCI_ID
  5  AND    PASS.PASS_NO=ANSWER.PASS_NO
  6  AND    ANSWER.PASS_NO=ACCEPT.PASS_NO
  7  AND    ACCEPT.RANK_NO>3
  8  GROUP BY SCIENTIST.MEMBER_ID,FIRST_NAME||' '||LAST_NAME
  9    MINUS
 10  SELECT SCIENTIST.MEMBER_ID ID,FIRST_NAME||' '||LAST_NAME	SCIENTIST_NAME,AVG(ACCEPT.RANK_NO) AVERAGE_RANK
 11  FROM   MEMBER,SCIENTIST,PASS,ANSWER,REJECT,RANK,ACCEPT
 12  WHERE  MEMBER.MEMBER_ID=SCIENTIST.MEMBER_ID
 13  AND    SCIENTIST.MEMBER_ID=PASS.SCI_ID
 14  AND    PASS.PASS_NO=ANSWER.PASS_NO
 15  AND    ANSWER.PASS_NO=REJECT.PASS_NO
 16  GROUP BY SCIENTIST.MEMBER_ID,FIRST_NAME||' '||LAST_NAME;

ID   SCIENTIST_NAME                  AVERAGE_RANK                                                   
---- ------------------------------- ------------                                                   
120  Kuoming Lee                                5                                                   
89   David Auerbach                             4         


/* ************************************************************************* */                                         

SQL> /* 6. FIND OUT WHICH MODERATOR IS INACTIVE-----Show the name of moderators who have never processed any submitted question. */
SQL> (SELECT MODERATOR.MEMBER_ID ID, FIRST_NAME||' '||LAST_NAME MODERATOR_NAME
  2  FROM   MEMBER,MODERATOR
  3  WHERE  MEMBER.MEMBER_ID=MODERATOR.MEMBER_ID
  4  	MINUS
  5  SELECT MODERATOR.MEMBER_ID ID, FIRST_NAME||' '||LAST_NAME MODERATOR_NAME
  6  FROM   MEMBER,MODERATOR,PASS
  7  WHERE  MEMBER.MEMBER_ID=MODERATOR.MEMBER_ID
  8    AND  MODERATOR.MEMBER_ID=PASS.MOD_ID)
  9  	INTERSECT
 10  (SELECT MODERATOR.MEMBER_ID ID, FIRST_NAME||' '||LAST_NAME MODERATOR_NAME
 11  FROM   MEMBER,MODERATOR
 12  WHERE  MEMBER.MEMBER_ID=MODERATOR.MEMBER_ID
 13  	MINUS
 14  SELECT MODERATOR.MEMBER_ID ID, FIRST_NAME||' '||LAST_NAME MODERATOR_NAME
 15  FROM   MEMBER,MODERATOR,QUESEDIT
 16  WHERE  MEMBER.MEMBER_ID=MODERATOR.MEMBER_ID
 17    AND  MODERATOR.MEMBER_ID=QUESEDIT.MEMBER_ID);

ID   MODERATOR_NAME                                                                                 
---- -------------------------------                                                                
123  John Tseng                                                                                     
14   Kevin Reed