Oracle Common SQL queries

Build table Information

1. Table Structure EMP----Employee Information Sheetename VARCHAR2 (30),--NameEmpno Number (5),--NumberDeptno Number (5),--Your departmentJob varchar2 (20),- Types of jobs (categories of personnel), such as manager managers, Clerk clerks hiredate Date--Employment datesHireDate Date,--Date of employmentComm number (6,2),--CommissionSal Number (6,2)--SalaryDept-----Department TableDname VARCHAR2 (30),--Department nameDeptno Number (5),--Department numberLoc VARCHAR2 (50)--Location2. Prepare the data:Create Tableemp– Create an Employee Information table (ename VARCHAR2 ( -),---name Empno Number(5),--ref. DEPTNO Number(5),--Job VARCHAR2 in your department ( -),--Types of jobs (category of personnel), such as: Manager, Clerk clerk HireDateDate--Employment Date HireDateDate--Employment Date Comm Number(6,2),--Commission Sal Number(6,2)--Salary)Insert  intoEMP (ename,empno,deptno,job,comm,sal)Values(' Liu Tao ',10001,Ten,' clerk ', -, -)Insert  intoEMP (ename,empno,deptno,job,comm,sal)Values(' Wu Hao ',10002,Ten,' clerk ',650,2200)Insert  intoEMP (ename,empno,deptno,job,comm,sal)Values(' Thandantan ',10002, -,' clerk ',650,2200)Insert  intoEMP (ename,empno,deptno,job,comm,sal)Values(' Li Yangyang ',20001, -,' manager ',980,3200)Create TableDept--Department table (Dname VARCHAR2 ( -),--Department name Deptno Number(5),--Department number Loc VARCHAR2 ( -)--location)Insert  intoDept (Dname,deptno,loc)Values(' marketing Department ',Ten,' Dalian, Liaoning ')Insert  intoDept (Dname,deptno,loc)Values(' PR ', -,' Shenyang, Liaoning ')Select* fromDept

First, simple query operation

1, select the employee in Department 30Select* fromEMP Ewheree.deptno=' A ' 　 2, list all clerks ' names, numbers, and departmentsSelectE.ename, E.empno, D.dname fromEMP E, Dept DwhereE.deptno=d.deptno3To identify employees with higher commissions than salariesSelect* fromEMP EwhereE.comm>e.sal4and find out that commissions are higher than salaries -% of employeesSelect* fromEMP EwhereE.comm>e.sal *0.65, identify departmentsTenAll managers and departments in -Detailed information on all clerks in theSelect* fromEMP Ewhere(e.job='?-àí '  ande.deptno=' Ten ')or(e.deptno=' A '  ande.job=' °ìê?? ± ')6, identify departmentsTenAll managers and departments in -All clerks, neither managers nor clerks but whose salary >= -Detailed information for all employeesSelect* fromEMP Ewhere(e.job='?-àí '  ande.deptno=' Ten ')or(e.deptno=' A '  ande.job=' °ìê?? ± ')or(e.job!='?-àí '  ande.job!=' °ìê?? ± '  ande.sal>= -)7To find out the different jobs of employees who receive commissionsSelect distinctJob fromEmpwhereNVL (Comm,0) >0 　 8, find out that no commissions or commissions are charged below -of employeesSelect* fromEmpwhereNVL (Comm,0) > - 　 9and find out all the employees who were hired on the last day of each monthSelect* fromEmpwhereHiredate=last_day (HireDate)Ten, showing the names of all employees with only the first letter capitalizedSelectEname fromEmpwhereInitcap (ename) =ename One, showing exactly the6Character name of an employeeSelectEname fromEmpwhereLength (ename) =6 　  A, display does not contain' R 'The name of the employeeSelectEname fromEmpwhereEname not  like '%r% ' 　  -, display the first three characters of all employees ' namesSelectSUBSTR (ename,1,3) fromEmp -, display the names of all employees, replace all with a' A 'Select Replace(Ename,' A ',' A ') fromEmp the, showing the names of all employees and the fullTenDate of year of serviceSelectename, Add_months (HireDate, -) fromEmp -, show employee details, sort by nameSelect* fromEMP E, Dept DwhereE.deptno=d.deptnoOrder  byE.ename -, displays the employee's name, and according to its service life, rank the oldest employee in frontSelectENAME, HireDate fromEmpOrder  byHireDate -, display the names, jobs, and salaries of all employees, sorted in descending order of work, and work in the same time by salary ascendingSelectENAME, HireDate fromEmpOrder  byHireDate +, display the names of all employees and the year and month in which the company was joined, sorted by the month in which the employee was employed, and the first year of the projectSelectename, To_char (HireDate,' yyyy '), To_char (HireDate,' mm ') fromEmpOrder  byHireDate -, shown in one months for -Day salary of all employees in the case ofSelectename, round (sal/ -,2) fromEmp +, find out in (any year's)2All employees employed in the monthSelect* fromEmpwhereTo_char (HireDate,' mm ')=' a ' 　  A, for each employee, shows the number of days they joined the companySelectename, Trunc (sysdate-hiredate) fromEmp at, displays any location of the last Name field, including"A"The names of all employeesSelect* fromEmpwhereEname like '%a% ' 　  -, showing the service life of all employees in years, months and daysSelectename, Trunc (Months_between (sysdate, HireDate)/ A), Trunc (Months_between (Sysdate, HireDate)), Trunc (sysdate-hiredate) fromEmp

