AB halduse konspekt

AB kasutajate rollid
 -AB kasutaja - tavakasutaja, kes saati muuta, lisada, filtreerida ja otsida vastavalt v  vajadusele
 -AB programmeerija -loob funktsioonid ja protseduurid(trigerid)
 -DBA -AB administraator - tagab, et õiged kasutajad saavad oma õigused
 -AB Koostaja - loob andmete tabelid
DBA -AB administratoor liigid:
*süsteemiadmin
*AB arhitekt
*AB analüütik
*Data warehouse admin - хранилище данных

1. Kasutajate tootus 
2. AB turvalisus  ja 
AB turvalisus - 3 põhiaspekti
*andmete konfidentsiaalsus ()
    
    
*andmete kättesaadavus - доступность - 
*andmete terviklikkus() - целостность -
RiskOhustuse aspekt
Inimlikud vead
füüsilised vead (ristvara)
COMMIT
ROLLBACK
ROLLFORWARD
Data Mining –поиск скрытых шаблонов – метод …
Data Warehouse –Хранилище данных
  • GROUP by
  • UNION ALL
  • GROUPING
  • ROLLUP
  • CUBE
  • Erinevus ROLLUP ja CUBE – ROLLUP summerib hierathilselt valitud veerud, CUBE arvutab summad igas veerus/kombinatsioonis.

GROUP by

Select Country, Gender, Sum (Salary) as TotalSalary
From Employees
Group By Country, Gender

UNION ALL

Select Country, Gender, Sum (Salary) as TotalSalary
From Employees
Group By Country, Gender

UNION ALL

GROUPING

Select Country, Gender, Sum (Salary) TotalSalary
From Employees
Group BY
GROUPING SETS
(
	(Country, Gender), -- Sum of Salary by Country and Gender
	(Country),		   -- Sum of Salary by Country
	(Gender),		   -- Sum of Salary by Gender
	()				   -- Grand Total
)

ROLLUP

//näide 1
SELECT Country, SUM (Salary) AS TotalSalary
FROM Employees

GROUP BY ROLLUP (Country)

--The above query can also be rewritten as shown below
SELECT Country, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country WITH ROLLUP

--We can also use UNION ALL operator along with GROUP BY
SELECT Country, SUM (Salary) AS TotalSalary
FROM Employees
GROUP BY Country

UNION ALL

SELECT NULL, SUM (Salary) AS TotalSalary
FROM Employees

--We can also use Grouping Sets to achieve the same result
SELECT Country, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY GROUPING SETS
(
	(Country),
	()
)

//näide 2
SELECT Country, Gender, SUM (Salary) AS TotalSalary FROM Employees
GROUP BY ROLLUP (Country, Gender)
--OR
SELECT Country, Gender, SUM (Salary) AS TotalSalary
FROM Employees
GROUP BY Country, Gender WITH ROLLUP
--Kasutan UNION ALL koos GROUP BY-ga:
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country, Gender
UNION ALL
SELECT Country, NULL, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country
UNION ALL
SELECT NULL, NULL, SUM(Salary) AS TotalSalary FROM Employees

CUBE

SELECT Country, Gender, SUM (Salary) AS TotalSalary
FROM Employees
GROUP BY Cube (Country, Gender)
--OR
SELECT Country, Gender, SUM (Salary) AS TotalSalary
FROM Employees
GROUP BY Country, Gender with Cube