SQL Musings

SELECT
distinct "public".task."id",
"public".task_log.created_at
FROM
"public".task ,
"public".task_log
WHERE
"public".task.actor_id = 777 and
task."id"= task_log.task_id
order by "public".task_log.created_at desc
limit 10



TO Set up data for the whole column
UPDATE schema.table_name SET column_name='value'



Deleting a lesson

 SELECT id FROM cmsession WHERE cmsession.title like '%elete%'

 DELETE FROM cmsession WHERE cmsession.title like '%elete%'

 SELECT * FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' )

 DELETE FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' )
SELECT * from assessment where lesson_id in (SELECT "id" FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' ))

 DELETE from assessment WHERE lesson_id in (SELECT "id" FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' ))

 SELECT * from assessment_question where assessmentid in (SELECT id from assessment where lesson_id in (SELECT "id" FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' )))

 DELETE from assessment_question where assessmentid in (SELECT id from assessment where lesson_id in (SELECT "id" FROM lesson WHERE  session_id in (SELECT id FROM cmsession
WHERE cmsession.title like '%elete%' )))

 SELECT * FROM task WHERE item_id in (SELECT id FROM lesson WHERE
session_id in (SELECT id FROM cmsession WHERE cmsession.title like
'%elete%'   ))

 DELETE FROM task WHERE item_id in (SELECT id FROM lesson WHERE
session_id in (SELECT id FROM cmsession WHERE cmsession.title like
'%elete%'   ))

 SELECT * FROM task_reviewer where task_id in (SELECT id FROM task
WHERE item_id in (SELECT id FROM lesson WHERE  session_id in (SELECT
id FROM cmsession WHERE cmsession.title like '%elete%'   )))

 DELETE FROM task_reviewer where task_id in (SELECT id FROM task WHERE
item_id in (SELECT id FROM lesson WHERE  session_id in (SELECT id FROM
cmsession WHERE cmsession.title like '%elete%'   )))

 SELECT * FROM presentaion WHERE lesson_id in (SELECT id FROM lesson
WHERE  session_id in (SELECT id FROM cmsession WHERE cmsession.title
like '%elete%'   ))

 DELETE FROM presentaion WHERE lesson_id in (SELECT id FROM lesson
WHERE  session_id in (SELECT id FROM cmsession WHERE cmsession.title
like '%elete%'   ))

 SELECT * FROM slide WHERE slide.presentation_id in (SELECT id FROM
presentaion WHERE lesson_id in (SELECT id FROM lesson WHERE
session_id in (SELECT id FROM cmsession WHERE cmsession.title like
'%elete%'   )))

 DELETE FROM slide WHERE slide.presentation_id in (SELECT id FROM
presentaion WHERE lesson_id in (SELECT id FROM lesson WHERE
session_id in (SELECT id FROM cmsession WHERE cmsession.title like
'%elete%'   )))

 SELECT * FROM learning_objective_lesson WHERE lessonid in (SELECT id
FROM lesson WHERE  session_id in (SELECT id FROM cmsession WHERE
cmsession.title like '%elete%'))

 DELETE FROM learning_objective_lesson WHERE lessonid in (SELECT id
FROM lesson WHERE  session_id in (SELECT id FROM cmsession WHERE

 cmsession.title like '%elete%'))




DB Queries for Org-Admin Pages - Finding Skills for a trainer


SELECT
ARRAY_AGG (name)
FROM
skill_objective
WHERE
ID IN (
SELECT
parent_skill_objective_id
FROM
course
WHERE
ID IN (
SELECT DISTINCT
course_id
FROM
batch
WHERE
batch."id" IN (
SELECT
batch_id
FROM
batch_schedule_event
WHERE
actor_id = 130
)
)
)


DB Queries for Org-Admin Page - Scheduled Session Details


SELECT DISTINCT
batch_schedule_event.actor_id,
CAST( batch_schedule_event.id as VARCHAR) AS event_id ,
batch_schedule_event.eventdate,
batch_schedule_event.eventhour,
batch_schedule_event.status,
batch. NAME AS batchname,
batch_schedule_event.cmsession_id,
cmsession.title,
classroom_details.classroom_identifier,
student. NAME AS trainername
FROM
batch_schedule_event
JOIN cmsession ON batch_schedule_event.cmsession_id = cmsession. ID
JOIN batch ON batch_schedule_event.batch_id = batch. ID
JOIN classroom_details ON batch_schedule_event.classroom_id = classroom_details. ID
JOIN student ON batch_schedule_event.actor_id = student. ID
WHERE
batch_schedule_event.batch_id IN (
SELECT DISTINCT
ID
FROM
PUBLIC .batch
WHERE
batch_group_id IN (
SELECT DISTINCT
ID
FROM
batch_group
WHERE
college_id = 3
)
)
AND CAST (
batch_schedule_event.eventdate AS VARCHAR
) LIKE '2016-07-21%'



08-03-2017


Making Complex SQL Queries is a journey;
for example a sample here shows this:
SELECT * from batch_group where college_id = 3
select * from batch where batch_group_id in (SELECT id from batch_group where college_id = 67)
select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67)
select * from course where id in (select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67))
select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67)
select * from course where id in (select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67));
select distinct course.parent_skill_objective_id from course where id in (select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67))
SELECT * from skill where parent_skill_id in (select distinct course.parent_skill_objective_id from course where id in (select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 67)))
select * from skill_precentile where skill_id in (select distinct course.parent_skill_objective_id from course where id in (select DISTINCT course_id from batch where batch_group_id in (SELECT id from batch_group where college_id = 5)))





Competition View for Org Admin

 (select id from batch_group where college_id = 67)
select distinct course_id from batch where batch_group_id in (select id from batch_group where college_id = 67)
select parent_skill_id, course.course_name from course where id in (select distinct course_id from batch where batch_group_id in (select id from batch_group where college_id = 67))
select student_id from batch_students where batch_group_id in (select id from batch_group where college_id = 67)

 select avg(percentage) from skill_precentile where student_id  in (select student_id from batch_students where batch_group_id in (select id from batch_group where college_id = 67)) and skill_id =2973
;
select  bg.college_id, c.parent_skill_id, c.course_name,avg(sp.percentage) 
from batch_group as bg, batch as b, course as c, skill_precentile sp, batch_students as bs
where bg.college_id = 68
and b.batch_group_id = bg."id"
and bs.batch_group_id = b.batch_group_id
and b.course_id = c."id"
and c.parent_skill_id = sp.skill_id
and bs.student_id = sp.student_id
GROUP BY c.course_name,c.parent_skill_id,bg.college_id;

 select DISTINCT batch_group_id from batch 
where batch.course_id in 
(select DISTINCT course_id from batch where batch_group_id in (select batch_group.id from batch_group where batch_group.college_id = 67))

and batch_group_id not in ((select batch_group.id from batch_group where batch_group.college_id = 67));

Program View Org Admin

SELECT batch_group_id, course_id, avg(master) as master, avg(rookie) as rookie, avg(apprentice) as apprentice, avg(wizard) as wizard,
(avg(master)+avg(rookie)+avg(apprentice)+avg(wizard)) as total
FROM mastery_level_per_course
where college_id = 67
and course_id = 22
GROUP BY batch_group_id, course_id
ORDER BY course_id;

 select distinct course_id from batch where batch_group_id in (select id from batch_group where college_id = 67)
select DISTINCT c.parent_skill_id, c.course_name,avg(sp.percentage) from batch_group as bg, batch as b, course as c, skill_precentile sp, batch_students as bs where bg.college_id = 67 and b.batch_group_id = bg. id and bs.batch_group_id = b.batch_group_id and b.course_id = c. id and c.parent_skill_id = sp.skill_id and bs.student_id = sp.student_id GROUP BY c.course_name,c.parent_skill_id


Course View Org Admin

 SELECT batch_group_id, course_id, avg(master) as master, avg(rookie) as rookie, avg(apprentice) as apprentice, avg(wizard) as wizard,
(avg(master)+avg(rookie)+avg(apprentice)+avg(wizard)) as total
FROM mastery_level_per_course
where college_id = 67
and batch_group_id = 133
GROUP BY batch_group_id, course_id
ORDER BY course_id;
select id, name from batch_group where college_id = 67;

Comments

Post a Comment

Popular posts from this blog

Using cookies with HttpURLConnection

Before the advent of REST, we services were stateful. There were cookies sent with every HTTP request to maintain the state of the client using the services. When we use HttpURLConnection (Java) we need to discretely mention the cookies in the header and store it somewhere for maintaining the session. Below is an attempt using a singleton class for maintaining the session details and some sample HTTP methods: First our Singleton to maintain the cookies in memory between successive transactions: package singletonCookieManager; import java.net.CookieManager; /**  * @author absin  *  */ public class EaqerInitializedCookieHelper { private static final CookieManager instance = new CookieManager(); // private constructor to avoid client applications to use constructor private EaqerInitializedCookieHelper() { } public static CookieManager getInstance() { return instance; } } Next, wherever you want to use this class initialize it as such private static jav
Read more

SPARQL

Sparql can be used to query structured data sets like DBPedia. These queries can be run on SPAQL enpoints like http://lod.openlinksw.com/sparql or http://dbpedia.org/sparql/. The queries can be slightly different, but for an experienced SPARQLer (which I hope to be one day) this won't be a problem. Here are a few of my first queries, the structure will be purpose followed by the query: The urban and metro population of Patna PREFIX dbo: <http://dbpedia.org/ontology/> PREFIX dbr: <http://dbpedia.org/resource/> select ?populationUrban  ?populationMetro where {   dbpedia:Patna  dbo:populationUrban ?populationUrban;                  dbo:populationMetro ?populationMetro  . } Details of Hemmingway: prefix dbpedia: <http://dbpedia.org/resource/> prefix dbpedia-owl: <http://dbpedia.org/ontology/> select ?abstract ?thumbnail where {    dbpedia:Ernest_Hemingway dbpedia-owl:abstract ?abstract ;                            dbpedia-owl:thumbn
Read more

ffmpeg for Google Speech API

ffmpeg -i call.mp3 -acodec pcm_s16le -ar 16000 call.wav  The following command will convert an audio file into a mono wav at a specified sample rate of 8000: ffmpeg -i /home/absin/Downloads/RT7345201f37e6ae014fcbbc353400bce8.mka -ar 8000 -ac 1 /home/absin/Downloads/audio.wav The following command will take a frame from a video ffmpeg -i input.flv -ss 00:00:14.435 -vframes 1 out.png
Read more