-- 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';
-- 27. From the following table, write a SQL query to find those employees who joined on 1st May 91. Return complete information about the employees.
SELECT *FROM employees WHERE hire_date='1991-05-01';
-- 28. From the following table, write a SQL query to find those employees working under the manger whose ID is 68319. Return employee ID, employee name, salary, and age
SELECT emp_id,emp_name,salary,age(current_date,hire_date)"experience" WHERE manager_id=68316;
-- 29. From the following table, write a SQL query to find those employees who earn more than 100 as daily salary. Return employee ID, employee name, salary, and age.
SELECT emp_id,emp_name,salary,age(current_date,hire_date)"Experience" FROM employees WHERE (salary/30)>100;
-- 30. From the following SELECT emp_name FROM employees table, write a SQL query to find those employees who retired after 31-Dec-99, completion of 8 years of service period. Return employee name.
SELECT emp_name from employees WHERE hire_date+interval'96 months'>'1999-12-31';
-- 31. From the following table, write a SQL query to find those employees whose salary is an odd value. Return complete information about the employees.
SELECT * FROM employees WHERE mod(salary,2)=1;
-- 32. From the following table, write a SQL query to find those employees whose salary contains only three digits. Return complete information about the employees.
SELECT * FROM employees WHERE length(TRIM(TO_CHAR(salary,'9999')))=3;
-- 33. From the following table, write a SQL query to find those employees who joined in the month of APRIL. Return complete information about the employees.
SELECT * FROM employees WHERE to_char(hire_date,'MON')='APR';
-- 34. From the following table, write a SQL query to find those employees who joined in the company before 19th of a month. Return complete information about the employees.
SELECT * FROM employees WHERE to_char(hire_date,'DD')<'19';
-- 35. From the following table, write a SQL query to find those employees who are SALESMAN and experience more than 10 months. Return complete information about the employees.
SELECT *
FROM employees
WHERE job_name = 'SALESMAN'
AND to_char(hire_date,'MONTH')>'10';
-- 36. From the following table, write a SQL query to find those employees of department id 3001 or 1001 and joined in the year 1991. Return complete information about the employees
SELECT * FROM employees WHERE t0_char(hire_date,'YYYY')='1991' AND (dep_id=3001 OR dep_id=1001);
-- 37. From the following table, write a SQL query to find those employees who are working for the department ID 1001 or 2001.Return complete information about the employees.
SELECT * FROM employees WHERE dep_id='1001' OR dep_id='2001';
-- 38. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’ and work in the department ID 2001. Return complete information about the employees
SELECT * FROM employees WHERE dep_id='2001'AND job_name='CLERK';
-- 39. From the following table, write a query in SQL to find those employees where -
1. the employees receive some commission which should not be more than the salary and annual salary including commission is below 34000.
2. Designation is ‘SALESMAN’ and working in the department ‘3001’. Return employee ID, employee name, salary and job name.
SELECT emp_name,emp_id,salary,job_name FROM employees WHERE *(salary+commission)<34000 AND commission IS NOT NULL AND commission<salary AND job_name='SALESMAN' AND dep_id=3001;
-- 40. From the following table, write a SQL query to find those employees who are either CLERK or MANAGER. Return complete information about the employees.
SELECT * FROM employees WHERE job_id IN ('CLERK','MANAGER');
-- 41. From the following table, write a SQL query to find those employees who joined in any year except the month of February. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'MON')NOT IN ('FEB');
--42. From the following table, write a SQL query to find those employees who joined in the year 91. Return complete information about the employees
SELECT * FROM employees WHERE hire_date BETWEEN '1991-01-01'AND '1991-12-31';
-- 43. From the following table, write a SQL query to find those employees who joined in the month of June 1991. Return complete information about the employees.
SELECT *
FROM employees
WHERE hire_date BETWEEN '1991-06-01' AND '1991-06-30';
-- 44. From the following table, write a SQL query to find all the employees whose annual salary is within the range 24000 and 50000 (Begin and end values are included.). Return complete information about the employees.
SELECT *
FROM employees
WHERE 12*salary BETWEEN 24000 AND 50000;
-- 45. From the following table, write a SQL query to find all those employees who have joined on 1st May, 20th Feb, and 3rd Dec in the year 1991. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'DD-MON-YY') IN ('01-MAY-91',
'20-FEB-91',
'03-DEC-91');
-- 45. From the following table, write a SQL query to find all those employees who have joined on 1st May, 20th Feb, and 3rd Dec in the year 1991. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'DD-MON-YY') IN ('01-MAY-91',
'20-FEB-91',
'03-DEC-91');
-- 46. From the following table, write a SQL query to find those employees working under the managers 63679 or 68319 or 66564 or 69000. Return complete information about the employees.
SELECT * FROM employees WHERE manager_id IN(63679,68319,66564,69000);
-- 47. From the following table, write a SQL query to find those employees who joined after the month JUNE in the year 1991 and within this year. Return complete information about the employees.
SELECT * FROM employees WHERE to_char(hire_date,'mon-1991')<'JUN-1991';
-- 48. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees.
SELECT * FROM employees WHERE to_char(hire_date,'YY') BETWEEN '90'AND '99';
-- 49. From the following table, write a SQL query to find those managers who are in the department 1001 or 2001. Return complete information about the employees.
SELECT * FROM employees WHERE job_name='MANAGER'AND(dep_id=1001 OR dep_id=2001);
-- 50. From the following table, write a SQL query to find those employees who joined in the month FEBRUARY with a salary range between 1001 to 2000 (Begin and end values are included.). Return complete information about the employees.
SELECT * FROM employee WHERE to_char(hire_date,'mon')='FEB'AND salary BETWEEN 1001 AND 2000;
-- 51. From the following table, write a SQL query to find those employees who joined before or after the year 1991. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char (hire_date,'YYYY') NOT LIKE '1991';
-- 52. From the following table, write a SQL query to find employees along with department name. Return employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name.
SELECT e.emp_id,
e.emp_name,
e.job_name,
e.manager_id,
e.hire_date,
e.salary,
e.commission,
e.dep_id,
d.dep_name
FROM employees e,
department d
WHERE e.dep_id = d.dep_id;
-- 53. From the following tables, write a SQL query to find those employees who earn 60000 in a year or not working as an ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department ID, and grade.
SELECT e.emp_name,
e.job_name,
(12*e.salary)"Annual Salary",
e.dep_id,
d.dep_name,
s.grade
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal
AND (((12*e.salary)>= 60000)
OR (e.job_name != 'ANALYST'));
-- 54. From the following table, write a SQL query to find those employees whose salary is higher than the salary of their managers. Return employee name, job name, manager ID, salary, manager name, manager's salary.
SELECT w.emp_name,
w.job_name,
w.manager_id,
w.salary,
m.emp_name "Manager",
m.emp_id,
m.salary "Manager_Salary"
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND w.salary > m.salary;
-- 55. From the following table, write a SQL query to find those employees whose salary is between 2000 and 5000 (Begin and end values are included.) and location is PERTH. Return employee name, department ID, salary, and commission.
SELECT e.emp_name,
e.dep_id,
e.salary,
e.commission
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND d.dep_location = 'PERTH'
AND e.salary BETWEEN 2000 AND 5000;
-- 56. From the following table, write a SQL query to find those employees whose department ID is 1001 or 3001 and salary grade is not 4. They joined the company before 1992-12-31. Return grade, employee name.
SELECT s.grade,
e.emp_name
FROM employees e,
salary_grade s
WHERE e.dep_id IN (1001,
3001)
AND hire_date < ('1992-12-31')
AND (e.salary BETWEEN s.min_sal AND s.max_sal
AND s.grade NOT IN (4));
-- 57. From the following table, write a SQL query to find those employees whose manager name is JONAS. Return employee id, employee name, job name, manager ID, hire date, salary, department ID, employee name.
SELECT w.emp_id,
w.emp_name,
w.job_name,
w.manager_id,
w.hire_date,
w.salary,
w.dep_id,
m.emp_name
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND m.emp_name = 'JONAS';
-- 58. From the following table, write a SQL query to find the name and salary of the employee FRANK. Salary should be equal to the maximum salary within his or her salary group.
SELECT e.emp_name,
e.salary
FROM employees e,
salary_grade s
WHERE e.emp_name = 'FRANK'
AND e.salary BETWEEN s.min_sal AND s.max_sal
AND e.salary = s.max_sal ;
-- 59. From the following table, write a SQL query to find those employees who are working either as a MANAGER or an ANALYST with a salary in the range 2000, 5000 (Begin and end values are included.) without any commission. Return complete information about the employees.
SELECT * FROM employees WHERE job_name IN ('MANAGER','ANALYST') AND SALARY BETWEEN 2000 AND 5000 AND commission IS NULL;
-- 60. From the following table, write a SQL query to find those employees working at PERTH, or MELBOURNE with an experience over 10 years. Return employee ID, employee name, department ID, salary, and department location.
SELECT e.emp_id,
e.emp_name,
e.dep_id,
e.salary,
d.dep_location
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND d.dep_location IN ('PERTH',
'MELBOURNE')
AND EXTRACT(MONTH
FROM age(CURRENT_DATE, hire_date)) > 10;
-- 61. From the following table, write a SQL query to find those employees whose department location is SYDNEY or MELBOURNE with a salary range of 2000, 5000 (Begin and end values are included.) and joined in 1991. Return employee ID, employee name, department ID, salary, and department location.
SELECT e.emp_id,e.emp_name,e.dep_id,e.salary,d.dep_location FROM employees e,department d WHERE e.dep_id=d.dep_id AND d.dep_location IN('SYDNEY','MELBOURNE')AND to_char(e.hire_date,'YY')='91' AND e.salary BETWEEN 2000 AND 5000;
-- 62. From the following table, write a SQL query to find those employees of MARKETING department come from MELBOURNE or PERTH within the grade 3 ,4, and 5 and experience over 25 years. Return department ID, employee ID, employee name, salary, department name, department location and grade
SELECT e.dep_id,
e.emp_id,
e.emp_name,
e.salary,
d.dep_name,
d.dep_location,
s.grade
FROM employees e,
salary_grade s,
department d
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal
AND s.grade IN (3,4,
5)
AND EXTRACT(YEAR
FROM age(CURRENT_DATE, hire_date)) > 25
AND (d.dep_name = 'MARKETING'
AND D.dep_location IN ('MELBOURNE',
'PERTH'));
-- 63. From the following table, write a SQL query to find those employees who are senior to their manager. Return complete information about the employees
SELECT *
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND w.hire_date < m.hire_date;
SELECT * FROM employees ORDER BY dep_id ASC,job_name DESC;
-- 68. From the following table, write a SQL query to find the entire unique jobs in descending order. Return job name.
SELECT DISTINCT job_name FROM employees ORDER BY job_name DESC;
-- 69. From the following table, write a SQL query to find the employees in the ascending order of their annual salary. Return employee ID, employee name, monthly salary, salary/30 as Daily_Salary, and 12*salary as Anual_Salary.
SELECT emp_id,
emp_name,
salary Monthly_Salary,
salary/30 Daily_Salary,
12*salary Anual_Salary
FROM employees
ORDER BY Anual_Salary ASC;
-- 70. From the following table, write a SQL query to find those employees who are either 'CLERK' or 'ANALYST’. Sort the result set in descending order on job_name. Return complete information about the employees.
SELECT * FROM employees WHERE job_name= 'CLERK' OR job_name='ANALYST' ORDER BY job_name DESC;
-- 71. From the following table, write a SQL query to find the department location of employee ‘CLARE’. Return department location.
SELECT dep_location
FROM department d,
employees e
WHERE e.emp_name = 'CLARE'
AND e.dep_id = d.dep_id ;
-- 72. From the following table, write a SQL query to find those employees who joined on 1-MAY-91, or 3-DEC-91, or 19-JAN-90. Sort the result-set in ascending order by hire date. Return complete information about the employees.
SELECT * FROM employees WHERE hire_date IN('1991-05-01','1991-12-03','1990-01-19') ORDER BY hire_date ASC;
-- 73. From the following table, write a SQL query to find those employees who draw salary less than 1000. Sort the result-set in ascending order by salary. Return complete information about the employees.
SELECT *
FROM employees
WHERE salary < 1000
ORDER BY salary;
-- 74. From the following table, write a SQL query to list the employees in ascending order on the salary. Return complete information about the employees.
SELECT *
FROM employees
ORDER BY salary ASC;
-- 75. From the following table, write a SQL query to list the employees in the ascending order on job name and descending order on employee id. Return complete information about the employees.
SELECT * FROM employees ORDER BY job_name ASC,emp_id DESC;
-- 76. From the following table, write a SQL query to list the unique jobs of department 2001 and 3001 in descending order. Return job name.
SELECT DISTINCT job_name FROM employees WHERE dep_id IN(2001,3001) ORDER BY job_name DESC;
-- 77. From the following table, write a SQL query to list all the employees except PRESIDENT and MANAGER in ascending order of salaries. Return complete information about the employees.
SELECT *
FROM employees
WHERE job_name NOT IN ('PRESIDENT',
'MANAGER')
ORDER BY salary ASC;
-- 78. From the following table, write a SQL query to find the employees whose annual salary is below 25000. Sort the result set in ascending order of the salary. Return complete information
about the employees.
SELECT * FROM employees WHERE (12*salary)<25000 ORDER BY salary ASC;
-- 79. From the following table, write a SQL query to list the employees who works as a SALESMAN. Sort the result set in ascending order of annual salary. Return employee id, name, annual salary, daily salary of all the employees.
SELECT e.emp_id,
e.emp_name,
12*salary "Annual Salary",
(12*salary)/365 "Daily Salary"
FROM employees e
WHERE e.job_name = 'SALESMAN'
ORDER BY "Annual Salary" ASC;
-- 80. From the following table, write a SQL query to list the employee ID, name, hire date, current date and experience of the employees in ascending order on their experiences..
SELECT emp_id,emp_name,hire_date,current_date,age(current_date,hire_date)EXP FROM employees order by EXP ASC;
--81. From the following table, write a SQL query to list the employees in ascending order of designations of those joined after the second half of 1991.
SELECT *
FROM employees
WHERE hire_date>('1991-6-30')
AND date_part('year',hire_date)=1991
ORDER BY job_name ASC;
-- 82. From the following tables, write a SQL query to find the location of all the employees working in FINANCE or AUDIT department. Sort the result-set in ascending order by department ID. Return complete information about the employees.
SELECT *
FROM employees e,
department d
WHERE d.dep_name IN ('FINANCE',
'AUDIT')
AND e.dep_id = d.dep_id
ORDER BY e.dep_id ASC;
-- 83. From the following tables, write a SQL query to find the employees along with grades in ascending order. Return complete information about the employees.
SELECT *
FROM employees e,
salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
ORDER BY grade ASC;
-- 84. From the following table, write a SQL query to find the employees according to the department in ascending order. Return name, job name, department, salary, and grade.
SELECT e.emp_name,
e.job_name,
d.dep_name,
e.salary,
s.grade
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal
ORDER BY e.dep_id ;
-- 85. From the following tables, write a SQL query to find all employees except CLERK and sort the result-set in descending order by salary. Return employee name, job name, salary, grade and department name.
SELECT e.emp_name,
e.job_name,
e.salary,
s.grade,
d.dep_name
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal
AND e.job_name NOT IN('CLERK')
ORDER BY e.salary DESC;
-- 86. From the following table, write a SQL query to find those employees work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary.
SELECT e.emp_id,
e.emp_name,
e.salary,
s.grade,
d.dep_name,
age(CURRENT_DATE, hire_date) AS "Experience",
12 * e.salary "Annual Salary"
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id IN (1001,
2001)
AND e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal ;
-- 87. From the following table, write a SQL query to list the details of the employees along with the details of their departments.
SELECT *
FROM employees e,
department d
WHERE e.dep_id= d.dep_id;
-- 88. From the following table, write a SQL query to list the employees who are senior to their MANAGERS. Return complete information about the employees.
SELECT * FROM employees e, employees w WHERE w.manager_id=e.emp_id AND w.hire_date<e.hire_date;
-- 89. From the following table, write a SQL query to find those employees who work in the department 1001. Sort the result-set in ascending order by salary. Return employee ID, employee name, salary and department ID.
SELECT e.emp_id,
e.emp_name,
e.salary,
e.dep_id
FROM employees E
WHERE e.dep_id = 1001
ORDER BY e.salary ASC;
-- 90. From the following table, write a SQL query to find the highest salary. Return highest salary.
SELECT MAX(salary)FROM employees;
-- 91. From the following table, write a SQL query to find the average salary and average total remuneration (salary and commission) for each type of job. Return name, average salary and average total remuneration.
SELECT job_name,
avg(salary),
avg(salary+commission)
FROM employees
GROUP BY job_name;
-- 92. From the following table, write a SQL query to compute the total annual salary distributed against each job in the year 1991. Return job name, total annual salary.
SELECT job_name,sum(12*salary) FROM employees WHERE to_char(hire_date,'YYYY')='1991'GROUP BY job_name;
-- 93. From the following table, write a SQL query to list the employee id, name, department id, location of all the employees.
SELECT e.emp_id,e.emp_name,e.dep_id,d.dep_location FROM employees WHERE e.dep_id=d.dep_id;
-- 94. From the following table, write a SQL query to find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name
SELECT e.emp_id,e.emp_name,e.dep_id,d.dep_location,d.dep_name FROM employees e,department d WHERE emp_id IN(1001,2001);
-- 95. From the following table, write a SQL query to find those employees whose salary is in the range minimum and maximum salary (Begin and end values are included.). Return employee ID, name, salary and grade.
SELECT e.emp_id,
e.emp_name,
e.salary,
s.grade
FROM employees e,
salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal ;
-- 96. From the following table, write a SQL query to list the managers and number of employees work under them. Sort the result set in ascending order on manager. Return manager ID and number of employees under them.
SELECT w.manager_id,
count(*)
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
GROUP BY w.manager_id
ORDER BY w.manager_id ASC;
-- 97. From the following table, write a SQL query to count the number of employees of each designation in each department. Return department id, job name and number of employees.
SELECT dep_id,
job_name,
count(*)
FROM employees
GROUP BY dep_id,
job_name;
-- 98. From the following table, write a SQL query to find those departments where at least two employees work. Return department id, number of employees.
SELECT dep_id,count(*) FROM employees GROUP BY dep_id HAVING COUNT(*)>=2;
-- 99. From the following table, write a SQL query to list the grade, number of employees, and maximum salary of each grade.
SELECT s.grade,
count(*),
max(salary)
FROM employees e,
salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
GROUP BY s.grade;
-- 100. From the following table, write a SQL query to find those departments where at least two employees work as a SALESMAN in each grade. Return department name, grade and number of employees.
SELECT d.dep_name,
s.grade,
count(*)
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id = d.dep_id
AND e.job_name = 'SALESMAN'
AND e.salary BETWEEN s.min_sal AND s.max_sal
GROUP BY d.dep_name,
s.grade
HAVING count(*) >= 2;
-- 101. From the following tables, write a SQL query to find those departments where less than four employees work. Return department ID, number of employees.
SELECT dep_id,
count(*)
FROM employees
GROUP BY dep_id
HAVING count(*)<4;
-- 102. From the following tables, write a SQL query to find those departments where at least two employees work. Return department name, number of employees.
SELECT d.dep_name,
count(*)
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_name
HAVING count(*) >= 2;
-- 103. From the following table, write a SQL query to check whether the employees ID are unique or not. Return employee id, number of employees.
SELECT emp_id,
count(*)
FROM employees
GROUP BY emp_id;
-- 104. From the following table, write a SQL query to find number of employees and average salary. Group the result set on department id and job name. Return number of employees, average salary, department ID, and job name.
SELECT count(*),
avg(salary),
dep_id,
job_name
FROM employees
GROUP BY dep_id,
job_name;
-- 105. From the following table, write a SQL query to find those employees whose name start with 'A' and six characters in length. Return employee name.
SELECT emp_name
FROM employees
WHERE emp_name LIKE 'A%'
AND length(emp_name)=6;
-- 106. From the following table, write a SQL query to find those employees whose name is six characters in length and the third character must be 'R'. Return complete information about the employees.
SELECT * FROM employees WHERE length(emp_name)=6 AND emp_name LIKE '__R%';
-- 107. From the following table, write a SQL query to find those employees whose name is six characters in length, starting with 'A' and ending with 'N'. Return number of employees.
SELECT * FROM employees WHERE length(emp_name)=6 AND emp_name LIKE'A%N';
-- 108. From the following table, write a SQL query to find those employees who joined in the month of where the second letter is 'a'. Return number of employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'mon') LIKE '_a%';
-- 109. From the following table, write a SQL query to find those employees whose names contain the character set 'AR' together. Return complete information about the employees
SELECT *
FROM employees
WHERE emp_name LIKE '%AR%';
-- 110. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees.
SELECT * FROM employees WHERE to_char(hire_date,'yy') LIKE '9%';
-- 111. From the following table, write a SQL query to find those employees whose ID not start with the digit 68. Return employee ID, employee ID using trim function.
SELECT emp_id,trim(to_char(emp_id,'99999')) FROM employees WHERE trim (to_char(emp_id,'99999')) NOT LIKE '68%';
-- 112. From the following table, write a SQL query to find those employees whose names contain the letter 'A’. Return complete information about the employees.
SELECT *
FROM employees
WHERE emp_name LIKE '%A%';
-- 113. From the following table, write a SQL query to find those employees whose name ends with 'S' and six characters long. Return complete information about the employees.
SELECT *
FROM employees
WHERE emp_name LIKE '%S'
AND LENGTH (emp_name) = 6;
-- 114. From the following table, write a SQL query to find those employees who joined in any month, but the month name contain the character ‘A’. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'MON') LIKE '%A%';
-- 115. From the following table, write a SQL query to find those employees who joined in any month, but the name of the month contain the character ‘A’ in second position. Return complete information about the employees.
SELECT *
FROM employees
WHERE to_char(hire_date,'MON') LIKE '_A%';
--
To embed this project on your website, copy the following code and paste it into your website's HTML: