DbScripts/Views.txt

CREATE OR REPLACE VIEW public.jddetailscoursematching
 AS
 SELECT jd.jdmasterid,
    sm.skilldetails
   FROM skillmaster sm
     JOIN jdskilldetails sd ON sm.skillid = sd.skillid
     JOIN jdmaster jd ON jd.jdmasterid = sd.jdmasterid
  WHERE sm.skilltype::text = 'Hard Skill'::text AND sm.weightage <> 0::numeric AND (jd.jdmasterid IN ( SELECT DISTINCT jd_1.jdmasterid
           FROM jdmaster jd_1
          WHERE jd_1.isskillsextracted = 1 AND NOT (jd_1.jdmasterid IN ( SELECT courseskillmatch.jdmasterid
                   FROM courseskillmatch))
          ORDER BY jd_1.jdmasterid DESC
         LIMIT 1));


CREATE OR REPLACE VIEW public.coursedetailsformatching
 AS
 SELECT jd.masterid,
    sm.skilldetails
   FROM skillmaster sm
     JOIN courseskilldetails sd ON sm.skillid = sd.skillid
     JOIN coursemaster jd ON jd.masterid = sd.masterid
  WHERE jd.isskillsextracted = 1;

CREATE OR REPLACE VIEW public.skillcount
 AS
 SELECT skillid,
    count(skillid) AS count
   FROM jdskilldetails
  WHERE (jdmasterid IN ( SELECT jdmaster.jdmasterid
           FROM jdmaster
          ORDER BY jdmaster.jdmasterid DESC
         LIMIT 10))
  GROUP BY skillid
  ORDER BY (count(skillid)) DESC
 LIMIT 20;

CREATE OR REPLACE VIEW public.topjd
 AS
 SELECT filename AS "File Name",
    "substring"(jobdescription, 0, 100) AS "Job Description",
    uploadeddate AS "Upload Date",
    uploadedby AS "Uploaded By"
   FROM jdmaster
  ORDER BY jdmasterid DESC
 LIMIT 20;


CREATE OR REPLACE VIEW public.latestskills
 AS
 SELECT sm.skilldetails AS "Skills",
    sc.count AS "Skill Count",
    sm.updateddate AS "Extraction Date"
   FROM skillmaster sm
     JOIN skillcount sc ON sm.skillid = sc.skillid;

CREATE OR REPLACE VIEW public.skilldetails
 AS
 SELECT jd.jdmasterid AS id,
    sm.skilldetails AS skillname,
    sm.skilltype,
    sm.skill_score::double precision AS skillscore
   FROM skillmaster sm
     JOIN jdskilldetails sd ON sm.skillid = sd.skillid
     JOIN jdmaster jd ON jd.jdmasterid = sd.jdmasterid
  WHERE sm.isactive = true AND sm.weightage <> 0::numeric;