SQL Assignment Help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Assignment Help.

Author

Topic

AReed51
Starting Member

3 Posts

Posted - 2013-11-09 : 23:08:39

Task: List the majors without any students.

Info: stu_id (student) is in the student table. prg_name (major) is in the program table. The two tables aren't connected in any way from what I can tell.

My Attempt:
SELECT prg_name
FROM program, student
WHERE stu_id IS NULL

That didn't work.

I asked on Y!A and this is what I got:
SELECT prg_name
FROM program
LEFT JOIN student ON stu_id=prg_name
WHERE stu_id IS NULL

I got back data, but I don't think it's correct since there are way too many mandatory courses coming up without students.

Tell me what other information I can provide to help you all solve this. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-10 : 01:30:00

ideally you should have programid field in student table as a FK. then you can do the below


SELECT *
FROM program p 
WHERE NOT EXISTS (SELECT 1
FROM student 
WHERE prg_name = p.prg_name
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

AReed51
Starting Member

3 Posts

Posted - 2013-11-10 : 12:35:10

quote:
Originally posted by visakh16

ideally you should have programid field in student table as a FK. then you can do the below

SELECT *
FROM program p
WHERE NOT EXISTS (SELECT 1
FROM student
WHERE prg_name = p.prg_name
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

~~What does "program p" mean? Sorry, total noob.~~ NVM I know now.

Anyway, I tried your code, didn't work. I think it's because I don't have a FK.

This is what my tables look like:

create table student
(stu_id varchar(6) primary key,
stu_first_name varchar(20),
stu_last_name varchar(20),
stu_city varchar(15),
stu_state varchar(2),
stu_date_of_admit date,
stu_classification varchar(2),
stu_major varchar(25));

create table program
(prg_id varchar(5) primary key,
prg_field varchar(20),
prg_name varchar(25),
prg_type varchar(1),
prg_hrs_required number);

create table course
(crs_id varchar(8) primary key,
crs_course_name varchar(40),
crs_credit_hrs number,
crs_pr_id varchar(6) references professor(pr_id));

create table course_program
(cp_prg_id varchar(5) references program(prg_id),
cp_crs_id varchar(8) references course(crs_id),
cp_requirement_type varchar(10),
primary key (cp_prg_id, cp_crs_id));

There's more, but I figured these were the relevant ones.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 01:42:48

how is program related to student? I cant see any ways by which you can relate them as per the above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

AReed51
Starting Member

3 Posts

Posted - 2013-11-12 : 20:22:25

quote:
Originally posted by visakh16

how is program related to student? I cant see any ways by which you can relate them as per the above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I found out what it was.

SELECT prg_name
FROM program
MINUS
SELECT stu_major
FROM student

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-13 : 07:46:05

quote:
Originally posted by AReed51

quote:
Originally posted by visakh16

how is program related to student? I cant see any ways by which you can relate them as per the above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I found out what it was.

SELECT prg_name
FROM program
MINUS
SELECT stu_major
FROM student

if this is how relationship stands, then you should ideally have a foreign key constraint in student on stu_major pointing to program (prg_name)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources