SQL special thread

[mathsjery]

Tujifunze namna ya kutumia SQL katika nyanja mbalimbali za programming.

Any one can be a teacher or a student.

Any tips and tricks is allowed? Any question base on this topic is allowed?

You can use any local server for your best practices.(xampp, wamp etc)

Let us start.

 

[racka98]

Mzee wewe ni Android dev?

 

[mathsjery]

Mzee wewe ni Android dev?

Specialized Web app developer

 

[racka98]

Specialized Web app developer

Aha. Ni vizuri. Mm bado najifunza aisee. Lkn hasa kwny Android na Flutter for multiplatform

 

[Upepo wa Pesa]

Hivi kuna haja gani ya kutumia au kuandika raw SQL wakati modeni frameworks kama django na laravel zinatumia ORM ambazo unaandika simple lines of codes then inakutengenezea table saaafi kabisa!!

Je kuna haja ya kusoma/kutumia raw sql kweli?

 

[mathsjery]

Hivi kuna haja gani ya kutumia au kuandika raw SQL wakati modeni frameworks kama django na laravel zinatumia ORM ambazo unaandika simple lines of codes then inakutengenezea table saaafi kabisa!!

Je kuna haja ya kusoma/kutumia raw sql kweli?

Swali Zuri Sana, I have some important points here;

ISSSUE YA PERFOMANCE
ORM na raw SQL zinatofautiana kwa kutegemea na project unayofanya in uzito gani, mfano issue ya perfomance ORM iko slow sana kuliko raw SQL kwenye execution ya data kwa karibia oparation zote i.e select, update, insert etc. kwa sababu data ORM ina badili raw zote kuwa objects ndo ikupe data wakati SQL infanya direct from/to rows/tuples.

Kama hapa haujanisoma, natoa mfano

Tengeneza database kisha tumia raw SQL kuweka data pima muda uliotumika kuweka data kwenye table.
Tengeneza database kisha tumia ORM kuweka data huko kisha cheki muda uliotumika.
Unaweza rudia rudia kwa opration zingine ili uweze kuona perfomance husika na hakikisha unatumia idadi ya data sawa na envronment sawa. Utaona maajabu ya row SQL.

Hapo hakuna cha ajax wala vue js n.k

Nenda kapime mwenyewe uone au mimi nitakupimia pia kama utataka.

ISSUE YA DEVELOPMENT
ORM iko vizuri kwani ni simple kuliko raw SQL kwa kuwa kuna baadhi ya query unaweza tumia line nyingi kukamilisha unachokitaka kama ukitumia raw SQL, wakati ukitumia ORM ni faster tu umemaliza kuandika line hizo.

ISSUE YA COMPLEXITY
Hakika kuna complex query ambazo ni ngumu mno kutumia ORM mfano query zinazo join table nyingi kwa wakati mmoja na unatakiwa upate data tofauti with their details hapo ongeza alias,sasa ukitumia ORM utahenya, but raw SQL can act accordingly mara nyingi hizi complex zinatumia kuretrieve data kwenye table nyingi at the same time cheki issue ya search engine utaelewa.


But all in al chagua kinachostahili wakati unaostahili kwa kuwa kuna mifumo mingine lazima ijumuishe both ORM na raw SQL.na mwisho wa yote lazima ujifunze kwanza SQL ndo ufuatilie ORM na ni sawa na kusoma python first kisha usome django au php kisha Laravel kiufupi lazima uchague vitu kwa wakati sahii.

 

[mathsjery]

When do you apply SQL UNION or UNION ALL and why?
Can you give out some examples?
Can you add a LIKE and WHERE clauses in your statement which contain UNION or UNION ALL?

 

[Cybergates]

Django

 total_price = Order.objects.filter(
        buyer = user, terminate = False,
        accepted = True, shipping = True, seller__profile__professinal = True).aggregate(total_price=Sum( F('quantity') * F('product__price'), output_field=FloatField()))
    
    total_price = total_price['total_price']
    if total_price == None:
        total_price = 0

 

[Upepo wa Pesa]

Swali Zuri Sana, I have some important points here;

