--Use ASCON
--Go -- Forces SQL to move onto the next line

Select count(*) --Aggregate function, functions within a function
From Orders

Select * --Non aggregate function
from Orders

Select count(*) AS Total_Count -- Adds a title label
from Orders

Select count(*) AS Total_Count -- Adds a title label
from Orders
GROUP BY [State] -- List of all counts of the total transaction counts per State BUT no state is shown. 


Select 
[State],
count(*) AS Total_Count -- Adds a title label
from Orders
GROUP BY [State] -- List of all counts of the total transaction counts per State NOW shows the states.

Select 
[State],
count(*) AS Total_Count -- Adds a title label
from Orders
GROUP BY [State] -- List of all counts of the total transaction counts per State NOW shows the states.
Order by [State] ASC -- Now ordered by alphabetical order in terms of state

Select 
[State],
count(*) AS Total_Count -- Adds a title label
from Orders
GROUP BY [State] -- List of all counts of the total transaction counts per State NOW shows the states.
Order by count(*) DESC -- Now ordered by alphabetical order in terms of count of transactions (high to low)

Select 
[State],
count(*) AS Total_Count -- Adds a title label
from Orders
GROUP BY [State] -- List of all counts of the total transaction counts per State NOW shows the states.
HAVING count(*) > 500 --- Filters to counts that are above 500
Order by count(*) DESC -- Now ordered by alphabetical order in terms of count of transactions (high to low)
--COUNT could be changed to MIN/MAX/SUM/AVERAGE/COUNT -- These are aggregate functions
-- WHERE does not work in aggregate functions, only HAVING does 
--WHERE works only in NON-aggregate functions

Select 
[State],
Min([Sales]) AS Minimum_Sales,
MAX([Sales]) AS Maximum_Sales
FROM Orders
GROUP by [State] -- GROUP BY is needed to show the minimum and maxiumum sales of the sales per states
Having Max([Sales]) > 2000 --Only shows the states that have maximum sales over 2000. (Like a filter/slicer)
Order by MAX([Sales]) desc -- Orders by the max sales (high to low)








-- In session worksheet
--Find Sum of all profits by state, identify top 3
Select TOP 3
[State],
SUM([Profit]) AS Sum_Profits
From Orders 
Group by [State]
Order by SUM([Profit]) desc

-- California, New York, Washington

--Find sum of all sales by city, identify bottom 3
Select TOP 3
[City],
SUM([Sales]) AS Sum_Sales
From Orders 
Group by [City]
Order by SUM([Sales]) asc

-- Abilene, Elyria, Jupiter

-- Count all the transaction in California by City
Select 
[City],
[State],
Count([Sales]) AS Sum_Sales
From Orders 
Group by [State], [City]
Having [State] = 'California'
Order by Count([Sales]) desc

--Lists all cities in California and shows the number of transactions

-- Find the sum of all profits by state, Identify bottom negative values 
Select 
[State],
SUM([Profit]) AS Sum_Profits
From Orders
Group by [State]
having sum([Profit]) < 0
Order by Sum([Profit]) asc

--Texas, Ohio, Pennsylvania, Illinois, North Carolina, Colorado,Tennessee,Arizona,Florida,Oregon




-- SQL Constraints 
Create table Student_Records (
	Student_ID Int Identity(200,1) NOT NULL Primary Key, --Identity (starting number, increment increase)
	FirstName varchar(255),
	LastName varchar(255) NOT NULL,
	Course varchar (450)
	);

Insert into Student_Records values -- Automatically generates the student ID so only 3 columns of input needed.
('James', 'Smith', 'Biology'),
('Susan', 'Baker', 'Chemistry'),
('John', 'Henry', 'Mathematics');

select * from Student_Records

