“Authors” quiz with answers

/* AuthorsWithMoreThanOneBook.sql */


/* 1 create the table */
DROP TABLE IF EXISTS #authors;
CREATE TABLE #authors 
(auth_name VARCHAR(50),
 book_title VARCHAR(50));


/* 2 add some data */
INSERT INTO #authors VALUES
('Isaac Asimov', 'iRobot'),
('Ben Bova', 'The towers of Titan'),
('Ben Bova', 'A long way back');


/* 3 confirm the table contents */
SELECT * FROM #authors;


/* 4 answer using the HAVING clause */
SELECT auth_name
FROM #authors
GROUP BY auth_name
HAVING COUNT(*) > 1;


/* 5 answer using a WINDOW function */
SELECT auth_name
FROM
(SELECT auth_name,
    ROW_NUMBER() OVER (PARTITION BY auth_name 
    ORDER BY (SELECT book_title)) AS book_count
 FROM #authors
) AS qty
WHERE book_count > 1;