Log in, view and use

Mysql-uroot-p password

SHOW DATABASES; -- view all databases

USE DB1; -- use one of the databases

Departmental table

CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,
    DNAME VARCHAR(14), -- Department name
    LOC VARCHAR(13)-- Department address
) ; 

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

SELECT * FROM DEPT;

Employee list

DROP TABLE EMP;

CREATE TABLE EMP(
    EMPNO INT  PRIMARY KEY, -- Employee number
    ENAME VARCHAR(10), -- Employee name
    JOB VARCHAR(9), -- Staff work
    MGR INT, -- Employee's direct leader No
    HIREDATE DATE, -- Entry time
    SAL DOUBLE, -- wages
    COMM DOUBLE, -- bonus
    DEPTNO INT,  -- Department in charge
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));  -- Relation dept surface  

-- ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
INSERT INTO EMP VALUES(7369,'SMITH','Staff member',7566,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','Salesperson',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','Salesperson',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','manager',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','Salesperson',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','manager',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','manager',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','Staff member',7566,'1987-07-03',3000,2000,20);
INSERT INTO EMP VALUES(7839,'KING','Chairman',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNERS','Salesperson',7698,'1981-09-08',1500,50,30);
INSERT INTO EMP VALUES(7876,'ADAMS','Staff member',7566,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','Staff member',7698,'1981-12-03',1250,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','Salesperson',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','Staff member',7782,'1981-01-23',1300,NULL,10);

SELECT * FROM EMP;

Salary scale

CREATE TABLE SALGRADE( 
    GRADE INT,-- Grade
    LOSAL DOUBLE, -- minimum wage
    HISAL DOUBLE ); -- Maximum wage

INSERT INTO SALGRADE VALUES (1,500,1000);
INSERT INTO SALGRADE VALUES (2,1001,1500);
INSERT INTO SALGRADE VALUES (3,1501,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

SELECT * FROM SALGRADE;

1. Find the details of the employees in department 30.

SELECT * FROM
        EMP
WHERE
        DEPTNO=30;

2. Find out the number, name and department number of the employee.

SELECT
    EMPNO,
    ENAME,
    DEPTNO
FROM
    EMP
WHERE
    JOB='Staff member';

3. Retrieve the information of employees whose bonus is more than the basic wage.

SELECT * FROM
        EMP
WHERE
        COMM>SAL;

4. Retrieve the information of employees whose bonus is more than 60% of the basic wage.

SELECT * FROM
        EMP
WHERE
        COMM>SAL*0.6;

5. Find out the employee information with A in the name. ENAME

SELECT * FROM
        EMP
WHERE
    ENAME LIKE '%A%';

6. Find out the employee information whose name starts with A, B and S.

SELECT * FROM 
        EMP
WHERE
    ENAME LIKE 'A%'
OR
    ENAME LIKE 'B%'
OR
    ENAME LIKE 'S%';

7. Find the employee information whose name is 7 characters long.

SELECT * FROM
        EMP
WHERE   
        ENAME LIKE'_______';

8. Employee information whose name does not contain the R character.

SELECT * FROM
        EMP
WHERE
        ENAME
NOT LIKE
        '%R%';  

9. Return the details of the employee and sort by name in ascending order.

SELECT * FROM 
        EMP
ORDER BY
        ENAME;

10. Return employee information in descending order of name and ascending order of salary.

SELECT * FROM
        EMP
ORDER BY 
        ENAME DESC;
        SAL ASC;

11. Calculate the employee's daily salary (based on 30 days).

SELECT 
        ENAME,
        SAL/30  
FROM
        EMP;        

12. Find out the information of the employees who get the bonus.

SELECT * FROM
        EMP
WHERE
        COMM>0;

13. Find out about employees who have less than 100 bonus or who have not received bonus.

SELECT * FROM
        EMP
WHERE
        COMM>0
OR
        COMM IS NULL;

14. Find out the manager of department 10 and the employee information of department 20.

SELECT * FROM 
        emp 
WHERE 
        deptno=10 
AND 
        job='manager'
 OR 
        (deptno=20 AND job='Staff member');

15. Find out the manager of department 10, the employee of department 20 or the employee who is neither a manager nor an employee but whose salary is more than 2000 yuan.

SELECT * FROM
        EMP
WHERE       
        deptno=10
AND
        job='manager'
OR
        deptno=20
AND
        job='Staff member'
OR
        job NOT IN('manager','Staff member')
AND
        sal>2000;