OraFAQ Forum: SQL & PL/SQL » Creating views - percentages and multiple tables. Newbie

Home » SQL & PL/SQL » SQL & PL/SQL » Creating views - percentages and multiple tables. Newbie

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating views - percentages and multiple tables. Newbie [message #598155]

Thu, 10 October 2013 13:30

bmartin1uk
Messages: 2
Registered: October 2013

Junior Member

Hi folks

I'm very new to Oracle and SQL, and i'm having trouble with a little assignment.

"Create a view named vuPassFailRate that will show the pass rate and fail rates of each test."

I have a table named Test_ID containing the following columns:

TEST_ID
TEST_NAME
PASSING_GRADE

And another table named Test_History containing the following columns:

TEST_ID
STUDENT_ID
SCORE

I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100.
I just cant work out the nested select statements, and work out the formula using two columns in two tables.

I have been staring at this problem for so long now i cant see the wood for the trees.

I appreciate any help people can offer.

Thanks

Ben

Report message to a moderator

Re: Creating views - percentages and multiple tables. Newbie [message #598156 is a reply to message #598155]

Thu, 10 October 2013 13:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Report message to a moderator

Re: Creating views - percentages and multiple tables. Newbie [message #598157 is a reply to message #598156]

Thu, 10 October 2013 13:43

bmartin1uk
Messages: 2
Registered: October 2013

Junior Member

Sorry BlackSwan

I'm even more lost now. I'll try a more beginners forum.

Thanks for your help though.

Ben

Report message to a moderator

Re: Creating views - percentages and multiple tables. Newbie [message #598158 is a reply to message #598155]

Thu, 10 October 2013 14:05

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select i.test_id,
       100*count(case when h.score >= i.passing_grade then 1 end)/count(*) pass_rate,
       100*count(case when h.score < i.passing_grade then 1 end)/count(*) fail_rate
from test_id i, test_history h
where h.test_id = i.test_id
group by i.test_id
/

Report message to a moderator

Previous Topic:	Bulk insert dummy data into table
Next Topic:	ORA:01427: single row sub query returns more than one record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:01:56 CDT 2024