SQL Queries

-- SQL QUERY FOR CREATING THE DATABASE LIBRARY_DB
create database Library_DB

-- SQL QUERY FOR CREATING THE TABLE BRANCH
create table  Branch(
	BranchId int primary key,
	BranchName varchar(100),
	HOD varchar(100)
);

-- SQL QUERY FOR CREATING THE TABLE STUDENT
create table student(
	USN varchar(100) primary key,
	Name varchar(100),
	Address varchar(100),
	BranchId int,
	Sem varchar(20),
	FOREIGN KEY (BranchID) REFERENCES Branch (BranchId) ON DELETE CASCADE,
	FOREIGN KEY (BranchID) REFERENCES Branch (BranchId) ON UPDATE CASCADE
);

-- SQL QUERY FOR CREATING THE TABLE AUTHOR
create table Author(
	AuthorId int primary key,
	AuthorName varchar(100),
	Country varchar(50),
	Age int
);

-- SQL QUERY FOR CREATING THE TABLE BOOK
create table Book(
	BookId int primary key,
	BookName varchar(100),
	AuthorId int,
	Publisher varchar(100), 
	BranchId int,
	FOREIGN KEY (BranchID) REFERENCES Branch (BranchId) ON DELETE CASCADE,
	FOREIGN KEY (BranchID) REFERENCES Branch (BranchId) ON UPDATE CASCADE,
	FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId) ON DELETE CASCADE,
	FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId) ON UPDATE CASCADE
);


-- SQL QUERY FOR CREATING THE TABLE BORROW
create table Borrow(
	USN varchar(100),
	BookId int,
	Borrowed_Date Date
	FOREIGN KEY (BookID) REFERENCES Book (BookId) ON DELETE CASCADE,
	FOREIGN KEY (BookId) REFERENCES Book (BookId) ON UPDATE CASCADE
);

SELECT * FROM sys.databases;

insert into Branch(Branchid,BranchName,HOD) values
		(1,'BCA','Prof. Gomathi'),
		(2,'Maths','Prof. Sandeep'),
		(3,'Physics','Prof. Mahesh'),
		(4,'Electronics','Prof. Asha'),
		(5,'Psychology','Prof. Elumalai');
		
select * from branch;

insert into student(USN,Name,Address,Branchid,Sem) values
		('1CR01','Harish','Bangalore','1','2'),
		('1CR02','Bharat','Mysore','2','3'),
		('1CR03','Kiran','Delhi','3','6'),
		('1CR04','Mahi','Chennai','4','7'),
		('1CR05','Krishna','Hubli','5','4');
		
select * from student;

insert into Author(AuthorId,AuthorName,Country,Age) values
		(123,'Navathe','India',55),
		(124,'Riche','UK',44),
		(125,'Ramakrishna','India',55),
		(126,'Sumitabha','India',38),
		(127,'Dennis','USA',66);

select * from author;

insert into Book(BookId,BookName,AuthorId,Publisher,BranchId) values
		(1111,'C Prog',123,'Pearson',1),
		(2222,'DBMS',124,'Mcgrawhill',2),
		(3333,'OOPS',125,'Sapna',3),
		(4444,'Unix',126,'Subhash',4),
		(5555,'C Prog',127,'Pearson',5),
        (7777,'C++',123,'Pearson',1);
		
select * from Book;

SET DATEFORMAT dmy
insert into Borrow(USN,BookId,Borrowed_Date) values
	('1CR01',2222,'10-01-2000'),
	('1CR01',3333,'05-03-2016'),
	('1CR03',5555,'01-06-2010'),
	('1CR05',2222,'19-05-2000'),
	('1CR02',1111,'22-02-2015'),
    ('1CR02',7777,'22-04-2015');
select * from Borrow;

-- UPDATING A RECORD IN THE TABLE BRANCH
update BRANCH 
set HOD='Prof. Gaurav'
where BRANCHID=1;
SELECT * FROM BRANCH

-- UPDATING A RECORD IN THE TABLE STUDENT
update STUDENT 
set Name='Mahesh'
where USN='1CR04';
SELECT * FROM STUDENT

-- UPDATING A RECORD IN THE TABLE BOOK
update Author 
set AuthorId=128
where AuthorId=123;
Select * from Author
SELECT * FROM BOOK

-- List the details of Students who are all studying in 2nd sem BCA.
select * from student 
	where Sem=2 and 
	Branchid in (select Branchid from branch 
						where BranchName='BCA');

--List the students who have not borrowed any books.
select * from student 
where USN not in (select USN from Borrow);

--Display the number of books written by each Author.
select count(*),Authorid from book group by Authorid;

--Display the USN, Student name, Branch_name, Book_name, Author_name, Books_Borrowed_Date of 2nd sem BCA Students who borrowed books.
select 
		student.USN,student.Name,
		branch.BranchName,
		book.Bookname,
		author.Authorname,
		Borrow.Borrowed_Date
from 
		student,branch,book,author,Borrow 
where 
		student.USN=Borrow.USN and 
		Borrow.Bookid=book.Bookid and 
		book.Authorid=author.Authorid and 
		student.Sem=2 and branch.BranchName='BCA';

-- Display the student details who borrowed more than two books.
select * from student 
where USN in (select USN from Borrow 
						 group by USN 
						 having count(USN)>=2);
 
-- Display the student details who borrowed books of more than one Author.
select * from student s 
where exists (select br.USN from Borrow br 
					join book bk on br.Bookid=bk.Bookid 
					where br.USN=s.USN 
					group by USN 
					having count(distinct Authorid)>1);

-- Display the student details who borrowed books of more than one Author.
select * from student  
where exists (select Borrow.USN from Borrow 
                    join BOOK on Borrow.Bookid=Book.Bookid 
                    where Borrow.USN=student.USN 
                    group by USN 
                    having count(distinct Authorid)>1);

-- Display the Book names in descending order of their names.
select Bookname from book order by Bookname desc;

-- Display the Book names in ascending order of their names.
select Bookname from book order by Bookname asc;

SELECT S.USN, S.NAME, 
    COUNT(BK.PUBLISHER) AS SAME_PUBLISHER   
    FROM STUDENT S, BOOK BK, BORROW BW   
		WHERE S.USN=BW.USN AND BK.Bookid = BW.Bookid    
	GROUP BY S.USN, S.NAME  
	HAVING COUNT(BK.Publisher)>1

-- DELETING A RECORD FROM THE TABLE AUTHOR
delete from author where authorId=124

-- DELETING A RECORD FROM THE TABLE BOOK
delete from book where bookId=1111

-- DELETING A RECORD FROM THE TABLE BORROW
delete from borrow where USN='1CR01'

-- DELETING A RECORD FROM THE TABLE BRANCH
delete from branch where branchId=1

-- DELETING A RECORD FROM THE TABLE STUDENT
delete from student where USN='1CR02'

BEGIN TRANSACTION
    BEGIN TRY 
        insert into Author(AuthorId,AuthorName,Country,Age) values (124,'Riche','UK',44);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH
    
select * from author

BEGIN TRANSACTION
    BEGIN TRY 
		insert into Author(AuthorId,AuthorName,Country,Age) values (123,'Navathe','India',55)
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH
    
select * from author

BEGIN TRANSACTION
    BEGIN TRY 
		insert into Book(BookId,BookName,AuthorId,Publisher,BranchId) values (1111,'C Prog',123,'Pearson',1);
		COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH
 
select * from Book

BEGIN TRANSACTION
    BEGIN TRY 
		insert into Branch(Branchid,BranchName,HOD) values (1,'BCA','Prof. Gomathi');
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH

BEGIN TRANSACTION
    BEGIN TRY 
		insert into Book(BookId,BookName,AuthorId,Publisher,BranchId) values (1111,'C Prog',123,'Pearson',1);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH
  
select * from Book

CREATE TABLE STUDENT_MARKS(
		USN varchar(10) primary key,
		Name Char(15) not null,  
		Date_of_birth Date null,  
		Branch char(15) not null,  
		Marks1 integer not null,  
		Marks2 integer not null,  
		Marks3 integer not null,  
		Total integer,  
		GPA real
);

-- INSERTING INTO A TABLE
set DATEFORMAT dmy;
insert into STUDENT_MARKS values('2JI150MC01','GAURAV','12-07-1999','MCA',75,86,72, null,null)
insert into STUDENT_MARKS values('2JI150CC10','JAI','02-01-1998','COMM',65,96,70, null,null)
insert into STUDENT_MARKS values('2JI150SC09','DEEPAK','12-12-1999','SCIENCE',72,66,59, null,null)
insert into STUDENT_MARKS values('2JI180MC01','ABHI','27-02-1996','ARTS',80,56,72, null,null)
insert into STUDENT_MARKS values('2JI150MC45','SAI','15-08-1999','MCA',95,96,50, null,null)

select * from STUDENT_MARKS

BEGIN TRANSACTION
    BEGIN TRY 
        UPDATE STUDENT_MARKS
		SET total = Marks1 + Marks2 + Marks3;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION;
    END CATCH
  
select * from STUDENT_MARKS

-- Find the GPA score of all the students.
UPDATE STUDENT_MARKS
SET GPA = (Total/ 3.0);
select * from STUDENT_MARKS

-- Find the students who born on a particular year of birth from the date of birth column (When No of _ added to the characters 1999 plus remaining character (-dd-mm).
SELECT USN,NAME,DATE_OF_BIRTH  
FROM STUDENT_MARKS  
WHERE Date_of_birth like  '1999______'

-- List the students who are studying in a particular branch of study.   
SELECT USN, NAME, BRANCH   
FROM STUDENT_MARKS   
WHERE BRANCH = 'MCA'

-- Find the maximum GPA score of the student branch-wise.
SELECT USN,BRANCH,MAX(GPA) AS MAX_GPA 
FROM STUDENT_Marks 
GROUP BY USN,BRANCH
ORDER BY MAX_GPA DESC

Delete from STUDENT_MARKS where USN='2JI150MC45'
Select * from STUDENT_MARKS