QUESTION 8

Question 8
APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)

CREATE TABLE

Application:

create table applicant

(

      aid varchar2(5) primary key,

      aname varchar2(20),

      addr varchar2(20),

      abirth_dt date

);

Entrance_Test

create table entrance_test

(

      etid number(10) primary key,

      etname varchar2(20),

      max_score number(10),

      cut_score number(10)

);

Etest_Centre

create table etest_centre

(

      etcid number(10) primary key,

      location varchar2(15),

      incharge varchar2(10),

      capacity number(10)

);

Etest_Details

create table etest_details

(

      aid varchar2(5) references applicant(aid),

      etid number(10) references entrance_test(etid),

      etcid number(10) references etest_centre(etcid),

      etest_dt date check(to_char(etest_dt,’d’)<>1),

      score number(10),

      primary key(aid,etid,etcid)

);

INSERT RECORDS

 -- Insert Into Application

insert into applicant values('A001','pankaj','bglore','25-jan-1988');

insert into applicant values('A002','sumit','pune','07-jun-1989');

insert into applicant values('A003','jigee','kolkata','12-apr-1990');

insert into applicant values('A004','monica','madras','02-jul-1988');

insert into applicant values('A005','parul','rajkot','01-aug-1989');

insert into applicant values('A006','geeta','mumbai','03-dec1988');

insert into applicant values('A007','sweta','surat','02-mar-1991');

insert into applicant values('A008','ami','anand','11-oct-1991');

-- Insert Into Entrace_Test

insert into entrance_test values(01,'GCET',400,150);

insert into entrance_test values(02,'AIEEE',200,100);

insert into entrance_test values(03,'NIIT',200,130);

insert into entrance_test values(04,'PMT',500,200);

insert into entrance_test values(05,'NDA',100,50);

insert into entrance_test values(06,'PABT',150,75);

-- Insert Into Etest_Centre

insert into etest_centre values(01,'JAMNAGAR','SWETAmam',120);

insert into etest_centre values(02,'PUNE','supriyamam',60);

insert into etest_centre values(03,'BGLORE','nilesh',120);

insert into etest_centre values(04,'DELHI','vikram',100);

insert into etest_centre values(05,'SURAT','aartimam',50);

insert into etest_centre values(06,'MEHSANA','vipulsir',200);

insert into etest_centre values(07,'RAJKOT','tyagisir',150);

-- Insert Into Etest_Details

insert into etest_details values('A001',02,03,'25-dec-2010',150);

insert into etest_details values('A002',03,06,'04-mar-2011',120);

insert into etest_details values('A002',02,02,'29-jan-2010',120);

insert into etest_details values('A004',04,04,'05-dec-2010',114);

insert into etest_details values('A002',05,07,'02-jan-2011',99);

insert into etest_details values('A006',04,04,'21-apr-2011',300);

insert into etest_details values('A006',06,02,'20-feb-2011',76);

(This database is for a common entrance test which is being conducted at a number of
centers and can be taken by an applicant on any day except holidays)

1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. E.g. if value is ‘1123’, it should become ‘A1123’.
   
   alter table applicant add check (aid like 'A%');
   
2. Display test center details where no tests were conducted.
   
   select * from etest_centre where etcid not in (select etcid from etest_details group by etcid);
   
3. Display details about applicants who have the same score as that of Jaydev in ‘ORACLE FUNDAMENTALS’.
   
   select applicant.aid,applicant.aname from applicant, entrance_test,etest_detailswhere applicant.aid=etest_details.aid and score =
   (
   select score
   from etest_details ed,entrance_test et,applicant a
   where et.etname='AIEEE' and et.etid=ed.etid
   and a.aname='pankaj' and a.aid=ed.aid
   ) and entrance_test.etname='AIEEE'
   and entrance_test.etid=etest_details.etid;
   
4. Display details of applicants who appeared for all tests.
   
   select aid from etest_details where etid = all (select etid from etest_details);
   
