…·─━☆ 무한코딩세계

SELECT * FROM lendinginfo_view WHERE userid = @UserID

DELETE FROM reservation WHERE idx = @Idx

SELECT count(*) AS counter from reservationinfo_view WHERE bookname = @bookname

SELECT count(*) AS counter from book WHERE isbn = @isbn

DELETE FROM reservation WHERE bookidx = @bookidx and useridx = @useridx

INSERT INTO lending (bookidx, useridx, returnplan, lendday) VALUES(@bookidx, @useridx, @returnplan, @lendday)

... WHERE bookname like @search and type = @type and useridx = @useridx

...ORDER BY returnday DESC

select * from book where typecode = 1

SELECT count(*) AS counter FROM magazineinfos WHERE bookname = @bookname

INSERT INTO magazineinfos (bookname, publisher, author, addyear, addmonth, addisbn, cd, term, bookgroup, position) "
   + "VALUES(@bookname, @publisher, @author, @addyear, @addmonth, @addisbn, @cd, @term, @bookgroup, @position)";

UPDATE magazineinfos SET publisher = @publisher, author = @author, " +
   "addyear = @addyear, addmonth = @addmonth, addisbn = @addisbn, cd = @cd, term = @term, bookgroup = @bookgroup, position = @position WHERE bookname = @bookname";

SELECT count(*) AS counter from book WHERE isbn = @isbn

UPDATE book SET samecount = samecount + 1 WHERE isbn = @isbn

"and NOT type = '토익' ";
"WHERE NOT type = '토익' ";

UPDATE book SET samecount = @samecount WHERE isbn = @isbn

DELETE FROM reservation WHERE bookidx = @bookidx and useridx = @useridx

SELECT * from bookinfo_view WHERE samecount > 1

SELECT A.useridx, B.username + ' ' + Convert(VARCHAR, B.season) + '기' 'user' FROM auth A, userdb B WHERE A.useridx = B.idx AND A.authcode = @AuthCode ORDER BY B.username

INSERT INTO bookrequestdate VALUES(@BeginDay, @EndDay)

SELECT username, season, university, major, userid, idx FROM userinfo_view WHERE auth = '가입대기'

SELECT distinct userid, username, season, university, major, idx FROM userinfo_view WHERE auth <> '가입대기' OR auth IS null

SELECT distinct userid, username, season, university, major, idx FROM userinfo_view WHERE username like @search

INSERT INTO university VALUES(@Univ)

// 석주 --
 public static string connectStr = "server=DGSSMBOOK\\MSSQLSERVER,1433;database=bookdb;uid=bookdb;pwd=bookdb";
 public static string sqlStrNew = "Select count(*) From bookinfo_view where month(purchasedate) = @month and year(purchasedate) = @year";
    public static string sqlStrNewPage = "Select TOP 3 * FROM bookinfo_view WHERE month(purchasedate) = @month and year(purchasedate) = @year and idx NOT IN (SELECT TOP (@iPage * 3) idx FROM bookinfo_view WHERE month(purchasedate) = @month and year(purchasedate) = @year ORDER BY purchasedate ASC) ORDER BY purchasedate ASC";
 public static string sqlStrBest = "Select top 3 * From bookinfo_view order by lendcount desc ";
 public static string sqlStrSelectedBook = "Select * From bookinfo_view where idx = @idx";
 public static string sqlStrInsertBookReview = "Insert into bookreview(bookidx, useridx, username, userseason, info) Values(@bookidx, @useridx, @username, @userseason, @info)";
 public static string sqlstrBookreview = "Select * from bookreview where bookidx = @bookidx order by reviewno desc";
 public static string sqlstrBooklendhistory = "Select * from lendhistoryinfo_view where bookidx = @bookidx order by lendday desc";
    public static string sqlStrInsertReservation = "Insert into reservation (bookidx, useridx) Values(@bookidx, @useridx)";
    public static string sqlStrSearchAll = "Select * From bookinfo_view order by purchasedate desc";
   
    // -- 석주

    //세미나일정
    public readonly static string sqlStrScheduleCount = "SELECT count(*) FROM seminar WHERE year(starttime) = @year and month(starttime) = @month";
    public readonly static string sqlStrSchedule = "SELECT * FROM seminar WHERE year(starttime) = @year and month(starttime) = @month order by starttime asc";
    public readonly static string sqlStrToday = "Select * FROM seminar WHERE month(starttime) = @month and year(starttime) = @year and day(starttime) = @day order by starttime asc";
    public readonly static string sqlStrDayScheduleCount = "SELECT count(*) FROM seminar WHERE year(starttime) = @year and month(starttime) = @month and day(starttime) = @day";
    public readonly static string sqlStrScheduleInput = "Insert into seminar (category, announcer, subject, starttime) Values(@category, @announcer, @subject, @starttime)";
    public readonly static string sqlStrCategory = "SELECT * FROM seminar_category order by idx asc";
    public readonly static string sqlStrStudyCategory = "SELECT * FROM study";
    public readonly static string sqlStrStudyInput = "Insert into study (studyname, starttime, period, people, introduce, leader) Values(@studyname, @starttime, @period, @people, @introduce, @leader)";
    public readonly static string sqlStrStudyCheckInput = "Insert into studyattend (studyname, attend, absent, report, etc, studydate, status) Values(@studyname, @attend, @absent, @report, @etc, @studydate, @status)";
 
  
    public readonly static string sqlStrStudyPeopleInput = "Insert into studypeople (studyname, person) Values(@studyname, @person)";
  
 // 지환 --
 // 도서신청검색 쿼리문(유저)
 public static string sqlstrUserViewReqBook = "SELECT * from requestinfo_view WHERE month(requestdate) = @month and year(requestdate) = @year and useridx = @useridx";
 // 도서신청검색 쿼리문(관리자)
 public static string sqlstrAdminViewReqBook = "SELECT * from requestinfo_view WHERE month(requestdate) = @month and year(requestdate) = @year";
 // 도서신청내용 삽입 쿼리문
 public static string sqlstrReqBook = "INSERT INTO requestbook (useridx, bookname, price, reason, site, link, compare) VALUES(@useridx, @bookname, @price, @reason, @site, @link, @compare)";
 // 도서신청내용 수정 쿼리문
    public static string sqlstrReqBookAdjust = "UPDATE requestbook SET " +
            "bookname = @bookname, price = @price, reason = @reason, site =@site, "
          + "link = @link, compare =@compare WHERE idx = @idx";
    // 도서를 등록할 삽입 쿼리문
 public static string sqlstrInsertBook = "INSERT INTO book (isbn, picture, typecode, bookname, publisher, author, cd, term, bookgroup ,position, samecount, purchasedate, publishdate, url) "
   + "VALUES(@isbn, @picture, @typecode, @bookname, @publisher, @author, @cd, @term, @bookgroup ,@position, @samecount, @purchasedate ,@publishdate, @url)";
 // 도서검색 관리 권한 체크 쿼리문
 public static string sqlstrCheckAdmin = "SELECT count(*) AS counter from auth WHERE id = @ID AND auth = '도서신청'";
 // 도서신청 모든 내용 보기(관리자용)
 public static string sqlstrAdminViewReqBook_All = "SELECT * from requestinfo_view";
 // 도서신청 모든 내용 보기(유저용)
 public static string sqlstrUserViewReqBook_All = "SELECT * from requestinfo_view WHERE useridx = @useridx";

-- 기존의 대여 테이블

CREATE TABLE lending (
 idx  INT IDENTITY(0,1),
 bookidx  INT NOT NULL,  // ISBN
 useridx  INT NOT NULL,  // 사용자 ID
 returnplan SMALLDATETIME NOT NULL, // 예정반납일   ----
 lendday  SMALLDATETIME NOT NULL, // 대출일
 PRIMARY KEY (idx)
 UNIQUE (bookidx, useridx, lendday),
 FOREIGN KEY (bookidx) REFERENCES book (idx),
 FOREIGN KEY (useridx) REFERENCES userdb (idx)
)

-- 기존의 대여했던 도서의 테이블

