Database 8/6/2023

inner join, left  join, right join
full-join, equi-join, natural join, cross join
union, intersect
view, index

p.4
Database is a collection of structured data in a computer system
It facilitates the storage and retrieval of data

Data management:
1. retrieval of information
2. update: insert, delete, modify data
3. modify database structure

Application:
1. Ordering system
2. ATM
3. Flight reservation system

Advantage of online Library management system

p.6
Database structure
1. Field
2. Record
3. Table

p.7
DBMS: Access(GUI), MySQL(CLI), MS SQL server, Oracle

p.8
SQL Structured Query Language

p.9
show databases

p.10
create database xxx
use xxx

create table yyy (...)

p.11
show tables

create table teacher(
	tid	char(4),
	name	char(16),
	class	char(2)
)

p.12 Activity 1.3

p.13 Data type
create table student(
	sid	char(5),
	name	varchar(40),
	class	char(2),
	cno	int,
	credit	float,
	dob	date,
	registered	boolean
)

p.14 Example 1.1

create table events(
	eid	int,
	ename	varchar(255),
	edate	date,
	fee	float,
	finished	boolean
)

insert into events values
	(83313,'History Tour','2036-04-15',40,1),
	(83314,'Science Day','2036-05-28', 10.5,1),
	(83315,'Swimming Gala','2036-06-08', 0,0),
	(83316,'Gardening Fun','2036-07-13', 59.9,0);

p.16 CP 1.1

p.17 insert into ... values ...

insert into student (sid, name, class, cno) values
	('03493','Ada','1A',1)

insert into student values
	('03496','Clem','1B',15)

insert into student (cno, name, sid, class) values
	(28,'Eva','03505','1B')

p.18
insert into student (sid, name, class) values
	('03506','Gabe','1C')

insert into student (sid, name, class, cno) values
	('03510','Finn','1B',7),
	('03513','Dio','1C',13),
	('03517','Ben','1A',9);

p.19
select ... from ...

select * from student
select name, cno from student

select name, cno from student where class='1B'

p.20

select name, cno from student where class='1B' order by cno
select name, cno from student where class='1B' order by cno desc

p.21
select distinct class from student

