CREATE DATABASE cgs3066;
USE cgs3066;
SHOW TABLES;

CREATE TABLE fruits (
	id INT UNSIGNED AUTO_INCREMENT,
	name VARCHAR(30),
	color VARCHAR(30),
	PRIMARY KEY (id)
);

DESCRIBE fruits;

INSERT INTO fruits ( name, color ) VALUES ( 'banana', 'yellow' );
INSERT INTO fruits ( name, color ) VALUES ( 'tangerine', 'orange' );
INSERT INTO fruits ( name, color ) VALUES ( 'plum', 'purple' );
INSERT INTO fruits ( name, color ) VALUES ( 'lime', 'green' );
INSERT INTO fruits ( name, color ) VALUES ( 'apple', 'red' );
INSERT INTO fruits ( name, color ) VALUES ( 'mango', 'green' );

SELECT name, color FROM fruits;
SELECT name, color FROM fruits WHERE name = 'plum';
SELECT name, color FROM fruits WHERE id >= 2;
SELECT name, color FROM fruits WHERE id >= 2 AND color = 'green';
SELECT name, color FROM fruits WHERE color LIKE 'red';
SELECT name, color FROM fruits WHERE color LIKE '%re%';
SELECT COUNT(*) FROM fruits WHERE color LIKE '%re%';
SELECT * FROM fruits ORDER BY name;
SELECT * FROM fruits LIMIT 3;
SELECT * FROM fruits LIMIT 3 OFFSET 2;
SELECT * FROM fruits LIMIT 3, 2;

UPDATE fruits SET name = 'grapefruit', color = 'yellow' WHERE id = 2; 

DELETE FROM fruits WHERE id = 2;