LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/Khasi

From Script | Spoken-Tutorial
Revision as of 11:59, 26 May 2017 by Pdiangburom (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Time Narration
00:02 Khublei ngi pdiangsngewbha iaphi sha ka “Spoken tutorial” halor ka “LibreOffice Base”.
00:06 Ha kane ka jinghikai, ngin ia pule kumno ban:
00:10 Thoh ia ki queries ha ka “SQL View”
00:13 Pyndonkam ia ka “ORDER BY” clause
00:15 Pyndonkam “JOINS”
00:17 Pyndonkam “Aggregate functions”
00:19 Pyndonkam “GROUP BY” clause
00:21 * Bad ban pyndonkam ia ka built in “Function”s.
00:26 To ngin ia pule kham bun shaphang ban thoh ia ka SQL queries.
00:31 Na ka bynta kane, to ngin ia plie ia ka “Library” database jong ngi.
00:36 Mynta, to ngin nion ha ka “Queries” lis ha ka panel ba sha kadiang
00:42 Bad nangta nion ha “Create Query in SQL View”.
00:49 Nyngkong, to ngin peit kumno ngi lah ban sort ia ki results jong ka query.
00:55 Bad to ngin ia khmih ia ka nuksa harum:
00:59 Sei ia ki kyrteng jong ki Book bad ka jingtip jong u nongthoh na ka bynta baroh ki kot ba la pynmih da ka Cambridge lane Oxford
01:09 Bad pynbeit ryntih ia ki da ka “publisher” ha ka order ba kiew, bud da ka “book titles” ha ka order ba kiew.
01:19 Bad hangne ka query:
01:22 SELECT Publisher, Title, Author
01:28 FROM Books
01:31 WHERE Publisher IN ( 'Cambridge', 'Oxford')
01:42 ORDER BY Publisher ASC, Title ASC
01:50 Te, phin iohi ba ngi la pyndonkam ia ka “ORDER BY” clause ban pynthikna ia ka sorting ha ka kyrteng ki column.
01:58 Bad ruh phin iohi ia ki comma characters kiba iarap ban pyndap shibun ki column na ka bynta ka sorting
02:05 Bad ban pynthikna ia ka order ba kiew lane ba hiar, ngi lah ban shu type ‘A S C’ lane ‘D E S C’ marbud ia man ka kyrteng jong ki column.
02:19 Mynta, to ngin ia nion ha ka “Run Query” icon hapoh u file menu bar.
02:26 Hangne kidon ki books, kiba lah pynbeit ryntih nyngkong da u “Publisher” bad nangta da ka “book title.
02:34 Mynta, ngin leit sha ka query kaba bud.
02:38 To mynta ngin ia pynioh ia ki lis jong ki kyrteng ki kot kiba khlem pat pynphai da ki member, ryngkat bad ki tarik ba lah ai ia ki kot.
02:48 Namar ba ki kyrteng kidon ha ka “Books” table bad Book Issue date ha ka “BooksIssued” table,
02:55 Ngin sa donkam ban pyniasnoh lang ia kine ar ha kano kano ka rukom.
03:00 Te, ngin ia pyndonkam ia u “JOIN” keyword, ban pyniasnoh ia kine ar tylli ki tables
03:07 Bad ngin ia pyndonkam ia u column uba kham paw, “BookId”, ban pyniadei ia kine ki ar tylli ki tables.
03:14 Te ka query kadei:
03:17 SELECT B.title, I.IssueDate, I.Memberid

FROM Books B JOIN BooksIssued I

03:35 ON B.bookid = I.BookId

WHERE CheckedIn = FALSE

03:48 Phin iohi ia ki dak ‘B’ bad ‘I’ ha ka “FROM” clause.
03:55 Ia kine la khot “Aliases” kiba lah ban dei ki alphabet kiba jrong lane tang iwei na ka bynta ban suk ban pule.
04:06 Phin iohi ba ka “BookId” column kadon ha baroh ar ki table”s.
04:11 Te, ngin ia pyndonkam ia u “aliase”s ban pynthikna lane ban jied bad phiah ia ki kyrteng ki colum ban kiar na ki jingkulmar
04:21 Te, mynta phin iohi ba ngi lah pyniasnoh ia ar tylli ki tables da kaba pyndonkam da ka “JOIN” keyword ha ka “FROM” clause
04:31 Bad ngi lah pynthikna ia ka “BookId” column na ka bynta ban join da kaba thoh:

ON B.bookid = I.BookId

04:46 Te, to ngin “run” mynta ia ka query jong ngi
04:49 Bad ngi iohi ia ka lis jong baroh ki kot bad ka issue date bad phin iohi ba ka “CheckedIn” status khlem pat checked in.
04:59 Te, phin iohi ruh ba ngi iohi tang ia ka “MemberId” ha ka results. Kam da long donkam, dei ne m ?
05:08 Te, kumno ngin pyni ia ka kyrteng jong ki member kaba don ha ka member table?
05:15 Da kaba suk. Ngi “JOIN” ia ka “members” table ha ka query jong ngi kumne harum:
05:21 SELECT B.Title, I.IssueDate, I.MemberId, M.Name

FROM Books B

05:37 JOIN BooksIssued I ON B.BookId = I.BookId

JOIN Members M ON I.MemberId = M.MemberId

05:58 WHERE CheckedIn = FALSE
06:02 Te, phin iohi ia ka jingpyniasnoh ba ar jong ka “Members” table bad ka “MemberId” column ba lah pyndonkam na ka bynta ban pyniasnoh.
06:12 To ngin ia “run” ia ka query.
06:14 Hangne ki kyrteng jong ki member ryngkat bad ki kot ba lah ai sha ki.
06:20 Kaba bud, to ngin ia pule shaphang ka aggregates bad grouping.
06:26 Kumno ngin ioh jingkhein ia baroh ki member ha ka library?
06:31 Hangne ka query:
06:34 SELECT COUNT(*) AS "Total Members"

FROM Members

06:47 Te hangne, phin iohi ia ka “COUNT”.
06:51 Ia kane la khot ka “aggregate function” namar ba ka pynphai tang ia uwei u value da kaba evaluate ia ka set jong ka “record”s.
07:02 Bad ruh ngi lah pyndap ia ka “Alias”- ‘Total Members’.
07:07 Mynta, to ngin “run” ia ka query.
07:10 Te hangne, ka “Base” ka lah dep evaluate ia ka record jong baroh 4 ngut ki member bad pynphai ia u numbar 4 uba dei u jingkhein jong baroh ki members
07:22 Katto katne ki nuksa jong ka aggregate functions kidei “SUM, MAX” bad “MIN”.
07:30 Mynta, to ngin ia pule shaphang ka jingtip jong ka grouping.
07:36 Kumno ngin ioh ia ka jingkhein jong ki kot ba la pynmih da man ki publisher?
07:40 Hangne kadei ka query:
07:43 SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books

GROUP BY Publisher ORDER BY Publisher

08:03 Phin iohi ia ka “GROUP BY” clause ba thymmai.
08:06 Te, ngi lah jied ia ka “Publisher” bad ka numbar jong ki kot bad ka “GROUP BY” clause ban group ia ka records jong man ki Publisher.
08:18 To ngin “run” ia ka query mynta.
08:21 Phin iohi ia ka kyrteng jong ki publisher bad ka numbar jong ki kot da man ki publisher hajan jong ki.
08:33 Hadien kane, to ngin pule shaphang kumno ban pyndonkam ia ka “Functions” ha ka “SQL”.
08:38 Functions kidei ki statements kiba pynphai tang uwei u value.
08:43 Kum ka nuksa, “CURRENT_DATE” ka pynphai ia ka tarik ba mynta ka sngi.
08:49 Te, to ngin ia lis ia baroh ki kyrteng jong ki kot kiba lah dei ban pynphai da ki members.
08:56 Bad ka query kadei:
08:58 SELECT B.Title, I.IssueDate, I.ReturnDate
09:08 FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId
09:21 WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE
09:31

Te, phin iohi ia ka jingdonkam bad treikam jong ka “CURRENT_DATE” function.

09:36 Ngi ia sei ia ka kyrteng ki kot kiba ka Return Date kadei shwa jong ka tarik ba mynta ka sngi.
09:43 To ngin “run” ia ka query.
09:45 Bad, hangne ki kot kiba lah dei ban pynphai.
09:51 Na ka bynta ki lis jong ki function kiba ka HSQLdb ka ai: leit sha ka http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html
10:23 Baroh ki user guide lah ban ioh da kaba leit sa ka website:
10:29 http://www.hsqldb.org/doc/2.0/guide/
10:48 Hangne ka kam na ka bynta jong phi:
10:50 Thoh bad tes ia ki SQL queries jong phi na ka bynta kane harum:
10:55 1. Ban ioh ia ka jingkhein jong baroh ki kot hapoh ka Library.
10:58 2. Ban ioh ia ka jingkhein jong ki kot ba la thoh da man ki nongthoh.
11:03 3. Ban ioh ia ka list jong ki kyrteng ki member bad phone numbar jong ki kiba donkam ban pynphai ia ki kot mynta ka sngi.
11:11 4. Batai kaei kaba kane ka query kaleh?

SELECT (price) AS "Total Cost of Cambridge Books"

11:24 FROM Books

WHERE publisher = 'Cambridge'

11:32 Da kane ngi lah poi sha kaba kut jong kane ka jinghikai halor shibun ki queries hapoh ka “SQL View” LibreOffice Base.
11:40 Ban kyllum lang, ngi lah pule kumno ban:
11:43 Shna ia ki Queries ha ka SQL View
11:47 Pyndonkam ia ka ORDER BY clause
11:49 Pyndonkam JOINS
11:51 Pyndonkam Aggregate functions
11:54 Pyndonkam ia ka GROUP BY clause
11:57 Bad pyndonkam ia ki built in Functions.
12:00 “Spoken Tutorial” project kadei shi bynta jong ka “Talk to a Teacher” project, la kyrshan ia ka da ka National Mission on Education lyngba ka ICT, MHRD, Sorkat India. Ia kane ka project la pynbit pynbiang da ka http://spoken-tutorial.org. Kham bun ki jingtip halor kane kidon ha ka link harum.
12:21 nga dei u Pdiangburom na Shillong, nga pynkut ia kane.

Khublei shibun ia ka jingsnohkti lang jong phi.

Contributors and Content Editors

Pdiangburom, PoojaMoolya, Pratik kamble