Database 101

Ash in Sydney

Mon Apr 20 2020

# 1. PostgreSQL

1. PostgreSQL

# 1.1. SQL Commands

# 1.1.1. CREATE DATABASE

\q to quit
\l list all the databases
CREATE DATABASE test;

# 1.1.2. Connect to databases

Psql -h local host -p 5432 -U amigoscode test
\c test is also connect to a database (after \l)

# 1.1.3. Dangerous command

CREATE DATABASE test
DROP DATABASE test; // drop all of datas

# 1.1.4. CREATE TABEL

CREATE TAVLE table_name (
Column name + data type + constraints if any
)
\d d for describe
\d test // show test table contents

Should you use constraints ?

# 1.1.5. INSERT INTO

INSERT INTO test ( first name,lastname)
VALUE (‘jane’,’jones’);

# 1.1.6. Generate 1000 rows into using mockaroo

a. Provides random data
b. Using \i
c. Pwd to show. Route

# 1.1.7. Select from

# 1.1.8. Order by

12345 ASC
54321 DESC

SELECT * form person ORDER BY first_name ASC;
SELECT * form person ORDER BY first_name, birth ASC;

# 1.1.9. DISTINCT

	 SELECT country_name from person  // will show all the results in country_name
	 SELECT DISTINCT country_name from person. // only show one times if is same country

# 1.1.10. WHERE

	 SELECT * form person WHERE gender = ‘MALE’;
	 SELECT * form person WHERE gender =‘male’ AND (countryName = ‘Poland’ or countryName =‘China ‘) ;

# 1.1.11. Comparison operators

	SELECT 1 >= 2; 	// will return f
	SELECT 1 <= 2;  	// will return t
	SELECT 1 <> 2;	// t <> means not equal

# 1.1.12. Limit / Offset keywords

LIMIT is widely use , but it is not an official way . However . FETCH is

   SELECT * FROM person LIMIT 10; // select first 10 rows
   SELECT * FROM person LIMIT 5; // select first 5 rows
   
   SELECT * FROM person OFFSET 5; // select from 6 to the follow rows

   SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY; // select first 5 rows. OFFICIAL WAY !!

# 1.1.13. “IN” use a "set" to enhance the ability of WHERE Normally we can use this way to find out the items in the table

SELECT * FROM person WHERE country_of_birth = ‘China’
OR country_of_birth = ‘France” OR country_of_birth = ‘Brazil”;

BUT we can use this

SELECT * FROM person WHERE country_of_birth IN (‘China’,’Brazil’,’France’); // we can also add ORDER BY for next sentence  if don’t add ";"  

ORDER BY country_of_birth;

# 1.2. Week 4 lecture

# 1.2.1. GROUP BY

# 1.2.2. Aggregate fuctions

SELECT AVG(shrdiv) AS avgdiv FROM shr;
SELECT AVG(shrdiv/shrprice*100) AS avgdic FROM shr;

# 1.2.3. Subqueris

SELECT shrfirm, shrpe FROM shr WHERE
shrpe > (
		SELECT AVG(shrpe) FROM shr
);

dont read from top , first read inside

SELECT pizza,price
FROM menu
WHERE price >= ALL (
	SELECT price
	FROM menu WHERE country = ‘italy’
);

# 1.2.4. ANY operator

# 1.2.5. Self-Join

Join a table to itself

# 1.3. WEEK4 TUT

# 1.3.1. Show the number of missions flown in each year. Order by the launch year


SELECT projectname,launchyear, count(*) AS missions FROM nasa2_mission GROUP BY projectname, launchyear ORDER BY launchyear;

# 1.3.2. Select all projects and the number of missions in each project, for all projects with more than one mission. With the given data.

SELECT projectname, count(*) AS number_missions from nasa2_mission GROUP BY projectname having count(*) > 1;

# 1.3.3. Select every astronaut who flew more than one mission. Give the name, number of missions.

select astroname, count(*) as missions FROM nasa2_astronaut natural join nasa2_
assigned GROUP BY astroname having count(*) > 1

having is pretty useful

count(*) should be careful here

# 1.3.4. Show the number of astronauts born each year.

SELECT birth,count(*) FROM nasa2_astronaut GROUP BY birth;

# 1.3.5. Show the number of different craft that have flown across all missions.

SELECT missiontype, count(*) FROM nasa2_mission GROUP BY missiontype;

# 1.3.6. Show the number of mission types that have flown across all missions.

！TODO

# 1.3.7. COPY to

\copy (select * FROM nasa_projects) to test_projects.csv

so i can save the result of the query in () as a csv file

# 1.4. WEEK 5 TUT

# 1.4.1. JOIN

Different types of Join

# 1.4.2. NATURAL JOIN

If there is same colum, using NATURE JOIN


select * from menu natural join recipe order by pizza;

# 1.4.3. INNER JOIN


select * from menu INNER JOIN recipe using(pizza);

# 1.4.4. Cross product

There are two ways of using cross product.

select * from menu , recipe;

get every combines that can be combined

select * from menu , recipe where menu.pizza = recipe.pizza;

same result with Natural join

# 1.5. Test sample 3

# 1.5.1. Question 1

select astroname, death, (death - birth) as age FROM nasa2_astronaut WHERE death >= 1960 and death =< 1970 ORDER BY death;