About SQL Classic Questions

Recently practiced a SQL to share to everyone,

First on the topic:

--Department table

CREATE TABLE DEPT (
DEPTNO INT PRIMARY KEY,--Department number
Dname VARCHAR (14),--Department name
LOC VARCHAR (13)); --Department Address

INSERT into DEPT VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT into DEPT VALUES ("DALLAS");
INSERT into DEPT VALUES (' SALES ', ' CHICAGO ');
INSERT into DEPT VALUES (+, ' OPERATIONS ', ' BOSTON ');

--Employee table

CREATE TABLE EMP
(
EMPNO INT PRIMARY KEY,--employee number
Ename VARCHAR (10),--Employee name
Job VARCHAR (9),--work
MGR double,--Direct leader number
HireDate date,--Entry time
SAL DOUBLE,--Salary
COMM DOUBLE,--bonus
DEPTNO INT,--Department number
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO));

SELECT * from EMP;

INSERT into EMP VALUES
(7369, ' SMITH ', ' clerk ', 7902, ' 1980-12-17 ', 800,null,20);
INSERT into EMP VALUES
(7499, ' ALLEN ', ' salesman ', 7698, ' 1981-02-20 ', 1600,300,30);
INSERT into EMP VALUES
(7521, ' WARD ', ' salesman ', 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 ', ' salesman ', 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 ', ' ANALYST ', 7566, ' 1987-07-13 ', 3000,null,20);
INSERT into EMP VALUES
(7839, ' KING ', ' president ', NULL, ' 1981-11-17 ', 5000,null,10);
INSERT into EMP VALUES
(7844, ' TURNER ', ' salesman ', 7698, ' 1981-09-08 ', 1500,0,30);
INSERT into EMP VALUES
(7876, ' ADAMS ', ' clerk ', 7788, ' 1987-07-13 ', 1100,null,20);
INSERT into EMP VALUES
(7900, ' JAMES ', ' clerk ', 7698, ' 1981-12-03 ', 950,null,30);
INSERT into EMP VALUES
(7902, ' FORD ', ' ANALYST ', 7566, ' 1981-12-03 ', 3000,null,20);
INSERT into EMP VALUES
(7934, ' MILLER ', ' clerk ', 7782, ' 1982-01-23 ', 1300,null,10);

--Salary Level table

CREATE TABLE Salgrade
(GRADE INT,--wage grade
Losal double,--minimum wage
Hisal DOUBLE); --Maximum wage
INSERT into Salgrade VALUES (1,700,1200);
INSERT into Salgrade VALUES (2,1201,1400);
INSERT into Salgrade VALUES (3,1401,2000);
INSERT into Salgrade VALUES (4,2001,3000);
INSERT into Salgrade VALUES (5,3001,9999);

SELECT * from Salgrade;

There's a lot of similarities on the internet, but I'd like to take a look at it myself. Help:

--1. Returns the department name and department number of the owning employee. (dept,emp)
SELECT DISTINCT D.deptno,d.dname from Dept d,emp E where E.deptno=d.deptno;
--2. Pay more than Smith's employee information.
Select Sal from emp where Ename= ' Smith ';
SELECT * FROM EMP where sal>800

SELECT * from emp where sal> (select Sal from emp where Ename= ' Smith ');
--3. Return the name of the employee and the leader to whom it belongs. (self-connected)
Select E.ename,l.ename from EMP e,emp l where e.mgr=l.empno;
--4. Return the employee's employment date earlier than the employee who led the employment date and the name of their leader. (can be directly compared in date type)
Select E.ename,l.ename from EMP e,emp l where E.mgr=l.empno and e.hiredate<l.hiredate;
--5. Returns the employee name and the department name where it is located.

Select E.ename,d.dname from emp e,dept D where E.deptno=d.deptno;
--6. Return the name and department of the employee who is engaged in clerk work
Select E.ename,d.dname from emp e,dept D where E.deptno=d.deptno and e.job= ' clerk ';
-7. Return the department number and the minimum wage for this department.
Select Deptno,min (SAL) from the EMP group by DEPTNO;

