Consider the student-project database.
STUDENT
STUDENT NO TEXT (3) PRIMARYKEY
STUDENT_NAME TEXT (10)
STUDENT_DOB DATE
STUDENT_DOJ DATE
PROJECT
PRJ_NO TEXT (3) PRIMARY KEY
PRJ_NAME TEXT (15)
PRJ_DUR NUMBER (2)
PRJ_PLATFORM TEXT (10)
STUDENT PROJECT
STUDENT_NO TEXT (3)
PRJ_NO TEXT (3)
DESIGNATION TEXT (10)
PRIMARYKEY (STUDENT_NO,PRJ_NO,DESIGNATION)
FOREIGN KEY(STUDENT_NO)
FOREIGN KEY(PRJ_NO)
Create student table
CREATE TABLE Student
(
st_no varchar2(5) PRIMARY KEY,
st_name varchar2(10),
st_dob date,
st_doj date
);
Create project table
CREATE TABLE Project
(
prj_no varchar2(3) PRIMARY KEY,
prj_name varchar2(15),
prj_dur number(2),
prj_platform varchar2(10)
);
Create studentproject table
CREATE TABLE StudentProject
(
st_no varchar2(3) REFERENCES STUDENT(st_no),
prj_no varchar2(3) REFERENCES PROJECT(prj_no),
designation varchar2(10),
PRIMARY KEY (st_no,prj_no,designation)
);
Description / definition of student table
desc student;
Name Null? Type
----------------------------------------- -------- -----------------------------------
ST_NO NOT NULL VARCHAR2(5)
ST_NAME VARCHAR2(10)
ST_DOB DATE
ST_DOJ DATE
Description / definition of project table
desc project;
Name Null? Type
-------------------------------------------- -------- -----------------------------------
PRJ_NO NOT NULL VARCHAR2(3)
PRJ_NAME VARCHAR2(15)
PRJ_DUR NUMBER(2)
PRJ_PLATFORM VARCHAR2(10)
Description / definition of studentproject table
desc StudentProject;
Name Null? Type
-------------------------------------------- -------- -----------------------------------
ST_NO NOT NULL VARCHAR2(3)
PRJ_NO NOT NULL VARCHAR2(3)
DESIGNATION VARCHAR2(10)
Insert statements
INSERT STATEMENT FOR STUDENT TABLE
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST1','KRUNAL','15-AUG-1982','10-JAN-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST2','BHAVESH','20-AUG-1983','10-JAN-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST3','DARSHAN','15-MAR-1983','12-FEB-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST4','DHAVAL','11-MAY-1982','11-MAR-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST5','BIBIN','23-MAY-1983','12-MAR-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST6','SAMIR','04-SEP-82','12-APR-2003');
INSERT INTO Student
(st_no,st_name,st_dob,st_doj) VALUES ('ST7','CHIRAG','04-JUN-82','12-FEB-2003');
INSERT STATEMENT FOR PROJECT TABLE
INSERT INTO Project
(prj_no,prj_name,prj_dur,prj_platform) VALUES ('P01','XYZ',6,'VB');
INSERT INTO Project
(prj_no,prj_name,prj_dur,prj_platform) VALUES ('P02','ABC',5,'JAVA');
INSERT INTO Project
(prj_no,prj_name,prj_dur,prj_platform) VALUES ('P03','LMN',6,'C++');
INSERT STATEMENT FOR STUDENTPROJECT TABLE
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST1','P01','PROGRAMMER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST2','P01','MANAGER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST3','P02','MANAGER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST4','P02','MANAGER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST5','P03','PROGRAMMER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST6','P03','MANAGER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST1','P03','PROGRAMMER');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST1','P01','ANALYST');
INSERT INTO StudentProject
(st_no,prj_no,designation) VALUES ('ST3','P02','ANALYST');
Display student table records using select statement
SELECT * FROM STUDENT;
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
ST2 BHAVESH 20-AUG-83 10-JAN-03
ST3 DARSHAN 15-MAR-83 12-FEB-03
ST4 DHAVAL 11-MAY-82 11-MAR-03
ST5 BIBIN 23-MAY-83 12-MAR-03
ST6 SAMIR 04-SEP-82 12-APR-03
ST7 CHIRAG 04-JUN-82 12-FEB-03
Display project table records using select statement
SELECT * FROM PROJECT;
Output :
PRJ PRJ_NAME PRJ_DUR PRJ_PLATFO
P01 XYZ 6 VB
P02 ABC 5 JAVA
P03 LMN 6 C++
Display studentproject table records using select statement
SELECT * FROM STUDENTPROJECT;
Output :
ST_ PRJ DESIGNATION
ST1 P01 PROGRAMMER
ST2 P01 MANAGER
ST3 P02 MANAGER
ST4 P02 MANAGER
ST5 P03 PROGRAMMER
ST6 P03 MANAGER
ST1 P03 PROGRAMMER
ST1 P01 ANALYST
ST3 P02 ANALYST
Find number of student who participated in the project 'p01'
SELECT * FROM STUDENT WHERE st_no IN
(SELECT st_no FROM StudentProject WHERE prj_no = 'P01');
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
ST2 BHAVESH 20-AUG-83 10-JAN-03
Find number of student who participated in the more than single project.
SELECT distinct st_no AS "STUDENT WHITH MORE PROJECTS " FROM STUdentPROJECT A
WHERE 1 <
(
SELECT COUNT(Prj_NO) FROM STUdentPROJECT WHERE St_NO=A.St_NO GROUP BY St_NO
);
STUDENT WHITH MORE PROJECTS
---------------------------
ST1
Find the no of student who did not participated in any of the project
SELECT COUNT(*) AS "STUDENTS NOT CONTAINS PROJECTS" FROM STUDENT WHERE st_no IN
(
SELECT st_no from student where st_no
NOT IN
(select st_no from StudentProject)
);
Output :
STUDENTS NOT CONTAINS PROJECTS
------------------------------
1
Display student_no, prj_name,duration
SELECT S.st_no,P.prj_name,P.prj_dur
from Student S,Project P,StudentProject SP
where S.st_no = SP.st_no and P.prj_no = SP.prj_no;
Output :
ST_NO PRJ_NAME PRJ_DUR
ST1 LMN 6
ST1 XYZ 6
ST2 XYZ 6
ST3 ABC 5
ST4 ABC 5
ST5 LMN 6
ST6 LMN 6
Display prj_no,total no student of the project
SELECT prj_no,count(st_no) AS "TOTAL STUDENTS" from StudentProject
group by prj_no;
Output :
PRJ TOTAL STUDENTS
P01 2
P02 2
P03 3
Display a student_no,name, total no of projects.
SELECT S.st_no,S.st_name,count(SP.prj_no) AS "TOTAL PROJECTS"
from Student S, StudentProject SP
where S.st_no = SP.st_no
group by S.st_no,S.st_name;
Output :
ST_NO ST_NAME TOTAL PROJECTS
ST1 KRUNAL 2
ST2 BHAVESH 1
ST3 DARSHAN 1
ST4 DHAVAL 1
ST5 BIBIN 1
ST6 SAMIR 1
Display the information(no,name,age) of student who made the project in java.
SELECT st_no,st_name,ROUND((SYSDATE-st_DOB)/365) AS AGE from STUDENT
where st_no IN
(
SELECT st_no from StudentProject where prj_no In
(SELECT prj_no from Project where prj_platform = 'JAVA')
);
Output :
ST_NO ST_NAME AGE
ST3 DARSHAN 21
ST4 DHAVAL 22
Display the detail of student who is a programmer.
SELECT * from Student where st_no IN
(SELECT st_no from StudentProject where designation = 'PROGRAMMER');
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
ST5 BIBIN 23-MAY-83 12-MAR-03
Display the informaton of student who is as programmer and analyst in the same project. (Can use table Alias)
SELECT * from student where st_no In
(
SELECT SP1.st_no from StudentProject SP1,StudentProject SP2
where SP1.st_no = SP2.st_no AND
SP1.designation = 'ANALYST' AND
SP2.designation = 'ANALYST'
);
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
ST3 DARSHAN 15-MAR-83 12-FEB-03
Display the student who played the max designation(e.g. manager,programmer) in the same project.
select st_no,COUNT(designation) from studentproject group by st_no,prj_no
HAVING COUNT(designation) =
(SELECT MAX(temp) FROM
(select COUNT(designation) temp from studentproject group by st_no,prj_no));
Output :
ST_ COUNT(DESIGNATION)
ST1 2
ST3 2
Display the info of the project with greater than single no of student involve in it.
SELECT COUNT(st_no),prj_no from StudentProject group by prj_no;
Output :
COUNT(ST_NO) PRJ
3 P01
3 P02
3 P03
Display detail of the youngest student.
SELECT * from Student where st_dob = (SELECT MAX(st_dob) from Student);
ST_NO ST_NAME ST_DOB ST_DOJ
ST2 BHAVESH 20-AUG-83 10-JAN-03
Display the info of the project which duration is the largest.
SELECT * FROM Project WHERE prj_dur = (SELECT max(prj_dur) from Project);
Output :
PRJ PRJ_NAME PRJ_DUR PRJ_PLATFO
P01 XYZ 6 VB
P03 LMN 6 C++
Display the info of the student who works as a prog and as a analyst not for the same project.(can Use Table alias)
SELECT * FROM Student where st_no IN
(
SELECT distinct SP1.st_no
FROM StudentProject SP1,StudentProject SP2
where SP1.designation IN ('PROGRAMMER','ANALYST') and
SP2.designation In ('PROGRAMMER','ANALYST') and
SP1.prj_no <> SP2.prj_no and
SP1.st_no = SP2.st_no
);
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
Display the info of the student who works as a programmer and not as an analyst for the same project.(can Use Table alias)
SELECT * FROM Student where st_no IN
(
SELECT distinct SP1.st_no
FROM StudentProject SP1,StudentProject SP2
where SP1.designation = 'PROGRAMMER' and
SP1.designation <> 'ANALYST' and
SP2.designation = 'PROGRAMMER' and
SP2.designation <> 'ANALYST' and
SP1.prj_no <> SP2.prj_no and
SP1.st_no = SP2.st_no
);
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
Display the info of the student who participated in the project where total no of the student should be exact three.
select * from student where st_no in(SELECT st_no from
(
SELECT COUNT(st_no),prj_no from StudentProject group by prj_no
HAVING COUNT(st_no) = 3
) a,StudentProject b where a.prj_no = b.prj_no);
Output :
ST_NO ST_NAME ST_DOB ST_DOJ
ST1 KRUNAL 15-AUG-82 10-JAN-03
ST2 BHAVESH 20-AUG-83 10-JAN-03
ST3 DARSHAN 15-MAR-83 12-FEB-03
ST4 DHAVAL 11-MAY-82 11-MAR-03
ST5 BIBIN 23-MAY-83 12-MAR-03
ST6 SAMIR 04-SEP-82 12-APR-03
Display the info. of oldest Student with its age.
SELECT st_no,st_name,ROUND((SYSDATE-st_DOB)/365) AS AGE
from Student where st_dob =
(SELECT MIN(st_dob) from Student);
Output :
ST_NO ST_NAME AGE
ST4 DHAVAL 22