>

User Login

TipidPC.com is the largest online IT Community in the Philippines. Have something to sell or share? Sign up for an account now. It's absolutely free!

Forum Topic

The SQL Thread

  • Pa OT po mga sir/mam

    Baka naman po meron sa inyo dyan na meron na training materials / documentation ng SQL Server 2008, need ko lang po.

    Sana po meron magandang loob na magbigay, bigyan ko na lang ng + feedback at saka papasko pag uwi ko dyan sa pinas :)

    TIA po
  • question po mga sir. is it possible for a user to have access to a view but not on the underlying table?

    example:

    i have a table named employees then i have a view named v_employees.

    the definition of v_employees is this:

    SELECT * FROM employees where employee_id > 1000

    now i want to give a particular login select access to v_employees but not on employees. is it possible to do it?
  • patulong naman po..

    eto po yung sample na tables and fields kada table, pero dun sa table2, kahit di ko na ilabas sa notepad yung TransactionNo

    Table1
    Date, TransactionNo, Description, Total Amount

    Table2
    TransactionNo, Items, Amount


    eto naman po yung dapat ma output ko sa .txt file

    Date, TransactionNo(1), Description, Total Amount
    Items(1), Amount(1)
    Items(2), Amount(2)
    Items(3), Amount(3)

    Date, TransactionNo(2), Description, Total Amount
    Items(1), Amount(1)
    Items(2), Amount(2)


    pwede po bang patulong mga sir, noob lang kasi ko pagdating sa script sa SQL. tsaka po pano ko sya mailalabas as tabulated form na kung ano field length ung ni-declare ko dapat ung rin ung susunding format sa .txt file. and let say pag ang declare kong length sa TransactionNo is 15, tapos meron syang actual TransactionNo na 8, ung extra na 7 eh gagawin lang nyang space? sana po may makatulong. maraming salamat po. pasensya na po talaga, noob lang talaga ko rito.
  • Hi Guys! meron lang akong tanong sa inyo. Natry ko na google ito pero hindi ko talaga makita kung ano ang sagot. I always hear my co-workers asking me if I know how to run an Orphan Script in SQL. First of all, what is this "Orphan Script" and how will I run it to work on my Database in MS SQL Server? I'm part of a team that provides phone assistance to the customers who purchase our software that stores it's database in MS SQL Server (2005, 2008, 2012 ans 2014).

    TIA!

    -- edited by cybermac on Oct 31 2014, 12:40 AM
  • Ex-DBA Here!

    @cybermac

    You might be referring to an "Orphaned Record". Nagyayari ito sa isang database na walang Referential Integrity checks. Let's say nag delete ka ng record sa Customer Table, pero merong syang "related" data na nasa ibang table na hindi kasama sa deletion process. That record is now "Orphaned".

    @amdg

    bigyan mo ng permission yung particular login mo dun sa view object mo. details here: http://technet.microsoft.com/en-us/library/ms188371(v=sql.105).aspx

    -- edited by cocoy0717 on Oct 31 2014, 05:28 AM
  • salamat po sir cocoy0717!

    Pero meron daw kasi script na kelangan run para mailagay yung isang table sa isang Database. Yun ba ang ibig sabihin ng orphan script? Yung gagamitin mo yung isang table from one Database and place it one another Database?
    At ano naman ang gagamitin ko para gumawa ng orphan script? Paano ito magrun para magconnect sa Database?

    -- edited by cybermac on Oct 31 2014, 06:53 AM
  • mga master saan kaya pwedeng magaral ng SQL Administration penge ng link site ty.
  • guys may question lang ako sa performance in execution ng 2 type of query:

    Implicit JOIN
    SELECT *
    FROM A, B
    WHERE A.column = B.column(+)

    Explicit JOIN
    SELECT *
    FROM A
    LEFT OUTER JOIN B ON A.column = B.column


    mas readable kasi sakin ang IMPLICIT JOIN (ngaun ko lang nalaman eto pala term after nagbasa-basa) compared using LEFT/RIGTH OUTER JOIN.

    almost live na kasi project and umaapaw pala prod record to 25M kaya mga query ang bagal mag extract ng data. naghahanal kami mga possible pwede optimize para mapabilis extraction ng data.

    other thing,, pag creation ba ng indexes sa isang table, dapat ba ung mga table columns na mostly gamit sa pagcompare ng data recommended i-index. meron bang impact if nag create ng maraming index sa isang table?
  • ^^^^^^
    ^meow^
  • @drunk3n, regarding your 1st question, generally mas ok performance nun implicit JOIN kasi it will only return records that matched your JOIN statement (A.column = B.column) wherein sa LEFT OUTER JOIN, it will return all records from the table in your FROM statement regardless kung may match sa nasa LEFT OUTER JOIN statement.

    About sa index, recommended yung mga columns na ginagamit sa WHERE clause & JOIN statement. Pero mas ok pa din ma confirm via SQL execution plan if nagagamit yung index sa query. Hindi din maganda kung marami index sa table kasi mag susuffer naman yung INSERT/UPDATE/DELETE operations sa table. Overhead kasi yung pag re-arrange ng mga index pag meron ka mga data manipulation sa table. So it's better to index only necessary columns and strike a balance between performance ng SELECT statements and DML statements (INSERT/UPDATE/DELETE). Lastly, better to test and compare lahat ng possible options/combinations.
  • Gooday po mga sql guru. baguhan po ako sa sql, patulong naman po kung paano solution dito using MySql update systax.

    before
    ------

    order_1
    item_code seq

    item 0
    item 0
    item 0

    order_2
    item_code seq

    item 0
    item 0
    item 0

    after
    -----

    order_1
    item_code seq

    item 1
    item 2
    item 3

    order_2
    item_code seq

    item 1
    item 2
    item 3


    Thanks po.

    -- edited by madkillah2 on Nov 19 2014, 10:17 AM
  • Hi mga bossing,

    Question lang, panu ako magsesetup ng MSSQL 2012 db na gagamitin para sa business intelligence coming from main db. pede ko bang ion yung feature ng "always on"

    Thanks
  • mga sir question saan maganda kumuha ng SQL traning courses ngayon?
  • @drunk3n
    Have you tried changing your select line from

    SELECT *

    to

    SELECT [column names]

    kahit isa isahin mo pa? I remember hearing and reading about not using asterisk dito for performance reasons especially with large datasets in MSSQL. Check mo din Execution Plan ng query mo to determine where it slows down.
  • Implicit JOIN
    SELECT *
    FROM A, B
    WHERE A.column = B.column(+)

    Explicit JOIN
    SELECT *
    FROM A
    LEFT OUTER JOIN B ON A.column = B.column


    checking the sql above, both are left join. according sa mga test ko using sql developer feature explain plan walang difference sa cost. wala din ako makita onine explaining if meron performance difference ang implicit vs explicit join.


    concentrate na ko sa INDEXing para ma-improve pag extract ng data.

    seems lahat ng tables ko may index,,, table partitioning na last option which is scope na ni DBA..
  • Gooday po mga sql guru. baguhan po ako sa sql, patulong naman po kung paano solution dito using MySql update systax.

    before
    ------

    order_1
    item_code seq

    item | 0
    item | 0
    item | 0

    order_2
    item_code seq

    item | 0
    item | 0
    item | 0

    after
    -----

    order_1
    item_code seq

    item | 1
    item | 2
    item | 3

    order_2
    item_code seq

    item | 1
    item | 2
    item | 3


    Thanks po.


    up po.

    -- edited by madkillah2 on Nov 26 2014, 07:31 AM
  • @ madkillah2

    hindi ko masyado ma-picture yung request mo. tama ba representation ko.

    table: ORDER

    id
    order_1
    order_2

    table: ORDER_LIST

    id | order_id | item
    0 order_1 0
    1 order_1 0
    2 order_1 0
    3 order_2 0
    4 order_2 0
    5 order_2 0


    GOAL:

    table: ORDER_LIST

    id | order_id | item
    0 order_1 1
    1 order_1 2
    2 order_1 3
    3 order_2 1
    4 order_2 2
    5 order_2 3


    need mo malaman mga IDs sa table ORDER_LIST para alam mo mga value ilalagay sa item column na uupdate mo.

    sample syntax:

    update order_list set item= 1 where ID = 0;
    update order_list set item= 2 where ID = 1;
    update order_list set item= 3 where ID = 2;

    -- edited by drunk3n on Nov 26 2014, 06:54 PM
  • sa mga DBA master and mga naka gamit na ng SQL DEVELOPER tool pahelp po...

    meron kasi kami dev db para sa dev, and prod db para sa prod..ehehehhe. ung dev db around 10k and prod 20M records.

    ginagamit ko yung explain plan ng sql dev para malaman yung COST nung stats. ang understanding kasi namin is lower cost faster execution and data extraction kaya eto ang INITIAL goal namin. ang tanong ko is yung cost ba ng 10k records will be the same sa cost ng 20M records.

    sa test ko kasi sa dev vs prod db magkaiba ang cost ng same query.. ang difference lang is number of records 10k vs 20m and ang mga indexes ng mga tables.
  • ask ko lang po posible ba na ma update ko yun table structure from database to another database? na di mawawala yun laman ng table?
  • 1. gawa ka ng table na me structure na gusto mo
    2. import mo laman ng old table dun sa new table
    3. rename mo yung old table. append ka ng "_old" sa name nya
    4. rename mo yung new table to the name of old table
  • Sana meron po familiar sa inyo sa informix. Question lang po, if ang isang user is nag unload ng file at yung naming convention na ginamit for the unload file is existing at na execute, ma ooverwrite po ba yung existing na unload file o magkakaroon ba ng copy?

    And in case ma overwrite nga yung unload file, is there a way para ma recover sya

    Thanks!

    -- edited by hatake_kakashi on Dec 17 2014, 05:29 PM

Who's Online

775 active users within the last minute, 416 members, 359 guests.
Our newest member is wizdomjv
Click here to see online members.

Browse Items

More »

Search TipidPC


New Want to Buys

Active Items for Sale

Active Want to Buys