5. Display those tests where no applicant has failed.
   
   select * from entrance_test where etid not in (select ed.etid from etest_details ed,entrance_test et
   where ed.etid=et.etid and score<cut_score);
   
6. Display details of entrance test centers which had full attendance between 1st Oct 05 and
   15th Oct 05.
   
   select etcid from etest_centre where (etcid,capacity) in (
   select etcid,count(*) from etest_details where etest_dt>'1-sep-2010' and etest_dt<'30-dec-2010' group by etcid );
   
7. Display details of the applicants who scored more than the cut score in the teststhey appeared in.
   
   select ed.etid,cut_score,score from etest_details ed, entrance_test et
   where ed.etid=et.etid and score>cut_score
   
8. Display average and maximum score test wise of tests conducted at Mumbai.
   
   select max(ed.score) "MAX", avg(ed.score) "AVG" from etest_details ed, etest_centre et where et.location='DELHI' and et.etcid=ed.etcid group by ed.etid;
   
9. Display the number of applicants who have appeared for each test, test center wise
   
   select ed.etid,ed.etcid,count(aid) from etest_details ed
   group by ed.etid,ed.etcid order by etid;
   
10. Display details about test centers where no tests have been conducted.
    
    select * from etest_centre where etcid not in (select etcid from etest_details);
    
11. For tests, which have been conducted between 2-3-04 and 23-4-04, show details of the tests as well as the test centres.
    
    select ed.etid, et.etname, et.max_score, et.cut_score, ec.etcid, ec.location, ec.incharge, ec.capacity from etest_details ed, entrance_test et, etest_centre ec
    where etest_dt>'01-dec-2010' and etest_dt<'30-dec-2010' and et.etid=ed.etid and ec.etcid=ed.etcid;
    
12. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of February ?
    
    select count(*) "NO"
    
    from etest_details ed, etest_centre ec, entrance_test et
    where to_char(etest_dt,'mon')='dec'
    and ec.location='DELHI' and ec.etcid=ed.etcid and et.etid=ed.etid
    and etname='PMT' group by ec.etcid;
    
13. Display details about applicants who appeared for tests in the same month as the month in which they were born.
    
    select aid,aname from applicant where aid in(
    select a.aid from applicant a,etest_details ed where to_char(a.abirth_dt,'mon')=to_char(ed.etest_dt,'mon'));
    
    
14. Display the details about APPLICANTS who have scored the highest in each test,test centre wise.
    
    select ed.etid,ed.etcid,max(score) from etest_details ed
    group by ed.etid,ed.etcid order by etid;
    
15. Design a read only view, which has details about applicants and the tests that he has appeared for.
    
    create view view1 as select a.aid,aname,et.etid,et.etname from applicant a, etest_details ed,entrance_test et where a.aid=ed.aid and et.etid=ed.etid WITH READ ONLY
    

QUESTION 7

Question 7
PUBLISHER(publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)
ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)

CREATE TABLE

Publisher:

create table publisher

(

      publ_id varchar2(10) primary key check (publ_id LIKE 'p%'),

      publ_name varchar2(10),

      cantact_person varchar2(10),

      contact_addr varchar2(10),

      contact_phone number(10)

);

Category:

create table category

(

      cat_id varchar2(10) primary key check(cat_id LIKE 'c%'),

      cat_details varchar2(10),

      max_books number(4),

      duration number(4)

);

Book_master:

create table book_master

(

      book_id number primary key,

      bname varchar2(20),

      isbn_no number(10),

      total_copies number(5),

      publ_id varchar2(10) references publisher(publ_id)

);

Member

create table member

(

      member_id number(5) primary key,

      mname varchar2(10),

      cat_id varchar2(10) references category(cat_id),

      mem_ship_dt date

);

Issue

create table issue

(

      issue_id number(10) primary key,

      member_id number(5) references member(member_id),

      book_id number references book_master(book_id),

      issu_ret varchar2(4),

      issue_ret_dt date

);

