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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- a survey of spreadsheet users
- csss 508 intro to r
- chapter 5 the structure and function of
- discriminant function analysis with three or more groups
- an introduction to functional groups in organic chemistry
- nci protocol
- solution assignment
- 670 notes department of mathematics
- micros 8700 hospitality management system
- chapter 08 dna structure and function
Related searches
- no solution infinite solution calculator
- no solution one solution infinite solutions calculator
- one solution no solution infinite solution calculator
- solution no solution calculator
- one solution no solution infinitely many
- no solution infinite solution worksheet
- one solution no solution infinite calculator
- no solution and infinite solution calculator
- solution no solution infinite solution
- no solution infinite solution examples
- one solution no solution infinite solution
- one solution two solution no solution