CREATE TABLE lendhistory (
 bookidx  INT NOT NULL,
 useridx  INT NOT NULL,
 username VARCHAR(10) NOT NULL,
 userseason DECIMAL(3,0) NOT NULL,
 typecode INT NOT NULL,
 bookname VARCHAR(100) NOT NULL,
 publisher VARCHAR(50) NOT NULL,
 author  VARCHAR(50) NOT NULL,
 returnplan SMALLDATETIME NOT NULL,
 lendday  SMALLDATETIME NOT NULL,
 returnday SMALLDATETIME NOT NULL, // 실제반납일
 PRIMARY KEY (bookidx, useridx)
)
 
--

select username, count(*) AS m_count
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 11
union all
select useridx, username
 from lending
  where year(lendday) = 2007 and month(lendday) = 11
) as a1
group by username
order by 2 desc

-- 쿼리 문

select * from lending where useridx = 67 and year(lendday) = 2007 and month(lendday) = 10

select count(*) AS counter from lending where useridx = 67 and year(lendday) = 2007 and month(lendday) = 10

/*
create view ssss
 select useridx, count(*) as num_prods
  from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/
/*
select useridx, count(*) as num_prods
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/

/*
select *
from lending
where
 year(lendday) = 2007 and month(lendday) = 10
 or
 useridx in (select useridx
     from lendhistory
     where year(lendday) = 2007 and month(lendday) = 10 )

 --from lending
*/

select lendhistory.useridx, count(*) as num_prods
 from lendhistory
  left join lending
   on lendhistory.useridx = lending.useridx
  where year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10
    or year(lending.lendday) = 2007 and month(lending.lendday) = 10
  group by lendhistory.useridx
  order by 2 desc

/*
select *--lendhistory.useridx, count(*) as num_prods
 from lendhistory full outer join lending
  on lendhistory.useridx = lending.useridx
  where
   ((year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10)
     or ((year(lending.lendday) = 2007 and month(lending.lendday) = 10)))
  --group by lendhistory.useridx
  --order by 2 desc
*/
/*
select useridx, count(*)
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
group by useridx
*/

-- 최종 1

select useridx, count(*)
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
union all
select useridx, 'aa'
 from lending
  where year(lendday) = 2007 and month(lendday) = 10
) as a1
group by useridx
order by 2 desc

/*
create view ssss
 select useridx, count(*) as num_prods
  from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/
/*
select useridx, count(*) as num_prods
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
  group by useridx
  order by 2 desc
*/

select useridx, count(*) AS m_count
from
(
select useridx, username
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
union all
select useridx, ''
 from lending
  where year(lendday) = 2007 and month(lendday) = 10
) as a1
group by useridx
order by 2 desc

/*
insert into test from  (
 select useridx
  from lendhistory
   where year(lendday) = 2007 and month(lendday) = 10
 union all
 select useridx
  from lending
   where year(lendday) = 2007 and month(lendday) = 10
)
*/
/*
select *
from lending
where
 year(lendday) = 2007 and month(lendday) = 10
 or
 useridx in (select useridx
     from lendhistory
     where year(lendday) = 2007 and month(lendday) = 10 )

 --from lending
*/
/*
select lendhistory.useridx, count(*) as num_prods
 from lendhistory
  left join lending
   on lendhistory.useridx = lending.useridx
  where year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10
    or year(lending.lendday) = 2007 and month(lending.lendday) = 10
  group by lendhistory.useridx
  order by 2 desc
*/

/*
select *--lendhistory.useridx, count(*) as num_prods
 from lendhistory full outer join lending
  on lendhistory.useridx = lending.useridx
  where
   ((year(lendhistory.lendday) = 2007 and month(lendhistory.lendday) = 10)
     or ((year(lending.lendday) = 2007 and month(lending.lendday) = 10)))
  --group by lendhistory.useridx
  --order by 2 desc
*/
/*
select useridx, count(*)
 from lendhistory
  where year(lendday) = 2007 and month(lendday) = 10
group by useridx
*/

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Hwang Jun - Beom
-- Create date: 2007/03/23
-- Description: input lendhistory
-- =============================================
CREATE PROCEDURE lendreturn_sp
 -- Add the parameters for the stored procedure here
 @idx  VARCHAR(15)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 DECLARE @d_returndate SMALLDATETIME;
 DECLARE @i_bookidx INT;

 SELECT @i_bookidx = bookidx FROM lendinginfo_view WHERE idx = @idx;
 SELECT @d_returndate = returndate FROM reservation WHERE bookidx = @i_bookidx;

 IF @d_returndate IS NULL
  BEGIN
   UPDATE reservation SET returndate = GETDATE() WHERE bookidx = @i_bookidx;
  END

 INSERT INTO lendhistory SELECT bookidx, useridx, username, season, type, bookname, publisher, author, returnplan, lendday, returnday FROM input_lendhistory_view WHERE idx = @idx;
 DELETE FROM lending WHERE idx = @idx;

END
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Hwang Jun - Beom
-- Create date: 2007/03/23
-- Description: insert user
-- =============================================
ALTER PROCEDURE [dbo].[insertuser_sp]
 -- Add the parameters for the stored procedure here
 @id  VARCHAR(15),
 @passwd  VARCHAR(20),
 @name  VARCHAR(10),
 @season  DECIMAL(3,0),
 @email  VARCHAR(50),
 @phone  VARCHAR(11),
 @university INT,
 @major  INT,
 @ipaddress VARCHAR(3),
 @result  int OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 DECLARE @i_count int;
 SELECT @i_count = count(*) FROM userdb WHERE userid = @id;

 IF @i_count != 0
  BEGIN
   SET @result = 1;
   RETURN;
  END

 INSERT INTO userdb VALUES(@id, @passwd, @name, @season, @email, @phone, @university, @major, @ipaddress);

 DECLARE @i_useridx int;
 SELECT @i_useridx = idx FROM userdb WHERE userid = @id;
 INSERT INTO auth VALUES(0, @i_useridx);
 
 SET @result = 0;
 RETURN;
END

USER --

CREATE TABLE university (
 idx  INT IDENTITY(0,1),
 university VARCHAR(30) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (university)
)

CREATE TABLE major (
 idx  INT IDENTITY(0,1),
 major  VARCHAR(20) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (major)
)

CREATE TABLE userdb (
 idx  INT IDENTITY(0,1),
 userid  VARCHAR(15) NOT NULL,
 passwd  VARCHAR(20) NOT NULL,
 username VARCHAR(10) NOT NULL,
 season  DECIMAL(3,0) NOT NULL,
 email  VARCHAR(50) NOT NULL,
 phone  VARCHAR(11) NOT NULL,
 university  INT NOT NULL,
 major   INT NOT NULL,
 PRIMARY KEY (idx),
 FOREIGN KEY (university) REFERENCES university (idx),
 FOREIGN KEY (major) REFERENCES major (idx),
 UNIQUE (userid)
)
 

AUTH --

CREATE TABLE authcode (
 idx  INT IDENTITY(0,1),
 auth  VARCHAR(10) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (auth)
)
 
CREATE TABLE auth (
 authcode INT NOT NULL,
 useridx  INT NOT NULL,
 PRIMARY KEY (authcode, useridx),
 FOREIGN KEY (authcode) REFERENCES authcode (idx),
 FOREIGN KEY (useridx) REFERENCES userdb (idx)
)

BOOK --

CREATE TABLE typecode (
 idx  INT IDENTITY(0,1),
 type  VARCHAR(10) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (type)
)

CREATE TABLE bookgroupcode (
 idx  INT IDENTITY(0,1),
 bookgroup VARCHAR(20) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (bookgroup)
)

CREATE TABLE positioncode (
 idx  INT IDENTITY(0,1),
 position VARCHAR(4) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (position)
)

CREATE TABLE book (
 idx  INT IDENTITY(0,1),
 isbn  VARCHAR(11) NOT NULL, // ISBN
 picture  VARCHAR(255),  // 그림
 typecode INT NOT NULL,  // 책/잡지
 bookname VARCHAR(100) NOT NULL, // 서명
 publisher VARCHAR(50) NOT NULL, // 출판사
 author  VARCHAR(50) NOT NULL, // 저자
 cd  BIT DEFAULT 0,  // CD 유무 0: 없음, 1: 있음
 term  DECIMAL(3) DEFAULT 30, // 대출제한 일단위
 bookgroup INT NOT NULL,  // JAVA, C, C++, OPERATING SYSTEM 등
 position INT NOT NULL,   // 도서 위치 A-1, I-3, G-10 등
 purchasedate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
 publishdate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
 samecount TINYINT DEFAULT '1',    // 도서 권수
 PRIMARY KEY (idx),
 FOREIGN KEY (typecode) REFERENCES typecode (idx),
 FOREIGN KEY (bookgroup) REFERENCES bookgroupcode (idx),
 FOREIGN KEY (position) REFERENCES positioncode (idx),
 UNIQUE (isbn)
)

CREATE TABLE bookrequestdate (
 beginday INT NOT NULL DEFAULT 27,
 endday  INT NOT NULL DEFAULT 1
)

CREATE TABLE lending (
 idx  INT IDENTITY(0,1),
 bookidx  INT NOT NULL,  // ISBN
 useridx  INT NOT NULL,  // 사용자 ID
 returnplan SMALLDATETIME NOT NULL, // 예정반납일   ----
 lendday  SMALLDATETIME NOT NULL, // 대출일
 PRIMARY KEY (idx)
 UNIQUE (bookidx, useridx, lendday),
 FOREIGN KEY (bookidx) REFERENCES book (idx),
 FOREIGN KEY (useridx) REFERENCES userdb (idx)
)

CREATE TABLE lendhistory (
 bookidx  INT NOT NULL,
 useridx  INT NOT NULL,
 username VARCHAR(10) NOT NULL,
 userseason DECIMAL(3,0) NOT NULL,
 typecode INT NOT NULL,
 bookname VARCHAR(100) NOT NULL,
 publisher VARCHAR(50) NOT NULL,
 author  VARCHAR(50) NOT NULL,
 returnplan SMALLDATETIME NOT NULL,
 lendday  SMALLDATETIME NOT NULL,
 returnday SMALLDATETIME NOT NULL, // 실제반납일
 PRIMARY KEY (bookidx, useridx)
)
 

CREATE TABLE requestbook (
 idx  INT IDENTITY(0,1),
 useridx  INT NOT NULL,
 bookname VARCHAR(100) NOT NULL,
 requestdate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
 price  VARCHAR(10) NOT NULL,
 reason  VARCHAR(20) NOT NULL,
 site  VARCHAR(10) NOT NULL,
 link  VARCHAR(200) NOT NULL,
 compare  VARCHAR(100) NOT NULL,
 PRIMARY KEY (idx),
 UNIQUE (useridx, bookname),
 FOREIGN KEY (useridx) REFERENCES userdb (idx)
)

CREATE TABLE reservation (
 idx  INT IDENTITY(0,1),
 bookidx  INT NOT NULL,
 useridx  INT NOT NULL,
 returndate SMALLDATETIME DEFAULT NULL,
 PRIMARY KEY (idx),
 FOREIGN KEY (bookidx) REFERENCES book (idx),
 FOREIGN KEY (useridx) REFERENCES userdb (idx),
 UNIQUE (bookidx, useridx)
)

CREATE TABLE bookreview (
 reviewno INT IDENTITY(0,1),
 bookidx  INT NOT NULL,
 useridx  INT NOT NULL,
 username VARCHAR(10) NOT NULL,
 userseason DECIMAL(3,0) NOT NULL,
 info  TEXT NOT NULL,
 PRIMARY KEY (reviewno),
 FOREIGN KEY (bookidx) REFERENCES book (idx)
)

LENDINGCOUNT_VIEW --

CREATE VIEW lendingcount_view AS
 SELECT bookidx, count(*) AS lendingcount FROM lending GROUP BY bookidx

LENDHISTORYCOUNT_VIEW --

CREATE VIEW lendhistorycount_view AS
 SELECT bookidx, count(*) AS lendhistorycount FROM lendhistory GROUP BY bookidx

LENDCOUNTER_VIEW --

CREATE VIEW lendcount_view AS
 SELECT A.bookidx, (A.lendingcount + ISNULL(B.lendhistorycount, 0)) AS lendcount
  FROM lendingcount_view A FULL OUTER JOIN lendhistorycount_view B ON A.bookidx = B.bookidx

BOOKINFO --

