CODEWITHSUNDEEP
sqlpostgresql
mrsquid
mrsquid

Reputation: 635

PostgreSQL subquery specification

I am working on several SQL queries for practice and was having some trouble with this one.

I am trying to find the distinct courses that ‘SYS’ track students in 'CptS' major are enrolled in and then return the courseno and credits for those courses. The results should be sorted based on the courseno.

The tables can be created from this file. It is a zip file containing the SQL to create the tables.

The order they need to be created is like so: course, tracks, student, enroll, prereq,trackrequirements

I am having trouble getting the count of the courses which are just in the the trackrequirements rather than all of the courses enrolled.

Here is my SQL statement thus far:

SELECT student.sname, student.sid, student.major FROM enroll, student, trackrequirements
WHERE student.trackcode = trackrequirements.trackcode AND student.major = trackrequirements.major AND enroll.sid = student.sid AND enroll.courseno = trackrequirements.courseno
GROUP BY student.sname, student.sid, student.major
HAVING COUNT(trackrequirements.courseno) = COUNT(enroll.courseno)
ORDER BY student.sname

If someone could give me some advice or help it would be much appreciated.

Here are the Schemas:

CREATE TABLE Course (
courseno VARCHAR(7),
credits INTEGER NOT NULL,
 enroll_limit INTEGER,
 classroom VARCHAR(10),
PRIMARY KEY(courseNo),
);
CREATE TABLE Tracks (
major VARCHAR(7),
trackcode VARCHAR(10),
title VARCHAR(30),
PRIMARY KEY(major, trackcode)
);
CREATE TABLE Student (
sID CHAR(8),
sName VARCHAR(30),
major VARCHAR(10),
trackcode VARCHAR(10),
PRIMARY KEY(sID),
FOREIGN KEY (major,trackcode) REFERENCES Tracks(major,trackcode)
);
CREATE TABLE Enroll (
courseno VARCHAR(7),
sID CHAR(8),
grade FLOAT NOT NULL,
PRIMARY KEY (courseNo, sID),
FOREIGN KEY (courseNo) REFERENCES Course(courseNo),
FOREIGN KEY (sID) REFERENCES Student(sID)
);
CREATE TABLE Prereq (
courseNo VARCHAR(7),
preCourseNo VARCHAR(7),
PRIMARY KEY (courseNo, preCourseNo),
FOREIGN KEY (courseNo) REFERENCES Course(courseNo),
FOREIGN KEY (preCourseNo) REFERENCES Course(courseNo)
);
CREATE TABLE TrackRequirements (
major VARCHAR(7),
trackcode VARCHAR(10),
courseNo VARCHAR(7),
PRIMARY KEY (major,trackcode,courseNo),
FOREIGN KEY (major,trackcode) REFERENCES Tracks(major,trackcode),
FOREIGN KEY (courseNo) REFERENCES Course(courseNo)
);

Here is how some of the data looks:

Thank you for reading

Course:

('CptS121',4,24,'Sloan175'),                                                                      ('CptS122',4,25,'Sloan175'),                                                                    ('CptS223',3,25,'Sloan150'),                                                                    ('CptS260',3,3,'Sloan150'),                                                                     ('CptS322',3,20,'Sloan169'),                                                                    ('CptS323',3,19,'Sloan169')

Enroll:

('MATH115','12584189',3),
('MATH115','12534189',2),
('MATH115','12524189',4),
('CE211','12584189',3.5),
('CE211','12534189',2.5)

Prereq:

('CptS122','CptS121'),
('CptS223','CptS122'),
('CptS322','CptS223'),
('CptS323','CptS322')

Student:

('12584489','Aaron','ME',NULL),

('12584189','Macy','CE',NULL),

('12583889','John','MATH',NULL)

('12583589','Alice','CptS','SYS')

TrackRequirements:

('CptS','SE','CptS121'),
('CptS','SE','CptS122'),    
('CptS','SE','CptS223'),    
('CptS','SE','CptS260')

Tracks:

('CptS','SE','Software Engineering Track'),
('CptS','SYS','Systems Track'), 
('CptS','G','General Track'),   
('EE','CE', 'Computer Engineering Track'),  
('EE','ME', 'Microelectronics Track'),  
('EE','POW','Power Track');

This is my current output:

 sname sid major
 Ali 12582389 CptS
 Alice 12583589 CptS
 Bill 12581189 CptS
 Jack 12584789 CptS

This is the desired output:

sid sname major
12584789 Jack CptS

The data shown is only a piece of the total data, if you want to see the full data please let me know.

Any help is appreciated, please let me know if I have to change anything else.

Thank you for reading

Upvotes: 0

Views: 149

Answers (1)

Neels
Neels

Reputation: 2543

I had to tweak your data to keep the relational integrity under check. With that what you need is something like the query below. Of course, you would need to replace the where class with the condition relevant to your problem. You can go to this DBFiddle to see a live run of the below query. The rule of thumb for building such queries is to follow basic steps like:

  • Isolate what you need as output
  • Find out the tables that provide the needed output
  • Look for any relationship between the tables.
  • If the relationship is missing find the tables that can complete the joins.
  • Add your filter criteria to the where clause.
  • Complete the final output demands like sorting, grouping.

This is absolutely a very layman way of explaining Query Building, but having a flow in mind helps. Let me know if you have questions.

select distinct s.sname, c.courseno, c.credits from trackrequirements t
JOIN enroll e ON e.courseno = t.courseno
JOIN student s ON s.sID = e.SID AND t.major = s.major AND t.trackcode = s.trackcode 
JOIN course c ON c.courseno = t.courseno
where t.major = 'CptS'  AND t.trackcode = 'SE'
ORDER BY c.courseno;

Upvotes: 1

Related Questions