My other class this semester (many of you have seen my Java threads, thank God I am done with that POS course) is a database class. The last homework (three SQL statements in particular) is not treating me well. Any SQL experts out there willing to help?
Not to be mean, but If you are considering a computer science/MIS degree. I'd suggest that you'd consider a different degree.
use the Code: tags to format your request neatly. We might be able to read it better if it is presentable. Sometimes the wrapping and normal formatting take away from what you intend to post. I'm saying I might be able to help, is all. :cool: Why are you suggesting that to him, [b]ling ling[/b] :confused:? It DOES sound sort of mean... I guess if I preceed anything by "Not to be mean, but..." can you really get mad at me?
I know Andy personally. This is a good degree for him. Not to derail the thread, but it's funny that a SQL thread popped up because I was just talking about SQL today with my boss. Can you SQL guys suggest some beginner materials for me? I'm going to add SQL to my skillset. I've been considering Head First SQL for some training since reading about it on Slashdot. Thanks.
SQL itself is pretty easy to learn. I don't think you even have to read any book to learn it, just read some sample codes on the net and you will get it pretty quickly. But you have to associate it with what you gonna do with it. I use SQL for Oracle database, and this is the book I'm reading. It's been pretty helpful to me. But it does take a long time to chew through it (1000+ pages).
Here is the script that creates the database... Code: school (sch_code, sch_name, sch_phone, sch_dean) advisor (adv_code,adv_fname,adv_lname, adv_phone, sch_code) major (maj_code, maj_desc, sch_code) maj_adv (maj_code, adv_code ) student (std_code , std_lname, std_fname, std_gend, maj_code, std_dob) grade (std_code, gr_lname, gr_fname, gr_t1, gr_t2, gr_hw, gr_pr) create table school (sch_code varchar2(8) constraint school_sch_code_pk primary key, sch_name varchar2(50), sch_phone varchar2(12), sch_dean varchar2(20)); insert into school values ('BUS', 'School of Business', '281-283-3100', 'Ted Cummings'); insert into school values ('EDU', 'School of Education', '281-283-3600','Dennis Spuck'); insert into school values ('HSH', 'School of Humanities and Human Sciences', '281-283-3333', 'Bruce Palmer'); insert into school values ('SCE', 'School of Science and Computer Engineering', '281-283-3700','Sadegh Davari'); create table advisor (adv_code varchar2(08) constraint advisor_adv_code_pk primary key, adv_lname varchar2(15), adv_fname varchar2(15), adv_phone varchar2(12), sch_code varchar2(8) constraint advisor_sch_code_fk references school (sch_code)); insert into advisor values ('A1', 'Porter', 'Mattie', '281-283-3163', 'BUS'); insert into advisor values ( 'A2', 'Grady', 'Perdue' ,'281-283-3400', 'BUS'); insert into advisor values ('A3', 'Tran', 'Van', '281-283-3203', 'BUS'); insert into advisor values ('A4', 'Saleem', 'Naveed', '281-283-3202', 'BUS'); insert into advisor values ('A5', 'Kwok-Bon', 'Yue', '281-283-3864', 'SCE'); insert into advisor values ('A6', 'Jones', 'Lisa', '281-283-3551', 'EDU'); insert into advisor values ('A7', 'Paolini', 'Shirley', '281-283-3445', 'HSH'); create table major (maj_code varchar2(10) constraint major_maj_code_pk primary key, maj_desc varchar2(30), sch_code varchar2(8) constraint major_sch_code_fk references school(sch_code)); insert into major values ('ACCT', 'Accounting', 'BUS'); insert into major values ('FINC', 'Finance', 'BUS'); insert into major values ('ISAM', 'Management Information Systems', 'BUS'); insert into major values ('CSCI', 'Computer Science', 'SCE'); insert into major values ('HIST', 'History', 'HSH'); insert into major values ('INST', 'Instructional Technology', 'EDU'); create table maj_adv (maj_code varchar2(10) constraint maj_adv_maj_code_fk references major (maj_code), adv_code varchar2(08) constraint maj_adv_adv_code_fk references advisor (adv_code), constraint maj_adv_maj_code_adv_code_cpk primary key (maj_code, adv_code)); insert into maj_adv values ('ACCT', 'A1'); insert into maj_adv values ('ACCT', 'A2'); insert into maj_adv values ('FINC', 'A2'); insert into maj_adv values ('ISAM', 'A3'); insert into maj_adv values ('ISAM', 'A4'); insert into maj_adv values ('CSCI', 'A5'); insert into maj_adv values ('INST', 'A6'); insert into maj_adv values ('HIST', 'A7'); create table student (std_code varchar2(9), std_lname varchar2(15) constraint student_std_lname_nn not null, std_fname varchar2(15) constraint student_std_fname_nn not null, std_gend varchar2(8), maj_code varchar2(10) constraint student_maj_code1_fk references major (maj_code), std_dob date, constraint student_std_code_pk primary key (std_code)); insert into student values ('S1', 'Jordan', 'Michael', 'F', 'FINC', to_date('10-Mar-1962', 'DD-Mon-YYYY')); insert into student values ('S2', 'Barkley', 'Charles', 'M', null, to_date('12-Sep-1964', 'DD-Mon-YYYY')); insert into student values ('S3', 'Johnson','Magic', 'M', 'ACCT', to_date('13-Sep-1960', 'DD-Mon-YYYY')); insert into student values ('S4', 'Williams', 'Serena', 'F','ISAM', to_date('23-Oct-1980', 'DD-Mon-YYYY')); insert into student values ('S5', 'Duncan', 'Tim', 'M', 'ISAM', to_date('07-Aug-1972', 'DD-Mon-YYYY')); insert into student values ('S6', 'Graff', 'Steffi', 'F', 'CSCI', to_date('30-Apr-1962', 'DD-Mon-YYYY')); insert into student values ('S7', 'Navratilova','Martina', 'F', 'ACCT', to_date('18-Dec-1972', 'DD-Mon-YYYY')); REMARK insert into student (std_code, std_lname, std_fname, std_dob) REMARK values ('S2', 'Barkley', 'Charles', to_date('12-Sep-1964','DD-Mon-YYYY')); create table grade (std_code varchar2(9) constraint grade_std_code_pk primary key constraint grade_std_code_fk references student (std_code), gr_lname varchar2(15) constraint grade_gr_lname_nn not null, gr_fname varchar2(15) constraint grade_gr_fname_nn not null, gr_t1 number(5), constraint grade_gr_t1_cc check (gr_t1 between 0 and 100), gr_t2 number(5), gr_hw number(5), gr_pr number(5)); insert into grade values ('S1', 'Jordan', 'Michael', 90, 80, 98, 90); insert into grade values ('S2', 'Barkley', 'Charles', 60, 100, 100, 60); insert into grade values ('S3', 'Johnson', 'Magic', 88, 98, 96, 98); insert into grade values ('S4', 'Williams', 'Serena', 92, 92, 92, 92); insert into grade values ('S5', 'Duncan', 'Tim', 94, 90, 94, 96); insert into grade values ('S6', 'Graff', 'Steffi', 80, 84, 83, 72); insert into grade values ('S7', 'Navratilova', 'Martina', 91, 88, 94, 95);
Here are the three problems I need to solve... Code: Give a curve of 5 points on test 1 to students who is oldest and also has the lowest average grade ( i.e. (gr_t1+gr_t2+gr_hw+gr_pr)/4)). update grade set gr_t1 = gr_t1 + 5 where (select min(gr_t1+gr_t2+gr_hw+gr_pr)/4)= min(gr_t1+gr_t2+gr_hw+gr_pr)/4) from grade) and std_dob = (select min(std_dob) from student); ____________________________________________________________ Display the maj_code of the major and the number of students in these majors whose gr_t1 score is higher than 70. However, display this information only if the major has an average of less than 80 and it has at least two advisors. select major.maj_code, count(student.maj_code) from major, maj_adv, grade where major.maj_code = maj_adv.maj_code and grade.gr_t1 > 70 having count (maj_adv.maj_code) > 1 and ( select min(avg(gr_t1))from grade = 80); ______________________________________________________________ Display the names of students whose average grade is higher than overall average grade but they are not higher than the average on every item. select gr_fname, gr_lname from grade where
So the SQL statements here, you wrote and need corrected? Question 1 Something like: Edit: Aw dude, the question is worded badly. No one is the oldest AND lowest average. I guess the instructor means, give a curve to the oldest and lowest avg person. ie. 2 people. I'm thinking TOO SQL style. haha so let me try again: make a temp table. calculate everyone's averages. So table ends up being a student code matched up with their average. Code: UPDATE grade SET gr_t1 = gr_t1 + 5 WHERE ( std_code = ( (SELECT std_code FROM student WHERE dob = (select min(dob) from student) OR (SELECT std_code FROM temp_avg WHERE avg = (select min(avg) from temp_avg) )) ) See if that works. my SQL is really rusty, but that should be the general idea. not sure if you can just do a "WHERE dob = min(dob)" at the end of those embedded selects. i'll keep lookin at it to see if it's even close to right. I'm typing without trying it out, so...yea
actually, you should also make a temp table that holds the students' averages. Just looking at question 2 you use averages again, so might as well keep teh data handy in a temp table. Edit: Never mind, you should have temp tables on most of these statements/procedures. they are using different averages, sorry about that.
You don't need temp tables or any of that... at least for the first one. Code: update grade set gr_t1 = gr_t1 + 5 where (select min(gr_t1 + gr_t2 + gr_hw + gr_pr)/4 from grade) = (gr_t1+gr_t2+gr_hw+gr_pr)/4 or std_code in (select std_code from student where std_dob = (select min(std_dob) from student)); Broken down: The key is obviously in the "where clause". You are going to update 2 people's grades: the oldest person and the person with the lowest gpa. You'll figure those things out separately and use the "or" keyword. (meaning you will update their grade if they are the oldest OR if they have the lower gpa) To figure out the gpa for each student, just calculate (gr_t1+gr_t2+gr_hw+gr_pr)/4 (which you already did) To figure out the lowest gpa, you have a separate select statement. Part of your problem was that you needed to treat the whole select statement as one side of the equation. Think of it as A = B. 'A' is your select statement and 'B' is the gpq of each student. Figuring out the oldest student is a little harder because you have to bring in another table. The date of birth is stored in the student table. Basically you can do a select on the student table to find the oldest student. By using the "in" keyword in your original where clause you can make sure that the second student you update is in the list containing the oldest student (which should be a list of one) That should be clear as mud. It's hard to explain dealing with sets.
You don't need them, but it makes it cleaner to read. Not only for yourself, but for anyone reading you code in the future. But ya, his/her code looks much better than mine. Pretty obvious I haven't written any in a couple years.
oh, shouldn't he be finding the minimun date of birth? oldest person has the smallest date...right? you got me all doubting myself now!
The one that really helped me when I was a beginner was SQL in 10 minutes: http://www.amazon.com/Sams-Teach-Yo...bs_sr_1?ie=UTF8&s=books&qid=1196298857&sr=8-1 Its good for learning basic queries that you can learn to expand on. It is VERY basic, so its good for beginners.
For the second part of the second question, is it asking for only where the average of test 1 is less than 80, or is it asking for where all the tests in the major are less than 80? If it's asking where the average in the major of test 1 is less than 80, there won't be any returns from what I'm seeing... I used this query: select student.maj_code, count(student.maj_code) from student, grade, school, advisor, major, maj_adv where school.sch_code = major.sch_code and advisor.sch_code = school.sch_code and student.maj_code = major.maj_code and grade.std_code = student.std_code and advisor.adv_code = maj_adv.adv_code and grade.gr_t1 > 70 and major.maj_code in (select student.maj_code from student, grade where grade.std_code = student.std_code group by maj_code having avg(gr_t1) < 80) and maj_adv.maj_code in (select maj_code from maj_adv group by maj_code having count(maj_code) > 1) group by student.maj_code; The third question I can interpret three or four different ways... what exactly is it asking for? Average grade on what? Overall average? What does it mean by the average on every item? Is your prof new or a grad student?
The best way to learn sql is to practice. practice. practice. start with the basics. since you're dealing with math problems, go find the available standard math functions like, max(), avg(), power(), rand(), sign()..., .... and play with it. that is the best way to learn.
Where can I find a database to practice on (it's something I will need to know as my job is evolving)?
