Database assignment

Q2 A) select​ ​max(a.salary),​ ​min(a.salary),​ ​count(a.agent_id)​ ​from​ ​agent​ ​a; ​ ​​ ​max​ ​​ ​​ ​|​ ​​ ​min​ ​​ ​|​ ​count --------+-------+------- ​ ​366962​ ​|​ ​50008​ ​|​ ​​ ​​ ​662 (1​ ​row) B) select​ ​((c.min_sal_rows​ ​-​ ​75000)/(-(b.max​ ​-​ ​b.min)/b.count))​ ​as​ ​estimated​ ​from​ ​(select max(a.salary),​ ​min(a.salary),​ ​count(a.agent_id)​ ​from​ ​agent​ ​a)​ ​as​ ​b,​ ​(select​ ​count(*)​ ​as min_sal_rows​ ​from​ ​agent​ ​where​ ​agent.salary​ ​<​ ​75000)​ ​as​ ​c; ​ ​estimate ---------- 156 ((#Rows​ ​of​ ​salaries​ ​less​ ​75000)​ ​-​ ​75000)​ ​/​ ​-((max​ ​salary)​ ​-​ ​(min​ ​salary))​ ​/​ ​(total​ ​#​ ​of​ ​rows) C) f17tdb47=>​ ​select​ ​(b.max​ ​-​ ​b.min)*.5​ ​fiftieth​ ​from​ ​(select​ ​max(a.salary),​ ​min(a.salary), count(a.agent_id)​ ​from​ ​agent​ ​a)​ ​as​ ​b; ​ ​​ ​​ ​fiftieth ---------- ​ ​158477.0 (1​ ​row) f17tdb47=>​ ​select​ ​(b.max​ ​-​ ​b.min)*.25​ ​as​ ​twentyfifth​ ​from​ ​(select​ ​max(a.salary),​ ​min(a.salary), count(a.agent_id)​ ​from​ ​agent​ ​a)​ ​as​ ​b; ​ ​​ ​​ ​twentyfifth ---------- ​ ​79238.50 (1​ ​row) f17tdb47=>​ ​select​ ​(b.max​ ​-​ ​b.min)*.75​ ​as​ ​seventyfifth​ ​from​ ​(select​ ​max(a.salary),​ ​min(a.salary), count(a.agent_id)​ ​from​ ​agent​ ​a)​ ​as​ ​b;

seventyfifth ----------- ​ ​237715.50 (1​ ​row) ------

D) f17tdb47=>​ ​select​ ​((75000​ ​-​ ​b.min)/(b.count*.25))​ ​as​ ​estimated​ ​from​ ​(select​ ​max(a.salary), min(a.salary),​ ​count(a.agent_id)​ ​from​ ​agent​ ​a)​ ​as​ ​b; estimated ---------------------- ​ ​151.0090634441087613 (1​ ​row) E) f17tdb47=>​ ​select​ ​count(*)​ ​from​ ​agent​ ​a​ ​where​ ​a.salary​ ​<​ ​75000 f17tdb47->​ ​; ​ ​count ------- ​ ​​ ​​ ​427 (1​ ​row) Q4 Advantages​ ​of​ ​Multiple​ ​Log​ ​Files: The​ ​advantage​ ​of​ ​the​ ​multiple​ ​log​ ​files​ ​is​ ​that​ ​it​ ​can​ ​only​ ​be​ ​to​ ​an​ ​advantage​ ​if​ ​there​ ​are​ ​multiple file​ ​groups​ ​because​ ​in​ ​this​ ​case​ ​there​ ​will​ ​be​ ​separation​ ​of​ ​the​ ​read​ ​only​ ​data​ ​and​ ​also partitioning​ ​can​ ​be​ ​done​ ​equally.Hence,​ ​it​ ​is​ ​good​ ​with​ ​multiple​ ​file​ ​groups.Indexing​ ​on​ ​the different​ ​file​ ​groups​ ​is​ ​the​ ​only​ ​advantage. Disadvantages​ ​of​ ​Multiple​ ​Log​ ​Files: As​ ​log​ ​files​ ​will​ ​be​ ​written​ ​sequentially​ ​hence,​ ​they​ ​are​ ​of​ ​no​ ​use​ ​when​ ​not​ ​multiple​ ​file​ ​gorups​ ​as they​ ​will​ ​be​ ​clustering​ ​indexes​ ​and​ ​hence,​ ​making​ ​the​ ​recovery​ ​process​ ​tough​ ​than​ ​earlier. Hence,​ ​these​ ​are​ ​the​ ​advantages​ ​and​ ​disadvantages​ ​of​ ​the​ ​multiple​ ​log​ ​file.