ISSSUE YA PERFOMANCE
ORM na raw SQL zinatofautiana kwa kutegemea na project unayofanya in uzito gani, mfano issue ya perfomance ORM iko slow sana kuliko raw SQL kwenye execution ya data kwa karibia oparation zote i.e select, update, insert etc. kwa sababu data ORM ina badili raw zote kuwa objects ndo ikupe data wakati SQL infanya direct from/to rows/tuples.

Kama hapa haujanisoma, natoa mfano

Tengeneza database kisha tumia raw SQL kuweka data pima muda uliotumika kuweka data kwenye table.
Tengeneza database kisha tumia ORM kuweka data huko kisha cheki muda uliotumika.
Unaweza rudia rudia kwa opration zingine ili uweze kuona perfomance husika na hakikisha unatumia idadi ya data sawa na envronment sawa. Utaona maajabu ya row SQL.

Hapo hakuna cha ajax wala vue js n.k

Nenda kapime mwenyewe uone au mimi nitakupimia pia kama utataka.

ISSUE YA DEVELOPMENT
ORM iko vizuri kwani ni simple kuliko raw SQL kwa kuwa kuna baadhi ya query unaweza tumia line nyingi kukamilisha unachokitaka kama ukitumia raw SQL, wakati ukitumia ORM ni faster tu umemaliza kuandika line hizo.

ISSUE YA COMPLEXITY
Hakika kuna complex query ambazo ni ngumu mno kutumia ORM mfano query zinazo join table nyingi kwa wakati mmoja na unatakiwa upate data tofauti with their details hapo ongeza alias,sasa ukitumia ORM utahenya, but raw SQL can act accordingly mara nyingi hizi complex zinatumia kuretrieve data kwenye table nyingi at the same time cheki issue ya search engine utaelewa.


But all in al chagua kinachostahili wakati unaostahili kwa kuwa kuna mifumo mingine lazima ijumuishe both ORM na raw SQL.na mwisho wa yote lazima ujifunze kwanza SQL ndo ufuatilie ORM na ni sawa na kusoma python first kisha usome django au php kisha Laravel kiufupi lazima uchague vitu kwa wakati sahii.

But how about sql injections attacks when it comes to ORM and raw sql queries?

 

[mathsjery]

But how about sql injections attacks when it comes to ORM and raw sql queries?

It depends on how you are going to write your code.

Na ndio maana tunapaswa kufanya validation ya Kila kinachoingia kwenye Database husika iwe tumetumia raw sql ama ORM.

Kwa hiyo usiruhusu data yoyote kuingia bila kufanya validation eti kwa kuwa unatumia ORM. Kumbuka hizi ni package tu na zina version hivyo hivyo kwa raw SQL.

Hivyo hivyo remember the prepared statement in raw SQL ambayo ndio inapunguza posibility ya sql injection bado inahitaji some adjustments.

So there is no simple answer, labda kama tunaandika app ya assignment chuoni na sio business based.

 

[mathsjery]

Django

 total_price = Order.objects.filter(
        buyer = user, terminate = False,
        accepted = True, shipping = True, seller__profile__professinal = True).aggregate(total_price=Sum( F('quantity') * F('product__price'), output_field=FloatField()))
  
    total_price = total_price['total_price']
    if total_price == None:
        total_price = 0

Mh, add some extra description

 

[sele255]

Hivi kuna haja gani ya kutumia au kuandika raw SQL wakati modeni frameworks kama django na laravel zinatumia ORM ambazo unaandika simple lines of codes then inakutengenezea table saaafi kabisa!!

Je kuna haja ya kusoma/kutumia raw sql kweli?

Kujua raw SQL ni muhimu Kama utahitaji kuwa professional developer,maana sio Kila project utayofanya au kukutana nayo utatumia framework

 

[sele255]

It depends on how you are going to write your code.

Na ndio maana tunapaswa kufanya validation ya Kila kinachoingia kwenye Database husika iwe tumetumia raw sql ama ORM.

