db_queries.sql

USE dbms_lab;
SELECT COUNT(*) AS total_employees FROM employee;
SELECT * FROM employee WHERE age=22;
SELECT emp_id,name,department FROM employee WHERE salary>=50000;
SELECT name AS "Full Name" FROM employee WHERE department='Finance' AND age=22;
SELECT DISTINCT department FROM employee;
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employee;
SELECT SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employee;
SELECT * FROM employee e1 WHERE EXISTS (SELECT 1 FROM employee e2 WHERE e2.salary=e1.salary AND e2.emp_id<>e1.emp_id);
SELECT name FROM employee ORDER BY salary ASC;
SELECT name,salary FROM employee WHERE salary>=10000 AND age>25 ORDER BY department;
SELECT * FROM employee WHERE city IN ('Delhi','Pune');
SELECT name,department FROM employee WHERE emp_id BETWEEN 2001 AND 2005;
SELECT name FROM employee WHERE city IN (SELECT city FROM employee GROUP BY city HAVING COUNT(*)>1);
SELECT * FROM employee WHERE city = ALL (SELECT city FROM employee);
SELECT * FROM employee WHERE city = ANY (SELECT city FROM employee);
SELECT EXISTS(SELECT 1 FROM employee WHERE city='Delhi') AS any_delhi_exists;
SELECT * FROM employee WHERE department='CSE';
SELECT name FROM employee WHERE name LIKE 'ay%';
SELECT name,department FROM employee WHERE name LIKE '%sh';
SELECT name,department FROM employee WHERE city LIKE 'D%' OR city LIKE '%h';
SELECT * FROM employee WHERE salary>15000 AND name LIKE 'h%';
SELECT name FROM employee WHERE CHAR_LENGTH(name)=3;
SELECT name,city FROM employee WHERE CHAR_LENGTH(name)>=5;
SELECT ejt.name,ejt.dept_name FROM employee ejt JOIN department d ON ejt.department=d.dept_name;
SELECT name FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM employee_department);
SELECT ej.name, d.dept_name FROM employee ej LEFT JOIN department d ON ej.department=d.dept_name WHERE ej.salary>25000;
SELECT d.dept_name, COUNT(DISTINCT p.program_id) AS projects_managed, COUNT(DISTINCT ed.emp_id) AS employees_assigned FROM department d LEFT JOIN program p ON d.dept_name=p.department LEFT JOIN employee_department ed ON ed.dept_id=d.dept_id GROUP BY d.dept_name;
SELECT customer.name AS destination FROM customer INNER JOIN orders o ON customer.customer_id=o.customer_id;
SELECT * FROM customer CROSS JOIN orders;
SELECT customer.name,orders.order_id FROM customer JOIN orders ON customer.customer_id=orders.customer_id WHERE customer.city=orders.order_city;
SELECT s.name, r.program_id FROM student s JOIN register r ON s.roll_no=r.roll_no WHERE r.program_id=(SELECT program_id FROM program WHERE program_name='MCA' LIMIT 1);
SELECT DISTINCT s.* FROM student s JOIN register r ON s.roll_no=r.roll_no;
SELECT * FROM program WHERE fee > (SELECT AVG(fee) FROM program);
SELECT s.name FROM student s JOIN register r ON s.roll_no=r.roll_no JOIN program p ON p.program_id=r.program_id WHERE p.fee<30000;
SELECT * FROM student WHERE roll_no NOT IN (SELECT roll_no FROM register);
SELECT program.program_name FROM program JOIN register r ON program.program_id=r.program_id GROUP BY program.program_id ORDER BY COUNT(r.roll_no) DESC LIMIT 1;
SELECT program.program_name FROM program LEFT JOIN register r ON program.program_id=r.program_id GROUP BY program.program_id ORDER BY COUNT(r.roll_no) ASC LIMIT 1;
SELECT DISTINCT salary FROM employee ORDER BY salary ASC LIMIT 1,1;
SELECT MIN(salary) FROM employee WHERE salary>(SELECT MIN(salary) FROM employee);
SELECT * FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employee
) t WHERE rnk=3;
SELECT name,salary FROM employee e1 WHERE salary > (SELECT AVG(salary) FROM employee e2 WHERE e2.department=e1.department);
SELECT e1.name FROM employee e1 JOIN employee e2 ON e1.department=e2.department WHERE e1.salary < e2.salary;
SELECT e1.name FROM employee e1 JOIN employee e2 ON e1.department=e2.department WHERE e1.age>e2.age;
SELECT * FROM salary_audit ORDER BY changed_at DESC;
SELECT * FROM wards;
SELECT * FROM doctors;
SELECT * FROM patients;
SELECT * FROM tests;
SELECT * FROM bills;
SELECT * FROM program;
SELECT * FROM register;
DROP TABLE IF EXISTS employee_join_temp;