ALTER TABLE Address DROP PRIMARY KEY;
ALTER TABLE Address ADD PRIMARY KEY(addressId);

ALTER TABLE People ADD PRIMARY KEY (peopleID, firstName);

ALTER TABLE People
ADD CONSTRAINT fk_people_address
FOREIGN KEY (addressId)
REFERENCES Address(addressId);

-- drop unique constraint use ADD UNIQUE to make it unique
ALTER TABLE Pets
DROP INDEX species;

-- change name of coloumn
ALTER TABLE Pets
CHANGE species animalType VARCHAR(10);

-- change datatype
ALTER TABLE Address
MODIFY city VARCHAR(100);

ALTER TABLE Pet ADD PRIMARY KEY (petId);

ALTER TABLE People ADD email VARCHAR(50);

INSERT INTO Address VALUES
('A1', '710, street no 4', 'San Fransisco', '94016'),
('A2', '12 Lincoln Park', 'San Fransisco', '94015'),
('A3', '511, street no 3', 'Boston', '02108'),
('A4', '1 Market Street', 'California', '90213');

SELECT * FROM Pets;
SELECT species FROM Pets;

SELECT petId, name, species
FROM Pets
WHERE species = 'Poodle';


SELECT * FROM PETS
WHERE species IN ('Poodle', 'Lebrador');


SELECT * FROM PETS
WHERE name LIKE 'T%' AND ownerID = '2';

SELECT * FROM People ORDER BY name ASC;

SELECT name, species FROM Pets
ORDER BY species ASC, name DESC;