SQL을 마스터 해보자 ( INSERT, ALTER, DELETE, UPDATE, SELECT, CREATE, GRANT,JOIN,REVOKE,CONCACT )

참고 :

CREATE TABLE PATIENT ( /* All Patient */
PatientID VARCHAR(12) NOT NULL, /* Patient ID */
PName VARCHAR(50) NOT NULL, /* Patient name */
DOB DATE NOT NULL, /* Patient Date Of Birth */
CONSTRAINT PATIENT_PKEY PRIMARY KEY (PatientID) );
CREATE TABLE DOCTOR ( /* All Doctors */
DoctorID VARCHAR(12) NOT NULL, /* Doctor ID */
DName VARCHAR(50) NOT NULL, /* Doctor name */
SPECIALTY VARCHAR(200) NOT NULL, /* Specialty of Doctor */
YearOfExp DECIMAL(2) NOT NULL, /* Doctor Years-of-Experience */
CONSTRAINT DOCTOR_PKEY PRIMARY KEY (DoctorID) );
CREATE TABLE DRUG ( /* All Drugs */
DrugName VARCHAR(50) NOT NULL, /* Drug name */
UnitPrice DECIMAL(7,2) NOT NULL, /* Unit Price of Drug */
CONSTRAINT DRUG_PKEY PRIMARY KEY (DrugName),
CONSTRAINT DRUG_CHECK_UNITPRICE CHECK (UnitPrice > 0) );
CREATE TABLE PRESCRIPTION ( /* Prescription issues to Patient */
PatientID VARCHAR(12) NOT NULL, /* Patient ID */
DoctorID VARCHAR(12) NOT NULL, /* Doctor ID */
DrugName VARCHAR(50) NOT NULL, /* Drug Name */
PrescriptionDate DATE NOT NULL, /* Date of prescription */
Quantity DECIMAL(5,2) NOT NULL, /* Quantity of Drug */
CONSTRAINT PRESCRIPTION_PKEY PRIMARY KEY (PatientID, DoctorID, DrugName, 
PrescriptionDate),
CONSTRAINT PRESCRIPTION_FKEY1 FOREIGN KEY (PatientID) REFERENCES PATIENT 
(PatientID),
CONSTRAINT PRESCRIPTION_FKEY2 FOREIGN KEY (DoctorID) REFERENCES DOCTOR 
(DoctorID),
CONSTRAINT PRESCRIPTION_FKEY3 FOREIGN KEY (DrugName) REFERENCES DRUG (DrugName),
CONSTRAINT PRESCRIPTION_CHECK_QUANTITY CHECK (QUANTITY > 0) );

 

 

 

문제 1)

1. 환자 항목에 주소, 전화번호 그리고 예약 가능일 추가

2. 전화번호 항목은 10개의 숫자로 제한하여야 하며, 주소와 예약 가능일은 50개의 영숫자로 제한

 

ALTER TABLE PATIENT ADD COLUMN PatientPhone INT (10) NOT NULL;
ALTER TABLE PATIENT ADD COLUMN PatientAddress VARCHAR(50) NOT NULL;
ALTER TABLE PATIENT ADD COLUMN PatientNOK VARCHAR(50) NOT NULL;

ALTER TABLE 테이블이름 ADD, DROP 등 명령어 COLUMN 컬럼명 INT , VARCHAR 컬럼의 타입 NOT NULL;

ALTER 를 이용해서 PATIENT 테이블에 COLUMN을 추가, 숫자이기 때문에 INT를 이용해서 10개의 숫자로 제한, 50개의 영숫자는 VARCHAR을 이용해서 50개로 제한.

 

문제 2)

1. PatientID 항목을 영숫자에서 숫자로 편집, 숫자들의 범위는 0 에서 99999사이의 숫자여야만함 

-- PERSCRIPTION 테이블에 있는 FOREIGNKEY 드랍 
ALTER TABLE PRESCRIPTION DROP CONSTRAINT PRESCRIPTION_FKEY1;
ALTER TABLE 테이블이름 DROP 명령어 CONSTRAINT 컬럼명;

-- PATIENT 테이블에 있는 PatientID 수정하기
ALTER TABLE PATIENT
MODIFY PatientID INT NOT NULL CHECK (PatientID BETWEEN 0 AND 99999);
ALTER 테이블명 MODIFY 유형변경 PatientID 컬럼명 CHECK 체크 (범위)

-- PERSCRIPTION 테이블에 있는 PatientID 수정하기
ALTER TABLE PRESCRIPTION
MODIFY PatientID INT NOT NULL;
ALTER 테이블명 MODIFY 유형변경 PatientID 컬럼명 INT 