insert into Student_Records	values --Use notepad to open CSV files and copy and paste directly into the query, it'll have formats and commas ready to get added to the table.
('Camila','Wood','B.Com Financial Services'),
('Alexander','Thompson','B.Com Accounting Analytics'),
('Liam','Taylor','B.Com Commerce'),
('Evelyn','Jenkins','MBA International Business'),
('Michael','Jackson','B.Com Commerce'),
('Chloe','Moore','MBA International Business'),
('Nicholas','Clark','BBA International Business'),
('Olivia','Richardson','B.Com Commerce'),
('Aiden','Rivera','MBA International Business'),
('Harper','Turner','BBA Business Analytics'),
('Madison','Lee','MBA International Business'),
('Carter','Hall','B.Com Information Technology with Accounting Analytics'),
('Mia','Thomas','Intg. BBA+MBA Financial Services'),
('Lucas','Evans','MBA International Business'),
('Abigail','Bailey','MBA International Business'),
('Joseph','Diaz','MBA Information Technology with Accounting Analytics'),
('William','Walker','Intg. BBA+MBA Financial Services'),
('Andrew','Anderson','B.Com Information Technology with Accounting Analytics'),
('Sophia','King','MBA Innovation Entrepreneurship and Venture Development'),
('Avery','Gonzalez','MBA Innovation Entrepreneurship and Venture Development'),
('Lily','Davis','BBA International Business'),
('Scarlett','Cooper','BBA International Business'),
('Grace','Reed','MBA International Business'),
('Matthew','Johnson','BBA International Business'),
('Jackson','Baker','B.Com Information Technology with Accounting Analytics'),
('Ella','Martinez','B.Com Commerce'),
('Henry','Perez','BBA International Business'),
('Ava','White','B.Com Commerce'),
('Gabriel','Miller','BBA Financial Services'),
('Mia','Brown','B.Com Information Technology with Accounting Analytics'),
('Layla','Garcia','BBA Financial Services'),
('Emma','Stewart','Intg. BBA+MBA Financial Services'),
('Daniel','Adams','BBA International Business'),
('Logan','Smith','B.Com Accounting'),
('James','Harris','MBA International Business'),
('Benjamin','Martin','MBA Innovation Entrepreneurship and Venture Development'),
('Elijah','Robinson','MBA Innovation Entrepreneurship and Venture Development'),
('Zoey','Hayes','B.Com Business Analytics'),
('Samuel','Phillips','B.Com Information Technology with Accounting Analytics'),
('Emily','Williams','BBA Financial Services'),
('Sofia','Lewis','Intg. BBA+MBA Financial Services'),
('Amelia','Young','MBA Innovation Entrepreneurship and Venture Development'),
('Charlotte','Wilson','MBA International Business'),
('Jack','Scott','MBA Innovation Entrepreneurship and Venture Development'),
('Noah','Parker','MBA Business Analytics'),
('David','Rodriguez','B.Com Commerce'),
('Aria','Nelson','B.Com Business Analytics'),
('Ethan','Carter','B.Com Information Technology with Accounting Analytics'),
('Oliver','Green','MBA International Business'),
('Harper','Jones','BBA Business Analytics'),
('David','Martin','BBA Financial Services'),
('Avery','Green','MBA International Business'),
('Aiden','Wood','B.Com Information Technology with Accounting Analytics'),
('Jackson','Anderson','BBA Finance'),
('Scarlett','Lee','MBA Digital Marketing'),
('Lucas','Johnson','BBA Digital Marketing'),
('Madison','Davis','MBA International Business'),
('Nicholas','Jones','BBA Financial Services'),
('Evelyn','Stewart','MBA International Business'),
('Benjamin','Richardson','B.Com Information Technology with Accounting Analytics'),
('Charlotte','Walker','B.Com Financial Services'),
('Samuel','Scott','BBA International Business'),
('William','Rodriguez','B.Com Business Analytics'),
('Emily','Carter','Intg. BBA+MBA International Business'),
('Carter','Hall','MBA International Business'),
('Alexander','Moore','BBA Digital Marketing'),
('Emma','Wilson','B.Com Financial Services with Digital Finance'),
('Mia','Young','BBA Financial Services'),
('Aria','Evans','MBA Innovation Entrepreneurship and Venture Development'),
('Layla','Phillips','BBA Digital Marketing'),
('Matthew','Lewis','B.Com Information Technology with Accounting Analytics'),
('Noah','Jenkins','MBA Entrepreneurship & Innovation'),
('Camila','Hayes','B.Com Finance'),
('Logan','Turner','B.Com Financial Services with Digital Finance'),
('Lily','Miller','B.Com Financial Services'),
('Mia','Perez','MBA Banking and Financial Services'),
('Zoey','Rivera','MBA Digital Marketing'),
('Sophia','King','MBA Innovation Entrepreneurship and Venture Development'),
('Grace','Cooper','BBA International Business'),
('Chloe','Williams','MBA International Business'),
('Ella','Bailey','MBA International Business'),
('Harper','Nelson','MBA International Business'),
('Henry','Baker','B.Com Information Technology with Accounting Analytics'),
('Andrew','Gonzalez','BBA International Business'),
('Amelia','Harris','MBA Innovation Entrepreneurship and Venture Development'),
('Abigail','Robinson','MBA International Business'),
('Harper','Jackson','BBA Digital Marketing'),
('Michael','White','Intg. BBA+MBA Financial Services'),
('Daniel','Brown','MBA Entrepreneurship & Innovation'),
('Elijah','Thomas','MBA International Business'),
('Liam','Thompson','MBA Innovation Entrepreneurship and Venture Development'),
('Joseph','Martinez','BBA Digital Marketing'),
('James','Taylor','BBA Digital Marketing'),
('Ava','Clark','B.Com Financial Services'),
('Gabriel','Diaz','BBA Brand Management'),
('Oliver','Parker','B.Com Financial Services'),
('Olivia','Smith','MBA International Business'),
('Ethan','Adams','MBA International Business'),
('Sofia','Garcia','Intg. BBA+MBA Financial Services'),
('Jack','Reed','MBA International Business'),
('Henry','Gonzalez','B.Com Financial Services with Digital Finance'),
('Jackson','Johnson','BBA Brand Management'),
('William','Richardson','BBA Entrepreneurship & Innovation'),
('Gabriel','Wilson','Intg. BBA+MBA Financial Services'),
('Ava','Walker','MBA International Business'),
('Harper','Bailey','MBA Innovation Entrepreneurship and Venture Development'),
('Alexander','Clark','B.Com Finance'),
('Michael','Evans','B.Com Business Analytics'),
('Zoey','Jenkins','MBA Innovation Entrepreneurship and Venture Development'),
('Liam','Taylor','Intg. BBA+MBA Financial Services'),
('Camila','Rivera','MBA International Business'),
('Jack','Garcia','BBA Digital Marketing'),
('Joseph','Parker','Intg. BBA+MBA Financial Services'),
('Abigail','Thomas','MBA International Business'),
('Sophia','Carter','MBA International Business'),
('Layla','Jackson','BBA International Business'),
('Mia','Smith','B.Com Business Analytics'),
('Sofia','Nelson','BBA International Business'),
('Elijah','Williams','BBA International Business'),
('Charlotte','Baker','BBA International Business'),
('Benjamin','Green','B.Com Information Technology with Accounting Analytics'),
('Matthew','Miller','BBA International Business'),
('Ella','Stewart','BBA Finance'),
('Emily','Moore','MBA Innovation Entrepreneurship and Venture Development'),
('Noah','Martinez','MBA Innovation Entrepreneurship and Venture Development'),
('Grace','Hall','Intg. BBA+MBA Financial Services'),
('Logan','Brown','BBA Business Analytics'),
('Lily','Davis','BBA Digital Marketing'),
('Amelia','Jones','MBA International Business'),
('Carter','Wood','MBA International Business'),
('Emma','Reed','MBA International Business'),
('Harper','Diaz','BBA International Business'),
('Daniel','Lee','BBA Business Analytics'),
('David','Adams','MBA Innovation Entrepreneurship and Venture Development'),
('James','Scott','MBA Innovation Entrepreneurship and Venture Development'),
('Chloe','Anderson','MBA Innovation Entrepreneurship and Venture Development'),
('Oliver','Turner','Intg. BBA+MBA Business Analytics'),
('Scarlett','Cooper','BBA International Business'),
('Mia','Young','B.Com Business Analytics'),
('Olivia','White','B.Com Commerce'),
('Lucas','Hayes','BBA International Business'),
('Aria','King','MBA Brand Management'),
('Madison','Harris','Intg. BBA+MBA Financial Services'),
('Aiden','Thompson','MBA International Business'),
('Andrew','Phillips','MBA Innovation Entrepreneurship and Venture Development'),
('Evelyn','Robinson','BBA Financial Services'),
('Avery','Lewis','BBA International Business'),
('Ethan','Martin','MBA International Business'),
('Nicholas','Rodriguez','BBA International Business'),
('Samuel','Perez','BBA Digital Marketing'),
('Emily','Lee','BBA Digital Marketing');