ORACLE lab 2

ORACLE lab 2

Experiment 2: Data manipulation

Lab hours: 4 hours

Lab type: Comprehensive

Lab requirements: Required

I. Tutorial Purpose

1. Master SQL data query statements;

2. Master the use of SQL Aggregate functions;

3. Master the use of SQL insert, modify, and delete statements.

Ii. experiment content

1. Import sample data to the project procurement database based on experiment 3.2 and complete the following operations:

(1) Calculate the supplier number that provides parts to project J1.

(2) Calculate the supplier number that provides parts P1 to project J1.

(3) Calculate the supplier number that provides red parts to project J1.

(4) do not use the engineering Number of the red parts provided by the Tianjin supplier.

(5) use at least the engineering Number of all parts provided by supplier S1.

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

(7) Calculate the engineering Number of at least three types of parts.

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

(9) Change S1, the supplier that provides parts P1 to project J1.

(10) delete all red parts and corresponding supply-use records.

2. Product Price Database: insert sample data on the basis of experiment 3.3 and complete the following operations

(1) list models of PCs with a speed of at least 150.

(2) which manufacturers produce no less than 1 LAPTOP hard disk configuration.

(3) Identify the models and prices of all products produced by manufacturer B.

(4) Find out the models of all color laser printers.

(5) Identify the PC manufacturer whose production speed is at least running MC.

(6) Find the most expensive printer.

(7) Find out the maximum price of PCs for each manufacturer.

(8) Identify the manufacturers that produce at least three different types of PCs.

(9) Vendor A merges Vendor B and changes the products produced by Vendor B to those produced by Vendor.

(10) delete all LAPTOP machines produced by manufacturers that do not produce printers.

Iii. Experiment principles, methods and means

Based on the relational mode defined in experiment 3, SQL data operation statements are used to query and update data. When querying and updating, pay attention to the query optimization policy.

Iv. Lab organization and operation requirements

This experiment is a comprehensive experiment, which adopts the centralized teaching mode and enables students to perform operations on the computer in a centralized manner. The specific requirements are as follows:

1. Students must fully preview the experiment and be familiar with the experiment content before conducting the experiment;

2. teachers provide necessary counseling during the student lab, and students complete the lab independently.

5. Lab Conditions

1. Provides a computer with a WINDOWS 98/2000/NT/XP operating system;

2. Oracle8.0 or above.

Vi. Experiment steps

Similar to experiment 3.

7. Questions

The experiment compares the differences between IN and Exists subqueries.

8. lab report

1. Experiment Preview

Before the experiment, everyone needs to carefully preview the experiment and write a preview report. In the preview Report, the purpose, requirements, and steps of the experiment should be written, form an operational outline.

2. lab records

When starting the experiment, students should keep the recording book close to each other and record the operations, results, and related conditions in the experiment.

3. lab report

The main content includes the query statement, data update statement, running result, Analysis and Summary of errors during the operation, answer questions, and give the experiment conclusion.

9. Other Instructions

Students strictly abide by the rules and regulations of the lab, and cooperate with and manage the personnel of the lab.

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

Note that the two SQL experiment scripts are based on the two databases in Experiment 1.

The Code is as follows. If there is no error in the verification experiment, the main problem is that the dual not exists has a detailed answer. See another chapter in my blog.

-- 2.1 explain 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 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 (PRICE) 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' group by makerhaving count (*)> = 3; update productset maker = 'B' WHERE MAKER = 'a '; delete product P1WHERE P1.TYPE = 'laptop' and exists (SELECT * from product P2 WHERE P2.TYPE = 'printer' AND P1.MODEL = P2.MODEL); ROLLBACK;

If you have any mistakes, please kindly advise...

For my labor achievements, please reprint the source of your signature...