SQL Statement Learning

---reference http://www.cnblogs.com/wishyouhappy/p/3700683.html

--Case 1 Student Selection Course
--Create a table
CREATE TABLE STU (
ID number is not NULL,
Name VARCHAR2 (255)
）;
CREATE TABLE Course (
ID number is not NULL,
Coursename VARCHAR2 (255)
）;
CREATE TABLE S_c (
Sid number,
CID number,
Score Number
）;
--Inserting data
Insert into STU (id,name) VALUES (1, ' wish ');
Insert into STU (id,name) VALUES (2, ' rain ');
Insert into STU (id,name) VALUES (3, ' July ');
Insert into STU (id,name) VALUES (4, ' Joey ');

Insert into COURSE (id,coursename) VALUES (1, ' MATH ');
Insert into COURSE (id,coursename) VALUES (2, ' 中文版 ');
Insert into COURSE (id,coursename) VALUES (3, ' Japanese ');
Insert into COURSE (id,coursename) VALUES (1, ' Chinese ');

Insert into S_c (Sid,cid,score) values (1,1,80);
Insert into S_c (Sid,cid,score) values (1,2,90);
Insert into S_c (Sid,cid,score) values (2,4,100);
Insert into S_c (Sid,cid,score) values (4,4,90);
Insert into S_c (Sid,cid,score) values (4,1,100);
Insert into S_c (Sid,cid,score) values (4,3,80);
Insert into S_c (Sid,cid,score) values (4,2,80);
Insert into S_c (Sid,cid,score) values (2,1,90);
Insert into S_c (Sid,cid,score) values (2,4,100);
Insert into S_c (Sid,cid,score) values (3,1,60);

--inquiry, to find out all the students ' achievements
With VT as
(select S.id,s.name,c.coursename,sc.score from Stu S, Course c, S_c SC where s.id=sc.sid and C.id=sc.cid)
SELECT * FROM VT ORDER by ID;

--Case 2 book Access

--Create TABLE book
CREATE TABLE book (
BookId VARCHAR2 (30),--Total number of books
SortID VARCHAR2 (30),--Class number
BookName varchar2 (100),--title
Author Varchar2 (30),---author
Publisher Varchar2,--Publishing Unit
Price Number (6,2)--prices, which allow a total of 6 characters, are called widths. The back 2 is reserved for two digits after the decimal point, known as precision.
);
--Create TABLE Reader
CREATE TABLE Reader (
CardId VARCHAR2 (30),--Library card number
ORG varchar2 (100),--unit
Name VARCHAR2 (100),--Name
Gender VARCHAR2 (2),--gender
Title VARCHAR2 (30),--Job title
Address VARCHAR2 (100)--Location
);
--Create TABLE borrow
CREATE TABLE Borrow (
CardId VARCHAR2 (30),--Library card number
BookId VARCHAR2 (30),--Total number of books
Borrowdate VARCHAR2 (30)--borrowing time
);
--Inserting data-reader
INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' xxx ', ' A ', ' Wish ', ' 1 ', ' student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' UUU ', ' A ', ' Luna ', ' 1 ', ' student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' VVV ', ' B ', ' Harry ', ' 1 ', ' student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' www ', ' C ', ' Chander ', ' 2 ', ' Professor ', ' Bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' yyy ', ' A ', ' Joey ', ' 2 ', ' Student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' zzz ', ' B ', ' Richard ', ' 2 ', ' Student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' OOO ', ' A ', ' micheal ', ' 2 ', ' Student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' PPP ', ' A ', ' Richal ', ' 2 ', ' Student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' ABP ', ' A ', ' Michal ', ' 2 ', ' Student ', ' bupt ');

INSERT into reader (cardid, org, Name,gender, title, address)
VALUES (' CCP ', ' A ', ' Mike ', ' 2 ', ' Student ', ' bupt ');

--Inserting data-book
Insert into book (Bookid,sortid,bookname,author,publisher,price)
VALUES (' AAA ', ' A1 ', ' Gone with the Wind ', ' CA ', ' renmin ', ' 103 ');

Insert into book (Bookid,sortid,bookname,author,publisher,price)
VALUES (' BBB ', ' A2 ', ' The Little Prince ', ' CB ', ' Jixie ', ' 30 ');

Insert into book (Bookid,sortid,bookname,author,publisher,price)
VALUES (' CCC ', ' A3 ', ' The Ordinary World ', ' CC ', ' renmin ', ' 130 ');

Insert into book (Bookid,sortid,bookname,author,publisher,price)
VALUES (' ddd ', ' A4 ', ' The Little Women ', ' CA ', ' Dianzi ', ' 110 ');

--Inserting data-borrow
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' xxx ', ' aaa ', ' 2014-4-29 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' xxx ', ' bbb ', ' 2014-4-29 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' xxx ', ' CCC ', ' 2014-4-28 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' CCC ', ' 2014-4-28 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' ddd ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' aaa ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' zzz ', ' BBB ', ' 2014-4-28 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' zzz ', ' ddd ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' zzz ', ' aaa ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' BBB ', ' 2014-4-28 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' ddd ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' AAA ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' CCC ', ' 2014-4-26 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' VVV ', ' BBB ', ' 2014-4-28 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' VVV ', ' ddd ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' www ', ' aaa ', ' 2014-4-27 ');
Insert into Borrow (cardid,bookid,borrowdate) VALUES (' www ', ' CCC ', ' 2014-4-26 ');

SELECT * from book;
select * from reader;
SELECT * from Borrow;
--Query the number of readers and personnel details of a unit borrowing books
--Number
--Common expression get
/*
With <name of cte> (<column names>)
As
<actual query>
)
SELECT * from <name of your cte>
*/
--count (1) Specifies the number of values that return the first column
--exists is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or False
--exists: Emphasizes whether to return the result set, does not require to know what to return
--exists each row of the outer query table as a test, and if the result returned by the inner query takes a non-null value, the EXISTS clause returns TRUE, which can serve as the result row of the outer query, otherwise it cannot be a result.
With VT1 as
(select Cardid from reader where reader.org= ' A ')
Select COUNT (1) from VT1 where exists (select Cardid from Borrow where borrow.cardid=vt1.cardid);

--Detailed information
With VT1 as
(select cardid,name,org from reader where reader.org= ' A ')
Select cardid,name,org from VT1 where exists (select Cardid from Borrow where borrow.cardid=vt1.cardid);

With VT1 as
(select cardid,name,org from reader where reader.org= ' A ')
Select cardid,name,org from VT1 where exists (select Cardid from Borrow where borrow.cardid=vt1.cardid);

--Query the reader with the end of the library card number as ' P '
--Fuzzy query:% denotes any 0 or more characters, _: represents any single character.
Select cardid,name,org from reader where cardid like '%p ';
--Query the reader whose name starts with M, ' 1 ' shows as female, ' 2 ' shows as male
/*
SELECT <myColumnSpec> =
Case
When <A> then <somethingA>
When <B> then <somethingB>
ELSE <somethingE>
END
*/

Select Cardid, name, org,
Case if gender= ' 1 ' Then ' Woman ' when gender= ' 2 ' then ' man ' else ' other ' end gender
From reader where name is like ' m% ';

Select cardid,name,org,
case when gender= ' 1 ' Then ' women ' when gender= ' 2 ' then ' man ' else ' other ' end gender
from read Er where name like ' m% ';
--2014 2-April readers who borrowed a book
--Query the reader satisfying the condition (including Cardid only)--did not go to the
Select * from borrow;
Select Cardid from borrow where borrowdate between ' 2014-2-1 ' and ' 2014-5-1 ';
--to_date (date, ' format '), converts a string to a date-type conversion function in a database,
--to_char (date, ' format '), which converts a date or number to a string
--date
--year yyyy yyy YY Year
-monthly month mm Mon month
-day + weekday DD DDD (days of the week) DY daily
--hours HH hh24
--min mi
-sec SS

--Method 1-3 (not heavy)
--Method 1
Select Cardid,borrowdate from Borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' 2014 '
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' mm ') >= ' 02 '
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' mm ') <= ' 04 ';
--Method 2
Select Cardid, borrowdate from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' 2014 '--query
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 '
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') <= ' 2014-04 ';
--Method 3
Select Cardid, borrowdate from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ') between
To_date (' 2014-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
To_date (' 2014-05-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Select Cardid,borrowdate from Borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ') between
To_date (' 2014-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and To_date (' 2014-05-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

--Query + go weight
--Keyword DISTINCT is used to return only different values.
Select distinct Cardid from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' 2014 '
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 '
and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') <= ' 2014-04 ';

Select distinct Cardid from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ') between
To_date (' 2014-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
To_date (' 2014-05-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

--3) query + de-weight + reader name and other information
SELECT * from Borrow;
With VI as
(SELECT distinct cardid from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ') between
To_date (' 2014-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
To_date (' 2014-05-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Select Cardid, name from reader where Vi.cardid=reader.cardid;

SQL Statement Learning