In this article, you find the following:
Copy these commands from here and past them into SQL*Plus to run them.
Creating tables and inserting data
Log on to Oracle and type the following commands at SQL prompt in SQL*Plus to create the requried tables. The following
script not only creates the tables, it also inserts some data into the tables.
CREATE TABLE PUBLISHERS
(
PUBID NUMBER(3) CONSTRAINT PUBLISHERS_PK PRIMARY KEY,
PNAME VARCHAR2(30),
EMAIL VARCHAR2(50) CONSTRAINT PUBLISHERS_EMAIL_U UNIQUE,
PHONE VARCHAR2(30)
);
CREATE TABLE SUBJECTS
(
SUBID VARCHAR2(5) CONSTRAINT SUBJECTS_PK PRIMARY KEY,
SNAME VARCHAR2(30)
);
CREATE TABLE AUTHORS
(
AUID NUMBER(5) CONSTRAINT AUTHORS_PK PRIMARY KEY,
ANAME VARCHAR2(30),
EMAIL VARCHAR2(50) CONSTRAINT AUTHORS_EMAIL_U UNIQUE,
PHONE VARCHAR2(30)
);
CREATE TABLE TITLES
(
TITLEID NUMBER(5) CONSTRAINT TITLES_PK PRIMARY KEY,
TITLE VARCHAR2(30),
PUBID NUMBER(3) CONSTRAINT TITLES_PUBID_FK REFERENCES PUBLISHERS(PUBID),
SUBID VARCHAR2(5) CONSTRAINT TITLES_SUBID_FK REFERENCES SUBJECTS(SUBID),
PUBDATE DATE,
COVER CHAR(1) CONSTRAINT TITLES_COVER_CHK CHECK ( COVER IN ('P','H','p','h')),
PRICE NUMBER(4)
);
CREATE TABLE TITLEAUTHORS
(
TITLEID NUMBER(5) CONSTRAINT TITLESAUTHORS_TITLEID_FK REFERENCES TITLES(TITLEID),
AUID NUMBER(5) CONSTRAINT TITLESAUTHORS_AUTHID_FK REFERENCES AUTHORS(AUID),
IMPORTANCE NUMBER(2),
CONSTRAINT TITLESAUTHORS_PK PRIMARY KEY(TITLEID,AUID)
);
INSERT INTO SUBJECTS VALUES ('ORA','ORACLE DATABASE 10g');
INSERT INTO SUBJECTS VALUES ('JAVA','JAVA LANGUAGE');
INSERT INTO SUBJECTS VALUES ('JEE','JAVA ENTEPRISE EDITION');
INSERT INTO SUBJECTS VALUES ('VB','VISUAL BASIC.NET');
INSERT INTO SUBJECTS VALUES ('ASP','ASP.NET');
INSERT INTO PUBLISHERS VALUES (1,'WILLEY','WDT@VSNL.NET','91-11-23260877');
INSERT INTO PUBLISHERS VALUES (2,'WROX','INFO@WROX.COM',NULL);
INSERT INTO PUBLISHERS VALUES (3,'TATA MCGRAW-HILL','FEEDBACK@TATAMCGRAWHILL.COM','91-11-33333322');
INSERT INTO PUBLISHERS VALUES (4,'TECHMEDIA','BOOKS@TECHMEDIA.COM','91-11-33257660');
INSERT INTO AUTHORS VALUES (101, 'HERBERT SCHILD','HERBERT@YAHOO.COM',NULL);
INSERT INTO AUTHORS VALUES (102, 'JAMES GOODWILL','GOODWILL@HOTMAIL.COM',NULL);
INSERT INTO AUTHORS VALUES (103, 'DAVAID HUNTER','HUNTER@HOTMAIL.COM',NULL);
INSERT INTO AUTHORS VALUES (104, 'STEPHEN WALTHER','WALTHER@GMAIL.COM',NULL);
INSERT INTO AUTHORS VALUES (105, 'KEVIN LONEY','LONEY@ORACLE.COM',NULL);
INSERT INTO AUTHORS VALUES (106, 'ED. ROMANS','ROMANS@THESERVERSIDE.COM',NULL);
INSERT INTO TITLES VALUES (1001,'ASP.NET UNLEASHED',4,'ASP','12-APR-02','P',540);
INSERT INTO TITLES VALUES (1002,'ORACLE10G COMP. REF.',3,'ORA','1-MAY-05','P',575);
INSERT INTO TITLES VALUES (1003,'MASTERING EJB',1,'JEE','3-FEB-05','P',475);
INSERT INTO TITLES VALUES (1004,'JAVA COMP. REF',3,'JAVA','3-APR-05','P',499);
INSERT INTO TITLES VALUES (1005,'PRO. VB.NET',2,'VB','15-JUN-05','P',450);
INSERT INTO TITLEAUTHORS VALUES (1001,104,1);
INSERT INTO TITLEAUTHORS VALUES (1002,105,1);
INSERT INTO TITLEAUTHORS VALUES (1003,106,1);
INSERT INTO TITLEAUTHORS VALUES (1004,101,1);
INSERT INTO TITLEAUTHORS VALUES (1005,103,1);
INSERT INTO TITLEAUTHORS VALUES (1005,102,2);
Queries with answers
Display number of books published in the current year
select count(*) from titles where to_char(sysdate,'yyyy') = to_char(pubdate,'yyyy');
Display publisher ID and maximum price for books on subject ORACLE.
select pubid, max(price)
from title
where subid = 'ora'
group by pubid;
Display Subject ID and max price.
select subid, max(price)
from titles
group by subid;
Display Subject ID and number of books of that subject with price more than 500.
select subid, count(*)
from titles
where price > 500
group by subid;
Display Subject ID, Publisher ID and average price.
select subid,pubid, avg(price)
from titles
group by subid,pubid;
Display month and number of books published for each month in the current year.
select to_char(pubdate,'mm'), count(*) nobooks
from titles
where to_char(sysdate,'yyyy') = to_char(pubdate,'yyyy')
group by to_char(pubdate,'mm');
Display Title ID and number of authors of the title.
select titleid, count(*)
from titleauthors
group by titleid;
Display how many titles author 103 wrote as the lead author.
select count(*)
from titleauthors
where importance = 1 and auid = 103;
Display Publisher name and number of books published.
select pname, count(*)
from publishers p, titles t
where p.pubid = t.pubid
group by pname;
Display the publisher who published more than 3 books on JAVA
select pubid
from titles
where subid = 'JAVA'
group by pubid
having count(*) > 3;
Display Publisher name and number of books published.
select pname, count(*)
from publishers p, titles t
where p.pubid = t.pubid
group by pname;
Display the author who wrote more than 3 titles in last two years.
select auid
from titleauthors ta, titles t
where months_between(sysdate,pubdate) <= 24
group by auid
having count(*) > 3;
Display the subjects for which we have the average price in the range 300 to 400
select subid from titles
group by subid
having avg(price) between 300 and 400;
Display the subject having maximum price and minimum price
select subid,max(price), min(price)
from titles
group by subid;
Display author name and maximum price of all the books written by the authors
select aname, max(price)
from authors a, titleauthors ta, titles t
where t.titleid = ta.titleid and a.auid = ta.auid
group by aname;
Display names of the authors who wrote a book with price <500
select aname from authors
where auid in (select auid from titleauthors where titleid in
(select titleid from titles where price < 500) );
Display authors who wrote a book on ORACLE.
select * from authors
where auid in (select auid from titleauthors where titleid in
(select titleid from titles where subid = 'ora') );
Display subjects in which minimum price <300 maximum price >600
select * from subjects
where subid in
( select subid
from titles
group by subid
having min(price) < 300)
and subid in
( select subid
from titles
group by subid
having max(price) > 600);
Display the author who has written the costliest book.
select * from authors
where auid =
( select auid from titleauthors
where titleid =
(select titleid from titles
where price =
(select max(price) from titles)
)
);
Display the author who wrote a book with price >500.
select * from authors
where auid =
( select auid from titleauthors
where titleid =
(select titleid from titles where price > 500)
);
Display the subject for which we have more than 3 books published in the current year
select * from subjects
where subid in
(select subid
from titles
where to_char(pubdate,'yyyy') = to_char(sysdate,'yyyy')
group by subid
having count(*) > 3);
Display author name and title of the books for which author is the lead author
select aname,title
from titles t, authors a, titleauthors ta
where t.titleid = ta.titleid and a.auid = ta.auid
and importance = 1;
Display author and number of books by the author from publisher WROX
select auid,count(*)
from titles
where pubid =
(select pubid from publishers where pname = 'WROX');
Display year and number of books published where the book is written by more than one author
select to_char(pubdate,'yyyy') , count(*)
from titles
where titleid in
(select titleid
from titleauthors
group by titleid
having count(*) > 1)
group by to_chat(pubdate,'yyyy');
Display the subjects where the difference between minimum price and maximum price is>200
select * from subjects
where subid in
( select subid
from titles
group by subid
having max(price) - min(price) > 200);
Change the price of title 1003 to the price of the title most recently published
update titles set price =
( select price from titles
where pubdate = ( select max(pubdate) from titles )
where titleid = 1003;
Display the authors who wrote a book in the previous year but not in the current year
select * from authors
where auid in
( select auid
from titleauthors ta, titles t
where ta.titleid = t.titleid and to_chat(pubdate,'yyyy') = to_char(sysdate,'yyyy') - 1
)
and auid not in
( select auid
from titleauthors ta, titles t
where ta.titleid = t.titleid and to_chat(pubdate,'yyyy') = to_char(sysdate,'yyyy')
);
Display the publishers who published a book on ORACLE but no book on JAVA.
select * from publishers
where pubid in
( select pubid
from titles
where subid = 'ORA');
)
and pubid not in
( select pubid
from titles
where subid = 'JAVA')
);
Display year the highest price by taking into account books that are written by more than one author.
select to_char(pubdate,'yyyy') , max(price)
from titles
where titleid in
(select titleid
from titleauthors
group by titleid
having count(*) > 1)
group by to_chat(pubdate,'yyyy');
Change the Publisher date of title 1003 to least publishing date of any book of that subject in the same year
update titles t set pubdate =
( select min(pubdate)
from titles where subid = t.subid
and to_chat(pubdate,'yyyy') = to_chat(t.pubdate,'yyyy')
)
where titleid = 1003;
Queries with answers for 17-Feb-2006 Batch
Display employees where name ends with n and salary is less than 5000.
select * from emp where ename like '%n' and sal<5000
Display name,salary and department of the employees where name of the employee contains 'a' twice.
select ename,sal,job from emp where ename like '%a%a%';
Display the employees where the commission is present and also calculate the commission on salary and display it.
select ename,sal*comm comm from emp where comm is not null;
Display the employees who joined after 1st Jan 2000.
select * from emp where hire_date>'01-Jan-2000';
Display the employees with salary more than 5000 and job is Clerk.
select * from emp where sal>5000 and job='clerk'
Increase the salaries of the department number 10 by 10%.
update emp set sal = sal * 1.1 where deptno = 10;
Change the date of joining for employee 7369 to '05-Mar-2001'.
update emp set hiredate = '05-mar-2001' where empno = 7369;
Display employee name, salary rounded to 100's,hra rounded to 10's (hra is 25% of the salary).
select ename, round(sal,-2) salary, round(salary * 0.25) hra from emp;
Display the employees who joined in the last 6 months.
select * from emp where months_between(sysdate,hiredate) <= 6;
Display employee name,hiredate and the date on which the first salary was paid.(The salary is paid on 1st of every month)
select ename,hiredate, last_day(hiredate) + 1 salarydate from emp;
Display employee name and first saturday since he joined and salary for all employees who have more than 3 years of experience.
select ename, next_day(hiredate,'sat') , sal from emp where months_between(sysdate,hiredate) > 36;
Display names of the employees if employee name contains more than 10 characters and also contains letters s and i(in any case)
select ename from emp where length(ename)>=10 and upper(ename) like '%S%I%'
Display employee number and the position at which letter 'i' appears in employee name for employees where name contains 'i'.
select empno, instr(ename,'i') from emp where ename like '%i%';
Replace the leading zeroes in employee id with *.
select lpad(ltrim(empno,'0'),5,'*') from emp;
Display the last three characters of employee name.
select substr(ename, length(ename)-2) from emp;
Display employee name,department no and job by concatenating deptno and job with , seperating them.
select ename, deptno || ',' || job from emp;
Display employee name and year in which employee joined.
select ename, to_char(hiredate,'yyyy') from emp;
Display employee name and hiredate in the format hrs,day,month.
select ename, to_char(hiredate,'hh24, day, month') from emp;
Change the hire date of employee 200 to same date but time should be 10:30 a:m.
update emp set hiredate=to_date(to_char(hiredate,'dd-mm-yy')||'10:30','dd-mm-yyhh24:MI') where empno =200;
Display employees who joined in the current year.
select last_name from employees where to_char(sysdate,'yyyy')=to_char(hire_date,'yyyy')
Modify price column of the title to include a constraint which ensures price>=0
alter table titles modify ( price number(5) constraint titles_price_chk check ( price >= 0 ) );
Include character s for checkconstraint of cover.
alter table titles drop constraint titles_cover_chk;
alter table titles modify (cover char(1) constraint titles_cover_chk check( cover in ('s','h','p','S','H','P')) );
Make title not null.
alter table titles modify ( title varchar(30) constraint titles_title_nn not null);
Display author who wrote more than 3 books.
select auid from titleauthors
group by auid
having count(*) > 3;
Display author who wrote more than one book as the lead author.
select auid from titleauthors where importance=1 group by auid having count(*)>1.
Display subid,year,number of books published.
select subid,to_char(pubdate,'yyyy'), count(*)
from titles
group by subid, to_char(pubdate,'yyyy');
Display publisherID,subjectID and maximum price.
select pubid,subid,max(price)
from titles
group by pubid,subid;
Display subject and the date of most recent title.
select subid,max(pubdate) from titles group by subid.
Display year in which we published more than 2 books for subject Oracle.
select to_char(pubdate,'yyyy')
from titles
where subid ='ora'
group by to_char(pubdate,'yyyy')
having count(*) > 2;
Display publishers who published more than 2 books in any single subject.
select pubid
from titles
group by pubid,subid
having count(*) > 2;
Display month in which more than 2 books are published with cover of type hardbound.
select to_char(pubdate,'month-yyyy')
from titles
where cover = 'h'
group by to_char(pubdate,'month-yyyy')
having count(*) > 2;
Display title,subname for all titles of publisher 1.
select title,sname
from titles t, subjects s
where t.subid = s.subid and pubid = 1;
Display title,year of publishing and publisher name for titles published in last 3 years.
select title,to_char(pubdate,'yyyy'),pname
from titles t, publishers p
where t.pubid = p.pubid and months_between(sysdate,pubdate) <= 36;
Display auid,year and number of titles written in each year.
select auid, to_char(pubdate,'yyyy'), count(*)
from titles t, titleauthors ta
where t.titleid = ta.titleid
group by auid,to_char(pubdate,'yyyy');
Display pubname,title of all titles written by author 103.
select pname,title from titles t, publishers p
where t.pubid = p.pubid and titleid in
( select titleid from titleauthors where auid = 103);
Display subname and average price of the books.
select sname,avg(price)
from subjects s, titles t
where s.subid = t.subid
group by sname;
Display the difference between maximum price and minimum price for all books.
select max(price) - min(price) from titles;
Display title and author name for only leading authors.
select title, aname
from titles t, authors a, titleauthors ta
where t.titleid = ta.titleid and a.auid = ta.auid and importance = 1;
Display authors who wrote a title in the current year.
select aname
from authors
where auid in ( select auid from titleauthors where titleid in
( select titleid from titles where to_char(sysdate,'yyyy') = to_char(pubdate,'yyyy') )
);
Display titles published by the publisher whose name starts with 'w'.
select title from titles
where pubid in
( select pubid from publishers where pname like 'W%');
Display publishers who published hardbound books.
select pname
from publishers
where pubid in
( select pubid from titles where cover = 'h');
Display the subjects for which we have more than 3 books published in any single year.
select sname from subjects where subid in(select subid from titles group by subid having count(*)>3);
Display the titles that are written by any author who wrote more than 2 books.
select title
from titles
where titleid in
( select titleid from titleauthors
where auid in
( select auid
from titleauthors
group by auid
having count(*) > 1)
);
Display the publishers for whom more than 3 authors worked.
select * from publishers
where pubid in
(select pubid from titles t,titleauthors ta where t.titleid=ta.titleid group by pubid having count(distinct auid)>3)
Display the author who worked for a book with price more than 500.
select aname
from authors
where auid in
( select auid from tilesauthors
where titleid in
(select titleid from titles where price > 500)
);
Change price of the title 1003 to average price of the titles.
update titles set price = ( select avg(price) from titles) where titleid = 1003;
Insert row into titleauthors where titleid is the maximum titleid,authorid is that of author 'stevens' with importance 1.
insert into titleauthors values ( (select max(titleid) from titles),
(select auid from authors where aname = 'Stevens'),1);
Delete authors who have not written any title.
delete from authors
where auid not in ( select auid from titleauthors);
Queries with answers for 21-Mar-2006 Batch
Display jobs that have minimum salary is set to null.
select employee_id,job_id,salary from employees
where salary is null;
Display jobs where the title contains letter 's' twice
select job_id,employee_id from employees where job_id like '%S%S%';
Display the employees in the ascending order of jobs and descending order of salary
select * from employees order by job_id,salary desc;
Change the salary of employees with id=205 to 10000
update employees set salary=10000 where employee_id=205;
Insert a new department with department_id 300, name customersupport and remaining values as null
insert into departments values(300,'cutsomer-support',null,null);
Display the employeeid,hiredate and Experience of all the employees to the date
select employee_id,hire_date,trunc(sysdate-hire_date)/365 Experience from employees;
Display the employees who joined in the month of march of any year
select employee_id,first_name,hire_date from employees where to_char(hire_date,'mon')='mar';
Display the name,hiredate and the date on which first salary paid to the employee (assume-salary paid on the last day of the month)
select first_name,hire_date,last_day(hire_date) FSPAIDON from employees;
Display employees where the FirstName is bigger than the LastName
select employee_id,first_name,last_name from employees
where length(first_name)>length(last_name);
Display employee name and experience in years where the name contains letter 'I' and 'T' in any order
select first_name,trunc(sysdate-hire_date)Experience from employees
where upper(first_name) like '%I%' and upper(first_name) like '%T%';
Change Hiredate of employee 205 to 5th-jan-2006 at 10:30 a.m
update employees set hire_date=to_date('5-JAN-06 10:30','DD-MON-YY HH24:MI')
where employee_id=205;
Display the title and authors for the title
select title,aname from titles t, authors a,titleauthors ta where t.titleid=ta.titleid and a.auid=ta.auid;
Display title and subject name for books published in current year
select title,sname from titles t,subjects s
where t.subid=s.subid and to_char(t.pubdate,'yyyy')=to_char(sysdate,'yyyy');
Display author name no.of titles written by author as lead author
select aname,count(title) from titles t,authors a,
titleauthors ta where ta.auid=a.auid and
ta.titleid=t.titleid and importance=1 group by(aname);
Display title that has no author details
select title from titles t,titleauthors ta,authors a
where t.titleid=ta.titleid and ta.auid=a.auid(+);
Display publishers,year and no.of books published
select pname,to_char(pubdate,'yyyy'),count(*)
from titles t,publishers p where t.pubid=p.pubid
group by pname,to_char(pubdate,'yyyy');
Display subjects and no.of books published in the current year.
select sname,to_char(pubdate,'yyyy'), count(*)
from titles t,subjects s where s.subid=t.subid
group by sname, to_char(pubdate,'yyyy');
Display maximum price of all books.
select title, max(price) from titles group by (title);
Display month name and books with highest price of all books published in that month
select to_char(pubdate,'month') Month,max(price) HighPrice from titles
group by to_char(pubdate,'month');
Display subjects for which we have more than 3 books which are hard bound.
select sname,count(*) from subjects s,titles t
where t.subid=s.subid and cover='P'
group by sname having count(*)>3;
Display the titleid for which we have got more than 2 authors
select titleid from titleauthors ta,authors a
where ta.auid=a.auid
group by titleid having count(*)>2;
Display titles published by publisher 'wrox' in the year 2004
select title from titles t,publishers p
where t.pubid=p.pubid
and pname='wrox' and to_char(pubdate,'yyyy')=2005;
Change email address of publisher 'wrox' from wroxus@yahoo.com to wroxus@usa.net
update publishers set email='wroxus@usa.net'
where pname='wrox';
Display title,subjects,publisher name,lead author name
select title,sname,pname,aname LeadAuthor from titles t,subjects s,authors a,publishers p,titleauthors ta
where t.subid=s.subid and t.pubid=p.pubid and t.titleid=ta.titleid and ta.auid=a.auid and importance=1;
Display authorname for authors who wrote more than 1 title in a single year
select aname from authors a,titleauthors ta,titles t
where t.titleid=ta.titleid and ta.auid=a.auid group by(aname),to_char(pubdate,'yyyy')
having count(*)>1;
Display the titles published by 'wrox'
select title from titles where pubid in
(select pubid from publishers where pname='wrox');
Display authors who wrote a book on oracle.
select aname from authors a,titleauthors ta,titles t,subjects s
where a.auid=ta.auid and ta.titleid=t.titleid and t.subid=s.subid and sname
like '%or%';
Display publishers who published books in the current year
select pname from publishers where pubid in(select pubid from titles where to_char(pubdate,'yyyy')=to_char(sysdate,'yyyy'));
Display titles that are published by 'wrox'
select title from titles where pubid in (select pubid from publishers
where pname='wrox');
Display subject name,no.of books published where the price is >500
select sname,count(title) from subjects s,titles t
where t.subid=s.subid and price>500 groupby(sname);
Display pubid,no.of books published where the title is written by more than one author
select pubid,count(*) from titles where
titleid in(select titleid from titleauthors group by titleid
having count(*)>1)
group by(pubid);
Display the subjects for which no books were published in the current year
select * from subjects where subid not in(select subid from titles where
to_char(sysdate,'yyyy')=to_char(pubdate,'yyyy'));
Display publishers who published a book in 2003 but not in 2004
select pname from publishers p,titles t where
t.pubid=p.pubid and to_char(pubdate,'yyyy')=2003 and to_char(pubdate,'yyyy')!=2004;
Change the price of title-1003 to average price of "oracle books".
update titles set price=(select avg(price)from subjects s,titles t
where s.subid=t.subid and sname like '%ora%')
where titleid=1003;
Insert a row into titleauthors for title java.comp.ref and author 'Herbert Schild'
insert into titleauthors values(
(select titleid from titles where title = 'Java Comp. Ref'),
(select auid from authors where aname = 'Herbert Schild'),1);
Display publishers worked with 'Herbert Schild'
select pname from publishers p,titles t,titleauthors ta, authors a
where p.pubid=t.pubid and t.titleid=ta.titleid and ta.auid=a.auid and aname='herbert schildt' ;
Display authors who have written at least one book as lead author.
select aname from authors where auid in
(select auid from titleauthors where importance=1);
Display authors name who have written any title with author 'HERBERT SCHILD'
select aname from authors where auid in
(select auid from titleauthors where titleid in
(select titleid from titleauthors where auid=
(select auid from authors where aname='HERBERT SCHILD')
)
);
Display publishers and no.of books published by them.
select pname, count(*)
from publishers p, titles t
where p.pubid = t.pubid
group by pname;
Display the subject for which we have more than 3 books written by more than one author.
select subid from titles where titleid in
(select titleid from titleauthors group by titleid having count(*)>1)
group by subid having count(*)>1;
Display no.of books published by each publisher on each subject
select pubid,subid,count(*) from titles group by(pubid,subid) order by pubid,subid;
Display subjects for which no book was published in the current year
select *from subjects where subid not in (select subid from titles
where to_char(sysdate,'yyyy')=to_char(pubdate,'yyyy'));
Display the 2nd highest priced book from bookstroe
select title,price from titles main
where 1= (select count(*) from titles where price > main.price);