QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
CC105 - Information Management
Activity-Exercise-Assignment-Week12
SCORE PERCENTAGE
NAME: Familgan, John Mark S.
STUDENT NO: 20-1722
YEAR/SECTION: SBIT-2A
DATE: 11/3/2021
PROBLEM #1: Write an SQL query to print details of the Workers who have joined in Feb’2014.
PROBLEM #1(SOLUTION): USING INNER JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
INNER JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
PROBLEM #1: Write an SQL query to print details of the Workers who have joined in Feb’2014.
PROBLEM #1(SOLUTION): USING RIGHT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
RIGHT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
PROBLEM #1: Write an SQL query to print details of the Workers who have joined in Feb’2014.
PROBLEM #1(SOLUTION): USING LEFT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
LEFT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
PROBLEM #1: Write an SQL query to print details of the Workers who have joined in Feb’2014.
PROBLEM #1(SOLUTION): USING FULL JOIN/UNION LEFT AND RIGHT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
RIGHT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2
UNION
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
LEFT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2;
OUTPUT:
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
PROBLEM #2: Write an SQL query to fetch the names of workers who earn the highest salary.
PROBLEM #2(SOLUTION): USING INNER JOIN
SELECT WORKER_TABLE.WORKER_ID, WORKER_TABLE.FIRST_NAME, WORKER_TABLE.LAST_NAME,
WORKER_TABLE.SALARY, TITLE_TABLE.WORKER_TITLE
FROM WORKER_TABLE
INNER JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE SALARY = (SELECT MAX(SALARY) FROM WORKER_TABLE);
OUTPUT:
PROBLEM #2: Write an SQL query to fetch the names of workers who earn the highest salary.
PROBLEM #2(SOLUTION): USING RIGHT JOIN
SELECT WORKER_TABLE.WORKER_ID, WORKER_TABLE.FIRST_NAME, WORKER_TABLE.LAST_NAME,
WORKER_TABLE.SALARY, TITLE_TABLE.WORKER_TITLE
FROM WORKER_TABLE
RIGHT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE SALARY = (SELECT MAX(SALARY) FROM WORKER_TABLE);
OUTPUT:
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
PROBLEM #2: Write an SQL query to fetch the names of workers who earn the highest salary.
PROBLEM #2(SOLUTION): USING LEFT JOIN
SELECT WORKER_TABLE.WORKER_ID, WORKER_TABLE.FIRST_NAME, WORKER_TABLE.LAST_NAME,
WORKER_TABLE.SALARY, TITLE_TABLE.WORKER_TITLE
FROM WORKER_TABLE
LEFT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE SALARY = (SELECT MAX(SALARY) FROM WORKER_TABLE);
OUTPUT:
PROBLEM #2: Write an SQL query to fetch the names of workers who earn the highest salary.
PROBLEM #2(SOLUTION): USING FULL JOIN/UNION LEFT AND RIGHT JOIN
SELECT WORKER_TABLE.WORKER_ID, WORKER_TABLE.FIRST_NAME, WORKER_TABLE.LAST_NAME,
WORKER_TABLE.SALARY, TITLE_TABLE.WORKER_TITLE
FROM WORKER_TABLE
RIGHT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE SALARY=(SELECT MAX(SALARY) FROM WORKER_TABLE)
UNION
SELECT WORKER_TABLE.WORKER_ID, WORKER_TABLE.FIRST_NAME, WORKER_TABLE.LAST_NAME,
WORKER_TABLE.SALARY, TITLE_TABLE.WORKER_TITLE
FROM WORKER_TABLE
LEFT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE SALARY=(SELECT MAX(SALARY) FROM WORKER_TABLE);
OUTPUT:
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
PROBLEM #3: Write an SQL query to print details of workers excluding first names, “Kelly” and “Mae” from
Worker table.
PROBLEM #3(SOLUTION): USING INNER JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
INNER JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE FIRST_NAME NOT IN ('KELLY','MAE')
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
PROBLEM #3: Write an SQL query to print details of workers excluding first names, “Kelly” and “Mae” from
Worker table.
PROBLEM #3(SOLUTION): USING RIGHT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
RIGHT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE FIRST_NAME NOT IN ('KELLY','MAE')
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
PROBLEM #3: Write an SQL query to print details of workers excluding first names, “Kelly” and “Mae” from
Worker table.
PROBLEM #3(SOLUTION): USING LEFT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
LEFT JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE FIRST_NAME NOT IN ('KELLY','MAE')
ORDER BY WORKER_TABLE.WORKER_ID;
OUTPUT:
PROBLEM #3: Write an SQL query to print details of workers excluding first names, “Kelly” and “Mae” from
Worker table.
PROBLEM #3(SOLUTION): USING FULL JOIN/UNION LEFT AND RIGHT JOIN
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
INNER JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE FIRST_NAME NOT IN ('KELLY','MAE')
UNION
SELECT WORKER_TABLE.*, TITLE_TABLE.*
FROM WORKER_TABLE
INNER JOIN TITLE_TABLE
ON WORKER_TABLE.WORKER_ID = TITLE_TABLE.WORKER_ID
WHERE FIRST_NAME NOT IN ('KELLY','MAE');
QUEZON CITY UNIVERSITY
COLLEGE OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
OUTPUT:
