COMP207LAB

Assignment 1

Basic SQL

插入语法 （INSRT INTO)

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

读取数据表

select * from runoob_tbl;

Execrises

P1

P2

---

Solution
p1

---

p2

---

p3

---

p4

---

p5

---

p6

---

Lab2 (ttl)

PART1: Simple queries

-- Average grade
SELECT AVG(grade)
FROM Enrolment
WHERE s_id = 1;

-- How many years have COMP207 been taught
SELECT COUNT(*)
FROM CoursesInYear
WHERE code = 'COMP207';

-- How many students are in COMP207 this year?

SELECT COUNT(*)
FROM Enrolment
WHERE code = 'COMP207' AND year = 2021;

• Simple joins:

-- 1.What lecturers have taught you? - sort by their birthday
SELECT first_name,last_name
FROM Enrolment e,CoursesInyear C,Lecturers l
WHERE c.l_id = l.l_id AND s_id=1 AND e.code = c.code AND c.year = e.year
ORDER BY birthday;

-- 2.What are the names of the courses you have attended? Sort by name
SELECT name
FROM Enrolment e,Courses c   
WHERE e.code = c.code AND s_id = 1
ORDER BY name;

-- 3.What are the names of the courses l, Rasums Ibsen-Jensen, have taught?(my name is unique...) -sort by code
SELECt name
FROM CoursesInYear ciy,Lecturers l,Courses c  
WHERE ciy.code = c.code AND l.l_id = ciy.l_id
AND first_name='Rasmus' AND last_name = 'Ibsen-Jensen'
ORDER BY c.code;

• Unions:

  -- What names (of students, lecturers or courses) exists?
  SELECT name
  FROM Courses
  UNION
  SELECT first_name
  FROM Students
  UNION
  SELECT last_name
  FROM Students
  UNION
  SELECT first_name
  FROM Lecturers
  UNION
  SELECT last_name
  FROM Lecturers;
  -- List all lecturers and studnets, ordered by their first and last name
  SELECT first_name, last_name
  FROM Students
  UNION
  SELECT first_name,last_name
  FROM Lecturers
  ORDER BY first_name, last_name;

• How would you do the above simple queries for each student or each course, depending(instead of you and COMP207)If you got the point of GROUP BY, this is hopefully reasonably clear and easy to do, otherwise you will hopefully get it from the answer… How about the simple joins?

-- Simple queries:
SELECT s_id, AVG(grade)
FROM Enrolment
GROUP BY s_id;

SELECT code, COUNT(*)
FROM CoursesInYear
GROUP BY code;

SELECT code, COUNT(*)
FROM Enrolment
WHERE year = 2021
GROUP BY code;

-- Simple joins:
SELECT s_id, first_name, last_name
FROM Enrolment e, CoursesInYear c, Lecturers l   
WHERE c.l_id = l.l_id AND e.code = c.code AND c.year=e.year
ORDER BY birthday;

SELECT name,s_id
FROM Enrolment e, Courses c
WHERE e.code = c.code
ORDER BY name;

SELECT name, first_name, last_name
FROM CoursesInYear ciy, Lecturers l, Courses C
WHERE ciy.code = c.code AND l.l_id = ciy.l_id
ORDER BY c.code;

PART 2: More complex queries

-- Find the students you have shared a course with
SELECT DISTINCT first_name, last_name
FROM Students s, Enrolment e1, Enrolment e2
WHERE s.s_id = e1.s_id AND e2.s_id AND e1.code = e2.code AND e1.year = e2.year;

-- Find the lectures who has not taught you
SELECT *
FROM Lecturers
WHERE l_id not in (SELECT l_id FROM Enrolment e, CoursesInYear c 
                    WHERE s_id = 1 AND e.code = c.code AND c.year = e.year);
                
-- Find the staff-student ratio(i.e. how many student per staff) in the computer science department this year
SELECT student_count/COUNT(*)
FROM (SELECT count(*)AS student_count
        FORM Students
        WHERE s_id IN (SELECT s_id
                        FROM Enrolments NATURAL JOIN Courses
                        WHERE year = 2021 AND department = 'Computer Science')),
        Lectures
WHERE l_id IN (SELECT l_id
                FROM CoursesInYear NATURAL JOIN Courses
                WHERE year = 2021 AND
                department = 'Computer Science');

-- Using views:
CREATE VIEW sc AS
SELECT COUNT(*) AS student_count
FROM Lecturers
WHERE l_id IN (SELECT l_id
                FROM CoursesInYear NATURAL JOIN Courses
                WHERE Year = 2021 AND department = 'Computer Science');

CREATE VIEW lc AS
SELECT COUNT(*) AS lecturer_count
FROM Lecturers
WHERE l_id IN (SELECT l_id
                FROM CoursesInYear NATURAL JOIN Courses
                WHERE Year = 2021 AND department = 'Computer Science');
SELECT studnet_count/lecturer_count
FROM sc,lc;


--WHAT day(s) in the year are the most common birthdays for students?
--Without views
SELECT MONTH(birthday),DAYOFMONTH(birthday)
FROM Students
GROUP BY DAYOFMONTH(birthday) + MONTH(birthday)*100
HAVING COUNT(*) IN (SELECT MAX(cnt)
                    FROM (SELECT COUNT(*) AS cnt
                            FROM Students
                            GROUP BY DAYOFMONTH(birthday)+MONTH(birthday)*100)AS T);

-- Using views:
CREATE VIEW birthdayCounter AS
SELECT DAYOFMONTH(birthday) + MONTH(birthday)*100, COUNT(*) as cnt
FROM Students
GROUP BY DAYOFMONTH(birthday) + MONTH(birthday)*100;

SELECT MONTH(birthday),DAYOFMONTH(birthday)
FROM Students
GROUP BY DAYOFMONTH(birthday) + MONTH(birthday)*100
HAVING COUNT(*) IN (SELECT MAX(cnt)
                                FROM birthdayCounter);

-- Sort all students so the first is the one with the soonest birthday - you can sort the ones with the same birthday by name(i.e. if you had your birthday yesterday you should be last, if you have it today you should be first, ignoring others with the same birthday)
-- Without views
SELECT first_name, last_name
FROM (SELECT 1 as part, birthday, first_name,last_name
Students
        WHERE DAYOFMONTH(birthday) + MONTH(birthday)*100 >= DAYOFMONTH(CURRENT_DATE)+MONTH(CURRENT_DATE)*100
        UNION
        SELECT 2, birthday, first_name, last_name
        FROM Students
        WHERE DAYOFMONTH(birthday)+MONTH(birthday)*100<DAYOFMONTH(CURRENT_DATE)+MONTH(CURRENT_DATE)*100) AS T
ORDER BY part,
DAYOFMONTH(birthday) + MONTH(birthday)*100, first_name,last_name;

-- Using views:
CREATE VIEW birthdaySorter AS
SELECT 1 as part, birthday, first_name, last_name
FROM Students
WHERE
DAYOFMONTH(birthday) + MONTH(birthday)*100 >=
DAYOFMONTH(CURRENT_DATE) + MONTH(CURRENT_DATE)*100
UNION
SELECT 2, birthday, first_name, last_name
FROM Students
WHERE DAYOFMONTH(birthday) + MONTH(birthday * 100 <
DAYOFMONTH(CURRENT_DATE) + MONTH(CURRENT_DATE)*100;

SELECT first_name, last_name
FROM birthdaySorter
ORDER BY part, DAYOFMONTH(birthday) + MONTH(birthday)*100,
first_name,last_name;

赏

谢谢你请我吃糖果

微信