Home » SQL & PL/SQL » SQL & PL/SQL » query help (10g , xp)
query help [message #398315] Thu, 16 April 2009 00:36 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have following objects

Tables:		evaluation
		

Views:		gpa_view 





 1* select * from evaluation where semester_code=3 and semester_year=2008  order by course_seq_no
SQL> /

COURSE_SEQ_NO                  GR NO_OF_STUDENTS         GP SEMESTER_CODE SEMESTER_YEAR
------------------------------ -- -------------- ---------- ------------- -------------
1                              A              11         44             3          2008
1                              A-             16      58.72             3          2008
1                              B              46        138             3          2008
1                              B+             10       33.3             3          2008
1                              C-             24      40.08             3          2008
1                              D+             31      41.23             3          2008
1                              W               1          0             3          2008
1                              F              33          0             3          2008
1                              D              32         32             3          2008
1                              C+             22      51.26             3          2008
1                              C              35         70             3          2008
1                              B-             18      48.06             3          2008
10                             A               2          8             3          2008
10                             A-              7      25.69             3          2008
10                             B              31         93             3          2008
10                             B+              4      13.32             3          2008
10                             C-              7      11.69             3          2008

18 rows selected.


SQL> select * from gpa_view where semester_code=3 and semester_year=2008
  2  order by course_seq_no;

COURSE_SEQ_NO                         GPA SEMESTER_CODE SEMESTER_YEAR
------------------------------ ---------- ------------- -------------
1                                       2             3          2008
10                                   2.39             3          2008
11                                   2.94             3          2008
123                                  2.38             3          2008
126                                  2.31             3          2008
132                                  3.17             3          2008
135                                  2.67             3          2008
136                                  3.73             3          2008
138                                  2.83             3          2008
140                                  2.97             3          2008
142                                  3.79             3          2008
143                                  3.12             3          2008
148                                  2.98             3          2008
150                                  2.88             3          2008
151                                  2.84             3          2008
155                                  2.88             3          2008
159                                  2.64             3          2008
163                                  2.71             3          2008
164                                  2.95             3          2008
169                                  2.65             3          2008
17                                    2.1             3          2008
170                                  2.71             3          2008

23 rows selected.


SQL> 



I am trying to produce a matrix query showing 

course_seq_no row heading,		--from evaluation
grade(GR) as column heading,		--from evaluation
no_of_students as second last column		-- from evaluation
and 
gpa as last column 				-- from gpa_view





any help would be highly appreciated

rz
Re: query help [message #398316 is a reply to message #398315] Thu, 16 April 2009 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is your problem in trying to achieve this?

Post a Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: query help [message #398368 is a reply to message #398315] Thu, 16 April 2009 02:10 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have solved my problem.
Thank you All

Rz

as

select substr(e.course_Seq_no,1,4) as cseq,
f.course_code,
substr(f.course_name,1,45) as cn,
f.credit_hours,
 sum(decode(grade,'A ',no_of_students)) A,
 sum(decode(grade,'A-',no_of_students)) A_Minus,
 sum(decode(grade,'B+',no_of_students)) BPlus,
 sum(decode(grade,'B',no_of_students)) B,
 sum(decode(grade,'B-',no_of_students)) BMinus,
 g.gpa
from evaluation e,gpa_view g,fcp f
where e.semester_code=1 and e.semester_year=2008
and  g.semester_code=1 and g.semester_year=2008
and e.course_seq_no=g.course_seq_no
and g.course_seq_no=f.course_seq_no
and e.semester_code=g.semester_code
and e.semester_year=g.semester_year
group by e.course_seq_no,g.gpa,f.course_code,f.course_name,f.credit_hours


The Result is below, which I required
SQL> /

CSEQ COURSE_C CN                                            CREDIT_HOURS          A    A_MINUS      BPLUS          B     BMINUS        GPA                      
---- -------- --------------------------------------------- ------------ ---------- ---------- ---------- ---------- ---------- ----------                      
5    CS102L   Intensive Programming Lab                                1         34         21         72                    59       2.66                      
6    CS211    Introduction to Operating Systems                        3          4          8         14                    17       2.45                      
8    CS211L   Introduction to Operating Systems Lab                    1          9         16         22                    13       2.95                      
13   CS223    Object Oriented Programming                              3          6          6          6                     8       2.33                      
15   CS223L   Object Oriented Programming Lab                          1          9          6         17                    17       2.94                      
20   CS313    Computer Communication and Networking                    3          2          9          3                    14        2.7                      
22   CS313L   Computer Communication and Networking Lab                1          7          8         16                    10       3.14                      
24   CS322    Theory of Automata                                       3          2          4          7                    13       2.41                      
34   CS351    Introduction to Artificial Intelligence                  3          2          3          3                    15       2.45                      
36   CS351L   Artificial Intelligence Lab                              1          5          8          8                     1       2.68                      
45   CS433    Computer Graphics                                        3          2          6          3                     5       3.11                      
54   CS482    Senior Design Project (Part II)                          3         13          4          7                     1       3.61                      
63   EE212    Circuit Analysis II                                      3          6         10         11                    14        2.6                      
68   EE222    Computer Architecture                                    3         19         22         30                    23       2.64                      
70   EE222L   Computer Architecture Lab                                1         29         25         25                    17       3.08                      
71   EE231    Electronics I                                            3          9         13         20                    27       2.63                      
73   EE231L   Electronics I Lab                                        1         44         22         32                    28       3.37                      
80   EE332    Electronics II                                           3          4          6          9                    11        2.5                      
82   EE332L   Electronics II Lab                                       1          9         21         31                     2       3.38                      
84   EE341    Control Systems                                          3          7          7         14                    13        2.7                      
86   EE341L   Control Systems Lab                                      1         13         22         16                     6       3.44                      
91   EE371    Electromagnetics Fields and Waves                        3          4          6          9                    13       2.57                      
94   EE434    Power Electronics                                        3          8          6          9                    10       2.84                      
106  EE474    Microwave Engineering                                    3          1          1          2                     2        3.1                      
109  EE482    Senior Design Project (Part II)                          3         42          9          4                     3       3.82                      
125  ES333    Fluid Mechanics                                          3          2          1          3                     4       2.41                      
126  ES341    Numerical Analysis                                       3          3          4         12                     8       2.22                      
127  ES342    Modeling Processes                                       3          2                     1                     2        2.7                      
129  ES371    Engineering Electromagnetics                             3          1          1          2                     4       2.16                      

30 rows selected.



Previous Topic: Multiple Column Subquery restricting rows
Next Topic: Select the data with highest update no
Goto Forum:
  


Current Time: Sat Dec 10 20:38:34 CST 2016

Total time taken to generate the page: 0.07757 seconds