How Can We Help?

SQL validation for viewsSQL validation for views

USER_DATA

SQL Description Expected result

select USER_NAME, count(USER_NAME) from USER_DATA group by USER_NAME having count(USER_NAME) > 1;

Verification of dublicate user_names

This sql should return 0 rows.

select EMAIL, count(EMAIL) from USER_DATA group by EMAIL having count(EMAIL) > 1;

Verification of dublicate emails_names

This sql should return 0 rows.

PERSON_DATA

SQL Description Expected result

select PERSON_ID, count(PERSON_ID) from PERSON_DATA group by PERSON_ID having count(PERSON_ID) > 1;

Verification of dublicate person_ids.

This SQL should return 0 rows.

select distinct PERSON_DATA.PERSON_ID

FROM PERSON_DATA

 LEFT OUTER JOIN STAFF_ORG_RELATION ON PERSON_DATA.PERSON_ID = STAFF_ORG_RELATION.PERSON_ID

 LEFT OUTER JOIN STUDENT_ORG_RELATION ON PERSON_DATA.PERSON_ID = STUDENT_ORG_RELATION.PERSON_ID

 LEFT OUTER JOIN VISITING_SCHOLAR_ORG_RELATION ON PERSON_DATA.PERSON_ID = VISITING_SCHOLAR_ORG_RELATION.PERSON_ID

 LEFT OUTER JOIN HONORARY_STAFF_ORG_RELATION ON PERSON_DATA.PERSON_ID = HONORARY_STAFF_ORG_RELATION.PERSON_ID
WHERE

 STAFF_ORG_RELATION.ID IS NULL

 AND STUDENT_ORG_RELATION.ID IS NULL

 AND VISITING_SCHOLAR_ORG_RELATION.ID IS NULL

 AND HONORARY_STAFF_ORG_RELATION.ID IS NULL ;

Finds all persons that do not have any organisation relations available.

This SQL should return 0 rows.

SELECT PERSON_DATA.PERSON_ID, COUNT(PERSON_DATA.PERSON_ID) FROM PERSON_DATA

JOIN (

SELECT STAFF_ORG_RELATION.PERSON_ID FROM STAFF_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE

UNION ALL

SELECT STUDENT_ORG_RELATION.PERSON_ID FROM STUDENT_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE

UNION ALL

SELECT VISITING_SCHOLAR_ORG_RELATION.PERSON_ID FROM VISITING_SCHOLAR_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE

UNION ALL

SELECT HONORARY_STAFF_ORG_RELATION.PERSON_ID FROM HONORARY_STAFF_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE

) A ON A.PERSON_ID=PERSON_DATA.PERSON_ID

GROUP BY PERSON_DATA.PERSON_ID

HAVING COUNT(PERSON_DATA.PERSON_ID)>1

Finds all person_ids that have organisation relations that have more than one primary association.

This SQL should return 0 rows.

SELECT PERSON_DATA.PERSON_ID, PERSON_DATA.USER_ID FROM PERSON_DATA WHERE NOT PERSON_DATA.USER_ID IS NULL AND USER_ID NOT IN (SELECT ID FROM USER_DATA)

Finds all persons that have relations to users that doesn't exist in the USER_DATA.

This SQL should return 0 rows.

ORG_RELATION

SQL Description Expected result

SELECT ID, COUNT(id) from (

 SELECT ID from STAFF_ORG_RELATION

 union all select id from STUDENT_ORG_RELATION

 union all select id from VISITING_SCHOLAR_ORG_RELATION

 union all select id from HONORARY_STAFF_ORG_RELATION

 ) as result group by ID having count(ID) > 1;

Verification of duplicate ids across relation tables

This SQL should return 0 rows.

STAFF_ORG_RELATION

SQL Description Expected result

select ID, count(ID) from STAFF_ORG_RELATION group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

SELECT ID, COUNT(id) from (

 SELECT ID from STAFF_ORG_RELATION

 union all select id from STUDENT_ORG_RELATION

 union all select id from VISITING_SCHOLAR_ORG_RELATION

 union all select id from HONORARY_STAFF_ORG_RELATION

 ) as result group by ID having count(ID) > 1;

