Question type III

Database operations have 2 Quasi question type :

The first is to give several groups of relational patterns without data , Perform database operations on it .eg: example 1

The second is to give some structure tables with data , Perform data operations on it .eg: example 2

The test sites are all chapter four , It's better to summarize all kinds of SQL sentence , Strengthen memory

Required form :

example 1,

Staff - The union database has three basic tables :

workers ( Employee No , name , Date of establishment , Gender );

Labour Union ( number , name , Person in charge employee No , Activity location );

participate in ( Employee No , number , Date of participation ).

The primary key of the employee table is the employee No , The primary key of the union table is number , The primary key of the participating table is the employee No. and No . on trial SQL Statement completes the following operations :

(1) Define employee table , Including employee No , name , Gender is character type , Establishment time is integer , Employee No. is the primary key ;

(2) Query responsible person employee No. yes “001” Number and name of the member ;

(3) Query the information of a female employee surnamed Zhang ;

(4) Increase the establishment time of Li Si in the employee table 1.

(5) Query established on 20 Years old to 30 Employee number of employees between years old , Name and date of establishment , And add the establishment time to 1 output ;

(6) The number of the trade union participating in the inquiry is “T1” Employee no. of .

solution :

example 2,

In a warehouse management system , There are two tables :KC Table to save the information of materials in the current warehouse and CKMX Table storage of stock materials           Material issue details ,

          Its structure is as follows :

KC( Material code , Material name , Company , Unit Price , Inventory quantity ) Material code is primary key ;

CKMX( Delivery order No , Material code , Delivery date , Outbound quantity ) Issue doc is primary key , Material code is foreign key .

* Try to write out the creation KC Table and CKMX Tabular SQL sentence , Entity definition and referential integrity required .
* use SQL The commands will be as follows 4-3 And table 4-4 Data of are inserted into KC Table and CKMX In the table .
* Inquire about the number of the computer , Delivery date , Material name , Unit Price , Issue information of issue quantity and amount .
* From table CKMX Count the delivery times and total delivery quantity of various materials in , Try to write SQL sentence .
surface 4-3,KC Table structure and data

Material code

Material name


Unit Price

Inventory quantity











surface 4-4,CKMX Table structure and data

      Delivery order No
Material code

Delivery date

Outbound quantity













solution :

supplement : There is a thing called wildcard :a%b Denoted by a start b Any length string at the end eg:akdhb

            use BETWEEN Indicates contains eg:20< time <50 finish writing sth. SQL Have to write time BETWEEN 20  AND 50

When the teacher draws the key points , Just say that the fourth chapter will SQL Language problems ( In previous years ), So there is no summary view here , Indexed SQL Statement notes , Just memorize the summary at the beginning of my article , Do a few more examples , The exam is all right