Kwa hiyo usiruhusu data yoyote kuingia bila kufanya validation eti kwa kuwa unatumia ORM. Kumbuka hizi ni package tu na zina version hivyo hivyo kwa raw SQL.

Hivyo hivyo remember the prepared statement in raw SQL ambayo ndio inapunguza posibility ya sql injection bado inahitaji some adjustments.

So there is no simple answer, labda kama tunaandika app ya assignment chuoni na sio business based.

Kuongeza hapo kwenye SQL injection lazima tufanye validation kwenye client(browser/app) kisha tufanye validation kwenye server(kwa kutumia backend lang uliotumia) kisha tuweze ku-bind parameters kabla ya excution sql statements hapo ndio tutaepuka sql injections

 

[Mama Debora]

Nafurahi sana napoona vijana/wakaka/masela wanadiscuss ku-push maujuzi kwenye field niliopo.

Sio kila siku tutegemee stack-overflow, tutorials point, github etc.

Kuna maswali mengine mfano stack-overflow ukiandika kwanza wanakushangaa.. Yaani wanakuona una utoto, hujui kitu au sijui aje yani.

Tofauti na ukiwa una discuss na wabongo wenzio. Mnakua very friendly.

 

[mathsjery]

Nafurahi sana napoona vijana/wakaka/masela wanadiscuss ku-push maujuzi kwenye field niliopo.

Sio kila siku tutegemee stack-overflow, tutorials point, github etc.

Kuna maswali mengine mfano stack-overflow ukiandika kwanza wanakushangaa.. Yaani wanakuona una utoto, hujui kitu au sijui aje yani.

Tofauti na ukiwa una discuss na wabongo wenzio. Mnakua very friendly.

let's push the tyre, unasemaje kuhusu hili swali

When do you apply SQL UNION or UNION ALL and why?
Can you give out some examples?
Can you add a LIKE and WHERE clauses in your statement which contain UNION or UNION ALL?

 

[Mama Debora]

let's push the tyre, unasemaje kuhusu hili swali

Mkuu niwe honesty... Sina constructive idea katika swali hili.

 

[mathsjery]

Mkuu niwe honesty... Sina constructive idea katika swami hili.

okay

 

[mathsjery]

When do you apply SQL UNION or UNION ALL and why?

SQL UNION na SQL UNION ALL, hizi ni aina mbili za operator zinazotumika kwa nyakati tofauti tofauti unapokuwa na table Zaidi ya moja na unataka kufetch(select) ama kuchukua data kutoka kwenye table hizo kwa wakati mmoja.
Tofauti ya UNION na UNION ALL ni pale unapotaka kuchukua data husika katika table hizo, kama unataka kuchukua data zisizojirudia rudia(duplicated data) basi utatumia UNION, lakini kama ni kinyume chake tumia UNION ALL.

Sheria:
Unapotumia operator hizi hakikisha table zako zina column sawa, lasivyo utatatakiwa kuspecify ni column zipi unataka kuchukua na lazima ziwe sawa kwa idadi kwa kila table.

Unapotaka kuzitawanya table ulizo chukua siku zote tumia 'alias' kwa kuwa table zikitumia operator hizi basi tambua ya kuwa column zilizoko kwenye table ya pili zitatumia column za upande wa kwanza.

Ninaposema column maana yake ni attributes.

Operator hizi utumike mara kwa mara kwenye search engine ya website au app yoyote inayochukua data kutoka kwenye table zaidi ya moja mfano mzuri ni website ama software zinazokubali kusearch vitu tofauti tofauti kwenye search engine yake.

Mfano mzuri: huwezi kutumia join oparator kutengeneza search engine kwa kuwa kuna column zinaweza kuachwa na hiyo ndo sababu ya kwanini tutumie oparator hizo tunapo taka data kutoka sehemu mbalimbali kwa wakati mmoja.

 

[IamMrLiverpool]

Wakuu ipi ni IDE nzuri ya kutumia kwenye sql tofauti na pop sql

 

[mathsjery]

Wakuu ipi ni IDE nzuri ya kutumia kwenye sql tofauti na pop sql

Ukiwa na lengo lipi? tuanzie hapo