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

Question

Question 1

A retail company uses a database to store information about its products and customers. The products table contains columns for product_id (integer, primary key), product_name (text), and price (decimal). The customers table contains columns for customer_id (integer, primary key), customer_name (text), and email (text). Write SQL queries to:

Create the products table.
Insert a new product into the products table.
Update the price of an existing product.

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 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;

Answer 2

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 3

Question 1 Answer:

1. Create the products table:


CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
price DECIMAL(10, 2)
);

2. Insert a new product into the products table:


INSERT INTO products (productid, productname, price)
VALUES (101, 'Wireless Headphones', 79.99);

3. Update the price of an existing product:


UPDATE products
SET price = 89.99
WHERE product_id = 101;

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

10 Database and file concepts (1 questions)

Question 1

Question 2

Question 3