Notes

--SELECT
--CITY,
--MIN(TOTALPRICE) AS MINPRICE,MAX(TOTALPRICE) AS MAXPRICE,COUNT(FICHENO) AS ROWCOUNT_,
--SUM(TOTALPRICE) AS TOTALPRICE,AVG(TOTALPRICE) AS AVGPRICE
--FROM SALES
--GROUP BY CITY
--ORDER BY CITY



--SELECT CONVERT(DATE,DATE_) AS DATE2,*
--FROM SALES WHERE CITY = 'ANKARA'
--ORDER BY DATE_

--UPDATE SALES SET DATE2 = CONVERT(DATE,DATE_)


--SELECT CITY,DATE2,SUM(TOTALPRICE) AS TOTALPRICE FROM
--SALES WHERE CITY='ANKARA'
--GROUP BY CITY,DATE2
--ORDER BY CITY,DATE2

--BİR GÜNÜN MAĞAZA BAZLI SATIŞ RAKAMLARINI GETİRME
--SELECT DATE2,CITY,SUM(TOTALPRICE) AS TOTALPRICE
--FROM SALES
--WHERE DATE2 = '2019-01-01'
--GROUP BY DATE2,CITY
--ORDER BY DATE2,CITY

--MAĞZALARIN AYLARA GÖRE SATIŞ RAKAMLARI
--UPDATE SALES SET MONTHNAME_ = '01.OCAK' WHERE DATEPART(MONTH,DATE2)=1
--UPDATE SALES SET MONTHNAME_ = '02.SUBAT' WHERE DATEPART(MONTH,DATE2)=2
--UPDATE SALES SET MONTHNAME_ = '03.MART' WHERE DATEPART(MONTH,DATE2)=3
--UPDATE SALES SET MONTHNAME_ = '04.NISAN' WHERE DATEPART(MONTH,DATE2)=4
--UPDATE SALES SET MONTHNAME_ = '05.MAYIS' WHERE DATEPART(MONTH,DATE2)=5
--UPDATE SALES SET MONTHNAME_ = '06.HAZIRAN' WHERE DATEPART(MONTH,DATE2)=6
--UPDATE SALES SET MONTHNAME_ = '07.TEMMUZ' WHERE DATEPART(MONTH,DATE2)=7
--UPDATE SALES SET MONTHNAME_ = '08.AGUSTOS' WHERE DATEPART(MONTH,DATE2)=8
--UPDATE SALES SET MONTHNAME_ = '09.EYLUL' WHERE DATEPART(MONTH,DATE2)=9
--UPDATE SALES SET MONTHNAME_ = '10.EKIM' WHERE DATEPART(MONTH,DATE2)=10
--UPDATE SALES SET MONTHNAME_ = '11.KASIM' WHERE DATEPART(MONTH,DATE2)=11
--UPDATE SALES SET MONTHNAME_ = '12.ARALIK' WHERE DATEPART(MONTH,DATE2)=12

--SELECT CITY,MONTHNAME_,SUM(TOTALPRICE) AS TOTALPRICE
--FROM SALES
--GROUP BY MONTHNAME_,CITY
--ORDER BY CITY,MONTHNAME_

--SELECT MONTHNAME_,CITY,SUM(TOTALPRICE) AS TOTALPRICE
--FROM SALES
--GROUP BY MONTHNAME_,CITY
--ORDER BY MONTHNAME_,SUM(TOTALPRICE) DESC


--ÜRÜN KATEGORİLERİNE GÖRE SATIŞ RAKAMLARINI GETİRME
--SELECT CATEGORY1,CATEGORY2,CATEGORY3,CATEGORY4,
--SUM(TOTALPRICE) AS TOTALPRICE,
--COUNT(*) AS ROWCOUNT_,
--SUM(AMOUNT) AS TOTALAMOUNT
--FROM SALES

--GROUP BY CATEGORY1,CATEGORY2,CATEGORY3,CATEGORY4
--ORDER BY CATEGORY1,CATEGORY2,CATEGORY3,CATEGORY4

--MAĞZALARIN MÜŞTERİ SAYILARINI GETİRME
--SELECT CITY,COUNT(DISTINCT CUSTOMERNAME) AS UNIQUECUSTOMER,COUNT(DISTINCT FICHENO) AS CUSTOMERCOUNT,
--COUNT(*) AS ITEMCOUNT
--FROM SALES WHERE MONTHNAME_='01.OCAK'
--GROUP BY CITY
--ORDER BY CITY

--BELLİR BİR CİRONUN ÜZERİNDE SATIŞ YAPAN MAĞAZALARI GETİRME
--SELECT CITY,SUM(TOTALPRICE) AS TOTALPRICE,
--COUNT(DISTINCT CUSTOMERNAME) AS CUSTOMERCOUNT
--FROM SALES
--GROUP BY CITY
----HAVING SUM(TOTALPRICE)>40000
--HAVING COUNT(DISTINCT CUSTOMERNAME)>500
--AND SUM(TOTALPRICE)>30000
--ORDER BY SUM(TOTALPRICE) DESC


--ETRADE4
SELECT
U.NAMESURNAME AS ADSOYAD,
U.EMAIL MAILADRESI, U.GENDER CINSIYET,U.BIRTHDATE DOGUMTARIHI,C.COUNTRY AS ULKE,CT.CITY SEHIR,T.TOWN ILCE,D.DISTRICT SEMT ,
A.POSTALCODE POSTAKODU,A.ADDRESSTEXT AS ACIKADRES
--U.NAMESURNAME,COUNT(A.ID) AS ADRESSAYISI,COUNT(DISTINCT C.ID) ULKESAYISI,COUNT(DISTINCT CT.ID) AS SEHIRSAYISI
FROM
USERS U,ADDRESS A,COUNTRIES C,CITIES CT, TOWNS T,DISTRICTS D
WHERE U.ID = A.USERID AND C.ID =A.COUNTRYID AND CT.ID = A.CITYID AND
T.ID=A.TOWNID AND D.ID = A.DISTRICTID
GROUP BY U.NAMESURNAME,U.EMAIL,U.GENDER,U.BIRTHDATE,C.COUNTRY,CT.CITY,T.CITYID,D.DISTRICT,A.POSTALCODE,A.ADDRESSTEXT,T.TOWN
--GROUP BY U.NAMESURNAME
--HAVING COUNT(A.ID)>2
ORDER BY U.NAMESURNAME

--ETRADE4
--SELECT
--CT.CITY,COUNT(U.ID) AS KULLANICISAYISI,COUNT(DISTINCT D.DISTRICT) AS ILCESAYISI
--FROM
--USERS U,ADDRESS A,COUNTRIES C,CITIES CT, TOWNS T,DISTRICTS D
--WHERE U.ID = A.USERID AND C.ID =A.COUNTRYID AND CT.ID = A.CITYID AND
--T.ID=A.TOWNID AND D.ID = A.DISTRICTID
--GROUP BY CT.CITY
--ORDER BY CT.CITY

SELECT
U.NAMESURNAME,U.PASSWORD_,A.ADDRESSTEXT,C.COUNTRY,CT.CITY,T.TOWN
FROM
USERS U
JOIN ADDRESS A ON A.USERID = U.ID
JOIN COUNTRIES C ON C.ID = A.COUNTRYID
JOIN CITIES CT ON CT.ID = A.CITYID
JOIN TOWNS T ON T.ID = A.TOWNID
WHERE CT.CITY = 'YOZGAT'