SQL statement exercises

1. Select all employees in department 30;
Note: All employees are to be checked

scoped to sector 30
code show as below:
mysql> select * from emp where deptno = 30;

2. List the names, numbers and department numbers of all clerks (CLERK);

Note: What you want to check is name, serial number and department serial number

clerk
code show as below:
mysql> select empno,ename,deptno from emp where job='CLERK';

3. Find employees whose bonuses are higher than their salaries;

to check the staff

The limit is that the bonus is higher than the salary
code show as below:
mysql> select * from emp where comm>sal;

4. Find employees whose bonus is higher than 60% of salary;

to check the staff

The limit is that the bonus is higher than the salary 60%
code show as below:
mysql> select * from emp where comm>(sal*0.6);

5. Find out the details of all managers (MANAGER) in department 10 and all clerks (CLERK) in department 20;
All the information is to be checked, and the scope is limited to
Note: The manager and clerk fields should be capitalized, and the query relationship is or

code show as below:
SQL>select * from emp

where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

6. Find out the details of all managers (MANAGER) in department 10, all clerks (CLERK) in department 20, and all employees who are neither managers nor clerks but whose salary is greater than or equal to 2000;

code show as below
mysql>select * from emp

where (deptno=10 and job='MANAGER')

or (deptno=20 and job='CLERK')

or ((Job!='MANAGER' and job!='CLERK') and sal > =2000);


There is a syntax error here, on the fourth line, it should be '>='

The third condition can also be limited by using IN. According to the requirements in the question, using not in can be replaced by

code show as below:
(job not in ('MANAGER','CLERK') and sal > =2000)

7. Find out the different jobs of employees who receive bonuses;

It's work to check,
Scope is
 employee receiving bonus
code show as below:
1,mysql> select job from emp where comm is not null;

2,mysql> select job from emp where not comm is null;
Both methods can be queried, but the writing of the statement is different.


If you want to remove duplicate options, you can use the following code,

mysql> select distinct job from emp where not comm is null;

8. Find employees who do not receive bonuses or whose bonuses are less than 100;

![insert image description here](https://img-blog.csdnimg.cn/3aea318626b6461289f0e71bc39fbbc4.png)
To check is the employee (details),

Scope is

no bonus  comm for null

The collected bonus is less than 100
code show as below:
mysql> select * from emp where comm is null or comm <100;

9. Find all employees who are employed on the penultimate day of each month;
Note: This topic requires a lot of conditions, each month, the third last day, all employees, many conditions, word by word to complete the screening.

Ideas:
    First take out the last day of the month of the employment date (take out the number of days) and then subtract 2. If it is the number of days of employment, then the condition is met.
step:
1,Take out the date of employment first day1
2,Take out the last day of the month of employment day2
3,reduce date with big date day2-day1 If it is equal to 2, output

analyze:

mysql> select last_day(hiredate) from emp;
 
mysql> select hiredate,to_char(hiredate,'yyyy-mm-dd'),to_char(hiredate,'  dd')day1 from emp;
 
mysql>select last_day(hiredate),
 
to_char(last_day(hiredate),'yyyy-mm-dd'),
 
to_char(last_day(hiredate),'  dd')day2
 
from emp;

According to the conditions, the final reserved code segment is:

mysql> select * from emp where ((to_char(last_day(hiredate),'  dd') - to_char(hiredate,'  dd')) =2);


The final effect is:

10. Find employees who were hired earlier than 12 years ago;

Ideas:
       First take out the year of employment, and then use the current year of employment   >13 just print
The idea code is as follows:
mysql> select to_char(hiredate,'yyyy')year1 from emp;

mysql> select to_char(sysdate,' yyyy')year2 from emp;

finally:

code show as below:
mysql> select * from emp where (to_char(sysdate,' yyyy') - to_char(hiredate,'yyyy')) > 13 ;

11. Display the names of all employees in capital letters;

code show as below:
mysql> select initcap(ename) from emp;

12. Display the names of employees with exactly 5 characters;

mysql> select ename from emp where length(ename)=5;

13. Display the names of employees without "R";

mysql> select ename from emp where ename not like '%R%';

14. Display the first three characters of all employees' names;

According to the normal counting, instead of starting from 0, it is the programmer who starts counting from 0

mysql> select ename,substr(ename,0,3) from emp;

mysql> select ename,substr(ename,1,3) from emp;

15. Display the names of all employees and replace all "A" with "a";

mysql> select replace(ename,'A','a') from emp;

16. Show the names and dates of employment of employees who have completed 10 years of service;
method one

mysql> select ename,hiredate from emp where
months_between(sysdate,hiredate)>120;

Method Two:

mysql> select ename,hiredate from emp where floor((sysdate-hiredate)/365)>10;

17. Display the detailed information of employees, sorted by name;

Sorting is ascending by default

mysql> select * from emp order by ename;
mysql> select * from emp order by ename asc;

18. Display the employee's name and date of employment, and rank the oldest employee at the top according to his years of service;

Note: Sort in ascending order here, because the time is small in front (older)

mysql> select ename,hiredate from emp order by hiredate asc;

19. Display the name, job and salary of all employees, sort by the descending order of the job, if the job is the same, sort by the salary;

mysql> select ename,job,sal from emp order by job desc,sal asc;

20. Display the names of all employees, the year and month when they joined the company, and sort by the month of the employment date. If the month is the same, the employee with the earliest year will be ranked first;

Since the year and month appear, it should be split first

Months are sorted in ascending order by default, employees with the earliest year are sorted in ascending order

Summarize:

Once there is something related to the year, if you want the older one to be in the front, then sort it in ascending order
mysql> select  ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'   mm') month from emp  order by month,year asc;

