Information Technology IT – 10 Database and file concepts | e-Consult

Question

Question 3

A university is creating a database to manage student records. The students table contains columns for student_id (integer, primary key), student_name (text), course_id (integer, foreign key referencing the courses table), and grade (text). The courses table contains columns for course_id (integer, primary key) and course_name (text). Write SQL queries to:

Create the students table.
Update the course a student is enrolled in.
Delete a student from the students table if they are no longer enrolled in any courses.

```

Answer 1

Question 3 Answer:

1. Create the students table:


CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
course_id INT,
grade VARCHAR(2)
);

2. Update the course a student is enrolled in:


UPDATE students
SET course_id = 456
WHERE student_id = 789;

3. Delete a student from the students table if they are no longer enrolled in any courses:

This requires a more complex query involving a subquery to identify students not in the courses table. Here's an example:


DELETE FROM students
WHERE studentid IN (SELECT studentid FROM students WHERE studentid NOT IN (SELECT studentid FROM courses));

Information Technology IT – 10 Database and file concepts | e-Consult

10 Database and file concepts (1 questions)

Question 3