SQL COde

SQL Codes.
Create Database
create database CountryClub
drop database countryclub
create table member(
memberid int
primary key, /*setting the memberid as primary key*/
name varchar(50) not null, /*the value cannot be null*/
address varchar(100) null, /*the value can be null*/
contact int null,
email varchar(100) null,
absentStatus char(1) null default('N'), /*the value of the absent Status can be null, but the default is 'N'*/
dob datetime null,
balance smallmoney null,
memberType int null default (1), /*the value of the member type can be null, but the dafault is 1*/
constraint check_absentStatus check (absentStatus in ('Y','N')) /*create a constrain/limit that the absent status must be either Y or N*/
)
/*inserting data into the table*/
insert into member values (3810, 'johan', 'xiao xhao bo road blk 4 #08-1998','98765443','johan_big@hotmail.com','N', 1988-05-03,0,1)

/*the staff, member and facility table must be present!*/
create table reservation(
reservationID int
primary key, /*set reservationID as primary key*/
dateMade datetime not null default(current_timestamp), /*the value of dateMade cannot be null and the default value is the current time*/
startTime datetime not null,
endTime datetime null,
charges smallmoney null,
staffID int
foreign key references staff(staffID), /*staffID is a foreign key. it is the primary key of staff. which is staffID*/
memberid int
foreign key references member(memberid), /*memberid is a foreigne key....*/
facilityID int
foreign key references facility(facilityID) /*the facility ID inputed must be found at facility de facilityID*/
)

update member set absentStatus = 'N' where name = 'Johan'
update nameOfTable set nameOfAttrubuteToChange = changeToWhatValue where anotherAttribute = someValue

delete reservation where reservationID = 1
delete nameOfTable where namOfAttribute = theValueofTheAttribute
all data with the selected value of the attributed will be deleted.

Previously Learnt
select * from staff
/* by using '*', it will select all the things in 'staff'*/

/* by highlighting a portion of the codees,
it will only execute selected codes. When u click executed. */

select name, gender from staff order by name desc
/* by using 'order by' it will sort by a variable value*/
/* by using 'desc', it will display the names in descending order. */
/* opposite of desc (descending) is asc (ascending) */
/* asc is by default. (ascending is by dafault) */

select distinct supervisorID from staff
/* by using distinct, it will automatically removed repeated output. */

select isbn, copyno, rentalrate, rentalrate*0.98 as "New Rental Rate" from bookcopy
/* by adding a comma, it will automatically add a new colum.*/
/* the part 'as "new rental rate" ' will add a title name to the new column*/

select isbn, copyNo, rentalrate, 10 as "trying" from bookcopy
/*there need a figure as result for the colum names as "trying" */

/*List every details of member who registered at Branch 1*/
select * from member where(branchno = 1)

/*List every detail of members who registered at BranchNo 1 or 2*/
select * from member where(branchNo = 1) OR (branchNo = 2)

/*List every detail of members who registered at branchNo 1 or 2, after 31 Dec 2005*/
select * from member where ((branchNo = 1) OR (branchNo = 2)) AND (dateJoin > '2005-12-31')



/*List every detail of loans that are made between '1 Dec 2006' and '31 Jan 2007'*/
select * from loan where (dateOut BETWEEN '2006-12-1' AND '2007-01-31')

/*List every detail of books belong to bookCat'C' or 'F'*/
select * from book where bookCat IN ('C' , 'F')

/*List every detail of member belonging to BranchNo 1 or 2 or 3*/
select * from member where branchNo IN (1,2,3)
/*List every detail of books with title that STARTS with 'Database'*/
select * from book where title Like 'Database%'

/*List every detail of books with title that ENDS with 'Database Design'*/
select * from book where title like '%Database Design'

/*List every detail of members with Name that contains 'Kim'*/
select * from member where name like '%kim%'





/*List every detail of books that have not been categorised yet
Not categorised as in the value for the category is not entered aka Null*/
select * from book where bookCat IS NULL

/*List every detail of staff whose DOB do NOT fall between '1 Jan 1988' and '31 Jun 1990'*/
select * from staff where (DOB NOT BETWEEN '1988-01-01' AND '1990-06-30')

