-- The School table will have the following columns:

-- SchoolID—positive integer
-- SchoolName—variable-length string, maximum 50 characters
-- DateConstructed—date
-- OperatingBudget—positive decimal value of up to 99.9, with one decimal place
-- Classification—fixed-length string with one character
-- President—variable-length string, maximum 20 characters


CREATE TABLE School( 
SchoolID INT UNSIGNED NOT NULL PRIMARY KEY , 
SchoolName VARCHAR(50), 
DateConstructed DATE, 
OperatingBudget DECIMAL(3,1) UNSIGNED NOT NULL,
Classification CHAR(1), 
President VARCHAR(20)
);

-- The Rating table has the following columns:

-- RatingCode—variable-length string, maximum 5 characters, primary key
-- RatingDescription—variable-length string, maximum 30 characters
-- The Movie table should have the following columns:

-- Title—variable-length string, maximum 30 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Genre—variable-length string, maximum 30 characters
-- Year—positive integer indicating the year the movie was released

CREATE TABLE Movie ( 
Title VARCHAR(30), 
RatingCode VARCHAR(5), 
Genre VARCHAR(30), 
YEAR INT UNSIGNED NOT NULL, 
FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode)
); 


-- The Horse table has the following columns:

-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date


-- Write an SQL statement to modify the Breed column to have a maximum character length of 15.

ALTER TABLE Horse 
MODIFY COLUMN Breed VARCHAR(15); 


-- The Horse table has the following columns:

-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date


-- Write an SQL statement 
-- to create a view named HorseBirthdays that contains the RegisteredName and BirthDate columns. 
-- Ensure your result set returns the columns in the order indicated.

CREATE VIEW HorseBirthdays AS 
SELECT RegisteredName, BirthDate 
FROM Horse; 


-- The Horse table has the following columns:

-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date
-- A VIEW named MyHorses has been created to display the ID, RegisteredName, and Breed columns. 



-- Alter the existing MyHorses view directly 
-- to have it include the exact columns RegisteredName, Height, and Breed (in that order) instead.

ALTER VIEW MyHorses AS 
SELECT RegisteredName, Height, Breed 
FROM Horse; 


-- The Movie table has the following columns:

-- ID—positive integer, primary key
-- Title—variable-length string, maximum 30 characters
-- Genre—variable-length string, maximum 20 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Year—positive integer
-- The YearStats table has the following columns:

-- Year—positive integer, primary key
-- TotalGross—positive bigint
-- Releases—positive integer


 -- REMEMBER SQL STATEMENT MEANS THE TABLE IS CREATED -- 
-- Write an SQL statement to designate the Year column in the Movie table as a foreign key to the Year column in the YearStats table

ALTER TABLE Movie 
ADD CONSTRAINT fk_Movie_Year 
FOREIGN KEY (Year) REFERENCES YearStats(Year); 


-- The Movie table has the following columns:

-- ID—positive integer, primary key
 -- Title—variable-length string, maximum 30 characters
-- Genre—variable-length string, maximum 20 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Year—positive integer
-- Write an SQL statement to create an index named idx_year on the Year column of the Movie table
CREATE INDEX idx_year ON MOVIE (Year); 


-- The Employee table has the following columns:

-- EmployeeID—positive integer, primary key, auto-increment
-- FirstName—variable-length string
-- LastName—variable-length string
-- Salary—decimal
-- ManagerID—integer
-- The following data needs to be added to the Employee table:

-- FirstName	LastName	Salary	ManagerID
-- Robert	Brown	150000.50	NULL

-- Write an SQL statement to insert the indicated data into the Employee table.
INSERT INTO Employee(FirstName, LastName, Salary, ManagerID) 
 VALUES ('Robert', 'Brown', 150000.50, NULL);


-- The Movie table has the following columns:

-- ID—positive integer, primary key
-- Title—variable-length string, maximum 30 characters
-- Genre—variable-length string, maximum 20 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Year—positive integer
-- Write an SQL statement to delete the row with the ID value of 3 from the Movie table
DELETE FROM Movie 
WHERE ID = 3; 


-- The Horse table has the following columns:

-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date
-- Write an SQL statement to delete records from the Horse table for horses with a Height value greater than or equal to 18.

DELETE FROM Horse 
WHERE Height >= 18; 



-- The Movie table has the following columns:

-- ID—positive integer, primary key
-- Title—variable-length string, maximum 30 characters
-- Genre—variable-length string, maximum 20 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Year—positive integer
-- Write an SQL query to retrieve the Title and Genre values for all records in the Movie table with a Year value of 2020. 
-- Ensure your result set returns the columns in the order indicated.

SELECT Title, Genre 
FROM Movie 
WHERE Movie = 2020; 



-- The Horse table has the following columns:
-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date
-- Write an SQL query to display the RegisteredName value in the Horse table ordered by BirthDate, 
-- in order from the oldest horse to the youngest.
SELECT RegisteredName 
FROM Horse 
ORDER BY BirthDate ASC; 


-- NEED TO PRACTICE THESE MOREEEEEE 
-- The Movie table has the following columns:

-- ID—positive integer, primary key
-- Title—variable-length string, maximum 30 characters
-- Genre—variable-length string, maximum 20 characters
-- RatingCode—variable-length string, maximum 5 characters
-- Year—positive integer

-- The YearStats table has the following columns:

-- Year—positive integer, primary key
-- TotalGross—positive bigint
-- Releases—positive integer


-- Write an SQL query to display 
-- both the Title and the TotalGross (if available) for all movies. 
-- Ensure your result set returns the columns in the order indicated.


SELECT Movie.Title , YearStats.TotalGross 
From Movie 
LEFT JOIN YearStats ON Movie.Year = YearStats.Year; 



-- The Horse table has the following columns:

-- ID—positive integer, primary key
-- RegisteredName—variable-length string, maximum 15 characters
-- Breed—variable-length string, maximum 20 characters
-- Height—positive decimal up to 99.9
-- BirthDate—date
-- Write an SQL query to return the average height of all horses that have a Height value of less than 17.

SELECT AVG(Height)
FROM HORSE 
WHERE Height > 17;