Skip to content

PostgreSQL

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

PostgreSQL

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
00 SELECT basics.ipynb
00 SELECT basics.ipynb
 
 
01 SELECT name.ipynb
01 SELECT name.ipynb
 
 
02 SELECT from World.ipynb
02 SELECT from World.ipynb
 
 
03 SELECT from Nobel.ipynb
03 SELECT from Nobel.ipynb
 
 
04 SELECT within SELECT.ipynb
04 SELECT within SELECT.ipynb
 
 
05 SUM and COUNT.ipynb
05 SUM and COUNT.ipynb
 
 
06 JOIN.ipynb
06 JOIN.ipynb
 
 
07 More JOIN operations.ipynb
07 More JOIN operations.ipynb
 
 
08 Using Null.ipynb
08 Using Null.ipynb
 
 
08+ Numeric Examples.ipynb
08+ Numeric Examples.ipynb
 
 
09 Self join.ipynb
09 Self join.ipynb
 
 
09+ COVID 19.ipynb
09+ COVID 19.ipynb
 
 
09- Window function.ipynb
09- Window function.ipynb
 
 
11 Module Feedback.ipynb
11 Module Feedback.ipynb
 
 
12-1 Helpdesk - Easy.ipynb
12-1 Helpdesk - Easy.ipynb
 
 
12-2 Helpdesk - Medium.ipynb
12-2 Helpdesk - Medium.ipynb
 
 
12-3 Helpdesk - Hard.ipynb
12-3 Helpdesk - Hard.ipynb
 
 
13-1 Guest House - Easy.ipynb
13-1 Guest House - Easy.ipynb
 
 
13-2 Guest House - Medium.ipynb
13-2 Guest House - Medium.ipynb
 
 
13-3 Guest House - Hard.ipynb
13-3 Guest House - Hard.ipynb
 
 
14-1 AdventureWorks - Easy.ipynb
14-1 AdventureWorks - Easy.ipynb
 
 
14-2 AdventureWorks - Medium.ipynb
14-2 AdventureWorks - Medium.ipynb
 
 
14-3 AdventureWorks - Hard.ipynb
14-3 AdventureWorks - Hard.ipynb
 
 
14-4 AdventureWorks - Resit.ipynb
14-4 AdventureWorks - Resit.ipynb
 
 
15-1 University Timetables - Easy.ipynb
15-1 University Timetables - Easy.ipynb
 
 
15-2 University Timetables - Medium.ipynb
15-2 University Timetables - Medium.ipynb
 
 
15-3 University Timetables - Hard.ipynb
15-3 University Timetables - Hard.ipynb
 
 
15-4 University Timetables - Resit.ipynb
15-4 University Timetables - Resit.ipynb
 
 
16-1 Musicians - Easy.ipynb
16-1 Musicians - Easy.ipynb
 
 
16-2 Musicians - Medium.ipynb
16-2 Musicians - Medium.ipynb
 
 
16-3 Musicians - Hard.ipynb
16-3 Musicians - Hard.ipynb
 
 
17-0 Dressmaker - Samples.ipynb
17-0 Dressmaker - Samples.ipynb
 
 
17-1 Dressmaker - Easy.ipynb
17-1 Dressmaker - Easy.ipynb
 
 
17-2 Dressmaker - Medium.ipynb
17-2 Dressmaker - Medium.ipynb
 
 
17-3 Dressmaker - Hard.ipynb
17-3 Dressmaker - Hard.ipynb
 
 
18-1 Congestion - Easy.ipynb
18-1 Congestion - Easy.ipynb
 
 
18-2 Congestion - Medium.ipynb
18-2 Congestion - Medium.ipynb
 
 
18-3 Congestion - Hard.ipynb
18-3 Congestion - Hard.ipynb
 
 
19 White Christmas.ipynb
19 White Christmas.ipynb
 
 
README.md
README.md
 
 

README.md

SQLZOO试题的PostgreSQL实现 | PostgreSQL Implementation of SQLZOO Solutions

步骤 Steps

  1. 需要用到ipython-sql包，安装方法：

    pip install ipython-sql

  2. 启动数据库localhost/sqlzoo（数据需提前导入）。

  3. 通过sqlalchemy形式的DB URI连接sqlzoo数据库。

  1. ipython-sql is required. Installation:

    pip install ipython-sql

  2. Boot the database localhost/sqlzoo (data should be imported before hand).

  3. Connect to the sqlzoo database by sqlalchemy compatible DB URI.

本案例使用PostgreSQL，也可以使用MySQL/MariaDB。 In this case, PostegreSQL is applied.

macOS Mojave无法安装mysql-python，只能装pymysql。因此如使用MySQL的话，URI要写成mysql+pymysq://...的形式。 macOS Majave cannot install mysql-python currently. Install pymysql insstead and write the URI in the form of mysql+pymysq://....

