Basic table Practice SQL statement under Scott users in Oracle

Source: Internet
Author: User
Tags sorted by name

--Select 30 of employees in the department
SELECT * from EMP where deptno=30;
--List all clerks ' names, departments, numbers
--Using the internal connection, that is, the equivalent link, is also the most commonly used links
SELECT ename,empno,dname from emp e INNER JOIN
Dept D on E.deptno=d.deptno where job= ' clerk ';
-Finding employees with higher commissions than salaries
SELECT * FROM EMP where comm>sal;
--Finding employees with commissions above 60% of salary
SELECT * FROM EMP where comm>sal*0.6;
--Find all the clerks in department 10 all managers and departments 20
SELECT * from EMP where (deptno=10 and Job=upper (' manager '))
or (Deptno=20 and job= ' clerk ');
--Find out all the clerks in department 10 all managers and departments 20, neither the manager nor the clerk,
--but the information of all employees whose salary >=2000
SELECT * from EMP where (deptno=10 and Job=upper (' manager '))
or (Deptno=20 and job= ' clerk ')
or (Job<>upper (' manager ') and Job<>upper (' clerk ') and sal>=2000);
--Find out the different jobs of employees who receive commissions
SELECT DISTINCT job from EMP where comm>0;
-Find employees who do not charge commissions or are less than 100 commission
SELECT * from emp where NVL (comm,0) <100;
--Find out all employees employed on the last day of each month
SELECT * from emp where hiredate=last_day (HireDate);
--Identify employees employed before 25
SELECT * FROM emp
where "Months_between" (Sysdate, HireDate)/12>25;
--Displays the names of all employees with only the first letter capitalized
--The "Initcap" (CH) function capitalizes the first letter of each word and the other letters into lowercase
Select ename from emp where ENAME=INITCAP (ename);
--Display the name of an employee who is exactly 6 characters
Select ename from emp where length (ename) = 6;
--show the names of employees without like
Select ename from emp where ename does like '%r% ';
--or write it like this.
Select Ename from emp where InStr (ename, ' R ') = 0;
--Displays the first three letters of all employees ' names
Select substr (ename,1,3) from EMP;
--show the names of all employees, replace a with a
Select Replace (ename, ' a ', ' a ') from EMP;
--Displays the names of all employees and the dates of service life of 10 years
Select Ename, Add_months (hiredate,12*10) as service life date from EMP;
--Show employee details, sorted by name
SELECT * from emp order by ename;
-Show the employee's name, according to their service life, the oldest employees in the front
Select ename from emp order BY hiredate Desc;
--Displays the names, jobs, and salaries of all employees, sorted in descending order of work, sorted in ascending order by salary
Select Ename,job,sal from emp order by job desc,sal ASC;
--Displays the names of all employees and the year and month of incorporation
-Rank items in the earliest year by the month in which the employee is employed
Select Ename, "To_char" (hiredate, ' yyyy ') as year, "To_char" (hiredate, ' mm ') as month
From EMP order by hiredate ASC;
--Shows the daily salary of all employees in the case of one months for 30 days
Select Sal/30 "Daily Salary" from EMP;
--Find out all employees employed in February in any year
Select Ename as employee name, hiredate as employment date
From EMP where TO_CHAR (hiredate, ' mm ') = ' 02 ';
--For each employee, show the number of days they joined the company
Select Ename,sysdate-hiredate from EMP;
--The name of the employee who appears with the letter A in the name
Select ename from emp where ename like '%a% ';
-OR
Select Ename from emp where InStr (ename, ' A ', 1) >0;
--View all tables under the current user
SELECT * from tab;

Basic table Practice SQL statement under Scott users in Oracle

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.