[DB] SQL 구문 기초 - AS, Comparison, OR, BETWEEN

 

all columns, all rows

SELECT staffNo, fName, lName, address, position, sex, DPB, salary, branchNo FROM Staff;

 

speicific columns, all rows

SELECT staffNo, fName, lName, salary FROM Staff;

 

 

1) table viewing에서 모든 속성을 보려고 할 때

SELECT propertyNo FROM viewing;

 

2) 중복값을 제거하려고 할 때

SELECT DISTINCT propertyNo FROM Viewing;

3) 월별 salary를 조회할 때 (staff no., first/last name 도 함께 조회)

SELECT staffNo, fName, lName, salary/12 FROM Staff;

 

AS

결과 값에 title을 줄 때

SELECT staffNo, fName, lName, salary1/2 AS monthlySalary FROM Staff;

 

Comparison

salary가 10,000보다 큰 staff들을 나열할 때

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000;

 

OR

branch office의 주소가 London이거나 Glasgow인 곳을 나열할 때

SELECT * FROM Branch WHERE city=‘London’ OR city = ‘Glasgow’;

 

BETWEEN

1) salary가 20,000~30,000 사이인 staff들을 나열할 때

SELECT staffNo, fName, lName, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000;

2) NOT BETWEEN

SELECT staffNo, fName, lName, salary FROM Staff WHERE salary>=20000 AND salary <=30000;