SQL Queries Library DB example

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)

  1. 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;
  1. a. List the details of Students who are all studying in 2nd sem BCA.
    b. List the students who are not borrowed any books.

a. List the details of Students who are all studying in 2nd sem BCA.

SELECT S.*
FROM STUDENT S
JOIN BRANCH B ON S.BranchID = B.BranchID
WHERE S.Sem = 2
  AND B.BranchName = 'BCA';

b. List the students who are not borrowed any books.

SELECT S.*
FROM STUDENT S
LEFT JOIN BORROW B ON S.USN = B.USN
WHERE B.USN IS NULL;

3. a. Display the USN, Student name, Branch_name, Book_name, Author_name, Books Borrowed Date of 2nd sem BCA Students who borrowed books.
b. Display the number of books written by each Author.

a. Display the USN, Student name, Branch_name, Book_name, Author_name, Books Borrowed Date of 2nd sem BCA Students who borrowed books.

SELECT S.USN,
       S.Name AS Student_Name,
       BR.BranchName AS Branch_Name,
       BK.BookName AS Book_Name,
       A.AuthorName AS Author_Name,
       BO.Borrowed_Date
FROM STUDENT S
JOIN BRANCH BR ON S.BranchID = BR.BranchID
JOIN BORROW BO ON S.USN = BO.USN
JOIN BOOK BK ON BO.BookID = BK.BookID
JOIN AUTHOR A ON BK.AuthorID = A.AuthorID
WHERE S.Sem = 2
  AND BR.BranchName = 'BCA';

b. Display the number of books written by each Author.

SELECT A.AuthorID,
       A.AuthorName,
       COUNT(BK.BookID) AS Number_of_Books
FROM AUTHOR A
LEFT JOIN BOOK BK ON A.AuthorID = BK.AuthorID
GROUP BY A.AuthorID, A.AuthorName;
  1. a. Display the student details who borrowed more than two books.
    b. Display the student details who borrowed books of more than one Author.

a. Display the student details who borrowed more than two books.

SELECT S.USN,
       S.Name,
       S.Address,
       S.BranchID,
       S.Sem
FROM STUDENT S
JOIN BORROW B ON S.USN = B.USN
GROUP BY S.USN, S.Name, S.Address, S.BranchID, S.Sem
HAVING COUNT(B.BookID) > 2;

b. Display the student details who borrowed books of more than one Author.

SELECT S.USN,
       S.Name,
       S.Address,
       S.BranchID,
       S.Sem
FROM STUDENT S
JOIN BORROW BO ON S.USN = BO.USN
JOIN BOOK BK ON BO.BookID = BK.BookID
GROUP BY S.USN, S.Name, S.Address, S.BranchID, S.Sem
HAVING COUNT(DISTINCT BK.AuthorID) > 1;
  1. a. Display the Book names in descending order of their names.
    b. List the details of students who borrowed the books which are all published by the same publisher.

a. Display the Book names in descending order of their names.

SELECT BookName
FROM BOOK
ORDER BY BookName DESC;

b. List the details of students who borrowed the books which are all published by the same publisher.

SELECT DISTINCT S.*
FROM STUDENT S
JOIN BORROW BO ON S.USN = BO.USN
JOIN BOOK BK ON BO.BookID = BK.BookID
WHERE S.USN IN
(
    SELECT BO2.USN
    FROM BORROW BO2
    JOIN BOOK BK2 ON BO2.BookID = BK2.BookID
    GROUP BY BO2.USN, BK2.Publisher
    HAVING COUNT(*) >= 1
);

Consider the following schema:
STUDENT (USN, name, date of birth, branch, mark1, mark2, mark3, total, GPA)

  1. Perform the following:
    Creating Tables (With and Without Constraints), Inserting/Updating/Deleting Records in a Table, Saving (Commit) and Undoing (rollback)

Creating Table:

CREATE TABLE STUDENT_Marks
(
    USN VARCHAR(15) PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Date_of_Birth DATE,
    Branch VARCHAR(20),
    Mark1 INT CHECK (Mark1 >= 0 AND Mark1 <= 100),
    Mark2 INT CHECK (Mark2 >= 0 AND Mark2 <= 100),
    Mark3 INT CHECK (Mark3 >= 0 AND Mark3 <= 100),
    Total INT,
    GPA DECIMAL(4,2)
);

Inserting records into the table:

INSERT INTO STUDENT_MARKS
VALUES ('1BC01', 'Ravi', '2005-06-15', 'BCA', 85, 90, 80, 255, 8.50);

INSERT INTO STUDENT_MARKS
VALUES ('1BC02', 'Anita', '2004-03-20', 'BCA', 90, 95, 88, 273, 9.10);

INSERT INTO STUDENT_MARKS
VALUES ('1CS01', 'Kiran', '2005-09-10', 'CSE', 75, 82, 79, 236, 7.80);

INSERT INTO STUDENT_MARKS
VALUES ('1CS02', 'Priya', '2003-12-25', 'CSE', 88, 91, 93, 272, 9.00);

Select * from STUDENT_MARKS

Updating a record in a table:

UPDATE STUDENT_MARKS
SET GPA = 8.75
WHERE USN = '1BC01';

Select * from STUDENT_MARKS

Delete and COMMIT

Select * from STUDENT_MARKS
BEGIN TRANSACTION;
DELETE FROM STUDENT_MARKS
WHERE USN = '1CS02';
COMMIT;
Select * from STUDENT_MARKS

Update and ROLLBACK

Select * from STUDENT_MARKS
BEGIN TRANSACTION;
UPDATE STUDENT_MARKS
SET GPA = 10.00
WHERE USN = '1BC01';
ROLLBACK;
Select * from STUDENT_MARKS
  1. a. Find the GPA score of all the students.
    b. Find the students who born on a particular year of birth from the date of birth column.

a. Find the GPA score of all the students.

SELECT USN, Name, GPA
FROM STUDENT_MARKS;

b. Find the students who born on a particular year of birth from the date of birth column.

SELECT *
FROM STUDENT_MARKS
WHERE YEAR(Date_of_Birth) = 2005;

SELECT *
FROM STUDENT_MARKS
WHERE Date_of_Birth >= '2005-01-01'
  AND Date_of_Birth < '2006-01-01';
  1. a. List the students who are studying in a particular branch of study.
    b. Find the maximum GPA score·of the student branch-wise.

a. List the students who are studying in a particular branch of study.

SELECT *
FROM STUDENT_MARKS
WHERE Branch = 'BCA';

b. Find the maximum GPA score·of the student branch-wise.

SELECT Branch,
       MAX(GPA) AS Maximum_GPA
FROM STUDENT_MARKS
GROUP BY Branch;