/*List every detail of books that do NOT belong to bookCat 'C' or 'F'.*/
select * from book where (bookCat NOT IN ('C','F') OR bookCat IS NULL )

SELECT GETDATE() get the current date

SELECT DATEPART(MONTH, GETDATE()) get the month part of the date in int
SELECT DATEPART(*can be dd for day, year and month*, aDate)

SELECT DATENAME(MONTH, GETDATE()) get the month part of the date in name

SELECT YEAR(GETDATE()) get the year of the date

SELECT DAY(GETDATE()) get the day of the date

Datediff(dd/month/year, startDate, endDate) it get the difference between startdate and endDate.


SELECT memberid, name, datejoin, datediff(year, datejoin, getdate()) as "year of membership" FROM member

SELECT loanno, isbn, copyno, memberid, datediff(day, datedue, datereturn) as "overdue" FROM loan where (datediff(day, datedue, datereturn) > 0)

SELECT memberid, name, datejoin from member WHERE (DATEPART(YEAR, datejoin) = 2006) order by datejoin desc

SELECT staffid, name, gender, dob from staff where (datepart(month, dob) = 02) order by name

SELECT COUNT(*) FROM staff
count the total number of rows with data from the staff table

SELECT COUNT(supervisorid) FROM staff count the supervisorID

SELECT COUNT(DISTINCT supervisorID) AS "QNS 12" FROM staff
Count the number of supervisorId without repeating.

SELECT (COUNT(*) - COUNT(ALL emailaddr)) AS "QNS 14" FROM member
Use the total number of rows with data, minu the total number of emailAddr which is not null.

SELECT MIN(rentalrate) FROM bookcopy select the minimun of value rentalrate

SELECT MAX(rentalrate) FROM bookcopy select the maximun of value

SELECT AVG(rentalrate) FROM bookcopy calculate the average of rentalrate


SELECT COUNT(DISTINCT loanno) AS "loans", SUM(rentalrate) AS "TOTAL INCOME" FROM loan

SELECT COUNT(DISTINCT loanno) AS "loans", SUM(rentalrate) AS "TOTAL INCOME" FROM loan WHERE(DATEPART(YEAR, dateout) = 2006)

SELECT COUNT(*), SUM(salary) FROM staff

SELECT name, ISNULL(emailaddr, 'NOT AVAILABLE') FROM member ORDER BY name
Isnull(emailAddr) means that the value of emailAddr is null.

SELECT name, address, contactno, emailaddr FROM member WHERE (DATEPART(YEAR, datejoin) < 2006 AND (emailaddr IS NOT NULL))
Inner Join
/*Qns1:List ISBN, CopyNo, Title and RentalRate for every book copy in NP40 Book Rental.*/
SELECT bookcopy.isbn, copyno, title, rentalrate FROM book
INNER JOIN bookcopy
ON book.isbn = bookcopy.isbn

** the bookcopy.isbn is referring to the isbn from the table “bookcopy” you need to specify which isbn u wana display cos both bookcopy and book got ‘isbn’

SELECT loanno, isbn, name, dateout, rentalrate FROM loan
INNER JOIN member
ON member.memberid = loan.memberid

**here, there is no member.name or loan.loanno cos only 1 of the table has the attribute name.
**i.e. if the 2 table which mean the same thing got different name (whn u create them ) u no need to put book.isbn or member.memberId.

SELECT book.isbn, title, description as "category" from book
inner join bookcategory
on book.bookcat = bookcategory.bookcat order by title

**order by clause is last~
** select smething, smething, smething from tableA
Inner join tableB
On commonFactorInA = commonFactorInB

/*Qns5:List the ISBN, CopyNo and DateOut for all the loans made on the book titled 'Stuart Little'.*/
select loan.isbn, title, copyno, dateout from loan
inner join book
on loan.isbn = book.isbn where (title = 'Stuart Little')
/*You can replace 'where' with 'and' if you like.... :P*/

/*Qns7:List the StaffID, Name and DateJoin for all the staff who work for the supervisor 'May May'.*/
select t.staffid, t.name, t.datejoin, s.name as "supervisor name" from staff s
inner join staff t
on s.staffid = t.supervisorid where s.name = 'May May'



/*Qns8:List the ISBN, Title and DateOut for all the loans made by the member 'Kumar' and display the results in ascending order of DateOut.*/
select book.isbn, title, dateout, member.name from book
inner join loan on book.isbn = loan.isbn
inner join member on loan.memberid = member.memberid where member.name = 'Kumar' order by dateOut

** The Title only the table book got, so u join the book table
** The dateOut aka loan detail need to get from loan table
** you only want to display things from member ‘Kumar’

/*Qns9:List the ISBN, Title and YearPublish for all the 'Fiction' books published by publisher named 'Arrow Books' and display the results in ascending order of YearPublish.*/
select book.isbn, book.title, book.yearpublish, description, publisher.name from book
inner join bookcategory on book.bookcat = bookcategory.bookcat
inner join publisher on book.publisherid = publisher.publisherid
where description = 'Fiction' AND publisher.name = 'Arrow Books' order by yearpublish

/*Qns10:List the ISBN, Title and Author for all the books and display the results in ascending order of Title.*/
select book.isbn, title, author.name from book
inner join bookauthor on book.isbn = bookauthor.isbn
inner join author on bookauthor.authorid = author.authorid
order by title

** order by is the last clause
Group By
/*Qns02. List the number of copies for each book in descending order of number of copies.*/
select isbn, count(copyno)as "number of copy" from bookcopy group by isbn order by count(copyno)desc

/*Qns03. List the branche(s) that have 3 or more members.*/
select branchno, count(memberid) from member group by branchno having(count(memberid) >0)
/*Qns04. List the number of copies for each book that have more than 2 copies.*/
select isbn, count(copyno)as "number of copy" from bookcopy group by isbn having count(copyno) <2

/*Qns05. For each branch, compute the number of members for each gender.*/
select branchno, gender, count(memberid) as "number of member" from member group by gender, branchno
/*Qns08. List each publisher in NP40 Book and the number of book it published*/
select name as "publisher", count(isbn) as "number of book" from book
inner join publisher on publisher.publisherid = book.publisherid
group by name order by count(isbn) desc

** the having clause can only be used if there is a group by clause
** got ‘count’ must have group by unless is count all~
Sub Query
/*Qns12. SELECT statement with subquery to list the loanno, isbn and date of loan for the loans made by member from branch number 1.*/

**one way of doing it
select loanno, isbn, dateout from loan
inner join member on member.memberid = loan.memberid
where branchno = 1

** another way of doing it using select statememt!!
select loanno,isbn, dateout from loan where memberid in
(select memberid from member group by memberid,branchno having branchno = 1)


/*Qns13. Construct a SELECT statement with subquery to list the ISBN and title of books that have not been loaned out before.*/

select isbn, title from book where isbn not in
(select isbn from loan)

Select smething, smething from tableA where commonfactor in/ not in
(select commonfactor from tableB)


/*Qns14. Construct a SELECT statement with subquery to list name and salary of staff who has the highest salary.*/
select name, salary from staff where salary in
(select max(salary) from staff)

/*Qns15. Construct a SELECT statement with subquery to list member id, name and gender of members who have made more than 5 loans.*/
select memberid, name, gender from member where memberid in
(select memberid from loan group by memberid having (count(loanno) >5) )
/*Qns17. For each branch, compute the number of staffs by gender supervised by each supervisor.*/
select gender, count(staffid) as "number of staff", supervisorid from staff where supervisorid is not null group by supervisorid, gender
Not so important
SELECT UPPER(name) FROM member ORDER BY name not sure is what

SELECT SUBSTRING(isbn, 1, 5), title FROM book ORDER BY SUBSTRING(isbn, 1, 5), title asc
Select only part of the string.

SELECT SUBSTRING(isbn, 1, 5) AS "isbn", REPLACE(title, 'Database', 'DB') AS "Title" FROM book
SELECT isbn, copyno, ROUND(rentalrate, 0) FROM bookcopy
Round means round down (i think)