RDBMS example for project with different quiries

SQL> create table project
  2  (pno number(5) primary key,
  3  pname varchar2(20),
  4  status varchar2(5)
  5  check (status in('c','p','i')));

Table created.
SQL> insert into project values (&pno,'&pname','&status');
SQL> select * from project;

       PNO PNAME                STATU
---------- -------------------- -----
        11 voice_recognition    i
        12 speech_recognition   c
        13 video_recognition    i
        14 steganography        p
        15 cryptography         i

SQL> create table dept
  2  (deptno number(5) primary key,
  3  deptname varchar2(20),
  4  HOD varchar2(20),
  5  location varchar2(20));

Table created.
SQL> insert into dept values (&deptno,'&deptname','&HOD','&location');
SQL> select * from dept;

    DEPTNO DEPTNAME             HOD                  LOCATION
---------- -------------------- -------------------- --------------------
         1 mathematics          vijay                pune
         2 computer             ranjit               pune
         3 chemical             rahul                nasik
         4 civil                ajay                 nagpur
         5 IT                   santosh              nanded

SQL> create table dep_pro
  2  (deptno number(5) references dept,
  3  pno number(5) references project,
  4  primary key (deptno,pno));

Table created.
SQL> insert into dep_pro values (&deptno,&pno);
SQL> select * from dep_pro;

    DEPTNO        PNO
---------- ----------
         1         11
         2         12
         3         13
         4         14
         5         15





Queries:-

1 Find HOD of computer Department located in ‘Pune’.
SQL> select HOD from dept
  2  where deptname='computer' and location='pune';

HOD
--------------------
Ranjit

2 List all projects of mathematics department which are Incomplete.
  SQL>select project.*
from dept,project
where dept.deptno=project.deptno and statu='i'
and deptname='mathmatics';
 
  PNO      PNAME                     STATU
---------- --------------------             -----
  11        voice_recognition             i



3 Display the Project details of Computer Department.
SQL>select project.*
from dept,project
where dept.deptno=project.deptno and deptname='Computer';
       PNO PNAME              
---------- --------------------
        12 speech_recognition  

4 List department wise project along with status.

SQL> select deptname,pname,status from project,dept,dep_pro
  2  where project.pno=dep_pro.pno and
  3  dep_pro.deptno=dept.deptno
  4  group by deptname,pname,status;

DEPTNAME             PNAME                STATU
-------------------- --------------------         -----
IT                   cryptography                    i
chemical             video_recognition       i
civil                steganography                 p
computer             speech_recognition    c
mathematics          voice_recognition    c