DB2 SQL Queries
DB2 SQL queries allow users to interact with databases for retrieving, inserting, updating, and
deleting data. Below are examples of commonly used DB2 SQL queries categorized by
functionality.
1. Data Retrieval (SELECT)
Retrieve all rows from a table
SELECT * FROM EMPLOYEE;
Retrieve specific columns
SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE;
Retrieve with condition
SELECT * FROM EMPLOYEE WHERE DEPTNO = 'A00';
Retrieve sorted results
SELECT * FROM EMPLOYEE ORDER BY LASTNAME ASC;
Aggregate function
SELECT DEPTNO, COUNT(*) FROM EMPLOYEE GROUP BY DEPTNO;
2. Data Modification (INSERT, UPDATE, DELETE)
Insert a new row
INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, DEPTNO) VALUES
('0010','John','Smith','A00');
Update employee data
UPDATE EMPLOYEE SET SALARY = SALARY * 1.10 WHERE DEPTNO = 'A00';
Delete rows
DELETE FROM EMPLOYEE WHERE EMPNO = '0010';
3. Joins
Inner Join
SELECT [Link], [Link], [Link] FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON
[Link] = [Link];
Left Join
SELECT [Link], [Link], [Link] FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON
[Link] = [Link];
4. Subqueries
Subquery in WHERE clause
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE DEPTNO = (SELECT DEPTNO FROM
DEPARTMENT WHERE DEPTNAME = 'SALES');
5. Advanced Queries
Common Table Expression (CTE)
WITH HIGH_SALARY AS (SELECT * FROM EMPLOYEE WHERE SALARY > 80000) SELECT * FROM
HIGH_SALARY;
Union
SELECT EMPNO, FIRSTNME FROM EMPLOYEE_A UNION SELECT EMPNO, FIRSTNME FROM EMPLOYEE_B;
Case expression
SELECT EMPNO, FIRSTNME, CASE WHEN SALARY > 80000 THEN 'HIGH' ELSE 'NORMAL' END AS
SALARY_LEVEL FROM EMPLOYEE;
