Assuming you’ve created a database named LibraryDatabase with tables Authors and Books, let’s perform some operations:
INSERT QUERY
Adds new records to the database.
-- Inserting an author
INSERT INTO Authors (FirstName, LastName, BirthYear)
VALUES ('J.K.', 'Rowling', 1965);
-- Inserting books
INSERT INTO Books (Title, ISBN, PublishYear, AuthorID)
VALUES ('Harry Potter and the Sorcerer''s Stone', '9780439554930', 1997, 1);
INSERT INTO Books (Title, ISBN, PublishYear, AuthorID)
VALUES ('Harry Potter and the Chamber of Secrets', '9780439554893', 1998, 1);
##Add more 10 authors and 10 books as well for practice
SELECT QUERY
Retrieves data from one or more tables.
-- Selecting all authors
SELECT * FROM Authors;
-- Selecting books along with author information
SELECT Books.Title, Authors.FirstName, Authors.LastName
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;
UPDATE QUERY
Modifies existing records in the database.
-- Updating an author's birth year
UPDATE Authors
SET BirthYear = 1967
WHERE LastName = 'Rowling';
-- Updating a book's publish year
UPDATE Books
SET PublishYear = 1999
WHERE Title = 'Harry Potter and the Sorcerer''s Stone';
DELETE QUERY
Removes records from the database.
-- Deleting a book
DELETE FROM Books
WHERE Title = 'Harry Potter and the Chamber of Secrets';
-- Deleting an author and associated books
DELETE FROM Authors
WHERE LastName = 'Rowling';
These SQL queries showcase the syntax for basic operations, including INSERT, SELECT, UPDATE, and DELETE, in the context of the example library database. Feel free to run these queries in your SQL environment to observe the changes in the data. Adjust the values and conditions based on your specific use case.