-- John Aromando -- SQL Basics --

--all fields from Customers table
select * from customers 

--four fields from Customers table 
select custnum,custname,State,City 
from customers

--same four fields where the customer resides in NY 
select custnum,custname,State,City
from customers
where state = 'NY'

--same four fields where the customer resides in NY or NJ
select custnum,custname,State,City
from customers
where state = 'NY' or state = 'NJ'
order by city asc, custname desc

--displays the amount of customers in states starting with N
select state, COUNT(custnum)
from customers
where state like 'N%'
group by state

--displays the amount of customers in states starting with N which have more than 1 customer in descending order.
select state, COUNT(custnum)
from customers
where state like 'N%'
group by state
having COUNT(custnum) > 1
order by COUNT(custnum) desc

--displays customer total (>7) and quantity total by states starting with N where there is a matching customer number from the Customers table in the Sales table
select count(customers.custnum), State, sum(qty)
from Customers
inner join Sales on Customers.custnum = Sales.custnum --one to many
where state like 'N%'
group by state
having COUNT(customers.custnum) > 7
order by COUNT(qty) desc -- descending order

--seven fields displayed for customers residing in the state of CA where there is a matching repid from the between the Customers and Slspers tables.
select custnum, custname, City, State, Customers.repid, fname, lname
from Customers
inner join Slspers on Customers.repid = Slspers.repid --many to one
where state = 'CA'
order by custname desc