ITEC 3220M Using and Designing Database Systems

ITEC 3220MUsing and Designing Database Systems Instructor: Prof. Z. Yang Course Website: http://people.math.yorku.ca/~zyang/itec3220m.htm Office: Tel 3049

SQL*Loader

SQL*Loader • SQL*Loader is a bulk loader utility used for moving data from external files into an Oracle table. • Tables must exist • Loader works with the control file (file.ctl)

Loader Files • SQL*Loader takes either: • two input files – a control file and a data file or • a single input file – a control file that contains a set of dataand loads the data into a single Oracle table

Loader Files The data file contains data • each record is placed in one row, • fields can be delimited by ‘,’ or ‘|’ • can have extensions .csv or .dat The control file contains information about the data • specifies action: insert, replace, append • describes the data, indicates which tables and columns the data is to be loaded • has extension .ctl

The SQL*Loader Environment

The Log, Bad and Discard Files • The log file (.log) • records SQL*Loader's activities during a load session and contains statistics on the load, error messages for records that cause errors It is important to review the log file after a load to make sure that no errors occurred! • The bad file (.bad) • shows database errors, e.g. insert failure due to integrity constraint violation • The discard file(.dis) • holds records that do not meet selection criteria specified in the SQL*Loader control file

The Data File (ship.csv) - example “001”,”Century”,”1986”,”2000” “002”,”Galaxy”,”1989”,”1500” “003”,”Horizon”,”1992”,”1600” “004”,”Infinity”,”1995”,”2500” “005”,”Journey”,”1998”,”2500” “006”, “Mercury”,”2001”,”3000”

The Control File (ship.ctl) - example INFILE ‘ship.csv’ REPLACE INTO TABLE Ship FIELDS TERMINATED By ‘,’ OPTIONALLY ENCLOSED BY ‘”’ (shipNum, shipName, yearBuilt, capacity)

Data in the Control File - example LOAD DATA INFILE * REPLACE INTO TABLE Ship FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (shipNum, shipName, yearBuilt, capacity) BEGINDATA "001","Century","1986","2000" "002","Galaxy","1989","1500" "003","Horizon","1992","1600" "004","Infinity","1995","2500" "005","Journey","1998","2500" "006","Mercury","2001","3000"

Execution of SQL*Loader sqlldr login/password@studb10g control=ship.ctl

SQL Queries

SQL Queries • Single table query • Multiple table query • Nesting query • Using IN • Using EXISTS • Join Table

Joining Database Tables • Ability to combine (join) tables on common attributes is most important distinction between a relational database and other databases • Join is performed when data are retrieved from more than one table at a time • Join is generally composed of an equality comparison between the foreign key and the primary key of related tables

Examples • SELECT Order_Num FROM ORDERS WHERE Order_Num IN (SELECT Order_Num FROM ORDER_LINE WHERE Part_Num =1234;

Examples (Cont’d) • SELECT Order_Num FROM ORDERS WHERE EXISTS (SELECT * FROM ORDER_LINE WHERE ORDERS.Order_Num = ORDERLINE.Order_Num AND Part_Num =1234;

Examples (Cont’d) • SELECT S.Last, S.First, C.Last, C.First FROM SALES_REP S, CUSTOMER C WHERE S.Srep_Num = C. Srep_Num

SQL Exercise • Write SQL code that will create the relations shown. Assume the following attribute data types: • Student_ID: integer • Student_Name: 25 characters • Faculty_ID: integer • Faculty_Name: 25 characters • Course_ID: 25 characters • Course_Name: 15 characters • Date_Qualified: date • Section_ID: integer • Semester: 7 characters

Faculty_ ID Student_ ID Student_ Name Course_ID Date_ Qualified 2143 38214 Letersky ISM3112 9/1988 54907 3467 Altvater ISM4212 9/1995 66324 3467 Aiken ISM4930 9/1996 70542 4756 Marra ISM3113 9/1991 4756 ISM3112 9/1991 SQL Exercise (Cont’d) STUDENT (Primary key: Student_ID) IS_QUALIFIED (Primary key: Faculty_ID, Course_ID)

Faculty_ID Section_ID Faculty_Name Course_ID 2143 2712 Birkin ISM3113 2713 3467 Berndt ISM3113 4756 2714 Collins ISM4212 2715 ISM4930 SQL Exercise (Cont’d) FACULTY (Primary key: Faculty_ID) SECTION (Primary key: Section_ID)

Student_ID Course_ID Section_ID Course_ Name Semester 38214 ISM3113 2714 Syst Analysis I - 2001 ISM3112 54907 2714 Syst Design I - 2001 54907 ISM4212 2715 Database I - 2001 ISM4930 66324 2713 Networking I - 2001 SQL Exercise (Cont’d) COURSE ((Primary key: Course_ID) IS_REGISTERED (Primary key: Student_ID, Section_ID)

SQL Exercise (Cont’d) • Write SQL queries to answer the following questions: • Display the course ID and course name for all courses with an ISM prefix. • Display all the courses (Course_Name) for which Professor Berndt has been qualified. • Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930? • How many students are enrolled in section 2714 during semester I – 2001? • Which students were not enrolled in any courses during semester I – 2001?

Lab Instruction • Login to sit.yorku.ca • Start Oracle SQL*PLUS environment by typing the following command: sqlplus • When prompted for the username/password enteryour_username@studb10g (where your_usernameis your AML username)at the username prompt and your AML password at the password prompt.

Lab Tips • To list all tables you have in your Oracle account use the following SQL command: select table_name from user_tables; • To describe a given Oracle table use the following Oracle environment command (note that this is not an SQL command): desc tablename (where tablename is the name of the table that you have in your account)

SQL Plus Editing Commands • Add text at end of current line: A text • Change current line: type the line number • Change text in current line: C/old/new • Insert a line following current line: I

ITEC 3220M Using and Designing Database Systems