import getpass
%load_ext sql
pwd = getpass.getpass()
# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo
%sql postgresql://postgres:$pwd@localhost/sqlzoo

连接上以后还可以做一些设置，比如将长度显示行数的上限为10. Do some configuration after connection, e.g., set the display upper limit of rows = 10.

%config SqlMagic.displaylimit = 10

记得每次运行SQL语句时，前面都要加%%sql magic符。 Remember to add %%sql magic before running SQL syntax.

举例 Exmaple

%%sql
SELECT * FROM world
name continent area population gdp capital tld flag
Afghanistan Asia 652230.0 32225560.0 21992000000.0 Kabul .af //upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
Albania Europe 28748.0 2845955.0 13039000000.0 Tirana .al //upload.wikimedia.org/wikipedia/commons/3/36/Flag_of_Albania.svg
Algeria Africa 2381741.0 43000000.0 167555000000.0 Algiers .dz //upload.wikimedia.org/wikipedia/commons/7/77/Flag_of_Algeria.svg
Andorra Europe 468.0 77543.0 3278000000.0 Andorra la Vella .ad //upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
Angola Africa 1246700.0 31127674.0 126505000000.0 Luanda .ao //upload.wikimedia.org/wikipedia/commons/9/9d/Flag_of_Angola.svg
Antigua and Barbuda Caribbean 442.0 96453.0 1248000000.0 St. John's .ag //upload.wikimedia.org/wikipedia/commons/8/89/Flag_of_Antigua_and_Barbuda.svg
Argentina South America 2780400.0 44938712.0 637486000000.0 Buenos Aires .ar //upload.wikimedia.org/wikipedia/commons/1/1a/Flag_of_Argentina.svg
Armenia Eurasia 29743.0 2957500.0 11536000000.0 Yerevan .am //upload.wikimedia.org/wikipedia/commons/2/2f/Flag_of_Armenia.svg
Australia Oceania 7692024.0 25690023.0 1408675000000.0 Canberra .au //upload.wikimedia.org/wikipedia/commons/8/88/Flag_of_Australia_%28converted%29.svg
Austria Europe 83871.0 8902600.0 416835000000.0 Vienna .at //upload.wikimedia.org/wikipedia/commons/4/41/Flag_of_Austria.svg

195 rows, truncated to displaylimit of 10

world表一共195行，但由于全局设置了SqlMagic.displaylimit，默认只显示了前10行。 There are a total of 195 rows in world. But only 10 rows are displayed due to the global setting SqlMagic.displaylimit.

目录 Table of Contents

基础题 Fundermentals

Title Link
00 SELECT basics GitHub Gitee
01 SELECT name GitHub Gitee
02 SELECT from World GitHub Gitee
03 SELECT from Nobel GitHub Gitee
04 SELECT within SELECT GitHub Gitee
05 SUM and COUNT GitHub Gitee
06 JOIN GitHub Gitee
07 More JOIN operations GitHub Gitee
08 Using Null GitHub Gitee
08+ Numeric Examples GitHub Gitee
09- Window function GitHub Gitee
09 Self join GitHub Gitee
09+ COVID-19 GitHub Gitee

提高题 Advanced

Title Link
11 Module Feedback GitHub Gitee
12-1 Helpdesk - Easy GitHub Gitee
12-2 Helpdesk - Medium GitHub Gitee
12-3 Helpdesk - Hard GitHub Gitee
13-1 Guest House - Easy GitHub Gitee
13-2 Guest House - Medium GitHub Gitee
13-3 Guest House - Hard GitHub Gitee
14-1 AdventureWorks - Easy GitHub Gitee
14-2 AdventureWorks - Medium GitHub Gitee
14-3 AdventureWorks - Hard GitHub Gitee
14-4 AdventureWorks - Resit GitHub Gitee
15-1 University Timetables - Easy GitHub Gitee
15-2 University Timetables - Medium GitHub Gitee
15-3 University Timetables - Hard GitHub Gitee
15-4 University Timetables - Resit GitHub Gitee
16-1 Musicians - Easy GitHub Gitee
16-2 Musicians - Medium GitHub Gitee
16-3 Musicians - Hard GitHub Gitee
17-0 Dressmaker - Samples GitHub Gitee
17-1 Dressmaker - Easy GitHub Gitee
17-2 Dressmaker - Medium GitHub Gitee
17-3 Dressmaker - Hard GitHub Gitee
18-1 Congestion - Easy GitHub Gitee
18-2 Congestion - Medium GitHub Gitee
18-3 Congestion - Hard GitHub Gitee

挑战 Challenge

Title Link
19 White Christmas GitHub Gitee