Shulou(Shulou.com)06/01 Report--

Oracle series "five": SQL comprehensive exercise

[1] list all kinds of jobs with a minimum wage greater than 1500 and the total number of employees engaged in this job

Select job,count (*) from emp group by job having min (sal) > 1500

[2] list the names of employees working in the department 'SALES'

Check the department number of SALES first.

SQL > SELECT deptno FROM dept WHERE dname='SALES'

SELECT ename FROM emp

WHERE deptno= (SELECT deptno FROM dept WHERE dname='SALES')

[3] list all employees whose salary is higher than the average salary of the company, their department, their superiors, and the salary grade of the company

Find out the average salary of the company

SQL > SELECT AVG (sal) FROM emp

List all employees whose salary is above average

SQL > SELECT * FROM emp

WHERE sal > (SELECT AVG (sal) FROM emp)

Query the information of your department

SQL > SELECT e.recording D.dnamered.loc FROM emp eRect d

WHERE sal > (SELECT AVG (sal) FROM emp) AND e.deptno=d.deptno

Inquire about the superior leader

SQL >

SQL > SELECT e.empnoree.enamerem.empnorem.enamered.deptnored.dnamered.loc

FROM emp e,dept d,emp m

WHERE e.sal > (SELECT AVG (sal) FROM emp) AND e.deptno=d.deptno AND e.mgr = m.empno (+)

Find out the salary scale of the salary

SQL > SELECT

E.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d,emp m,salgrade s

