This question is based on the following data and tables. Create a database named books( make sure you don’t forget to select the database) and run the MySQL queries below to be able to complete the following MySQL Queries. For each MySQL query, screenshot the output and include the MySQL query in your submission PDF. -- Authors Table CREATE TABLE authors ( author_id INT PRIMARY KEY AUTO_INCREMENT, author_name VARCHAR(255) NOT NULL, birth_year INT ); -- Sample Data INSERT INTO authors (author_name, birth_year) VALUES ('J.K. Rowling', 1965), ('George Orwell', 1903), ('Jane Austen', 1775); -- Books Table CREATE TABLE books ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, publication_year INT, author_id INT, FOREIGN KEY (author_id) REFERENCES authors(author_id) ); -- Sample Data INSERT INTO books (title, publication_year, author_id) VALUES ('Harry Potter and the Sorcerer''s Stone', 1997, 1), ('1984', 1949, 2), ('Pride and Prejudice', 1813, 3); a- Write a MySQL query that selects the author with the earliest birth year. b- WriteaMySQLquerythatwillfindthetitlesofbookspublishedaftertheyear1900from the books table. c- Write a MySQL query that utilizes a join to retrieve the names of authors and the titles of their books (if any) from the authors and books tables. d- WriteaMySQLquerythatwillcalculatetheaveragebirthyearforallauthors. e- Write a MySQL query that will count the number of books written by each author. Include the author's name and the count of books. Show authors even if they have written zero books
This question is based on the following data and tables. Create a
-- Authors Table CREATE TABLE authors (
author_id INT PRIMARY KEY AUTO_INCREMENT, author_name VARCHAR(255) NOT NULL, birth_year INT
);
-- Sample Data
INSERT INTO authors (author_name, birth_year) VALUES ('J.K. Rowling', 1965),
('George Orwell', 1903),
('Jane Austen', 1775);
-- Books Table CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, publication_year INT,
author_id INT,
FOREIGN KEY (author_id) REFERENCES authors(author_id) );
-- Sample Data
INSERT INTO books (title, publication_year, author_id) VALUES ('Harry Potter and the Sorcerer''s Stone', 1997, 1),
('1984', 1949, 2),
('Pride and Prejudice', 1813, 3);
-
a- Write a MySQL query that selects the author with the earliest birth year.
-
b- WriteaMySQLquerythatwillfindthetitlesofbookspublishedaftertheyear1900from
the books table.
-
c- Write a MySQL query that utilizes a join to retrieve the names of authors and the titles
of their books (if any) from the authors and books tables.
-
d- WriteaMySQLquerythatwillcalculatetheaveragebirthyearforallauthors.
-
e- Write a MySQL query that will count the number of books written by each author.
Include the author's name and the count of books. Show authors even if they have written zero books.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 1 images