INSERT RECORDS

-- Insert Into Publisher

insert into publisher values('p01','pankaj','ashish','bglore',9992225554);

insert into publisher values('p02','geeta','shweta','rajkot',9922448891);

insert into publisher values('p03','ami','parul','pune',8884565789);

insert into publisher values('p04','mehul','monica','delhi',7896541289);

-- Insert Into Category

insert into category values('c01','science',120,2004);

insert into category values('c02','computer',110,2002);

insert into category values('c03','novel',40,2008);

insert into category values('c04','physics',90,2003);

insert into category values('c05','halloween',77,2007);

insert into category values('c06','civics',50,2010);

-- Insert Into Book_Master

insert into book_master values(001,'HP',160054,100,'p01');

insert into book_master values(002,'godaan',160159,150,'p02');

insert into book_master values(003,'RDPD',160357,50,'p03');

insert into book_master values(004,'S1',160789,88,'p04');

insert into book_master values(005,'H1',160152,95,'p02');

insert into book_master values(006,'GK',160789,200,'p04');

insert into book_master values(007,'FAQ',160152,65,'p01');

-- Insert Into Member

insert into member values(01,'rahul','c03','01-jan-2010','jamnagar');

insert into member values(02,'preeti','c04','21-jun-2010','rajkot');

insert into member values(03,'amar','c04','15-apr-2010','delhi');

insert into member values(04,'ashish','c05','30-aug-2010','pune');

insert into member values(05,'abhishek','c03','23-oct-2010','madras');

insert into member values(06,'arun','c06','20-sep-2010','bgolore');

-- Insert Into Issue

insert into issue values(01,02,002,'I','22-jun-2010');

insert into issue values(02,03,001,'I','16-apr-2010');

insert into issue values(07,04,004,'R','14-oct-2010');

insert into issue values(03,02,005,'R','05-jul-2010');

insert into issue values(04,04,002,'I','25-nov-2010');

insert into issue values(05,02,006,'I','02-aug-2010');

insert into issue values(06,06,007,'I','30-sep-2010');

insert into issue values(07,05,007,'I','25-nov-2010');

insert into issue values(08,04,007,'I','15-dec-2010');

insert into issue values(09,03,007,'I','30-dec-2010');

In the above tables duration is in years and it stores the membership duration for that
category.

1. Change the table design of ISSUE table to add a constraint, which will allow only ‘I’ or ‘R’ to be entered in the ISSUE_RET column, which stores the action whether the book is being issued or returned.
   
   alter table issue add check(issu_ret in ('I','R'));
   
2. Add a column to the MEMBER table, which will allow us to store the address of the member.
   
   alter table member add (addr varchar2(10));
   
3. Create a table LIBRARY_USERS which has a structure similar to that of the MEMBER table but with no records.
   
   create table library_users as select * from member where 1=2;
   
4. Give details about members who have issued books, which contain ‘DATA’ somewhere in their titles.
   
   select i.member_id,m.mname from issue i, book_master b, member m
   where i.book_id=b.book_id and bname LIKE '%P%' and i.issu_ret='I' and m.member_id=i.member_id
   
5. Display the books that have been issued at the most three times in the year 2003.
   
   select book_id , count(*) from issue where issu_ret='I' and to_char(issue_ret_dt,'yyyy')= 2010 group by book_id having count(*)<=2 order by book_id
   
6. Display which books of publisher PHI that are issued right now.
   
   
   select p.publ_id,b.bname from issue i,publisher p,book_master b
   where p.publ_id=b.publ_id and b.book_id=i.book_id and i.issu_ret='I' and p.publ_name='pankaj' and to_char(issue_ret_dt,’dd-mon-yyyy’)=to_char(sysdate,’dd-mon-yyyy’);
   
   
   
7. Display details about books whose all copies are issued.
   
   select  * from book_master where (book_id,total_copies)  in
   (select book_id,count(*) from issue group by book_id);
   
