Solution Assignment



Solution Assignment #1

Parts a

Create statements:

SQL> Create table author

(authornum char(3) primary key,

authorlast char (18),

authorfirst char(10)); 2 3 4

Table created.

SQL> create table book

(bookcode char(4) primary key,

title char(40),

publish_code char(2),

type char(4),

price number (5,2),

papaerback char(3)); 2 3 4 5 6 7

Table created.

SQL> create table wrote

(bookcode char(4) references book,

authornum char (3) references author,

sequence number(2),

primary key (bookcode,authornum)); 2 3 4 5

Table created.

SQL> desc author;

Name Null? Type

----------------------------------------- -------- ----------------------------

AUTHORNUM NOT NULL CHAR(3)

AUTHORLAST CHAR(18)

AUTHORFIRST CHAR(10)

SQL> select * from author;

AUT AUTHORLAST AUTHORFIRS

--- ------------------ ----------

1 Morrison Toni

2 Solotaroff Paul

3 Vintage Vernor

4 Francis Dick

5 Straub Peter

6 King Stephen

7 Prattt Philip

8 Chase Truddi

9 Collins Bradley

10 Heller Joseph

11 Wills Gary

AUT AUTHORLAST AUTHORFIRS

--- ------------------ ----------

12 Hofstadter Douglas R.

13 Lee Harper

14 Ambrose Stephen E.

15 Rowling J.K.

16 Salinger J.D.

17 Heaney Seamus

18 Camus Albert

19 Collins. Jr. Bradley

20 Steinbeck John

21 Castleman Riva

22 Owen Barbara

AUT AUTHORLAST AUTHORFIRS

--- ------------------ ----------

23 O' Rourke Randy

24 Kidder Tracy

25 Schleining Lon

25 rows selected.

SQL> desc book;

Name Null? Type

----------------------------------------- -------- ----------------------------

BOOKCODE NOT NULL CHAR(4)

TITLE CHAR(40)

PUBLISH_CODE CHAR(2)

TYPE CHAR(4)

PRICE NUMBER(5,2)

PAPERBACK CHAR(3)

SQL> SELECT * FROM BOOK;

BOOK TITLE PU TYPE PRICE PAP

---- ---------------------------------------- -- ---- ---------- --- --

180 A Deepness in the Sky TB SFI 7.19 Yes

189 Magic Terror FA HOR 7.99 Yes

200 The Stranger VB FIC 8 Yes

378 Venice SS ART 24.5 No

079X Second Wind PU MYS 24.95 No

808 The Edge JP MYS 6.99 Yes

1351 Dreamcatcher:A Novel SC HOR 19.6 No

1382 Treasure Chests TA ART 24.46 No

138X Beloved PL FIC 12.95 Yes

2226 Harry Potter and the Prisoner of Azkaban ST SFI 13.96 No

2281 Van Gogh and Gauguin WP ART 21 No

BOOK TITLE PU TYPE PRICE PAP PU

---- ---------------------------------------- -- ---- ---------- --- --

2766 Of Mice and Men PE FIC 6.95 Yes

2908 Electric Light FS POE 14 No

3350 Group: Six People in Search of Life BP PSY 10.4 Yes

3743 Nine Stories LB FIC 5.99 Yes

3906 The Soul of a new machine BY SCI 11.16 Yes

5163 Travels and Charley PE TRA 7.95 Yes

5790 Catch-22 SC FIC 12 Yes

6128 Jazz PL FIC 12.95 Yes

6328 Band of Brothers TO HIS 9.6 Yes

669X A Guide To SQL CT CMP 37.95 Yes

6908 Frany and Zooey LB FIC 5.99 Yes

BOOK TITLE PU TYPE PRICE PAP PU

---- ---------------------------------------- -- ---- ---------- --- --

7405 East of Eden PE FIC 12.95 Yes

7443 Harry Potter and the Goblet of Fire ST SFI 18.16 No

7559 The Fall VB FIC 8 Yes

8092 Godel, Escher, Bach BA PHI 14 Yes

8720 When Rabbit Howls JP PSY 6.29 Yes

9611 Black House RH HOR 18.81 No

9627 Songs of Solomon PL FIC 14 Yes

9701 The Grapes of Wrath PE FIC 13 Yes

9882 Slay Ride JP MYS 6.99 Yes

9883 The Catch in the Rye LB FIC 5.99 Yes

9931 To Kill a Mocking Bird HC FIC 18 No

33 rows selected.

SQL> desc wrote;

Name Null? Type

----------------------------------------- -------- ----------------------------

BOOKCODE NOT NULL CHAR(4)

AUTHORNUM NOT NULL CHAR(3)

SEQUENCE NUMBER(2)

SQL> select * from wrote;

BOOK AUT SEQUENCE

---- --- ----------

180 3 1

189 5 1

200 18 1

378 11 1

079X 4 1

808 4 1

1351 6 1

1382 23 2

1382 25 1

138X 1 1

2226 15 1

BOOK AUT SEQUENCE

---- --- ----------

2281 9 2

2281 19 1

2766 20 1

2908 17 1

3350 2 1

3743 16 1

3906 24 1

5163 20 1

5790 10 1

6128 1 1

6328 14 1

BOOK AUT SEQUENCE

---- --- ----------

6908 16 1

7405 20 1

7443 15 1

7559 18 1

8092 12 1

8720 8 1

9611 5 2

9611 6 1

9627 1 1

9701 20 1

9882 4 1

BOOK AUT SEQUENCE

---- --- ----------

9883 16 1

9931 13 1

669X 7 1

36 rows selected.

Part c

Q1.What are the titles of all psychology (PSY) books?

SQL> select Title from book

2 where upper(type)='PSY';

TITLE

----------------------------------------

Group: Six People in Search of Life

When Rabbit Howls

Q2. Give the count of different book types?

SQL> select count(*), type

2 from book

3 group by type;

COUNT(*) TYPE

---------- ----

3 ART

1 CMP

13 FIC

1 HIS

3 HOR

3 MYS

1 PHI

1 POE

2 PSY

1 SCI

3 SFI

COUNT(*) TYPE

---------- ----

1 TRA

12 rows selected.

Q3.What is the average price of fictional (FIC) books?

SQL> select avg(price)

2 from book

3 where upper(type) = 'FIC';

AVG(PRICE)

----------

10.5207692

If you want book type ALSO,

SQL> SELECT TYPE, AVG(PRICE)

2 FROM BOOK

3 GROUP BY TYPE

4 HAVING UPPER(TYPE)='FIC';

TYPE AVG(PRICE)

---- ----------

FIC 10.5207692

Q4.What are the AuthorNum of authors that have NOT written a book?

SQL> select authornum

from author

minus

select distinct authornum

from wrote; 2 3 4 5

AUT

---

21

22

If you want the names of authors, then use following query:

SQL> select authorlast, authorfirst

from author

where authornum in (select authornum

from author

minus

select distinct authornum

from wrote); 2 3 4 5 6 7

AUTHORLAST AUTHORFIRS

------------------ ----------

Castleman Riva

Owen Barbara

Q5.How many fictional books are published by publisher LB?

SQL> select count(*)

from book

where upper(type)='FIC' and upper(publish_code)='LB'; 2 3

COUNT(*)

----------

3

How can we get type and publisher_code also?

SQL> select count(*), type, publish_code

from book

where upper(type)='FIC' and upper(publish_code)='LB'; 2 3

select count(*), type, publish_code

*

ERROR at line 1:

ORA-00937: not a single-group group function

Need to group by BOTH fields

SQL> select count(*), type, publish_code

from book

where upper(type)='FIC' and upper(publish_code)='LB'

group by publish_code, type; 2 3 4

COUNT(*) TYPE PU

---------- ---- --

3 FIC LB

Q6.What are the names of authors that have written fiction or trade books?

SQL> select authorfirst||authorlast, authornum from author

where authornum in (

select distinct authornum from wrote

where bookcode in (select bookcode from book

where upper(type) in ('FIC','TRA')));

2 3 4 5

AUTHORFIRST||AUTHORLAST AUT

---------------------------- ---

Toni Morrison 1

Joseph Heller 10

Harper Lee 13

J.D. Salinger 16

Albert Camus 18

John Steinbeck 20

6 rows selected.

Q7Give the names (first and last) of authors that have no more than one r (i.e., 0 or 1)in their last name?

SQL> SQL> select authorfirst|| ' ' ||authorlast as "authorName (FIRST/LAST)"

from author

where upper(authorlast) NOT like '%R%R%'; 2 3

authorName (FIRST/LAST)

-----------------------------

Paul Solotaroff

Vernor Vintage

Dick Francis

Peter Straub

Stephen King

Philip Prattt

Truddi Chase

Bradley Collins

Joseph Heller

Gary Wills

Douglas R. Hofstadter

authorName (FIRST/LAST)

-----------------------------

Harper Lee

Stephen E. Ambrose

J.K. Rowling

J.D. Salinger

Seamus Heaney

Albert Camus

Bradley Collins. Jr.

John Steinbeck

Riva Castleman

Barbara Owen

Tracy Kidder

authorName (FIRST/LAST)

-----------------------------

Lon Schleining

23 rows selected.

Q8 Give the titles of books that cost more than $20 or less than $8.

SQL> select title

2 from book

3 where price >20 or price l

1 select authornum, count(*)

2 from wrote

3* group by authornum

SQL> /

AUT COUNT(*)

--- ----------

1 3

10 1

11 1

12 1

13 1

14 1

15 2

16 3

17 1

18 2

19 1

AUT COUNT(*)

--- ----------

2 1

20 4

23 1

24 1

25 1

3 1

4 3

5 2

6 2

7 1

8 1

AUT COUNT(*)

--- ----------

9 1

23 rows selected.

Q10. A new book with book code 9645, title “lost” is to be published by author Jim Stack. Process this request.

(Do not actually save it, i.e., rollback after your output is done). Do NOT add a new author Jim Stewart,

you may get an integrity constraint error, submit the query with the error.

Insert New book

SQL> Insert into book (bookcode,title)

values (9645,'LOST'); 2

1 row created.

SQL> select * from book;

BOOK TITLE PU TYPE PRICE PAP

---- ---------------------------------------- -- ---- ---------- ---

9645 LOST

180 A Deepness in the Sky TB SFI 7.19 Yes

189 Magic Terror FA HOR 7.99 Yes

200 The Stranger VB FIC 8 Yes

378 Venice SS ART 24.5 No

079X Second Wind PU MYS 24.95 No

808 The Edge JP MYS 6.99 Yes

1351 Dreamcatcher:A Novel SC HOR 19.6 No

1382 Treasure Chests TA ART 24.46 No

138X Beloved PL FIC 12.95 Yes

2226 Harry Potter and the Prsioner of Azkaban ST SFI 13.96 No

BOOK TITLE PU TYPE PRICE PAP

---- ---------------------------------------- -- ---- ---------- ---

2281 Van Gogh and Gauguin WP ART 21 No

2766 Of Mice and Men PE FIC 6.95 Yes

2908 Electric Light FS POE 14 No

3350 Group: Six People in Search of Life BP PSY 10.4 Yes

3743 Nine Stories LB FIC 5.99 Yes

3906 The Soul of a new machine BY SCI 11.16 Yes

5163 Travels and Charley PE TRA 7.95 Yes

5790 Catch-22 SC FIC 12 Yes

6128 Jazz PL FIC 12.95 Yes

6328 Band of Brothers TO HIS 9.6 Yes

669X A Guide To SQL CT CMP 37.95 Yes

BOOK TITLE PU TYPE PRICE PAP

---- ---------------------------------------- -- ---- ---------- ---

6908 Frany and Zooey LB FIC 5.99 Yes

7405 East of Eden PE FIC 12.95 Yes

7443 Harry Potter and the Goblet of Fire ST SFI 18.16 No

7559 The Fall VB FIC 8 Yes

8092 Godel, Escher, Bach BA PHI 14 Yes

8720 When Rabbit Howls JP PSY 6.29 Yes

9611 Black House RH HOR 18.81 No

9627 Songs of Solomon PL FIC 14 Yes

9701 The Grapes of Wrath PE FIC 13 Yes

9882 Slay Ride JP MYS 6.99 Yes

9883 The Catch in the Rye LB FIC 5.99 Yes

BOOK TITLE PU TYPE PRICE PAP

---- ---------------------------------------- -- ---- ---------- ---

9931 To Kill a Mocking Bird HC FIC 18 No

34 rows selected.

SQL> insert into wrote values (9645,null,null);

insert into wrote values (9645,null,null)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("AGGARWAL"."WROTE"."AUTHORNUM")

If we make up a value for author number say 26

SQL> insert into wrote values (9645,26,null);

insert into wrote values (9645,26,null)

*

ERROR at line 1:

ORA-02291: integrity constraint (AGGARWAL.SYS_C0035756) violated - parent key

not found

Part d:

An AUTHOR can be part of many WROTE entries but a WROTE entry belongs to only one author

AUTHOR(-----(>WROTE

SIMILARLY A BOOK be part of many WROTE entries but a WROTE entry belongs to only one book

BOOK(-----------(>WROTE

[pic]

Part e: There are any issues here?

Tables do not exhibit any anomalies, since there are no redundant repeating fields

-----------------------

AUTHOR

WROTE

BOOK

WROTE

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download