Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Student - project database

Posted By: Bogart Fischer     Category: Oracle     Views: 20479

Article that create Student - Project database with insert, select, update, delete etc...

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

  
Share: 


Didn't find what you were looking for? Find more on Student - project database Or get search suggestion and latest updates.

Bogart Fischer
Bogart Fischer author of Student - project database is from Frankfurt, Germany.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!