CREATE TABLE courses (
   c_no text PRIMARY KEY,
   title text,
   hours integer);

   
CREATE TABLE students (
s_id integer PRIMARY KEY,
name text,
start_year integer);

INSERT INTO courses (c_no, title, hours)
VALUES ('CS301', 'Databases', 30),
('CS305', 'Networks', 60);

INSERT INTO students (s_id, name, start_year)
VALUES (1451, 'Anna', 2014),
(1432, 'Victor', 2014),
(1556, 'Nina', 2015);

UPDATE courses SET hours = hours*0.8 WHERE hours > 45; 


CREATE TABLE exams(
s_id integer REFERENCES students(s_id),
c_no text REFERENCES courses(c_no),
score integer,
CONSTRAINT pk PRIMARY KEY(s_id, c_no));

INSERT INTO exams(s_id, c_no, score)
VALUES (1451, 'CS301', 5),
(1556, 'CS301', 5),
(1451, 'CS305', 5),
(1432, 'CS305', 4);


UPDATE exams SET score  = score*1.1 FROM courses WHERE (courses.c_no = exams.c_no AND courses.hours > 10) ; 

ALTER TABLE students ADD COLUMN address text;

UPDATE students SET address = 'Address 1' WHERE name = 'Anna';

ALTER TABLE students ALTER COLUMN name SET NOT NULL;

SELECT * FROM courses;

SELECT title AS course_title, hours
FROM courses;

SELECT start_year FROM students;

SELECT DISTINCT start_year FROM students;

SELECT * FROM courses WHERE hours > 45;

SELECT * FROM exams ORDER BY score DESC;

SELECT * FROM exams ORDER BY score DESC LIMIT 2 OFFSET 1;

SELECT  s_id, avg(score) FROM exams GROUP BY s_id;

SELECT students.s_id, name, start_year, score FROM students  INNER JOIN exams ON students.s_id = exams.s_id;

SELECT students.name, exams.score
FROM students
LEFT JOIN exams
ON students.s_id = exams.s_id
AND exams.c_no = 'CS305';

SELECT students.name, exams.score
FROM students
FULL OUTER JOIN exams
ON students.s_id = exams.s_id
AND exams.c_no = 'CS305';

SELECT s_id FROM students EXCEPT SELECT s_id FROM exams WHERE exams.score < 5;

SELECT name, start_year
FROM students
WHERE s_id IN (SELECT s_id
FROM exams
WHERE c_no = 'CS305');

CREATE VIEW results AS
SELECT s_id , c_no, score FROM exams
JOIN students USING (s_id)
JOIN courses USING (c_no);

CREATE EXTENSION postgis; 

SELECT postgis_full_version(); 

CREATE TABLE cities ( id integer primary key, name varchar(50), geom geometry(POINT,4326) );

SELECT * from cities;

INSERT INTO cities (id, geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');
INSERT INTO cities (id, geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');
INSERT INTO cities (id, geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

SELECT * FROM cities;

SELECT id, ST_AsText(geom), ST_AsEwkt(geom), ST_X(geom), ST_Y(geom) FROM cities;

SELECT p1.name, p2.name, ST_DistanceSphere(p1.geom,p2.geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;


----------------------------------------------
Extract SQL from a database via Pgadmin
Select Database --> Backup --> Provide filename
Select Format --> plain
Select Encoding --> UTF8
Backup
----------------------------------