1 / 16

SQL in Oracle

SQL in Oracle. Basic commands in SQL. Create Creates a table, view, domain, etc. Drop Drops a table, view, domain, etc. Insert Adds a row of data to a table or view. Delete Deletes rows of data from table. Update Changes data in a table or view.

adelio
Download Presentation

SQL in Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL in Oracle

  2. Basic commands in SQL Create Creates a table, view, domain, etc. Drop Drops a table, view, domain, etc. Insert Adds a row of data to a table or view. Delete Deletes rows of data from table. Update Changes data in a table or view. Select Retrieves rows from a table or view.

  3. Insertions • Insert a record into a table INSERT INTO students (sid, name, age, gpa) VALUES (12, ‘Mike Patton’, 19, 3.4) • Insert results of query into a table INSERT INTO tablename (attr1, attr2, …) SELECT … FROM … WHERE …;

  4. Example Employee (ssn, fname, minit, lname, address, salary) Department (dno, dname, mgrssn) Manager (ssn, fname, minit, lname, dname) INSERT INTO managers SELECT mgrssn, fname, minit, lname, dname FROM employee, department WHERE mgrssn = ssn;

  5. Basic Data Types char(size) Stores fixed-length character data, with a maximum size of 2,000. varchar(size) Stores variable-length character data, with a maximum size of 4,000. number(l) Stores numeric data for integers, where “l” stands for the number of digits. number(l,d) Stores numeric data for floats, where “l” stands for the number of digits before the decimal point, and “d” for the number of decimal digits. Date Stores dates from January 1,4712 B.C. to December 31, 9999 A.D. (e.g.: 21-NOV-1996)

  6. Basic SQL*plus Commands

  7. Getting Help • To get online help for SQL commands, type HELP at the command prompt followed by the name of the command. SQL>HELP CREATE • To get all the commands in the system: SQL>HELP COMMAND

  8. A Complete Example Step1: Execute contents in file SQL> start createall.sql Step2: see the all the tables or views SQL> select * from tab; Step3: Describe table SQL> describe customer

  9. Cont. Step4: type the SQL command to buffer SQL>select firstname, lastname, orderid 2 from customer, order_ 3 where customer.customerid = order_.customerid Step5: edit the SQL bufferSQL>edit Step6: Execute the SQL in buffer SQL> run or SQL>/

  10. Cont. Step7: Show the contents in SQL buffer SQL> list Step8: Save SQL Buffer to File SQL> save try Step9: Read buffer from File SQL> get try Step10: append SQL Buffer to the end of the File SQL> save try app

  11. Insert data into tables (data.sql) /* Customer Table */ insert into customer values(9,'Brad','Pitt','(237) 369-8524','Morgan Street','Apt. 12','Minneapolis','MN','78922'); insert into customer values(10,'Bart','Simpson','(706) 852-9874','Box 9874',NULL,'Reading','MA','96521'); /* Product Table */ insert into product values(1,'Microsoft SQL Sever 2000','Microsoft',1500,1800); insert into product values(2,'Oracle 8i Server','Oracle Corporation',7000,9500); /* Order Table */ insert into order_ values(1,to_date('12-AUG-2000'),1); insert into order_ values(2,to_date('15-JUL-2000'),6);

  12. SQL*Loader SQL*Loader loads data from external files into tables of an Oracle database.

  13. A simple control file (customer.ctl) LOAD DATA INFILE * INTO TABLE customer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (customerID, firstName, lastName, phone, address1, address2, city, state, zip) BEGINDATA 1, Zhen, Liu, (662) 325-8073, Box 1111, NULL, Mississippi State, MS, 39762, 2, Giovanni, Modica, (662) 324-5772, Box 2618, NULL, Mississippi State, MS, 39762, 3, Tom, Cruise, (987) 456-0515, Nash Street, Apt #1, Los Angeles, CA, 58746, 4, Alicia, Silverstone, (662) 323-4624, Box 2345, NULL, Starkville, MS, 39759, 5, Cristina, Aguilera, (276) 587-9851, Sunset Boulevard, Number 5, Orlando, FL,56984, 6, Silvester, Stallone, (873) 632-9651, Main Street, Apt B, Portland, OR, 48751, 7, Bill, Gates, (759) 695-7541, University Drive, NULL, New York, NY, 69354, 8, Britney, Spears, (456) 325-9654, Bourbon Street, Number 34G, Miami, FL, 12547, 9, Kim, Basinger, (981) 741-8526, 5th Avenue, NULL, Houston, TX, 69541, 10, Brad, Pitt, (237) 369-8524, Morgan Street, Apt. 12, Minneapolis, MN, 78922, 11, Bart, Simpson, (706) 852-9874, Box 9874, NULL, Reading, MA, 96521

  14. Descriptions of the control file • The LOAD DATA statement is required at the beginning of the control file • INFILE * specifies that the data is found in the control file and not in an external file • The INTO TABLE statement is required to identify the table to be loaded (customer) into. By default, SQL*Loader requires the table to be empty before it inserts any records • FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks • The names of columns of the table to load are enclosed in parentheses • BEGINDATA specifies the beginning of the data

  15. Another Control File Example LOAD DATA INFILE ‘customer.dat’ INTO TABLE customer FIELDS TERMINATED BY ',‘ OPTIONALLY ENCLOSED BY '"' (customerID, firstName, lastName, phone, address1, address2, city, state, zip) Data are stored in an external file customer.dat

  16. Invoking SQL*Loader • sqlldr username/password • Construct a loading file myload.ldr: sqlload xt1/xt1 control=customer.ctl sqlload xt1/xt1 control=product.ctl

More Related