Use of SQL system functions (experiment Five)

Use of SQL system functions (test 5) Functions in query statements

• Check the employee's name and daily salary (keep 1 decimal places);
• Query and display the first name and post of all employees with department number 01 and 02;
• Query and display the names and seniority of all employees;
• To inquire about the names and positions of employees who have been in employment before June 5, 1990;
• The names and dates of the employees who entered the employment in 1990 (using extract, to_date respectively);
• The name and entry date of the employee who entered the job in June;

1. Check the employee's name and daily salary (keep 1 decimal places);

select ename,round(sal/30,1)from emp;

2. Query and display the initials and post of all employees with department number 01 and 02;

Select substr (ename,0,1), job
From EMP
where deptno = ' n ' or deptno = ' 02 ';

3. Query and display the names and seniority of all employees;

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

4. Enquiries about the names and positions of employees who entered the position before June 5, 1990;

select ename,jobfrom empwhere   hiedate < to_date('1990-06-5','yyyy-mm-dd');

5. Enquiries on the names and dates of entry of employees in 1990 (using extract, to_date respectively);

select ename,hiedatefrom empwhere extract(year from hiedate) = '1990'select ename , hiedatefrom empwhere hiedate >= to_date('1990-01-01','yyyy-mm-dd') and hiedate <= to_date  ('1990-12-31','yyyy-mm-dd')

6. Enquiries about the names and dates of the employees who entered the employment in June;

select ename,hiedatefrom empwhere extract(month from hiedate) = '06'

Use statistical functions and corresponding query statements to complete the following work, and the table data validation results are correct

• Statistics of the total number of employees;
• The number of employees in the 2 and above departments and persons;
• Check the total salary, average salary, maximum salary and minimum salary of all employees;
• Check the ' 02 ' employees ' total salary, average salary, maximum salary and minimum salary;
• Query the number of employees and average bonus in each department;
• Statistics of the number of male employees;
• Statistics of the number of male and female employees;
• Number of male and female employees with department numbers 01 and 22;

1. Statistics of the total number of employees;

select count(empno)from emp

2. Number of departments and persons with 2 or more employees;

select count(ename)人数,jobfrom empgroup by jobhaving count(ename)>1

3. Check the total salary, average salary, maximum salary and minimum salary of all employees;

select sum(sal),avg(sal),max(sal),min(sal)from emp

4. Check the total salary, average salary, maximum salary and minimum salary of employees in ' 02 ' department;

select sum(sal),avg(sal),max(sal),min(sal)from empwhere deptno = '02'

5. Check the number of employees and average bonuses in each department;

select count(ename)人数,job 部门，avg(comn)平均奖金from empgroup by job

6. Statistics of the number of male employees;

select count(ename) 人数from empwhere sex = '男'

7. Statistics of the number of male and female employees;

select count(ename) 人数from empgroup by sex

8. Number of male and female employees who are part of the statistics Department numbered 01 and 22;

select count(sex) from empwhere deptno = '01'or '02'group by sex

Use of SQL system functions (experiment Five)