|
SQL>
SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY,
3 StudentID INT NOT NULL,
4 ClassID INT NOT NULL,
5 EnrolledOn DATE,
6 Grade INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE '2002-09-23',62);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE '2002-09-30',70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE '2003-09-23',51);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE '2003-09-23',41);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE '2003-09-23',68);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE '2002-09-30',78);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE '2002-09-30',80);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE '2002-09-20',70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE '2002-09-20',60);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (10,5,1,DATE '2002-09-23',33);
1 row created.
SQL>
SQL>
SQL> SELECT StudentID,
2 FLOOR(MAX(CURRENT_DATE - EnrolledOn)) AS DaysEnrolled
3 FROM dept
4 GROUP BY StudentID
5 ORDER BY DaysEnrolled DESC;
STUDENTID DAYSENROLLED
---------- ------------
4 2593
1 2590
5 2590
3 2583
2 2225
5 rows selected.
SQL>
SQL>
SQL> drop table dept;
Table dropped.
|