-- Creates ------------------------------------------------------------

CREATE DATABASE stars;


-- TRUNCATE TABLE objects;
CREATE TABLE objects (
	oname VARCHAR(32) PRIMARY KEY,
	distance FLOAT NULL
);

CREATE TABLE collections (
	colname VARCHAR(32) PRIMARY KEY REFERENCES objects
);

CREATE TABLE heavenly_bodies (
	hname VARCHAR(32) PRIMARY KEY REFERENCES objects,
	description VARCHAR(1024) NULL,
	mass FLOAT NULL,
	age INT NULL,
	day FLOAT NULL, 
	radius FLOAT NULL, 
	temperature INT NULL
);
		

CREATE TABLE stars (
	sname VARCHAR(32) PRIMARY KEY REFERENCES heavenly_bodies, 
	spactral_class VARCHAR(32)NULL, 
	root_collection VARCHAR(32) REFERENCES collections NOT NULL
);

CREATE TABLE planets (
	pname VARCHAR(32) PRIMARY KEY REFERENCES heavenly_bodies, 
	orbital_period FLOAT NULL, 
	root_star VARCHAR(32)REFERENCES stars NOT NULL
);

CREATE TABLE moons (
	mname VARCHAR(32) PRIMARY KEY REFERENCES heavenly_bodies, 
	orbital_period FLOAT NULL, 
	root_planet VARCHAR(32)REFERENCES planets NOT NULL
);



CREATE TABLE constellations(
	conname VARCHAR(32) PRIMARY KEY REFERENCES collections,
	stars_marked INT NULL,
	stars_with_planet INT NULL,
	stars_bigger INT NULL
);

CREATE TABLE galaxies (
	gname VARCHAR(32) PRIMARY KEY REFERENCES collections,
	type VARCHAR(16) NOT NULL, 
	apparent_magnitude FLOAT NOT NULL,
	addarent_dim_x FLOAT NOT NULL,
	addarent_dim_y FLOAT NOT NULL,
	root_constellation VARCHAR(32) REFERENCES constellations NOT NULL
);

CREATE TABLE elements (
	atomic_number INT PRIMARY KEY,
	elname VARCHAR(32) NOT NULL
);


CREATE TABLE is_built_of (
	id INT IDENTITY PRIMARY KEY,
	percantage FLOAT NOT NULL,
	element INT REFERENCES elements NOT NULL, 
	object VARCHAR(32) REFERENCES objects NOT NULL
);

CREATE TABLE discoveries (
    id INT IDENTITY PRIMARY KEY,
    object VARCHAR(32) REFERENCES objects UNIQUE,
    date DATE NULL
);

CREATE TABLE scientists (
	name VARCHAR(64) PRIMARY KEY
);

CREATE TABLE finding (
	id INT IDENTITY PRIMARY KEY,
	discovery INT REFERENCES discoveries NOT NULL,
	discoverer VARCHAR(64) REFERENCES scientists NOT NULL
);


-- Inserts -----------------------------------------------------------

-- Constellations

INSERT INTO objects VALUES ('Milk Way', NULL),
	('Antilia', NULL),
	('Aries', NULL);

INSERT INTO collections VALUES ('Milk Way'),
	('Antilia'),
	('Aries');

INSERT INTO constellations VALUES ('Milk Way', NULL, NULL, NULL),
	('Antilia', NULL, NULL, NULL),
    ('Aries', NULL, NULL, NULL);

-- Galaxies

INSERT INTO objects VALUES ('Creter', NULL),
	('Crux', NULL),
	('Pae', NULL);

INSERT INTO collections VALUES ('Creter'),
    ('Crux'),
    ('Pae');

INSERT INTO galaxies VALUES ('Creter', 'spiral', 2.42, 1.345, 1.23, 'Milk Way'),
    ('Crux', 'spiral', 4.123, 1.234, 3.23, 'Aries'),
    ('Pae', 'eliptic', 5.1234, 1.234, 6.2341, 'Milk Way');

-- Stars

INSERT INTO objects VALUES ('Sun', 1);
INSERT INTO objects VALUES ('Aim', 29);
INSERT INTO objects VALUES ('Caph', 101);
INSERT INTO objects VALUES ('Syrius', 50);
INSERT INTO objects VALUES ('1 Ari', 542);
INSERT INTO objects VALUES ('14 Ari', 32);
INSERT INTO objects VALUES ('33 Ari', 135);
INSERT INTO objects VALUES ('19 Ari', 600);

INSERT INTO heavenly_bodies VALUES ('Sun', 'Our sun', 2022341.0, 10452134, 3.112, 50.32, 1200000000);
INSERT INTO heavenly_bodies VALUES ('Aim', '', 2041.0, 3452134, 23.112, 20.32, 23100002);
INSERT INTO heavenly_bodies VALUES ('Caph', '', 4022341.0, 852134, 50.112, 500.32, 812000000);
INSERT INTO heavenly_bodies VALUES ('Syrius', '', 431.0, 754, 5202, 180.32, 1851200);
INSERT INTO heavenly_bodies VALUES ('1 Ari', '', 1241.0, 645, 5112, 180.32, 451200);
INSERT INTO heavenly_bodies VALUES ('14 Ari', '', 2141.0, 123434, 76512, 12.32, 2251200);
INSERT INTO heavenly_bodies VALUES ('33 Ari', '', 12341.0, 121234, 652, 250.32, 21200);
INSERT INTO heavenly_bodies VALUES ('19 Ari', '', 6534, 12124, 520.0, 230.32, 2351200);

INSERT INTO stars VALUES ('Sun',  10, 'Milk Way');
INSERT INTO stars VALUES ('Aim',  22, 'Aries');
INSERT INTO stars VALUES ('Caph', 31, 'Crux');
INSERT INTO stars VALUES ('Syrius', 321, 'Crux');
INSERT INTO stars VALUES ('1 Ari', '12H', 'Aries');
INSERT INTO stars VALUES ('14 Ari', 'B8V', 'Aries');
INSERT INTO stars VALUES ('33 Ari', 'G2V', 'Aries');
INSERT INTO stars VALUES ('19 Ari', 'C9G', 'Aries');


-- Planets

INSERT INTO objects VALUES ('Mercury', 6300000);
INSERT INTO objects VALUES ('Venus', 7300000);
INSERT INTO objects VALUES ('Earth', 8300000);
INSERT INTO objects VALUES ('Mars', 8900000);
INSERT INTO objects VALUES ('Jupiter', 9000000);
INSERT INTO objects VALUES ('Saturn', 10000000);
INSERT INTO objects VALUES ('Uranus', 11000000),
	('Pluto', 20000000);

INSERT INTO heavenly_bodies VALUES ('Mercury', '', 300000.0, 2345200, 0.3412, 1.00, 12);
INSERT INTO heavenly_bodies VALUES ('Venus', '', 8003000.0, 23500, 123.12, 4.12, 100);
INSERT INTO heavenly_bodies VALUES ('Earth', 'Sky-blue planet.', 6300.0, 575100, 023, 0.8321, 2120);
INSERT INTO heavenly_bodies VALUES ('Mars', 'Planet we are going to live on ;)', 6230.0, 2313, 333, 821, 50);
INSERT INTO heavenly_bodies VALUES ('Jupiter', '', 1630.0, 235240, 223, 0.8321, 20);
INSERT INTO heavenly_bodies VALUES ('Saturn', '', 8630.0, 23500, 323, 521, 234);
INSERT INTO heavenly_bodies VALUES ('Uranus', '', 1630.0, 23100, 5123, 8321, 242),
	('Pluto', '', 3, 20, 0.3, 300, '-200');

INSERT INTO planets VALUES ('Mercury', 0.41, 'Sun');
INSERT INTO planets VALUES ('Venus', 1.4, 'Sun');;
INSERT INTO planets VALUES ('Earth',  10.2, 'Sun');
INSERT INTO planets VALUES ('Mars', 2.1, 'Sun');
INSERT INTO planets VALUES ('Jupiter',  2.2, 'Sun')
INSERT INTO planets VALUES ('Saturn', 7.2, 'Sun');
INSERT INTO planets VALUES ('Uranus', 20.1, 'Sun'),
	('Pluto', 10, 'Sun');

-- Moons

INSERT INTO objects VALUES ('Europa', 31.1),
	('Moon', 112.1),
	('Deimos', 12.1),
	('Phobos', 30);

INSERT INTO heavenly_bodies VALUES ('Europa', '', 3.0, 2345200, 0.3412, 1.00, 122),
	('Moon', '', 8.0, 2300, 13.12, 4.12, 10),
	('Deimos', '', 60.0, 260, 0.12, 0.8321, 140),
	('Phobos', '', 4, 234, 0.5, 0.9, 80);

INSERT INTO moons VALUES ('Europa',  1.2, 'Jupiter'),
	('Moon',  2.2, 'Earth'),
	('Deimos', 0.7, 'Mars'),
	('Phobos', 0.07, 'Mars');

-- Elements

INSERT INTO elements VALUES (1, 'Gal');
INSERT INTO elements VALUES (2, 'Hel');
INSERT INTO elements VALUES (3, 'Arsen'), 
    (5, 'Boron'),
    (6, 'Carbon'),
    (7, 'Nitrogen'),
    (8, 'Oxygen'),
    (9, 'Fluorine'),
    (10, 'Neon'),
    (11, 'Sodium');

-- Built of

INSERT INTO is_built_of VALUES (3.134, 1, 'Milk Way');
INSERT INTO is_built_of VALUES (6.123, 2, 'Pae');
INSERT INTO is_built_of VALUES (0.12, 3, 'Crux');
INSERT INTO is_built_of VALUES (6.14, 1, 'Pae'),
    (20, 6, 'Earth'),
    (5.3, 8, 'Earth'),
    (0.04, 10, 'Earth'), 
    (33.2, 6, 'Mars'),
    (1.12, 7, 'Mars');

-- Scientists

INSERT INTO scientists VALUES ('Damian Skrodzki')
INSERT INTO scientists VALUES ('Pawel Matuszewski');
INSERT INTO scientists VALUES ('Jan Kowalski');
INSERT INTO scientists VALUES ('Ela Rogowska');
INSERT INTO scientists VALUES ('Tomcio Paluch');
INSERT INTO scientists VALUES ('Cezary Andryskowski');

-- Discoveries 

INSERT INTO discoveries VALUES ('Milk Way', '1000-01-01');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Damian Skrodzki');
INSERT INTO discoveries VALUES ('Crux', '1996-01-05');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Damian Skrodzki');
INSERT INTO discoveries VALUES ('Sun', '2010-12-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Damian Skrodzki');
INSERT INTO discoveries VALUES ('Aim', '2000-12-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Damian Skrodzki');
INSERT INTO discoveries VALUES ('Caph', '1990-12-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Damian Skrodzki');

INSERT INTO discoveries VALUES ('Earth', '2004-12-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Tomcio Paluch');

INSERT INTO discoveries VALUES ('Pae', '1996-01-05');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Jan Kowalski');
INSERT INTO discoveries VALUES ('Antilia', '1996-07-15');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Jan Kowalski');

INSERT INTO discoveries VALUES ('Aries', '1804-01-23');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Cezary Andryskowski');
INSERT INTO discoveries VALUES ('Moon', '2010-12-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Cezary Andryskowski');

INSERT INTO discoveries VALUES ('Jupiter', '2010-09-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Pawel Matuszewski');
INSERT INTO discoveries VALUES ('Mars', '2010-12-10');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Pawel Matuszewski');

INSERT INTO discoveries VALUES ('Europa', '2010-02-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Ela Rogowska');
INSERT INTO discoveries VALUES ('Venus', '2010-03-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Ela Rogowska');
INSERT INTO discoveries VALUES ('Deimos', '2010-11-08');
INSERT INTO finding VALUES (IDENT_CURRENT('discoveries'), 'Ela Rogowska');



-- Selects ------------------------------------------------------------------

-- Wypisuje wszystkie obiekty odkryte przez "Damian Skrodzki" 
-- w chronoligicznej kolejności
SELECT discoveries.date "Data odkrycia", discoveries.object "Obiekt"
	FROM finding f, discoveries
	WHERE f.discoverer = 'Damian Skrodzki' and 
		discoveries.id = f.discovery
    ORDER BY discoveries.date;
    

-- Konstalacja zawierajca najwicej galaktyk + gwiazd(bez tych w galaktykach)
select top 1 c.conname "Największa konstelacja"
	from constellations c
	order by ((select COUNT(*)
			from stars s
			where s.root_collection = c.conname)+
		(select COUNT(*)
			from galaxies g
			where g.root_constellation = c.conname)) desc;
            

-- Liczba gwiazd (samotnych + tych z galaktyk) dla każdej konstelacji
select conname "Nazwa gwiazdozbioru", (select count(*)
        from galaxies, stars
        where galaxies.root_constellation=conname
            and stars.root_collection=gname) as "W galaktykach", (select COUNT(*)
        from stars
        where root_collection=conname) as "Samotnych gwiazd", (select count(*)
        from galaxies, stars
        where galaxies.root_constellation=conname
            and stars.root_collection=gname) + (select COUNT(*)
        from stars
        where root_collection=conname) as "Suma gwiazd"
    from constellations c
    order by ("Suma gwiazd") desc;


-- Wszyskie księżyce układu słonecznego malejąco wg masy
select  mname as 'Nazwa księżyca', mass as "Masa"
    from hmoons, planets p
    where hmoons.root_planet=p.pname and p.root_star='Sun'
    order by mass desc;


-- Wszystkie księżyce w układzie słonecznym posortowane po odległości planety
-- wokol ktorej kraza od slonca.
select mname as 'Nazwa księżyca', mass as "Masa", 
        objects.distance as "Odległość planety ok słońca"
	from moons, planets, heavenly_bodies, objects
	where root_planet=pname and root_star='Sun' and hname=mname and oname=pname
	order by "Odległość planety ok słońca" desc;

-- Dla każdego ksiezyca podac mniejsze od niego planety
select mname, h1.mass as 'mass of moon', pname, h2.mass as 'mass of planet'
	from moons, planets, heavenly_bodies h1, heavenly_bodies h2
	where mname=h1.hname and pname=h2.hname 
		and h2.mass < h1.mass
	order by pname;
	

-- Ilość każdego pierwiastka we wszechświecie posortowana od najczęściej
-- występujących.
select elname as "Nazwa pierwiastka" , sum(mass*percantage) as "Masa"
    from is_built_of, heavenly_bodies, elements
    where atomic_number=element and is_built_of.object=hname
    group by  elname
    order by "Masa" desc;


-- 10 ostatnio odkrytych obiektów + nazwy odkrywców, od ostatnio odkrytego
select top 10 date, oname, scientists.name
    from discoveries, objects, finding f, scientists
    where object=oname and f.discoverer=scientists.name and f.discovery=discoveries.id
    order by date desc;

-- Najbardziej aktywny odkrywca w ciągu ostatnich 5 lat


-- kolekcja (konstalacja lub galaktyka) z największą liczbą gwiazd
select top 1 colname, COUNT(*) as sum
	from stars s, collections c
	where s.root_collection = colname
	group by colname
	order by  COUNT(*) desc;

-- konstelacja z największą liczbą gwiazd
select top 1 conname, COUNT(*) as sum
	from stars s, constellations c
	where s.root_collection = conname
	group by conname
	order by  COUNT(*) desc;

-- konstelacja z największą liczbą galaktyk
select top 1 conname, COUNT(*) 
	from constellations, galaxies
	where root_constellation=conname
	group by conname
	order by COUNT(*) desc;

	




-- Delete database
USE master
GO
ALTER DATABASE stars
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE stars;