INSERT INTO DEPT (dept_id, dept_name, location) VALUES (1, 'Marketing', 'Boston');

INSERT INTO DEPT (dept_name, location) VALUES (NULL, 'Boston');

INSERT INTO DEPT (dept_name, location) VALUES ('HR', 'Boston');
INSERT INTO EMP (emp_name, job_title, salary, commission, dept_id) VALUES ('Test
User', 'Tester', 0, 0, 1);
SELECT emp_name, salary FROM EMP;
SELECT emp_name, job_title FROM EMP WHERE salary > 60000;
SELECT dept_name FROM DEPT WHERE location = 'New York';
SELECT DISTINCT job_title FROM EMP;
UPDATE EMP SET salary = 70000 WHERE emp_name = 'Bob Smith';
UPDATE DEPT SET location = 'Los Angeles' WHERE dept_name = 'IT';
UPDATE EMP SET commission = 2000 WHERE dept_id = 3;
DELETE FROM EMP WHERE emp_name = 'David Miller';
DELETE FROM EMP WHERE salary < 50000;
DELETE FROM DEPT WHERE dept_name = 'Marketing';
SELECT location from dept, emp where dept.dept_id=emp.dept_id and dept_name='HR'
and emp_id=1;
SELECT emp_name, salary from emp, dept where emp.dept_id=dept.dept_id and
dept_name='HR';
SELECT emp_name from emp e, dept d where e.dept_id=d.dept_id order by dept_name;
SELECT d.dept_name, sum([Link]) as total_commission from emp e join dept d on
e.dept_id = d.dept_id group by d.dept_name order by total_commission desc limit 1;
SELECT emp_name, dept_name from emp e inner join dept d on e.dept_id=d.dept_id;
SELECT emp_name, manager_id from emp;
SELECT emp_name, location from emp e inner join dept d on e.dept_id=d.dept_id;
SELECT emp_name, dept_name from emp e left join dept d on e.dept_id=d.dept_id;
SELECT dept_name, emp_name from emp e right join dept d on e.dept_id=d.dept_id;
SELECT dept_name, manager_id from emp e right join dept d on e.dept_id=d.dept_id;
SELECT dept_name, emp_name, salary from emp e right join dept d on
d.dept_id=e.dept_id and salary>60000;
SELECT e.emp_name, d.dept_name from emp e left join dept d on e.dept_id = d.dept_id
union SELECT e.emp_name, d.dept_name from emp e right join dept d on e.dept_id =
d.dept_id;
SELECT e.emp_name, d.dept_name from emp e join dept d on e.dept_id = d.dept_id;
commit;
rollback;
savepoint s1;
rollback to s1;
SELECT cname from borrower union select cname from depositor;
SELECT distinct [Link] from borrower b join depositor d on [Link] = [Link];
SELECT distinct [Link] from depositor d left join borrower b on [Link] = [Link]
where [Link] is null;
SELECT emp_name, salary FROM EMP WHERE emp_name LIKE 'A%' AND salary > 60000;
SELECT emp_name, job_title, commission FROM EMP WHERE job_title LIKE '%Manager%' OR
commission > 3000;
SELECT emp_name, dept_id FROM EMP WHERE emp_name LIKE '%son' AND dept_id NOT IN (2,
3);
SELECT emp_name, job_title, salary FROM EMP WHERE job_title NOT LIKE '%Developer%'
AND salary >= 50000;
SELECT E.emp_name, D.dept_name FROM EMP E JOIN DEPT D ON E.dept_id = D.dept_id
WHERE E.emp_name LIKE '%Lee%' OR D.dept_name = 'IT';
SELECT emp_name, job_title, commission FROM EMP WHERE job_title NOT IN ('Analyst',
'Developer') AND commission = 0;
SELECT emp_name, job_title, salary FROM EMP WHERE job_title LIKE 'S%' and salary
BETWEEN 50000 AND 80000;
SELECT emp_name, commission FROM EMP WHERE emp_name LIKE '% %' AND NOT commission >
2000;
SELECT E.emp_name, [Link] FROM EMP E JOIN DEPT D ON E.dept_id = D.dept_id WHERE
[Link] NOT IN ('Chicago', 'Boston');
SELECT emp_name, job_title, salary, commission FROM EMP WHERE (job_title LIKE '%er
%' AND salary > 55000) OR commission > 3000;
SELECT dept_id, COUNT(emp_id) AS employee_count FROM EMP GROUP BY dept_id;
SELECT dept_id, AVG(salary) AS avg_salary FROM EMP GROUP BY dept_id;
SELECT dept_id, MAX(salary) AS max_salary FROM EMP GROUP BY dept_id;
SELECT dept_id, MIN(salary) AS min_salary FROM EMP GROUP BY dept_id;
SELECT dept_id, SUM(commission) AS total_commission FROM EMP GROUP BY dept_id;
SELECT job_title, COUNT(emp_id) AS count_per_job FROM EMP GROUP BY job_title;
SELECT job_title, AVG(commission) AS avg_commission FROM EMP GROUP BY job_title;
SELECT emp_name, salary FROM EMP ORDER BY salary DESC;
SELECT dept_name, location FROM DEPT ORDER BY location ASC;
SELECT emp_name, dept_id, salary FROM EMP ORDER BY dept_id ASC, salary DESC;
SELECT job_title, AVG(salary) AS avg_salary FROM EMP GROUP BY job_title ORDER BY
avg_salary DESC;
SELECT dept_id, COUNT(emp_id) AS employee_count FROM EMP GROUP BY dept_id ORDER BY
employee_count DESC;
SELECT dept_id, COUNT(emp_id) AS employee_count FROM EMP GROUP BY dept_id HAVING
COUNT(emp_id) > 1;
SELECT dept_id, AVG(salary) AS avg_salary FROM EMP GROUP BY dept_id HAVING
AVG(salary) > 60000;
SELECT job_title, SUM(commission) AS total_commission FROM EMP GROUP BY job_title
HAVING total_commission > 3000;
SELECT dept_id, MAX(salary) AS max_salary FROM EMP GROUP BY dept_id HAVING
max_salary > 80000;
SELECT dept_id, COUNT(emp_id) AS employee_count FROM EMP GROUP BY dept_id HAVING
employee_count > 1 ORDER BY employee_count DESC;
SELECT job_title, AVG(salary) AS avg_salary FROM EMP GROUP BY job_title HAVING
avg_salary > 50000 ORDER BY avg_salary DESC;
SELECT dept_id, SUM(commission) AS total_commission FROM EMP GROUP BY dept_id
HAVING total_commission > 2000 ORDER BY total_commission DESC;
SELECT job_title, COUNT(emp_id) AS emp_count, AVG(salary) AS avg_salary FROM EMP
GROUP BY job_title HAVING emp_count > 1 AND avg_salary > 55000 ORDER BY job_title
ASC;
SELECT COUNT(emp_id) AS total_employees FROM EMP;
SELECT AVG(salary) AS average_salary FROM EMP;
SELECT MAX(salary) AS max_salary FROM EMP;
SELECT MIN(salary) AS min_salary FROM EMP;
SELECT SUM(salary) AS total_salary FROM EMP;
SELECT COUNT(*) AS count_high_salary FROM EMP WHERE salary > 60000;
SELECT SUM(commission) AS total_commission FROM EMP;
SELECT AVG(commission) AS avg_commission FROM EMP WHERE commission > 0;
SELECT COUNT(dept_id) AS total_departments FROM DEPT;
SELECT MAX(commission) AS max_commission FROM EMP;
SELECT SUM(salary) AS total_salary_IT FROM EMP WHERE dept_id = 2;
SELECT AVG(salary) AS avg_low_salary FROM EMP WHERE salary < 60000;
SELECT COUNT(*) AS count_mid_salary FROM EMP WHERE salary BETWEEN 45000 AND 70000;
SELECT MAX(salary) AS max_salary_finance FROM EMP WHERE dept_id = 3;
SELECT SUM(salary) AS salary_commission_over_1000 FROM EMP WHERE commission > 1000;
SELECT COUNT(*) AS employees_with_managers FROM EMP WHERE manager_id IS NOT NULL;
SELECT MIN(salary) AS min_above_avg FROM EMP WHERE salary > (SELECT AVG(salary)
FROM EMP);
SELECT SUM(commission) AS commission_low_salary FROM EMP WHERE salary < 50000;
SELECT COUNT(*) AS non_HR_employees FROM EMP WHERE dept_id != 1;
SELECT emp_name, salary, salary * 12 AS yearly_salary FROM EMP;
SELECT emp_name, salary, salary + (salary * 0.10) AS increased_salary FROM EMP;
SELECT emp_name, commission, salary, (commission / salary) * 100 AS
commission_percent FROM EMP;
SELECT emp_name, salary, commission FROM EMP WHERE salary > 60000 AND commission >
0;
SELECT emp_name, dept_id FROM EMP WHERE dept_id = 1 OR dept_id = 2;
SELECT emp_name, dept_id FROM EMP WHERE NOT dept_id = 3;
SELECT emp_name, salary FROM EMP WHERE salary >= 50000 AND salary <= 80000;
SELECT emp_name, job_title, commission FROM EMP WHERE job_title = 'Manager' OR
commission > 3000;
SELECT emp_name, job_title, salary FROM EMP WHERE NOT (job_title = 'Developer' AND
salary < 70000);
SELECT emp_name, salary FROM EMP WHERE salary = 60000;
SELECT emp_name, salary FROM EMP WHERE salary != 55000;
SELECT emp_name, salary FROM EMP WHERE salary >= 60000;
SELECT emp_name, dept_id FROM EMP WHERE dept_id IN (1, 2, 3);
SELECT emp_name, dept_id FROM EMP WHERE dept_id NOT IN (2, 3);
SELECT dept_name, location FROM DEPT WHERE location IN ('New York', 'Chicago');
CREATE TABLE DEPT (dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50)
NOT NULL UNIQUE, location VARCHAR(100) NOT NULL);
CREATE TABLE EMP (emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(100) NOT
NULL, job_title VARCHAR(50), salary DECIMAL(10, 2) CHECK (salary > 0), commission
DECIMAL(10, 2) DEFAULT 0, dept_id INT, manager_id INT, FOREIGN KEY (dept_id)
REFERENCES DEPT(dept_id), FOREIGN KEY (manager_id) REFERENCES EMP(emp_id));
INSERT INTO DEPT (dept_name, location) VALUES ('HR', 'New York'), ('IT', 'San
Francisco'), ('Finance', 'Chicago');
INSERT INTO EMP (emp_name, job_title, salary, commission, dept_id) VALUES ('Alice
Johnson', 'Manager', 85000, 5000, 1), ('Bob Smith', 'Developer', 60000, 0, 2),
('Carol Lee', 'Analyst', 55000, 0, 3);
ALTER TABLE EMP ADD COLUMN email VARCHAR(100) UNIQUE;
ALTER TABLE EMP ADD CONSTRAINT chk_commission CHECK (commission <= salary * 0.2);
ALTER TABLE EMP DROP COLUMN email;
ALTER TABLE EMP MODIFY COLUMN job_title VARCHAR(100);