SQL Queries Library DB example - Srinivas Narasegouda

Create the following tables with properly specifying Primary keys, foreign keys and solve the following queries.
BRANCH (Branchid, Branchname, HOD)
STUDENT (USN, Name, Address, Branchid, sem)
BOOK (Bookid, Bookname, Authorid, Publisher, Branchid)
AUTHOR (Authorid, Authomame, Country, age)
BORROW (USN, Bookid, Borrowed_Date)

Perform the following:
Viewing all databases, creating a Database, viewing all Tables in a Database,
Creating Tables (With and Without Constraints), Inserting/Updating/Deleting Records in a Table,
Saving (Commit) and Undoing (Rollback)

Viewing all databases

SELECT name
FROM sys.databases;

Creating a database

CREATE DATABASE LibraryDB;
GO

Viewing all tables in a database

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

Creating Tables (With and Without Constraints)

USE LibraryDB;
GO
CREATE TABLE BRANCH
(
    BranchID INT PRIMARY KEY,
    BranchName VARCHAR(30),
    HOD VARCHAR(30)
);

USE LibraryDB;
GO
CREATE TABLE AUTHOR
(
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(30),
    Country VARCHAR(30),
    Age INT
);

USE LibraryDB;
GO
CREATE TABLE STUDENT
(
    USN VARCHAR(15) PRIMARY KEY,
    Name VARCHAR(30),
    Address VARCHAR(50),
    BranchID INT,
    Sem INT,

    FOREIGN KEY(BranchID)
    REFERENCES BRANCH(BranchID)
);

USE LibraryDB;
GO
CREATE TABLE BOOK
(
    BookID INT PRIMARY KEY,
    BookName VARCHAR(50),
    AuthorID INT,
    Publisher VARCHAR(30),
    BranchID INT,

    FOREIGN KEY(AuthorID)
    REFERENCES AUTHOR(AuthorID),

    FOREIGN KEY(BranchID)
    REFERENCES BRANCH(BranchID)
);

USE LibraryDB;
GO
CREATE TABLE BORROW
(
    USN VARCHAR(15),
    BookID INT,
    Borrowed_Date DATE,

    PRIMARY KEY(USN,BookID),

    FOREIGN KEY(USN)
    REFERENCES STUDENT(USN),

    FOREIGN KEY(BookID)
    REFERENCES BOOK(BookID)
);

Inserting values into the table:

USE LibraryDB;
GO
INSERT INTO BRANCH VALUES
(1,'BCA','Dr. Kumar'),
(2,'BCom','Dr. Rao'),
(3,'BSc','Dr. Sharma');
select * from BRANCH;

USE LibraryDB;
GO
INSERT INTO AUTHOR VALUES
(101,'James','USA',50),
(102,'Ramesh','India',45),
(103,'John','UK',60);

select * from AUTHOR;

USE LibraryDB;
GO
INSERT INTO STUDENT VALUES
('BCA001','Rahul','Bangalore',1,2),
('BCA002','Anita','Mysore',1,2),
('BCA003','Ajay','Hubli',1,4),
('BCM001','Deepa','Bangalore',2,2);

Select * from student;

USE LibraryDB;
GO
INSERT INTO BOOK VALUES
(1,'Python',101,'Pearson',1),
(2,'DBMS',102,'McGraw Hill',1),
(3,'Java',101,'Pearson',1),
(4,'C Programming',103,'Oxford',2);

Select * from book;

USE LibraryDB;
GO

INSERT INTO BORROW VALUES
('BCA001',1,'2025-01-10'),
('BCA001',2,'2025-01-15'),
('BCA001',3,'2025-02-01'),
('BCA002',2,'2025-02-20'),
('BCM001',4,'2025-02-15');

select * from borrow;

Updating/Deleting Records in a Table

USE LibraryDB;
GO
select * from student;
UPDATE STUDENT
SET Address='Tumkur'
WHERE USN='BCA003';
select * from student;

select * from borrow;
DELETE FROM BORROW
WHERE BookID=4;

select * from borrow;

Saving (Commit) and Undoing (Rollback)

USE LibraryDB;
GO

select * from BOOK;
BEGIN TRANSACTION;
UPDATE BOOK
SET Publisher='Wiley'
WHERE BookID=1;
COMMIT;
select * from BOOK;

select * from borrow;
BEGIN TRANSACTION;
DELETE FROM borrow
WHERE USN='BCM001';
ROLLBACK;
select * from borrow;