Database_61_SQL 활용 실습 1

    -- PRACTICE 1
--  DEPARTMENTS 테이블의 구조를 표시하고 테이블의 모든 데이터를 선택
DESC departments;
    /*
    이름                          널?              유형           
    ---------------           --------         ------------ 
    DEPARTMENT_ID       NOT NULL     NUMBER(4)    
    DEPARTMENT_NAME  NOT NULL     VARCHAR2(30) 
    MANAGER_ID                              NUMBER(6)    
    LOCATION_ID                              NUMBER(4)    
    */

SELECT * FROM departments;  

    
-- EMPLOYEES 테이블의 구조를 표시하시오. 사원 번호가 가장 앞에 오고 이어서 각 사원의 이름, 업무 코드, 입사일이 오도록 질의를 작성하시오. HIRE_DATE 열에 STARTDATE라는 별칭을 지정
DESC employees;
    /*
    이름                      널?                      유형           
    --------------          --------            ------------ 
    EMPLOYEE_ID         NOT NULL        NUMBER(6)    
    FIRST_NAME                                VARCHAR2(20) 
    LAST_NAME           NOT NULL        VARCHAR2(25) 
    EMAIL                   NOT NULL       VARCHAR2(25) 
    PHONE_NUMBER                          VARCHAR2(20) 
    HIRE_DATE             NOT NULL       DATE         
    JOB_ID                  NOT NULL       VARCHAR2(10) 
    SALARY                                     NUMBER(8,2)  
    COMMISSION_PCT                       NUMBER(2,2)  
    MANAGER_ID                              NUMBER(6)    
    DEPARTMENT_ID                          NUMBER(4)    
    */

SELECT employee_id, last_name, job_id, hire_date
FROM employees;

    
-- EMPLOYEES 테이블의 업무 코드를 중복되지 않게 표시
SELECT DISTINCT job_id 
FROM employees;

-- 2번의 명령문을 복사하여 머리글을 각각 Emp #, Employee, Job 및 Hire Date로 명명한 다음 질의를 다시 실행
SELECT employee_id "Emp #", last_name "Employee", job_id "Job", hire_date "Hire Date"
FROM employees;

-- 업무 ID와 이름을 연결한 다음 쉼표 및 공백으로 구분하여 표시하고 열 이름을 Employee and Title로 지정하시오
SELECT last_name || ' ' || job_id AS "Employee and Title"
FROM employees;