Verification of duplicate ids across relation tables

This SQL should return 0 rows.

STUDENT_ORG_RELATION

SQL Description Expected result

select ID, count(ID) from STUDENT_ORG_RELATION group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

select * from STUDENT_ORG_RELATION as s

    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)

    left join organisation_data as o2 on (o2.organisation_id = s.org_id)

    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)

    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)

    where o1.organisation_id is null AND o2.organisation_id is null

    AND o3.organisation_id is null AND o4.organisation_id is null;

Verifies that organisation data matches organisation relations

This SQL should return 0 rows.

VISITING_SCHOLAR_ORG_RELATION

SQL Description Expected result

select ID, count(ID) from VISITING_ORG_RELATION group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

select * from VISITING_ORG_RELATION as s

    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)

    left join organisation_data as o2 on (o2.organisation_id = s.org_id)

    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)

    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)

    where o1.organisation_id is null AND o2.organisation_id is null

    AND o3.organisation_id is null AND o4.organisation_id is null;

Verifies that organisation data matches organisation relations

This SQL should return 0 rows.

HONORARY_STAFF_ORG_RELATION

SQL Description Expected result

select ID, count(ID) from HONORARY_STAFF_ORG_RELATION group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

select * from HONORARY_STAFF_ORG_RELATION as s

    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)

    left join organisation_data as o2 on (o2.organisation_id = s.org_id)

    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)

    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)

    where o1.organisation_id is null AND o2.organisation_id is null

    AND o3.organisation_id is null AND o4.organisation_id is null;

Verifies that organisation data matches organisation relations

This SQL should return 0 rows.

ORG_RELATION_ADDRESSES

SQL Description Expected result

select ID, count(ID) from ORG_RELATION_ADDRESSES group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_ADDRESSES

WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR

STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR

VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR

HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)

Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.

ORG_RELATION_PHONE_NUMBERS

SQL Description Expected result

select ID, count(ID) from ORG_RELATION_PHONE_NUMBERS group by ID having count(ID) > 1;

Verification of dublicate ids

This SQL should return 0 rows.

SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_PHONE_NUMBERS

WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR

STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR

VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR

HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)

Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.

This SQL should return 0 rows.

ORG_RELATION_EMAILS

SQL Description Expected result

select ID, count(ID) from ORG_RELATION_EMAILS group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_EMAILS

WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR

STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR

VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR

HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)

Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.

ORG_RELATION_WEB_ADDRESSES

SQL Description Expected result

select ID, count(ID) from ORG_RELATION_WEB_ADDRESSES group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_WEB_ADDRESSES

WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR

STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR

VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR

HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)

Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.

ORG_RELATION_KEYWORDS

SQL Description Expected result

SELECT STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_KEYWORDS

WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR

STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR

VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR

HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)

Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.

This SQL should return 0 rows.

PERSON_IDS

SQL Description Expected result

SELECT * FROM PERSON_IDS WHERE PERSON_IDS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_IDS that are not related to a person in PERSON_DATA

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_IDS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_NAMES

SQL Description Expected result

SELECT * FROM PERSON_NAMES WHERE PERSON_NAMES.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_NAMES that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_NAMES GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_TITLES

SQL Description Expected result

SELECT * FROM PERSON_NAMES WHERE PERSON_NAMES.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_NAMES that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_NAMES GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_PROFILE_INFORMATION

SQL Description Expected result

SELECT * FROM PERSON_PROFILE_INFORMATION WHERE PERSON_PROFILE_INFORMATION.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_PROFILE_INFORMATION that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_PROFILE_INFORMATION GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_LEAVE_OF_ABSENCE

SQL Description Expected result

SELECT * FROM PERSON_LEAVE_OF_ABSENCE WHERE PERSON_LEAVE_OF_ABSENCE.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_LEAVE_OF_ABSENCE that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_LEAVE_OF_ABSENCE GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

