Scribd
Upload
31 views4 pages

EduDB Query Part1

The document outlines a database structure for an educational institution, detailing tables such as students, subjects, lecturers, and enrollments. It provides a set of SQL queries to be executed for various tasks, including listing subjects, students in specific classes, and calculating scores. The queries are to be saved in individual SQL files named after the student and their ID.

Uploaded by

maianh0805fw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
31 views4 pages

EduDB Query Part1

The document outlines a database structure for an educational institution, detailing tables such as students, subjects, lecturers, and enrollments. It provides a set of SQL queries to be executed for various tasks, including listing subjects, students in specific classes, and calculating scores. The queries are to be saved in individual SQL files named after the student and their ID.

Uploaded by

maianh0805fw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Practice – part 1:

Simple SELECT
Given a designed database that contains the following tables. The description in detail
is found in Database description_Final_edudb_v2.doc file.

´ student(student_id, first_name, last_name, dob, gender, address, note, clazz_id)


´ subject(subject_id, name, credit, percentage_final_exam)
´ lecturer(lecturer_id, first_name, last_name, dob, gender, address, email)
´ teaching(subject_id, lecturer_id)
´ clazz(clazz_id, name, lecturer_id, monitor_id)
´ enrollment(student_id, subject_id, semester, midterm_score, final_score)

Bài làm của sv: Các lệnh cần được lưu trong 1 file cho mỗi sinh viên, tên file:
tensv_mssv-SQLPart1.sql. Ví dụ: oanhnt_20202201_SQLPart1.sql
Solution:
Create and import database: using EducationDB_v2_withCreateDB.sql
• Connnect to database server with super user and execute commands from
sql file:
psql –h localhost –f path_to_file_ EducationDB_v2_withCreateDB.sql postgres
postgres
1. List of subjects having 5 or more credits.
2. List of students in the class named "CNTT 2 K58".
3. List of students in classes whose name contains "CNTT"
4. Display a list of students who have enrolled in both ”Tin học đại cương" and ”Cơ sở dữ liệu".
5. Display a list of students who have enrolled in "Tin học đại cương" or "Cơ sở dữ liệu".
6. Display subjects that have never been registered by any students
7. List of subjects (subject name and credit number corresponding) that student "Nguyễn Hoài
An" have enrolled in the semester '20171'.
8. Show the list of students who enrolled in 'Cơ sở dữ liệu' in semesters = '20172'). This list
contains student id, student name, midterm score, final exam score and subject score. Subject
score is calculated by the weighted average of midterm score and final exam score : subject
score = midterm score * (1- percentage_final_exam/100) + final score
*percentage_final_exam/100.
9. Display IDs of students who failed the subject with code 'IT1110' in semester '20171'. Note: a
student failed a subject if his midterm score or his final exam score is below 3 ; or his subject
score is below 4.
10. List of all students with their class name, monitor name.

You might also like