Simple SQL partially strengthens exercise questions, simple SQL strengthens exercise questions

Simple SQL partially strengthens exercise questions, simple SQL strengthens exercise questions

Simple query of some SQL exercises

-- Select * from emp where deptno = 30 for all employees in department 30; -- list the names, numbers, and department numbers of all clerks (clers. ename, e. empno, e. deptno from emp e where e. job = 'cler'; -- select * from emp where comm> sal for employees with higher bonuses than salaries; -- select * from emp where comm> sal * 60% for employees with salaries higher than 0.6; -- find all managers in department 10 and all clerks in department 20 (CLERK) select * from emp e where e. deptno = 10 and e. job = 'manager' or e. deptno = 20 and e. job = 'cler'; select * from emp e where (e. deptno = 10 and e. job = 'manager') or (e. deptno = 20 and e. job = 'cler'); select * from emp e where e. deptno = 10 and e. job = 'manager' union select * from emp e where e. deptno = 20 and e. job = 'wheel'; -- find all managers in department 10 and all salesmen in department 20 ), select * from emp e where e. deptno = 10 and e. job = 'manager' unionselect * from emp e where e. deptno = 20 and e. job = 'cler' union select * from emp e where e. sal & gt; 2000 and e. job not in ('manager', 'cler'); -- select distinct e. job from emp e; -- select * from emp e where e. comm is null or e. comm <100; -- select * from emp e where e. hiredate between last_day (hiredate)-3 and last_day (hiredate); -- select * from emp e where (sysdate-e. hiredate)/365> 30; -- display the names of all employees in the capital form of select initcap (ename) from emp; -- select * from emp where length (ename) = 5; -- display select * from emp where ename not like '% K %' for employee names without "R"; -- display the first three characters of all employee names select substr (ename, 0, 3) from emp; -- display the names of all employees, and use 'A' to replace all 'A' select replace (ename, 'A', 'A') from emp; -- select * from emp where (sysdate-hiredate)/365> 30 shows the name and employment date of an employee who has been in service for 30 years; -- displays details of the employee, select * from emp order by ename desc by name in ascending order; -- displays the employee's name and employment date. The oldest employee is ranked first by select ename based on the service life, hiredate from emp order by hiredate asc; -- displays the names, jobs, and salaries of all employees in descending order of work. select ename, job, sal from emp order by job desc, sal asc; select ename, job, sal from emp order by 2 desc, 3; -- display the names of all employees, the year and month of joining the company, sort by the month where the employment date is located. If the month is the same, select ename, to_number (to_char (hiredate, 'yyyy') Year, to_number (to_char (hiredate, 'mm') from emp order by 3 desc, 2 asc; -- displays the daily salary of an employee whose monthly salary is 30 days, ignore the remainder select round (sal/30) daily salary from emp; -- find all employees hired in (any year) April February select * from emp where to_number (to_char (hiredate, 'mm') = 2; -- for each employee, display the number of Days it joins the company select ename, round (sysdate-hiredate) Days from emp; -- select * from emp where upper (ename) like '% A %' for all employees whose names contain "A" at any position is displayed '; -- select ename, hiredate, trunc (months_between (sysdate, hiredate)/12) year, trunc (mod (months_between (sysdate, hiredate ), 12) months, trunc (sysdate-add_months (hiredate, months_between (sysdate, hiredate) dayfrom emp;