Need Help with few queries please

[ballouta]

consider these four relations
        BookAuthor (book, author, earnings)
        BookReference (book, referenceBook, times)
        BookReview (book, reviewer, score)
        BookPublish (book, year, price, num)
 

*an author can review his book

 

1) I need a sql query that finds all the authors who reviewed more than two books written by 'ballouta' USING aggregators

2) I need a sql query that finds all the authors who reviewed more thna two books written by 'ballouta' with subqueries

3) Find all authors who have written exactly one book and reviewed more than one book

4) Find all reviewers who have reviewed everybook by 'ballouta' using Except'

 

thank you so much

[Barand]

You should at least attempt to do your own assignments. I bet you wish you'd been listening now. What have you tried so far?

[ballouta]

For this first question I have

 

 

Select author, count(reviewers)

From BookAuthor BA, BookReview BR

where BA.autgor = 'ballouta'

Group by author

Having count(reviewers) > 2

[Barand]

You have not specified the join condition between BookAuthor and BookReviewer (ie BA.col_x = BR.coly_y)

 

It more efficient to use an explicit join syntax.

 

EG

... 
FROM BookAuthor BA
    INNER JOIN BookReview BR ON BA.col_x = BR.col_y
 ...

[ballouta]

so for question 1, would this query be correcy?

 

Select author, count(reviewers)
From BookAuthor BA
INNER JOIN BookReview BR ON
BA.book = BR.author
where BA.author = 'Charles Dickens'
Group by author
Having count(reviewers) > 2
 

Edited February 24, 2014 by ballouta

[Barand]

No.

 

"A Tale of Two Cities" is NOT equal to "Charles Dickens" so why try to join on them

[ballouta]

this?

 

Select author, count(reviewers)
From BookAuthor BA
INNER JOIN BookReview BR ON
BA.book = BR.book
where BA.author = 'Charles Dickens'
Group by author
Having count(reviewers) > 2

[Barand]

That should give a count of each author's books that have been reviewed, but I don't think that was the question asked.

[ballouta]

Select reviewer, count(reviewer)
From BookAuthor BA
INNER JOIN BookReview BR ON
BA.book = BR.book
where BA.author = 'Charles Dickens'
Group by reviewer
Having count(reviewer) > 2
 

this?

[Barand]

Have you tested any of these on your database? You have that advantage over me

[ballouta]

unfor this class didn't provide any kind of DB access to test our code, this is why it is difficult to wite queries for me as a beginner

[ballouta]

May someone help me with Question no 2 please

so I have the correct query for question # 1

which is

Select reviewer, count(reviewer)
From BookAuthor BA
INNER JOIN BookReview BR ON
BA.book = BR.book
where BA.author = 'Charles Dickens'
Group by reviewer
Having count(reviewer) > 2

I need to convert it to subqueries in the where clause, thank you

[Barand]

I set up some test data.

 

Running this query

SELECT reviewer, COUNT(*)
FROM BookAuthor ba
INNER JOIN BookReview br ON ba.book = br.book
WHERE ba.author = 'ggg'
GROUP BY reviewer
HAVING COUNT(*) >= 2;

+----------+----------+
| reviewer | COUNT(*) |
+----------+----------+
| bbb      |        3 |
| ddd      |        2 |
| eee      |        2 |
| m        |        2 |
| o        |        2 |
| p        |        2 |
+----------+----------+

In my data m, o and p are reviewers but not authors and the question asked for authors so I then used a subquery to select only those reviewers that are also authors

SELECT reviewer, COUNT(*)
FROM BookAuthor ba
INNER JOIN BookReview br ON ba.book = br.book
INNER JOIN (
    SELECT DISTINCT author
    FROM bookauthor
    ) as ba2
    ON br.reviewer = ba2.author
WHERE ba.author = 'ggg'
GROUP BY reviewer
HAVING COUNT(*) >= 2;

+----------+----------+
| reviewer | COUNT(*) |
+----------+----------+
| bbb      |        3 |
| ddd      |        2 |
| eee      |        2 |
+----------+----------+

I could also have used

SELECT reviewer, COUNT(*)
FROM BookAuthor ba
INNER JOIN BookReview br ON ba.book = br.book
WHERE ba.author = 'ggg'
AND br.reviewer IN (SELECT author FROM bookauthor)
GROUP BY reviewer
HAVING COUNT(*) >= 2;

My data

 

 