--8. Return the names of all employees in the sales department.
Select Deptno from dept where dname= ' sales ';
Select ename from emp where deptno=30

Select ename from emp where deptno= (select Deptno from dept where dname= ' sales ')

--9. Return employees who pay more than the average wage.
Select AVG (SAL) from EMP;
SELECT * from emp where Sal;?

SELECT * from emp where sal > (select AVG (SAL) from EMP)

--10. Return to the employee who is doing the same job as Scott.
Select Job from emp where ename= ' SCOTT ';
SELECT * from emp where job=?

SELECT * from emp where job= (select Job from emp where ename= ' Scott ') and ename <> ' Scott ';

--11. Return the employee's name and salary with the same salary as the 30 department.
Select Sal from EMP where deptno=30
Select Ename,sal from emp where Sal in (??)

Select Ename,sal from emp where Sal in (select Sal from emp where deptno=30)

---12. Returns employee information for wages above 30 departments for all employees.
Select Max (SAL) from EMP where deptno=30
SELECT * from emp where Sal;?

SELECT * from emp where sal > (select Max (SAL) from EMP where deptno=30)
SELECT * from emp where Sal >all (select Sal from EMP where deptno=30);

--13. Return the department number, department name, department location, and the total number of employees in each department.
Select Deptno,count (*) from EMP Group BY (DEPTNO)

Select D.*,tmp.total from Dept D, (select Deptno,count (*) total from EMP Group by (DEPTNO)) TMP where D.deptno=tmp.deptno;

Select D.*,tmp.total
From Dept D
Left JOIN (select Deptno,count (*) Total
From EMP Group BY (DEPTNO)) TMP
On D.deptno=tmp.deptno;
--14. Return the employee's name, department name and salary.
Select E.ename,d.dname,e.sal from emp e,dept D where E.deptno=d.deptno;
Select E.ename,d.dname,e.sal from emp e joins dept D on E.deptno=d.deptno;

--15. Return employee details. (including department name)
Select E.*,d.dname from emp e joins dept D on E.deptno=d.deptno;

--16. Return the employee's work and the minimum wage for the job.
Select Job,min (SAL) from the EMP group by job

--17. Return the minimum wage for different department managers.
Select Deptno,min (SAL) from the EMP where job= ' manager ' GROUP by DEPTNO;

--18. Calculate the employee's annual salary and sort it by annual salary.
Select Ename,sal*12+ifnull (comm,0) yearsal from the EMP order by Yearsal;

--19. Returns the name of the employee whose salary is at level fourth.
SELECT * from Salgrade where grade=4;
Select E.ename from emp E, (SELECT * from Salgrade where grade=4) tmp where e.sal between Tmp.losal and Tmp.hisal;

Select E.ename from EMP e,salgrade s where s.grade=4 and e.sal between S.losal and S.hisal;
--20. Return the employee's name, department seat, and level two minimum wage and maximum wage at two
Select E.ename,d.loc,s.losal,s.hisal from emp e,salgrade s,dept D where s.grade=2 and E.deptno=d.deptno and e.sal between S.losal and S.hisal;

Select E.ename,d.loc,s.losal,s.hisal
From EMP E
Join Salgrade S
On s.grade=2 and e.sal between S.losal and S.hisal
Join Dept D
On e.deptno = D.deptno

--21. Salary level more than Smith's employee information.
Select S.hisal from EMP e,salgrade s where E.ename= ' Smith ' and e.sal between S.losal and S.hisal;
SELECT * from emp where Sal;?

SELECT * from emp where sal > (select S.hisal from EMP e,salgrade s where E.ename= ' Smith ' and e.sal between S.losal and S.hisal)

I am a small white one. We welcome all kinds of suggestions,

About SQL Classic Questions