Oracle database, simple SQL exercises and Answers

1. Data

--Create an employee table
CREATE TABLE Tbemp
(
Eidnumberprimary Key,--Employee number
ENAMEVARCHAR2 () NOT NULL,--employee name
ESEXVARCHAR2 (2) NOT null--staff sex
Check (esex in (' Male ', ' female ')),
Eagenumbernot null check (EAGE>=18),--staff age
EADDRVARCHAR2 () Not NULL,--employee address
ETELVARCHAR2 (+) NOT NULL,--staff phone
EEMAILVARCHAR2 (+) NULL,--staff Mailbox
Ejointimedatenot null--Time of entry
);
INSERT INTO Tbemp
(Eid,ename,esex,eage,eaddr,etel,eemail,ejointime)
Values
(1, ' Zhao ', ' Male ', 25, ' Hunan Province, Shashi Wu Jia Ling Jiangnan Garden 9 building No. 203 ', ' 0731-4230123 ', ' [email protected] ', ' May-October-2005 ');
INSERT INTO Tbemp
(Eid,ename,esex,eage,eaddr,etel,eemail,ejointime)
Values
(2, ' Levin ', ' female ', 23, ' Changsha Dongfeng Road, Hunan Province, No. 21, East Dongfeng Village No. 502 ', ' 0731-4145268 ', ' [email protected] ', ' March-July-2003 ');
INSERT INTO Tbemp
(Eid,ename,esex,eage,eaddr,etel,eemail,ejointime)
Values
(3, ' Sun Yicheng ', ' Male ', 24, ' Hunan Province Zhuzhou 601 Factory dormitory 15 building No. 308 ', ' 0732-8342567 ', ' [email protected] ', ' February-November-2002 ');
INSERT INTO Tbemp
(Eid,ename,esex,eage,eaddr,etel,eemail,ejointime)
Values
(4, ' Pauline ', ' Men ', 27, ' 20 Building No. 301, Chen People's Hospital, Hunan province ', ' 0735-2245214 ', ' [email protected] ', ' May-January-2006 ');
INSERT INTO Tbemp
(Eid,ename,esex,eage,eaddr,etel,eemail,ejointime)
Values
(5, ' Wei Qing ', ' female ', 23, ' Hunan Province Changsha Moon Lake 12 Building No. 403 ', ' 0731-8325124 ', ' [email protected] ', ' May-March-2007 ');

Commit

2. Exercises

--1. Query all staff for all information
SELECT * from Tbemp;

--2. Check the name, phone number and address of all staff
Select Ename,etel,eaddr from Tbemp;

--3. Find out more about all female staff
SELECT * from tbemp where esex = ' female '

--4. Check the name of the employee between 24-26 years of age, gender
Select Ename,esex from Tbemp where eage between and 26

--5. Check the name, telephone number and address of the female employee who lives in Changsha.
Select Ename,etel,eaddr from tbemp where esex = ' female ' and eaddr like '% Changsha '

--6. Enquiry Levin, Sun Yicheng, Pauline's phone, address
Select Ename,etel,eaddr from Tbemp where ename in (' Levin ', ' Sun Yicheng ', ' Pauline ');

--7. Check the names, gender, age of Chen and Zhuzhou employees
Select Ename,esex,eage from tbemp where eaddr like '% Chen Continent ' or eaddr like '% Zhuzhou ';

--8. Check the name of the male employee who lives in Changsha, aged between 25-28 years old
Select ename from tbemp where eaddr like '% Changsha ' and (Eage between) and Esex = ' male ';

--9. Query an employee with an empty e-mail address
SELECT * from tbemp where eemail is null;

--10. Query for employees who have been in employment for more than two years----here is the operation of the date, the current time-entry time---------------------------
SELECT * from Tbemp where Months_between (sysdate,ejointime)/12 > 2

--11. Identification of employees who have been in employment in January
SELECT * from Tbemp where Extract (month from ejointime) = 1;

--12. Change all email 163.net mailboxes to 126.com----Modify the results of the query, the string substitution function-------------------
SELECT * from tbemp where eemail like '%163.net% '

--13. Find out the youngest of two Changsha employees------------page Query--------------------
--1. Three layers of nesting, on the two layer is the pagination processing, the innermost layer is to solve the rownum page and order by together resulting in a sort of error
Select a1.* from
(select A2.*,rownum as RN from
(SELECT * from Tbemp ORDER by Eage) A2
where RowNum <= 2) A1
where RN >= 1;

--14. Find employee information that requires the result set to be expressed as
--Field Name: Personal information
--Content Description: ' Employee 1, Zhao, 25 years old, lives in Hunan province, Shashi Wu Jia Ling Jiangnan Garden 9 building No. 203 '
Select ' Employees ' | | eid| | ', ' | | ename| | ', this year ' | | eage| | ' years old, lives ' | | Eaddr as staff introduction from Tbemp;

--15. Total age of all employees
Select SUM (eage) as age sum from tbemp;

--16. Male, female employee age sum
Select Esex as Gender, sum (eage) as age sum from Tbemp Group by Esex;

3. Summary

1. Basic SQL format

2. Operation of the date

A. Date operation:
Back Time-Front time
Date subtraction The default result is the difference in days

Months_between (Back Time-front time)
Months_between default return result is the number of months difference

B. Value of date taken
Extract function
Extract (year from Sysdate)

3. Paging Query

Rownum:oracle-specific keywords for paging

A. Nested implementation paging Query

Paged queries are nested with two queries, internal queries are used to specify upper bounds, and result sets return rownum, and external queries specify the lower bound

B. Issues with sorting and paging joint use

RowNum paging with order by uses pagination sorting, which results in sorting errors, Workaround: Multiple nested one layer Select

Select a1.* from
(select A2.*,rownum as RN from
(SELECT * from Tbemp ORDER by Eage) A2
where RowNum <= 2) A1
where RN >= 1;

Oracle database, simple SQL exercises and answers