Create & Modify

Create database

Create database Forests

Use:

Use Forests

Create Table

create table ForestDistricts
(ForestID int primary key,
 ForestDistrict Varchar(150) not null,
 HuntingDistrict int not null)

create table LargeWild
(LW_ID int references ForestDistricts(ForestID),
 Moose int not null,
 Deer int not null,
 FallowDeer int not null,
 Fawn int not null,
 Mouflon int not null,
 )

create table SmallWild
(SW_ID int references ForestDistricts(ForestID),
Fox int not null,
Badger int not null,
Jackal int not null,
Marten	int not null,
AmericanMink int not null,
FoulMarten int not null,
Hare int not null,
WildRabbits int not null
)

Refresh database to check if tables were created properly:


Check how it looks as a diagram:

create table from another table

select * into RLO_DROBNA
From Raport_RLO_DROBNA
Where Rodzaj_pozycji = 'S'

Inserts

To insert any values into tables created in prvs lesson

insert into ForestDistricts
(ForestID, ForestDistrict, HuntingDistrict)

values
(1, 'AUGUSTOW', 12),
(2, 'BIALOWIEZA', 1),
(3, 'BIELSK', 23)

or without column names:

insert into ForestDistricts

values
(4, 'BORKI', 12),
(5, 'BROWSK', 4),
(6, 'CZARNA BIALOSTOCKA', 27)


Alter table

adding new column to existing table

alter table LargeWild
add Boar int

remove column from existing table

alter table SmallWild
drop column WildRabbits

change datatype:

alter table LargeWild
alter column Mouflon float;

rename table

--method 1
alter table LargeWild
rename to Wild

--method 2
EXEC sp_rename 'LargeWild', 'Wild';

rename column:
EXEC sp_rename 'dbo.produkt.[Rodzaje towarów i usług]', 'Towary', 'COLUMN';

Update data in table

Update LargeWild
Set FallowDeer = 1
where Deer > 600

Delete selected values from table

Delete from SmallWild
Where WildRabbits = 0

Delete whole data in table

TRUNCATE TABLE SmallWild;

Delete table

DROP TABLE SmallWild;

Merge values from tables

MERGE ForestDistricts AS FD
USING (SELECT Moose, Deer FROM LargeWild) AS LW
ON LW.LW_ID=FD.ForestID
WHEN MATCHED AND FD.ForestDistrict = 'Olsztyn' THEN DELETE
WHEN MATCHED AND FD.HuntingDistrict is null THEN 
UPDATE SET FD.ForestDistrict = 'n/a'
WHEN NOT MATCHED THEN
INSERT(ForestID,ForestDistrict,HuntingDistrict)
VALUES(1, 'Olsztyn', 'n/a');

Delete database

DROP DATABASE testDB;