21. Display the daily wages of all employees in the case of 30 days in a month, ignoring the remainder;

To check is the daily salary (ignore the remainder) using the alias (daily salary)

The range is limited to 30 days in a month

Ideas:

       use sal/30 And ignore the remainder, the alias replaces the calculated daily salary
mysql> select ename,trunc(sal/30)day_sal from emp;

22. Find all employees hired in February (of any year);

to check the staff

The scope is limited to those hired in February

Ideas:
    use to_char The conversion function takes out the month, subtracts 02, and prints if it is equal to 0
mysql> select ename from emp where ((to_char(hiredate,' mm') -02)=00);
If you need to print all the information, then use*

select * from emp where ((to_char(hiredate,' mm') -02)=00);

23. For each employee, display the number of days he joined the company;

Ideas:
       Subtract the date of employment from the current date to get the number of days to join     
  mysql> select ename,hiredate,sysdate-hiredate from emp;

24. Display the names of all employees whose name field contains "A" anywhere;

code show as below:
mysql> select ename from emp where ename like '%A%';

25. Display the service life of all employees in the form of year, month and day;
I didn't do this very well, I borrowed from the Internet and learned a bit!

Step 1: Find the number of years of employment for each employee: the total number of months employed/12=number of years

select  ename,hiredate,trunc(months_between(sysdate,hiredate)/12)toatl_year from emp;

Step 2: Find the number of months. The ignored decimal point in the above calculation is the month, so just take the remainder directly.

select ename,hiredate,

trunc(months_between(sysdate,hiredate)/12)toatl_year,

trunc(mod(months_between(sysdate,hiredate),12))total_month

from emp;
Step 3: Find the number of days, the most accurate way is to calculate within the range of no more than 30 days;

current time in sysdate I take it out and use it at the time of employment hiredate Take it out, but the gap between the two is too big, so there will definitely be errors, then you must find a way to hiredate date promoted to the same sysdate The difference is within the range of 30 days,

There are two functions;

       MONTHS_BETWEEN();Finds the number of months between two dates.

       ADD_MONTHS():Adds the date after the specified month to a date.

if we assume hiredate+months from today=A new date, and this new date must be consistent with sysdate Not more than 30 days apart.

                                  

       1987 onboarding +number of months ( month between(sys dateļ¼Œhiredate  )

Tags: Database MySQL SQL

Posted by TylerL on Thu, 05 Jan 2023 18:21:51 +0300