- Find all employees whose job is either Clerk or Salesman.
SELECT * FROM emp2019284073 WHERE JOB = "CLERK" OR JOB = "SALESMAN"
- Find any Clerk who is not in department 10
SELECT * FROM emp2019284073 WHERE DEPTNO!=10
Find everyone whose job is Salesman and all the Analysts in department 20
SELECT * FROM emp2019284073 WHERE JOB = 'SALESMAN' OR (JOB = 'ANALYST' AND DEPTNO = 20);
- Find all the employees who earn between £15,000 and £20,000. Show the employee name, department and salary.
SELECT ENAME,DEPTNO,SAL FROM emp2019284073 WHERE SAL BETWEEN 15000 AND 20000;
- Find the name of the President.
SELECT ENAME FROM emp2019284073 WHERE JOB = 'PRESIDENT';
- Find all the employees whose last names end with S
SELECT * FROM emp2019284073 WHERE ENAME LIKE '%S';
- List the employees whose names have TH or LL in them
SELECT * FROM emp2019284073 WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';
- List only those employees who receive commission.
SELECT * FROM emp2019284073 WHERE COMM IS NOT NULL;
Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.
SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM emp2019284073 ORDER BY ENAME;
- Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.
SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM emp2019284073 ORDER BY SAL DESC;
- List all salesmen in descending order by commission divided by their salary
SELECT * FROM emp2019284073 ORDER BY ifnull(COMM, 0)/SAL DESC;
- Order employees in department 30 who receive commision, in ascending order by commission
SELECT * FROM emp2019284073 WHERE DEPTNO=30 AND COMM IS NOT NULL ORDER BY COMM DESC;
Find the names, jobs, salaries and commissions of all employees who do not have managers.
SELECT ENAME,JOB,SAL,COMM FROM emp2019284073 WHERE MGR IS NULL;
Find all the salesmen in department 30 who have a salary greater than or equal to £18000.
SELECT * FROM emp2019284073 WHERE DEPTNO=30 AND JOB = 'SALESMAN' AND SAL >= 18000;
- Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.
SELECT * FROM emp2019284073 WHERE HIREDATE<'1998-01-01' AND (SAL > 5000 OR SAL<1000);
- What is the command to add primary key constraint to EMPNO
ALTER TABLE emp2019284073 ADD PRIMARY KEY(EMPNO);
- What is the command to add a new column EMP_family_name to existing EMP table
ALTER TABLE emp2019284073 ADD EMP_family_name VARCHAR(20);
- How to drop primary key constraint for EMPNO
ALTER TABLE emp2019284073 DROP PRIMARY KEY;
- rename EMP table to EMPLOYEE
RENAME TABLE emp2019284073 TO EMPLOYEE_2019284073;
- rename EMPLOYEE back to EMP
RENAME TABLE EMPLOYEE_2019284073 TO emp2019284073;
- What is the SQL command to remove column EMP_family_name from EMP table
ALTER TABLE emp2019284073 DROP COLUMN EMP_family_name;
- What is the SQL command to copy emp table to employee table
CREATE TABLE EMPLOYEE_2019284073 AS SELECT * FROM emp2019284073;
- What is the SQL command to drop employee table
DROP TABLE EMPLOYEE_2019284073;
- What is the SQL command to display name’s of employee entered interactively from user
SELECT * FROM emp2019284073 WHERE ENAME = '&ENTER';
- What is the SQL command to find the employee whose commission is NULL
SELECT * FROM emp2019284073 WHERE COMM IS NULL;
3. Experiment process and content
From this experiment of MySQL, I learned how to build a database, how to initialize the database, how to insert data into tables and how to select and edit values by language of MySQL.
MySQL has more convenient way to deal with huge data, which makes it fast to run. In addition, it offers enough operations to deal with this data.
During this experiment, errors occur sometimes. With the help of my classmates and knowledge in Internet, I finally finished this experiment successfully.
