【SQL开源代码栏目提醒】:网学会员为需要SQL开源代码的朋友们搜集整理了chapter10Example.sql相关资料,希望对各位网友有所帮助!
### create table
create table READER (
oid INTEGER not null,
firstname VARchar(50) not null,
lastname VARchar(50) not null,
constraint PK_PERSON primary key (oid)
);
create table BOOK (
oid INTEGER not null,
name VARchar(50) not null,
publish DATE not null,
description VARCHAR(255),
reader_oid INTEGER,
primary key (oid),
foreign key (reader_oid)
references READER(oid)
);
### insert data
INSERT INTO READER
VALUES (1, 'Jing','Ge');
INSERT INTO READER
VALUES (2, 'Anderson','Neo');
INSERT INTO READER
VALUES (3, 'Michael','Peter');
INSERT INTO BOOK
VALUES (1, 'Hibernate3','2007-06-06', 'Hibernate3开发', 1);
INSERT INTO BOOK
VALUES (2, 'JPA','2007-08-08', 'JPA开发', 2);
INSERT INTO BOOK
VALUES (3, 'EJB3','2007-09-09', 'EJB3开发', 1);
INSERT INTO BOOK
VALUES (4, 'Groovy','2007-10-10', 'Groovy开发', null);
### example
SELECT * FROM BOOK WHERE name like '%3';
SELECT DISTINCT book.READER_OID from BOOK as book where book.reader_oid is not null;
SELECT * FROM BOOK WHERE name like '%\%%';
SELECT DISTINCT book.reader_oid
FROM BOOK AS book
WHERE book.reader_oid IS NOT NULL;
SELECT DISTINCT book.reader_oid, COUNT(*) reading
FROM BOOK AS book
WHERE book.reader_oid IS NOT NULL
GROUP BY book.reader_oid;
SELECT DISTINCT book.reader_oid, COUNT(*) reading
FROM BOOK AS book
WHERE book.reader_oid IS NOT NULL
GROUP BY book.reader_oid
HAVING COUNT(*) > 1;
SELECT * FROM BOOK
ORDER BY name;
SELECT reader.firstname, reader.lastname
FROM READER
WHERE READER.oid IN (
SELECT DISTINCT book.reader_oid
FROM BOOK AS book
WHERE book.reader_oid IS NOT NULL);