- List the name of all the Manager who manages at least 2 employees.
SELECT ENAME, NUM FROM (SELECT e1.ENAME, COUNT(*) NUM FROM emp2019284073 e1 JOIN emp2019284073 e2 ON e1.EMPNO = e2.MGR GROUP BY e1.ENAME) e3 WHERE NUM >= 2
EXERCISES 6 SUB QUERIES.
- List the name and job of employees who have the same job as Jones.
SELECT ENAME, JOB FROM emp2019284073 WHERE JOB = (SELECT JOB FROM emp2019284073 WHERE ENAME = 'JONES') AND ENAME != 'JONES'
- Find all the employees in Department 10 that have a job that is the same as anyone in department 30.
SELECT e.ENAME FROM emp2019284073 e, emp2019284073 m WHERE e.deptno != m.deptno AND e.deptno = 10 AND m.deptno = 30 AND e.job = m.job;
- List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.
SELECT ename, job, deptno FROM emp2019284073 WHERE (job = (SELECT job FROM emp2019284073 WHERE ename = 'JONES') AND ename != 'JONES') OR sal >= (SELECT sal FROM emp2019284073 WHERE ename = 'FORD' AND ename != 'FORD');
- Find all employees in department 10 that have a job that is the same as anyone in the Sales department
SELECT ENAME FROM emp2019284073 WHERE deptno = 10 AND job = (SELECT job FROM dept2019284073 WHERE dname = 'SALES');
- Find the employees located in Liverpool who have the same job as Allen. Return the results in alphabetical order by employee name.
SELECT ename FROM emp2019284073 e, dept2019284073 d WHERE d.loc = 'LIVERPOOL' AND e.deptno = d.deptno AND job = (SELECT job FROM emp2019284073 WHERE ename = 'ALLEN') AND ename != 'ALLEN' ORDER BY ename;
- Find all the employees that earn more than the average salary of employees in their department.
SELECT e.ename FROM emp2019284073 e, (SELECT deptno, AVG(sal) salVal FROM emp2019284073 GROUP BY deptno) d WHERE e.deptno = d.deptno AND e.sal > d.salVal;
- Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.
SELECT e.ename FROM emp2019284073 e WHERE sal > (SELECT sal FROM emp2019284073 WHERE ename = 'JONES');
- List the Name of all employees who earn Highest salary and Second Highest salary.
SELECT ENAME FROM (SELECT ENAME FROM emp2019284073 ORDER BY SAL DESC) a LIMIT 2
EXERCISES 7 Data Manipulation
Create a new table called loans with columns named LNO NUMBER (3), EMPNO NUMBER (4), TYPE CHAR (1), AMNT NUMBER (8,2), Create all constraints, such as Primary Key, Foreign Key, Check
CREATE TABLE loan2019284073 ( LNO DECIMAL(3) PRIMARY KEY, EMPNO DECIMAL(4), TYPE CHAR(1), AMNT DECIMAL(8 , 2 ) )
- Insert the following data
INSERT INTO loan2019284073 VALUES(23,7499,'M',20000.00); INSERT INTO loan2019284073 VALUES(42,7499,'C',2000.00); INSERT INTO loan2019284073 VALUES(65,7844,'M',3564.00);
- Check that you have created 3 new records in Loans
SELECT * FROM loan2019284073;
- The Loans table must be altered to include another column OUTST NUMBER (8,2)
ALTER TABLE loan2019284073 ADD(OUTSET DECIMAL(8,2))
- Add 10% interest to all M type loans
UPDATE loan2019284073 SET AMNT = AMNT * 1.1 WHERE TYPE = 'M' AND LNO > 0;
- Remove all loans less than £3000.00
DELETE FROM loan2019284073 WHERE AMNT < 3000 AND LNO > 0;
- Change the name of loans table to accounts
ALTER TABLE loan2019284073 RENAME ACCOUNT2019284073;
- Change the name of column LNO to LOANNO
ALTER TABLE `dong2019284073`.`account2019284073` CHANGE COLUMN `LNO` `LOANNO` DECIMAL(3,0) NOT NULL ;
- Create a view for use by personnel in department 30 showing employee name, number, job and hiredate.
CREATE VIEW view2019284073 AS SELECT ENAME, EMPNO, JOB, hiredate FROM emp2019284073 WHERE deptno = 30
- Create a view which shows summary information for each department.
CREATE VIEW view2019284073 AS SELECT * FROM dept2019284073
Experiment process and content
From this experiment of MySQL, I learned how to select different kinds of data from a database, how to edit values by language of MySQL. I also learn the use of “JOIN”, “GROUP BY”, “HAVING”. Now, I can write some uncomplex functions to show data.
During this experiment, errors occur sometimes. With the help of my
classmates and knowledge in Internet, I finally finished this experiment
successfully. For example, in Exercise7 question 5 and question6, the select don’t need to edit or compare the primary key, but in safe mode, the comparison of primary key is necessary. Thus, the script “LNO > 0” is necessary.
