DB2 Practice - Some samples

DB2_create.sql

/* Create table syntax with an auto-incremented field. */
DROP TABLE NewEmployees;

/* Note the auto increment syntax. It is slightly different. */
CREATE TABLE NewEmployees (
		ID INT GENERATED BY DEFAULT AS IDENTITY 
		(START WITH 100 INCREMENT BY 10),
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	salary INT, 
	PRIMARY KEY(ID)
);

INSERT INTO  NewEmployees (first_name, last_name, salary)
VALUES ('Jane', 'Doe', '50000');
INSERT INTO  NewEmployees (first_name, last_name, salary)
VALUES ('Janelle', 'Doe', '55000');
INSERT INTO  NewEmployees (first_name, last_name, salary)
VALUES ('James', 'Doe', '60000');

SELECT SUM(salary) FROM NewEmployees;
SELECT AVG(salary) FROM NewEmployees; 
SELECT ID, salary FROM NewEmployees WHERE salary < (SELECT AVG(salary) FROM NewEmployees);