SQLQuerySafari.sql

-- Table creation
CREATE TABLE Animal (
  Animal_ID CHAR(10) NOT NULL PRIMARY KEY,
  CONSTRAINT check_Animal_ID CHECK (Animal_ID LIKE '%[0-9]%'),
  Species VARCHAR(50) NULL,
  CONSTRAINT check_species CHECK (Species LIKE '%[A-Z]%' AND Species LIKE '%[a-z]%'),
  Name VARCHAR(50) NULL
);

CREATE TABLE Mammal_Predator (
  Mammal_pre_ID CHAR(10) NOT NULL,
  constraint FK__Mammal_Predator__Animal_ID FOREIGN KEY  (Mammal_pre_ID) REFERENCES Animal(Animal_ID),
  meatType VARCHAR(50)
);

CREATE TABLE Mammal_Vegetarian (
  Mammal_veg_ID CHAR(10) NOT NULL,
  constraint FK__Mammal_Vegetarian__Animal_ID FOREIGN KEY (Mammal_veg_ID) REFERENCES Animal(Animal_ID),
  vegType VARCHAR(50)
);

CREATE TABLE Reptiles (
  reptiles_ID CHAR(10) NOT NULL,
  constraint FK__Reptiles__Animal_ID FOREIGN KEY (reptiles_ID) REFERENCES Animal(Animal_ID),
  FoodType VARCHAR(50)
);

CREATE TABLE Attraction (
  attrac_num CHAR(10) NOT NULL PRIMARY KEY,
  CONSTRAINT attrac_num_check CHECK (attrac_num LIKE '%[0-9]%'),  -- checks every char is 0-9 
  name VARCHAR(50) NULL 
);

CREATE TABLE Restaurant (
  attrac_num CHAR(10) NOT NULL primary key,
  MilkOrMeat char(4) not null ,
  constraint FK__Restaurant__Attraction FOREIGN KEY (attrac_num) REFERENCES Attraction(attrac_num),
  CONSTRAINT MilkOrMeat_check CHECK (MilkOrMeat IN ('Milk', 'Meat'))
);

CREATE TABLE Employee (
  First_name VARCHAR(50) NULL,
  Second_name VARCHAR(50) NULL,
  Employee_ID_num CHAR(9) NOT NULL PRIMARY KEY, 
  CONSTRAINT Employee_ID_num CHECK (Employee_ID_num LIKE '%[0-9]%')  -- checks every char is 0-9 
);

CREATE TABLE CareTaker (
  CareTaker_ID_num CHAR(9) NOT NULL,
  constraint FK__CareTaker__Employee_ID_num FOREIGN KEY (CareTaker_ID_num) REFERENCES Employee(Employee_ID_num),
  exper INT DEFAULT 0 NOT NULL, 
  CONSTRAINT Experience_check CHECK (exper BETWEEN 0 AND 100) -- check is making sure the value between 0-100 
);

CREATE TABLE Service (
  service_ID_num CHAR(9) NOT NULL,
 constraint FK__Service__Employee_ID_num FOREIGN KEY (service_ID_num) REFERENCES Employee(Employee_ID_num)
);

CREATE TABLE Customer (
  cust_ID CHAR(10) NOT NULL PRIMARY KEY,
  CONSTRAINT cust_ID CHECK (cust_ID LIKE '%[0-9]%'),
  cust_age INT NULL,
  CONSTRAINT cust_age_check CHECK (cust_age >= 0),
  first_name VARCHAR(50) NULL, 
  last_name VARCHAR(50) NULL
);

create table Watches(
  cust_ID CHAR(10) NOT NULL,
  attrac_num CHAR(10) NOT NULL,
  constraint FK__Watches__Customer_ID FOREIGN KEY (cust_ID) REFERENCES Customer(cust_ID),
  constraint FK__Watches__Attraction FOREIGN KEY (attrac_num) REFERENCES Attraction(attrac_num)
);

CREATE table Feeding (
  which_animal varchar(50) NULL,
  attrac_num CHAR(10) NOT NULL primary key,
  constraint FK__Feeding__Attraction FOREIGN KEY (attrac_num) REFERENCES Attraction(attrac_num)
);

CREATE table Participate (
cust_ID CHAR(10) NOT NULL,
  attrac_num CHAR(10) NOT NULL,
  constraint FK__Participate__Customer_ID FOREIGN KEY (cust_ID) REFERENCES Customer(cust_ID),
  constraint FK__Participate__Attraction FOREIGN KEY (attrac_num) REFERENCES Attraction(attrac_num)
  );

CREATE table Show (
  ages int  DEFAULT 0 NOT NULL,
  attrac_num CHAR(10) NOT NULL primary key,
  constraint FK__Show__Attraction FOREIGN KEY (attrac_num) REFERENCES Attraction(attrac_num)
);

-- Insert data into Employee table
INSERT INTO Employee (Employee_ID_num, First_name, Second_name) VALUES
('000000001', 'Jane', 'Doe'),
('000000002', 'Itay', 'Levi'),
('000000003', 'Taylor', 'Swift');

-- Insert data into CareTaker table
INSERT INTO CareTaker (CareTaker_ID_num, exper) VALUES
('000000001', 2);

-- Insert data into Service table
INSERT INTO Service (service_ID_num) VALUES
('000000002'),
('000000003');

-- Insert data into Customer table
INSERT INTO Customer (cust_ID, cust_age, first_name, last_name) VALUES
('000000005', 70, 'LeBron', 'James'),
('000000004', 22, 'Ravid', 'Plotnik'),
('000000006', 30 ,'Leo', 'Messi'),
('000000007', 2, 'Benny', 'Gantz'),
('000000008', 15, 'Yair', 'Lapid');

-- Insert data into Animal table
INSERT INTO Animal (Animal_ID, Species, Name) VALUES
('0000000001', 'Snake', 'Slinky'),
('0000000002', 'Lizard', 'Lizzy'),
('0000000003', 'Turtle', 'Shelly'),
('0000000004', 'Gecko', 'Gerry'),
('0000000005', 'Chameleon', 'Cammy'),
('1111111110','zebra','gigi'),
('1111111111','zebra','tuna'),
('1111111112','giraffe','shrek'),
('1111111113','giraffe','shahar'),
('1111111114','kengaroo','kengi');

-- Insert data into Reptiles table
INSERT INTO Reptiles (reptiles_ID, FoodType) VALUES
('0000000001', 'mouse'),
('0000000002', 'flower'),
('0000000003', 'flower'),
('0000000004', 'fly'),
('0000000005', 'fly');


INSERT INTO Mammal_Vegetarian (Mammal_veg_ID, vegType) VALUES
('1111111110','grass'),
('1111111111','grass'),
('1111111112','leaves'),
('1111111113','leaves'),
('1111111114','grass');


-- Insert data into Attraction table
INSERT INTO Attraction (attrac_num, name) VALUES
('2222222220', 'SNAKES FEEDING'),
('2222222221', 'LIONS FEEDING'),
('2222222222', 'KIDS SHOW'),
('2222222223', 'CLOWN'),
('2222222224', 'ELEPHANT FEEDING'),
('2222222225', 'ITALIAN SAFARI'),
('2222222226', 'CAFE JUNGLE');

INSERT INTO Feeding (attrac_num,which_animal) VALUES
('2222222220','SNAKE'),
('2222222221','LION'),
('2222222224','ELEPHANT');

INSERT INTO Restaurant(attrac_num, MilkOrMeat) VALUES
('2222222225', 'Meat'),
('2222222226', 'Milk');

INSERT INTO Show (ages,attrac_num) VALUES
(6,'2222222222'),
(18,'2222222223');

INSERT INTO Watches(cust_ID, attrac_num) VALUES
('000000005', '2222222220'),
('000000005', '2222222221'),
('000000006', '2222222220'),
('000000008', '2222222220'),
('000000006', '2222222224'),
('000000007', '2222222224');

INSERT INTO Participate(cust_ID, attrac_num) VALUES
('000000007', '2222222222'),
('000000008', '2222222222'),
('000000005', '2222222223'),
('000000007', '2222222223'),
('000000006', '2222222223');

--query 1 
-- Select customers' age between 21 and 67
SELECT COUNT(cust_age) as ADULTS_CUST_COUNT
FROM Customer 
WHERE cust_age BETWEEN 21 AND 67;
 
 --query 2
-- Get animals that are giraffes or zebras
SELECT an.Species, an.Name 
FROM Animal an
WHERE an.Species = 'Giraffe' OR an.Species = 'Zebra';

  --query 3
--Gets list of the names of the customers that age is not allowed in a show
SELECT c.first_name, c.last_name, a.name
FROM Customer AS c 
JOIN participate AS p ON c.cust_ID = p.cust_ID
JOIN show AS s ON p.attrac_num = s.attrac_num AND c.cust_age <= s.ages
JOIN Attraction AS a ON s.attrac_num = a.attrac_num ;

  
--query 4
--For every feeding show, the minimum age of people who came to watch
SELECT a.name AS Feeding_Show, MIN(c.cust_age) AS Min_Age
FROM watches w
JOIN Attraction a ON w.attrac_num = a.attrac_num
JOIN Customer c ON w.cust_ID = c.cust_ID
JOIN Feeding f ON a.attrac_num = f.attrac_num
GROUP BY a.name;


