Write a query that selects the names of all parents together with the age of their youngest child

These are my notes for SICP(Structure and Interpretation of Computer Programs). Hope they’ll be of some help to you.

A table stores data. It consists of …

• a fixed number of columns.
• data entries stored in rows.

• To make a table in SQL, use a CREATE TABLE statement:

• To create rows of data, UNION together SELECT statements:

• To create rows of data from existing tables, use a SELECT statement with a FROM clause:

Given multiple tables, we can join them together by specifying their names, separated by commas, in the FROM clause of a SELECT statement.

When we join two tables, we get a new table with one row for each combination of rows from the original tables.

Table songs:

name | artist |album

Table albums:

name |artist | release_year

Table artists:

name | first_year_active

1. Write an SQL query that outputs the first 10 artists who became active after 2015.

   SELECT name FROM artists WHERE first_year_active > 2015 LIMIT 10;

2. Write an SQL query that outputs the names and artists of songs that were released in 2010 ordered by the first year active of the artist.

   SELECT s.name, s.artist FROM songs AS s, artists AS ar, albums AS al WHERE album = al.name AND s.artist = ar.name AND release_year = 2010 ORDER BY first_year_active;

So far, our SQL statements have referred to the values in a single row at a time.

Write a query that outputs the name of dogs that either have long fur or are named Grover.

Join

Write a query that outputs the names and fur types of all of fillmore’s children.

Aggregation

Aggregation is the process of doing operations on groups of rows instead of just a single row.

SQL provides aggregate functions whose return values can be used as entries in a column.

table dogs

out put the average age of all dogs:

output:

output the total number of rows:

output:

Aggregate function

table dogs

output the sum of ages of all dogs:

output the name thet comes first alphabetically:

Groups

By default, aggregation id performed over all the rows of the table.

• We can specify that we want to group rows based on values in a particular column using the GROUP BY clause in a SELECT statement.

table dogs

Write a query that finds out the average age of dogs for each fur type.

output:

More on group by

You can GROUP BU any valid SQL expression, which includes using multiple column names and operators.

• A single group consists of all rows for which [expression] evaluates to the same value.
• The output table will have one row per group.

Check your understanding

table dogs

table parents

1. Write a query that outputs a table containing the average age of each parent’s children.

   SELECT parent, AVG(age) AS avg_age FROM dogs, parents WHERE name = child GROUP BY parent;

   output:

   parent	avg_age
   delano	4
   fillmore	9.5
   eisenhower	8

2. Write a query that outputs a table with 2 rows: one with the number of dogs of even ages and the other with the number of dogs of odd ages (ignore order).

   Remember that you can GROUP BY expressions containing operators!

   SELECT COUNT(*) AS count FROM dogs GROUP BY age % 2 = 0;

   output:

Filtering Groups

We know how to filter individual rows using the WHERE clause.

To filter groups, use the HAVING [condition] clause!

table dogs

Write a query that finds the average age of dogs for each fur type if there are more than one dogs with that fur type.

output:

Check your understanding

table dogs

table parents

Write a query that outputs the average age of each parent’s children if that parent’s youngest child is at least 5.

output:

Mutating Tables

Databases

In real databases, it’s common practice to initialize empty tables and add rows as new data is introduced.

Create / remove tables

• To create an empty table, use the CREATE TABLE statement, specifying the table name and column names (and possible default values):

  CREATE TABLE [name]([columns]); CREATE TABLE parents(parent, child); CREATE TABLE dogs(name, fur, phrase DEFAULT 'woof');

• To remove a table from our database, use the DROP TABLE statement:

  DROP TABLE [IF EXISTS] [name]; DROP TABLE dogs; DROP TABLE IF EXISTS parents;

Inserting records

• To insert rows into a table:

Updating Records

• To update existing entries in a table:

  UPDATE [table] SET [column] = [expression] WHERE [condition];

• To delete existing rows in a table:

  DELETE FROM [table] WHERE [condition];

Summary

• Cerate empty table

  CREATE TABLE [name]([columns]);

  • using default values

    CREATE TABLE [name](..., [column] DEFAULT [value], ...);

• Remove table from database

  DROP TABLE [IF EXISTS] [name];

• Inserting records (new row)

  INSERT INTO [table]([columns]) VALUES([values]), ([values]); INSERT INTO [table] VALUES(..., [values (one for each column)], ...);

• Updating records (existing row):

  UPDATE [table] SET [column] = [expression] WHERE [condition]; DELETE FROM [table] WHERE [condition];

Summary

• We can use aggregate function to perform operations on a set of rows rather than on individual rows.
• To specify an expression by which to group rows, use the GROUP BY clause.
• To filter groups based on a condition over the whole group, use the HAVING clause.
• In real database, we commonly initialize empty tables and insert, update, or remove records over time.