Need Help in SQL !

[adil.ghori]

Write a select statement that will display student id, student last name and the
number of courses a student has been enrolled. You will need to join two table, use a
COUNT aggregate and a GROUP BY clause in your SELECT statement.

this is something i found on internet, im not a student and not a uni student who is willing to do his assignment, im just learning it myself... alli did is
`

SELECT 
    s_id,
    s_last,
Count (max_enrl)
From 
  UC_Student, UC_Enrollment
Where
UC_Student = UC_Enrollemnt
Group by s_id, s_last

`

but its not giving me the correct output, what should I do ....

Regards
adil

[pritaeas]

What output do you get? What output do you want?

[adil.ghori]

I need to display student id and student last name, plus max enrollment

by using group by and count clause

[pritaeas]

That was clear from your question. I suggested you share the output you get (sample data/records), and then show what your expected output would be.

[adil.ghori]

this is the whole question;

need to find out these things.....

1. Display student id, student first name, student last name, the students’s faculty
   advisor id, advisor last name, advisor location and phone number. You will join
   uc_student and uc_faculty
2. Write a SELECT statement that will return the faculty last name, faculty first name
   and the room for faculty whose offices are located in the ‘BUS’ building.
3. Display data from the uc_course_section table by performing a Three Table Join with
   the uc_course_section, uc_location and uc_course. The SELECT statement will display
   the course section id (c_sec_id), the course name, number of credits, maximum
   number of enrollments in a course section, building and room where the class meets
   and the capacity of the room … and only for courses for term_id 5.
4. Write a select statement that will display student id, student last name and the
   number of courses a student has been enrolled. You will need to join two table, use a
   COUNT aggregate and a GROUP BY clause in your SELECT statement.
5. Create a SELECT statement that would provide data for a faculty directory. Display
   should include faculty id, first name, middle initial, last name, office location (building
   and room), rank and phone number. The display should be sorted by last name
   ascending. Concatenate first name, middle initial and last name to look like ‘ James E.
   Smith’. Concatenate building and room to look like ‘ENG102’. Override the column
   labels with meaningful descriptions.

do u have any idea where to start and where to end... and how to get output....

[pritaeas]

but its not giving me the correct output

What did you get as output? Reposting the question is not what I asked.

[adil.ghori]

Output will be like ;

I was getting errors...

[adil.ghori]

SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;

[adil.ghori]

we have the following tables,

1 - UC_Course ---------- contains -- [(PK) course_id,call_id,course_name,credits]

2 - UC_Course_Sec------- contains -- [(PK) c_sec_id,(FK)course_id,(FK)term_id,sec_num,(FK)f_id,
c_sec_day,c_sec_time_start,c_sec_time_end,(FK)loc_id,max_enrl]

3 - UC_enrollment------- contains -- [(PK)(FK)s_id, (PK)(FK)c_sec_id,grade]

4 - UC_faculty---------- contains -- [(PK) f_id,f_last,f_first,f_mi,(FK)loc_id,f_phone,f_rank,f_pin]

5 - UC_location--------- contains -- [(PK) loc_id,bldg_code,room,capacity]

6 - UC_student---------- contains -- [(PK) s_id,s_last,s_first,s_mi,s_address,s_city,s_state,s_zip,
s_phone,s_class,s_dob,s_pin,(FK)f_id]

7 - UC_term------------- contains -- [(PK) term_id,term_desc,status]

here is all tables and colums of the database; would you like to solve it now according to the question, Please.....

Regards

[pritaeas]

I was getting errors...

What errors?

would you like to solve it now according to the question

I will guide you, but not solve it for you.

[Stuugie]

I agree wit pritaeas, you need guidance, not someone to solve this for you. A dm to me does not inspire me to solve this for you. Plus, you have provided no consistency with regards to what you want. You have shown both business statements and student type statements.

Plus, pritaeas has asked you on more than one occasion to state your errors but you still haven't. Please, help us to help you.

[adil.ghori]

Thank You for nothing @ Pritaeas & Stuugie...

Have sorted it myself...

[adil.ghori]

