Scribd
Upload
9 views

Dbms SQL Queries

Uploaded by

Aisha Yahya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views

Dbms SQL Queries

Uploaded by

Aisha Yahya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

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%';

You might also like