SQL作业问题。。。帮帮忙啊。。

来源：百度知道 编辑：UC知道 时间：2024/09/21 10:47:40
II. Study the following tables carefully and then answer the queries in SQL listed below:

NATION

NATCODE Nation_Name Exchange_Rate
UK United Kingdom 1.00
USA United States 0.67
AUS Australia 0.46
IND INDIA 0.0228

STKCODE STKFIRM STKPRICE STKQTY STKDIV NATCODE
FC Freedonia Copper 27.5 10,529 1.84 UK
PT Patagonian Tea 55.25 12,635 2.50 UK
AR Abyssinian Ruby 31.82 22,010 1.32 UK
MG Minnesota Gold 53.87 816,122 1.00 USA
GP Georgia Peach 2.35 387,333 0.20 USA
NE Narembeen Emu 12.34 45,619 1.00 AUS
QD Queensland Diamond 6.73 89,251 0.50 AUS
IR Indooroorpilly Ruby 15.92 56,147 0.50 AUS
BD Bombay Duck 25.55 167,382 1.00 IND
Queries

i. Report the value of each stock holding in UK pounds. Sort the report by nation and firm.

ii. Report by nation the total value of stockholdings.

iii. Report the number of stocks and their total value by n

i. Report the value of each stock holding in UK pounds. Sort the report by nation and firm.

SELECT b.STKCODE,b.STKFIRM,b.STKPRICE*a.Exchange_Rate AS STKPRICE_NEW FROM nation a ,stockinfo b WHERE b.natcode=a.natcode
ORDER BY b.natcode,b.STKFIRM

ii. Report by nation the total value of stockholdings.

SELECT NATCODE, sum(STKPRICE*STKQTY) AS Tal_stockholdings FROM stockinfo GROUP BY natcode

iii. Report the number of stocks and their total value by nation.

SELECT NATCODE, COUNT(STKPRICE*STKQTY) AS tal_num,sum(STKPRICE*STKQTY) AS Tal_value FROM stockinfo GROUP BY natcode

iv. Report the total value of stocks for nations with two or more listed stocks.

SELECT NATCODE, sum(STKPRICE*STKQTY) AS Tal_stockholdings FROM stockinfo GROUP BY natcode HAVING COUNT(STKPRICE*STKQTY)>=2

v. How many firms are in the stock portfolio?

SELECT COUNT(*) FROM stockinfo

vi. How many fir