SQL somo la 16: Jinsi ya kuandaa matokeo ya Mtihani kwa kutumia database

Kuweka poston kwa kila somo:

Sasa tutaanza na kutafuta position ya mwanafunzi kwa kila somo. Pia tutamuwekea Daraja lake.

Hapa tunakwenda kutumia kanuni nyingine. Kwanza tutatengeneza table alias ambayo tutanaka kuitengenezea position. Kwa mfano tutaanza na mtihani wa kusoma. KWa kuwa tayar tunajuwa jinsi ya kukokotoa Daraja yaan Grade hapo sitazungumznia. 

Kuhusu position kwanza tutatengeneza table alias. badala ya luita kusoma yutaipa alias Reading. Kisha ili tuweze kutafuta positio kwa simo husika hapa hatutatumia rank() over(). HApa tutatumia mfummo mwingine wa ku count, na kulinganisha alama.

(

    SELECT COUNT(kusoma)

    FROM majibu

    WHERE (kusoma > reading)

  ) AS kus_pafasi,

utaona hapo kwanz atime count row zinazohuscolumn ya kusoma, kwa kuangalia maksi kubwa. HApo itaangalia yenye maksi kubwa ndio inatangulia. mfano code hizo zitakupa matokeo haya

Changamoto ya kwanz ahapo utangunduwa kuw aimeanza kuhesabu kutoka 0 kwenda juu. sasa ili tuanzie moja tutaongeza namba moja hapo kwenye ku count

Sasa ngoja tuongeze na column ya grade. Lengo tuwe na somo, grade na position .

SELECT DISTINCT(concat(jina_la_kwanza, "  ", jina_la_pili)) as jina, kusoma as Reading,

CASE 

     WHEN kusoma > 40 THEN 'A'

     WHEN kusoma > 30 AND kusoma <41 THEN 'B'

     WHEN kusoma > 20 AND kusoma < 31 THEN 'C'

     WHEN kusoma > 10 AND kusoma < 20 THEN 'D'

     ELSE 'F'

END as GRADE,

(

    SELECT COUNT(kusoma) + 1

    FROM majibu

    WHERE (kusoma > reading)

  ) AS Rank,

kuandika, kuhesabu, (kusoma + kuandika + kuhesabu) as jumla, round(((kusoma + kuandika + kuhesabu)/3),1) as wastani FROM majibu;

Sasa ngoja tufanye hivyo kwa masomo yote yaliobakia.

SELECT DISTINCT(concat(jina_la_kwanza, "  ", jina_la_pili)) as jina, kusoma as Reading, 

CASE 

     WHEN kusoma > 40 THEN 'A'

     WHEN kusoma > 30 AND kusoma <41 THEN 'B'

     WHEN kusoma > 20 AND kusoma < 31 THEN 'C'

     WHEN kusoma > 10 AND kusoma < 20 THEN 'D'

     ELSE 'F'

END as GRADE,

(

    SELECT COUNT(kusoma) + 1

    FROM majibu

    WHERE (kusoma > reading)

  ) AS Rank,

kuandika as writing,

CASE 

     WHEN kuandika > 40 THEN 'A'

     WHEN kuandika > 30 AND kuandika <41 THEN 'B'

     WHEN kuandika > 20 AND kuandika < 31 THEN 'C'

     WHEN kuandika > 10 AND kuandika < 20 THEN 'D'

     ELSE 'F'

END as GRADE,

(

    SELECT COUNT(kuandika) + 1

    FROM majibu

    WHERE (kuandika > writing)

  ) AS Rank,

kuhesabu as arthmetics,

CASE 

     WHEN kuhesabu > 40 THEN 'A'

     WHEN kuhesabu > 30 AND kuhesabu <41 THEN 'B'

     WHEN kuhesabu > 20 AND kuhesabu < 31 THEN 'C'

     WHEN kuhesabu > 10 AND kuhesabu < 20 THEN 'D'

     ELSE 'F'

END as GRADE,

(

    SELECT COUNT(kuhesabu) + 1

    FROM majibu

    WHERE (kuhesabu > arthmetics)

  ) AS Rank,

(kusoma + kuandika + kuhesabu) as jumla, round(((kusoma + kuandika + kuhesabu)/3),1) as wastani FROM majibu;

Sasa tunakwenda kumalizia na kuweka position ya ujumla, kw akuangalia jumla ya asomo yake yote.

SELECT DISTINCT(concat(jina_la_kwanza, "  ", jina_la_pili)) as jina, kusoma as Reading, 

CASE 

     WHEN kusoma > 40 THEN 'A'

     WHEN kusoma > 30 AND kusoma <41 THEN 'B'

     WHEN kusoma > 20 AND kusoma < 31 THEN 'C'

 ...