1 / 42

Database Programming

Database Programming. Sections 7– Data Manipulation Language (DML) transaction, INSERT, implicit, explicit, USER, UPDATE, DELETE, integrity constraint, Parent record, Child record. Data Manipulation Language DML INSERT UPDATE DELETE MERGE Default Values Merge Statements Creating Tables

jerrod
Download Presentation

Database Programming

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. Database Programming Sections 7– Data Manipulation Language (DML) transaction, INSERT, implicit, explicit, USER, UPDATE, DELETE, integrity constraint, Parent record, Child record

  2. Data Manipulation Language DML INSERT UPDATE DELETE MERGE Default Values Merge Statements Creating Tables Using Data Types Data Definition Language DDL ALTER TABLE DROP TABLE RENAME TRUNCATE COMMENT Define Constraints Manage Constraints Overview of remainder of lesson Marge Hohly

  3. Using a subquery to copy a table • In order to experiment with the tables, make a copy of them:Select all rows from the EMPLOYEES table and insert them into the COPY_EMPLOYEES table. • CREATE TABLE copy_employeesAS (SELECT * FROM employees); • Verify by:SELECT *FROM copy_employees; • DESCRIBE copy_employees; • the integrity rules (primary keys, default values are not passed to the copy, only the column datatype definitions.) Marge Hohly

  4. Explicitly inserting data • Executing one DML statement is considered a transaction. • The INSERT statement is used to add new rows to a table. To get the column names and default order, use: • The statement requires three values: • the name of the table • the name of the column in the table to populate • a corresponding value for the column – • INSERT INTO copy_departments (department_id, department_name, manager_id, location_id)VALUES (70,’Public Relations’, 100, 1700); • INSERT INTO copy_departments (department_id, manager_id, location_id, department_name)VALUES (99, 100, 1700, ‘Advertising’) Marge Hohly

  5. Implicitly inserting data • Omit the column names • Match exactly the default order in which columns appear in the table • Provide a value for each column • Data types need to match • Use DESCRIBE to check the table structure before adding is a good idea • INSERT INTO copy_departmentsVALUES (100,’Education’, 100, 1700); • using VALUES adds on row at a time Marge Hohly

  6. Insert with NULL values • Implicit Method – omit the column from the column list • Any column that is not listed obtains a null value in the new row – errors can occur – the row has been specified NOT NULL, uniqueness, foreign key violation • INSERT INTO copy_departments (department_id, department_name)VALUES (30,’Purchasing’); • Explicit Method – specify the NULL keyword in the values clause • INSERT INTO copy_departmentsVALUES (100, ‘Finance’, NULL, NULL); Marge Hohly

  7. Inserting Special Values • Special values such as SYSDATE and USER can be entered in the VALUES list of an INSERT statement. • SYSDATE puts current date in a column • USER places current username (Oracle Application Express will put APEX_PUBLIC_USER) • INSERT INTO copy_employees (employee_id, last_name, email, hire_date,job_id)VALUES(1001, USER, ‘Test’, SYSDATE, ‘IT_PROG’); Marge Hohly

  8. Inserting Special Values • Can add functions and calculated expressions as values • Example of inserting a system date and a calculated expression • INSERT INTO copy_f_orders (order_number, order_date, order_total ,cust_id, staff_id)VALUES(1889,SYSDATE,87.92*1.08,123,19); Marge Hohly

  9. Inserting Specific date values • The default date before Oracle 9i was DD-MON-YY. • The default format for Oracle 9i is DD-MON-RR • century defaults to the current century • default time of midnight (00:00:00) • formats other than the default format use TO_DATE function • INSERT INTO copy_employeesVALUES (114, ‘Den’, ‘Raphealy’, ‘DRAPHEAL’, ‘515.127.4561’, ’03-FEB-49’, ‘AC_ACCOUNT’, 11000, NULL, 100, 30); Marge Hohly

  10. Inserting Date values • The default format model for date is DD-MON-RR. • Recall the century defaults to the nearest century (nearest to SYSDATE) with default time midnight (00:00:00) • TO_CHAR review:SELECT first_name, TO_CHAR(birthdate, ‘Month fmDD, RRRR’)FROM f_staffsWHERE id=12; Marge Hohly

  11. Inserting Date values • To INSERT a row with a non-defaut format for a date column, use the TO_DATE function to convert the date value (a character string ) to a date • INSERT INTO copy_f_staffs (first_name, last_name, TO_DATE(birthdate, ‘Month fmDD, RRRR’), salary, staff_type)VALUES (‘Sue’, ‘Jones’, ’July 1, 1980’, 25, ‘clerk’); Marge Hohly

  12. Date Example • INSERT INTO copy_employeesVALUES (114, ‘Den’, ‘Raphealy’, ‘DRAPHEAL’, ‘515.127.4561’, ’03-FEB-49’, ‘AC_ACCOUNT’, 11000, NULL, 100, 30); • SELECT last_name, TO_CHAR(hire_date, ’Month dd, RRRR’)FROM copy_employeesWHERE employee_id = 114; Marge Hohly

  13. Example • INSERT INTO copy_f_staffs (first_name, salary, staff_type, TO_DATE(birthdate, 'Month fmDD, RRRR HH24:MI‘)VALUES ('Sue', null, null, 'July 1, 1980 17:20'); Marge Hohly

  14. Inserting multiple rows • INSERT statement can be used to insert one row, but to insert multiple rows use a subquery • Use a subquery within the INSERT command to insert multiple rows Marge Hohly

  15. Using a subquery to copy rows • Copy values from an existing tableCREATE TABLE sales_reps(id number(5), name varchar2(15), salary number(10), commission_pct number (8)); • No VALUES clauseINSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE ‘%REP%’; • NOTE: subquery is not in parentheses as in WHERE clause previously Marge Hohly

  16. Copy all the data from a table • INSERT INTO sales_repsSELECT * FROM employees; • This works ONLY if both tables have same number of columns, same types, and in the same order Marge Hohly

  17. UPDATE statements • The UPDATE statement is used to modify existing rows in a table. It requires four values: • the name of the table • UPDATE copy_employees • the name of the column in the table to populate • SET department_id • a corresponding value or subquery for the column • SET department_id = 70 • a condition that identifies the columns and the changes for each column • WHERE employee_id = 103; • New value can be from a single-row subquery Marge Hohly

  18. Updating one column • Specific row or rows are modified if you specify the WHERE clause. • UPDATE copy_employeesSET department_id = 70WHERE employee_id = 103;(One row is updated) • All rows in the table are modified if you omit the WHERE clause. • UPDATE copy_employeesSET department_id = 110;(All rows are updated) Marge Hohly

  19. UPDATING multiple columns • Can update one or more rows in a statement • UPDATE copy_f_customers SET phone_number = ‘4475582344’, city = ‘Chicago’ WHERE id<200; • If no WHERE clause all rows are updated in the table Marge Hohly

  20. Update using subquery • UPDATE copy_employeesSET department_id = (SELECT department_id FROM employees WHERE last_name = ‘Ernst’)WHERE employee_id = 103; Marge Hohly

  21. Updating columns with subqueries • You can update one or more columns in the SET clause of an UPDATE statement by writing subqueries. • UPDATE copy_employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id =205) salary = (SELECT salary FROM employees WHERE employee_id =205)WHERE employee_id = 114; Marge Hohly

  22. Updating rows based on another table • Use a subquery to update data in one table with data in another table • UPDATE copy_f_staffsSET salary = (SELECT salary FROM f_staffs WHERE id = 9)WHERE id = 9; Marge Hohly

  23. DELETE statement • The DELETE statement is used to remove existing rows in a table. The statement requires two values: • the name of the table • the condition that identifies the rows to be deletedDELETE *FROM copy_employeesWHERE employee_id = 200; • If you omit the WHERE clause all rows will be deleted Marge Hohly

  24. Subquery Delete • Use subqueries in DELETE statements to remove rows from a table based on values from another table. • DELETE FROM copy_employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name = ‘Shipping’); Marge Hohly

  25. Deleting rows based on another table • A subquery can be used to remove rows from one table based on values from another table • DELETE FROM empWHERE plant_id = (SELECT plant_id FROM locations WHERE plant_loc = ‘AnyVilla’); Marge Hohly

  26. Integrity Constraints • Integrity constraints define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements an error will occur. • Types of Integrity constraints • NOT NULL – each row in the column must have a value • PRIMARY KEY – unique and not null • FOREIGN KEY – A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table. • CHECK – value meets certain conditions salary CHECK < 50000 • UNIQUE – no duplicate values in a column (email address) Marge Hohly

  27. Integrity Constraint Errors • UPDATE employeesSET department_id = 55WHERE department_id = 110; • ORA-02291: integrity constraint(USCA_INST_SQL03_T01.EMP_DEPT_FK) violated – parent key not found • There is no department_id = 55 in the departments table which is the parent table Marge Hohly

  28. Marge Hohly

  29. Marge Hohly

  30. Default Values • A column in a table can be given a default value. • Assigning default values prevents null values from existing in the column. • Default values can be: • a literal value ‘no commission assigned’ • an expression salary*1.15 • SQL function, such as SYSDATE or USER • Default values must match the data type of the column Marge Hohly

  31. Default Values Examples • Default Values Specified at the time the table is created:CREATE TABLE items( part_number VARCHAR2(10), description VARCHAR2(10), qty_on_hand NUMBER DEFAULT 0); • INSERT INTO items(part_number, description)VALUES (‘AB154’,’hammer’); Marge Hohly

  32. Default Values - Examples • Use DEFAULT when inserting values: • INSERT INTO items(part_number, description, qty_on_hand)VALUES (300,’Widger’, DEFAULT); • Use DEFAULT when updating values: • UPDATE itemsSET qty_on_hand = DEFAULTWHERE part_number = ‘200’; • Now check the results! • SELECT *FROM items; Marge Hohly

  33. MERGE • MERGE Statement • Accomplishes an UPDATE and INSERT at the same time ** the ON clause specifies the match – if two id’s match make the following updates, otherwise insert the following as new rows • MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCH THEN UPDATE SET c.first_name = e.first_name c.last_name = e.last_name ..... c.department_id = e.department_idWHEN NOT MATCHED THEN INSERT VALUES( e.employee_id, e.first_name, e.last_name, e.salary, e.commission_pct, e.manager_id, e.department_id); Marge Hohly

  34. MERGE Example: • MERGE INTO copy_items c USING items i ON(c.part_number = i.part_number)WHEN MATCHED THEN UPDATE SETc.description = i.descriptionc.qty_on_hand = i.qty_on_handWHEN NOT MATCHED THEN INSERT VALUES(i.part_number, i.description, i.qty_on_hand); Marge Hohly

  35. CREATING TABLES • Naming Rules: • Must begin with a letter • Must be 1 to 30 characters in length • Must only contain alpha/numeric,_,$,# • Must be unique • Must not be an Oracle Server reserved word Marge Hohly

  36. Using Data Types • Most Common Data Types: • VARCHAR2 • Examples: Name, Address • NUMBER • Examples: Price, Quatity • DATE • Examples: DOB, Hire Date Marge Hohly

  37. Creating Tables • CREATE TABLE name(column name DATATYPE(specifications for datatype) • VARCHAR2(number of characters) – variable length data 1-4000 • CHAR(size) – fixed length data 1-2000 • NUMBER(precision, scale) – total number of decimal digits(1 to 38), right of decimal digits(-84 to 127) For example, a scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46); a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000). • DATE – date and time Marge Hohly

  38. Creating Tables cont’d • CREATE TABLE students(id VARCHAR2(5), lname VARCHAR2(15), fname VARCHAR2(15), GPA number(6), enroll_date date DEFAULT SYSDATE); Marge Hohly

  39. ORACLE Data Dictionary • The data dictionary is a collection of tables created and maintained by the Oracle Server and contains information about the database. • All data dictionary tables are owned by the SYS user. Because the tables are difficult to understand, users access data dictionary includes names of the Oracle Server users, privileges granted to users, database object names, table constrains and auditing information. • You may browse the Oracle Data Dictionary to show objects you own. • SELECT *FROM DICTIONARY; - returns 610 items • SELECT table_nameFROM user_tables; • SELECT DISTINCT object_typeFROM user_objects; • SELECT *FROM user_catalog; Marge Hohly

  40. Using Interval Year to Month • INTERVAL YEAR TO MONTH: Stores a period of time in years/monthsNote that the argument 3 in the create table and insert statements refers to precision • CREATE TABLE time_ex2(school_year_duration INTERVAL YEAR (3) TO MONTH); • INSERT INTO time_ex2(school_year_duration)VALUES(INTERVAL ‘9’ Month(3)); • SELECT TO_CHAR(SYSDATE + school_year_duration, ‘dd-Mon-yyyy’)FROM time_ex2; • Returned 9 month from today’s date Marge Hohly

  41. USING INTERVAL DAY TO SECOND • INTERVAL DAY TO SECOND: Stores a more precise period of time (days/hours/minutes/seconds) • CREATE TABLE time_ex3(day_duration INTERVAL DAY(3) to SECOND); • INSERT INTO time_ex3(day_duration) VALUES(INTERVAL ‘180’ DAY(3)); • SELECT SYSDATE + day_duration “Half Year” FROM time_ex3; Marge Hohly

  42. Using Time Data Types • TIMESTAMP: stores values with respect to universal time • CREATE TABLE time_example (order_date TIMESTAMP WITH LOCAL TIME ZONE,ship_date DATE DEFAULT SYSDATE); • INSERT INTO time_exampleVALUES(SYSDATE, SYSDATE); • SELECT *FROM time_example; Marge Hohly

More Related