p.22 Example 1.2
INSERT INTO EVENT (EID, ENAME, EDATE, FEE, FINISHED) VALUES
	(83313, 'History Tour', '2036-04-151, 40, 1),
	(83315, 'Swimming Gala', '2036-06-08', 0, 0)

SELECT *  FROM EVENT WHERE FINISHED = 1

SELECT ENAME FROM EVENT ORDER BY FEE DESC

p.23 CP 1.2

p.26
update student set name='Bob' where sid='03517'
update student set name='Bob'

p.27
update student set name='Buzz', class='1B' where sid='03517'

delete from student where class='1B'
delete from student

p.28
drop table student

drop database school

p.29 Example 1.3

create table product(
	pid	char(6),
	pname	varchar(80),
	stock	int,
	price	float,
	expiry	date
)

insert into product values
	('SN2359','Smart Candy',138,14.5,'2042-11-24')

update product set stock=0 where pid='BA1100'

p.31 CP 1.3

p.32
Primary key is a field (some fields)
unique data to identify each record
non-null

hkid, ename, sex, dob, bank A/C, email?

p.33
create table student(
	sid	char(5)		primary key,
	name	varchar(40),
	class	char(2),
	cno	int
)

error 1:
insert into student (sid, name, class, cno) values
	('03493','Ada','1A',1),
	('03496','Cle','1B',15),
	('03493','Eva','1B',28);

error 2:
insert into student (name, class, cno) values
	('Gabe','1C',15)

error 3:
insert into student (sid, name, class, cno) values
	('03506','Gab','1C',15),
	('03513','Dio','1C',13)	/* ok */

update student set sid='03506' where name='Dio'	/* error */

p.34 composite key

p.35 foreign key

customer order - employee
1.
create table products(
	productID	char(4) primary key,
	productName	varchar(100),
	unitPrice	float,
	qtyOnHand	int
)

2.
create table orders(
	orderNo		char(7) primary key,
	orderDate	date,
	customerID	char(4),
	employeeID	char(4),
	foreign key (customerID) references customers (customerID),
	foreign key (employeeID) references employers (employerID)
)

3.
create table orderItems(
	orderNo		char(7),
	productID	char(4),
	qty		int,
	primary key (orderNo, productID),
	foreign key (orderNo)   references orders   (orderNo),
	foreign key (productID) references products (productID)
)

p.37 constraints
	primary key
	not null
	unique
	foreign key
	default
	check

create table student(
	sid	char(5)		primary key,
	name	varchar(40)	not null,
	class	char(2),
	cno	int	unique,
	house	char(1),
	score	int	default 0,
	foreign key (house) references houseList (house),
	check (score>=0),
	unique (class, no)
)

p.38 auto_increment
CREATE TABLE NOTICE(
	NID	INT	PRIMARY KEY	AUTO_INCREMENT,
	AUTHOR	VARCHAR(255),
	CONTENT	VARCHAR(255),
	CREATETIME	DATETIME
)

INSERT INTO NOTICE (AUTHOR, CONTENT, CREATETIME) VALUES
	('Zack', 'Good morning',   '2026-06-02 20:26:26'),
	('Yen',  'Goodbye',        '2026-06-13 19:16:36'),
	('Xeno', 'Good afternoon', '2026-06-30 17:20:18')

NOTICE
NID	AUTHOR	CONTENT	CREATETIME
1	Zack	Good morning	2026-06-02 20:26:26
2	Yen	Goodbye		2026-06-13 19:16:36
3	Xeno	Good afternoon	2026-06-30 17:20:18

p.39 Example 1.4
(a) gid
(b) pcode and genreid
(c) create table game(
	pcode	char(2) not null,
	gcode	char(3) not null,
	gid	char(5) primary key,
	gname	varchar(255) not null,
	genreid	int,
	price	float,
	foreign key (pcode) references publisher (code),
	foreign key (genreid) references genre (id)
)
(d) gid  pcode+gcode

p.41 CP 1.4
1(a) shop.SID, manager.Phone
(b) create table shop(
	sid	int	primary key,
	sname	varchar(255)	unique	not null,
	open	time	not null	default '09:00',
	phone	char(8)	not null,
	sex	char(1),
	foreign key (phone) references manager (phone),
	check (open >= '08:00')
)
(c) shop.sex

p.42
alter table studnet rename student

alter table student add name varchar(40)

p.43
create table student(
	sid	char(5),
	name	varchar(40)	default 'John',
	class	char(2),
	cno	int
)

alter table student add name varchar(40) default 'John'

alter table student rename lame to name

p.44
alter table student modify sid int

alter table student drop cno

p.45
alter table student add primary key (sid)

alter table student modify sid char(5) primary key

alter table student drop primary key

p.46
alter table student
	add foreign key (class) references classList(class)

show create table student

alter table student drop foreign key ....

p.47 other constraints

p.48 Example 1.5
(a) ALTER TABLE USER ADD MTYPE CHAR (7) DEFAULT 'Regular1
(b) UPDATE USER SET MTYPE = 'Bronze'
WHERE MTYPE = 'Premium'
(C) (i) SELECT *  FROM USER ORDER BY MTYPE [ASC]
(ii) Bronze, Gold, Regular and Silver.
(d) ALTER TABLE USER MODIFY MTYPE INT
(e) ALTER TABLE USER ADD FOREIGN KEY (MTYPE) REFERENCES MTYPELIST(TID)

p.67
STUDENT
SID	NAME	CLASS	DOB	SCORE1	SCORE2
03369	Hans	2C	2012-05-18	82	77
03405	Ivy	2A	2012-04-16	46	45
03410	Jean	2B	2011-11-08	74	65
03493	Ada	1A	2013-09-21	0	0
03496	Clem	1B	2013-10-08	81	89
03505	Eva	1B	2013-03-19	62	83
03506	Gabe	1C	2013-08-23	66	47
03510	Finn	1B	2013-01-24	53	35
03513	Dio	1C	2013-06-25	58	67
03517	Ben	1A	2013-11-17	73	86

SELECT NAME, SCORE1, SCORE2 FROM STUDENT
WHERE SCORE1 < 60

SELECT NAME, SCORE1, SCORE2 FROM STUDENT
WHERE NOT SCORE1 < 60

SELECT NAME, SCORE1, SCORE2 FROM STUDENT
WHERE SCORE2 < 60

SELECT NAME, SCORE1, SCORE2 FROM STUDENT
WHERE SCORE1 < 60 AND SCORE2 < 60

SELECT NAME, SCORE1, SCORE2 FROM STUDENT
WHERE SCORE1 < 60 OR SCORE2 < 60

p.70
SELECT SID, NAME FROM STUDENT
WHERE CLASS IN ('1A','1B','1C')

SELECT SID, NAME FROM STUDENT
WHERE CLASS='1A' OR CLASS='1B' OR CLASS='1C'

SELECT SID, NAME FROM STUDENT
WHERE DOB BETWEEN '2013-08-23' AND '2013-09-22'

p.71
SELECT SID, NAME FROM STUDENT
WHERE NAME LIKE '%an%'
:

p.75
(a) SELECT ENAME FROM EMPLOYEE
WHERE DOB < '1966-01-01' OR TENURE >= 20

(b) SELECT EID FROM EMPLOYEE
WHERE TEAM <> 'D'  AND ROOM BETWEEN 202 AND 208

SELECT EID FROM EMPLOYEE
WHERE NOT TEAM = 'D' AND ROOM >= 202 AND ROOM <= 208

(C) SELECT EID, DOB FROM EMPLOYEE
WHERE TEAM IN ('A','C','E') AND LEAVE > 10

SELECT EID, DOB FROM EMPLOYEE
WHERE (TEAM = 'A' OR TEAM = 'C OR TEAM = 'E')

(d) SELECT DISTINCT TEAM FROM EMPLOYEE
WHERE EID LIKE 'A%X'

(e) SELECT ENAME FROM EMPLOYEE
WHERE NOT ENAME LIKE 'Ng %'

(f) SELECT ENAME FROM EMPLOYEE
WHERE TEAM IS NULL

p.76 CP 2.2
1(a) select * from student where dob >= '2013-05-05'
1(b) select * from student where score1<60 or score1>80
1(c) select * from student where class not in ('1B','2C')
1(d) select * from student where score2 between score1 and score3

2(a) select BN from BK where BN like 'the%'
2(b) select BN from BK where BN like '%tt%'
2(c) select BN from BK where BN like '%t%t%'
2(d) select BN from BK where BN like '%t_'

p.79
(a) UPDATE USER SET USERNAME = TRIM(USERNAME)
(b) SELECT USERNAME FROM USER ORDER BY MONTH(DOB) [ASC]
(c) SELECT UID FROM USER WHERE MONTH(DOB)*100+DAY(DOB) BETWEEN 823 AND 922
(d) SELECT USERNAME FROM USER WHERE MID(CLASS,1,1) = MID(OLDCLASS,1,1)
(e) SELECT UID, PHONE FROM USER WHERE LENGTH(PHONE) < 8
(f) SELECT MID (UID,LENGTH(UID)-3,4) FROM USER

p.80 CP 2.3
(a) select fname from film
	where year(release) in (2012,2016,2021)
(b) select fname from film
	order by length(fname)
(c) select mid(fid, length(fid)-2, 2) from film
(d) select length(fname)-length(trim(fname))
	from film where fid='2468'
(e) update film set fname = mid(upper(fname),1,length(fname)-9)
	where mid(fname,length(fname)-8,9) = 'uppercase'

p.93 CP 2.4
(a) memid (primary key), typeid (foreign key)
(b) update member set typeid = 3
	where typeid=2 and year(regdate)<=2018
(c) update member set credit = credit+1000
	where month(dob)=8 or day(dob) in (8,18,28)
(d) select typeid, sex, avg(credit) from member
	group by typeid, sex
(e) select typeid, max(regdate) from member
	group by typeid
(f) select month(regdate), count(*) from member
	where typeid=0 group by month(regdate)

p.101
create table movie(
	movid	int	primary key,
	mname	varchar(255),
	dname	varchar(50),
	release	date
)
MOVID	MNAME	DNAME	RELEASE
1	The Manhunter	Ada Kwong	2023-04-04
2	Postman	Bill Smith	2025-09-15
3	Don The Mantle	Cheve Lee	2024-08-27
4	The Manic	Bill Smith	2025-06-02
5	The Man on Fire	Cheve Lee	2025-03-16
6	Manhattan	Cheve Lee	2023-12-24
7	The Man	Ada Kwong	2022-09-09
8	Mandy The Mantis	Cheve Lee	2024-01-08

p.108
create table student(
	sid	char(5) primary key,
	name	char(10),
	sex	char(1),
	class	char(2),
	score	int
)

Insert into student values
	('03493','Ada','F','3A',0),
	('03496','Clem','F','3B',89),
	('03505','Eva','F','3B',83),
	('03506','Gabe','M','3C',47),
	('03510','Finn','M','3B',35),
	('03513','Dio','M','3C',67),
	('03517','Ben','M','3A',86);

p.108
create table club(
	cid	int	primary key,
	name	char(20),
	room	int,
	teach	char(10)
)

Insert into club values
	(1,'Chess Club','501','Mr. Chan'),
	(2,'Art Club','402','Mrs. Lee'),
	(3,'English Club','203','Mr. Suen'),
	(4,'Science Club','502','Mr. Wong');

Create table SC(
	sid	char(5),
	cid	int
)

Insert into SC values
	('03493',2),
	('03496',1),
	('03496',2),
	('03496',3),
	('03505',4),
	('03506',4),
	('03510',1),
	('03513',4);

p.120 CP 3.1

p.155
(a) SELECT NAME FROM BREED WHERE ID IN
(SELECT BID FROM CAT, HOTEL h WHERE HID = h.ID AND h.NAME = 'Alpha')

(b) SELECT c.NAME FROM CAT c, BREED b, HOTEL h
WHERE BID = b.ID AND HID = h.ID AND
h.NAME = 'Beta' AND c.WEIGHT < b.WEIGHT

(c) SELECT NAME FROM HOTEL h WHERE
CAPACITY < (SELECT COUNT(c.ID) FROM CAT c WHERE HID = h.ID)

(d) SELECT c.NAME FROM CAT c, BREED b
WHERE BID = b.ID AND b.NAME = 'Ragdoll' AND
AGE > (SELECT AVG(c2.AGE) FROM CAT c2 WHERE c2.BID = b.BID)

(e) SELECT NAME FROM HOTEL WHERE ID NOT IN
(SELECT HID FROM CAT, BREED b WHERE BID = b.ID AND b.NAME = 'Ragdoll')

(f) It counts the number of breeds within each hotel.

p.168
(a) SELECT BIB FROM FOOTBALL
UNION ALL
SELECT BIB FROM RELAYRACE
ORDER BY BIB

(b) SELECT PID FROM FOOTBALL WHERE TEAM = 'A'
UNION
SELECT PID FROM RELAYRACE WHERE TEAM = 'A'

(c) SELECT NAME  FROM PARTICIPANT WHERE GENDER = 'F' AND ID IN
(SELECT PID FROM FOOTBALL EXCEPT SELECT PID FROM RELAYRACE)

(d) SELECT NAME  FROM PARTICIPANT WHERE AGE >= 22 AND ID NOT IN
(SELECT PID FROM FOOTBALL INTERSECT SELECT PID FROM RELAYRACE)

p.169 cp3.4
(a) SELECT ISBN FROM WBS WHERE PRICE < 100
EXCEPT
SELECT ISBN FROM HBS

(b) SELECT BNAME, AUTHOR FROM BOOK
WHERE ISBN IN
	(SELECT ISBN FROM HBS
	INTERSECT
	SELECT ISBN FROM WBS)

(c) SELECT BNAME, PRICE FROM HBS h, BOOK b
	WHERE h.ISBN = b.ISBN AND PAGE >= 300
UNION
SELECT BNAME, PRICE FROM WBS w, BOOK b
	WHERE w.ISBN = b.ISBN AND PAGE >= 300
ORDER BY BNAME

(d) SELECT ISBN FROM HBS
	WHERE ISBN NOT IN (SELECT ISBN FROM BOOK)
UNION
SELECT ISBN FROM WBS
	WHERE ISBN NOT IN (SELECT ISBN FROM BOOK)

Alternative:
SELECT ISBN FROM HBS
UNION
	SELECT ISBN FROM WBS
	EXCEPT
	SELECT ISBN FROM BOOK

p.173
NAME	CLASS	SCORE1	SCORE2
Hans	3C	82	67	
Ivy	3A	65	56	
Jean	3B	74	65	
Ada	3A	0	81	
Clem	3B	71	87	
Eva	3B	62	75	
Gabe	3C	66	59	
Finn	3B	43	71	
Dio	3C	64	45	
Ben	3A	88	74	

create table student(
	name	char(10),
	class	char(2),
	score1	int,
	score2	int
)

Insert into student values
	('Hans','3C',82,67),
	('Ivy','3A',65,56),
	('Jean','3B',74,65),
	('Ada','3A',0,81),
	('Clem','3B',71,87),
	('Eva','3B',62,75),
	('Gabe','3C',66,59),
	('Finn','3B',43,71),
	('Dio','3C',64,45),
	('Ben','3A',88,74);

CLASS
CLASS	TEACH	LANG
3A	Mr Chan	Chinese
3B	Mrs Lee	Chinese
3C	Mr Suen	English

create table class(
	CLASS	char(2),
	TEACH	char(20),
	LANG	char(10)
);

insert into class values
	('3A','Mr Chan','Chinese'),
	('3B','Mrs Lee','Chinese'),
	('3C','Mr Suen','English');

CLUB
CID	NAME
1	Chess Club
2	Art Club
3	English Club
4	Science Club
5	Economy Club
6	Debate Club

create table club(
	CID	int,
	NAME	char(20)
);

insert into club values
	(1,'Chess Club'),
	(2,'Art Club'),
	(3,'English Club'),
	(4,'Science Club'),
	(5,'Economy Club'),
	(6,'Debate Club');


p.174
SELECT NAME FROM STUDENT s
	LEFT JOIN SC ON s.SID = SC.SID
	GROUP BY s.SID
	HAVING COUNT(CID) = (...)

SELECT COUNT(*) FROM SC
	GROUP BY SID
	ORDER BY COUNT(*) DESC
	LIMIT 1;

CREATE OR REPLACE VIEW AS FSCORE
p.175

CREATE VIEW SCORESHEET AS
	SELECT S.SID, NAME, SEX, s.CLASS, TEACH, LANG, SCORE1*0.4 + SCORE2*0.6 AS FSCORE
		FROM STUDENT s, CLASS cl
		WHERE s.CLASS = cl.CLASS

SELECT * FROM SCORESHEET