8. Display the book details and members for books, which have been issued between 1st Oct 2005 and 15th Nov 2005.
   
   select i.book_id,i.member_id "MEMBER ID",m.mname "MEMBER NAME",
   
   b.bname,b.isbn_no,b.total_copies,b.publ_id
   from issue i,member m,book_master b
   where i.issue_ret_dt> '01-sep-2010' and i.issue_ret_dt<'01-dec-2010' and i.book_id= b.book_id and i.member_id=m.member_id and i.issu_ret='I';
   
9. Display all staff members who have issued at least two books.
   
   select mname from member where member_id in(select i.member_id from issue I group by i.member_id having count(i.book_id)>=3);
   
10. Display details about those publishers whose more than 100 books are available in the library.
    
    select publ_id,publ_name from publisher where publ_id in ( select b.publ_id from book_master b,publisher p where p.publ_id=b.publ_id group by b.publ_id having count(b.book_id)>=2);
    
11. Delete all those members whose membership has expired.
    
    delete from category c where duration < to_char(to_date(sysdate),'yyyy');
    
12. How many members registered in the last three months ?
    
    select count(*) "TOTAL NO" from member where to_char(mem_ship_dt,'mon') in ('dec','oct','nov')
    
13. Display since how many months has each staff member registered.
    
    select member_id, round((sysdate-mem_ship_dt)/30) "TOTAL MONTH" from member;
    
    

QUESTION 6

Question 6
WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)

CREATE TABLE

Worker:

create table worker

(

      worker_id number(10) primary key,

      name varchar2(10),

      wage_per_hour number(10),

      specialised_in varchar2(20),

      manager_id number(10)

);

Job:

create table job

(

      job_id number(10) primary key,

      type_of_job varchar2(10),

      status varchar2(10)

);

Assigned:

create table job_assigned

(

      worker_id number(10) references worker(worker_id),

      job_id number(10) references job(job_id),

      starting_date date,

      number_of_days number,

      primary key(worker_id,job_id)

);

INSERT RECORDS

-- Insert Into Worker

insert into worker values(001,'pankaj',1000,'computer','');

insert into worker values(002,'jigee',500,'medical',005);

insert into worker values(003,'ami',100,'sports',002);

insert into worker values(004,'geeta',600,'civil',006);

insert into worker values(005,'sumit',750,'engg',001);

insert into worker values(006,'parul',500,'autocad',003);

insert into worker values(008,'monica',500,'computer',009);

insert into worker values(009,'rahul',800,'computer',006);

insert into worker values(007,'sanju',1100,'civil',008);

--  Insert Into Job

insert into job values(05,'computer','incomplete');

insert into job values(02,'banking','half');

insert into job values(03,'engg','one third');

insert into job values(01,'electrical','incomplete');

insert into job values(04,'autocad','incomplete');

insert into job values(07,'civil','half');

insert into job values(06,'medical','one fourth');

-- Insert Into Job_Assigned

insert into job_assigned values(002,06,'02-jan-2000',350);

insert into job_assigned values(001,06,'15-jan-2010',50);

insert into job_assigned values(003,01,'22-mar-2003',150);

insert into job_assigned values(001,05,'01-jan-2000',05);

insert into job_assigned values(002,04,'02-jan-2008',20);

insert into job_assigned values(004,05,'28-dec-2000',10);

insert into job_assigned values(002,05,'30-apr-2000',89);

insert into job_assigned values(003,05,'30-apr-2000',89);

1. Display the date on which each worker is going to end his presently assigned job.
   
   select worker_id,starting_date+number_of_days "ENDING DATE" from job_assigned;
   
2. Display how many days remain for each worker to finish his job.
   
   select worker_id, round(starting_date+number_of_days - sysdate) "REMAINING DAYS"  from job_assigned where starting_date+number_of_days>sysdate;
   
