SQL>
SQL>
SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY,
3 empID INT NOT NULL,
4 ClassID INT NOT NULL,
5 EnrolledOn DATE,
6 Grade INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE '2002-09-23',62);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE '2002-09-30',70);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE '2003-09-23',51);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE '2003-09-23',41);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE '2003-09-23',68);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE '2002-09-30',78);
1 row created.
SQL>
SQL>
SQL> SELECT e1.empID, e1.ClassID, (
2 SELECT COUNT(*) FROM dept e2
3 WHERE e1.ClassID = e2.ClassID)-1
4 AS OtherempsInClass
5 FROM dept e1
6 WHERE empID = 6;
no rows selected
SQL>
SQL> drop table dept;
Table dropped.