-- ============================================================
-- Zero to SQL, 2nd Edition  -  Example Queries (MySQL edition)
-- Carlos L Chacon Jr  -  zerotosql.com/scripts
--
-- Every example from the book, in order, so you can follow along.
-- Run Setup_MySQL.sql first to create the LearnSQL database.
-- ============================================================

USE LearnSQL;


-- ============================================================
-- Getting Started — verify your setup
-- ============================================================
/* Verify the sample data loaded */
SELECT * FROM Dog;


-- ============================================================
-- Lesson 3 — Retrieving Data
-- ============================================================
/* Select every column from a table */
SELECT *
FROM Cat;

/* Distinct values in one column */
SELECT DISTINCT ColorOfDog
FROM Dog;

/* Distinct combinations across two columns */
SELECT DISTINCT ColorOfDog, BreedOfDog
FROM Dog;

/* Return only the first 5 rows */
SELECT OwnerName, OwnerAge, OwnerGender
FROM PetOwner
LIMIT 5;

/* Return the top 1 percent of rows */
-- MySQL has no PERCENT keyword. Count the rows, then use LIMIT with ~1%:
--   SELECT CEIL(COUNT(*) * 0.01) FROM PetOwner;
SELECT OwnerName, OwnerAge, OwnerGender
FROM PetOwner
LIMIT 4;


-- ============================================================
-- Lesson 4 — Filtering
-- ============================================================
/* Filter with an exact match */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat
WHERE NameOfCat = 'Garfield';

/* Filter with a numeric comparison */
SELECT NameOfDog, BreedOfDog, ColorOfDog
FROM Dog
WHERE MonthsWithOwner < 2;

/* Filter with LIKE (wildcard) */
SELECT NameOfCat, BreedOfCat
FROM Cat
WHERE NameOfCat LIKE 'Gar%';

/* Filter with IN (a list of values) */
SELECT NameOfDog, BreedOfDog, ColorOfDog
FROM Dog
WHERE NameOfDog IN ('Pluto', 'Lassie', 'Benji', 'Old Yeller');

/* IN with numbers */
SELECT OwnerName, OwnerAge, OwnerGender
FROM PetOwner
WHERE OwnerAge IN (8, 10, 12, 14, 16);

/* Filter with NOT IN */
SELECT NameOfDog, BreedOfDog, ColorOfDog
FROM Dog
WHERE NameOfDog NOT IN ('Hercules', 'Spike', 'Pongo', 'Ace', 'Clifford', 'Laddie', 'Rover');

/* Combine conditions with AND */
SELECT NameOfDog, BreedOfDog, ColorOfDog
FROM Dog
WHERE NameOfDog IN ('Pluto', 'Lassie', 'Benji', 'Old Yeller')
      AND MonthsWithOwner > 24;

/* Combine conditions with OR */
SELECT NameOfDog, BreedOfDog, ColorOfDog
FROM Dog
WHERE NameOfDog IN ('Pluto', 'Lassie', 'Benji', 'Old Yeller')
      OR MonthsWithOwner > 24;


-- ============================================================
-- Lesson 5 — Sorting Data
-- ============================================================
/* Order the results */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY NameOfCat;

/* ASC is the default (same as above) */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY NameOfCat ASC;

/* Order descending */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY NameOfCat DESC;

/* Order by two columns */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY NameOfCat, BreedOfCat;

/* Mix ASC and DESC */
SELECT NameOfCat, BreedOfCat, MonthsWithOwner
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY MonthsWithOwner DESC, NameOfCat ASC;

/* Order by column position */
SELECT NameOfCat, BreedOfCat, MonthsWithOwner
FROM Cat
WHERE NameOfCat LIKE 'Gar%'
ORDER BY 1, 2;


-- ============================================================
-- Lesson 6 — Joins
-- ============================================================
/* INNER JOIN */
SELECT NameOfCat, NameOfDog
FROM Dog
INNER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier;

/* LEFT OUTER JOIN */
SELECT DogIdentifier, NameOfDog, BreedOfDog, NameOfCat
FROM Dog
LEFT OUTER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier;

/* RIGHT OUTER JOIN */
SELECT NameOfCat, NameOfDog, BreedOfDog
FROM Dog
RIGHT OUTER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier
ORDER BY Cat.CatIdentifier;


-- ============================================================
-- Lesson 7 — Manipulating Data and Aliases
-- ============================================================
/* Calculate a column and alias it */
SELECT NameOfCat, MonthsWithOwner / 12 AS `Years WITH Owner`
FROM Cat
WHERE MonthsWithOwner > 12;

/* Build a sentence by concatenating columns */
SELECT CONCAT(NameOfDog, ' is a ', ColorOfDog, ' dog who lives with ', OwnerName, '.')
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.DogIdentifier < 510;

/* Show the columns alongside the sentence */
SELECT NameOfDog, ColorOfDog, OwnerName,
       CONCAT(NameOfDog, ' is a ', ColorOfDog, ' dog who lives with ', OwnerName, '.') AS SQLSentence
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.DogIdentifier < 510;


-- ============================================================
-- Lesson 8 — Functions
-- ============================================================
-- ChooseNewName is created by your setup script (Setup_MySQL.sql); it returns a random
-- pet name for the given type ('Dog' or 'Cat').
/* Call a function with a parameter */
SELECT ChooseNewName('Dog') AS NameOfDog;

/* Count rows */
SELECT COUNT(*) AS `Total Number of Cats`
FROM Cat;

/* Call a function with no parameter */
SELECT ChooseNewName() AS NameOfDog;

/* Smallest value */
SELECT MIN(MonthsWithOwner) AS LeastTimeWithOwner
FROM Cat;

/* Largest value */
SELECT MAX(MonthsWithOwner) AS MostTimeWithOwner
FROM Cat;

/* Average value */
SELECT AVG(MonthsWithOwner) AS AvgTimeWithOwner
FROM Cat;

/* Average across all owners */
SELECT AVG(OwnerAge) AS `Average Pet Owner Age`
FROM PetOwner;

/* Average with a filter */
SELECT AVG(MonthsWithOwner) AS AvgTimeWithOwner
FROM Cat
WHERE ColorOfCat = 'Orange';

/* Sum a column */
SELECT SUM(MonthsWithOwner) AS `Total Months of Cat Ownership`
FROM Cat;

/* Sum with a filter */
SELECT SUM(MonthsWithOwner) AS `Total Months of Cat Ownership for Black Cats`
FROM Cat
WHERE ColorOfCat = 'Black';


-- ============================================================
-- Lesson 9 — Window Functions
-- ============================================================
-- Window functions are ANSI standard and run identically in SQL Server, MySQL 8+, and PostgreSQL.
/* ROW_NUMBER assigns a running number */
SELECT ROW_NUMBER() OVER (ORDER BY MonthsWithOwner DESC) AS RowNum,
       NameOfDog, BreedOfDog, MonthsWithOwner
FROM Dog;

/* RANK by a value */
SELECT RANK() OVER (ORDER BY MonthsWithOwner DESC) AS CatRank,
       NameOfCat, MonthsWithOwner
FROM Cat
ORDER BY MonthsWithOwner DESC;

/* RANK within each group (PARTITION BY) */
SELECT NameOfDog, BreedOfDog, MonthsWithOwner,
       RANK() OVER (PARTITION BY BreedOfDog ORDER BY MonthsWithOwner DESC) AS BreedRank
FROM Dog
ORDER BY BreedOfDog, BreedRank;

/* Aggregate as a window (average per group) */
SELECT NameOfCat, ColorOfCat, MonthsWithOwner,
       AVG(MonthsWithOwner) OVER (PARTITION BY ColorOfCat) AS AvgForColor
FROM Cat
ORDER BY ColorOfCat, MonthsWithOwner DESC;

/* Look at the previous row with LAG */
SELECT NameOfDog, MonthsWithOwner,
       LAG(MonthsWithOwner) OVER (ORDER BY DogIdentifier) AS PreviousDogMonths
FROM Dog
ORDER BY DogIdentifier;


-- ============================================================
-- Lesson 10 — Summarizing Data
-- ============================================================
/* Group and count */
SELECT NameOfCat, COUNT(*) AS `Number of Cats`
FROM Cat
GROUP BY NameOfCat
ORDER BY COUNT(*) DESC;

/* Group by country */
SELECT CountryOfResidence, COUNT(*) AS NumberOfOwners
FROM PetOwner
GROUP BY CountryOfResidence
ORDER BY COUNT(*) DESC;

/* Group by two columns */
SELECT CountryOfResidence, OwnerGender, COUNT(*) AS NumberOfOwners
FROM PetOwner
GROUP BY CountryOfResidence, OwnerGender
ORDER BY COUNT(*) DESC;

/* Change the ordering */
SELECT CountryOfResidence, OwnerGender, COUNT(*) AS NumberOfOwners
FROM PetOwner
GROUP BY CountryOfResidence, OwnerGender
ORDER BY CountryOfResidence ASC, COUNT(*) DESC;

/* Filter groups with HAVING */
SELECT CountryOfResidence, COUNT(*) AS NumberOfOwners
FROM PetOwner
GROUP BY CountryOfResidence
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;

/* HAVING across a join */
SELECT NameOfDog, PetOwner.CountryOfResidence, COUNT(*) AS NumberOfOwners
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
GROUP BY NameOfDog, PetOwner.CountryOfResidence
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

/* WHERE and HAVING together */
SELECT NameOfDog, PetOwner.CountryOfResidence, COUNT(*)
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
WHERE CountryOfResidence <> 'New Zealand' AND NameOfDog <> 'Spike'
GROUP BY NameOfDog, PetOwner.CountryOfResidence
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;


-- ============================================================
-- Lesson 11 — Subqueries
-- ============================================================
/* Step 1: find the data */
SELECT DogIdentifier
FROM VeterinarianVisitHistory
WHERE RabiesShotCurrent = 0;

/* Step 2: get the owners for those dogs */
SELECT OwnerName
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
WHERE Dog.DogIdentifier IN (541, 551);

/* Combine both steps with a subquery */
SELECT PetOwner.OwnerName
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
INNER JOIN ( SELECT VeterinarianVisitHistory.DogIdentifier
             FROM VeterinarianVisitHistory
             WHERE RabiesShotCurrent = 0
           ) shot ON shot.DogIdentifier = Dog.DogIdentifier;

/* Use a value from the subquery in the results */
SELECT OwnerName, visits.VetVisits AS `Number of Vet Visits`
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
INNER JOIN ( SELECT DogIdentifier, COUNT(*) AS VetVisits
             FROM VeterinarianVisitHistory
             GROUP BY VeterinarianVisitHistory.DogIdentifier
             HAVING COUNT(*) > 1
           ) visits ON visits.DogIdentifier = Dog.DogIdentifier;


-- ============================================================
-- Lesson 12 — Inserting Data
-- ============================================================
/* Insert a row, listing the columns */
INSERT INTO Cat
       (BirthDateOfCat, BreedOfCat, ColorOfCat, KeptIndoors, MonthsWithOwner, NameOfCat, OwnerIdentifier)
VALUES ('2014-01-01', 'Himalayan', 'Gray', 0, 18, 'Shadow', 84);

-- The next insert omits the column list. It matches the one above, so don't run both.
/* Insert without listing columns */
INSERT INTO Cat
VALUES ('2014-01-01', 'Himalayan', 'Gray', 0, 18, 'Shadow', 84);

/* Insert only the required columns */
INSERT INTO Cat
       (ColorOfCat, KeptIndoors, NameOfCat)
VALUES ('White', 0, 'Dru');

/* Insert rows selected from another table */
INSERT INTO Dog
       (BirthDateOfDog, BreedOfDog, ColorOfDog, KeptIndoors, MonthsWithOwner, NameOfDog, OwnerIdentifier)
SELECT BirthDateOfAnimal, BreedOfAnimal, ColorOfAnimal, KeptIndoors, MonthsWithOwner, NameOfAnimal, OwnerIdentifier
FROM AnimalsToBePlaced
WHERE TypeOfAnimal = 'Dog';

/* ...and the same for cats */
INSERT INTO Cat
       (BirthDateOfCat, BreedOfCat, ColorOfCat, KeptIndoors, MonthsWithOwner, NameOfCat, OwnerIdentifier)
SELECT BirthDateOfAnimal, BreedOfAnimal, ColorOfAnimal, KeptIndoors, MonthsWithOwner, NameOfAnimal, OwnerIdentifier
FROM AnimalsToBePlaced
WHERE TypeOfAnimal = 'Cat';

-- This fails until the table below is created.
/* Query a table that doesn't exist yet */
SELECT * FROM DogsInEstablishedHomes;

/* Create a new table from a query */
CREATE TABLE DogsInEstablishedHomes AS
SELECT NameOfDog, BreedOfDog, ColorOfDog, MonthsWithOwner
FROM Dog
WHERE MonthsWithOwner > 24;

/* Now the query succeeds */
SELECT * FROM DogsInEstablishedHomes;


-- ============================================================
-- Lesson 13 — Making Changes to Data
-- ============================================================
/* Update a value */
UPDATE Cat
SET NameOfCat = 'Mr. Whiskers'
WHERE NameOfCat = 'Whiskers';

/* Update using a join */
UPDATE Cat
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Cat.OwnerIdentifier
SET Cat.MonthsWithOwner = Cat.MonthsWithOwner + 2
WHERE PetOwner.CountryOfResidence = 'Russia';

/* Delete rows */
DELETE
FROM Dog
WHERE Dog.NameOfDog = 'Test';


-- ============================================================
-- Lesson 14 — Creating Objects
-- ============================================================
-- The download scripts add the auto-incrementing key for you (IDENTITY / AUTO_INCREMENT / GENERATED).
/* Create a table */
CREATE TABLE Fish (
    FishIdentifier    INT,
    BirthDateOfFish   DATETIME     NULL,
    BreedOfFish       VARCHAR(30)  NULL,
    ColorOfFish       VARCHAR(25)  NOT NULL,
    MonthsWithOwner   INT          NULL,
    NameOfFish        CHAR(40)     NOT NULL,
    OwnerIdentifier   INT          NULL,
    TankSizeInGallons SMALLINT     NULL,
    PRIMARY KEY (FishIdentifier)
);


-- ============================================================
-- Lesson 15 — Views
-- ============================================================
/* Create a view */
CREATE VIEW v_SearchForGarfield
AS
SELECT *
FROM Cat
WHERE Cat.NameOfCat = 'Garfield';

/* Select from the view */
SELECT * FROM v_SearchForGarfield;


-- ============================================================
-- Lesson 16 — Security
-- ============================================================
-- Roles and grants need appropriate privileges and may not run on a shared sandbox such as DB Fiddle.
/* Create a role */
CREATE ROLE DogsOnly;

/* Grant permissions to the role */
GRANT SELECT ON LearnSQL.PetOwner TO DogsOnly;
GRANT SELECT ON LearnSQL.Dog TO DogsOnly;


-- ============================================================
-- Lesson 17 — Changing Objects
-- ============================================================
/* Change a column's size */
ALTER TABLE PetOwner MODIFY OwnerName VARCHAR(100);

/* Add a foreign key to an existing table */
ALTER TABLE Fish ADD CONSTRAINT RefPetOwner6
FOREIGN KEY (OwnerIdentifier)
REFERENCES PetOwner (OwnerIdentifier);

/* Change a view's definition */
ALTER VIEW v_SearchForGarfield
AS
SELECT *
FROM Cat
WHERE Cat.NameOfCat LIKE 'Gar%';


-- ============================================================
-- Lesson 20 — Working with Dates
-- ============================================================
/* Current date and time */
SELECT CURRENT_TIMESTAMP;

/* Difference between two dates (age in years) */
SELECT Dog.NameOfDog, TIMESTAMPDIFF(YEAR, Dog.BirthDateOfDog, CURDATE()) AS AgeOfDog
FROM Dog;

/* Do math on the interval (human years) */
SELECT Cat.NameOfCat, TIMESTAMPDIFF(YEAR, Cat.BirthDateOfCat, CURDATE()) * 7 AS AgeInHumanYears
FROM Cat;

/* Extract part of a date (births per year) */
SELECT YEAR(Dog.BirthDateOfDog) AS BirthYear, COUNT(*) AS `Number of Births`
FROM Dog
GROUP BY YEAR(Dog.BirthDateOfDog)
ORDER BY YEAR(Dog.BirthDateOfDog);


-- ============================================================
-- BONUS — examples from the original edition no longer used in the book
-- ============================================================
/* Select specific columns (no filter) */
SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cat;

/* Include all WHERE items in the output */
SELECT NameOfDog, BreedOfDog, ColorOfDog, MonthsWithOwner
FROM Dog
WHERE NameOfDog IN ('Pluto', 'Lassie', 'Benji', 'Old Yeller')
      AND MonthsWithOwner > 24;

/* Many conditions on a join */
SELECT Dog.ColorOfDog, Dog.MonthsWithOwner, Dog.NameOfDog,
       PetOwner.OwnerName, PetOwner.CountryOfResidence
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.ColorOfDog LIKE '%white%'
      AND PetOwner.CountryOfResidence = 'Costa Rica'
      AND Dog.MonthsWithOwner > 24;

/* The subquery rewritten as a plain join */
SELECT PetOwner.OwnerName
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
INNER JOIN VeterinarianVisitHistory ON VeterinarianVisitHistory.DogIdentifier = Dog.DogIdentifier
WHERE RabiesShotCurrent = 0;

/* Add a column to a table */
ALTER TABLE Dog ADD PetNickName VARCHAR(50);

/* Filter on the year of a date */
SELECT * FROM Dog WHERE YEAR(Dog.BirthDateOfDog) = 1949;

/* Extract the month from a date */
SELECT MONTH(BirthDateOfDog)
FROM Dog;

