생활코딩 SQL Join 수업 DB별 샘플코드

생활코딩 > DATABASE > SQL Join 수업에서 각 DB별로 사용할 수 있는 실습 예시코드 입니다.
https://opentutorials.org/course/3884/25180

PostgreSQL

DROP TABLE IF EXISTS author;
CREATE TABLE author (
  aid int NOT NULL,
  name varchar(10) DEFAULT NULL,
  city varchar(10) DEFAULT NULL,
  profile_id int DEFAULT NULL,
  PRIMARY KEY (aid)
) ;
INSERT INTO author VALUES (1,'egoing','seoul',1),(2,'leezche','jeju',2),(3,'blackdew','namhae',3);
 
DROP TABLE IF EXISTS profile;
CREATE TABLE profile (
  pid int NOT NULL,
  title varchar(10) DEFAULT NULL,
  description varchar,
  PRIMARY KEY (pid)
) ;
INSERT INTO profile VALUES (1,'developer','developer is ...'),(2,'designer','designer is ..'),(3,'DBA','DBA is ...');
 
DROP TABLE IF EXISTS topic;
CREATE TABLE topic (
  tid int NOT NULL,
  title varchar(45) DEFAULT NULL,
  description varchar,
  author_id varchar(45) DEFAULT NULL,
  PRIMARY KEY (tid)
) ;
INSERT INTO topic VALUES (1,'HTML','HTML is ...','1'),(2,'CSS','CSS is ...','2'),(3,'JavaScript','JavaScript is ..','1'),(4,'Database','Database is ...',NULL);

Oracle

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE author';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE author (
  aid number(10) NOT NULL,
  name varchar2(10) DEFAULT NULL,
  city varchar2(10) DEFAULT NULL,
  profile_id number(10) DEFAULT NULL,
  PRIMARY KEY (aid)
) ;
-- SQLINES LICENSE FOR EVALUATION USE ONLY
INSERT INTO author  SELECT 1,'egoing','seoul',1 FROM dual
 UNION ALL
 SELECT 2,'leezche','jeju',2 FROM dual
 UNION ALL
 SELECT 3,'blackdew','namhae',3 FROM dual;
 
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE profile';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE profile (
  pid number(10) NOT NULL,
  title varchar2(10) DEFAULT NULL,
  description varchar2(255),
  PRIMARY KEY (pid)
) ;
INSERT INTO profile  SELECT 1,'developer','developer is ...' FROM dual
 UNION ALL
 SELECT 2,'designer','designer is ..' FROM dual
 UNION ALL
 SELECT 3,'DBA','DBA is ...' FROM dual;
 
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE topic';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE topic (
  tid number(10) NOT NULL,
  title varchar2(45) DEFAULT NULL,
  description varchar2(255),
  author_id varchar2(45) DEFAULT NULL,
  PRIMARY KEY (tid)
) ;
INSERT INTO topic  SELECT 1,'HTML','HTML is ...','1' FROM dual
 UNION ALL
 SELECT 2,'CSS','CSS is ...','2' FROM dual
 UNION ALL
 SELECT 3,'JavaScript','JavaScript is ..','1' FROM dual
 UNION ALL
 SELECT 4,'Database','Database is ...',NULL FROM dual;