-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';
-- write a SQL query to find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.
SELECT first_name,last_name from employees WHERE salary>(SELECT Salary FROM employees WHERE employee_id=163);
-- write a SQL query to find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.
SELECT first_name,last_name,salary,department_id,job_id FROM employees WHERE job_id=(SELECT job_id FROM employees WHERE employee_id=169);
-- write a SQL query to find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.
SELECT first_name,last_name,department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees group by department_id);
-- write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.
SELECT first_name,last_name,employee_id from employees WHERE salary>(SELECT AVG(salary)FROM employees);
-- From the following table, write a SQL query to find those employees who earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.
SELECT first_name,last_name,salary ,department_id FROm employees WHERE salary<ALL(selectsalary FROm employees WHERE department_id=70);
SELECT first_name,last_name,salary ,department_id FROM employees WHERE salary < ALL(select salary FROM employees WHERE department_id=70);
-- From the following table, write a SQL query to find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.
SELECT first_name,last_name,employee_id,salary from employees WHERE manager_id= (SELECT employee_id FROM employees where first_name='Payam');
-- write a SQL query to find all those employees who work in the Finance department. Return department ID, name (first name), job ID and department name.
SELECT e.department_id,e.first_name,e.job_id ,d.department_id FROM employees e,departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
-- From the following table, write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.
SELECT * FROM employees WHERE (Salary,manager_id)=(SELECT 3000,121);
-- From the following table, write a SQL query to find those employees whose ID matches any of the number 134, 159 and 183. Return all the fields.
SELECT * FROM employees WHERE employee_id IN (134,159,183);
-- From the following table, write a SQL query to find those employees whose salary is in the range 1000, and 3000 (Begin and end values have included.). Return all the fields.
SELECT *FROM employees WHERE salary BETWEEN 1000 AND 3000;
-- From the following table and write a SQL query to find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.
SELECT * FROM employees WHERE salary BETWEEN (SELECT MIN(salary) FROM employees) AND 2500;
-- write a SQL query to find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.
SELECT * FROM employees where department_id NOT IN(SELECT department_id from departments where manager_id BETWEEN 100 AND 200);
-- write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
SELECT * FROM employees WHERE employee_id IN (SELECT employee_id FROM employees WHERE salary=(SELECT MAX (salary) FROM employees WHERE salary<(SELECT MAX (salary) FROM employees)));
-- write a SQL query to find those employees who work in the same department where 'Clara' works. Exclude all those records where first name is 'Clara'. Return first name, last name and hire date.
SELECT first_name, last_name, hire_date FROM employees WHERE department_id = ( SELECT department_id FROM employees WHERE first_name = 'Clara') AND first_name <> 'Clara';
-- write a SQL query to find those employees who work in a department where the employee’s first name contains a letter 'T'. Return employee ID, first name and last name
SELECT employee_id, first_name, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%T%' );
-- write a SQL query to find those employees who earn more than the average salary and work in a department with any employee whose first name contains a character a 'J'. Return employee ID, first name and salary.
SELECT employee_id,first_name,salary FROM employees WHERE salary >AVG(salary) AND Department_id IN (SELECT department_id from employees WHERE first_name LIKE "%J%);
-- write a SQL query to find those employees whose department located at 'Toronto'. Return first name, last name, employee ID, job ID.
SELECT first_name,last_name,employee_id,job_id FROM employees WHERE department_id=(select department_id FROM departments WHERE location_id=(Select location_id from locations WHERE city='Toronto'));
-- From the following table, write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is 'MK_MAN'. Return employee ID, first name, last name, job ID.
SELECT employee_id,first_name,last_name,job_id FROM employees WHERE salary < ANY(SELECT salary FROM employees WHERE job_id='MK_MAN');
-- write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is ‘MK_MAN’. Exclude employees of Job title ‘MK_MAN’. Return employee ID, first name, last name, job ID.
SELECT employee_id,first_name,last_name,job_id FROM employees WHERE salary<ANY(SELECT salary from employees where job_id='MK_MAN') AND job_id <> 'MK_MAN' );
-- write a SQL query to find those employees whose salary is more than average salary of any department. Return employee ID, first name, last name, job ID.
SELECT employee_id,first_name,last_name,job_id FROM employees WHERE salary>ALL(SELECT AVG(salary)FROM employees group by department_id );
-- write a SQL query to find any existence of those employees whose salary exceeds 3700. Return first name, last name and department ID
SELECT first_name,last_name,department_id FROM employees WHERE exists (SELECT * FROM employees WHERE salary>3700);
-- write a SQL query to find total salary of those departments where at least one employee works. Return department ID, total salary.
SELECT departments.department_id, result1.total_amt
FROM departments,
( SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE result1.department_id = departments.department_id;
-- Write a query to display the employee id, name ( first name and last name ) and the job id column with a modified title SALESMAN for those employees whose job title is ST_MAN and DEVELOPER for whose job title is IT_PROG.
SELECT employee_id,first_name,last_name, CASE job_id WHEN 'ST_MAN' THEN 'SALESMAN' WHEN 'IT_PROG' THEN 'DEVELOPER' ELSE job_id END AS designation,salary FROM employees;
-- Write a query to display the employee id, name ( first name and last name ), salary and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.
SELECT employee_id,first_name,last_name ,salary,CASE WHEN salary>=(SELECT AVG(Salary) FROM employees) THEN 'HIGH' ELSE 'Low' END as SalaryStatus FROM employees;
-- Write a query to display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.
SELECT employee_id, first_name, last_name, salary AS SalaryDrawn,
ROUND((salary -(SELECT AVG(salary) FROM employees)),2) AS AvgCompare,
CASE WHEN salary >=
(SELECT AVG(salary)
FROM employees) THEN 'HIGH'
ELSE 'LOW'
END AS SalaryStatus
FROM employees;
-- From the following table, write a SQL query to find all those departments where at least one or more employees work.Return department name.
SELECT department_name
FROM departments
WHERE department_id IN
(SELECT DISTINCT(department_id)
FROM employees);
-- write a SQL query to find those employees who work in departments located at 'United Kingdom'. Return first name.
SELECT first_name
FROM employees
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id IN
(SELECT location_id
FROM locations
WHERE country_id =
(SELECT country_id
FROM countries
WHERE country_name='United Kingdom')));
-- From the following table, write a SQL query to find those employees who earn more than average salary and who work in any of the ‘IT’ departments. Return last name
SELECT last_name from employees WHERE department_id IN(SELECT department_id FROM departments WHERE department_name LIKE '%IT')AND salary>(SELECT AVG (salary) FROM employees );
-- From the following table, write a SQL query to find all those employees who earn more than an employee whose last name is 'Ozer'. Sort the result in ascending order by last name. Return first name, last name and salary.
SELECT first_name,last_name,salary FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name='Ozer' Order By last_name);
-- From the following tables, write a SQL query to find those employees who work under a manager based in ‘US’. Return first name, last name.
SELECT first_name,last_name FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE country_id='US')));
-- From the following table, write a SQL query to find all those departments where at least one or more employees work.Return department name
SELECT department_name
FROM departments
WHERE department_id IN
(SELECT DISTINCT(department_id)
FROM employees);
-- From the following tables, write a SQL query to find those employees whose salary is greater than 50% of their department's total salary bill. Return first name, last name.
SELECT e1.first_name,e1.last_name FROM employees e1 WHERE salary> (SELECT (SUM(salary))*.5 FROM employees e2 WHERE e1.department_id=e2.department_id);
-- From the following tables, write a SQL query to find those employees who are managers. Return all the fields of employees table.
SELECT *
FROM employees
WHERE EXISTS
(SELECT *
FROM departments
WHERE manager_id = employee_id);
-- From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.
SELECT *FROM employees WHERE employee_id=ANY(SELECT manager_id FROM departments);
-- SELECT a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city
FROM employees a, departments b, locations c
WHERE a.salary =
(SELECT MAX(salary)
FROM employees
WHERE hire_date BETWEEN '01/01/2002' AND '12/31/2003')
AND a.department_id=b.department_id
AND b.location_id=c.location_id;
-- SELECT a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city
FROM employees a, departments b, locations c
WHERE a.salary =
(SELECT MAX(salary)
FROM employees
WHERE hire_date BETWEEN '01/01/2002' AND '12/31/2003')
AND a.department_id=b.department_id
AND b.location_id=c.location_id;
-- write a SQL query to find those departments, located in the city ‘London’. Return department ID, department name.
SELECT department_id,department_name FROM departments WHERE location_id=(SELECT location_id from locations WHERE city='London');
-- write a SQL query to find those employees who earn more than the average salary. Sort the result-set in descending order by salary. Return first name, last name, salary, and department ID.
SELECT first_name, last_name , salary, department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees )
ORDER BY salary DESC;
-- From the following table, write a SQL query to find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.
SELECT first_name,last_name,department_id FROM employees WHERE salary>All(SELECT salary from employees WHERE department_id=40);
-- write a SQL query to find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID
SELECT department_id,department_name FROM departments WHERE location_id=(SELECT location_id FROM departments WHERE department_id=30);
-- write a SQL query to find those employees who work in that department where the employee works of ID 201. Return first name, last name, salary, and department ID.
SELECT first_name,last_name,salary,department_id FROM employees WHERE department_id=(SELECT department_id from employees WHERE employee_id=201);
From the following table, write a SQL query to find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.
-- From the following table, write a SQL query to find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.
SELECT first_name,last_name,salary,department_id FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id=40);
-- From the following table, write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID
SELECT first_name,last_name,department_id FROM employees WHERE department_id=(SELECT department_id FROM departments WHERE department_name='Marketing');
-- From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID
SELECT first_name,last_name,salary,department_id FROM employees WHERE salary>Any(SELECT salary FROM employees WHERE department_id=40);
-- From the following table, write a SQL query to find those employees who joined after the employee whose ID is 165. Return first name, last name and hire date.
SELECT first_name,last_name,hire_date FROM employees WHERE hire_date>(SELECT hire_date FROM employees employee_id=165);
-- From the following table, write a SQL query to find those employees who earn less than the average salary, and work at the department where the employee ‘Laura’ (first name) works. Return first name, last name, salary, and department ID.
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE salary <
(SELECT AVG(salary)
FROM employees )
AND department_id =
(SELECT department_id
FROM employees
WHERE first_name = 'Laura');
SELECT first_name,last_name,salary,department_id FROM employees WHERE department_id IN(SELECT department_id FROM department_id WHERE location_id=(SELECT location_id FROM employees WHERE location_name='London'));
-- From the following tables, write a SQL query to find the city of the employee of ID 134. Return city.
SELECT city
FROM locations
WHERE location_id =
(SELECT location_id
FROM departments
WHERE department_id =
(SELECT department_id
FROM employees
WHERE employee_id=134));
-- From the following tables, write a SQL query to find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.
SELECT *
FROM departments
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID
FROM employees
WHERE EMPLOYEE_ID IN
(SELECT EMPLOYEE_ID
FROM job_history
GROUP BY EMPLOYEE_ID
HAVING COUNT(EMPLOYEE_ID) > 1)
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) > 7000);
-- From the following tables, write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.
SELECT * FROM departments
WHERE department_id IN
( SELECT department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>=8000);
-- From the following table, write a SQL query to find those managers who supervise four or more employees. Return manager name, department ID.
SELECT first_name||' '||last_name AS manager_name,department_idFROM employees WHERE employees_is IN (SELECT manager_id FROM employees GROUP BY manager_id Having count(*)>4);
-- write a SQL query to find those employees who worked as a ‘Sales Representative’ in the past. Return all the fields of jobs.
SELECT *
FROM jobs
WHERE job_id IN
(SELECT job_id
FROM employees
WHERE employee_id IN
(SELECT employee_id
FROM job_history
WHERE job_id='SA_REP'));
From the following table, write a SQL query to find those employees who earn second-lowest salary of all the employees. Return all the fields of employees.
SELECT *
FROM employees m
WHERE 2 = (SELECT COUNT(DISTINCT salary )
FROM employees
WHERE salary <= m.salary);
-- From the following table, write a SQL query to find those departments managed by 'Susan'. Return all the fields of departments.
SELECT *
FROM departments
WHERE manager_id IN
(SELECT employee_id
FROM employees
WHERE first_name='Susan');
-- From the following table, write a SQL query to find those employees who earn highest salary in a department. Return department ID, employee name, and salary.
SELECT department_id, first_name || ' ' || last_name AS Employee_name, salary
FROM employees a
WHERE salary =
(SELECT MAX(salary)
FROM employees
WHERE department_id = a.department_id);
-- From the following table, write a SQL query to find those employees who did not have any job in the past. Return all the fields of employees.
SELECT *
FROM employees
WHERE employee_id NOT IN
(SELECT employee_id
FROM job_history);
To embed this project on your website, copy the following code and paste it into your website's HTML: