1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help Regarding a Simple Oracle SQL Query

Discussion in 'SQL PL/SQL' started by srijit92, Feb 22, 2012.

  1. srijit92

    srijit92 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Kolkata
    My Table structure is like this (Check Attachment)

    I need help in doing the following queries :

    a. Find average payment made by each department
    b. Display the id and name of all the students admitted after 12th May 2008
    c. Find the names of all the students not having a phone number
    d. Find the department having minimum number of student
    e. Names of all the students of Kolkata who is under professor ‘Sam’
    f. Find the id, name and dept of the student made highest payment so far
    g. Find the name, code number and dept of each professor not having a ‘PhD’
    h. Number of student admitted in this year
    i. Find the department having minimum number of professor
    j. Design a query to find name and id of all the students of IT department whose fee is lesser than 50000

    Please help me, i need t solve them ASAP !
     

    Attached Files:

    • f.jpg
      f.jpg
      File size:
      22.8 KB
      Views:
      27
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    When is your homework deadline ?
     
  3. srijit92

    srijit92 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Kolkata
    The day after tomorrow...
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    We are not here to do your home work. We can just guide/assist you in case you are away from the result.
    In your case, you haven't even tried your homework.

    You do your homework your own and let us know the major issues in case if you face them. we will be ready to guide you. But you have to show us what you tried so far.

    1. Create script
    2. Insert script
    3. Expected output
    4. Query you tried
    5. Output you got
    3. Query you rt
     
  5. srijit92

    srijit92 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Kolkata
    Check the attachment.. Created & established relationship.

    Code (SQL):


    CREATE TABLE professor
    (
        code varchar2(6),
        name varchar2(50),
        degree varchar2(10) CHECK (degree IN ('b-tech','m-tech','phd')),
        dept varchar2(10) CHECK (dept IN ('cse','it','me','ece'))
    );

    CREATE TABLE department
    (
        dept varchar2(10) CHECK (dept IN ('cse','it','me','ece')),
        capacity NUMBER(5),
        fee FLOAT(10)
    );

    CREATE TABLE contact
    (
        id NUMBER(5),
        phone varchar2(15),
        address varchar2(100)
    );

    CREATE TABLE student
    (
        id NUMBER(5),
        name varchar2(50),
        dept varchar2(10) CHECK (dept IN ('cse','it','me','ece')),
        admission_date DATE
    );

    ALTER TABLE contact ADD PRIMARY KEY(id);
    ALTER TABLE student ADD PRIMARY KEY(id);
    ALTER TABLE department ADD PRIMARY KEY(dept);
    ALTER TABLE professor ADD PRIMARY KEY(code);

    ALTER TABLE contact ADD ( FOREIGN KEY (id) REFERENCES student(id));
    ALTER TABLE student ADD ( FOREIGN KEY (dept) REFERENCES department(dept));
    ALTER TABLE professor ADD ( FOREIGN KEY (dept) REFERENCES department(dept));


    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('1', 'srijit', 'me');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('2', 'Raj', 'cse');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('3', 'Kishan', 'it');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('4', 'Rohim', 'ece');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('5', 'Ratul', 'it');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('6', 'Resma', 'cse');
    INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('7', 'Sam', 'me');

    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('1', '9999', 'ferfetfetrf');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('2', '35352', 'sgsgsg');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('3', '532252', '325afasfd');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('4', '2435243', 'sgsdg');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('5', '235235', '2asdsdg');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('6', '325235', 'aefwqet');
    INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('7', '235235', 'sswwrt');

    INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('a', 'ratul', 'phd', 'cse');
    INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('b', 'asok', 'b-tech', 'it');
    INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('c', 'lala', 'm-tech', 'ece');
    INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('d', 'hori', 'phd', 'me');
    INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('e', 'malati', 'b-tech', 'cse');

    CREATE VIEW summary AS
     SELECT student.id,student.name,student.admission_date,
        contact.phone,contact.address,student.dept,
        department.capacity,department.fee
       FROM student,
        contact,
        department
      WHERE student.id=contact.id
        AND department.dept=student.dept;
     
     

    Attached Files:

    • ss.txt
      File size:
      2.9 KB
      Views:
      4
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Still you haven't provided us what you tried foe each of these queries.

    BTW. It is not good practice to create the object in SYSTEM user. Create a different user and create the tables over there.
     
  7. srijit92

    srijit92 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Kolkata
    What i tried is in the notepad file.. Whatever i'm posting here, its telling spam...
     

    Attached Files:

    • s.txt
      File size:
      592 bytes
      Views:
      5
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I've run your queries and have found the following issues (I added department data because you didn't include it in your create table script):

    Code (SQL):
    SQL>
    SQL> CREATE TABLE professor
      2  (
      3  code varchar2(6),
      4  name varchar2(50),
      5  degree varchar2(10) CHECK (degree IN ('b-tech','m-tech','phd')),
      6  dept varchar2(10) CHECK (dept IN ('cse','it','me','ece'))
      7  );
    TABLE created.
    SQL>
    SQL> CREATE TABLE department
      2  (
      3  dept varchar2(10) CHECK (dept IN ('cse','it','me','ece')),
      4  capacity NUMBER(5),
      5  fee FLOAT(10)
      6  );
    TABLE created.
    SQL>
    SQL> CREATE TABLE contact
      2  (
      3  id NUMBER(5),
      4  phone varchar2(15),
      5  address varchar2(100)
      6  );
    TABLE created.
    SQL>
    SQL> CREATE TABLE student
      2  (
      3  id NUMBER(5),
      4  name varchar2(50),
      5  dept varchar2(10) CHECK (dept IN ('cse','it','me','ece')),
      6  admission_date DATE
      7  );
    TABLE created.
    SQL>
    SQL> ALTER TABLE contact ADD PRIMARY KEY(id);
    TABLE altered.
    SQL> ALTER TABLE student ADD PRIMARY KEY(id);
    TABLE altered.
    SQL> ALTER TABLE department ADD PRIMARY KEY(dept);
    TABLE altered.
    SQL> ALTER TABLE professor ADD PRIMARY KEY(code);
    TABLE altered.
    SQL>
    SQL> ALTER TABLE contact ADD ( FOREIGN KEY (id) REFERENCES student(id));
    TABLE altered.
    SQL> ALTER TABLE student ADD ( FOREIGN KEY (dept) REFERENCES department(dept));
    TABLE altered.
    SQL> ALTER TABLE professor ADD ( FOREIGN KEY (dept) REFERENCES department(dept));
    TABLE altered.
    SQL>
    SQL> INSERT ALL
      2  INTO department
      3  VALUES('cse', 400, 1000)
      4  INTO department
      5  VALUES('it', 600, 4250)
      6  INTO department
      7  VALUES('me', 125, 2175)
      8  INTO department
      9  VALUES('ece',275, 3000)
     10  SELECT * FROM dual;
    4 ROWS created.
    SQL>
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('1', 'srijit', 'me');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('2', 'Raj', 'cse');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('3', 'Kishan', 'it');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('4', 'Rohim', 'ece');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('5', 'Ratul', 'it');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('6', 'Resma', 'cse');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('7', 'Sam', 'me');
    1 ROW created.
    SQL> INSERT INTO "STUDENT" (ID, NAME, DEPT) VALUES ('8', 'Nishal', 'it');
    1 ROW created.
    SQL>
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('1', '9999', 'ferfetfetrf');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('2', '35352', 'sgsgsg');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('3', '532252', '325afasfd');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('4', '2435243', 'sgsdg');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('5', '235235', '2asdsdg');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('6', '325235', 'aefwqet');
    1 ROW created.
    SQL> INSERT INTO "CONTACT" (ID, PHONE, ADDRESS) VALUES ('7', '235235', 'sswwrt');
    1 ROW created.
    SQL>
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('a', 'ratul', 'phd', 'cse');
    1 ROW created.
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('b', 'asok', 'b-tech', 'it');
    1 ROW created.
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('c', 'lala', 'm-tech', 'ece');
    1 ROW created.
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('d', 'hori', 'phd', 'me');
    1 ROW created.
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('e', 'malati', 'b-tech', 'cse');
    1 ROW created.
    SQL> INSERT INTO "PROFESSOR" (CODE, NAME, DEGREE, DEPT) VALUES ('f', 'sam', 'b-tech', 'it');
    1 ROW created.
    SQL>
    SQL> CREATE VIEW summary AS
      2   SELECT student.id,student.name,student.admission_date,
      3  contact.phone,contact.address,student.dept,
      4  department.capacity,department.fee
      5   FROM student,
      6  contact,
      7  department
      8    WHERE student.id=contact.id
      9    AND department.dept=student.dept;
    VIEW created.
    SQL>
    SQL> -- Find the names of all the students not having a phone number
    SQL> SELECT name FROM student WHERE contact.phone IS NULL;
    SELECT name FROM student WHERE contact.phone IS NULL
                                   *
    ERROR at line 1:
    ORA-00904: "CONTACT"."PHONE": invalid identifier

    SQL> SELECT student.name FROM student LEFT OUTER JOIN contact ON (contact.id = student.id) WHERE contact.phone IS NULL;
    NAME
    ------------------------------
    Nishal
    SQL>
    SQL> -- Names of all the students of Kolkata who is under professor 'Sam'÷
    SQL> SELECT name FROM student WHERE professor.name='Sam' AND department.dept=student.dept;
    SELECT name FROM student WHERE professor.name='Sam' AND department.dept=student.dept
                                                            *
    ERROR at line 1:
    ORA-00904: "DEPARTMENT"."DEPT": invalid identifier

    SQL> SELECT student.name FROM student, department, professor WHERE professor.name='sam' AND student.dept = professor.dept AND department.dept=student.dept;
    NAME
    ------------------------------
    Kishan
    Ratul
    Nishal
    SQL>
    SQL> -- Find the name, code number and dept of each professor not having a 'PhD'
    SQL> SELECT name,code,dept FROM professor WHERE degree NOT LIKE 'phd';
    NAME                           CODE   DEPT
    ------------------------------ ------ ----------
    asok                           b      it
    lala                           c      ece
    malati                         e      cse
    sam                            f      it
    SQL>
    SQL> -- Design a query to find name and id of all the students of IT department whose fee is lesser than 5000
    SQL> SELECT name,id FROM student WHERE dept='it' AND department.fee<5000;
    SELECT name,id FROM student WHERE dept='it' AND department.fee<5000
                                                    *
    ERROR at line 1:
    ORA-00904: "DEPARTMENT"."FEE": invalid identifier

    SQL> SELECT student.name,student.id FROM student, department WHERE student.dept='it'AND department.dept = student.dept AND department.fee<5000;
    NAME                             ID
    ------------------------------ ----
    Kishan                            3
    Ratul                             5
    Nishal                            8
    SQL>
    You are not performing joins when they are necessary which is why most of your queries produced errors. Corrected queries are in the code posted above. Notice also the outer join to find students without contact information; it is possible in your example to not have a contact record for a valid student record.
     
    srijit92 likes this.
  9. srijit92

    srijit92 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Kolkata
    Thanks David !

    Can you please tell me how do i perform date operations in Oracle like inserting into a date field, i mean what is the format to do so?
    Suppose i have :
    Code (SQL):
    CREATE TABLE test(ad_date DATE);
    INSERT INTO test(?????);
    Regarding the problem (are they correct),

    Find average payment made by each department :
    Code (SQL):
    SELECT dept, fees=(avg(fees)) FROM department;
     
    Display the id and name of all the students admitted after 12th May 2008
    Code (SQL):
    SELECT id,name FROM students WHERE admisssion_date > ???
    Find the department having minimum number of student
    select dept from department where student=min(count(student)) from student;

    Number of student admitted in this year
    select count(student) from student where admssion date > ???

    Find the department having minimum number of professor
    select dept from department where min(count(professor));

    Find the id, name and dept of the student made highest payment so far
    select id,name,dept from student where fees=(select max(fees) from student);
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    For you first section of questions.

    dates are inserted or queried using TO_DATE() function. It is the Inbuilt function of Oracle. A lot of date format mask can be used with this function.You can get the explanation from here

    Also, Please have a look at the following queires

    Code (SQL):

     
    SQL> SELECT * FROM emp;

         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        400         30
          7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
          7839 KING       PRESIDENT            17-NOV-1981       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-1983       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-1981        950        150         30
          7902 FORD       ANALYST         7566 03-DEC-1981       3000        130         20

    12 ROWS selected.

    SQL> -- Employees joined before 01-Jul-1981 --> First Method
    SQL> SELECT * FROM emp WHERE HIREDATE > TO_DATE('01-JUL-1981','DD-MON-YYYY');

         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
          7839 KING       PRESIDENT            17-NOV-1981       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-1983       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-1981        950        150         30
          7902 FORD       ANALYST         7566 03-DEC-1981       3000        130         20

    6 ROWS selected.

    SQL> -- Employees joined before 01-Jul-1981 --> Second Method
    SQL> SELECT * FROM emp WHERE HIREDATE > TO_DATE('01071981','ddmmyyyy');

         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
          7839 KING       PRESIDENT            17-NOV-1981       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-1983       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-1981        950        150         30
          7902 FORD       ANALYST         7566 03-DEC-1981       3000        130         20

    6 ROWS selected.

    SQL>

     
     
    srijit92 likes this.
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Regarding Average payment...

    Average is an aggregate function in Oracle. There are some other built-in aggregate functions in Oracle like AVG, MAX, MIN , SUM etc. These function used to aggregate the result against a group of fields. You can see some illustration here. Aggregate function normally used with Group By and HAVING clause

    1. When the Select statement queries just an aggregate function, You don't need any group by clause.
    2. When the Select statement queries aggregate function along with some filed , You need to have Non-aggregating fields as a part of GROUP BY Clause.
    3. You can use Having clause optionally for restricting the selected aggregate functions.

    Please have a look at the SQL statements.

    Code (SQL):

    SQL> -- No Need Of Group by
    SQL> SELECT SUM(SAL) "Total Salary" FROM EMP;

    Total Salary
    ------------
           24725

    SQL> -- Using GROUP BY to get Department Wise Total Salary
    SQL> SELECT DEPTNO,  SUM(SAL) "DEPT_SALARY"
      2  FROM EMP
      3  GROUP BY DEPTNO;

        DEPTNO DEPT_SALARY
    ---------- -----------
            30        9400
            20        7875
            10        7450

    SQL> -- Using Having clause to select only those departments having total salary > 7500
    SQL> SELECT DEPTNO,  SUM(SAL) "DEPT_SALARY"
      2  FROM EMP
      3   GROUP BY DEPTNO
      4  HAVING SUM(SAL) > 7500;

        DEPTNO DEPT_SALARY
    ---------- -----------
            30        9400
            20        7875

    SQL>
     

    So Your code should look like this..

    Code (SQL):

    SELECT dept, avg(fees) "fees"
    FROM department
    GROUP BY dept;
     
     
    srijit92 likes this.
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Regarding subqueries..

    We use subqueries , when we need to get the output of some other query as the input to our main query.
    You can better idea from this link

    Also please have a look at the below example.

    Code (SQL):

    SQL> -- Departments with Employee count --> used COUNT(*)
    SQL>  SELECT DEPTNO, COUNT(*)
      2  FROM EMP
      3  GROUP BY DEPTNO;

        DEPTNO   COUNT(*)
    ---------- ----------
            30          6
            20          4
            10          2

    SQL> -- Uses Order by
    SQL> SELECT DEPTNO, COUNT(*)
      2  FROM EMP
      3  GROUP BY DEPTNO
      4  ORDER BY COUNT(*);

        DEPTNO   COUNT(*)
    ---------- ----------
            10          2
            20          4
            30          6

    SQL> SELECT DEPTNO, COUNT(*)
      2  FROM EMP
      3  GROUP BY DEPTNO
      4  ORDER BY COUNT(*) DESC;

        DEPTNO   COUNT(*)
    ---------- ----------
            30          6
            20          4
            10          2

    SQL> -- Select the Deprtment No having maximum Employees
    SQL> -- Uses Rownum to get the first record
    SQL>SELECT DEPTNO FROM ( SELECT DEPTNO, COUNT(*)
       2                                          FROM EMP
       3                                   GROUP BY DEPTNO
       4                                   ORDER BY COUNT(*) DESC)
       5   WHERE ROWNUM =1;

        DEPTNO
    ----------
            30

    SQL>
    SQL> -- Now We use the above query as subquery to get the Department name and location
    SQL>
    SQL>SELECT DEPTNO, DNAME ,LOC
      2    FROM DEPT WHERE DEPTNO = (SELECT DEPTNO
      3                                                  FROM ( SELECT DEPTNO, COUNT(*)
      4                                                                FROM EMP
      5                                                         GROUP BY DEPTNO
      6                                                         ORDER BY COUNT(*) DESC)
      7                                                WHERE ROWNUM =1);

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            30 SALES          CHICAGO

    SQL>
     
    There are lost of alternatives to get the same output. Bit for a beginner this SQL may be suffice.

    Code (SQL):

    SQL> -- Alternative --> slightly complex
    SQL> SELECT dept.DEPTNO, DNAME ,LOC
      2  FROM dept, emp
      3  WHERE  emp.deptno = dept.deptno
      4  GROUP BY dept.DEPTNO, DNAME ,LOC
      5  HAVING COUNT(*) = (  SELECT  MAX(COUNT(*))
      6                       FROM EMP
      7                       GROUP BY DEPTNO );

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            30 SALES          CHICAGO

    SQL>

     
     
    srijit92 likes this.