Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    Basis Data Tugas SQL

    Uploaded by

    kyuda812
    jtd

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Basis Data Tugas SQL

    Uploaded by

    kyuda812
    4 views3 pages
    jtd

    Original Title

    basis data tugas sql

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    jtd

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    4 views3 pages

    Basis Data Tugas SQL

    Uploaded by

    kyuda812
    jtd

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 3

    Nama : Yuda Kurniawan

    Kelas :E
    Nim : 2350081143
    Tugas basis data SQL

    Jawaban
    1. Membuat tabel Student
    CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    major VARCHAR(50),
    enrollment_year INT CHECK (enrollment_year >= 2000)
    );

    2. Membuat table Books


    CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100),
    published_year INT,
    genre VARCHAR(50)
    );

    3. Buat tabel borrow_records dengan kolom


    CREATE TABLE borrow_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    book_id INT,
    borrow_date DATE DEFAULT CURRENT_DATE,
    return_date DATE,
    FOREIGN KEY (student_id) REFERENCES
    students(student_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
    );
    4. Menambahkan Data ke Tabel students
    INSERT INTO students (student_id, name, major,
    enrollment_year) VALUES
    (1, 'Ahmad Syah', 'Computer Science', 2021),
    (2, 'Rina Anggraini', 'Information System', 2020),
    (3, 'Budi Santoso', 'Mathematics', 2019),
    (4, 'Siti Handayani', 'Computer Science', 2022),
    (5, 'Joko Widodo', 'Law', 2021);
    5. Menambahkan Data ke Tabel books
    INSERT INTO books (book_id, title, author, published_year,
    genre) VALUES
    (1, 'Database Systems', 'Elmasri & Navathe', 2019,
    'Technology'),
    (2, 'Linear Algebra', 'David C. Lay', 2018, 'Mathematics'),
    (3, 'Artificial Intelligence', 'Stuart Russell', 2021,
    'Technology'),
    (4, 'Criminal Law Handbook', 'Paul Bergman', 2017, 'Law'),
    (5, 'Networking Essentials', 'Jeffrey S. Beasley', 2020,
    'Technology'),
    (6, 'Basic Econometrics', 'Damodar Gujarati', 2016,
    'Economics');
    6. Menambahkan Data ke Tabel borrow_records
    INSERT INTO borrow_records (student_id, book_id, borrow_date,
    return_date) VALUES
    (1, 1, '2023-01-15', '2023-01-30'),
    (2, 2, '2023-02-20', NULL),
    (3, 4, '2023-03-10', '2023-03-20'),
    (4, 1, '2023-04-25', NULL);
    7. Menambahkan Kolom email pada Tabel students dan Memasukkan Data Email
    ALTER TABLE students
    ADD email VARCHAR(100) NOT NULL;

    UPDATE students SET email = '[email protected]' WHERE


    student_id = 1;
    UPDATE students SET email = '[email protected]' WHERE
    student_id = 2;
    UPDATE students SET email = '[email protected]' WHERE
    student_id = 3;
    UPDATE students SET email = '[email protected]' WHERE
    student_id = 4;
    UPDATE students SET email = '[email protected]' WHERE
    student_id = 5;
    8. Query JOIN untuk Menampilkan Nama Mahasiswa, Jurusan, dan Judul Buku yang
    Dipinjam
    SELECT s.name AS student_name, s.major, b.title AS book_title
    FROM borrow_records br
    JOIN students s ON br.student_id = s.student_id
    JOIN books b ON br.book_id = b.book_id;
    9. Transaksi SQL untuk Menambahkan Data Peminjaman dan ROLLBACK
    BEGIN TRANSACTION;

    INSERT INTO borrow_records (student_id, book_id, borrow_date)


    VALUES ((SELECT student_id FROM students WHERE name = 'Joko
    Widodo'),
    (SELECT book_id FROM books WHERE title = 'Networking
    Essentials'),
    '2023-11-05');

    ROLLBACK;
    10. Menampilkan Jumlah Peminjaman untuk Setiap Mahasiswa, Urut Berdasarkan
    Jumlah Peminjaman
    SELECT s.name AS student_name, COUNT(br.record_id) AS
    borrow_count
    FROM borrow_records br
    JOIN students s ON br.student_id = s.student_id
    GROUP BY s.student_id, s.name
    ORDER BY borrow_count DESC;

    You might also like