2. Working with pgAdmin

Requirements

• Your database must store the following student data:

• Your database must store the following course data:

• Your database must store the following course enrollments:

Tasks

1. Create the tables to store all of this data

   CREATE TABLE students (
       student_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       email TEXT,
       enrollment_date DATE
   );
   
   CREATE TABLE courses (
       course_id SERIAL PRIMARY KEY,
       course_name TEXT,
       course_description TEXT
   );
   
   CREATE TABLE enrollments (
       enrollment_id SERIAL PRIMARY KEY,
       student_id INT REFERENCES students(student_id),
       course_id INT REFERENCES courses(course_id),
       enrollment_date DATE
   );
2. Insert the data provided into the tables

   INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
   ('Alice', 'Johnson', 'alice.johnson@example.com', '2024-09-15'),
   ('Bob', 'Smith', 'bob.smith@example.com', '2024-09-16'),
   ('Charlie', 'Williams', 'charlie.williams@example.com', '2024-09-17');
   
   INSERT INTO courses (course_name, course_description) VALUES
   ('Physics 101', 'Introduction to Physics'),
   ('Literature 201', 'Basics of World Literature'),
   ('History 101', 'A Survey of Historical Events');
   
   INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
   ((SELECT student_id FROM students WHERE first_name = 'Alice' AND last_name = 'Johnson'), (SELECT course_id FROM courses WHERE course_name = 'Physics 101'), '2024-09-18'),
   ((SELECT student_id FROM students WHERE first_name = 'Alice' AND last_name = 'Johnson'), (SELECT course_id FROM courses WHERE course_name = 'Literature 201'), '2024-09-18'),
   ((SELECT student_id FROM students WHERE first_name = 'Bob' AND last_name = 'Smith'), (SELECT course_id FROM courses WHERE course_name = 'History 101'), '2024-09-19');

3. Write SELECT statements to do the following:

   • Retrieve the full names of all students

     SELECT first_name || ' ' || last_name AS full_name FROM students;
   • Retrieve the course names of all of "Bob Smith"'s courses

     SELECT course_name FROM courses
     JOIN enrollments ON courses.course_id = enrollments.course_id
     JOIN students ON students.student_id = enrollments.student_id
     WHERE students.first_name = 'Bob' AND students.last_name = 'Smith';
   • Retrieve all students enrolled in "Physics 101"

     SELECT first_name || ' ' || last_name AS full_name FROM students
     JOIN enrollments ON students.student_id = enrollments.student_id
     JOIN courses ON courses.course_id = enrollments.course_id
     WHERE courses.course_name = 'Physics 101';
4. Change "Charlie William"'s email to charlie.w.newemail@example.com

   UPDATE students
   SET email = 'charlie.w.newemail@example.com'
   WHERE first_name = 'Charlie' AND last_name = 'Williams';
5. Remove "Alice Johnson" from "Literature 201"

   DELETE FROM enrollments
   WHERE student_id = (SELECT student_id FROM students WHERE first_name = 'Alice' AND last_name = 'Johnson')
   AND course_id = (SELECT course_id FROM courses WHERE course_name = 'Literature 201');

Requirements

• Your database must store the following author data:

• Your database must store the following book data:

• Your database must store the following book availability data:

Tasks

1. Create the tables to store all of this data

   CREATE TABLE authors (
       author_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       date_of_birth DATE,
       nationality TEXT
   );
   
   CREATE TABLE books (
       book_id SERIAL PRIMARY KEY,
       title TEXT,
       genre TEXT,
       publication_year INT,
       author_id INT REFERENCES authors(author_id)
   );
   
   CREATE TABLE book_availability (
       availability_id SERIAL PRIMARY KEY,
       book_id INT REFERENCES books(book_id),
       available BOOLEAN,
       last_checkout_date DATE
   );
2. Insert the data provided into the tables

   INSERT INTO authors (first_name, last_name, date_of_birth, nationality) VALUES
   ('George', 'Orwell', '1903-06-25', 'British'),
   ('Jane', 'Austen', '1775-12-16', 'British'),
   ('Mark', 'Twain', '1835-11-30', 'American');
   
   INSERT INTO books (title, genre, publication_year, author_id) VALUES
   ('1984', 'Dystopian', 1949, (SELECT author_id FROM authors WHERE first_name = 'George' AND last_name = 'Orwell')),
   ('Pride and Prejudice', 'Romance', 1813, (SELECT author_id FROM authors WHERE first_name = 'Jane' AND last_name = 'Austen')),
   ('The Adventures of Huckleberry Finn', 'Adventure', 1884, (SELECT author_id FROM authors WHERE first_name = 'Mark' AND last_name = 'Twain'));
   
   INSERT INTO book_availability (book_id, available, last_checkout_date) VALUES
   ((SELECT book_id FROM books WHERE title = '1984'), TRUE, '2024-09-10'),
   ((SELECT book_id FROM books WHERE title = 'Pride and Prejudice'), FALSE, '2024-08-25'),
   ((SELECT book_id FROM books WHERE title = 'The Adventures of Huckleberry Finn'), TRUE, '2024-09-15');