1Student Table Students#学号, sname name, difdate date of birth, grade grade2Timetable Coursec#课程号, name cname3Transcript Scores#学号 C # course number score scoreCreateTable Student (s#学号 varchar2 (+),Sname name VARCHAR2 ( -), Difdate date of birthDate, Grade grade VARCHAR2 ( -))CreateTable Course (c#课程号 varchar2 (+),CNAME name VARCHAR2 ( -))CreateTable Score (s#学号 varchar2 (+),C#课程号 varchar2 (+),Score Score VARCHAR2 ( -))

Second, the conditions of inquiry

1. Check the student's list with the surname Li and the students who are level 2007Select* fromStudentwhereSname Name like ' li% '  andGrade grade = ' -’2.Check the results of the physics class of the student surnamed WangSelectS.sname name, c.cname name, T.score score fromStudent S,course C,score Twheres.s# School Number =t.s# No. andC.c# Course Number =t.c# Course No. andS.sname Name like ' Wang% '  andt.c# Course Number =' 4 '3.Count how many people are in each gradeSelectGrade grade,Count(Grade Grade) fromStudentGroup  byGrade Grade4.2007The average grade of each student has no merit in0；Selectt.s# number, s.sname name,avg(T.score results) fromStudent S,score Twheres.s# School Number =t.s# No. andS.grade grade =' " Group  by(s.sname name, t.s# School number)5.  -The average grade and the highest achievement of the students in each section are retained2Bit decimal roundingSelectC.cname Name, t.c# course number, round (avg(T.score results),2),Max(T.score score ¨),min(T.score results) fromCourse C,score TwhereC.c# Course Number =t.c# Course No. and Group  by(t.c# course number, c.cname name6.To give -Grade students ' math scores plus5Score ofSelectS.sname name, c.cname name, T.score score +5  fromStudent S,course C,score Twheres.s# School Number =t.s# No. andC.c# Course Number =t.c# Course No. andS.grade grade =' "  andt.c# Course Number =' 2 '7.Number of people born in the same monthSelect Count(To_char (difdate Birth date,' mm ')) fromStudentGroup  byTo_char (difdate date of birth,' mm ')8.Delete Name is Zhang San's University language achievementDelete  fromScorewheres# number =' 2008020177 '  andC # Course Number =' 3 '9.To replace mathematics with higher mathematicsUpdateCourseSetCNAME name =' Advanced mathematics ' whereC # Course Number =' 2 '.Format, display will change the number to the beginning of the s not enough ABit left complement0; elect' S '|| Lpad (s# School Number, One,' 0 ') fromStudent S

Oracle Common SQL queries