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
Company
Unit Price
Inventory quantity
01
computer
platform
5000.00
10
02
Television
platform
2000.00
20
surface 4-4,CKMX Table structure and data
Delivery order No
Material code
Delivery date
Outbound quantity
1
01
2020-10-10
4
2
01
2020-11-20
3
3
02
2020-11-20
5
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
Technology