Refine and Refresh#

Exercise 1 – Considering the following tables from two different modules in the computing school, DB and FOC.

DB_module

Student_name	DB_Grade
Garry	C
Robin	D
Hans	A
Lily	B
Sophie	B

FOC_module

Student_name	FOC_Grade
Garry	A
Robin	C
Hans	B
Denis	A
Lewis	D

a. Create the tables above in the schema, taking into account the practical size of the data.

1
CREATE TABLE brookes.DB_module (
2
    id INT PRIMARY KEY AUTO_INCREMENT,
3
    Student_name TEXT NOT NULL,
4
    DB_Grade TEXT NOT NULL
5
);
6
CREATE TABLE brookes.FOC_module (
7
    id INT PRIMARY KEY AUTO_INCREMENT,
8
    Student_name TEXT NOT NULL,
9
    FOC_Grade TEXT NOT NULL
10
);
11

12
INSERT INTO brookes.DB_module (Student_name, DB_Grade) VALUES
13
('Garry', 'C'),
14
('Robin', 'D'),
15
('Hans',  'A'),
16
('Lily',  'B'),
17
('Sophie','B');
18

19
INSERT INTO brookes.FOC_module (Student_name, FOC_Grade) VALUES
20
('Garry', 'A'),
21
('Robin', 'C'),
22
('Hans',  'B'),
23
('Denis', 'A'),
24
('Lewis', 'D');

b.

Write a basic SQL statement to retrieve the students who are intersecting between both modules.

1
SELECT Student_name
2
FROM brookes.db_module
3
WHERE Student_name IN (
4
    SELECT Student_name
5
    FROM brookes.foc_module
6
);

The university is planning an external workshop. Only the students enrolled in both modules, DB and FOC, are allowed to attend that workshop. Write a Join statement to help the coordinator make the right decision.

1
select * from brookes.db_module JOIN brookes.foc_module ON
2
brookes.DB_module.Student_name=brookes.foc_module.Student_name;

1
select db_Student_name as Student_name from (
2
select d.id as db_id,
3
d.Student_name as db_Student_name,
4
d.DB_Grade as db_Grade,
5
f.id as foc_id,
6
f.Student_name as foc_Student_name,
7
f.FOC_Grade as foc_Grade
8
from brookes.db_module as d JOIN brookes.foc_module as f ON
9
d.Student_name=brookes.f.Student_name) as temp;

Validate your answers by comparing the output from sections b(1) and b(2).

c. The workshop coordinator only selects students enrolled in DB and FOC modules. However, there are not enough students with the necessary conditions to fill all the available seats. So the coordinator wants a list of students who are already enrolled in one module.

1
SELECT d.Student_name FROM db_module AS d LEFT JOIN foc_module AS f ON d.Student_name = f.Student_name
2
UNION
3
SELECT f.student_name FROM brookes.db_module AS d RIGHT JOIN brookes.foc_module AS f ON d.Student_name = f.Student_name;

1
SELECT Student_name
2
FROM DB_module
3
UNION
4
SELECT Student_name
5
FROM FOC_module;

TIP
UNION 会自动去重，而 UNION ALL 不会

d. A programming contest has been planned to take place on Friday morning in the library. Students who take the DB module can join the contest. However, students enrolled in the FOC class can not join because their module has a class at the contest time. Write a join SQL statement to retrieve the prospective contest candidates.

1
SELECT d.Student_name
2
FROM brookes.db_module AS d
3
LEFT JOIN brookes.foc_module AS f
4
    ON d.Student_name = f.Student_name
5
WHERE f.Student_name IS NULL;

e. Dr Maged is presenting a basic introduction to databases. Only students enrolled in the FOC module but not enrolled in the DB module can join the presentation. Help the teacher invite deserving students to the presentation.

1
SELECT f.Student_name
2
FROM brookes.db_module AS d
3
RIGHT JOIN brookes.foc_module AS f
4
    ON d.Student_name = f.Student_name
5
WHERE d.Student_name IS NULL;