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

Question

Question 2

You are designing a database for a library. The books table stores information about books with columns book_id (integer, primary key), title (text), author (text), and publication_year (integer). The members table stores information about library members with columns member_id (integer, primary key), member_name (text), and borrowed_books (text). Write SQL queries to:

Create the books table.
Delete a book from the books table based on its book_id.
Update the author of a book.

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 student_id IN (SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM courses));

Answer 2

Question 2 Answer:

1. Create the books table:


CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255),
publication_year INT
);

2. Delete a book from the books table:


DELETE FROM books
WHERE book_id = 123;

3. Update the author of a book:


UPDATE books
SET author = 'Jane Doe'
WHERE book_id = 123;

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

10 Database and file concepts (1 questions)

Question 2

Question 3

Support e-Consult Kenya