-- PERSCRIPTION 테이블에 있는 FOREIGNKEY 다시 추가하기
ALTER TABLE PRESCRIPTION
ADD CONSTRAINT PRESCRIPTION_FKEY1 FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID);
ALTER 테이블명 ADD 제약조건 CONSTRAINT 제약조건명 PRESCRIPTION_FKEY1 컬럼명

 

문제 3)

1. 문제2를 참고하여서 필수 항목 추가하기, 모든 값들의 범위는 0에서 99999사이여야함

 

ALTER 테이블명 ADD/DROP/MODIFY 제약조건 PatientID 컬럼명

 

ALTER 테이블명 ADD/DROP/MODIFY 제약조건 PatientID 컬럼명
ALTER TABLE PATIENT
MODIFY PatientID INT NOT NULL CHECK (PatientID BETWEEN 0 AND 99999);

ALTER 테이블명 CONSTRAINT 제약조건 CHECK_PATIENTID_RANGE CHECK (범위)
ALTER TABLE PRESCRIPTION
ADD CONSTRAINT CHECK_PATIENTID_RANGE CHECK (PatientID BETWEEN 0 AND 99999);

CHECK를 사용할때에는 무조건 CONSTRAINT를 이용

 

CONSTRAINT를 이용하는 제약조건 리스트

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. DEFAULT

 

문제 4)

1.SECIALTY 항목을 지우고 QUALIFICATIONS 리는 항목을 Doctor 테이블에 추가, 새로운 항목은 영숫자 30자를 사용 할 수 있게 제한.

-- SECIALTY 항목 드랍하기
ALTER TABLE DOCTOR
DROP COLUMN SPECIALTY;

-- Qualifications 항목 추가하기
ALTER TABLE DOCTOR
ADD Qualifications VARCHAR(30) NOT NULL;

 

문제 5)

1. Daniel 이라는 새로운 데이터를 추가 (Patient ID:10011) 출생은 1965년 8월9일 그리고 담당 의사는 Christopher Teo (Doctor ID: 90002) 환자가 복용 해야하는 약은 Aspirin 2.00 2018년 5월 5일로 추가

 

INSERT INTO 테이블명 (칼럼) VALUES (값)  칼럼의 값과 VALUES 의 값은 항상 동일해야함

INSERT INTO 테이블명 (칼럼) VALUES (값)
-- Daniel 추가
INSERT INTO PATIENT (PatientID, PName, DOB)
VALUES (10011, 'Daniel', '1965-08-09');

-- Daniel의 PRESCRIPTION 추가
INSERT INTO PRESCRIPTION (PatientID, DoctorID, DrugName, PrescriptionDate, Quantity)
VALUES (10011, 90002, 'Aspirin', '2018-05-05', 2.00);

 

 

문제  6)

1. 의사 Robert Tay에 대한 데이터들을 다 지워야함

 

DELETE FROM [테이블 명] WHERE [조건]

 

DELETE FROM 테이블 명 WHERE 조건

DELETE FROM PERSCRIPTION WHERE IN (SELECT DoctorID FROM DOCTER WHERE DName = 'Robert  Tay');

DELETE FROM DOCTOR WHERE (DName = 'Robert Tay');

 

문제 7)

1. 약 Cyramza의 가격이 1050으로 바뀜

 

UPDATE [테이블] SET [열] = [변경값] WHERE [조건]

 

UPDATE [테이블] SET [열] = [변경값] WHERE [조건]

UPDATE DRUG SET UnitPrice = 1050 WHERE DrugName = 'Cyramza';

 

문제 8)

1. 의사중 Neurology가 전문이고 20년 이상의 경력이 있는 의사를 불러내라

 

SELECT [열] FROM [테이블 이름] WHERE [조건]

 

SELECT [열] FROM [테이블 이름] WHERE [조건]

SELECT DName
FROM DOCTOR
WHERE SPECIALTY = 'Neurology' AND YearOfExp > 20;

 

문제 9)

1. 약 Comtan을 복용하고 있는 환자의 이름을 불러내라

 

SELECT 선택할 컬럼
FROM 1번 테이블 
JOIN 2번 테이블
ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건

 

SELECT 선택할 컬럼
FROM 1번 테이블 
JOIN 2번 테이블
ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건

SELECT DISTINCT P.PName
FROM PATIENT P
JOIN PRESCRIPTION PR 
ON P.PatientID = PR.PatientID
WHERE PR.DrugName = 'Comtan';

 

