SQLize | PHPize | SQLtest

--table initialization --staff table initialization CREATE TABLE staff ( Staff_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name VARCHAR(50) NOT NULL, Last_Name VARCHAR(50) NOT NULL, Staff_Position VARCHAR(50) NOT NULL, Staff_Salary INTEGER NOT NULL ); --species table initialization CREATE TABLE species ( Species_ID INTEGER PRIMARY KEY AUTOINCREMENT, Common_Name VARCHAR(50) NOT NULL, Scientific_Name VARCHAR(75) NOT NULL, Diet VARCHAR(25) NOT NULL ); --animals table initialization CREATE TABLE animals ( Animal_ID INTEGER PRIMARY KEY AUTOINCREMENT, Animal_Name VARCHAR(50) NOT NULL, Animal_Age INTEGER, Species_ID INTEGER NOT NULL, FOREIGN KEY(Species_ID) REFERENCES species (Species_ID) ); --animal_informattion table initialization --contains data from previous animals table CREATE TABLE animal_information ( Animal_ID INTEGER NOT NULL, Habitat_ID INTEGER NOT NULL, Staff_ID INTEGER NOT NULL, FOREIGN KEY(Animal_ID) REFERENCES animals (Animal_ID), FOREIGN KEY(Habitat_ID) REFERENCES habitats (Habitat_ID), FOREIGN KEY(Staff_ID) REFERENCES staff (Staff_ID) ); --climate table initialization --contains data from previous habitats table CREATE TABLE climate ( Climate_ID INTEGER PRIMARY KEY AUTOINCREMENT, Climate_Name VARCHAR(25) NOT NULL, Humidity_Percentage INTEGER NOT NULL, Temperature_F INTEGER NOT NULL ); --habitats table initialization CREATE TABLE habitats ( Habitat_ID INTEGER PRIMARY KEY AUTOINCREMENT, Climate_ID INTEGER NOT NULL, Animal_Capacity INTEGER NOT NULL, Budget INTEGER NOT NULL, FOREIGN KEY(Climate_ID) REFERENCES climate (Climate_ID) ); --accessories table initialization CREATE TABLE accessories ( Accessory_ID INTEGER PRIMARY KEY AUTOINCREMENT, Accessory_Name VARCHAR(50) NOT NULL, Accessory_Type VARCHAR(50) NOT NULL ); --habitat_accessories table initialization --bridge entity between habitats and accessories CREATE TABLE habitat_accessories ( Habitat_ID INTEGER NOT NULL, Accessory_ID INTEGER NOT NULL, FOREIGN KEY(Habitat_ID) REFERENCES habitats (Habitat_ID), FOREIGN KEY(Accessory_ID) REFERENCES accessories (Accessory_ID) ); --sample records --staff table population INSERT INTO staff (First_Name, Last_Name, Staff_Position, Staff_Salary) VALUES ("Jaime", "Fritz", "Executive Director", 250000), ("Rhianne", "Hopkins", "Director of Conservation Education", 100000), ("Bronwen", "Bate", "Director of Marketing and Development", 130000), ("Preston", "Middleton", "Director of Guest Experience", 125000), ("Marcie", "Horn", "Financial Manager", 130000), ("Elvis", "Alford", "Curator", 85000), ("Hoorain", "Hunter", "Lead Zookeeper", 65000), ("Monica", "Hopkins", "Zookeeper", 50000), ("Matt", "Mcphee", "Zookeeper", 49000), ("Enya", "Dillon", "Zookeeper", 40000), ("Tyreece", "Frye", "Zookeeper", 55000), ("Arya", "Field", "Zookeeper", 60000), ("Sommer", "Wheatley", "Zookeeper", 45000); --species table population INSERT INTO species (Common_Name, Scientific_Name, Diet) VALUES ("Plains Zebra", "Equus quagga", "Herbivore"), ("Roosevelt Elk", "Cervus canadensis", "Omnivore"), ("Bobcat", "Lynx rufus", "Carnivore"), ("Common Raven", "Corvus corax", "Omnivore"), ("Golden Eagle", "Aquila chrysaetos", "Carnivore"), ("Canada Lynx", "Lynx canadensis", "Carnivore"), ("Mountain Lion", "Felis concolor", "Carnivore"), ("Snowy Owl", "Bubo scandiacus", "Carnivore"), ("Bald Eagle", "Haliaeetus leucocephalus", "Carnivore"), ("River Otter", "Lontra canadensis", "Carnivore"), ("North American Black Bear", "Ursus americanus", "Omnivore"), ("Gray Wolf", "Canis lupus", "Carnivore"), ("Wolverine", "Gulo gulo", "Carnivore"), ("Polar Bear","Ursus maritimus", "Carnivore"), ("Harp Seal", "Pagophilus groenlandicus", "Carnivore"); --animal table population INSERT INTO animals (Animal_Name, Animal_Age, Species_ID) VALUES ("Clarice", 10, 1), ("Bekki", 10, 2), ("Lars", 5, 3), ("Keenan", 20, 4), ("Chaminda", 11, 5), ("Fidda", 9, 6), ("Ceara", 12, 7), ("Andrew", 7, 8), ("Ozzie", 15, 9), ("Branda", 4, 10), ("Cicely", 18, 11), ("Sue", 6, 12), ("Roy", 7, 13), ("Martha",9,14), ("Bert",7,14), ("Clarice",4,15), ("Penny",3,15); --animal_information table population INSERT INTO animal_information VALUES (1,1,9), (2,2,9), (3,2,7), (4,2,10), (5,3,10), (6,2,7), (7,3,11), (8,4,8), (9,2,13), (10,5,12), (11,2,12), (12,2,11), (13,3,13), (14,6,7), (15,6,7), (16,4,8), (17,4,8); --climate table population INSERT INTO climate (Climate_Name, Humidity_Percentage, Temperature_F) VALUES ("Savannah", 20, 75), ("Forest", 60, 50), ("Mountain", 66, 45), ("Tundra", 67, 27), ("Wetland", 65, 70); --habitats table population INSERT INTO habitats (Climate_ID, Animal_Capacity, Budget) VALUES (1, 5, 57000), (2, 10, 100000), (3, 7, 75000), (4, 9, 70000), (5, 5, 40000), (4, 5, 55000); --accessories table population INSERT INTO accessories (Accessory_Name, Accessory_Type) VALUES ("Ball", "Leisure"), ("Incubator", "Health"), ("Heat Lamp", "Health"), ("Heat Lamp", "Health"), ("Bungie", "Leisure"), ("Rope","Leisure"), ("Ball","Leisure"), ("Float","Leisure"), ("Vitamin Block","Health"), ("Vitamin Block","Health"), ("Thermostat","Health"), ("Bungie","Leisure"), ("Ball","Leisure"), ("Float","Leisure"); --habitat_accessories table population INSERT INTO habitat_accessories VALUES (2,1), (5,2), (1,3), (5,4), (1,5), (3,6), (4,7), (4,8), (2,9), (3,10), (1,11), (3,12), (6,13), (6,14); --queries --SELECT ... FROM ... query SELECT DISTINCT Diet FROM species; --SELECT ... FROM ... WHERE query SELECT * FROM staff WHERE Staff_Position LIKE "%Zookeeper"; --SELECT ... FROM ... WHERE ... ORDER BY ... query SELECT Animal_ID, Animal_Name, Animal_Age FROM animals WHERE Animal_Age > 10 ORDER BY Animal_Age ASC; --SELECT ... FROM ... WHERE ... GROUP BY ... query SELECT Climate_ID, SUM(Animal_Capacity), SUM(Budget) FROM habitats WHERE Budget > 50000 GROUP BY Climate_ID; --Query utilizing a subquery SELECT DISTINCT Accessory_Name FROM accessories WHERE Accessory_ID IN (SELECT Accessory_ID FROM habitat_accessories WHERE Habitat_ID = 1); --Query utilizing a JOIN SELECT animals.Animal_ID, animals.Animal_Name, animals.Animal_Age, species.Common_Name, species.Scientific_Name, species.Diet FROM animals LEFT JOIN species ON animals.Species_ID = species.Species_ID;