Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Create table author (
- author_id int primary key,
- name varchar(50),
- city varchar(50),
- country varchar(50)
- );
- Create table publisher (
- publisher_id int primary key,
- name varchar(50),
- city varchar(50),
- country varchar(50)
- );
- Create table category (
- category_id int primary key,
- description varchar(100)
- );
- Create table catalog (
- book_id int primary key,
- title varchar(100),
- author_id int,
- publisher_id int,
- category_id int,
- year int,
- price int,
- foreign key (author_id) references author(author_id),
- foreign key (publisher_id) references publisher(publisher_id),
- foreign key (category_id) references category(category_id)
- );
- Create table order_details (
- order_no int,
- book_id int,
- quantity int,
- primary key (order_no, book_id),
- foreign key (book_id) references catalog(book_id)
- );
- insert into author (author_id, name, city, country) values
- (1, 'Author A', 'City 1', 'Country 1'),
- (2, 'Author B', 'City 2', 'Country 2'),
- (3, 'Author C', 'City 3', 'Country 3'),
- (4, 'Author D', 'City 4', 'Country 4'),
- (5, 'Author E', 'City 5', 'Country 5');
- insert into publisher (publisher_id, name, city, country) values
- (1, 'Publisher A', 'City 1', 'Country 1'),
- (2, 'Publisher B', 'City 2', 'Country 2'),
- (3, 'Publisher C', 'City 3', 'Country 3'),
- (4, 'Publisher D', 'City 4', 'Country 4'),
- (5, 'Publisher E', 'City 5', 'Country 5');
- insert into category (category_id, description) values
- (1, 'Fiction'),(2, 'Non-Fiction'),(3, 'Science Fiction'),
- (4, 'Mystery'),(5, 'Thriller');
- insert into catalog (book_id, title, author_id, publisher_id, category_id, year, price) values
- (1, 'Book 1', 1, 1, 1, 2005, 100),(2, 'Book 2', 2, 1, 2, 2010, 120),
- (3, 'Book 3', 3, 3, 3, 2015, 150),(4, 'Book 4', 4, 4, 4, 2020, 200),
- (5, 'Book 5', 5, 5, 5, 2025, 250);
- insert into order_details (order_no, book_id, quantity)
- values (101, 1, 2),(102, 2, 3),
- (103, 3, 1),(104, 4, 4),(105, 5, 2);
- select a.author_id, a.name, a.city, a.country, count(c.book_id) as book_count from author a
- join catalog c on a.author_id = c.author_id where c.year > 2000
- group by a.author_id, a.name, a.city, a.country having count(c.book_id) >= 2;
- select a.author_id, a.name, a.city, a.country, c.title, od.quantity from order_details od
- join catalog c on od.book_id = c.book_id join author a on c.author_id = a.author_id
- where od.quantity = (select max(quantity) from order_details);
- update catalog set price = price * 1.10 where
- publisher_id = (select publisher_id from publisher where name = 'Publisher E');
- select * from catalog;
- Output:
- +-----------+----------+--------+-----------+--------+----------+
- | author_id | name | city | country | title | quantity |
- +-----------+----------+--------+-----------+--------+----------+
- | 4 | Author D | City 4 | Country 4 | Book 4 | 4 |
- +-----------+----------+--------+-----------+--------+----------+
- +---------+--------+-----------+--------------+-------------+------+-------+
- | book_id | title | author_id | publisher_id | category_id | year | price |
- +---------+--------+-----------+--------------+-------------+------+-------+
- | 1 | Book 1 | 1 | 1 | 1 | 2005 | 100 |
- | 2 | Book 2 | 2 | 1 | 2 | 2010 | 120 |
- | 3 | Book 3 | 3 | 3 | 3 | 2015 | 150 |
- | 4 | Book 4 | 4 | 4 | 4 | 2020 | 200 |
- | 5 | Book 5 | 5 | 5 | 5 | 2025 | 275 |
- +---------+--------+-----------+--------------+-------------+------+-------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
