S380F - SQL Statement And Notes

Some sql line for checking:

• database base information:
  dbname: account
  name:nbuser
  password:nbuser

• create table statement

database base information:
dbname: account
name:nbuser
password:nbuser


CREATE TABLE "USER" (
    "NAME" VARCHAR(20) not null primary key,
    "PASSWORD" VARCHAR(20),
    "ISADMIN" BOOLEAN
);

INSERT INTO "USER" VALUES ('keith', '123','true');
INSERT INTO "USER" VALUES ('jacky', '321','false');



CREATE TABLE COURSEAIM (
 id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
 COURSEAIMSUBJECT VARCHAR(255) NOT NULL,
 PRIMARY KEY (id)
);

CREATE TABLE ANNOUNCEMENT(
TITLE VARCHAR(50) NOT NULL,
DATE VARCHAR(20) NOT NULL,
SUBJECT VARCHAR(50) NOT NULL);


CREATE TABLE course (
 id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
 name VARCHAR(255) NOT NULL,
 subject VARCHAR(255) NOT NULL,
 body VARCHAR(255) NOT NULL,
 PRIMARY KEY (id)
);
CREATE TABLE attachment (
 id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
 filename VARCHAR(255) DEFAULT NULL,
 content_type VARCHAR(255) DEFAULT NULL,
 content BLOB DEFAULT NULL,
 course_id INTEGER DEFAULT NULL,
 PRIMARY KEY (id),
 FOREIGN KEY (course_id) REFERENCES course(id)
);

CREATE TABLE QUIZ (
 id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
 title VARCHAR(50) NOT NULL,
 q1 VARCHAR(50) NOT NULL,
 c1 VARCHAR(50) NOT NULL,
 c2 VARCHAR(50) NOT NULL,
 c3 VARCHAR(50) NOT NULL,
 a1 VARCHAR(50) NOT NULL,
 q2 VARCHAR(50) NOT NULL, 
 c4 VARCHAR(50) NOT NULL,
 c5 VARCHAR(50) NOT NULL,
 c6 VARCHAR(50) NOT NULL,
 a2 VARCHAR(50) NOT NULL,
 PRIMARY KEY (id)
);

• update statement for reference

SELECT *
FROM Quiz
WHERE TITLE='Quiz2';

UPDATE NBUSER."USER"
SET isADMIN=false
WHERE Name='a'; 

• Lab Notes:

//Data Access Service -> different version

Data Access Object (DAO)
+User(Data transfer object /DTO)-> getter setter function

JDBC (java DB / Apache Derby)

//get connection
@Autowired
DataSource dataSource;