mean

Creating two table named employee and branch

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  birth_day DATE,
  sex VARCHAR(10),
  salary INT,
  super_id INT,
  branch_id INT
    );


CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  manager_id INT,
  manager_start_date DATE
  );

Inserting values in both table named employee and branch

INSERT INTO employee VALUES (1000, 'Sam', 'Shah', '1977-11-17', 'M', 230000, 105, 2);
INSERT INTO employee VALUES(1001, 'Ram', 'Kapoor', '1951-05-11', 'M', 140000, 100, 1);
INSERT INTO employee VALUES(1002, 'Martina', 'Gates', '1984-03-15', 'F', 95000, 100, 3);
INSERT INTO employee VALUES(1003, 'Angela', 'Porter', '1951-06-25', 'F', 83000, 102, 2);
INSERT INTO employee VALUES(1004, 'Kelly', 'Kapoor', '1990-02-05', 'F', 65000, 102, 2);
INSERT INTO employee VALUES(1005, 'Stave', 'Kharel', '1988-02-19', 'M', 79000, 102, 2);
INSERT INTO employee VALUES(1006, 'Joshina', 'Martin', '1989-09-05', 'F', 88000, 100, 1);
INSERT INTO employee VALUES(1007, 'Mandy', 'Cooper', '1963-07-22', 'F', 75000, 106, 3);
INSERT INTO employee VALUES(1008, 'Jamie', 'Hudson', '1968-10-01', 'M', 61000, 106, 3);


INSERT INTO branch VALUES(3, 'Data Analyst', 1006, '1998-02-13');
INSERT INTO branch VALUES(2, 'Data Scientist', 1002, '1992-04-06');
INSERT INTO branch VALUES(1, 'Corporate', 1000, '2006-02-09');

Here are some of the examples using the commands in the chitsheet as follows:

Find all employees from table ‘employee’

SELECT *
FROM employee;

Find all the employee whose salary is greater than 85000

SELECT *
FROM employee
WHERE salary>85000;

Find all ‘employee’ and ordered it by ‘salary’

SELECT *
from employee
ORDER BY salary;

Find all employee and order it by ‘sex’ then name

SELECT *
from employee
ORDER BY sex, first_name, last_name;

Find the first three employees in the table

SELECT *
from employee
LIMIT 3;

Find out all the different genders which is male or female

SELECT DISTINCT sex
FROM employee;

Find the number of employee having table name emp_id

SELECT COUNT(emp_id)
FROM  employee;

Find the number of employee having female sex and birth_day greater than 1971-01-01

SELECT COUNT(emp_id)
FROM  employee
WHERE  sex = 'F' AND birth_day >'1971-01-01';

Find all employees at branch_id 2

SELECT *
FROM employee
WHERE branch_id = 2;

Find all female employees at branch 2

SELECT *
FROM employee
WHERE branch_id = 2 AND sex = 'F';

Find all employees born between 1970 and 1975

SELECT *
FROM employee
WHERE birth_day BETWEEN '1970-01-01' AND '1975-12-31';

Find all employees named Jim, Michael, Johnny or David

SELECT *
FROM employee
WHERE first_name IN ('Sam', 'Kelly', 'Martina', 'Stave');

Find the number of employees, average salary and total salary.

SELECT COUNT(super_id) as Count, AVG(salary) as avg_salary, sum(salary) as totalSalary
FROM employee;

Find out how many males and females there are

SELECT COUNT(sex), sex
FROM employee
GROUP BY sex

Find any branch name who are ‘Data’

SELECT *
FROM branch
WHERE branch_name LIKE '%Data%';

Find any employee born on the 11th day of the month

SELECT *
from employee
where birth_day like '%-11';

Find a list of employee and branch names

SELECT e.first_name AS Employee_Branch_Names
FROM employee e
UNION
SELECT b.branch_name
FROM branch b;

Find the common list of ids, names of employee and branch

SELECT e.emp_id, e.first_name, b.branch_name
FROM employee e
JOIN branch b     
ON e.emp_id = b.manager_id;

Find the list of ids, names of employee and branch but not missing any of the employee folder

SELECT e.emp_id, e.first_name, b.branch_name
FROM employee e
LEFT JOIN branch b     
ON e.emp_id = b.manager_id;

Find the list of ids, names of employee and branch but not missing any of the branch name

SELECT e.emp_id, e.first_name, b.branch_name
FROM employee e
RIGHT JOIN branch b     
ON e.emp_id = b.manager_id;