March 16, 2010 at 4:55 am
Hi all, thanks for taking the time to read this. I've got a college course on the go called 'Introduction to SQL' and I'm having a bit of trouble with three of the questions in my latest tutorial. Can you help?
Using these tables:
STUDENT table:
A list of student details including student’s ID, forename, surname, sex and date of birth as follows:
studentID, forename, surname, sex, dateofbirth
SUBJECT table:
A list of subject details including the subject code, the subject title and the ID number of the staff member who teaches that subject as follows:
subjectID, subjecttitle, staffID
RESULT table
A list of exam results for each of the students on each subject in the course, including subject ID, student ID and the student’s mark for the subject exam. Note that you will be required to perform calculations on the ‘Mark’ field in the next assessment, so choose an appropriate field type.
subjectID, studentID, mark
STAFF table
A list of staff members who teach the subjects, including the staff member’s ID, forename, surname and the post held by the member in the college.
staffID, forename, surname, post
I am having a lot of trouble with the following questions, and would be very grateful for a few pointers, if anyone would care to help. Questions are:
A. Along with each subject id, list the average exam mark, the maximum exam
mark and the minimum exam mark for each subject. Use suitable headings
for the columns.
B. Retrieve the subject ID, the student’s name and exam mark of the student
who obtained the maximum result in subject AWD169. Use the appropriate
column headings and concatenate the student’s first and last names.
C. Teaching staff are concerned about the wide spread of results obtained in unit
IIA887 - Intelligent Internet Applications, owing to technical problems. They
have asked for a list of students whose results are either below the pass mark
of 50, or are above the distinction mark of 70. Order the results in descending
order of marks. Use appropriate column headings and concatenate each
student’s first and last name.
Thanks again for reading. Colin
March 16, 2010 at 5:07 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
However....
We don't answer homework questions here. We'll give you hints, no more than that. Answering your homework questions for you means you learn nothing. Have you spoken to your lecturer/tutor? If not, I suggest doing that first. That's what they are there for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 5:12 am
Thanks for the heads up. All my files are on another PC, so I'll post them later on.
Thanks again,
Colin
March 16, 2010 at 5:18 am
This was the code used to create everything. Thanks for replying, Colin
DROP TABLE if exists STAFF;
DROP TABLE if exists RESULT;
DROP TABLE if exists SUBJECT;
DROP TABLE if exists STUDENT;
CREATE TABLE student
(
studentid NUMERIC (5),
forename CHAR (20),
surname CHAR (30),
sex CHAR (6),
dateofbirth DATE,
PRIMARY KEY (studentid)
)
type=innoDB;
CREATE TABLE subject
(
subjectid VARCHAR (6),
subjecttitle VARCHAR (40),
staffid NUMERIC (5),
PRIMARY KEY (subjectid),
FOREIGN KEY (staffid) REFERENCES staff(staffid)
);
CREATE TABLE staff
(
staffid NUMERIC (5),
forename CHAR (20),
surname CHAR (30),
post CHAR (20),
PRIMARY KEY (staffid)
);
CREATE TABLE result
(
subjectid VARCHAR (6),
studentid NUMERIC (5),
mark NUMERIC (2),
FOREIGN KEY (subjectid) REFERENCES subject(subjectid)
FOREIGN KEY (studentid) REFERENCES student(studentid)
);
--Code for entering data into tables-- Assignment 2
INSERT INTO student VALUES
('33457','Alan','Burns','Male','1980-05-31'),
('33672','Mark','Harrison','Male','1985-03-26'),
('46321','Morven','Summers','Female','1986-07-04'),
('21698','Kenneth','Sloan','Male','1974-01-23'),
('25814','Scott','Burns','Male','1985-10-04'),
('34852','Colin','Gardiner','Male','1984-11-28'),
('30321','Sarah','Douglas','Female','1979-08-13'),
('30731','Stephen','Billingham','Male','1982-04-19'),
('46372','Rizwana','Hussain','Female','1986-11-08'),
('31279','Stephen','Kelly','Male','1979-06-05');
INSERT INTO subject VALUES
('TMP243','Team Project','85321'),
('AWD169','Advanced Web Databases','85321'),
('CNA334','Commercial Network Applications','55273'),
('ECO776','E-Commerce','47610'),
('IIA887','Intelligent Internet Applications','55981');
INSERT INTO staff VALUES
('85321','Gillian','Douglas','Lecturer'),
('55273','Derek','Summers','Lecturer'),
('47610','Val','Innes','Programme Leader'),
('55981','Albert','Lumsden','Lecturer'),
('55819','Geoffrey','McKenna','Lecturer');
INSERT INTO result VALUES
('TMP243','33447','78'),
('TMP243','33672','57'),
('TMP243','46321','39'),
('TMP243','21698','75'),
('TMP243','46372','66'),
('TMP243','31279','51'),
('IIA887','33447','48'),
('IIA887','46321','76'),
('IIA887','21698','87'),
('IIA887','25814','23'),
('IIA887','34852','45'),
('CNA334','30321','94'),
('CNA334','30731','65'),
('AWD169','33447','45'),
('AWD169','33672','67'),
('AWD169','46321','27'),
('AWD169','21698','84'),
('AWD169','25814','67'),
('AWD169','34852','45'),
('AWD169','30321','92'),
('AWD169','30731','65'),
('AWD169','46372','88'),
('AWD169','31279','39'),
('ECO776','34852','67'),
('ECO776','30321','88'),
('ECO776','30731','62'),
('ECO776','46372','70'),
('ECO776','31279','34');
commit;
--Code for adding column into staff table-- Assignment 3
ALTER TABLE staff
(
ADD salary NUMERIC (10,2)
);
UPDATE staff
SET salary='28528.00'
WHERE staffid='85321';
UPDATE staff
SET salary='27887.00'
WHERE staffid='55273';
UPDATE staff
SET salary='34000.00'
WHERE staffid='47610';
UPDATE staff
SET salary='28223.00'
WHERE staffid='55981';
commit;
March 16, 2010 at 5:31 am
Ok, so what is it that you want? As I indicated earlier, I'm not going to give you the answers. I'm not the one doing the course, I don't need to learn T-SQL. I'll give you hints at best.
Have you spoken with your teacher or tutor and asked them for help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 5:38 am
Hehe, that's not what I was hoping! I'm looking for hints, nothing more! Any you could give would be most appreciated.
I've got this far trying A):
SELECT result.subjectid, max( result.mark ) , CONCAT( student.forename, ' ', student.surname ) AS 'Student Name'
FROM result, student
WHERE result.subjectid='AWD169'
LIMIT 0 , 1
and I know I'm close. It's doing my head in to be honest, that way when you look at something too much and it all gets confusing.
I get the first student in the list returned instead of the student with the highest mark, so I know I need to connect the two (max(result.mark) and 'Student Name') somehow.
EDIT: I missed this part of your post. I do my course through Distance Learning and getting a hold of a tutor is tricky at best. I've emailed her for help but she's not being very forthcoming so far. (Over a week).
March 16, 2010 at 5:51 am
Hang on a minute.....
LIMIT and CONCAT are mySQL, not SQL Server. Looking at your table creation scripts, they're MySQL as well.
MySQL and MS SQL Server are two very different products, they have different variants of the SQL language. Any code I give you may well not even run.
This is a Microsoft SQL Server forum, not a MySQL forum.
Hints:
For A, you need a subquery (if the version of MySQL you're using has subqueries). A subquery to get the studentID, then join that back to the table to get the rest of the data. The SQL Server version would be something like this.
SELECT
FROM FirstTable INNER JOIN
(SELECT SomeID, MAX(SomeValue) AS MaxValue FROM SecondTable GROUP BY SomeID) Sub ON FirstTable.SomeID = Sub.SomeID
No TOP (LIMIT) necessary.
I suggest that you bug your tutor some more, it is her job to help you, or look for a MySQL forum on teh net. dbforums has a MySQL section, can't say how good it is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 7:01 am
I suggest you read about how to do grouping and sorting i SQL...
March 16, 2010 at 7:10 am
Well I feel a bit thick! I might take up SQL Server 2005 just to make use of my login...thanks for your replies nonetheless. I do, however, think I have it.
SELECT subjectid, CONCAT(forename,' ',surname) as 'Student Name', mark as 'Highest Result'
FROM student, result
WHERE subjectid='AWD169'
ANDstudent.studentid=result.studentid
ORDER BY mark desc
LIMIT 1;
I know this is the wrong forum and all that, but I posted it anyway as I hate to leave things unfinished...
Thanks again for all your help.
I assume I could be learning SQL Server through my course eventually, so I'll probably talk to you again.
Colin
March 16, 2010 at 7:45 am
That'll work, as long as they're asking for one subject only. If the question was for the student with the highest park per subject, then just doing a TOP (LIMIT) wouldn't work, and that's when you'd need a subquery or similar construct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 7:53 am
Yip, it was only for one subject.
Cheers,
Colin
March 18, 2010 at 6:10 am
Hi,
I thoroughly recommend the SQL Cookbook from O'Reilly. It's not too bad for beginners of SQL and it gives each 'recipe' in 5 flavours of SQL (Oracle, DB2, Transact SQL, MySQL and PostgresSQL). It starts off with the basics and gets progressively more difficult. I find it very useful for DB2 (which I occasionally have to use).
I hope that that helps
gosh
March 18, 2010 at 6:12 am
Thanks for that Gosh, I'll have a look on Amazon for it.
Cheers,
Colin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply