week4lab

  --Import the librarybooks.xlsx (librarybooks.xlsx) file into your database.
--Analyze the data and draw out a diagram of the table structures that you will need.
--Create the tables in sql to match your diagram
--Create queries to populate your tables from the librarybooks.xlsx table that you imported
--Answer the following questions:
--How many books are there total in the library?
--How many books are currently checked out?
--How many students are there?
--How many books are there in each category?
--How many books are checked out by category?
--How many books will be returned in december?
--How many books are missing authors?
--use week4lab
drop table Categrory
go
drop table Books 
go 
drop table Students
go 
drop table Library
go 


create table Category(categoryid int identity (10,1), 
					  category varchar(20))

insert into category(categoryid) values (100)
insert into category(category) select distinct categoryid from librarybooks2

create table Books ( BooksID int identity (1,1),
					 Author varchar(50) , 
					 Categoryid int, 
					 DateOfPublication datetime , 
					 ISBN int)
insert into Books (Author, Categoryid, DateOfPublication, ISBN) 
				select distinct Author, 
				(select category.categoryid) , 
				(select distinct DateOfPublication), 
				(select distinct ISBN) 
				from librarybooks2
				join category on librarybooks2.Categoryid= category.category


create table Students (
					   StudentName varchar(50),
					   StudentAddress varchar(200),
					   Age int,
					   studentID int identity(100,1))
insert into Students (studentID) values (100)
insert into Students ( StudentName, StudentAddress, Age)
			select distinct StudentName,
			(select distinct StudentAddress),
			(select distinct Age) from librarybooks2 where StudentName <> ''


create table library (returndate datetime, 
					  borrowdate datetime, 
					  booksid bigint, 
					  studentid bigint)
insert into library(returndate, borrowdate, booksid, studentid)
select distinct returndate, (select distinct borrowdate), (select books.BooksID), (select students.studentid)
from librarybooks2
join books
on librarybooks2.isbn = books.isbn
join students
on librarybooks2.StudentName = students.StudentName where StudentName <> ' '

--How many books are there total in the library?
select count(booksid) from books

--Which books are currently checked out?
select library.booksid, books.isbn from library
join books
on books.BooksID = library.BooksID
where returndate <> getdate()
group by library.BooksId, books.isbn

--How many students are there?
select count(studentid)  from students

--How many books are in each category?
select count(BooksID) as 'books', (select distinct category.categoryid) as 'category' from books
join category
on category.categoryid = books.categoryid
group by category.categoryid

--How many books are checked out by category?
select count(books.BooksID) as 'books that are checked out', (select distinct category.categoryid) as 'category'
from books
join category
on category.categoryid = books.categoryid
join library
on library.booksid = books.BooksID
where library.returndate <> getdate()
group by category.categoryid

--Which books will be returned in December?
select books.BooksID, books.isbn
from books
join library
on library.booksid = books.BooksID
where month(library.returndate) = 12
group by books.BooksID, books.isbn

--Which books are missing authors?
select BooksID, isbn from books
where books.author = ' '
		

--Import the librarybooks.xlsx (librarybooks.xlsx) file into your database.
--Analyze the data and draw out a diagram of the table structures that you will need.
--Create the tables in sql to match your diagram
--Create queries to populate your tables from the librarybooks.xlsx table that you imported
--Answer the following questions:
--How many books are there total in the library?
--How many books are currently checked out?
--How many students are there?
--How many books are there in each category?
--How many books are checked out by category?
--How many books will be returned in december?
--How many books are missing authors?
use week4lab
drop table Category
go
drop table Books
go
drop table Students
go
drop table Library
go

create table Category (categoryid int identity (10,1),
category varchar(20))

insert into category(category) select distinct categoryid from librarybooks2

create table Books ( BooksID int identity (1,1),
Author varchar(50) ,
Categoryid int,
DateOfPublication datetime ,
ISBN int)
insert into Books (Author, Categoryid, DateOfPublication, ISBN)
select distinct Author,
(select category.categoryid) ,
(select distinct DateOfPublication),
(select distinct ISBN)
from librarybooks2
join category on librarybooks2.Categoryid= category.category
update books set Author = null where Author= ' '

create table Students (
StudentName varchar(50),
StudentAddress varchar(200),
dateofbirth datetime,
studentID int identity(100,1))

insert into Students ( StudentName, StudentAddress, dateofbirth)
select distinct StudentName,
(select distinct StudentAddress),
(select distinct DateOfBirth) from librarybooks2 where librarybooks2.StudentName <> ''

create table library (returndate datetime,
borrowdate datetime,
booksid bigint,
studentid bigint)
insert into library(returndate, borrowdate, booksid, studentid)
select distinct returndate, (select distinct borrowdate), (select books.BooksID), (select students.studentid)
from librarybooks2
join books
on librarybooks2.isbn = books.isbn
join students
on librarybooks2.StudentName = students.StudentName where librarybooks2.StudentName <> ' '

select * from librarybooks2

--How many books are there total in the library?
select count(booksid) from books

--Which books are currently checked out?
select library.booksid, books.isbn from library
join books
on books.BooksID = library.BooksID
where returndate <> getdate()

--How many students are there?
select count(studentid) from students

--How many books are in each category?
select count(BooksID) as 'books', (select distinct category.categoryid) as 'category' from books
join category
on category.categoryid = books.categoryid
group by category.categoryid

--How many books are checked out by category?
select count(books.BooksID) as 'books that are checked out', (select distinct category.categoryid) as 'category'
from books
join category
on category.categoryid = books.categoryid
join library
on library.booksid = books.BooksID
where library.returndate > getdate()
group by category.categoryid

--Which books will be returned in December?
select books.BooksID, books.isbn
from books
join library
on library.booksid = books.BooksID
where month(library.returndate) = 12

--Which books are missing authors?
select BooksID, isbn from books
where books.author is null