1) Displays the name of the employee who is exactly 5 characters HR@ORA11GR2>SelectLast_name,first_name fromEmployees2 whereLength (first_name)= 5;2displays the names of employees without "R". HR@ORA11GR2>SelectLast_name,first_name fromEmployees2 whereFirst_Name not like '%r%';3) Displays the names of all employees and replaces all "a" HR with a@ORA11GR2>Select Replace(First_Name,'A','a') fromemployees;4displays the names, jobs, and salaries of all employees, sorted in descending order of work, sort by salary if the work is the same. HR@ORA11GR2>SelectLast_name,first_name,job_id,salary fromEmployees2 Order byjob_iddesc, salary;5) shows the daily salary of all employees at one months for 30 days, ignoring the remainder. HR@ORA11GR2>SelectLast_name,first_name,trunc (Salary/ -) daysal fromemployees;6find the HR with A and E in the employee's name@ORA11GR2>Select distinct(first_name) fromEmployees2 whereFirst_Name like '%a%' andFirst_Name like '%e%';7the output result format for the SELECT statement is as follows:Select * fromhr_departments;Select * fromhr_emp;Select * fromHr_region;.......departments is the name of the table and can be queried TABHR@ORA11GR2>Select 'SELECT * from'||'Hr_'||Tname||';' asSelect_from_hr_table fromtabwhereTabtype='TABLE';7Employee Information HR that requires a base salary greater than 1500 and can receive bonuses at the same time@ORA11GR2>Select * fromEmployees2 whereSalary> the andcommission_pct is not NULL;8requires that the last 3 characters of all employees ' names and names be displayed hr@ORA11GR2>SelectFirst_name,substr (first_name,-3,3) fromemployees;9) Find out each employee's annual salary (should be counted as bonus) note handling null values HR@ORA11GR2>SelectLast_name,first_name, (Salary+Salary*NVL (commission_pct,0))* AYearsal fromemployees;Ten) Displays the date of appointment of all employees by month and day. HR@ORA11GR2>SelectLast_name,first_name,to_char (Hire_date,'YYYY-MM-DD') 2 fromemployees; One) display all employees ' names with concat hr@ORA11GR2>SelectConcat (first_name||Chr +), last_name) fromemployees; A) Displays the third-to-last character of all employee names (last_name). HR@ORA11GR2>SelectFirst_name,substr (first_name,-3,1) ename fromemployees; -) HR user splicing SQL displays the following format: Note: The Index_name field can be queried from the user_indexesAlter Index"Index_name" Rebuild;hr@ORA11GR2>Select 'ALTER INDEX'||Index_name||'Rebuild' fromuser_indexes; theThe employee table shows the following information: Year-end awards are wages+Bonus Department number name year-end award HR@ORA11GR2>SelectDepartment_id,first_name,last_name, (Salary+Salary*NVL (commission_pct,0)) Commission fromemployees; -displays the entire company's maximum wage, minimum wage, sum of wages, average wage, retained to the whole number of digits. HR@ORA11GR2>Select Max(Nvl (Salary,0)) Maxsal,min(Nvl (Salary,0)) Minsal,sum(Nvl (Salary,0)) Sumsal,trunc (avg(Nvl (Salary,0)) Avgsal2 fromemployees; Maxsal minsal sumsal avgsal---------- ---------- ---------- ---------- 24000 2100 691416 6461 -the number of departments is more than the number 32nd, Scott.@ORA11GR2>SelectDeptnoCount(empno)2 fromEMP3 Group byDeptno4 having Count(empno)>(Select Count(empno) fromEmpwhereDeptno= Ten); -) Find out information about all employees who are higher than the 7654 wage. Scott@ORA11GR2>Select * fromEMP2 whereSal>(SelectSal fromEmpwhereEmpno= 7654); +) asked for a higher salary than 7654, and with 7788 of all employees engaged in the same job Scott@ORA11GR2>Select * fromEMP2 whereSal>(SelectSal fromEmpwhereEmpno= 7654) 3 andDeptnoinch(SelectDeptno fromEmpwhereEmpno= 7788); -which employees pay higher than the entire company's average salary, lists the employee's name and salary (descending) HR@ORA11GR2>SelectLast_name,first_name,salary fromEmployees2 whereSalary>(Select avg(Salary) fromemployees)3 Order bySalarydesc; +lists the department name and employee information for those departments, and lists the departments that have no employees. SCOTT@ORA11GR2> SelectA.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno2 fromDept A Left JoinEMP B onA.deptno=B.deptno; Aquery who is the leader of each employee (self-connected). SCOTT@ORA11GR2>SelectA.ename asClerk,b.ename asBoss fromEMP A,emp bwhereA.mgr=B.empno; atask for an employee's number, name, department number, department name, and department location Scott@ORA11GR2>SelectE.empno,e.ename,d.deptno,d.dname,d.loc fromEMP E,dept DwhereE.deptno=D.deptno;
"Exam" Simple SQL statement