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

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	Ia kane ka script la noh synniang da ka Priya Suresh, DesiCrew Solutions. Bad 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