CREATE DATABASE bookstore;
use bookstore;
CREATE TABLE books (
	book_id INT AUTO_INCREMENT PRIMARY KEY,
	title varchar(255) NOT NULL, 
	author varchar(255) NOT NULL,
	price FLOAT,
    publish_date date NOT NULL
);

INSERT INTO books (title, author, price, publish_date)
VALUES
('Python', 'Eric Matthes', 45.00,'2000-01-01'),
('java', 'Robert C. Martin', 60.00,'2001-01-01'),
('c++', 'Andrew Hunt', 80.00,'2002-01-01'),
('qt', 'Joshua Bloch', 120.00,'2003-01-01'),
('c', 'Erich Gamma', 250.00,'2004-01-01');

UPDATE books
SET price = price * 0.1
WHERE title = 'c++';

DELETE FROM books WHERE publish_date>'2003-01-01';

SELECT * FROM books WHERE price > 45.00;

SELECT * FROM books ORDER BY price DESC;

SELECT author,COUNT(*)AS book_c,sum(price) AS zong_price FROM books GROUP BY author;