SQL 실습 - 개요 A. SQL 실습환경 i. 운영체제 : UNIX 또는 Windows 서버 데이터베이스관리시스템 : Oracle 9i 또는 10g B. SQL 문장유형 Data Manipulation Language(DML) SELECT INSERT UPDATE DELETE MERGE Data Definition Language(DDL) CREATE ALTER DROP RENAME TRUNCATE COMMENT Data Control Language(DCL) GRANT REVOKE Transaction Control COMMIT ROLLBACK SAVEPOINT 1
C. 예제테이블 2
D. 실습준비 i. 과제제출시스템에서 hr_cre_pop_2007.sql 파일과 hr_drop_2007.sql 파일 을다운로드받는다. 실습실컴퓨터 (Windows XP 환경 ) 에서실습 1. 필요한프로세스작동시키기 A. 설정 -> 제어판 -> 관리도구 -> 서비스를시작한다. i. OracleServiceORCL 서비스시작 i iv. OracleDBConsoleorcl 서비스시작 OracleOraDb10g_home1TNSListener 시작 OracleOraDb10g_home1iSQL*Plus 시작 B. Oracle의 home directory 아래의 install\portlist.ini 파일을열어서어떤 port를사용하는지확인한다. * 밑줄친부분이외의이름은설정에따라달라질수있다. 2. Scott 사용자활성화하기 3
A. Internet Explore에서주소창에 http://localhost:1156/em을입력한다. i. 로그인 1. 사용자이름 : sys 2. 암호 : open123 3. 다음으로접속 : SYSDBA i 관리 -> 사용자선택 SCOTT 사용자선택 1. 편집버튼선택 2. 암호입력 : open123 3. 암호확인 : open123 4. 상태 : 잠금해제됨선택 5. 적용버튼선택 iv. 로그아웃 3. Internet Explore에서주소창에 http://localhost:5560/isqlplus을입력한다. A. 로그인 i. 사용자이름 : scott i 암호 : open123 접속식별자 : orcl B. 스크립트로드버튼을누른다. C. 찾아보기를통해서 hr_cre_pop_2007.sql 파일을찾는다. D. 로드버튼을누른다. E. 실행버튼을누른다. 4
F. 추후에실수로예제테이블들이이상하게되면, hr_drop_2007.sql 을수행하여테이블내용을모두없앤후다시 hr_cre_pop_2007.sql 을수행하여테이블을다시생성한다. i sky.skhu.ac.kr 에서실습하기 A. SSH 의 secure shell client 프로그램실행 i. Quick Connect 버튼을누른다. 1. Host Name: sky.skhu.ac.kr 2. User Name A. s+ 학번 ( 예 : s200730245) 3. connect button을누른다. B. Oracle을사용할수있는환경만들기 ( 명령어입력 ) i. cp ~hong/.cshrc. i iv. source.cshrc cd ~oracle/demo/schema/human_resources sqlplus 1. 사용자명입력 : s+ 학번 ( 예 : s200730245) 2. 암호입력 : open123 3. start hr_cre_pop_2007.sql 4. exit v. 나중에데이터베이스샘플을다시만들고싶으면, sqlplus내에서 hr_drop_2007.sql을수행한후, 다시 hr_cre_pop_2007.sql을수행한다. C. sqlplus 사용하기 i. sky.skhu.ac.kr로로그인한후, sqlplus를바로수행하면된 5
다. E. SQL 실습내용 i. 데이터베이스개론 과목과 데이터베이스실습 (2학기개설예정 ) 과목에서 SQL 실습을나누어실시한다. 1. 데이터베이스개론 A. 데이터베이스를설계하고이를기본적으로구현하는데에목적을둔다. B. 초보적인 SELECT문, DDL, DML C. 내용 i. Retrieving Data Using the SQL SELECT Statement i iv. Restricting and Sorting Data Conversion Functions Manipulating Data v. Using DDL Statements to Create and Manage Tables vi. v Creating Index Objects Displaying Data from Multiple Tables 2. 데이터베이스실습 A. 데이터베이스를이용하여응용프로그램을작성할때필요한질의작성능력배양에목적을둔다. B. SQL문전반, Stored Procedure, DCL, SQL의진보된기능등 6
1. Retrieving Data Using the SQL SELECT Statement A. Basic SQL Statement SELECT * {[DISTINCT] column expression [alias], } FROM table; B. Example SELECT * FROM departments; SELECT department_id, location_id FROM departments; C. Writing SQL Statements i. SQL statements are not case-sensitive. i iv. SQL statements can be on one or more lines. Keywords cannot be abbreviated or split across lines. Clauses are usually placed on separate lines. v. Indents are used to enhance readability. vi. In isql*plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements. v In SQL*plus, you are required to end each SQL statement with a semicolon(;). D. Arithmetic Expressions i. +, -, *, / Examples SELECT last_name, salary, salary+300 ; SELECT last_name, salary, 12*salary+100 7
; SELECT last_name, salary, 12*(salary+100) ; E. Defining a Null Value i. A null is a value that is unavailable, unassigned, unknown, or inapplicable. i A null is not the same as a zero or a black space. Example SELECT last_name, job_id, salary, commission_pct ; iv. Null Values in Arithmetic Expressions SELECT last_name, 12*salary*commission_pct ; F. Defining Column Alias i. A Column Alias Renames a column heading Is useful with calculations Immediately follows the column name (Optional AS keyword) Required double quotation marks if it contains spaces or special characters or if it is case-sensitive Examples SELECT last_name AS name, commission_pct comm ; SELECT last_name Name, salary*12 Annual Salary ; G. Literal Character Strings 8
i. A literal is a character, a number, or a date that is included in the SELECT statement. Date and character literal values must be enclosed by single quotation marks. i iv. Each character string is output once for each row returned. Example SELECT last_name ' is a ' job_id AS "Employee Details" ; H. Duplicate Rows i. The default display of queries is all rows, including duplicate rows. Example SELECT department_id ; SELECT DISTINCT department_id ; I. Displaying Table Structure i. It s a command in SQL*Plus rather than in SQL. DESC[RIBE] tablename Example DESC employees; J. Practice You have been hired as a SQL programmer for Acme Corporation. Your first task is to create some reports based on data from the Human Resources tables. i. Your first task is to determine the structure of the DEPARTMENTS table and its contents. 9
i You need to determine the structure of the EMPLOYEES table. The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column. Save your SQL statement a file named lab_01_07.sql so that you can disperse this file to the HR department. iv. Test your query in the lab_01_07.sql file to ensure that it runs correctly. v. The HR department needs a query to display all unique job codes from the EMPLOYEES table. vi. The HR department wants more descriptive column headings for its report on employees. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. Then run your query again. v To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data from that table. Separate each output by a comma. Name the column title THE_OUTPUT. 10
11
2. Restricting and Sorting Data A. Limiting Rows Using a Selection SELECT * {[DISTINCT] column/expression[alias], } FROM table [WHERE conditions(s)]; i. The WEHRE clause follows the FROM clause. Example SELECT employee_id, last_name, job_id, department_id WHERE department_id=90; B. Character strings and Dates i. Character strings and date values are enclosed by single quotation marks. Character values are case-sensitive, and date values are formatsensitive. i iv. The default date format is DD-MON-RR. Example SELECT last_name, job_id, department_id WHERE last_name= Whalen ; 12
C. Comparison Conditions i. Example SELECT last_name, salary where salary <= 3000; D. Using the BETWEEN Condition i. Use the BETWEEN condition to display rows based on a range of values Example SELECT last_name, salary WHERE salary BETWEEN 2500 AND 3500; E. IN i. Use the IN membership condition to test for values in a list SELECT employee_id, last_name, salary, manager_id WHERE manager_id IN (100, 101, 201); F. LIKE 13
i. Use the LIKE condition to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers: % denotes zero or many characters. _ denotes one character. SELECT first_name WHERE first_name LIKE S% ; SELECT last_name WHERE last_name LIKE _o% ; SELECT employee_id, last_name, job_id WHERE job_id LIKE %SA\_% ESCAPE \ ; The ESCAPE option identifies the backslash(\) as the escape character. G. NULL i. IS NULL operator SELECT last_name, manager_id WHERE manager_id IS NULL; H. Logical Conditions 14
I. AND Operator SELECT employee_id, last_name, job_id, salary WHERE salary >= 10000 AND job_id LIKE %MAN% ; J. OR Operator SELECT employee_id, last_name, job_id, salary WHERE salary >= 10000 OR job_id LIKE %MAN% ; K. NOT operator SELECT last_name, job_id WHERE job_id NOT IN ( IT_PROG, ST_CLERK, SA_REP ); L. Precedence i. You can use parentheses to override rules of precedence. 15
SELECT last_name, job_id, salary WHERE job_id = SA_REP OR job_id= AD_PRES AND salary > 15000; M. ORDER BY Clause i. Sorting ASC: ascending order, default DESC: descending order The ORDER BY clause comes last in the SELECT statement: SELECT last_name, job_id, department_id, hire_date ORDER BY hire_date; SELECT last_name, job_id, department_id, hire_date ORDER BY hire_date DESC; SELECT last_name, job_id, salary*12 annsal ORDER BY annsal; SELECT last_name, department_id, salary ORDER BY department_id, salary DESC; N. Practice The HR department needs your assistance with creating some queries. i. Due to budget issues, the HR department needs a report that displays 16
the last name and salary of employees who earn more that $12,000. Place your SQL statements in a text file named lab_0201.sql. Run your query. Create a report that displays the last name and department number for employee number 176. i The HR departments needs to find high-salary and low-salary employees. Modify lba_02_01.sql to display the last name and salary for any employee whose salary is not in the range of $5,000 to $12,000. Place your SQL statement in a text file named lab_02_03.sql. iv. Create a report to display the last name, job ID, and start date for the employees with the last names of Matos or Taylor. Order the query in ascending order by start date. v. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name. vi. Modify lab_02_03.sql to display the last name and salary of employees who earn between $5,000 and $12,000 and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively. Resave lab_02_03.sql as lab_02_06.sql. Run the statement in lab_02_06.sql. v The HR department needs a report that displays the last name and hire date for all employees who were hired in 1994. vi Create a report to display the last name and job title of all employees who do no have manager. ix. Create a report to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of 17
salary and commissions. x. Display the last name, job, and salary for all employees whose job is sales representative or stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000. xi. Modify lab_02_06.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Resave lab_02_06.sql as lab_02_15.sql. Rerun the statement in lab_02_15.sql. 18
3. Conversion Functions i. Implicit Data Type Conversion Explicit Data Type Conversion TO_CHAR Function TO_CHAR(date, format_model ) The format model: Must be enclosed by single quotation marks In case-sensitive Can include any valid date format element Has an fm element to remove padded blanks or suppress leading zeros Is separated from the date value by a comma SELECT employee_id, TO_CHAR(hire_date, MM/YY ) Month_Hired 19
WHERE last_name= Higgins ; B. Elements of the Date Format Model i. Time elements format the time portion of the date: HH24:MI:SS AM => 15:45:32 PM Add character strings by enclosing them double quotation marks: DD of MONTH => 12 of OCTOBER C. Using the TO_CHAR Function with Dates SELECT last_name, TO_CHAR(hire_date, fmdd Month YYYY ) AS HIREDATE ; D. Using the TO_CHAR Function with Numbers 20
SELECT TO_CHAR(salary, $99,999.00 ) SALARY WHERE last_name= Ernst ; E. Using the TO_NUMBER and TO_DATE Functions SELECT last_name, hire_date WHERE hire_date=to_date( 12/24/1999, mm/dd/yyyy ); F. RR Date Format SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY ) WHERE hire_date < TO_DATE( 01-Jan-90, DD-Mon-RR ); 4. Manipulating Data 21
A. Data Manipulation Language i. A DML statement is executed when you: Add new rows to a table Modify existing rows in a table Remove existing rows from a table A transaction consists of a collection of DML statements that form a logical unit of work. B. Adding a new record INSERT INTO table [(column[, column ])] VALUES (value[, value ]); i. With this syntax, only one row is inserted at a time i iv. List values in the default order of the columns in the table. Optionally, list the columns in the INSERT clause. Enclose character and date values in single quotation marks. INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (300, 'Public Relations', 100, 1700); v. Inserting Rows with Null Values Implicit method INSERT INTO departments(department_id, department_name) VALUES (310, 'Purchasing'); Explicit method INSERT INTO departments VALUES (320, 'Finance', NULL, NULL); vi. Inserting Special Values 22
The SYSDATE function records the current date and time. INSERT INTO employees(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (340, 'Louis', 'Popp', 'LPOPP_1', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100); C. Changing Data in a Table i. Modify existing rows with the UPDATE statement: UPDATE table SET column=value [, column = value, ] [WHERE condtion]; Update more than one row at a time (if required). UPDATE employees SET department_id = 70 WHERE employee_id = 113; UPDATE copy_emp SET department_id = 110; D. Removing a Row from a Table DELETE statement DELETE [FROM] table [WHERE condition]; DELETE FROM departments WHERE department_name = 'Finance'; 23
DELETE FROM departments WHERE department_id = 320; DELETE FROM copy_emp; E. Database Transactions i. A database transaction consists of one of the following: DML statements that constitute one consistent change to the data One DDL statement One data control language(dcl) statement i Begin when the first DML SQL statement is executed End with one of the following events: A COMMIT or ROLLBACK statement issued. A DDL or DCL statement executes (automatic commit). The user exists SQL*Plus. The system crashes. iv. Advantages of COMMIT and ROLLBACK Statements You can: A. Ensure data consistency B. Preview data changes before making changes permanent C. Group logically related operations v. Controlling Transactions 24
vi. Rolling Back Changes to a Marker Create a marker in a current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. 25
v Implicit Transaction Processing An automatic commit occurs under the following circumstances: A. DDL statement is issued. B. DCL statement is issued. C. Normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK statements An automatic rollback occurs under an abnormal termination of SQL*Plus or system failure. vi State of the Data Before COMMIT or ROLLBACK The previous state of the data can be recovered. The current user can review the results of the DML operations by using the SELECT statement. Other users cannot view the results of the DML statements by the current user. The affected rows are locked; other users cannot change the data in the affected rows. ix. State of the Data After COMMMIT Data changes are made permanent in the database. The previous state of the data is permanently lost. All users can view the results. Locks on the affected rows are released; those rows are available for other users to manipulate. All savepoints are erased. x. Committing Data 26
Making the changes: Commit the changes: xi. State of the Data After ROLLBACK Discard all pending changes by using the ROLLBACK statement: A. Data changes are undone. B. Previous state of the data is restored. C. Locks on the affected rows are released. F. Statement-Level Rollback i. If a single DML statement fails during execution, only that statement is rolled back. The Oracle server implements an implicit savepoint. 27
i iv. All other changes are retained. The user should terminate transactions explicitly by executing a COMMIT or ROLLBACK statement. G. Read Consistency i. Read consistency guarantees a consistent view of the data at all times. Changes made by one user do not conflict with changes made by another user. i Read consistency ensures that on the same data: Readers do not wait for writers Writers do not wait for readers H. Practice The HR department wants you to create SQL statements to insert, update, and delete employee data. As a prototype, you use the MY_EMPLOYEE table, prior to giving the statements to the HR department. Insert data into the MY_EMPLOYEE table. i. Run the statement in the lab_08_01.sql script to build the MY_EMPLOYEE table to be used for the lab. Describe the structure of MY_EMPLOYEE table to identify the column names. i Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the INSERT clause. Do not enter all rows yet. 28
iv. Populate the MY_EMPLOYEE table with the next three rows of sample data from the preceding list. This time, list the columns explicitly in the INSERT clause. v. Confirm your addition to the table. vi. Make the data additions permanent. Update and delete data in the MY_EMPLOYEE table. v vi Change the last name of employee 3 to Drexler. Change the salary to $1,000 for all employees who have a salary less than $900. ix. Verify your changes to the table. x. Delete Betty Dancs from the MY_EMPLOYEE table. xi. x Confirm your changes to the table. Commit all pending changes. Control data transaction to the MY_EMPLOYEE table. xi Populate the MY_EMPLOYEE table with the last row of sample data from the list. xiv. xv. xvi. xv Confirm your addition to the table. Mark an intermediate point in the processing of the transaction. Empty the entire table. Confirm that the table is empty. 29
xvi Discard the most recent DELETE operation without discarding the earlier INSERT operation. xix. xx. Confirm that the new row is still intact. Make the data addition permanent. 30