Q-1



select



s_id, s_first, s_last,



uc_student.f_id,



f_last,



loc_id,



f_phone



from



uc_student, uc_faculty



where



uc_student.f_id=uc_faculty.f_id



Result:



s_id      s_first            s_last           f_id           f_last           



loc_id      f_phone



-----  -------------    -----------------   ---------    ---------------   --



---------  -------------



1         Sarah               Miller            1             Cox               



9        7155551234



2         Brian               Umato             1             Cox               



9        7155551234



3         Daniel              Black             1             Cox               



9        7155551234



4         Amanda              Mobley            2             Blanchard         



10       7155559087



5         Ruben               Sanchez           4             Sheng             



11       7155556409



6         Michael             Connoly           3             Williams          



12       7155555412



(6 row(s) affected)



Q-2



Select



 f_last, f_first, room



From



 uc_faculty, uc_location



Where



 uc_location.loc_id=uc_faculty.loc_id and bldg_code='BUS'



Result:



f_last                         f_first                        room



------------------------------ ------------------------------ ------



Cox                            Kim                            424



Blanchard                      John                           402



Sheng                          Laura                          433



(3 row(s) affected)



Q-3.



Select



 c_sec_id, course_name, credits, max_enrl, bldg_code, room, capacity



from



 uc_course_section, uc_location, uc_course



where  



 uc_course_section.term_id=uc_location.loc_id and term_id='5'



Result:



c_sec_id                                course_name               credits                                 



max_enrl                                bldg_code  room   capacity



--------------------------------------- ------------------------- -------------------------------



-------- --------------------------------------- ---------- ------ ------------------------------



---------



5                                       Intro. to Info. Systems   3                                       



35                                      BUS        105    42



6                                       Intro. to Info. Systems   3                                       



30                                      BUS        105    42



7                                       Intro. to Info. Systems   3                                       



30                                      BUS        105    42



8                                       Intro. to Info. Systems   3                                       



35                                      BUS        105    42



9                                       Intro. to Info. Systems   3                                       



35                                      BUS        105    42



10                                      Intro. to Info. Systems   3                                       



35                                      BUS        105    42



5                                       Systems Analysis          3                                       



35                                      BUS        105    42



6                                       Systems Analysis          3                                       



30                                      BUS        105    42



7                                       Systems Analysis          3                                       



30                                      BUS        105    42



8                                       Systems Analysis          3                                       



35                                      BUS        105    42



9                                       Systems Analysis          3                                       



35                                      BUS        105    42



10                                      Systems Analysis          3                                       



35                                      BUS        105    42



5                                       Database Management       3                                       



35                                      BUS        105    42



6                                       Database Management       3                                       



30                                      BUS        105    42



7                                       Database Management       3                                       



30                                      BUS        105    42



8                                       Database Management       3                                       



35                                      BUS        105    42



9                                       Database Management       3                                       



35                                      BUS        105    42



10                                      Database Management       3                                       



35                                      BUS        105    42



5                                       Programming in C++        3                                       



35                                      BUS        105    42



6                                       Programming in C++        3                                       



30                                      BUS        105    42



7                                       Programming in C++        3                                       



30                                      BUS        105    42



8                                       Programming in C++        3                                       



35                                      BUS        105    42



9                                       Programming in C++        3                                       



35                                      BUS        105    42



10                                      Programming in C++        3                                       



35                                      BUS        105    42



5                                       Web-Based Systems         3                                       



35                                      BUS        105    42



6                                       Web-Based Systems         3                                       



30                                      BUS        105    42



7                                       Web-Based Systems         3                                       



30                                      BUS        105    42



8                                       Web-Based Systems         3                                       



35                                      BUS        105    42



9                                       Web-Based Systems         3                                       



35                                      BUS        105    42



10                                      Web-Based Systems         3                                       



35                                      BUS        105    42



(30 row(s) affected)

[pritaeas]

Have sorted it myself

That's supposed to be the point. Giving you an answer won't help you learn. In my defense, you didn't even bother to answer my questions, how am I supposed to help then?