Oracle Simple Query Instance

--query for positions that are not duplicatedSelect distinctJob fromEMP;--Search for annual salary, alias, alias not enclosed in single quotation marks.SelectSal* A  asNianxin fromEMP Sal;--the specific data is displayed in this form: The employee number is: The name is: The salary is: the position is:! Select 'the employee number is:'||Empno'the name is:'||ENAME,'Salary is:'||Sal'positions are:'||Job||'!'  fromEMP;--The query is not an employee information for a position that is not "clerk" (at least 2 ways of querying)Select *  fromEmpwhereJob!= 'Clerk';Select *  fromEmpwhereJob not  like '%clerk%';--Check the employee name with O and T in theSelect *  fromEmpwhereEname like '%o%'  andEname like '%t%';--arrange employee wages in order from highest to lowestSelectSal fromEmpOrder  bySaldesc;--The age of an employee who displays the average wage above the wages of each departmentSelectEmpno,a.deptno,a.sal,b.deptno,b.gsal,round((sysdate-HireDate)/ 365,0)--Age fromEMP A, (SelectDeptnoround(avg(SAL),0) asGsal fromEmpGroup  byDEPTNO) b--base table and view chartwhereA.deptno=B.deptno--primary key join base tables and views andA.sal>B.gsal;--salary is higher than average wage in each department--Check the salary range of each department, according to 1000~2000,2000~3000 .... This format shows the number of people--------------------------didn't read the meaning .--ask for full employee information that is higher than Smith's salarySelect *  fromEmpwhereSal>(SelectSal fromEmpwhereEname='SMITH');--ask for full employee information that is higher than the company's average salarySelect *  fromEmpwhereSal>(Select avg(SAL) fromEMP);--Find out the number, name, location, number of departments, average wage for each departmentSelectDeptnoavg(SAL) fromEmpGroup  byDeptno;--count the numbers in each department * *SelectDeptnoCount(*) fromEmpGroup  byDeptno;Select *  fromEMP;--1 Select all employees in department 30. SelectEname fromEmpwhereDeptno= ' -';--2 lists the name, employee number, and department number of all clerks (clerk). SelectEname,empno,deptno fromEmpwhereJob='Clerk';--3 Find employees with bonuses above their salaries. SelectEname fromEmpwhereComm>Sal;--4 Find employees with bonuses above 60% of their salary. SelectEname fromEmpwhereComm>(Sal*0.6);--5 Identify all managers (manager) in department 10Select *  fromEmpwhereDeptno= 'Ten'  andJob= 'MANAGER';--6 Find out the details of all the managers in department 10, and all the employees in all departments who are not managers but are not clerks but have a salary greater than or equal to 2000. Select *  fromEmpwhere(Deptno=Ten  andJob= 'MANAGER')or(Job not inch('MANAGE','Clerk') andSal>= -)Select *  fromEMP;--7 Identify the different jobs of employees with bonuses. Select distinctJob fromEmpwhereComm>0;--8 Find employees with no bonuses or bonuses below 100. Select *  fromEmpwhere(Comm< - orComm is  not NULL);--9 Find all employees employed on the 3rd day of the month. Select *  fromEmpwhereHireDate=(Last_day (HireDate)-2);--10 identify employees employed prior to 30. Select *  fromEmpwhereAdd_months (Sysdate,- -* A)>=HireDate;--add_months (xxxx,+or-) current time + or minus month--11 Displays the names of all employees in an uppercase letter. SelectInitcap (ename) fromEMP;--12 Displays the name of the employee who is exactly 5 characters. SelectEname fromEmpwhereLength (ename)=5;--13 Displays the names of employees without ' R '. SelectEname fromEmpwhereEname not  like '%r%';--14 Displays the first three characters of all employee names. SelectSUBSTR (ename,3) fromEMP;--15 Show all employees ' names, replace all ' A ' with ' L 'Select Replace(Ename,'L','A') fromEMP;--16 Displays the name and date of employment of the employee who has been in service for 30 years. SelectMonths_between (Sysdate,hiredate)/ A  fromEMP;SelectEname,hiredate fromEmpwhereMonths_between (Sysdate,hiredate)/ A >= - ;--17 Show employee details, sorted by name. Select *  fromEmpOrder  byEnamedesc;--18 Displays the employee's name and the date of employment, according to their service life, the oldest employees at the front. SelectEname,hiredate fromEmpOrder  byhiredate;--19 Displays the names, jobs, and salaries of all employees, sorted by work in descending order, and by salary if the work is the same. SelectEname,job,sal fromEmpOrder  byJobdesc, Sal;--20 Displays the names of all employees, the year and month in which the company was joined, sorted by the month of the hire date, and, if the month is the same, the employee of the earliest year is ranked first. SelectEname,to_char (HireDate,'yyyy') year, To_char (HireDate,'mm') Month fromEmpOrder  bymonth, year;----------------------------------21 shows the daily salary of all employees in one months for 30 days, ignoring the remainder. SelectTrunc (sal/ -) fromEMP;--22 Identify all employees employed in February (in any year). Select *  fromEmpwhereTo_char (HireDate,'mm')=2;--23 for each employee, show the number of days that they joined the company. SelectEname,sysdate-HireDate fromEMP;--24 Displays the names of all employees who have ' a ' at any point in the Name field. SelectEname fromEmpwhereEname like '%a%';

Oracle Simple Query Instance