3. Display the STARTING_DATE in the following format – ‘The fifth day of the month of October, 2004’.
   
   select 'The ' || to_char(starting_date,'ddth')||' day of the month of '||to_char(starting_date,'Month, yyyy') "DATE" from job_assigned;
   
4. Change the status to ‘Complete’ for all those jobs, which started in year 2000.
   
   update job set status='complete' where job_id in
   
   (
   select job_id from job_assigned where    to_char(starting_date,'yyyy')='2000' group by job_id
   )
5. Display job details of all those jobs where at least 25 workers are working.
   
   select job_id,count(worker_id) "TOTAL WORKER" from job_assigned group by job_id having count(worker_id)>=3;
   
   
6. Display all those jobs that are already completed.
   
   select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where (ja.starting_date+ja.number_of_days)<sysdate and j.job_id=ja.job_id;
   
7. Find all the jobs, which will begin within the next two weeks.
   
   select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where ja.starting_date>sysdate and ja.starting_date+ja.number_of_days<sysdate+14;
   
8. List all workers who have their wage per hour ten times greater than the wage of their managers.
   
   select a.name "worker name" from worker a,worker b where
   a.manager_id=b.worker_id and a.wage_per_hour>b.wage_per_hour*2;
   
9. List the names of workers who have been assigned the job of molding.
   
   select w.worker_id,w.name from worker w,job j,job_assigned ja where w.worker_id=ja.worker_id and j.job_id=ja.job_id and j.type_of_job='computer';
   
10. What is the total number of days allocated for packaging the goods for all the workers together.
    
    select job_id, avg(number_of_days) from job_assigned group by job_id;
    
11. Which workers receive higher than average wage per hour.
    
    select * from worker where wage_per_hour>(select avg(wage_per_hour) from worker);
    
12. Display details of workers who are working on more than one job.
    
    select worker_id, count(job_id) from job_assigned group by worker_id having count(job_id)>1;
    
13. Which workers having specialization in polishing start their job in December?
    
    select w.worker_id , name from worker w, job_assigned ja where w.specialise d_in='computer' and to_char(ja.starting_date,'mon')='dec';
    
14. Display details of workers who are specialized in the same field as that of Mr. Cacophonix or have a wage per hour more than any of the workers.
    
    select * from worker where wage_per_hour>(select MAX(wage_per_hour) from worker) or specialised_in=(select specialised_in from worker where name='pankaj');

QUESTION 5

Question 5
DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)

CREATE TABLE

Distributor

create table distributor

(

      dno number(10) primary key,

      dname varchar2(20),

      daddress varchar2(30),

      dphone number(10)

);

Item

create table  item

(

      itemno number(10) primary key,

      itemname varchar2(10),

      color varchar2(10),

      weight number(10)

);

Dist_Item

create table dist_item

(

      dno number(10) references distributor(dno),

      itemno number(10) references item(itemno),

      qty number(10),

      primary key(dno,itemno)

);

INSERT

-- INSERT INTO DESTRIBUTOR

insert into distributor values(001,'geeta','mumbai',9995556663,'pankaj');

insert into distributor values(002,'jigee','pune',9995556663,'monica');

insert into distributor values(003,'parul','banglore',9995556663,'shweta');

insert into distributor values(004,'purohit','vijaywada',9956157815,'tyagi');

insert into distributor values(006,'pranaohsiva','madinagar',9545158844,'sharad');

insert into distributor values(005,'lohnghat','chandigarh',8545264588,'rahul');

insert into distributor values(007,'sheela','ambala',8881556478,'praveen');

insert into distributor values(008,'sumit','mehasana','','amar');

-- INSERT INTO ITEM

insert into item values(01,'keyboar','black',70);

insert into item values(02,'mouse','white',20);

insert into item values(03,'monitor-T','white',500);

insert into item values(05,'printer','yellow',750);

insert into item values(04,'monitor-C','green',1500);

insert into item values(06,'cabinate','black',200);

insert into item values(07,'Mboard','white',75);

insert into item values(11,'speaker','red',10);

