Scribd Logo
Upload

Welcome to Scribd!

  • 11 views

    Day 8

    Uploaded by

    Dharmik Buddhdev
    The document contains SQL queries and tables related to applicants, entrance exams, distributors, and items. It defines tables for applicants, entrance exams, distributors, and items and includes several queries joining these tables on different fields.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Day 8

    Uploaded by

    Dharmik Buddhdev
    11 views3 pages
    The document contains SQL queries and tables related to applicants, entrance exams, distributors, and items. It defines tables for applicants, entrance exams, distributors, and items and includes several queries joining these tables on different fields.

    Original Title

    day8

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains SQL queries and tables related to applicants, entrance exams, distributors, and items. It defines tables for applicants, entrance exams, distributors, and items and includes several queries joining these tables on different fields.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    11 views3 pages

    Day 8

    Uploaded by

    Dharmik Buddhdev
    The document contains SQL queries and tables related to applicants, entrance exams, distributors, and items. It defines tables for applicants, entrance exams, distributors, and items and includes several queries joining these tables on different fields.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 3

    create table applicant(aid varchar(4) primary key,

    aname varchar(10),
    city varchar(10),
    b_date date);

    create table entrance_test(et_id varchar(4) primary key,


    et_name varchar(10),
    max_score number);

    ***query***

    1. select e.et_id,max(score) from entrance_test e,etest_details t


    where e.et_id=t.et_id
    group by e.et_id;

    ET_I MAX(SCORE)
    ---- ----------
    e001 90
    e002 95

    2.
    select t.et_id,count(a.aid) from applicant a, etest_details t
    where a.aid=t.aid
    group by t.et_id;

    ET_I COUNT(A.AID)
    ---- ------------
    e001 2
    e002 2

    3.
    select count(t.aid)
    from applicant a,etest_details t
    where a.aid=t.aid
    group by t.aid;

    COUNT(T.AID)
    ------------
    2
    2

    select min(t.aid)
    from applicant a,etest_details t
    where a.aid=t.aid
    group by t.aid;

    MIN(
    ----
    a002
    a001

    4.
    select city,count(aid) from applicant
    group by city;
    CITY COUNT(AID)
    ---------- ----------
    baroda 1
    surat 1

    5.
    select a.aid,t.* from applicant a, etest_details t
    where a.aid=t.aid and a.aname='ohm patel';

    AID AID ET_I ETEST_DAT SCORE


    ---- ---- ---- --------- ----------
    a001 a001 e001 15-SEP-21 85
    a001 a001 e002 20-SEP-21 90

    *******tables*******

    create table distributor(dno varchar(4) primary key,


    dname varchar(10),
    city varchar(10),
    phone number(10));

    SQL> insert into distributor values('d001','dev shah','surat',123456789);

    1 row created.

    SQL> insert into distributor values('d002','kajal oza','baroda',223456789);

    1 row created.

    create table iteam(itno varchar(4) primary key,


    itname varchar(10),
    price number(10),
    weight number(10));

    insert into iteam values('t001','jence','500',2);

    insert into iteam values('t002','jecket','1500',3);

    insert into iteam values('t003','skirt','700',1);

    create table dist_item( dno varchar(4) references distributor(dno),


    itno varchar(4) references iteam(itno),
    qty number(10),
    ddate date);

    **query***

    1.
    SQL> select d.dname,t.itno,di.dno from distributor d,iteam t,dist_item di
    2 where t.itno=di.itno and d.dno=di.dno and t.itno='t002';

    DNAME ITNO DNO


    ---------- ---- ----
    kajal oza t002 d002

    2.
    3.
    select t.itname from distributor d,iteam t,dist_item di
    where di.dno=d.dno and t.itno=di.itno and d.dname='dev shah';

    ITNAME
    ----------
    jence

    4.
    select t.itname,di.qty from iteam t,dist_item di
    where to_char(ddate,'mon-yy')='jul-21' and t.itno=di.itno;

    ITNAME QTY
    ---------- ----------
    jence 10
    jecket 9

    5.

    select t.itname from iteam t,dist_item di


    where t.price<1000 and di.qty=10 and t.itno=di.itno;

    ITNAME
    ----------
    jence

    You might also like