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
 General SQL Server Forums
 New to SQL Server Programming
 Which professor has biggestnumber of students?

Author  Topic 

whitepear
Starting Member

24 Posts

Posted - 2014-04-13 : 09:01:31
Hello!
I have to make such a querry.
Here is what I tried as the first step:

BEGIN TRANSACTION;

DROP TABLE TEACHER;
DROP TABLE STUDENT;
DROP TABLE LS;
DROP TABLE LECTURE;




CREATE TABLE TEACHER(
ID_TEACHER INTEGER PRIMARY KEY,
);

CREATE TABLE LECTURE(
ID_LECTURE INTEGER PRIMARY KEY,
FK_ID_TEACHER INTEGER
);

CREATE TABLE LS(
ID_LS INTEGER PRIMARY KEY,
FK_ID_LECTURE INTEGER,
FK_ID_STUDENT INTEGER
);

CREATE TABLE STUDENT(
ID_STUDENT INTEGER PRIMARY KEY
);

INSERT INTO STUDENT VALUES(1);
INSERT INTO STUDENT VALUES(2);
INSERT INTO STUDENT VALUES(3);
COMMIT;

INSERT INTO TEACHER VALUES(1);
INSERT INTO TEACHER VALUES(2);
INSERT INTO TEACHER VALUES(3);
COMMIT;

INSERT INTO LECTURE VALUES(1,1);
INSERT INTO LECTURE VALUES(2,1);
INSERT INTO LECTURE VALUES(3,1);
INSERT INTO LECTURE VALUES(4,2);
INSERT INTO LECTURE VALUES(5,2);
INSERT INTO LECTURE VALUES(6,2);
INSERT INTO LECTURE VALUES(7,3);
INSERT INTO LECTURE VALUES(8,3);
COMMIT;


INSERT INTO STUDENT VALUES(1);
INSERT INTO STUDENT VALUES(2);
INSERT INTO STUDENT VALUES(3);
COMMIT;

INSERT INTO LS VALUES(1,1,1);
INSERT INTO LS VALUES(2,2,1);
INSERT INTO LS VALUES(3,3,1);
INSERT INTO LS VALUES(4,4,2);
INSERT INTO LS VALUES(5,5,2);
INSERT INTO LS VALUES(6,6,2);
INSERT INTO LS VALUES(7,7,1);
INSERT INTO LS VALUES(8,8,1);
INSERT INTO LS VALUES(9,8,2);
INSERT INTO LS VALUES(10,8,3);
INSERT INTO LS VALUES(11,7,2);
INSERT INTO LS VALUES(12,7,3);
COMMIT;


SELECT * FROM TEACHER, STUDENT, LS, LECTURE;






I am gettin next errors:
Error: near line 3: no such table: TEACHER
Error: near line 11: near ")": syntax error
Error: near line 35: no such table: TEACHER
Error: near line 36: no such table: TEACHER
Error: near line 37: no such table: TEACHER
Error: near line 38: SQL logic error or missing database
Error: near line 48: SQL logic error or missing database
Error: near line 51: constraint failed
Error: near line 52: constraint failed
Error: near line 53: constraint failed
Error: near line 54: SQL logic error or missing database
Error: near line 68: SQL logic error or missing database
Error: near line 71: no such table: TEACHER

Please, does someone has any idea how to fix it?
many thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-04-13 : 14:39:40
Is this MS SQL Server? error messages doesnt seem like t-sql.
In t-sql you'll do it as below

SELECT TOP 1 WITH TIES l.FK_ID_TEACHER
FROM LS ls
INNER JOIN LECTURE l
ON l.ID_LECTURE = ls.FK_ID_LECTURE
GROUP BY l.FK_ID_TEACHER
ORDER BY COUNT(ls.FK_ID_STUDENT) DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-14 : 09:15:29
quote:
Originally posted by visakh16

Is this MS SQL Server? error messages doesnt seem like t-sql.




Error messages look like SQLite
Go to Top of Page
   

- Advertisement -