-- Write a query to retrieve a list of unique states where students reside, ordered alphabetically from A to Z, from the Student table.

CREATE TABLE Student ( 
    ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    FirstName VARCHAR(20) NOT NULL, 
    LastName VARCHAR(30) NOT NULL, 
    Street VARCHAR(50) NOT NULL, 
    City VARCHAR(20) NOT NULL, 
    State CHAR(20) NOT NULL DEFAULT 'TX', 
    Zip MEDIUMINT UNSIGNED NOT NULL, 
    Phone CHAR(10) NOT NULL, 
    Email VARCHAR(30) UNIQUE
    );

INSERT INTO Student (ID, FirstName, LastName, Street, City, State, Zip, Phone, Email) VALUES
  (1, 'Ava', 'Johnson', '123 Maple St', 'Austin', 'TX', 73301, '5125551234', 'ava.johnson@example.com'),
  (2, 'Liam', 'Martinez', '456 Oak Ave', 'Chicago', 'IL', 60601, '3125555678', 'liam.martinez@example.com'),
  (3, 'Sophia', 'Chen', '789 Pine Rd', 'Seattle', 'WA', 98101, '2065559012', 'sophia.chen@example.com'),
  (4, 'Noah', 'Patel', '321 Birch Blvd', 'Miami', 'FL', 33101, '3055553456', 'noah.patel@example.com'),
  (5, 'Isabella', 'Nguyen', '654 Cedar Ln', 'Phoenix', 'AZ', 85001, '6025557890', 'isabella.nguyen@example.com'),
  (6, 'Ethan', 'Garcia', '987 Elm St', 'Denver', 'CO', 80201, '7205551122', 'ethan.garcia@example.com'),
  (7, 'Mia', 'Rodriguez', '222 Spruce Ct', 'Boston', 'MA', 02108, '6175553344', 'mia.rodriguez@example.com'),
  (8, 'James', 'Kim', '135 Willow Way', 'Minneapolis', 'MN', 55401, '6125555566', 'james.kim@example.com'),
  (9, 'Charlotte', 'Singh', '876 Aspen Dr', 'Las Vegas', 'NV', 89101, '7025557788', 'charlotte.singh@example.com'),
  (10, 'Benjamin', 'Lee', '444 Redwood Pl', 'New York', 'NY', 10001, '2125559900', 'benjamin.lee@example.com'),
  (11, 'Olivia', 'Brown', '101 Chestnut St', 'Dallas', 'TX', 75202, '2145551111', 'olivia.brown@example.com'),
  (12, 'William', 'Davis', '202 Walnut Ave', 'San Antonio', 'TX', 78206, '2105552222', 'william.davis@example.com'),
  (13, 'Emma', 'Wilson', '303 Poplar Blvd', 'Orlando', 'FL', 32801, '4075553333', 'emma.wilson@example.com'),
  (14, 'Lucas', 'Moore', '404 Cypress Ct', 'Tampa', 'FL', 33602, '8135554444', 'lucas.moore@example.com'),
  (15, 'Amelia', 'Taylor', '505 Redwood Dr', 'Albany', 'NY', 12207, '5185555555', 'amelia.taylor@example.com'),
  (16, 'Henry', 'Thomas', '606 Magnolia Ln', 'Buffalo', 'NY', 14201, '7165556666', 'henry.thomas@example.com'),
  (17, 'Harper', 'White', '707 Fir St', 'Boulder', 'CO', 80301, '3035557777', 'harper.white@example.com'),
  (18, 'Alexander', 'Harris', '808 Palm Way', 'Scottsdale', 'AZ', 85251, '4805558888', 'alexander.harris@example.com'),
  (19, 'Evelyn', 'Martin', '909 Olive Rd', 'Mesa', 'AZ', 85201, '4805559999', 'evelyn.martin@example.com'),
  (20, 'Sebastian', 'Clark', '111 Hickory Pl', 'Springfield', 'IL', 62701, '2175550000', 'sebastian.clark@example.com');

SELECT DISTINCT State -- UNIQUE State, distinct REMOVES THE DUPLICATES 
FROM Student 
WHERE State IS NOT NULL 
ORDER BY State ASC; 

SELECT State -- Duplicates were not removed
FROM Student 
WHERE State IS NOT NULL 
ORDER BY State ASC;