SQL jobs: A review of comprehensive exercises (ii)

One: Job Title: Comprehensive Exercises (ii) II: Topic Requirements:

1, the creation of the database Cpxs, saved in the E-packing directory to their own school number + first letter (Arabic numerals + capital letters) to create a folder, the initial size of 5MB, the largest 20MB, in 10% ways to grow, log files in the same folder, the initial size of 2MB, the maximum 5MB, grow in 1MB mode;
2, CREATE table CP,CPBH primary key, 8 digits, CPMC, 12 characters in length, JG for accurate to 2 digits after the decimal point, KCl is an integer, except KCl, the other can not be empty;
3, using insert input data, the specific data are as follows:
' 10002120 ', ' refrigerator a_200 ', 1850.00, 200
' 20011001 ', ' air conditioning k_1200 ', 2680.00, 300
' 20012000 ', ' air conditioning k_2100 ', 3200.00, 1000
' 30003001 ', ' freezer l_150 ', 5000.00, 100
' 10001200 ', ' refrigerator b_200 ', 1600.00, 1200
' 10001102 ', ' refrigerator c_210 ', 1890.00, 600
' 30004100 ', ' freezer l_210 ', 4800.00, 200
' 20001002 ', ' air conditioning k_3001 ', 3800.00, 280
' 20001600 ', ' air conditioning k_1600 ', 4200.00, 1500
3. Display CP All records
4. Display "Product name" and "Product value", of which Total product =JG*KCL
5. Display "Product name" and "Product price", which product price =jg*0.8

Three: The main points of the operation:

1, save in the e-packing directory to their own school number + surname the first letter created folder, some students will save the database files in the ' Data ' folder, should pay attention to the topic requirements

2, after creating the database, use CPXS create the table, the error ' CPXS ' database does not exist, it is recommended to create a database, and then open a page to create a table

3, the data type of the field in the table, with float (2) or decimal (6,2) can be, although the title of the data is accurate to two digits after the decimal point and the length of 6, but in general conditions with float, decimal can be

4, when using insert input data, an insert can only correspond to a values, if there is only one insert multiple values will appear system error, can also be an insert, a values but the values behind the parentheses can have many, There is also a use of INSERT, VALUES, SELECT, specific links I will be sent to the comment area

5. Display all records of CP table not only with Select*from CP can also be used with select (All) CPBH,CPMC,JG,KLC from CP

6, the fourth to fifth application point is the use of other names instead of the original field of the table, for this use I provide three ways

①select JG as ' price '

②SELECR Price =JG

③select JG Price

Four out of code example

1 CREATE DATABASECpxs on  2(NAME='Cpxs_data',  3FILENAME='E:\1715901****w\cpxs.mdf',  4SIZE=5MB,5MAXSIZE=20MB,6FileGrowth=Ten%)  7 LOG  on  8(NAME='Cpxs_log',  9FILENAME='E:\1715901****w\cpxs.ldf',  TenSIZE=2MB, OneMAXSIZE=5MB, AFileGrowth=1MB) - GO  

Create a database

1  UseCpxs2    3 IF EXISTS(SELECTtable_name frominformation_schema. TABLES4 WHEREtable_name= 'CP')       5 DROP TABLECP6  GO  7   8 CREATE TABLECP9(CPBHCHAR(8) not NULL PRIMARY KEY,  TenCpmcCHAR( A) not NULL,   OneJgFLOAT  not NULL,   AKCLINT NULL   - )   - Insert  intoCP the        Values('10001100','Refrigerator a_100',1500.00, -)   -    - Insert  intoCP -        Values('10002120','Refrigerator a_200',1850.00, $)   +    - Insert  intoCP +        Values('20011001','Air Conditioning k_1200',2680.00, -)   A    at Insert  intoCP -        Values('20012000','Air Conditioning k_2100',3200.00, +)   -    - Insert  intoCP -        Values('30003001','Freezer l_150',5000.00, -)   -    in Insert  intoCP -        Values('10001200','Refrigerator b_200',1600.00, -)   to    + Insert  intoCP -        Values('10001102','Refrigerator c_210',1890.00, -)   the    * Insert  intoCP $        Values('30004100','Freezer l_210',4800.00, $)  Panax Notoginseng    - Insert  intoCP the        Values('20001002','Air Conditioning k_3001',3800.00,280)   +    A Insert  intoCP the        Values('20001600','Air Conditioning k_1600',4200.00, the)   + GO   - SELECT *  fromCp

Create a table, insert data, display all records in a table

1  Use Cpxs   2 SELECT  as ' Product Name ', product value =JG*KCL  3 from

Find a

1  Use Cpxs   2 SELECT  as ' Product Name ' = JG*0.8  3 from

find twoV. Student assignments

Vi. (laughter) The personal summary of the fish blogger

1, this is the second time evaluation of the fish Bo Master homework, more than the first time personal feeling has a lot of progress (?? _?), the first time to judge the work encountered many difficulties, such as: SQL SEVER2008 connection is not on the local server, the student submitted folder can not open, even if the folder repair is useless , debugging code, and so on, and so on, but also gradually increased their experience, but also feel the responsibility of the work and a feeling of enjoyment

2, well, overall their efficiency ah, the completion of Ah, what are not as good as the rest of the team, they still have a lot of people do not understand the need to ask others places

3, I hope this homework evaluation can let you crossing master satisfaction, when they can have high-yielding that day ...

4. Attach SQL SEVER INSERT statement usage SQL SEVER series: INSERT statement

SQL jobs: A review of comprehensive exercises (ii)