3. Write SELECT statements to do the following:

   • Retrieve the full names of all authors

     SELECT first_name || ' ' || last_name AS full_name FROM authors;
   • Retrieve the titles of all available books

     SELECT title FROM books
     JOIN book_availability ON books.book_id = book_availability.book_id
     WHERE available = TRUE;
   • Retrieve the titles of all books by "George Orwell"

     SELECT title FROM books
     JOIN authors ON books.author_id = authors.author_id
     WHERE authors.first_name = 'George' AND authors.last_name = 'Orwell';
4. Update the availability of "Pride and Prejudice" to "Yes"

   UPDATE book_availability
   SET available = TRUE
   WHERE book_id = (SELECT book_id FROM books WHERE title = 'Pride and Prejudice');
5. Remove "The Adventures of Huckleberry Finn" from the available books list

   UPDATE book_availability
   SET available = FALSE
   WHERE book_id = (SELECT book_id FROM books WHERE title = 'The Adventures of Huckleberry Finn');

Requirements

• Your database must store the following employee data:

• Your database must store the following department data:

• Your database must store the following salary data:

Tasks

1. Create the tables to store all of this data

   CREATE TABLE employees (
       employee_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       email TEXT,
       hire_date DATE
   );
   
   CREATE TABLE departments (
       department_id SERIAL PRIMARY KEY,
       department_name TEXT,
       department_head TEXT
   );
   
   CREATE TABLE salaries (
       employee_id INT REFERENCES employees(employee_id),
       department_id INT REFERENCES departments(department_id),
       salary DECIMAL(10, 2),
       PRIMARY KEY (employee_id, department_id)  -- Composite primary key
   );
2. Insert the data provided into the tables

   INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
   ('Sarah', 'Connor', 'sarah.connor@example.com', '2023-01-05'),
   ('John', 'Doe', 'john.doe@example.com', '2022-03-12'),
   ('Jane', 'Smith', 'jane.smith@example.com', '2023-05-15');
   
   INSERT INTO departments (department_name, department_head) VALUES
   ('Human Resources', 'Sarah Connor'),
   ('Engineering', 'John Doe'),
   ('Marketing', 'Jane Smith');
   
   INSERT INTO salaries (employee_id, department_id, salary) VALUES
   (1, 1, 75000),
   (2, 2, 85000), 
   (3, 3, 70000);

3. Write SELECT statements to do the following:

   • Retrieve the full names of all employees

     SELECT first_name || ' ' || last_name AS full_name FROM employees;
   • Retrieve the department names which have an employee with a salary greated than $70,000

     SELECT DISTINCT department_name
     FROM departments
     JOIN salaries ON departments.department_id = salaries.department_id
     WHERE salary > 70000;
   • Retrieve the department head of "Engineering"

     SELECT department_head FROM departments WHERE department_name = 'Engineering';
4. Increase Jane Smith's salary to $75,000

   UPDATE salaries
   SET salary = 75000
   WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith');
5. Remove "John Doe" from the "Engineering" department

   DELETE FROM salaries WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'John' AND last_name = 'Doe');
   DELETE FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

Requirements

• Your database must store the following customer data:

• Your database must store the following product data:

• Your database must store the following order data:

• Your database must store the following order details data:

Tasks

1. Create the tables to store all of this data

   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       email TEXT,
       address TEXT,
       city TEXT,
       country TEXT
   );
   
   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name TEXT,
       description TEXT,
       price DECIMAL(10, 2),
       stock_quantity INT
   );
   
   CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       customer_id INT REFERENCES customers(customer_id),
       order_date DATE,
       total_amount DECIMAL(10, 2)
   );
   
   CREATE TABLE order_details (
       order_detail_id SERIAL PRIMARY KEY,
       order_id INT REFERENCES orders(order_id),
       product_id INT REFERENCES products(product_id),
       quantity INT,
       line_total DECIMAL(10, 2)
   );
2. Insert the data provided into the tables

   INSERT INTO customers (first_name, last_name, email, address, city, country) VALUES
   ('Emily', 'Brown', 'emily.brown@example.com', '1234 Elm Street', 'New York', 'USA'),
   ('Michael', 'Green', 'michael.green@example.com', '5678 Maple Avenue', 'Los Angeles', 'USA'),
   ('Sarah', 'White', 'sarah.white@example.com', '246 Oak Street', 'London', 'UK');
   
   INSERT INTO products (product_name, description, price, stock_quantity) VALUES
   ('Laptop', '15" gaming laptop', 1500, 20),
   ('Smartphone', 'Latest model', 800, 50),
   ('Headphones', 'Wireless headset', 100, 100);
   
   INSERT INTO orders (customer_id, order_date, total_amount) VALUES
   (1, '2024-09-01', 2400),
   (2, '2024-09-05', 800);
   
   INSERT INTO order_details (order_id, product_id, quantity, line_total) VALUES
   (1, 1, 1, 1500),
   (1, 3, 3, 300),
   (2, 2, 1, 800);