insert into item values(09,'scanner','yellow',50);

insert into item values(08,'ups','yellow',1500);

insert into item values(10,'mboard-j','red',200);

-- INSERT INTO DIST_ITEM

insert into dist_item values(1,5,20);

insert into dist_item values(2,2,80);

insert into dist_item values(1,3,59);

insert into dist_item values(3,2,90);

insert into dist_item values(3,5,08);

insert into dist_item values(3,4,75);

insert into dist_item values(1,2,100);

      insert into dist_item values(2,2,24);

1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.
   
   alter table distributor add contact_person varchar2(10) not null;
   
2. Create a view LONDON_DIST on DIST_ITEM which contains only those records where distributors are from London. Make sure that this condition is checked for every DML against this view.
   
   create view pune_dist as select di.dno,di.itemno,di.qty from distributor d,dist_item di where d.daddress='pune' and d.dno=di.dno;
   
3. Display details of all those items that have never been supplied.
   
   select * from item where itemno not in(select itemno from dist_item group by itemno);
   
4. Delete all those items that have been supplied only once.
   
   delete from dist_item where itemno in(select itemno from dist_item group by itemno having count(itemno)=1)
   
5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
   
   select dno, dname from distributor where dname like '%a%e%' or dname like '%e%a%';
   
6. Count the number of items having the same colour but not having weight between 20 and 100.
   
   select count(color),color from item where weight  <20 or  weight>100 group by color;
   
7. Display all those distributors who have supplied more than 1000 parts of the same type.
   
   select dno,itemno,sum(qty) from dist_item group by dno,itemno having sum(qty)>=100;
   
8. Display the average weight of items of same colour provided at least three items have that colour.
   
   select color, avg(weight) "AVG WEIGHT", count(color) "TOTAL NO" from item group by color having count(color)>=3;
   
9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after the fourth character.
   
   select dname,instr(dname,'oh') “POSITION” from distributor where instr(dname,'oh',5)<>0;
   
10. Count the number of distributors who have a phone connection and are supplying item number ‘I100’.
    
    select count(d.dno) from distributor d, dist_item di where d.dno=di.dno and di.itemno=5 and d.dphone is not NULL
11. Create a view on the tables in such a way that the view contains the distributor name, item name and the quantity supplied.
    
    select * from view1;
    
12. List the name, address and phone number of distributors who have the same three digits in their number as ‘Mr. Talkative’.
    
    select dname,dphone,daddress from distributor where substr(dphone,1,3) in
    (select substr(dphone,1,3) from distributor where dname='jigee') and dname!='jigee'
    
13. List all distributor names who supply either item I1 or I7 and the quantity supplied is more than 100.
    
    select d.dno , d.dname from distributor d where dno in(select dno from dist_item where (itemno=2 or itemno=3) and qty>=50);
    
14. Display the data of the top three heaviest ITEMS.
    
    select *  from
    
    (
       select itemname, row_number()  over (order by weight desc) rank  from item
                )where rank<=3;

QUESTION 4

Question 4
Screen(screen_id,location ,seating_cap)
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must be less than date_of_closure.

CREATE TABLE

Screen

create table screen

(

screen_id varchar2(3) primary key,

location varchar2(3) not null check(location in('FF','SF','TF')),

          seating_cap number(3) not null check(seating_cap>0),

constraint sid check(screen_id like ('S%')),

);

Movie

Create table movie

(

movie_id varchar2(3) primary key,

          movie_name varchar2(20) unique,

date_of_release date not null

);

Current1

create table current1

(

screen_id varchar2(3) references SCREEN(screen_id),

          movie_id varchar2(6) references MOVIE(movie_id),

          date_of_arrival date not null,

          date_of_closure date not null,

          constraint dtc check(date_of_arrival<date_of_closure)

);

Solve the following queries based on the above schema:

1. Get the name of movie which has run the longest in the multiplex so far.
   
   select a.movie_name from movie a,current1 b  where (date_of_closure - date_of_arrival) =(select max(date_of_closure - date_of_arrival) from current1) and a.movie_id=b.movie_id;
   
2. Get the average duration of a movie on screen number ‘S4’.
   
   select screen_id, avg(date_of_closure-date_of_arrival) avg_duration from current1 where screen_id='S3' group by screen_id;
   
3. Get the details of movie that closed on date 24-november-2004.
   
   select a.movie_id, a.movie_name,a.date_of_release from current1 b, movie a where date_of_closure='27-Oct-10' and a.movie_id=b.movie_id;
   
   
   
4. Movie ‘star wars III ‘ was released in the 7th week of 2005. Find out the date of its release considering that a movie releases only on Friday.
   
   select * from movie where TO_CHAR(date_of_release,'ww')=3 and movie_name=’hulchul’;
   
5. Get the full outer join of the relations screen and current.
   
   select * from screen a, current1 b where a.screen_id(+)=b.screen_id(+);

IVAN BAYROSS PROGRAMS (SQL)

TABLES [DOWNLOAD QUESTION]

[Creating Tables]


create table client_master
(
client_no varchar(6) primary key check(client_no like 'C%'),
name varchar(20) NOT NULL,
address1 varchar(30),
address2 varchar(30),
city varchar(15),
state varchar(15),
pincode number(6),
bal_due number(10,2)
);

create table product_master
(
product_no varchar(6) primary key check(product_no like 'P%'),
description varchar(5) NOT NULL,
profit_percent number(5,2) NOT NULL,
unit_measure varchar(10) NOT NULL,
qty_on_hand number(8) NOT NULL,
recorder_lvl number(8) NOT NULL,
sell_price number(8,2) NOT NULL check(sell_price<>0),
cost_price number(8,2) NOT NULL check(cost_price<>0)
);
alter table product_master modify description varchar(20);

create table salesman_master
(
salesman_no varchar(6) primary key check(salesman_no like 'S%'),
salesman_name varchar(20) NOT NULL,
address1 varchar(30) NOT NULL,
address2 varchar(30),
city varchar(20),
pincode varchar(6),
state varchar(20),
sal_amt number(8,2) NOT NULL check(sal_amt<>0),
tgt_to_get number(6,2) NOT NULL check(tgt_to_get<>0),
ytd_sales number(6,2) NOT NULL,
remarks varchar(60)
);
create table sales_order
(
s_order_no varchar(6) primary key check(s_order_no like 'O%'),
s_order_date date,
client_no varchar(6) references client_master(client_no),
dely_addr varchar(25),
salesman_no varchar(6) references salesman_master(salesman_no),
dely_type char(1) check(dely_type in ('P','F','D')),
billed_yn char(1),
dely_date date,
order_status varchar(10) check(order_status in('IP','F','B','C')),
check(dely_date>s_order_date)
);

create table sales_order_details
(
s_order_no varchar(6) references sales_order(s_order_no),
product_no varchar(6) references product_master(product_no),
qty_ordered number(8),
qty_disp number(8),
product_rate number(10,2)
);

create table challan_header
(
challan_no varchar(6) primary key check(challan_no like 'CH%'),
s_order_no varchar(6) references sales_order(s_order_no),
challan_date date not null,
billed_yn char(1)
);

create table challan_details
(
challan_no references challan_header(challan_no),
product_no references product_master(product_no),
qty_disp number(8)
);

[Inserting Records]


insert into client_master values('C00001','Ivan Bayross','','','Bombay','Maharashtra',400054,15000);
insert into client_master values('C00002','Vandana Saitwal','','','Madras','Tamil Nadu',780001,0);
insert into client_master values('C00003','Pramada Jaguste','','','Bombay','Maharashtra',400057,5000);
insert into client_master values('C00004','Basu Navindgi','','','Bombay','Maharashtra',400056,0);
insert into client_master values('C00005','Ravi Sreedharan','','','Delhi','',100001,2000);
insert into client_master values('C00006','Rukmini','','','Bombay','Maharashtra',400050,0);

