%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
%sql postgresql://mitloehn:mitloehn@postgresql.student-db.svc.cluster.local/mitloehn
%%sql
-- list item table
select * from item;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 6 rows affected.
ordr | prod | qty | sess |
---|---|---|---|
1 | HUB1 | 1 | None |
1 | HD05 | 2 | None |
2 | MON1 | 1 | None |
2 | HD05 | 1 | None |
3 | HD05 | 1 | None |
4 | MON1 | 1 | None |
%%sql
-- Number of units sold per product
select prod, sum(qty)
from item
group by prod;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
prod | sum |
---|---|
HUB1 | 1 |
HD05 | 4 |
MON1 | 2 |
%%sql
-- Number of orders per product
select prod, count(*)
from item
group by prod;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
prod | count |
---|---|
HUB1 | 1 |
HD05 | 3 |
MON1 | 2 |
%%sql
-- Sales per product
select prod, sum(price * qty)
from item join prod on item.prod = prod.id
group by prod;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
prod | sum |
---|---|
HUB1 | 15.90 |
HD05 | 223.60 |
MON1 | 131.80 |
%%sql
-- Sales per customer
select cust, sum(price * qty)
from item join prod on item.prod = prod.id
join ordr on ordr.id = item.ordr
group by cust;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
cust | sum |
---|---|
BK | 55.90 |
DF | 193.60 |
RV | 121.80 |
%%sql
-- Create tables for student - enroll - course, insert some data
drop table if exists enroll;
drop table if exists course;
drop table if exists student;
create table student (id varchar primary key, name varchar);
insert into student values ('h001', 'Buster Keaton');
insert into student values ('h002', 'Myrna Loy');
insert into student values ('h003', 'Douglas Fairbanks');
create table course (id varchar primary key, name varchar);
insert into course values ('DB', 'Databases');
insert into course values ('PR', 'Python Programming');
insert into course values ('DA', 'Data Structures and Algorithms');
create table enroll (student varchar references student(id), course varchar references course(id));
-- one course with no students, and one student with no courses
insert into enroll values ('h001', 'DB');
insert into enroll values ('h002', 'DB');
insert into enroll values ('h001', 'PR');
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn Done. Done. Done. Done. 1 rows affected. 1 rows affected. 1 rows affected. Done. 1 rows affected. 1 rows affected. 1 rows affected. Done. 1 rows affected. 1 rows affected. 1 rows affected.
[]
%%sql
-- Number of courses enrolled per student, for all students: nice, with names
select student.id, student.name, count(course)
from student left join enroll on student.id = enroll.student
group by student.id, student.name;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
id | name | count |
---|---|---|
h002 | Myrna Loy | 1 |
h001 | Buster Keaton | 2 |
h003 | Douglas Fairbanks | 0 |
%%sql
-- Number of courses enrolled per student, for all students
select student.id, student.name, count(course)
from student left join enroll on student.id = enroll.student
group by student.id;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
id | name | count |
---|---|---|
h002 | Myrna Loy | 1 |
h001 | Buster Keaton | 2 |
h003 | Douglas Fairbanks | 0 |
%%sql
-- Number of students per course, for all courses: use columns aliases for nicer result
select course.id, course.name as course_name, count(student) as students
from course left join enroll on course.id = enroll.course
group by course.id, course.name;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 3 rows affected.
id | course_name | students |
---|---|---|
DB | Databases | 2 |
DA | Data Structures and Algorithms | 0 |
PR | Python Programming | 1 |
%%sql
-- Students not enrolling in any course
select student.id, student.name, count(course) as courses
from student left join enroll on student.id = enroll.student
group by student.id, student.name
having count(course) = 0;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 1 rows affected.
id | name | courses |
---|---|---|
h003 | Douglas Fairbanks | 0 |
%%sql
-- Courses with fewer than two students
select course.id, course.name as course_name, count(student) as students
from course left join enroll on course.id = enroll.course
group by course.id, course.name
having count(student) < 2;
* postgresql://mitloehn:***@postgresql.student-db.svc.cluster.local/mitloehn 2 rows affected.
id | course_name | students |
---|---|---|
DA | Data Structures and Algorithms | 0 |
PR | Python Programming | 1 |