SQL

From today, we can run SQL queries with KLIPSE.

CREATE TABLE

CREATE TABLE play (game, points, day)

INSERT values

INSERT INTO play VALUES ("go", 500, "monday"),
("go", 300, "tuesday"),
("chess", 1250, "tuesday"),
("chess", 1250, "tuesday"),
("chess", 50, "sunday"),
("checkers", 100, "monday"),
("chess", 3200, "saturday");

SELECT

Let’s check the contents of our table:

SELECT * from play;

GROUP BY

SELECT game, sum(points) as total_points FROM play GROUP BY game

HAVING vs. WHERE

WHERE is for columns that are part of the table.

SELECT game, points FROM play WHERE points > 10

HAVING is for examinating the results of aggregation functions.

SELECT game, sum(points) as total_points FROM play GROUP BY game HAVING total_points > 10

JOIN

Let’s create another table in order to explore the joy of join.

Our new table is called settings and it contains the settings of the game:

• point_value: how much in dollars each point worths?

CREATE TABLE settings (game, point_value)

Let’s insert a row for each game - except for checkers:

INSERT INTO settings VALUES ("go", 1000),
("chess", 500);

Now, let’s join the play and settings table:

select * from play left join settings where play.game = settings.game;

Table alias

Sometimes, you want to join bewteen results of other queries. In that case, the result of the query is a table that Then you need to alias your table in order to let