insert into product_master values('P00001','1.44 Floppies',5,'Piece',100,20,525,500);
insert into product_master values('P03453','Monitors',6,'Piece',10,3,12000,11280);
insert into product_master values('P06734','Mouse',5,'Piece',20,5,1050,1000);
insert into product_master values('P07865','1.22 Floppies',5,'Piece',100,20,525,500);
insert into product_master values('P07868','Keyboards',2,'Piece',10,3,3150,3050);
insert into product_master values('P07885','CD Drive',2.5,'Piece',10,3,5250,5100);
insert into product_master values('P07965','540 HDD',4,'Piece',10,3,8400,8000);
insert into product_master values('P07975','1.44 Drive',5,'Piece',10,3,1050,1000);
insert into product_master values('P08865','1.22 Drive',5,'Piece',2,3,1050,1000);


insert into salesman_master values('S00001','Kiran','A/14','Worli','Bombay',400002,'MAH',3000,100,50,'Good');
insert into salesman_master values('S00002','Manish','65','Nariman','Bombay',400001,'MAH',3000,200,100,'Good');
insert into salesman_master values('S00003','Ravi','P-7','Bandra','Bombay',400032,'MAH',3000,200,100,'Good');
insert into salesman_master values('S00004','Ashish','A/5','Juhu','Bombay',400044,'MAH',3000,200,150,'Good');

insert into sales_order values('O19001','12-jan-1996','C00001','','S00001','F','N','20-jan-1996','IP');
insert into sales_order values('O19002','25-jan-1996','C00002','','S00002','P','N','27-jan-1996','C');
insert into sales_order values('O46865','18-feb-1996','C00003','','S00003','F','Y','20-feb-1996','F');
insert into sales_order values('O19003','03-apr-1996','C00001','','S00001','F','Y','07-apr-1996','F');
insert into sales_order values('O46866','20-may-1996','C00004','','S00002','P','N','22-may-1996','C');
insert into sales_order values('O10008','24-may-1996','C00005','','S00004','F','N','26-may-1996','IP');

insert into sales_order_details values('O19001','P00001',4,4,525);
insert into sales_order_details values('O19001','P07965',2,1,8400);
insert into sales_order_details values('O19001','P07885',2,1,5250);
insert into sales_order_details values('O19002','P00001',10,0,525);
insert into sales_order_details values('O46865','P07868',3,3,3150);
insert into sales_order_details values('O46865','P07885',3,1,5250);
insert into sales_order_details values('O46865','P00001',10,10,525);
insert into sales_order_details values('O46865','P03453',4,4,1050);
insert into sales_order_details values('O19003','P03453',2,2,1050);
insert into sales_order_details values('O19003','P06734',1,1,12000);
insert into sales_order_details values('O46866','P07965',1,0,8400);
insert into sales_order_details values('O46866','P07975',1,0,1050);
insert into sales_order_details values('O10008','P00001',10,5,525);
insert into sales_order_details values('O10008','P07975',5,3,1050);


insert into challan_header values('CH9001','O19001','12-dec-1995','Y');
insert into challan_header values('CH6865','O46865','12-nov-1995','Y');
insert into challan_header values('CH3965','O10008','12-oct-1995','Y');


insert into challan_details values('CH9001','P00001',4);
insert into challan_details values('CH9001','P07965',1);
insert into challan_details values('CH9001','P07885',1);
insert into challan_details values('CH6865','P07868',3);
insert into challan_details values('CH6865','P03453',4);
insert into challan_details values('CH6865','P00001',10);
insert into challan_details values('CH3965','P00001',5);
insert into challan_details values('CH3965','P07975',2);



Perform the following queries on the basis of these tables.

• Single Table Retrival.
• Set functions and concatenation.
• Having and Group By.
• Joins and correlation.
• Nested Queries.
• Query Using Date.
• Table Updations.