SQL special thread

mathsjery

mathsjery

JF-Expert Member
Sep 26, 2015
2,208
1,755
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.
 
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?
 
Upepo wa Pesa said:
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?
Click to expand...
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.
 
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?
 
Django

Python: 
 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
 
mathsjery said:
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.
Click to expand...
But how about sql injections attacks when it comes to ORM and raw sql queries?
 
Upepo wa Pesa said:
But how about sql injections attacks when it comes to ORM and raw sql queries?
Click to expand...
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.
 
Cybergates said:
Django

Python: 
 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
Click to expand...
Mh, add some extra description
 
Upepo wa Pesa said:
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?
Click to expand...
Kujua raw SQL ni muhimu Kama utahitaji kuwa professional developer,maana sio Kila project utayofanya au kukutana nayo utatumia framework
 
mathsjery said:
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.
Click to expand...
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
 
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.
 
Mama Debora said:
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.
Click to expand...
let's push the tyre, unasemaje kuhusu hili swali
mathsjery said:
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?
Click to expand...
 
mathsjery said:
When do you apply SQL UNION or UNION ALL and why?
Click to expand...
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.
 
You must log in or register to reply here.

Similar Discussions

Back
Top Bottom
Forums
New posts
Post thread
Back