3. Write SELECT statements to do the following:

   • Retrieve the full names of all customers

     SELECT first_name || ' ' || last_name AS full_name FROM customers;
   • Retrieve the products purchased by "Emily Brown"

     SELECT product_name FROM products
     JOIN order_details ON products.product_id = order_details.product_id
     JOIN orders ON order_details.order_id = orders.order_id
     JOIN customers ON orders.customer_id = customers.customer_id
     WHERE customers.first_name = 'Emily' AND customers.last_name = 'Brown';
   • Retrieve all customers who ordered a product costing more than $1,000

     SELECT first_name, last_name FROM customers
     JOIN orders ON customers.customer_id = orders.customer_id
     JOIN order_details ON orders.order_id = order_details.order_id
     JOIN products ON order_details.product_id = products.product_id
     WHERE products.price > 1000;
4. Update the stock quantity for "Headphones" after Emily Brown's purchase

   UPDATE products
   SET stock_quantity = stock_quantity - 3
   WHERE product_name = 'Headphones';
5. Cancel Michael Green’s order by deleting all order details and the corresponding order

   DELETE FROM order_details WHERE order_id = (SELECT order_id FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE first_name = 'Michael' AND last_name = 'Green'));
   
   DELETE FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE first_name = 'Michael' AND last_name = 'Green');

Requirements

• Your database must store the following patient data:

• Your database must store the following doctor data:

• Your database must store the following treatment data:

• Your database must store the following prescription data:

Tasks

1. Create the tables to store all of this data

   CREATE TABLE patients (
       patient_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       date_of_birth DATE,
       address TEXT,
       phone_number TEXT
   );
   
   CREATE TABLE doctors (
       doctor_id SERIAL PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       specialty TEXT,
       email TEXT
   );
   
   CREATE TABLE treatments (
       treatment_id SERIAL PRIMARY KEY,
       patient_id INT REFERENCES patients(patient_id),
       doctor_id INT REFERENCES doctors(doctor_id),
       treatment TEXT,
       treatment_date DATE
   );
   
   CREATE TABLE prescriptions (
       prescription_id SERIAL PRIMARY KEY,
       patient_id INT REFERENCES patients(patient_id),
       doctor_id INT REFERENCES doctors(doctor_id),
       medication TEXT,
       dosage TEXT,
       prescription_date DATE
   );
2. Insert the data provided into the tables

   INSERT INTO patients (first_name, last_name, date_of_birth, address, phone_number) VALUES
   ('John', 'Miller', '1985-05-15', '789 Pine Street', '555-1234'),
   ('Laura', 'Turner', '1992-02-28', '987 Cedar Lane', '555-5678'),
   ('Daniel', 'Lewis', '1975-07-08', '456 Oak Avenue', '555-8765');
   
   INSERT INTO doctors (first_name, last_name, specialty, email) VALUES
   ('David', 'Adams', 'Cardiologist', 'david.adams@hospital.com'),
   ('Susan', 'Martinez', 'Neurologist', 'susan.martinez@hospital.com'),
   ('Emily', 'Johnson', 'General Practitioner', 'emily.johnson@hospital.com');
   
   INSERT INTO treatments (patient_id, doctor_id, treatment, treatment_date) VALUES
   (1, 1, 'Heart Surgery', '2024-08-15'),
   (2, 3, 'Flu Treatment', '2024-09-10'),
   (3, 2, 'Brain Scan', '2024-09-20');
   
   INSERT INTO prescriptions (patient_id, doctor_id, medication, dosage, prescription_date) VALUES
   (1, 1, 'Beta Blockers', '50mg', '2024-08-20'),
   (2, 3, 'Ibuprofen', '200mg', '2024-09-11'),
   (3, 2, 'Antidepressants', '10mg', '2024-09-22');

3. Write SELECT statements to do the following:

   • Retrieve the full names of all doctors

     SELECT first_name || ' ' || last_name AS full_name FROM doctors;
   • Retrieve all patients treated by "Emily Johnson"

     SELECT first_name, last_name FROM patients
     JOIN treatments ON patients.patient_id = treatments.patient_id
     JOIN doctors ON treatments.doctor_id = doctors.doctor_id
     WHERE doctors.first_name = 'Emily' AND doctors.last_name = 'Johnson';
   • Retrieve all prescriptions for "John Miller"

     SELECT medication, dosage, prescription_date FROM prescriptions
     JOIN patients ON prescriptions.patient_id = patients.patient_id
     WHERE patients.first_name = 'John' AND patients.last_name = 'Miller';
4. Update the dosage for Laura Turner’s "Ibuprofen" prescription to 400mg

   UPDATE prescriptions
   SET dosage = '400mg'
   WHERE patient_id = (SELECT patient_id FROM patients WHERE first_name = 'Laura' AND last_name = 'Turner')
   AND medication = 'Ibuprofen';
5. Remove Daniel Lewis' Brain Scan treatment from the system

   DELETE FROM treatments
   WHERE patient_id = (SELECT patient_id FROM patients WHERE first_name = 'Daniel' AND last_name = 'Lewis')
   AND treatment = 'Brain Scan';