Databases Concepts and SQL

  • Question 1
    CBSEENCO12011598

    Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.

    DVD

    DCODE  DTITLE   DTYPE 
    F101 Henry Martin  Folk
    C102 Dhrupad Classical
    C101 The Planets Classical
    F102 Universal Soldier Folk
    R102 A day in life Rock


    MEMBER

    MID

    NAME

    DCODE

    ISSUEDATE

    101

    AGAM SINGH

    R102

    2017-11-30

    103

    ARTH JOSEPH

    F102

    2016-12-13

    102

    NISHA HANS

    C101

    2017-07-24

    1. To display all details from the table MEMBER in descending order of ISSUEDATE.
    2. To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
    3. To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
    4. To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued (i.e ISSUEDATE) in the year 2017.
    5. SELECT MIN(ISSUEDATE) FROM MEMBER;
    6. SELECT DISTINCT DTYPE FROM DVD;
    7. SELECT D.DCODE,NAME,DTITLE
    8. FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE; SELECT DTITLE FROM DVD
    9. WHERE DTYPE NOT IN ( ' Folk ' , ' Classical ' );

     

    Solution

    (i) SELECT * FROM MEMBER ORDER BY ISSUEDATE DESC;

    (ii)SELECT DCODE,DTITLE FROM DVD WHERE DTYPE=’Folk’;

    (iii)SELECT COUNT(*),DTYPE FROM DVD GROUP BY DTYPE;

    (iv) SELECT NAME, ISSUEDATE FROM MEMBER WHERE
          ISSUEDATE>=’2017-01-01’ AND ISSUEDATE<=’2017-12-31’;

    (v) MIN(ISSUEDATE)
         2016-12-13

    (vi)DISTINCT DTYPE
         Folk
         Classical
         Rock

    (vii) 

    DCODE  NAME   DTITLE  
    R102 AGAM SINGH  A day in life
    F102 ARTH JOSEPH Universal Soldier
    C101 NISHA HANS The Planets


    (viii) DTITLE
           A day in life

    Question 2
    CBSEENCO12011698

    Consider the following DEPT and WORKER tables. Write SQL queries for (i) to(iv) and find outputs for SQL queries (v) to (viii):
    Table: DEPT

    DCODE DEPARTMENT CITY
    D01 MEDIA DELHI
    D02 MARKETING DELHI
    D03 INFRASTRUCTURE MUMBAI
    D05 FINANCE  KOLKATA
    D04 HUMAN RESOURCE MUMBAI

    Table: WORKER

    WNO NAME DOJ DOB GENDER DCODE
    1001 George K 2013-09-02 1991-09-01 MALE D01
    1002 Ryma Sen 2012-12-11 1990-12-15 FEMALE D03
    1003 Mohitesh 2013-02-03 1987-09-04 MALE D05
    1007 Anil Jha 2014-01-17 1984-10-19 MALE D04
    1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE D01
    1005 R SAHAY 2013-11-18 1987-03-31 MALE D02
    1006 Jaya Priya 2014-06-09 1985-06-23 FEMALE D05

    Note : DOJ refers to date of joining and DOB refers to date of Birth of workers.

    1. To display Wno, Name, Gender from the table WORKER in descending order of Wno.
    2. To display the Name of all the FEMALE workers from the table WORKER.
    3. To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.
    4. To count and display MALE workers who have joined after ‘1986-01-01’.
    5. SELECT COUNT(*), DCODE FROM WORKER
      GROUP BY DCODE HAVING COUNT(*)>1;
    6. SELECT DISTINCT DEPARTMENT FROM DEPT;
    7. SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D WHERE
      W.DCODE=D.DCODE AND WNO<1003;
    8. SELECT MAX(DOJ), MIN(DOB) FROM WORKER;

    Solution

    (i) SELECT Wno,Name,Gender FROM Worker
        ORDER BY Wno DESC;

    (ii) SELECT Name FROM Worker
         WHERE Gender=’FEMALE;

    (iii) SELECT Wno, Name FROM Worker
         WHERE DOB BETWEEN ‘1987­01­01’ AND ‘1991­12­01;

    (iv) SELECT COUNT(*) FROM Worker
          WHERE GENDER=’MALE’ AND DOJ > ‘1986­01­01;

    (v)

    COUNT(*) DCODE
    2 D01
    2 D05


    (vi)

    Department
    MEDIA
    MARKETING
    INFRASTRUCTURE
    FINANCE
    HUMAN RESOURCE


    (vii)

    NAME DEPARTMENT CITY
    George K MEDIA DELHI
    Ryma Sen INFRASTRUCTURE MUMBAI


    (viii)

    MAX (DOJ) MIN (DOB)
    2014­06­09 1984­10­19

     

    Sponsor Area

    Mock Test Series

    Sponsor Area

    NCERT Book Store

    NCERT Sample Papers

    Entrance Exams Preparation