[ DB / Oracle ] sql 실습

환경 : DBeaver

 

-- Create a new sequence and table
CREATE SEQUENCE music_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE music (
  music_id NUMBER PRIMARY KEY,
  music_title VARCHAR2(200) NOT NULL CHECK(REGEXP_LIKE(music_title, '^[^%^]+$')),
  artist_name VARCHAR2(100) NOT NULL,
  release_date DATE NOT NULL,
  genre VARCHAR2(50) NOT NULL,
  duration_minutes NUMBER(3) NOT NULL,
  album_name VARCHAR2(150),
  price NUMBER(5,2) NOT NULL CHECK(price >= 0)
);

-- Optionally delete existing data
-- DELETE FROM music;

-- Insert data
INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Bohemian Rhapsody', 'Queen', TO_DATE('1975-10-31', 'YYYY-MM-DD'),
        'Rock', 6, 'A Night at the Opera', 1.99);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Billie Jean', 'Michael Jackson', TO_DATE('1983-01-02', 'YYYY-MM-DD'),
        'Pop', 4, 'Thriller', 1.49);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Shape of You', 'Ed Sheeran', TO_DATE('2017-01-06', 'YYYY-MM-DD'), 'Pop', 3, '÷', 1.29);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Stairway to Heaven', 'Led Zeppelin', TO_DATE('1971-11-08', 'YYYY-MM-DD'), 'Rock', 8, 'Led Zeppelin IV', 2.49);
INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Dancing Queen', 'ABBA', TO_DATE('1976-08-15', 'YYYY-MM-DD'), 'Disco', 4, 'Arrival', 1.79);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Sweet Child o'' Mine', 'Guns N'' Roses', TO_DATE('1987-06-15', 'YYYY-MM-DD'), 'Rock', 5, 'Appetite for Destruction', 2.29);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Despacito', 'Luis Fonsi ft. Daddy Yankee', TO_DATE('2017-01-13', 'YYYY-MM-DD'), 'Reggaeton', 3, 'Vida', 1.99);

INSERT INTO music(music_id, music_title, artist_name, release_date, 
                  genre, duration_minutes, album_name, price)
VALUES (music_seq.nextval, 'Imagine', 'John Lennon', TO_DATE('1971-10-11', 'YYYY-MM-DD'), 'Rock', 3, 'Imagine', 1.49);


-- Query all data
SELECT * FROM music;

 

   (+) 문자열에 ' 따옴표를 출력하고 싶으면 두 번 적어주면 된다.

 

 

 

 

 

 

 

 

 

개인 공부 기록용입니다:)