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 ofjoin.

Our new table is calledsettings 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 forcheckers:

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

Now, let’s join theplay andsettings 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