Home » Other » Training & Certification » SQL Help
SQL Help [message #278416] Sun, 04 November 2007 00:28 Go to next message
orangejuice
Messages: 3
Registered: November 2007
Junior Member
Hello everyone, i've been trying to figure this out for a while but to no avail and am running out of time so plz any help is appreciated.

My tables are: School, Area, Student, Majors, Minors

Heres my task:
1)
Select the id and name of all students who are minoring in any area belonging to the 'Basket Weaving and Juggling' school; Make sure each student appears only once, even if minoring in more than one area within that school.

2)
The name of each area with the number of students who are minoring on it, plus the age of the oldest student(s) majoring on it, and the average age of all students majoring on it. Make sure ALL areas appear, even those with no students majoring on it.

For Task 1, this is what i've been able to come up with
select Distinct S.name, S.id
from Student S JOIN Minor M ON (S.id = M.student) Join Area A ON (A.id = M.Area) JOIN School H ON (A.school = H.id)
where H.name = 'Basket Weaving and Juggling';


but i get the no row selected response, which is odd b/c i was expecting it to return some columns......here are the data in the tables

INSERT INTO School (Id, Name) VALUES (1, 'Computing and Software Engineering');
INSERT INTO School (Id, Name) VALUES (2, 'Basket Weaving and Juggling');

INSERT INTO Area (Id,Name,School) VALUES
	(1,'Computer Science',1);
INSERT INTO Area (Id,Name,School) VALUES
	(2,'Software Engineering',1);
INSERT INTO Area (Id,Name,School) VALUES
	(3,'Information Technology',1);

INSERT INTO Area (Id,Name,School) VALUES
	(4,'Basket Weaving',2);
INSERT INTO Area (Id,Name,School) VALUES
	(5,'Juggling',2);

INSERT INTO Minors (Student,Area) VALUES (1,5);
INSERT INTO Minors (Student,Area) VALUES (2,1);
INSERT INTO Minors (Student,Area) VALUES (2,4);
INSERT INTO Minors (Student,Area) VALUES (3,5);
INSERT INTO Minors (Student,Area) VALUES (4,5);


For task 2:
I don't know where to start with this one

Thnx to any1 who helps

[Updated on: Sun, 04 November 2007 01:08]

Report message to a moderator

Re: SQL Help [message #278417 is a reply to message #278416] Sun, 04 November 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't have:
- the description of the tables (or better their create table statement)
- their primary key
- the relation between the tables (foreign key)
- the student data
- the majors data
- the Oracle version

but number=COUNT, oldest=MAX or MIN, average=AVG

Regards
Michel
Re: SQL Help [message #278421 is a reply to message #278416] Sun, 04 November 2007 01:56 Go to previous messageGo to next message
orangejuice
Messages: 3
Registered: November 2007
Junior Member
Oracle 10i
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(1, 'Light Yamagashi',30, 'M');
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(2, 'Lina Colli',29, 'F');
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(3, 'John Smith',18, 'M');
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(4, 'Jane Smith',19, 'F');
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(5, 'Ruben Rada',20, 'M');
INSERT INTO Student(Id, Name, Age, Gender) VALUES
	(6, 'Manuel Morales',21, 'M');


INSERT INTO Majors (Student,Area) VALUES (1,4);
INSERT INTO Majors (Student,Area) VALUES (2,1);
INSERT INTO Majors (Student,Area) VALUES (3,2);
INSERT INTO Majors (Student,Area) VALUES (4,3);
INSERT INTO Majors (Student,Area) VALUES (4,4);


CREATE TABLE School (
	Id 	INTEGER PRIMARY KEY,
	Name 	VARCHAR(45) UNIQUE NOT NULL
);

CREATE TABLE Area (
	Id 	INTEGER PRIMARY KEY,
	Name 	VARCHAR(25) UNIQUE NOT NULL,
	School 	INTEGER NOT NULL 
		REFERENCES School(id)
);

CREATE TABLE Student (
	Id      INTEGER PRIMARY KEY,
	Name 	VARCHAR(30) NOT NULL,
	Age 	INTEGER NOT NULL, 
	Gender 	CHAR(1) NOT NULL 
		CHECK(Gender IN ('M','F'))
);

CREATE TABLE Majors (
	Student INTEGER REFERENCES Student(Id),
	Area 	INTEGER REFERENCES Area(Id),
	PRIMARY KEY(Student,Area)
);

CREATE TABLE Minors (
	Student INTEGER REFERENCES Student(Id),
	Area 	INTEGER REFERENCES Area(Id),
	PRIMARY KEY(Student,Area)
);

[Updated on: Sun, 04 November 2007 01:57]

Report message to a moderator

Re: SQL Help [message #278424 is a reply to message #278421] Sun, 04 November 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

For Task 1, this is what i've been able to come up with


Does it work? Do you ask us if this is a correct answer?

For Task 2, try to answer (build a query) to each question independently then try to combine the answers.
Post what you try, given the answer you posted for task 1 and the clues I gave you, I think you can easily do the first part.

Regards
Michel
Re: SQL Help [message #279080 is a reply to message #278424] Tue, 06 November 2007 19:42 Go to previous messageGo to next message
orangejuice
Messages: 3
Registered: November 2007
Junior Member
thnx for the help michel, do u mind deleting this thread?
Re: SQL Help [message #279108 is a reply to message #279080] Wed, 07 November 2007 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that every solved topic should be removed? How would anyone benefit from it?
Re: SQL Help [message #279137 is a reply to message #279080] Wed, 07 November 2007 01:55 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of deleting the topic, it would better you post the queries you find in order to help others in their problem and maybe we can enhance them for the good of everyone.

Regards
Michel
Previous Topic: OCP or OCA?
Next Topic: sample database for studing
Goto Forum:
  


Current Time: Sun Dec 04 04:36:30 CST 2016

Total time taken to generate the page: 0.04463 seconds