문제 10)

1. 의사들이 처방한 약의 갯수와 의사의 이름, 약의 이름 그리고 처방된 횟수를 불러내라

SELECT D.DName, PR.DrugName, 
	COUNT(*) AS TotalPrescriptions
	FROM DOCTOR D
JOIN PRESCRIPTION PR ON D.DoctorID = PR.DoctorID
GROUP BY D.DName, PR.DrugName;

 

문제 11)

1. 각 환자들에게 처방된 약의 이름을 불러내라, 단 Joshua Chua에게 처방된 약의 이름만 빼고

 

SELECT DISTINCT [컬럼명] 
FROM [테이블명] 
WHERE [조건] 
    SELECT [선택할 컬럼]
    FROM [1번 테이블 ]
    JOIN [2번 테이블]
    ON [테이블1.컬럼 = 테이블2.컬럼]
    WHERE [조건]

 

SELECT DISTINCT [컬럼명] 
FROM [테이블명] 
WHERE [조건] 
    SELECT [선택할 컬럼]
    FROM [1번 테이블 ]
    JOIN [2번 테이블]
    ON [테이블1.컬럼 = 테이블2.컬럼]
    WHERE [조건]


SELECT DISTINCT DrugName
FROM PRESCRIPTION
WHERE DrugName NOT IN (
    SELECT DrugName 
    FROM PRESCRIPTION 
    JOIN PATIENT ON PRESCRIPTION.PatientID = PATIENT.PatientID
    WHERE PName = 'Joshua Chua'
)
ORDER BY DrugName DESC;

 

문제 12)

1. 아무것도 처방하지 않은 의사의 이름을 불러내라

 

SELECT 컬럼명
FROM 테이블1 이름
LEFT JOIN 테이블2 이름
ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건

 

SELECT 컬럼명
FROM 테이블1 이름
LEFT JOIN 테이블2 이름
ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건

SELECT D.DName
FROM DOCTOR D
LEFT JOIN PRESCRIPTION PR 
ON D.DoctorID = PR.DoctorID
WHERE PR.DoctorID IS NULL;

 

문제 13)

1. doctor라는 이름으로 새로운 유저를 생성, 비밀번호는 doctor 그리고 doctor의 쿼리 생성을 50으로 제한

 

CREATE USER '유저명' IDENTIFIED BY '암호'
ALTER USER '유저명' WITH '제한'

CREATE USER 'doctor' IDENTIFIED BY 'doctor';
ALTER USER 'doctor'@'localhost' WITH MAX_QUERIES_PER HOUR 50;

 

문제 14)

1. 새로운 유저 doctor를  prescription 테이블을 수정 가능하도록 권한을 부여하여라

 

GRANT [부여할 권한]  ON [테이블] TO [계정명]

 

GRANT [부여할 권한] ON [권한을 받을 테이블] TO [계정명]

GRANT CREATE ON prescription.* TO doctor;

 

문제 15)

1. 새로운 유저 doctor를 테이블 DOCTOR, PATIENT 읽기만 가능하게 권한을 부여하여라

 

DML(SELECT, INSERT, UPDATE, DELETE )

 

GRANT [부여할 권한] ON [권한을 받을 테이블] TO [계정명] WITH GRANT OPTION;

GRANT SELECT ON prescription.DOCTOR TO 'doctor'@'localhost' WITH GRANT OPTION;
GRANT SELECT ON prescription.PATIENT TO 'doctor'@'localhost' WITH GRANT OPTION;

 

문제 16)

1.새로운 유저 doctor를 테이블 DRUG 와 컬럼 UnitPrice를 업데이트 가능하게 권한을 부여하여라

GRANT UPDATE [컬럼명] ON [테이블] TO [계정명] WITH GRANT OPTION;

GRANT UPDATE (UnitPrice) ON prescription.DRUG TO doctor WITH GRANT OPTION;

문제 17)

1. 유저 " " 의 모든 권한을 없애라

Remove all privileges

REVOKE ALL PRIVILEGES FROM [유저명];

 

문제18) 

1. CONCAT을 이용해서 특정 문장을 불러내라

SELECT CONCAT ([컬럼], 불러낼 문장) AS Message 
FROM [테이블]
WHERE [컬럼2] = [조건]

SELECT CONCAT(DName, ' with the specialty of Cardiology with ', YearOfExp, ' years of experience,
which is below 18 years of experience, need to take a special course') AS Message
FROM DOCTOR
WHERE Specialty = 'Cardiology' AND YearOfExp < 18;