EXERCISE A

DROP TABLE IF EXISTS students;
CREATE TABLE students (sname varchar(100), sid int NOT NULL, address varchar(100))TYPE=InnoDB;

CREATE TABLE courses (cid int NOT NULL, cname varchar(100), teacher varchar(100))TYPE=InnoDB;

CREATE TABLE signup (sid int NOT NULL, cid int NOT NULL)TYPE=InnoDB;

INSERT INTO students VALUES ('Student One', 0001, 'Vejen 1');
INSERT INTO students VALUES ('Student Two', 0002, 'Gaden 2');
INSERT INTO students VALUES ('Student Three', 0003, 'Ved Skellet 3');
INSERT INTO students VALUES ('Student Four', 0004, 'Gadevej 4');
INSERT INTO students VALUES ('Student Five', 0005, 'Stien 5');
INSERT INTO students VALUES ('Student Six', 0006, 'Vejen 6');
INSERT INTO students VALUES ('Student Seven', 0007, 'Gaden 7');

INSERT INTO courses VALUES (0011, 'It-fag nummer 1', 'Teacher One');
INSERT INTO courses VALUES (0012, 'IT-fag nummer 2', 'Teacher Two');
INSERT INTO courses VALUES (0013, 'IT-fag nummer 3', 'Teacher Two');
INSERT INTO courses VALUES (0014, 'IT-fag nummer 4', 'Teacher Two');
INSERT INTO courses VALUES (0015, 'IT_fag nummer 5', 'Teacher Five');

INSERT INTO signup VALUES (0001, 0011);
INSERT INTO signup VALUES (0001, 0012);
INSERT INTO signup VALUES (0002, 0011);
INSERT INTO signup VALUES (0003, 0012);
INSERT INTO signup VALUES (0005, 0013);
INSERT INTO signup VALUES (0002, 0014);
INSERT INTO signup VALUES (0004, 0014);
INSERT INTO signup VALUES (0002, 0015);
INSERT INTO signup VALUES (0001, 0015);
INSERT INTO signup VALUES (0002, 0013);

EXERCISE B1+2

SELECT cname, teacher 
FROM courses
ORDER BY cname;

SELECT sid, cname
FROM courses, signup
WHERE courses.cid = signup.cid
ORDER BY cname;

EXERCISE B3

SELECT sname, students.sid, cname, teacher
FROM students, courses, signup
WHERE students.sid = signup.sid AND courses.cid = signup.cid
ORDER BY sname;

EXERCISE B4

SELECT sname, signup.sid, COUNT(signup.cid) AS total_courses
FROM students, signup
WHERE students.sid = signup.sid
GROUP BY sname, sid
ORDER BY total_courses;

EXERCISE B5

SELECT sname, signup.sid, COUNT(signup.cid) AS total_courses
FROM students LEFT JOIN signup
ON students.sid = signup.sid
GROUP BY sname, sid
ORDER BY total_courses;

EXERCISE C

CREATE TABLE weblog2 (client varchar(70), time datetime, web_page varchar(70)); 

LOAD DATA LOCAL INFILE "wwwstats.txt" INTO TABLE weblog2;


EXERCISE C1

SELECT COUNT(*) AS total_entries
FROM weblog;

EXERCISE C2

SELECT web_page, COUNT(client) AS total_visits
FROM weblog
GROUP BY client, web_page
ORDER BY total_visits DESC;

EXERCISE C3

CREATE TABLE web_page (web_page varchar(70), amount int);

INSERT INTO web_page (SELECT web_page, COUNT(client) AS total_visits
FROM weblog
GROUP BY client, web_page
ORDER BY total_visits DESC);

EXERCISE C4

SELECT client, COUNT(web_page) AS accessed
FROM weblog
GROUP BY client, web_page
ORDER BY client, accessed;