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