DDM - INSERT, UPDATE, DELETE

In [23]:
SELECT * FROM tCity WHERE CountryCode =
    (SELECT Code FROM tCountry WHERE CountryName LIKE 'Estonia')
(25 rows affected)
Total execution time: 00:00:00.050
Out[23]:
IDCityNameCountryCodeDistrictPopulation
3791TallinnESTHarjumaa 403981
3792TartuESTTartumaa 101246
4080NarvaESTIda-Virumaa68680
4081NarvaESTIda-Virumaa68680
4082NarvaESTIda-Virumaa68680
4083NarvaESTIda-Virumaa68680
4084NarvaESTIda-Virumaa68680
4085NarvaESTIda-Virumaa68680
4086NarvaESTIda-Virumaa68680
4087NarvaESTIda-Virumaa68680
4088NarvaESTIda-Virumaa68680
4089ViljandiESTViljandimaa0
4090NarvaESTIda-Virumaa68680
4091K-JESTIda-Virumaa0
4092NarvaESTIda-Virumaa68680
4093PärnuESTPärnumaa85760
4094PärnuESTPärnumaa85760
4095PärnuESTPärnumaa85760
4096KoluvereESTLäänemaa333
4097JõhviESTIda-Virumaa10051
4098PärnuESTPärnumaa85760
4099PärnuESTPärnumaa85760
4100HaapsaluESTLäänemaa10000
4101JõhviESTIda-Virumaa10051
4102HaapsaluESTLäänemaa10000
In [9]:
INSERT INTO tCity
    VALUES((SELECT MAX(ID)+1 FROM tCity),'Narva', 
    (SELECT Code FROM tCountry WHERE CountryName LIKE 'Estonia'),
    NULL,
    0
        )
(1 row affected)
Total execution time: 00:00:00.014
In [12]:
INSERT INTO tCity(ID, CityName, CountryCode, Population)
    VALUES((SELECT MAX(ID)+1 FROM tCity),'Pärnu', 
    (SELECT Code FROM tCountry WHERE CountryName LIKE 'Estonia'),
    0
        )
(1 row affected)
Total execution time: 00:00:00.174
In [22]:
UPDATE tCity
SET District = (SELECT DISTINCT District FROm tCity WHERE CityName LIKE 'Narva'), 
    Population=0
WHERE CityNAme LIKE 'K-J'
(1 row affected)
Total execution time: 00:00:00.027
In [29]:
SELECT * , getdate() AS AddDate  --uue tabeli loomiseks ja kirjete lisamiseks
    INTO EestiLinnad
    FROM tCity
    WHERE CountryCode LIKE 'EST'
(26 rows affected)
Total execution time: 00:00:00.198
In [31]:
INSERT INTO  Eestilinnad
SELECT *,getdate()
FROM tCity
WHERE CountryCode LIKE 
    (SELECt Code FROM tCountry WHERE CountryName LIKE 'Latvia')
(3 rows affected)
Total execution time: 00:00:00.110
In [35]:
SELECT * FROM Eestilinnad
WHERE CityName LIKE '%J_rve'

UPDATE Eestilinnad
SET Population=45454
WHERE CityName LIKE '%J_rve'
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.050
Out[35]:
IDCityNameCountryCodeDistrictPopulationAddDate
4091Kohtla-JärveESTIda-Virumaa454542021-12-01 15:09:57.333
In [57]:
--DELETE FROM EestiLinnad
SELECT * FROM EestiLinnad
(34 rows affected)
Total execution time: 00:00:00.022
Out[57]:
IDCityNameCountryCodeDistrictPopulationAddDate
2434RigaLVARiika 7643282021-12-01 15:41:31.217
2435DaugavpilsLVADaugavpils 1148292021-12-01 15:41:31.217
2436LiepajaLVALiepaja 894392021-12-01 15:41:31.217
2447VilniusLTUVilna 5779692021-12-01 15:41:31.217
2448KaunasLTUKaunas 4126392021-12-01 15:41:31.217
2449KlaipedaLTUKlaipeda 2024512021-12-01 15:41:31.217
2450ŠiauliaiLTUŠiauliai 1465632021-12-01 15:41:31.217
2451PanevezysLTUPanevezys 1336952021-12-01 15:41:31.217
3791TallinnESTHarjumaa 4039812021-12-01 15:41:31.217
3792TartuESTTartumaa 1012462021-12-01 15:41:31.217
4080NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4081NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4082NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4083NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4084NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4085NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4086NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4087NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4088NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4089ViljandiESTViljandimaa02021-12-01 15:41:31.217
4090NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4091Kohtla-JärveESTIda-Virumaa02021-12-01 15:41:31.217
4092NarvaESTIda-Virumaa686802021-12-01 15:41:31.217
4093PärnuESTPärnumaa1000002021-12-01 15:41:31.217
4094PärnuESTPärnumaa1000002021-12-01 15:41:31.217
4095PärnuESTPärnumaa1000002021-12-01 15:41:31.217
4096KoluvereESTLäänemaa3332021-12-01 15:41:31.217
4097JõhviESTIda-Virumaa100512021-12-01 15:41:31.217
4098PärnuESTPärnumaa1000002021-12-01 15:41:31.217
4099PärnuESTPärnumaa1000002021-12-01 15:41:31.217
4100HaapsaluESTLäänemaa100002021-12-01 15:41:31.217
4101JõhviESTIda-Virumaa100512021-12-01 15:41:31.217
4102HaapsaluESTLäänemaa100002021-12-01 15:41:31.217
4103PärnuESTPärnumaa1000002021-12-01 15:41:31.217
In [56]:
--1. Kopeerige tabelisse EestiLinnad regiooni Baltic Countries kirjed 
--2. Eemaldage tabelitest Estonia minimaalse elanikude arvuga linna andmed
INSERT INTO EestiLinnad 
SELECT *, getdate() 
FROM tCity
WHERE CountryCode IN 
    (SELECT Code FROM tCountry WHERE Region LIKE 'Baltic Countries')
(34 rows affected)
Total execution time: 00:00:00.059