ORACLE Experiment II

Experiment two: Data manipulation

Experimental Hours:4 hours

Experiment Type: Comprehensive type

Experimental requirements: Compulsory

First, the purpose of the experiment

1, master SQL data query statement;

2, master the use of SQL aggregation function.

3, master the use of SQL Insert, change, DELETE statement.

Ii. contents of the experiment

1. Project projects Procurement database, on the basis of experimental 3.2 Insert example data and completed the following operations:

(1) The number of the vendor that provided the part to projectJ1 .

(2) The number of the vendor that provided the part P1 to ProjectJ1 .

(3) The number of the vendor that supplied the red part to ProjectJ1 .

(4) project number for a red part supplied by a Tianjin supplier is not used.

(5) use at least the project number of all the parts provided by the vendor S1 .

(6) count the total number of parts supplied by each supplier.

(7) a project number that uses at least 3 or more parts.

(8) change the color of all red parts to pink.

(9) The vendor S1 that will provide part P1 to ProjectJ1 is changed to S2 provided.

(Ten) Delete all red parts and the corresponding supply - usage records.

2. Product Price database. Insert the example data on the basis of the experimental 3.3 and complete the following operations

(1) list the models of those PCs that are at least the same speed.

(2) which manufacturers produce the LAPTOP configuration of the hard drive not less than 1.

(3) find out the model and price of all products produced by Plant B .

(4) Find out all the color laser printer models.

(5) find the manufacturer of a PC with a production execution speed of at least 450MC .

(6) find the printer with the highest price.

(7) find the highest price for each manufacturer's PC .

(8) find a manufacturer that produces at least three different models of PC machines.

(9) Vendor A merger vendor B. The products produced by all B manufacturers are changed to production by manufacturer A .

(Ten) Remove all LAPTOP machines produced by manufacturers that do not produce printers.

Iii. Principles, methods and means of experiment

On the basis of the relationship pattern defined in experiment 3 . Use SQL data to manipulate statements to complete the query and update operation of the data. Be aware of the query's optimization strategy when querying and updating.

Iv. implementation requirements of the experimental organization

This experiment is a comprehensive experiment. In the form of centralized teaching, students focus on machine operation. Detailed requirements such as the following:

1, students must be fully prepared before the experiment, familiar with the contents of the experiment;

2, the teacher in the student experiment process to give the necessary counseling. The students completed their experiments independently.

V. Conditions of the experiment

1. Provide a computer with a WINDOWS 98/2000/nt/xp operating system.

2,Oracle8.0 above design environment.

Vi. Steps of the experiment

Similar experiment three.

Seven, study questions

The difference between experiment comparison and Exists two seed query.

Eight, the experimental report

1. Experiment Preview

Before the experiment, each student needs to carefully prepare the experiment, and write a preview report. In the preview report to write the purpose of the experiment, requirements, a brief experimental steps to form an operational outline.

2. Experimental records

When students start experimenting. The records should be kept close by, with each step of the experiment done, the results obtained and the related conditions recorded faithfully.

3. Lab Report

The main contents include the query statement in the experiment, the data UPDATE statement, the result of the execution and the analysis summary of the error during the operation. Answer study questions, give the experimental conclusion.

Ix. Other Notes

Students strictly abide by the rules and regulations of the laboratory, cooperate with and obey the management of laboratory personnel.

After the experiment, we also got the results of the experiment.

Note that the two SQL lab scripts are based on the two databases in the experiment one.

Code such as the following, in the validation experiment without errors basic is double not EXISTS specific answers see my blog and a chapter

--2.1---------------------------------------------------------------------------------------Select SNO from Spjwhere jno= ' J1 '; select SNO from Spjwhere jno= ' J1 ' and pno= ' P1 '; Select SPJ. SNO from Spj,pwhere SPJ. jno= ' J1 ' and SPJ. Pno=p.pno and color= ' red '; SELECT DISTINCT SPJ. Jno from Spj,s,pwhere SPJ. Sno=s.sno and SPJ.      Pno=p.pno and s.city!= ' Tianjin ' and p.color!= ' red ';      Select DISTINCT jnofrom jwhere not EXISTS (SELECT * from SPJ SPJ1 WHERE spj1.sno= ' S1 ' and not EXISTS      (SELECT * from SPJ SPJ2 WHERE spj2.pno= spj1.pno and spj1.jno= j.jno)); SELECT SPJ. Sno,count (*) from Spjgroup by SPJ. SNO; SELECT SPJ. Jno from Spjgroup by SPJ. Jnohaving COUNT (*) >=3; UPDATE PSET color= ' Pink ' WHERE color= ' red '; UPDATE spjset sno= ' S2 ' WHERE sno= ' S1 ' and jno= ' J1 ' and pno= ' P1 '; DELETE spjwhere SPJ.            PNO in (SELECT p.pno from P WHERE p.color= ' red '); DELETE pwhere p.color= ' red ';--2.2---------------------------------------------------------------------------------SELECT modelfrom pcwhere speed >= 150; SELECT DISTINCT PRODUCT. MAKER from Product,laptopwhere PRODUCT. Model=laptop. MODEL and LAPTOP. HD >= 1.0; SELECT maker,product. Model,pricefrom Product,pcwhere PRODUCT. Maker= ' B ' and product.model=pc.modelunion (SELECT maker,product. Model,pricefrom Product,laptopwhere PRODUCT. Maker= ' B ' and Product.model=laptop.model) UNION (SELECT maker,product. Model,pricefrom Product,printerwhere PRODUCT. Maker= ' B ' and Product.model=printer.model); SELECT MODEL from Printerwhere color= ' TRUE '; SELECT DISTINCT PRODUCT. Makerfrom product,pcwhere type= ' PC ' and PRODUCT. Model=pc. MODEL and speed>450; SELECT * from PRINTER WHERE price >= All (select Price from PRINTER); Select Model,price from Printerwhere price= (select MAX, from PRINTER); SELECT DISTINCT Maker,max (price) from product, Pcwhere product. Model=pc. MODEL and type= ' PC ' GROUP by MAKER; SELECT DISTINCT Maker,count (*) from Productwhere type= ' PC ' GROUPby Makerhaving COUNT (*) >=3; UPDATE productset maker= ' B ' WHERE maker= ' A ';D elete PRODUCT p1where P1. Type= ' LAPTOP ' and EXISTS (SELECT * from PRODUCT P2 WHERE P2. Type= ' Printer ' and P1. Model=p2. MODEL); ROLLBACK;

If there is any improper, please do not hesitate to enlighten ...

I labor results, please reprint the source of the attribution ...

ORACLE Experiment II