CREATE VIEW bookinfo_view AS
 SELECT A.idx, A.isbn, A.picture, A.bookname, A.publisher, A.author, A.cd, A.term, A.samecount, A.purchasedate, A.publishdate,
  B.type, C.bookgroup, D.position, (A.samecount - ISNULL(E.lendingcount, 0)) AS lendingcounter,
  F.useridx AS reservation, G.lendcount, A.url
  FROM book A
   LEFT OUTER JOIN lendingcount_view E
    ON A.idx = E.bookidx
   LEFT OUTER JOIN reservation F
    ON A.idx = F.bookidx
   LEFT OUTER JOIN lendcount_view G
    ON A.idx = G.bookidx,
  typecode B,
  bookgroupcode C,
  positioncode D
 WHERE A.typecode = B.idx
  AND A.bookgroup = C.idx
  AND A.position = D.idx

LENDINGINFO_VIEW --

CREATE VIEW lendinginfo_view AS
 SELECT A.idx, F.useridx, F.userid, F.username, C.type, D.bookgroup, E.position, B.bookname, B.term,
  A.bookidx, A.returnplan, A.lendday,
  (B.samecount - ISNULL(G.lendingcount, 0)) AS lendingcounter,
  I.userid AS reservationuserid, I.username AS reservationusername
  FROM lending A
   LEFT OUTER JOIN lendingcount_view G ON A.bookidx = G.bookidx
   LEFT OUTER JOIN reservation H ON A.bookidx = H.bookidx
   LEFT OUTER JOIN userdb I ON H.useridx = I.idx,
   book B, typecode C, bookgroupcode D, positioncode E, userdb F
  WHERE A.bookidx = B.idx
   AND B.typecode = C.idx
   AND B.bookgroup = D.idx
   AND B.position = E.idx
   AND A.useridx = F.idx

LENDHISTORYINFO_VIEW --

CREATE VIEW lendhistoryinfo_view AS
 SELECT F.userid, F.username, ISNULL(C.type, '폐기') AS type, D.bookgroup, E.position, A.bookname, B.term, A.useridx, A.bookidx, A.returnplan, A.lendday, A.returnday
  FROM lendhistory A
   LEFT OUTER JOIN book B ON A.bookidx = B.idx
   LEFT OUTER JOIN typecode C ON B.typecode = C.idx
   LEFT OUTER JOIN bookgroupcode D ON B.position = D.idx
   LEFT OUTER JOIN positioncode E ON B.position = E.idx
   LEFT OUTER JOIN userdb F ON A.useridx = F.idx

REQUESTINFO_VIEW --

CREATE VIEW requestinfo_view AS
 SELECT A.userid, A.username, B.*
  FROM userdb A, requestbook B
  WHERE A.idx = B.useridx

USERINFO_VIEW --

CREATE VIEW userinfo_view AS
 SELECT A.idx, A.season, A.username, A.userid, A.passwd, D.university, E.major, C.auth
  FROM userdb A
   LEFT OUTER JOIN auth B ON A.idx = B.useridx
   LEFT OUTER JOIN authcode C ON B.authcode = C.idx,
   university D, major E
  WHERE A.university = D.idx
   AND A.major = E.idx

INPUT_LENDHISTORY_VIEW --

CREATE VIEW input_lendhistory_view AS
 SELECT A.idx, A.bookidx, A.useridx, C.username, C.season, D.type, B.bookname, B.publisher, B.author, A.returnplan,
  A.lendday, GETDATE() AS returnday
  FROM lending A, book B, userdb C, typecode D
  WHERE A.bookidx = B.idx
   AND A.useridx = C.idx
   AND B.typecode = D.idx

CREATE VIEW reservationinfo_view AS
 SELECT A.idx, A.bookidx, A.useridx, F.userid, F.username, B.bookname, C.type, D.bookgroup, E.position, B.term, (B.samecount - ISNULL(G.lendingcount, 0)) AS lendingcounter, A.returndate
  FROM reservation A LEFT OUTER JOIN lendingcount_view G ON A.bookidx = G.bookidx, book B, typecode C, bookgroupcode D, positioncode E, userdb F
  WHERE A.bookidx = B.idx
   AND B.typecode = C.idx
   AND B.bookgroup = D.idx
   AND B.position = E.idx
   AND A.useridx = F.idx