Dbms sql queries
Two tables
Create two tables
create table customers ( customer_id int primary key, customer_name varchar,email varchar,phone
int);
create table orders (order_id int primary key, order_date date,total_amount int,customer_id int,
foreign key(customer_id) REFERENCES customers(customer_id));
SELECT c.customer_id, c.customer_name, orders1.total_amount
FROM customers1 c,orders1
where orders1.customer_id=c.customer_id and orders1.total_amount>160
GROUP BY c.customer_id, c.customer_name;
SELECT MAX(Salary) AS ThirdHighestSalary
FROM Employees
WHERE Salary NOT IN (
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 2
);
SELECT Category, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Category
HAVING SUM(Amount) > 1000;
Given 2 tables ENGINEER and DATA, query the sum of Count of all the engineers
whose Type is FrontEnd.
SELECT SUM(ENGINEER.Count) as A FROM ENGINEER INNER JOIN DATA ON
ENGINEER.ID = DATA.ID WHERE DATA.Type = 'FrontEnd';
Given a table STUDENT, query for all the Names with Class 1 and SubjectCount
greater than 3.
SELECT Name FROM STUDENT WHERE Class = 1 AND SubjectCount > 3;
Given a table COUNTRY, query for all the country names with Code NA.
SELECT Name FROM COUNTRY WHERE Code = 'NA';
Given a table STUDY, query for all the attributes, which have marks greater than
80.
SELECT * FROM STUDY WHERE ID IN (SELECT ID FROM STUDY WHERE Marks >
80);
Max salary
SELECT MAX(salary) AS max_salary
FROM employees;
2nd max salary
SELECT MAX(salary) AS second_max_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Find employees whose salary is greater than the average salary.
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Correlated query
SELECT employee_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id =
e.department_id);
Count the number of employees in each department and show only those with
more than 5 employees.
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING employee_count > 5;
Find the total sales amount for each product category.
SELECT p.product_category, SUM(s.sales_amount) AS total_sales
FROM products p
INNER JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_category;
Find the departments with an average salary greater than $60000.
SELECT d.department_id, d.department_name, AVG(e.salary) AS average_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING average_salary > 60000;
Retrieve information about employees and their departments, filtering for a
specific department.
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE departments.department_name = 'Sales';
Retrieve all employees whose names start with "J".
SELECT employee_name
FROM employees
WHERE employee_name LIKE 'J%';
