What is an attribute in SQL?

In the context of SQL, the attributes refer to a column in a particular table. It helps in representing a specific piece of the data which are stored for each record in the table. Each attribute has a data type which would help in defining the kind of data it can hold, such as Integers, strings, dates, etc.

In the context of books table l, here are the attributes are as follows:-

Title which contains the title of the books.

ISBN refers to the international standards book number, which is a unique identifier of the Book.

Publication date refers to the date of the book when it was published.

Price refers to the price of the book.

AuthorID refers to the foreign key that links to the author of the book.

Here is how you can define the book table with these attributes in SQL:-

Create the Authors table to store author information

CREATE TABLE Authors (

    AuthorID INT PRIMARY KEY,

    FirstName VARCHAR(100) NOT NULL,

    LastName VARCHAR(100) NOT NULL

);

Create the Books table with the specified attributes

CREATE TABLE Books (

    BookID INT PRIMARY KEY,               -- Primary key for the Books table

    Title VARCHAR(255) NOT NULL,          -- Title of the book

    ISBN CHAR(13) UNIQUE NOT NULL,        -- Unique ISBN for the book

    PublicationDate DATE NOT NULL,        -- Publication date of the book

    Price DECIMAL(10, 2) NOT NULL,        -- Price of the book

    AuthorID INT,                         -- Foreign key referencing the author

    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)  -- Enforcing referential integrity

);

Example of inserting data into the Authors table

INSERT INTO Authors (AuthorID, FirstName, LastName)

VALUES 

(1, ‘John’, ‘Doe’),

(2, ‘Jane’, ‘Smith’);

Example of inserting data into the Books table

INSERT INTO Books (BookID, Title, ISBN, PublicationDate, Price, AuthorID)

VALUES 

(1, ‘SQL for Beginners’, ‘9781234567890’, ‘2023-01-15’, 29.99, 1),

(2, ‘Advanced SQL Queries’, ‘9780987654321’, ‘2023-05-20’, 39.99, 2);

Here is also a java based coding given of how you can manage book and author:-

Import java.sql.Connection;

Import java.sql.DriverManager;

Import java.sql.PreparedStatement;

Import java.sql.ResultSet;

Import java.sql.SQLException;

Import java.sql.Statement;

Public class BookstoreManager {

    Private static final String DB_URL = “jdbc:mysql://localhost:3306/bookstore”;

    Private static final String USER = “root”;

    Private static final String PASS = “password”;

    Public static void main(String[] args) {

        Try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {

            createTables(conn);

            insertSampleData(conn);

            queryBooks(conn);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    Private static void createTables(Connection conn) throws SQLException {

        String createAuthorsTable = “CREATE TABLE IF NOT EXISTS Authors (“ +

                                    “AuthorID INT PRIMARY KEY AUTO_INCREMENT, “ +

                                    “FirstName VARCHAR(100) NOT NULL, “ +

                                    “LastName VARCHAR(100) NOT NULL)”;

        String createBooksTable = “CREATE TABLE IF NOT EXISTS Books (“ +

                                  “BookID INT PRIMARY KEY AUTO_INCREMENT, “ +

                                  “Title VARCHAR(255) NOT NULL, “ +

                                  “ISBN CHAR(13) UNIQUE NOT NULL, “ +

                                  “PublicationDate DATE NOT NULL, “ +

                                  “Price DECIMAL(10, 2) NOT NULL, “ +

                                  “AuthorID INT, “ +

                                  “FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID))”;

        Try (Statement stmt = conn.createStatement()) {

            Stmt.executeUpdate(createAuthorsTable);

            Stmt.executeUpdate(createBooksTable);

            System.out.println(“Tables created successfully.”);

        }

    }

    Private static void insertSampleData(Connection conn) throws SQLException {

        String insertAuthors = “INSERT INTO Authors (FirstName, LastName) VALUES (?, ?)”;

        String insertBooks = “INSERT INTO Books (Title, ISBN, PublicationDate, Price, AuthorID) VALUES (?, ?, ?, ?, ?)”;

        Try (PreparedStatement pstmtAuthors = conn.prepareStatement(insertAuthors, Statement.RETURN_GENERATED_KEYS);

             PreparedStatement pstmtBooks = conn.prepareStatement(insertBooks)) {

            // Insert authors

            pstmtAuthors.setString(1, “John”);

            pstmtAuthors.setString(2, “Doe”);

            pstmtAuthors.executeUpdate();

            ResultSet rs = pstmtAuthors.getGeneratedKeys();

            Rs.next();

            Int author1ID = rs.getInt(1);

            pstmtAuthors.setString(1, “Jane”);

            pstmtAuthors.setString(2, “Smith”);

            pstmtAuthors.executeUpdate();

            rs = pstmtAuthors.getGeneratedKeys();

            rs.next();

            int author2ID = rs.getInt(1);

            // Insert books

            pstmtBooks.setString(1, “SQL for Beginners”);

            pstmtBooks.setString(2, “9781234567890”);

            pstmtBooks.setDate(3, java.sql.Date.valueOf(“2023-01-15”));

            pstmtBooks.setBigDecimal(4, new java.math.BigDecimal(“29.99”));

            pstmtBooks.setInt(5, author1ID);

            pstmtBooks.executeUpdate();

            pstmtBooks.setString(1, “Advanced SQL Queries”);

            pstmtBooks.setString(2, “9780987654321”);

            pstmtBooks.setDate(3, java.sql.Date.valueOf(“2023-05-20”));

            pstmtBooks.setBigDecimal(4, new java.math.BigDecimal(“39.99”));

            pstmtBooks.setInt(5, author2ID);

            pstmtBooks.executeUpdate();

            System.out.println(“Sample data inserted successfully.”);

        }

    }

    Private static void queryBooks(Connection conn) throws SQLException {

        String query = “SELECT b.BookID, b.Title, b.ISBN, b.PublicationDate, b.Price, a.FirstName, a.LastName “ +

                       “FROM Books b JOIN Authors a ON b.AuthorID = a.AuthorID”;

        Try (Statement stmt = conn.createStatement();

             ResultSet rs = stmt.executeQuery(query)) {

            While (rs.next()) {

                Int bookID = rs.getInt(“BookID”);

                String title = rs.getString(“Title”);

                String isbn = rs.getString(“ISBN”);

                Java.sql.Date publicationDate = rs.getDate(“PublicationDate”);

                Java.math.BigDecimal price = rs.getBigDecimal(“Price”);

                String authorFirstName = rs.getString(“FirstName”);

                String authorLastName = rs.getString(“LastName”);

                System.out.println(“Book ID: “ + bookID);

                System.out.println(“Title: “ + title);

                System.out.println(“ISBN: “ + isbn);

                System.out.println(“Publication Date: “ + publicationDate);

                System.out.println(“Price: “ + price);

                System.out.println(“Author: “ + authorFirstName + “ “ + authorLastName);

                System.out.println(“-----“);

            }

        }

    }

}