CREATE TABLE employees (
EMPLOYEE_ID INT(6),
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
EMAIL VARCHAR(100),
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE,
JOB_ID VARCHAR(20),
SALARY Decimal(10,2),
COMMISSION_PCT Decimal(4,2),
MANAGER_ID INT(6),
DEPARTMENT_ID INT(4)
);
INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '123-456-7890', STR_TO_DATE('2023-01-01', '%Y-%m-%d'), 'MANAGER', 5000, NULL, NULL, 1),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', STR_TO_DATE('2023-02-15', '%Y-%m-%d'), 'SALESPERSON', 3000, 0.05, 1, 2),
(3, 'David', 'Johnson', 'david.johnson@example.com', '555-555-5555', STR_TO_DATE('2023-03-10', '%Y-%m-%d'), 'ANALYST', 4000, NULL, 1, 3),
(4, 'Emily', 'Anderson', 'emily.anderson@example.com', '444-444-4444', STR_TO_DATE('2023-04-20', '%Y-%m-%d'), 'ENGINEER', 4500, NULL, 2, 1),
(5, 'Michael', 'Brown', 'michael.brown@example.com', '777-777-7777', STR_TO_DATE('2023-05-05', '%Y-%m-%d'), 'SALESPERSON', 3200, 0.03, 2, 2),
(6, 'Sarah', 'Davis', 'sarah.davis@example.com', '111-222-3333', STR_TO_DATE('2023-06-10', '%Y-%m-%d'), 'ANALYST', 3800, NULL, 2, 3);
select FIRST_NAME AS "First Name",
LAST_NAME AS "Last Name"
from employees;
select *
from employees;
Select distinct DEPARTMENT_ID from employees;
Select FIRST_NAME, LAST_NAME,SALARY, (0.15*SALARY) AS PF from employees
;
Select AVG(SALARY) AS AVG_SALARY, count(EMPLOYEE_ID) AS Total_no_of_employees from employees;
Select SUBSTRING(FIRST_NAME,1,3) from employees;
Select Count(Distinct JOB_ID) from employees;
Select 171*214+625 Result;
Select FIRST_NAME,LAST_NAME,length(FIRST_NAME)+length(LAST_NAME) As Name_Length from employees;
Select Trim(FIRST_NAME) As First_name from employees;
select * from employees
where FIRST_NAME REGEXP '[0-9]';
Select FIRST_NAME,LAST_NAME,round(SALARY/12,2) AS Annual_Salary from employees;
Select FIRST_NAME,LAST_NAME,DEPARTMENT_ID from employees
where DEPARTMENT_ID IN (1,3)
order by DEPARTMENT_ID
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM employees
WHERE SALARY NOT BETWEEN 3000 AND 4000
AND DEPARTMENT_ID IN (1, 3);
Telegram: DB developers channel