Mega Code Archive

Simple Subqueries in select statement

SQL> SQL> SQL> CREATE TABLE emp ( 2 empID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL); Table created. SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom'); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack'); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary'); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill'); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat'); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor'); 1 row created. SQL> CREATE TABLE empExam ( 2 empID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID)); Table created. SQL> SQL> SQL> INSERT INTO empExam (empID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,'Satisfactory'); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,'Good result'); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,'Hard'); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,'Simple'); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SQL> SELECT empID, Name, 2 (SELECT COUNT(*) FROM empExam 3 WHERE empExam.empID = emp.empID) 4 AS ExamsTaken 5 FROM emp 6 ORDER BY ExamsTaken DESC; EMPID NAME EXAMSTAKEN ---------- -------------------------------------------------- ---------- 2 Jack 3 1 Tom 2 5 Cat 0 4 Bill 0 6 Victor 0 3 Mary 0 6 rows selected. SQL> SQL> drop table empExam; Table dropped. SQL> drop table emp; Table dropped.