1 / 65

CarnieChrisMyCareer Database

Helen Spiropoulos Benjamin Mills Nicoleta Bikrogiannis Jessica El-chaar. CarnieChrisMyCareer Database. Models the mycareer website ( www.mycareer.com.au )

mary-arnold
Download Presentation

CarnieChrisMyCareer Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Helen Spiropoulos Benjamin Mills Nicoleta Bikrogiannis Jessica El-chaar CarnieChrisMyCareerDatabase

  2. Models the mycareer website(www.mycareer.com.au) • This database models the real world process of searching and applying for jobs. It can be applied both to job seekers and recruiters who are looking to increase the efficiency of sorting and searching their data. • Allows jobseekers to search for and apply for jobs • Jobs can be searched via a range of attributes such as salary, location, description, date of posting etc MyCareer Database

  3. Job seekers must register in order to apply for jobs • Jobs must belong to a location and sector • Recruiters can search for applicants who have applied to certain jobs within a chosen sector and notify them of future listings MyCareer Database

  4. Job Sector Location JobID JobPostDate JobSalary JobPosition LocID* SecID* JobDesc SecID SecTitle SecDesc LocID LocCity LocCountry Carnie_Mycareer Database ERD Application JobID* CanEmail* Candidate CanEmail CanFirstName CanLastName CanSex CanBirthYear

  5. SQL Queries on a Single Entity/ Table MyCareer Database

  6. Projecting tables’ contents using ‘SELECT’ select * fromcarnie_mycareer_candidate; Selecting Everything in a table MyCareer Database select sectitle, secdesc from carnie_mycareer_sector; Selecting a couple of columns from a table

  7. Restrict using ‘WHERE’ ** Show all male candidates MyCareer Database select * from carnie_mycareer_candidate where cansex = 'M'; Restricts the rows shown

  8. Project and Restrict combo Can use to project a combination of columns and rows. Example: List the last name and email address of all male candidates over 30yrs select canlastname,canemail from carnie_mycareer_candidate where canbirthyear < 1977 and cansex = 'M'; MyCareer Database

  9. IS NULL List the last name and email address of the candidates where the candidate has no email address entered. select canlastname, canemail from carnie_mycareer_candidate where canemail is null; canlastname | canemail ------------------+---------- (0 rows) There are no null entries so nothing appears in the table! MyCareer Database

  10. IS NOT NULL List the last name and email address of the candidates who have an email address. select canlastname, canemail from carnie_mycareer_candidate where canemail is not null; MyCareer Database

  11. IN Used when you want to return various values. Example; List the job position and salary of all jobs position available from Sydney, Canberra and Wellington. select Jobposition, jobsalary from carnie_mycareer_job where locid in (1,2,5) ; It’s the same as doing the below but easier! Select Jobposition, Jobsalary from carnie_mycareer_job where locid = 1 OR locid = 2 OR locid = 5; MyCareer Database

  12. NOT IN Used when you want to exclude various values. Example; List the job position and salary of all jobs position available except those from Sydney, Canberra and Wellington. select Jobposition, jobsalary from carnie_mycareer_job where locid not in (1,2,5) ; It’s the same as doing the below but easier! Select Jobposition, Jobsalary from carnie_mycareer_job where locid <> 1 AND locid <> 2 and locid <> 5; MyCareer Database

  13. ORDERING COLUMNS The order stated after the select statement is the order that the Columns will appear. Example; select canfirstname, canlastname from CARNIE_MYCAREER_CANDIDATE;  MyCareer Database select canfirstname, canlastname from CARNIE_MYCAREER_CANDIDATE; 

  14. ORDER ROWS: ‘ORDER BY’ List the Lastnames and Firstnames of all candidates. Order the candidates in alphabetical order by their last name. select canlastname, canfirstname from CARNIE_MYCAREER_CANDIDATE ORDER BY canlastname; MyCareer Database

  15. CALCULATING PostgreSQL can even do calculations (Such as divide, times and present them for you! Maybe you want to work out how much money the jobs available will be earning per week instead of per year! (Assumption that there are 52 paid weeks per year) select jobposition, jobsalary / 52 AS SalaryPerWeek FROM CARNIE_MYCAREER_JOB ; MyCareer Database ‘AS’ allows you to name your calculation results to whatever you wish. In this case we rename it to salaryperweek

  16. BUILT IN FUNCTIONS: COUNT Two ways to use count: COUNT(*) – This is used to count ALL of the rows. Remember * means everything… Example: How many candidates are registered? Select count(*) from CARNIE_MYCAREER_CANDIDATE; COUNT(NameofCollumn) – This can be used to count the number of non null values in the column. See the next slide … MyCareer Database

  17. MyCareer Database BUILT IN FUNCTIONS: COUNT The second way to use count: COUNT(NameofCollumn) – This can be used to count the number of non null values in the column. Here is an example of using it with DISTINCT in order to see how many different types of values are in a specified collumn. Example: How many different countries does MyCareer cater to? Select count(distinct loccountry) from CARNIE_MYCAREER_LOCATION ; If we did not use the distinct, it would return the same values as COUNT(*) since we have no NULL data values in this database. Thus COUNT(Collumn) had no use for us in this example.

  18. OTHER BUILT IN FUNCTIONS: MAX, MIN, AVG & SUM The above built in functions are mostly only useful when dealing with numbers such as price, salaries, quantities etc. What is the highest Salary? select MAX(jobsalary) from carnie_mycareer_job; What is the name of the job of the lowest Salary? select MIN(jobsalary) from carnie_mycareer_job; What is the average Salary? select AVG(jobsalary) from carnie_mycareer_job; MyCareer Database SUM (jobsalary) could be used to add all of the salaries together.

  19. LIKE – PATTERN MATCHING List all candidates with a last name starting with ‘S’ select canfirstname, canlastname from CARNIE_MYCAREER_CANDIDATE where canlastname LIKE 'S%'; List all candidates with a first name which contains ‘ie’ select canfirstname, canlastname from CARNIE_MYCAREER_CANDIDATE WHERE canfirstname LIKE '%ie%'; MyCareer Database

  20. LIKE – PATTERN MATCHING List all candidates with ‘i’ as the fourth letter in their first name SELECT canfirstname, canlastname FROM CARNIE_MYCAREER_CANDIDATE WHERE canfirstname LIKE '___i%'; List all candidates who do not have ‘i’ in their first name select canfirstname, canlastname from CARNIE_MYCAREER_CANDIDATE WHERE canfirstname NOT LIKE '%i%‘ AND canfirstname NOT LIKE '%I%'; MyCareer Database

  21. DISTINCT Distinct removes duplicate rows. For example: List the different countries where jobs are available. SELECT distinct loccountry FROM CARNIE_MYCAREER_LOCATION ; MyCareer Database

  22. INSERTING ROWS INSERT INTO CARNIE_MYCAREER_CANDIDATE VALUES ('robertp@hotmail.com', 'Robert', 'Pratt', 'M', '09-04-1963'); … OR … INSERT INTO CARNIE_MYCAREER_CANDIDATE (canemail, canfirstname, canlastname, cansex,canbirthyear) VALUES ('robertp@hotmail.com', 'Robert', 'Pratt', 'M', '09-04-19-63'); MyCareer Database

  23. Foreign Keys & Natural Joins

  24. Carnie_MyCareer_Candidate Primary Key canemail | canfirstname | canlastname | cansex | canbirthyear -------------------------+--------------+-------------+--------+-------------- chris@hotmail.com | Chris | Carnie | M | 08-01-1981 david11@hotmail.com | David | Saddington | M | 21-06-1980 katiele@hotmail.com | Katie | Lenehan | F | 04-04-1978 sussiekelly@hotmail.com | Sussie | Kelly | F | 24-11-1969 ron12@hotmail.com | Ron | Howard | M | 01-01-2000 Carnie_MyCareer_Application MyCareer Database jobid | canemail -------+------------------------- 1 | chris@hotmail.com 1 | sussiekelly@hotmail.com 1 | katiele@hotmail.com 2 | sussiekelly@hotmail.com 3 | david11@hotmail.com 5 | chris@hotmail.com 5 | david11@hotmail.com 5 | katiele@hotmail.com 5 | sussiekelly@hotmail.com 8 | david11@hotmail.com 8 | katiele@hotmail.com 9 | chris@hotmail.com 9 | katiele@hotmail.com Foreign Key

  25. The Natural Join SELECT jobid, jobsalary, jobposition, locid, secid, canemail FROM carnie_mycareer_job NATURAL JOIN carnie_mycareer_application; MyCareer Database

  26. Cross Product Joins This query produces the same result as a natural join, making use of the cross product function: SELECT jobid, jobsalary, jobposition, locid, secid, canemail FROM carnie_mycareer_job, carnie_mycareer_application WHERE carnie_mycareer_job.jobID = carnie_mycareer_application.jobID; MyCareer Database

  27. Cross Product Joins versus Natural Joins Natural join simply takes two or more selected tables and joins them via the common column(s) that each of them share. For example: Select * from carnie_mycareer_job natural join carnie_mycareer_application; The common column in this case is “jobID” (see previous slides). Cross product joins essentially do the same thing, however, the user must specify the join: Select * from carnie_mycareer_job, carnie_mycareer_application Where carnie_mycareer_job.jobID = carnie_mycareer_application.jobID MyCareer Database

  28. Entities & Relationships MyCareer Database

  29. The Sector – Job 1:m relationship Job Sector JobID JobPostDate JobSalary JobPosition LocID* SecID* Job Desc SecID SecTitle SecDesc Foreign Key MyCareer Database

  30. The Location – Job 1:m relationship Job Location LocID LocCity LocCountry JobID JobPostDate JobSalary JobPosition LocID* SecID* Job Desc Foreign Key MyCareer Database

  31. Job JobID JobPostDate JobSalary JobPosition LocID* SecID* Job Desc Dependant Entities The Job – Application – Candidate 1:m relationship jobid | jobpostdate | jobsalary | jobposition | locid | secid | jobdesc -------+-------------+-----------+-------------+-------+-------+------------------------------------------------ 1 | 01-01-2005 | 50000 | Senior | 1 | 1 | Account manager, blue chip company 2 | 01-01-2005 | 120000 | CFO | 1 | 1 | CFO of big four bank 3 | 01-03-2005 | 45000 | Jr | 1 | 2 | Entry level Database position. Great start!! 4 | 05-03-2005 | 20000 | Reception | 2 | 3 | Basic office admin, phones and coffee making 5 | 07-02-2005 | 38000 | Graduate | 3 | 1 | Great start for a fresh graduate (5 rows) Application jobid | canemail ------+------------------------- 1 | chris@hotmail.com 1 | sussiekelly@hotmail.com 1 | katiele@hotmail.com 2 | sussiekelly@hotmail.com 3 | david11@hotmail.com 5 | chris@hotmail.com 5 | david11@hotmail.com 5 | katiele@hotmail.com 5 | sussiekelly@hotmail.com 8 | david11@hotmail.com Foreign Key JobID* CanEmail* MyCareer Database Candidate Foreign Key CanEmail CanFirstName CanLastName CanSex CanBirthYear canemail | canfirstname | canlastname |cangender| canbirthyear -------------------------+---------------+------------+---------+------------- chris@hotmail.com | Chris | Carnie | M | 08-01-1981 david11@hotmail.com | David | Saddington | M | 21-06-1980 katiele@hotmail.com | Katie | Lenehan | F | 04-04-1978 sussiekelly@hotmail.com | Sussie | Kelly | F | 24-11-1969 ron12@hotmail.com | Ron | Howard | M | 01-01-2000

  32. The Job – Candidate m:m relationship Application Job Candidate JobID JobPostDate JobSalary JobPosition LocID* SecID* Job Desc JobID* CanEmail* CanEmail CanFirstName CanLastName CanSex CanBirthYear MyCareer Database jobid | canemail ------+------------------------- 1 | chris@hotmail.com 1 | sussiekelly@hotmail.com 1 | katiele@hotmail.com 2 | sussiekelly@hotmail.com 3 | david11@hotmail.com 5 | chris@hotmail.com 5 | david11@hotmail.com 5 | katiele@hotmail.com 5 | sussiekelly@hotmail.com 8 | david11@hotmail.com (10 rows) canemail | canfirstname | -------------------------+--------------+ chris@hotmail.com | Chris | david11@hotmail.com | David | katiele@hotmail.com | Katie | sussiekelly@hotmail.com | Sussie | ron12@hotmail.com | Ron | (5 rows) jobid | jobpostdate | jobsalary | jobposition | -------+-------------+-----------+-------------+ 1 | 01-01-2005 | 50000 | Senior | 2 | 01-01-2005 | 120000 | CFO | 3 | 01-03-2005 | 45000 | Jr | 4 | 05-03-2005 | 20000 | Reception | 5 | 07-02-2005 | 38000 | Graduate | (5 rows)

  33. The Job – Candidate m:m relationship (cont.) As demonstrated by the ERD on the previous slide, the links between the separate entities show that one candidate can have many applications, while one job can also have many applications. Furthermore, as a result of this link, a candidate can have one application to one specific job, however, can have numerous applications, each for a different job. Therefore a many-to-many relationship is formed between candidates and jobs. MyCareer Database

  34. An example of creating a view CREATE VIEW ausjobs (JobID,PostDate,Salary,Position,City,Sector,Description) AS SELECT jobid,jobpostdate,jobsalary,jobposition,loccity,sectitle,jobdesc FROMcarnie_mycareer_job job, carnie_mycareer_sector sec, carnie_mycareer_location loc WHEREjob.secid = sec.secidANDjob.locid = loc.locid ANDloc.loccountry = 'Australia'; MyCareer Database

  35. An example of querying a view • Query exactly as if a table • SELECT jobid, postdate, salary, position, city, sector FROM ausjobs WHERE salary >= 50000; MyCareer Database

  36. Group by, sub-queries and complex joins MyCareer Database

  37. The GROUP BY clause is an elementary form of control break reporting. It permits grouping of rows that have the same value for a specified column or columns, and it produces one row for each different value of the grouping column(s) MyCareer Database SQL’s built- in functions (COUNT, SUM, AVERAGE, MIN, and MAX) can be used with the GROUP BY clause

  38. GROUP BY Report by sector the average value for salary select SecTitle, AVG(JobSalary) AS AvgSalary from CARNIE_MYCAREER_JOB, CARNIE_MYCAREER_SECTOR where CARNIE_MYCAREER_JOB.SecID= CARNIE_MYC AREER_SECTOR.SecID GROUP BY SecTitle; MyCareer Database

  39. Another example of GROUP BY – with COUNT(*) Report by Sector the average value for salary select SecTitle, COUNT (*), AVG(JobSalary) As AvgSalary from CARNIE_MYCAREER_JOB, CARNIE_MYCAREER_SECTOR where CARNIE_MYCAREER_JOB.SecID= CARNIE_MYCAREER_SECTOR.SecID GROUP BY SecTitle; MyCareer Database

  40. HAVING – Like WHERE, but after the grouping Show candidates’ email and report those with less than three applications and their average salary select CanEmail, AVG(JobSalary) As AvgSal from CARNIE_MYCAREER_APPLICATION, CARNIE_MYCAREER_JOB where CARNIE_MYCAREER_APPLICATION.JobId = CARNIE_MYC AREER_JOB.JobID GROUP BY CanEmail HAVING COUNT (*) <= 3; MyCareer Database

  41. Subqueries Report all Job Positions with a Job Salary greater than the average of all job salaries SELECT JobPosition, JobSalary FROM CARNIE_MYCAREER_JOB WHERE JobSalary > ( SELECT AVG(JobSalary) from CARNIE_MYCAREER_JOB); MyCareer Database

  42. Subquery - returns one value SELECT LocID FROM CARNIE_MYCAREER_LOCATION WHERE LocCountry = ‘Australia’ and LocID <= all ( SELECT LocID from CARNIE_MYCAREER_LOCATION where LocCountry = ‘Australia’); MyCareer Database Returns

  43. Using subqueries to find the maximum (or minimum) SELECT JobPosition, JobSalary FROM CARNIE_MYCAREER_JOB WHERE JobSalary >= ( SELECT max(JobSalary) from CARNIE_MYCAREER_JOB where LocID = 1); MyCareer Database

  44. Alternate way to find the maximum (or minimum): “ALL” SELECT JobPosition, JobSalary FROM CARNIE_MYCAREER_JOB WHERE JobSalary >= ALL ( SELECT JobSalary from CARNIE_MYCAREER_JOB where LocID = 1); MyCareer Database

  45. Another example SELECT JobPosition, JobSalary FROM CARNIE_MYCAREER_JOB WHERE JobSalary <= ALL (SELECT JobSalary from CARNIE_MYCAREER_JOB); That’s equivalent to … Where JobSalary = (SELECT min(JobSalary) from CARNIE_MYCAREER_JOB); MyCareer Database

  46. Any operator SELECT DISTINCT JobPosition FROM CARNIE_MYCAREER_JOB WHERE LocID = ANY ( SELECT LocID FROM CARNIE_MYCAREER_LOCATION WHERE LocCountry = 'Australia‘ ); MyCareer Database

  47. In: an Alternate to ANY SELECT DISTINCT JobPosition FROM CARNIE_MYCAREER_JOB WHERE LocID IN ( SELECT LocID FROM CARNIE_MYCAREER_Location WHERE LocCountry = 'Australia‘ ); MyCareer Database

  48. LEFT Outer Join SELECT jobID, appID, canEmail, jobsalary, jobposition FROM carnie_mycareer_application LEFT JOIN carnie_mycareer_job USING (jobid) where jobsalary < 60000; Not a great example as all applications match a job MyCareer Database

  49. RIGHT Outer Join SELECT jobID, appID, canEmail, jobsalary, jobposition FROM carnie_mycareer_application RIGHT JOIN carnie_mycareer_job USING (jobid) where jobsalary < 60000; MyCareer Database

  50. SELF Join Joining a table to itself. In this case, carnie_mycareer_candidate is joined to itself SELECT job.jobid, can1.canlastname AS applicant1, can2.canlastname AS applicant2 FROM carnie_mycareer_candidate can1, carnie_mycareer_candidate can2, carnie_mycareer_application app1, carnie_mycareer_application app2, carnie_mycareer_job job WHERE can1.canemail = app1.canemail ANDcan2.canemail = app2.canemail AND job.jobID = app1.jobID AND job.jobID = app2.jobID AND app1.jobID = 9 AND app2.canemail < app1.canemail; MyCareer Database Produces

More Related