CREATE TABLE snowflake.testschema.emp (employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, salary INT)

INSERT INTO snowflake.testschema.emp(employee_id, first_name, last_name, department_id, salary) VALUES (1, 'John', 'Doe', 101, 50000),(2, 'Jane', 'Smith', 102, 60000),(3, 'Michael', 'Johnson', 101, 55000),(4, 'Emily', 'Williams', 103, 52000),(5, 'Robert', 'Brown', 102, 58000)

CREATE TABLE snowflake.testschema.dept (department_id INT, department_name VARCHAR(100))

INSERT INTO snowflake.testschema.dept VALUES (101, 'HR'), (102, 'Finance'),(103, 'IT')

SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM snowflake.testschema.emp e INNER JOIN snowflake.testschema.dept d ON e.department_id = d.department_id

SELECT d.department_name, SUM(e.salary) AS total_salary FROM snowflake.testschema.emp e INNER JOIN snowflake.testschema.dept d ON e.department_id = d.department_id GROUP BY d.department_name

SELECT (SELECT COUNT(employee_id) FROM snowflake.testschema.emp WHERE department_id = 102) AS finance_employee_count, (SELECT COUNT(employee_id) FROM snowflake.testschema.emp WHERE department_id = 103) AS it_employee_count

CREATE TABLE snowflake.testschema.table7 AS SELECT * FROM snowflake.testschema.emp WHERE salary > 55000       (CTAS)

DROP TABLE IF EXISTS snowflake.testschema.table7

ALTER TABLE snowflake.testschema.emp ADD COLUMN exp INT

ALTER TABLE snowflake.testschema.emp RENAME TO snowflake.testschema.employee;

ALTER TABLE snowflake.testschema.employee DROP COLUMN exp;

ALTER TABLE snowflake.testschema.employee RENAME COLUMN employee_id TO id;