SoftUni_MS_SQL/Incomes-Database.sql at master · Dimitrov-S-Dev/SoftUni_MS_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE DATABASE Incomes

USE Incomes

 CREATE TABLE DailyIncome(
 VendorId NVARCHAR(10),
 IncomeDay NVARCHAR(10),
 IncomeAmount INT
 )

INSERT INTO DailyIncome VALUES ('SPIKE', 'FRI', 100)
INSERT INTO DailyIncome VALUES ('SPIKE', 'MON', 300)
INSERT INTO DailyIncome VALUES ('FREDS', 'SUN', 400)
INSERT INTO DailyIncome VALUES ('SPIKE', 'WED', 500)
INSERT INTO DailyIncome VALUES ('SPIKE', 'TUE', 200)
INSERT INTO DailyIncome VALUES ('JOHNS', 'WED', 900)
INSERT INTO DailyIncome VALUES ('SPIKE', 'FRI', 100)
INSERT INTO DailyIncome VALUES ('JOHNS', 'MON', 300)
INSERT INTO DailyIncome VALUES ('SPIKE', 'SUN', 400)
INSERT INTO DailyIncome VALUES ('JOHNS', 'FRI', 300)
INSERT INTO DailyIncome VALUES ('FREDS', 'TUE', 500)
INSERT INTO DailyIncome VALUES ('FREDS', 'TUE', 200)
INSERT INTO DailyIncome VALUES ('SPIKE', 'MON', 900)
INSERT INTO DailyIncome VALUES ('FREDS', 'FRI', 900)
INSERT INTO DailyIncome VALUES ('FREDS', 'MON', 500)
INSERT INTO DailyIncome VALUES ('JOHNS', 'SUN', 600)
INSERT INTO DailyIncome VALUES ('SPIKE', 'FRI', 300)
INSERT INTO DailyIncome VALUES ('SPIKE', 'WED', 500)
INSERT INTO DailyIncome VALUES ('SPIKE', 'FRI', 300)
INSERT INTO DailyIncome VALUES ('JOHNS', 'THU', 800)
INSERT INTO DailyIncome VALUES ('JOHNS', 'SAT', 800)
INSERT INTO DailyIncome VALUES ('SPIKE', 'TUE', 100)
INSERT INTO DailyIncome VALUES ('SPIKE', 'THU', 300)
INSERT INTO DailyIncome VALUES ('FREDS', 'WED', 500)
INSERT INTO DailyIncome VALUES ('SPIKE', 'SAT', 100)
INSERT INTO DailyIncome VALUES ('FREDS', 'SAT', 500)
INSERT INTO DailyIncome VALUES ('FREDS', 'THU', 800)
INSERT INTO DailyIncome VALUES ('JOHNS', 'TUE', 600)

SELECt * FROM DailyIncome

SELECT * FROM DailyIncome
PIVOT (
	AVG (IncomeAmount) FOR IncomeDay
		IN ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])
	  )
	 AS AvgIncomePerDay