WHERE e.sal > (SELECT AVG (sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno (+)

AND e.sal BETWEEN s.losal AND s.hisal

[4] list all employees and department names who do the same job as "SCOTT"

Identify employees who work in the same way as SCOTT, but not yourself

SQL > SELECT empno,ename,job FROM emp

WHERE job= (SELECT job FROM emp WHERE ename='SCOTT') AND enameplates

Associate with department table, query department name

SQL > SELECT e.empnoree.enameree.jobred.dname FROM emp edept d

WHERE job= (SELECT job FROM emp WHERE ename='SCOTT') AND enameplates

AND e.deptno=d.deptno

[5] list the names and salaries of all employees whose salary is equal to that of the employees in department 30 (this topic is inconclusive and the salary is different from that of department 30)

List the salaries of 30 employees in the department

SQL > SELECT sal FROM emp WHERE deptno=30

The above conditions are used as subqueries. Note here that the above results return multiple records, so use IN.

SQL > SELECT ename,sal FROM emp

WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND deptnotated 30

[6] list the number of employees working in each department, average salary and average length of service

Number of employees per department: the name of the department can be found

SQL > SELECT d.dame.com count (e.empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname

Calculate the average salary and length of service

SQL > SELECT d.dame.com count (e.empno), AVG (e.sal), AVG (MONTH_BETWEENS (sysdate,hiredate) / 12) year

FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname

[7] list the details and number of departments of all departments

List the number of people in all departments

SQL > SELECT deptno,COUNT (empno) FROM emp GROUP BY deptno

Treat the above query as a temporary table

SQL > SELECT d.journal ed.cou

FROM dept d, (SELECT deptno,COUNT (empno) cou from emp GROUP BY deptno) ed

WHERE d.deptno = ed.deptno

However, if there is no information about 40 departments above, you should use NVL and left connection operation.

SQL > SELECT d. Magic NVL (ed.cou,0)

FROM dept d, (SELECT deptno,COUNT (empno) cou FROM emp GROUP BY deptno) ed

WHERE d.deptno = ed.deptno (+)

[8] list the minimum wage for various jobs and the names of employees engaged in the work

Group according to the work, use the MIN function to calculate the minimum wage

SQL > SELECT job,MIN (sal) FROM emp GROUP BY job

Query employee information according to salary

SQL > SELECT * FROM emp

WHERE sal IN (SELECT MIN (sal) FROM emp GROUP BY job)

[9] list the minimum salary of MANAGER in each department

SQL > SELECT deptno,MIN (sal) FROM emp

WHERE job='MANAGER' GROUP BY deptno

[10] list the annual salaries of all employees, sorted from lowest to highest

SQL > SELECT ename, (sal+NVL (comm,0)) * 12 income

FROM emp ORDERY BY income

[11] find out the employees of the department with the character'S' in the name of the department, the total salary, and the number of the department

Use a fuzzy query to get the department number

SQL > SELECT deptno FROM dept WHERE dname LIKE'% S%'

Above as a subquery

SQL > SELECT deptno,SUM (sal), COUNT (empno) FROM emp

WHERE deptno IN (SELECT deptno FROM dept WHERE dname LIKE'% S%') GROUP BY deptno

[12] pay a 10% increase for people who have served for more than 10 years

SQL > UPDATE emp SET sal=sal+ (sal*0.1)

WHERE MONTH_BETWEENS (sysdate,hiredate) / 12 > 10

[comprehensive question] there is a database of student sports competition information, and the following table needs to be established. The structure of the athlete sporter is as follows: (athlete number sporterid, athlete name name, athlete gender sex, department number department)

Project item: (project number itemid, project name itemname, project competition venue location)

Score grade: (athlete No. Sporterid, item No. Itemid, points mark)

1. Table-building requirements

Define primary foreign key constraints for each table

The name of the athlete and the department to which he belongs cannot be empty

The points are either controlled or for 6pm 4pm 2pm 0

CREATE TABLE sporter (

Sporterid NUMBER (4) PRIMARY KEY

Name VARCHAR2 (20) NOT NULL

Sex VARCHAR2 (2) NOT NULL

Department VARCHAR2 (20) NOT NULL

CONSTRAINT sporter_sex_CK CHECK (sex IN ('Maureen F'))

);

CREATE TABLE item (

Itemid VARCHAR2 (4) PRIMARY KEY

Itemname VARCHAR2 (20) NOT NULL

Location VARCHAR2 (20) NOT NULL)

CREATE TABLE grade (

Sporterid NUMBER (4)

Itemid VARCHAR2 (20)

Mark NUMBER (2)

CONSTRAINT sporter_grade_fk FOREIGN KEY (sporterid) REFERENCES sporter (sporterid) ON DELETE CASCADE

CONSTRAINT item_grade_fk FOREIGN KEY (itemid) REFERENCES item (itemid) ON DELETE CASCADE

CONSTRAINT grade_mark_CK CHECK (mark IN (6, 4, 2, 0))

);

The record can be inserted according to the situation to complete the above query statement

1. Find out the name of the system with the highest total score at present, and its integral

SQL > SELECT s.departmentsimSUM (g.mark) sum FROM sporter sparting grade g

WHERE s.sporterid=g.sporterid GROUP BY s.department ORDER BY sum DESC

Of course, the above results are sorted multiple records, and it is the easiest to use ROWNUM.

SQL > SELECT * FROM (

SELECT s.departmentmain sum (g.mark) sum FROM sporter sparting grade g

Where s.sporterid = g.sporterid GROUP BY s.department ORDER BY sum DESC)

WHERE ROWNUM=1

2. Find out the name of each event and the name of the champion when the venue is'S1'.

First of all, determine all the events in a playground and the highest score of each event.

SQL > SELECT i.itemname.namememeg.mark FROM item iRegent grade grecoversporter s

WHERE i.locationmakers S1' AND i.itemid = g.itemid AND s.sporterid = g.sporterid

Get the highest score according to the above results

SQL > SELECT i.itemres.namerecoveryg.mark FROM item iGrad ggrader sporter s

WHERE i.locationmakers S1' AND i.itemid = g.itemid AND s.sporterid AND g.mark=6

3. Find out the names of other students who have participated in the programs that wilson has participated in

Find the project number that wilson participated in

SQL > SELECT g.itemid FROM sporter sparry grade g

WHERE s.sporterid=g.sporterid AND s. Nameplate island'

SELECT DISTINCT s.name FROM sporter s, grade g

WHERE s.sporterid=g.sporterid AND S. nameplate Wilson'

AND g.itemid IN

(SELECT g.itemid FROM sporter Scripture grade g WHERE s.sporterid=g.sporterid AND s.nameplate engineer Wilson')

4. Wilson used illegal drugs, and the score was 0.

SQL > UPDATE grade SET makr=0

WHERE sporterid= (SELECT sporterid FROM sporter WHERE name='wilson')

5. Delete the S2 project

SQL > DELETE FROM item WHERE itemid='S2'

At this point, the basic grammar training of ORACLE is over, and we will learn the architecture of ORACLE later.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.