/*
	Each item in a web shop belongs to a seller. To ensure service quality, each seller has a rating.

	The data are kept in the following two tables:
	TABLE sellers
	  id INTEGER PRIMARY KEY,
	  name VARCHAR(30) NOT NULL,
	  rating INTEGER NOT NULL

	TABLE items
	  id INTEGER PRIMARY KEY,
	  name VARCHAR(30) NOT NULL,
	  sellerId INTEGER REFERENCES sellers(id)

	  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.
*/

--https://[Log in to view URL]
-- create a table
CREATE TABLE sellers (
      id INTEGER PRIMARY KEY,
	  name VARCHAR(30) NOT NULL,
	  rating INTEGER NOT NULL
);


CREATE TABLE items (
     id INTEGER PRIMARY KEY,
	  name VARCHAR(30) NOT NULL,
	  sellerId INTEGER REFERENCES sellers(id)
);



-- insert some values
INSERT INTO sellers VALUES (1, 'Ryan', 15);
INSERT INTO sellers VALUES (2, 'Joanna', 30);
INSERT INTO sellers VALUES (3, 'CatRyan', 20);
INSERT INTO sellers VALUES (4, 'DogJoanna', 34);


INSERT INTO items VALUES (1, 'car',   1);
INSERT INTO items VALUES (2, 'tenis', 2);
INSERT INTO items VALUES (3, 'tv',  3);
INSERT INTO items VALUES (4, 'sofa', 3);
-- fetch some values
SELECT * FROM sellers;
SELECT * FROM items;

select a.name as item, b.name as seller from items a inner join sellers b
on a.sellerid = b.id
where rating > 4;