--query 5
-- Calculate the difference of each customer's age from the average age
SELECT c.cust_ID, c.first_name, c.last_name, c.cust_age, 
  (c.cust_age - (SELECT AVG(cust_age) FROM Customer)) AS age_diff_from_avg
INTO tempAvgAges
FROM Customer c;
-- Select from the temporary table
SELECT * FROM tempAvgAges;
drop table tempAvgAges;
  

--query 6  
-- List of feeding show that have a greater number of watches than the average number of viewers for all feeding shows
WITH FeedingShowWatches AS (SELECT a.attrac_num, a.name, COUNT(w.cust_ID) AS watch_count
							FROM watches w
							JOIN Attraction a ON w.attrac_num = a.attrac_num
							WHERE a.name LIKE '%FEEDING%'
							GROUP BY a.attrac_num, a.name),
AverageWatches AS (SELECT AVG(watch_count) AS avg_watch_count
				   FROM FeedingShowWatches)
SELECT f.attrac_num, f.name, f.watch_count
INTO ShowsSummary
FROM FeedingShowWatches f
JOIN AverageWatches a ON f.watch_count > a.avg_watch_count;
SELECT * FROM ShowsSummary;
DROP TABLE ShowsSummary;


--query 7
-- For each show present the show serial number, and number of participants, only if the number of participants is greater than 2
select s.attrac_num, count(p.cust_ID) as Participant_count
from show as s join participate as p  on s.attrac_num = p.attrac_num
group by s.attrac_num
having count(p.cust_ID) > 2


--query 8
-- Calculate the average age of customers for each show or feeding show
SELECT a.name AS Attraction_Name, AVG(c.cust_age) AS Avg_Age
FROM Customer c
JOIN (SELECT cust_ID, attrac_num FROM watches
      UNION ALL
      SELECT cust_ID, attrac_num FROM participate) ca ON c.cust_ID = ca.cust_ID
JOIN Attraction a ON ca.attrac_num = a.attrac_num
GROUP BY a.name;


--query 9
-- Retrieve all clients ordered from smallest to biggest based on their customer ID
SELECT cust_ID, first_name, last_name, cust_age
FROM Customer
ORDER BY cust_age;
  
 --query 10
-- Update query to switch reptile food from flower to grass
UPDATE Reptiles
SET FoodType = 'Grass'
WHERE FoodType = 'Flower';


--query 11
-- Update query to switch all meat restaurants to dairy
UPDATE RESTAURANT
SET MilkOrMeat = 'Milk'
WHERE MilkOrMeat = 'Meat';


--query 12
-- Delete query to remove all people with first name starting with 'I'
DELETE FROM Service
WHERE service_ID_num IN (SELECT service_ID_num FROM Employee);
DELETE FROM Employee
WHERE Employee_ID_num IN (SELECT Employee_ID_num FROM Service);

DELETE FROM Employee
WHERE First_name LIKE 'I%';


--query 13
-- Delete from Reptiles table where species is Lizard
DELETE FROM Reptiles
WHERE reptiles_ID IN 
	(SELECT Animal_ID
	  FROM Animal
      WHERE Species = 'Lizard');
	 



-- Drop foreign key constraints
ALTER TABLE Mammal_Predator DROP CONSTRAINT FK__Mammal_Predator__Animal_ID;
ALTER TABLE Mammal_Vegetarian DROP CONSTRAINT FK__Mammal_Vegetarian__Animal_ID;
ALTER TABLE Reptiles DROP CONSTRAINT FK__Reptiles__Animal_ID;
ALTER TABLE CareTaker DROP CONSTRAINT FK__CareTaker__Employee_ID_num;
ALTER TABLE Service DROP CONSTRAINT FK__Service__Employee_ID_num;
ALTER TABLE Restaurant DROP CONSTRAINT FK__Restaurant__Attraction;
ALTER TABLE Watches DROP CONSTRAINT FK__Watches__Customer_ID;
ALTER TABLE Watches DROP CONSTRAINT FK__Watches__Attraction;
ALTER TABLE Participate DROP CONSTRAINT FK__Participate__Customer_ID;
ALTER TABLE Participate DROP CONSTRAINT FK__Participate__Attraction;
ALTER TABLE Feeding DROP CONSTRAINT FK__Feeding__Attraction;
ALTER TABLE Show DROP CONSTRAINT FK__Show__Attraction;

-- Drop tables
DROP TABLE Mammal_Predator;
DROP TABLE Mammal_Vegetarian;
DROP TABLE Reptiles;
DROP TABLE CareTaker;
DROP TABLE Service;
DROP TABLE Restaurant;
DROP TABLE Watches;
DROP TABLE Participate;
DROP TABLE Feeding;
DROP TABLE Show;
DROP TABLE Employee;
DROP TABLE Customer;
DROP TABLE Animal;
DROP TABLE Attraction;