Jul 30, 2020 2:52 pm
Write a query that selects the item name and the name of its seller for each item that belongs to a seller with a rating greater than 4. The query should return the name of the item as the first column and name of the seller as the second column.
Use the below script to create the required table.
CREATE TABLE sellers ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, rating INTEGER NOT NULL ); CREATE TABLE items ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, sellerId INTEGER REFERENCES sellers(id) ); INSERT INTO sellers(id, name, rating) VALUES(1, 'Roger', 3); INSERT INTO sellers(id, name, rating) VALUES(2, 'Penny', 5); INSERT INTO items(id, name, sellerId) VALUES(1, 'Notebook', 2); INSERT INTO items(id, name, sellerId) VALUES(2, 'Stapler', 1); INSERT INTO items(id, name, sellerId) VALUES(3, 'Pencil', 2);
1 Reply
Jul 30, 2020 2:58 pm
You can use the below query to get the expected results.
SELECT sellers.name as sellers, items.name as items FROM items INNER JOIN sellers ON items.sellerId=sellers.Id and sellers.rating>4;