Mastering Database Management

0 of 78 lessons complete (0%)

Basic SQL Commands with CRUD Operations

SELECT, INSERT, UPDATE, DELETE

You don’t have access to this lesson

Please register or sign in to access the course content.

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.