首页 > 代码库 > 2016.9.27小程序---数据库练习1

2016.9.27小程序---数据库练习1

  1 -- createbbc, 1, 50  2 DROP TABLE bbc;   3   4   5 CREATE INDEX bbc_region ON bbc(region);  6   7 GO  8 -- tabbbc, 1, 50  9 INSERT INTO bbc VALUES (Afghanistan,South Asia,652225,26000000,NULL); 10 INSERT INTO bbc VALUES (Albania,Europe,28728,3200000,6656000000); 11 INSERT INTO bbc VALUES (Algeria,Middle East,2400000,32900000,75012000000); 12 INSERT INTO bbc VALUES (Andorra,Europe,468,64000,NULL); 13 INSERT INTO bbc VALUES (Angola,Africa,1250000,14500000,14935000000); 14 INSERT INTO bbc VALUES (Antigua and Barbuda,Americas,442,77000,770000000); 15 INSERT INTO bbc VALUES (Argentina,South America,2800000,39300000,146196000000); 16 INSERT INTO bbc VALUES (Armenia,Europe,29743,3000000,3360000000); 17 INSERT INTO bbc VALUES (Australia,Asia-Pacific,7700000,20300000,546070000000); 18 INSERT INTO bbc VALUES (Austria,Europe,83871,8100000,261630000000); 19 INSERT INTO bbc VALUES (Azerbaijan,Europe,86600,8500000,NULL); 20 INSERT INTO bbc VALUES (Bahamas,Americas,13939,321000,4789320000); 21 INSERT INTO bbc VALUES (Bahrain,Middle East,717,754000,9357140000); 22 INSERT INTO bbc VALUES (Bangladesh,South Asia,143998,152600000,67144000000); 23 INSERT INTO bbc VALUES (Barbados,Americas,430,272000,2518720000); 24 INSERT INTO bbc VALUES (Belarus,Europe,207595,9800000,20776000000); 25 INSERT INTO bbc VALUES (Belgium,Europe,30528,10300000,319609000000); 26 INSERT INTO bbc VALUES (Belize,Americas,22965,266000,NULL); 27 INSERT INTO bbc VALUES (Benin,Africa,112622,7100000,3763000000); 28 INSERT INTO bbc VALUES (Bhutan,South Asia,38364,2400000,1824000000); 29 INSERT INTO bbc VALUES (Bolivia,South America,1100000,9100000,NULL); 30 INSERT INTO bbc VALUES (Bosnia-Hercegovina,Europe,51129,4200000,8568000000); 31 INSERT INTO bbc VALUES (Botswana,Africa,581730,1800000,7812000000); 32 INSERT INTO bbc VALUES (Brazil,South America,8550000,182800000,564852000000); 33 INSERT INTO bbc VALUES (Brunei,Asia-Pacific,5765,374000,NULL); 34 INSERT INTO bbc VALUES (Bulgaria,Europe,110994,7800000,21372000000); 35 INSERT INTO bbc VALUES (Burkina Faso,Africa,274200,13800000,4968000000); 36 INSERT INTO bbc VALUES (Burma,Asia-Pacific,676552,50700000,NULL); 37 INSERT INTO bbc VALUES (Burundi,Africa,27816,7300000,NULL); 38 INSERT INTO bbc VALUES (Cambodia,Asia-Pacific,181035,14800000,4736000000); 39 INSERT INTO bbc VALUES (Cameroon,Africa,465458,16600000,13280000000); 40 INSERT INTO bbc VALUES (Canada,North America,9900000,32000000,908480000000); 41 INSERT INTO bbc VALUES (Cape Verde,Africa,4033,482000,853140000); 42 INSERT INTO bbc VALUES (Central African Republic,Africa,622984,3900000,NULL); 43 INSERT INTO bbc VALUES (Chad,Africa,1280000,9100000,2366000000); 44 INSERT INTO bbc VALUES (Chile,South America,756096,16200000,79542000000); 45 INSERT INTO bbc VALUES (China,Asia-Pacific,9600000,1300000000,1677000000000); 46 INSERT INTO bbc VALUES (Colombia,South America,1140000,45600000,NULL); 47 INSERT INTO bbc VALUES (Comoros,Africa,1862,812000,NULL); 48 INSERT INTO bbc VALUES (Costa Rica,Americas,51100,4300000,NULL); 49 INSERT INTO bbc VALUES (Croatia,Europe,56594,4400000,28996000000); 50 INSERT INTO bbc VALUES (Cuba,Americas,110860,11300000,NULL); 51 INSERT INTO bbc VALUES (Cyprus,Europe,9250,807000,14187060000); 52 INSERT INTO bbc VALUES (Czech Republic,Europe,78866,10200000,93330000000); 53 INSERT INTO bbc VALUES (Democratic Republic of Congo,Africa,2340000,56000000,6720000000); 54 INSERT INTO bbc VALUES (Denmark,Europe,43098,5400000,219510000000); 55 INSERT INTO bbc VALUES (Djibouti,Africa,23200,721000,NULL); 56 INSERT INTO bbc VALUES (Dominica,Americas,751,71000,259150000); 57 INSERT INTO bbc VALUES (Dominican Republic,Americas,48072,9000000,NULL); 58 INSERT INTO bbc VALUES (East Timor,Asia-Pacific,14609,857000,NULL); 59 -- tabbbc, 51, 50 60 INSERT INTO bbc VALUES (Ecuador,South America,272045,13400000,NULL); 61 INSERT INTO bbc VALUES (Egypt,Middle East,1000000,74900000,98119000000); 62 INSERT INTO bbc VALUES (El Salvador,Americas,21041,6700000,15745000000); 63 INSERT INTO bbc VALUES (Equatorial Guinea,Africa,28051,521000,484530000); 64 INSERT INTO bbc VALUES (Eritrea,Africa,117400,4561599,NULL); 65 INSERT INTO bbc VALUES (Estonia,Europe,45227,1300000,9113000000); 66 INSERT INTO bbc VALUES (Ethiopia,Africa,1130000,74200000,8162000000); 67 INSERT INTO bbc VALUES (Fiji,Asia-Pacific,18376,854000,NULL); 68 INSERT INTO bbc VALUES (Finland,Europe,338145,5200000,170508000000); 69 INSERT INTO bbc VALUES (Former Yugoslav Republic of Macedonia,Europe,25713,2000000,4700000000); 70 INSERT INTO bbc VALUES (France,Europe,543965,60700000,1826463000000); 71 INSERT INTO bbc VALUES (Gabon,Africa,267667,1400000,NULL); 72 INSERT INTO bbc VALUES (Georgia,Europe,69700,5000000,5200000000); 73 INSERT INTO bbc VALUES (Germany,Europe,357027,82500000,2484900000000); 74 INSERT INTO bbc VALUES (Ghana,Africa,238533,21800000,8284000000); 75 INSERT INTO bbc VALUES (Greece,Europe,131957,11000000,182710000000); 76 INSERT INTO bbc VALUES (Grenada,Americas,344,103000,NULL); 77 INSERT INTO bbc VALUES (Guatemala,Americas,108890,13000000,NULL); 78 INSERT INTO bbc VALUES (Guinea,Africa,245857,8800000,4048000000); 79 INSERT INTO bbc VALUES (Guinea-Bissau,Africa,36125,1600000,256000000); 80 INSERT INTO bbc VALUES (Guyana,South America,214969,768000,NULL); 81 INSERT INTO bbc VALUES (Haiti,Americas,27750,8500000,NULL); 82 INSERT INTO bbc VALUES (Honduras,Americas,112492,7200000,7416000000); 83 INSERT INTO bbc VALUES (Hungary,Europe,93030,9800000,81046000000); 84 INSERT INTO bbc VALUES (Iceland,Europe,103000,294000,11354280000); 85 INSERT INTO bbc VALUES (India,South Asia,3100000,1100000000,682000000000); 86 INSERT INTO bbc VALUES (Indonesia,Asia-Pacific,1900000,225300000,256842000000); 87 INSERT INTO bbc VALUES (Iran,Middle East,1650000,70700000,162610000000); 88 INSERT INTO bbc VALUES (Iraq,Middle East,438317,26500000,NULL); 89 INSERT INTO bbc VALUES (Ireland,Europe,70182,4000000,137120000000); 90 INSERT INTO bbc VALUES (Israel and Palestinian territories,Middle East,20770,3800000,4256000000); 91 INSERT INTO bbc VALUES (Italy,Europe,301338,57200000,1494064000000); 92 INSERT INTO bbc VALUES (Ivory Coast,Africa,322462,17100000,13167000000); 93 INSERT INTO bbc VALUES (Jamaica,Americas,10991,2700000,7830000000); 94 INSERT INTO bbc VALUES (Japan,Asia-Pacific,377864,127900000,4755322000000); 95 INSERT INTO bbc VALUES (Jordan,Middle East,89342,5700000,12198000000); 96 INSERT INTO bbc VALUES (Kazakhstan,Asia-Pacific,2700000,15400000,NULL); 97 INSERT INTO bbc VALUES (Kenya,Africa,582646,32800000,15088000000); 98 INSERT INTO bbc VALUES (Kiribati,Asia-Pacific,810,85000,82450000); 99 INSERT INTO bbc VALUES (Kuwait,Middle East,17818,2700000,48519000000);100 INSERT INTO bbc VALUES (Kyrgyzstan,Asia-Pacific,199900,5300000,NULL);101 INSERT INTO bbc VALUES (Laos,Asia-Pacific,236800,5900000,2301000000);102 INSERT INTO bbc VALUES (Latvia,Europe,64589,2300000,NULL);103 INSERT INTO bbc VALUES (Lebanon,Middle East,10452,3800000,18924000000);104 INSERT INTO bbc VALUES (Lesotho,Africa,30355,1800000,1332000000);105 INSERT INTO bbc VALUES (Liberia,Africa,99067,3600000,396000000);106 INSERT INTO bbc VALUES (Libya,Africa,1770000,5800000,25810000000);107 INSERT INTO bbc VALUES (Liechtenstein,Europe,160,34000,NULL);108 INSERT INTO bbc VALUES (Lithuania,Europe,65300,3400000,19516000000);109 INSERT INTO bbc VALUES (Luxembourg,Europe,2586,465000,26146950000);110 -- tabbbc, 101, 50111 INSERT INTO bbc VALUES (Madagascar,Africa,587041,18400000,5520000000);112 INSERT INTO bbc VALUES (Malawi,Africa,118484,12600000,2142000000);113 INSERT INTO bbc VALUES (Malaysia,Asia-Pacific,329847,25300000,NULL);114 INSERT INTO bbc VALUES (Mali,Africa,1250000,13800000,4968000000);115 INSERT INTO bbc VALUES (Malta,Europe,316,397000,4863250000);116 INSERT INTO bbc VALUES (Marshall Islands,Asia-Pacific,181,57000,135090000);117 INSERT INTO bbc VALUES (Mauritania,Middle East,1040000,3100000,1302000000);118 INSERT INTO bbc VALUES (Mauritius,Africa,2040,1200000,5568000000);119 INSERT INTO bbc VALUES (Mexico,North America,1960000,106400000,720328000000);120 INSERT INTO bbc VALUES (Micronesia,Asia-Pacific,700,111000,NULL);121 INSERT INTO bbc VALUES (Moldova,Europe,33800,4300000,3053000000);122 INSERT INTO bbc VALUES (Monaco,Europe,2,32000,NULL);123 INSERT INTO bbc VALUES (Mongolia,Asia-Pacific,1560000,2700000,NULL);124 INSERT INTO bbc VALUES (Morocco,Middle East,710850,31600000,48032000000);125 INSERT INTO bbc VALUES (Mozambique,Africa,812379,19500000,4875000000);126 INSERT INTO bbc VALUES (Namibia,Africa,824292,2000000,4740000000);127 INSERT INTO bbc VALUES (Nauru,Asia-Pacific,21,9900,NULL);128 INSERT INTO bbc VALUES (Nepal,South Asia,147181,26300000,6838000000);129 INSERT INTO bbc VALUES (New Zealand,Asia-Pacific,270534,4000000,81240000000);130 INSERT INTO bbc VALUES (Nicaragua,Americas,120254,5700000,4503000000);131 INSERT INTO bbc VALUES (Niger,Africa,1270000,12900000,2967000000);132 INSERT INTO bbc VALUES (Nigeria,Africa,923768,130200000,50778000000);133 INSERT INTO bbc VALUES (North Korea,Asia-Pacific,122762,22900000,NULL);134 INSERT INTO bbc VALUES (Norway,Europe,323759,4600000,239338000000);135 INSERT INTO bbc VALUES (Oman,Middle East,309500,3000000,23670000000);136 INSERT INTO bbc VALUES (Pakistan,South Asia,796095,161100000,96660000000);137 INSERT INTO bbc VALUES (Palau,Asia-Pacific,508,20000,NULL);138 INSERT INTO bbc VALUES (Panama,Americas,75517,3200000,NULL);139 INSERT INTO bbc VALUES (Papua New Guinea,Asia-Pacific,462840,5900000,3422000000);140 INSERT INTO bbc VALUES (Paraguay,South America,406752,6200000,NULL);141 INSERT INTO bbc VALUES (Peru,South America,1280000,28000000,NULL);142 INSERT INTO bbc VALUES (Poland,Europe,312685,38500000,234465000000);143 INSERT INTO bbc VALUES (Portugal,Europe,92345,10500000,150675000000);144 INSERT INTO bbc VALUES (Qatar,Middle East,11437,628000,NULL);145 INSERT INTO bbc VALUES (Republic of Congo,Africa,342000,3039126,NULL);146 INSERT INTO bbc VALUES (Romania,Europe,238391,22200000,64824000000);147 INSERT INTO bbc VALUES (Russia,Europe,17000000,141500000,482515000000);148 INSERT INTO bbc VALUES (Rwanda,Africa,26338,8600000,1892000000);149 INSERT INTO bbc VALUES (Samoa,Asia-Pacific,2831,182000,NULL);150 INSERT INTO bbc VALUES (San Marino,Europe,61,27000,NULL);151 INSERT INTO bbc VALUES (Sao Tome and Principe,Africa,1001,169000,62530000);152 INSERT INTO bbc VALUES (Saudi Arabia,Middle East,2240000,25600000,267008000000);153 INSERT INTO bbc VALUES (Senegal,Africa,196722,10600000,7102000000);154 INSERT INTO bbc VALUES (Serbia and Montenegro,Europe,102173,10500000,27510000000);155 INSERT INTO bbc VALUES (Seychelles,Africa,455,76000,NULL);156 INSERT INTO bbc VALUES (Sierra Leone,Africa,71740,5300000,1060000000);157 INSERT INTO bbc VALUES (Singapore,Asia-Pacific,660,4400000,106568000000);158 INSERT INTO bbc VALUES (Slovakia,Europe,49033,5400000,34992000000);159 INSERT INTO bbc VALUES (Slovenia,Europe,20273,2000000,29620000000);160 INSERT INTO bbc VALUES (Solomon Islands,Asia-Pacific,27556,504000,277200000);161 -- tabbbc, 151, 50162 INSERT INTO bbc VALUES (Somalia,Africa,637657,10700000,NULL);163 INSERT INTO bbc VALUES (South Africa,Africa,1220000,45300000,164439000000);164 INSERT INTO bbc VALUES (South Korea,Asia-Pacific,99313,48200000,673836000000);165 INSERT INTO bbc VALUES (Spain,Europe,505988,44100000,935361000000);166 INSERT INTO bbc VALUES (Sri Lanka,South Asia,65610,19400000,19594000000);167 INSERT INTO bbc VALUES (St Kitts and Nevis,Americas,269,46000,NULL);168 INSERT INTO bbc VALUES (St Lucia,Americas,616,152000,655120000);169 INSERT INTO bbc VALUES (St Vincent and the Grenadines,Americas,389,121000,441650000);170 INSERT INTO bbc VALUES (Sudan,Middle East,2500000,35000000,18550000000);171 INSERT INTO bbc VALUES (Surinam,South America,163265,442000,NULL);172 INSERT INTO bbc VALUES (Swaziland,Africa,17364,1100000,1826000000);173 INSERT INTO bbc VALUES (Sweden,Europe,449964,8900000,318353000000);174 INSERT INTO bbc VALUES (Switzerland,Europe,41284,7100000,342433000000);175 INSERT INTO bbc VALUES (Syria,Middle East,185180,18600000,22134000000);176 INSERT INTO bbc VALUES (Taiwan,Asia-Pacific,36188,22700000,302364000000);177 INSERT INTO bbc VALUES (Tajikistan,Asia-Pacific,143100,6300000,NULL);178 INSERT INTO bbc VALUES (Tanzania,Africa,945087,38400000,NULL);179 INSERT INTO bbc VALUES (Thailand,Asia-Pacific,513115,64100000,162814000000);180 INSERT INTO bbc VALUES (The Gambia,Africa,11295,1500000,NULL);181 INSERT INTO bbc VALUES (The Maldives,South Asia,298,338000,848380000);182 INSERT INTO bbc VALUES (The Netherlands,Europe,41864,16300000,516710000000);183 INSERT INTO bbc VALUES (The Philippines,Asia-Pacific,300000,82800000,96876000000);184 INSERT INTO bbc VALUES (Togo,Africa,56785,5100000,1938000000);185 INSERT INTO bbc VALUES (Tonga,Asia-Pacific,748,106000,NULL);186 INSERT INTO bbc VALUES (Trinidad and Tobago,Americas,5128,1300000,NULL);187 INSERT INTO bbc VALUES (Tunisia,Middle East,164150,10000000,26300000000);188 INSERT INTO bbc VALUES (Turkey,Europe,779452,73300000,274875000000);189 INSERT INTO bbc VALUES (Turkmenistan,Asia-Pacific,488100,5000000,NULL);190 INSERT INTO bbc VALUES (Tuvalu,Asia-Pacific,26,10000,NULL);191 INSERT INTO bbc VALUES (Uganda,Africa,241038,27600000,7452000000);192 INSERT INTO bbc VALUES (Ukraine,Europe,603700,47800000,60228000000);193 INSERT INTO bbc VALUES (United Arab Emirates,Middle East,77700,3100000,NULL);194 INSERT INTO bbc VALUES (United Kingdom,Europe,242514,59600000,2022824000000);195 INSERT INTO bbc VALUES (United States of America,North America,9800000,295000000,12213000000000);196 INSERT INTO bbc VALUES (Uruguay,South America,176215,3500000,NULL);197 INSERT INTO bbc VALUES (Uzbekistan,Asia-Pacific,447400,26900000,NULL);198 INSERT INTO bbc VALUES (Vanuatu,Asia-Pacific,12190,222000,297480000);199 INSERT INTO bbc VALUES (Vatican,Europe,0,NULL,NULL);200 INSERT INTO bbc VALUES (Venezuela,South America,881050,26600000,NULL);201 INSERT INTO bbc VALUES (Vietnam,Asia-Pacific,329247,83600000,45980000000);202 INSERT INTO bbc VALUES (Yemen,Middle East,536869,21500000,12255000000);203 INSERT INTO bbc VALUES (Zambia,Africa,752614,11000000,4950000000);204 INSERT INTO bbc VALUES (Zimbabwe,Africa,390759,12900000,6192000000);205 206 207 SELECT * FROM bbc208 CREATE TABLE bbc(209    NAME VARCHAR(50) NOT NULL210    ,region VARCHAR(60)211    ,AREA DECIMAL(10)         212    ,population DECIMAL(11)    213    ,gdp DECIMAL(14)        214    ,PRIMARY KEY (NAME)215    );216 -- 获得到和‘ Brazil’同属一个地区的所有国家217 SELECT * FROM bbc WHERE region = (SELECT region FROM bbc WHERE NAME=Brazil)218 219 -- 给出人口多于Russia的国家名称220 SELECT NAME FROM bbc WHERE population > (SELECT population FROM bbc WHERE  NAME=Russia)221 222 -- 给出‘India‘,‘Iran‘所在地区的所有国家信息223 SELECT * FROM bbc  WHERE region IN (SELECT region FROM bbc WHERE NAME=India OR NAME=Iran)224 225 -- 给出人均gdp超过‘United Kingdom‘(英国)的欧州国家226 SELECT * FROM bbc WHERE gdp > (SELECT gdp FROM bbc WHERE NAME=United Kingdom)227 228 -- 给出人口比‘Canada‘多但少于Algeria的国家的信息229 SELECT * FROM bbc WHERE population > (SELECT population FROM bbc WHERE NAME=Canada) AND population <(SELECT population FROM bbc WHERE NAME=Algeria)230 231 -- 给出gdp比任何欧州国家都多的国家232 SELECT NAME FROM bbc WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region =Europe)233 234 -- 给出每个地区人口最大的国家235 SELECT NAME,region FROM bbc WHERE  population IN (SELECT MAX(population) FROM bbc GROUP BY region)236 237 -- 给出地区中所有国家的人口总数为0的地区238 SELECT region,SUM(population) FROM bbc GROUP BY region HAVING SUM(population) = 0239 240 -- 有些国家的人口数比她的周边国家要多三倍,请列出这些国家和地区。241 SELECT NAME,region FROM bbc b WHERE population >4*(242 SELECT MAX(population)  FROM bbc c WHERE c.region = b.region AND c.NAME != b.NAME ) 

 

2016.9.27小程序---数据库练习1