CREATE TABLE `bookauthor` (
  `book` int(11) NOT NULL,
  `title` varchar(20) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `earnings` int(11) DEFAULT NULL,
  PRIMARY KEY (`book`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `bookauthor` WRITE;
/*!40000 ALTER TABLE `bookauthor` DISABLE KEYS */;
INSERT INTO `bookauthor` VALUES (1,'Book 1','hhh',0),(2,'Book 2','bbb',0),(3,'Book 3','eee',0),(4,'Book 4','hhh',0),(5,'Book 5','ggg',0),(6,'Book 6','aaa',0),(7,'Book 7','eee',0),(8,'Book 8','eee',0),(9,'Book 9','ggg',0),(10,'Book 10','ddd',0),(11,'Book 11','eee',0),(12,'Book 12','ddd',0),(13,'Book 13','ccc',0),(14,'Book 14','ggg',0),(15,'Book 15','ddd',0),(16,'Book 16','ggg',0),(17,'Book 17','ccc',0),(18,'Book 18','fff',0),(19,'Book 19','ccc',0),(20,'Book 20','ccc',0),(21,'Book 21','bbb',0),(22,'Book 22','ggg',0),(23,'Book 23','eee',0),(24,'Book 24','hhh',0),(25,'Book 25','fff',0),(26,'Book 26','ddd',0),(27,'Book 27','fff',0),(28,'Book 28','bbb',0),(29,'Book 29','aaa',0),(30,'Book 30','ggg',0),(31,'Book 31','aaa',0),(32,'Book 32','ggg',0),(33,'Book 33','eee',0),(34,'Book 34','ddd',0),(35,'Book 35','aaa',0),(36,'Book 36','ggg',0),(37,'Book 37','ccc',0),(38,'Book 38','bbb',0),(39,'Book 39','ggg',0),(40,'Book 40','bbb',0),(41,'Book 41','aaa',0),(42,'Book 42','bbb',0),(43,'Book 43','eee',0),(44,'Book 44','aaa',0),(45,'Book 45','ggg',0),(46,'Book 46','bbb',0),(47,'Book 47','fff',0),(48,'Book 48','aaa',0),(49,'Book 49','fff',0),(50,'Book 50','bbb',0);
/*!40000 ALTER TABLE `bookauthor` ENABLE KEYS */;
UNLOCK TABLES;

CREATE TABLE `bookreview` (
  `reviewid` int(11) NOT NULL,
  `book` int(11) DEFAULT NULL,
  `reviewer` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`reviewid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `bookreview` WRITE;
/*!40000 ALTER TABLE `bookreview` DISABLE KEYS */;
INSERT INTO `bookreview` VALUES (1,47,'fff',10),(2,29,'k',48),(3,5,'m',79),(4,9,'hhh',60),(5,46,'q',89),(6,43,'ddd',25),(7,2,'o',51),(8,10,'m',55),(9,6,'r',19),(10,12,'eee',38),(11,26,'ccc',92),(12,34,'ddd',41),(13,2,'bbb',62),(14,2,'n',21),(15,18,'ccc',71),(16,25,'ccc',74),(17,50,'fff',67),(18,35,'r',92),(19,21,'hhh',63),(20,39,'ggg',3),(21,36,'bbb',10),(22,24,'bbb',45),(23,13,'aaa',71),(24,6,'l',90),(25,33,'aaa',65),(26,13,'l',77),(27,28,'ggg',93),(28,26,'hhh',16),(29,31,'p',41),(30,12,'ccc',84),(31,34,'ccc',99),(32,41,'ddd',4),(33,6,'o',1),(34,9,'p',18),(35,22,'eee',61),(36,40,'bbb',94),(37,33,'k',98),(38,19,'l',18),(39,18,'o',79),(40,9,'eee',10),(41,9,'ddd',20),(42,3,'q',33),(43,26,'bbb',50),(44,24,'ggg',36),(45,45,'aaa',6),(46,38,'ggg',33),(47,24,'ccc',40),(48,38,'p',64),(49,24,'hhh',18),(50,5,'bbb',27),(51,10,'o',81),(52,24,'eee',17),(53,30,'m',19),(54,17,'k',97),(55,17,'bbb',64),(56,34,'q',11),(57,29,'bbb',56),(58,34,'k',49),(59,10,'k',62),(60,6,'q',57),(61,25,'l',90),(62,16,'bbb',2),(63,14,'o',35),(64,3,'r',77),(65,12,'fff',78),(66,41,'ccc',12),(67,49,'m',55),(68,16,'ddd',34),(69,1,'aaa',45),(70,42,'aaa',60),(71,24,'k',14),(72,46,'m',26),(73,37,'o',48),(74,5,'p',78),(75,7,'q',45),(76,20,'n',80),(77,37,'m',77),(78,8,'hhh',71),(79,13,'o',16),(80,37,'m',73),(81,20,'r',92),(82,13,'k',97),(83,10,'hhh',31),(84,17,'eee',20),(85,49,'fff',43),(86,32,'o',48),(87,6,'ddd',4),(88,33,'fff',64),(89,12,'n',63),(90,26,'k',21),(91,17,'o',25),(92,50,'ddd',91),(93,21,'o',88),(94,16,'ccc',4),(95,7,'ccc',96),(96,18,'ddd',22),(97,33,'n',5),(98,15,'r',70),(99,15,'m',60),(100,27,'ddd',5);
/*!40000 ALTER TABLE `bookreview` ENABLE KEYS */;
UNLOCK TABLES;

 

 

[ballouta]

Thank you, let me study your answers I really appreciate the help.

[ballouta]

I just finished studying your queries, thanks again

I am thinking of how to wrie the same query without aggregators but with subqueries in the where clause

 

I have these idea, If it is true, I am not sure yet hot to translate it to a query

so the same query above without aggregator

SELECT reviewer, COUNT(*)
FROM BookAuthor ba
INNER JOIN BookReview br ON ba.book = br.book
WHERE ba.author = 'ggg'
AND br.reviewer IN (SELECT author FROM bookauthor)
GROUP BY reviewer
HAVING COUNT(*) >= 2;

I say:

after joining the two tables on the same criteria above

get a Distinct list of reviewers from the tuples (that are the result of the inner join)

then the "inner join tuples" Except (difference) the "distinct list"

will tell us who had reviewed 2 books or mores

 

what do you think?