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