PERSON_DOCUMENTS

SQL Description Expected result

SELECT * FROM PERSON_DOCUMENTS WHERE PERSON_DOCUMENTS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_DOCUMENTS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_DOCUMENTS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

PERSON_PHOTOS

SQL Description Expected result

SELECT * FROM PERSON_PHOTOS WHERE PERSON_PHOTOS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_PHOTOS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_PHOTOS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_EDUCATIONS

SQL Description Expected result

SELECT * FROM PERSON_EDUCATIONS WHERE PERSON_EDUCATIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_EDUCATIONS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_EDUCATIONS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_EXTERNAL_POSITIONS

SQL Description Expected result

SELECT * FROM PERSON_EXTERNAL_POSITIONS WHERE PERSON_EXTERNAL_POSITIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_EXTERNAL_POSITIONS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_EXTERNAL_POSITIONS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0 rows.

PERSON_PROF_QUALIFICATIONS

SQL Description Expected result

SELECT * FROM PERSON_PROF_QUALIFICATIONS WHERE PERSON_PROF_QUALIFICATIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_PROF_QUALIFICATIONS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_PROF_QUALIFICATIONS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0

PERSON_LINKS

SQL Description Expected result

SELECT * FROM PERSON_LINKS WHERE PERSON_LINKS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_LINKS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SELECT ID, COUNT(ID) FROM PERSON_LINKS GROUP BY ID HAVING COUNT(ID) > 1

Verify that the IDs are unique

This SQL should return 0

PERSON_KEYWORDS

SQL Description Expected result

SELECT * FROM PERSON_KEYWORDS WHERE PERSON_KEYWORDS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)

Finds all entries in PERSON_KEYWORDS that are not related to a person in PERSON_DATA.

This SQL should return 0 rows.

SUPERVISOR_DATA

SQL Description Expected result

select ID, count(ID) from HONORARY_STAFF_ORG_RELATION group by ID having count(ID) > 1;

Verification of duplicate ids

This SQL should return 0 rows.

SELECT SUPERVISOR_DATA.* FROM SUPERVISOR_DATA

LEFT OUTER JOIN STAFF_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = STAFF_ORG_RELATION.ID

 LEFT OUTER JOIN STUDENT_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = STUDENT_ORG_RELATION.ID

 LEFT OUTER JOIN VISITING_SCHOLAR_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = VISITING_SCHOLAR_ORG_RELATION.ID

 LEFT OUTER JOIN HONORARY_STAFF_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = HONORARY_STAFF_ORG_RELATION.ID
WHERE

 STAFF_ORG_RELATION.ID IS NULL

 AND STUDENT_ORG_RELATION.ID IS NULL

 AND VISITING_SCHOLAR_ORG_RELATION.ID IS NULL

 AND HONORARY_STAFF_ORG_RELATION.ID IS NULL;

Verification of apprentice relations in supervisor_data view. Must return 0 rows

This SQL should return 0 rows.

Published at May 27, 2024

How Can We Help?

Search Results

Filter By Category

Contact Us

SQL validation for viewsSQL validation for views

USER_DATA

PERSON_DATA

ORG_RELATION

STAFF_ORG_RELATION

STUDENT_ORG_RELATION

VISITING_SCHOLAR_ORG_RELATION

HONORARY_STAFF_ORG_RELATION

ORG_RELATION_ADDRESSES

ORG_RELATION_PHONE_NUMBERS

ORG_RELATION_EMAILS

ORG_RELATION_WEB_ADDRESSES

ORG_RELATION_KEYWORDS

PERSON_IDS

PERSON_NAMES

PERSON_TITLES

PERSON_PROFILE_INFORMATION

PERSON_LEAVE_OF_ABSENCE

PERSON_DOCUMENTS

PERSON_PHOTOS

PERSON_EDUCATIONS

PERSON_EXTERNAL_POSITIONS

PERSON_PROF_QUALIFICATIONS

PERSON_LINKS

PERSON_KEYWORDS

SUPERVISOR_DATA