SQL Data Manipulation Techniques

1. Chapter # 7 Introduction to Structured Query Language (SQL) Part II

2. UPDATE Modify data in a table Basic Syntax: Updating Table Rows UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist]; • If more than one attribute is to be updated in row, separate corrections with commas

3. ROLLBACK Undoes changes since last COMMIT Brings data back to pre-change values Syntax: Restoring Table Contents ROLLBACK; • COMMIT and ROLLBACK only work with commands to add, modify, or delete table rows

4. DELETE Deletes a table row Basic Syntax: Deleting Table Rows DELETE FROM tablename [WHERE conditionlist ]; • WHERE condition is optional • If WHERE condition is not specified, all rows from specified table will be deleted

5. Inserting Table Rows with a SELECT Subquery • INSERT • Inserts multiple rows from another table (source) • Uses SELECT subquery • Subquery: query embedded (or nested) inside another query • Subquery executed first • Syntax: INSERT INTO tablename SELECT columnlist FROM tablename;

6. Fine-tune SELECT command by adding restrictions to search criteria using: Conditional restrictions Arithmetic operators Logical operators Special operators SELECT Queries

7. Selecting Rows with Conditional Restrictions • Select partial table contents by placing restrictions on rows to be included in output • Add conditional restrictions to SELECT statement, using WHERE clause • Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist] ;

8. Arithmetic Operators The Rule of Precedence • Perform operations within parentheses • Perform power operations • Perform multiplications and divisions • Perform additions and subtractions

9. Searching data involves multiple conditions Logical operators: AND, OR, and NOT Can be combined Parentheses placed to enforce precedence order Conditions in parentheses always executed first Boolean algebra: mathematical field dedicated to use of logical operators NOT negates result of conditional expression Logical Operators: AND, OR and NOT

10. BETWEEN: checks whether attribute value is within a range IS NULL: checks whether attribute value is null LIKE: checks whether attribute value matches given string pattern IN: checks whether attribute value matches any value within a value list EXISTS: checks if subquery returns any rows Logical Operators: AND, OR and NOT

11. Advanced Data Definitions Commands • All changes in table structure are made by using ALTER command • Three options • ADD adds a column • MODIFY changes column characteristics • DROP deletes a column • Can also be used to: • Add table constraints • Remove table constraints

12. ALTER can be used to change data type Some RDBMSs do not permit changes to data types unless column is empty Basic Syntax: Changing a Column’s Data Type ALTER TABLE tablename MODIFY COLUMN columnname datatype;

13. Adding a Column • Use ALTER to add column • Do not include the NOT NULL clause for new column • Basic Syntax: ALTER TABLE tablename ADD columnname datatype ; Example: ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1));

14. Dropping a Column • Use ALTER to drop column • Some RDBMSs impose restrictions on the deletion of an attribute • Basic Syntax: ALTER TABLE tablename DROP COLUMN columnname ; ALTER TABLE VENDOR DROP COLUMN V_ORDER;

15. UPDATE command updates only data in existing rows If relationship between entries and existing columns, can assign values to slots Arithmetic operators useful in data updates In Oracle, ROLLBACK command undoes changes made by last two UPDATE statements Advanced Data Updates UPDATE PRODUCT SET P_SALECODE =‘2’ WHERE P_CODE ='1546-QQ2';

16. Advanced Data Updates (2)

17. SQL permits copying contents of selected table columns Data need not be reentered manually into newly created table(s) First create the table structure Next add rows to new table using table rows from another table Copying Parts of Tables

18. First create the table structure CREATE TABLE PART ( PART_CODE CHAR(8) NOT NULL UNIQUE, PART_DESCRIPT CHAR(35), PART_PRICE DECIMAL(8,2), V_CODE INTEGER, PRIMARY KEY (PART_CODE) ); Next add rows to new table using table rowsfrom another table • INSERT INTO PART ( • PART_CODE, • PART_DESCRIPT, • PART_PRICE, • V_CODE) • SELECT • P_CODE, • P_DESCRIPT, • P_PRICE, • V_CODE • FROM PRODUCT;

19. Copying Parts of Tables (2)

20. Adding Primary and Foreign Key Designations • When table is copied, integrity rules do not copy • Primary and foreign keys manually defined on new table • User ALTER TABLE command • Syntax: ALTER TABLE tablename ADD PRIMARY KEY (fieldname); • For foreign key, use FOREIGN KEY in place of PRIMARY KEY ALTER TABLE PART ADD PRIMARY KEY (PART_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

21. DROP Deletes table from database Syntax: Deleting a Table from the Database DROP TABLE tablename; • Can drop a table only if it is not the “one” side of any relationship • Otherwise RDBMS generates an error message • Foreign key integrity violation DROP TABLE PART;

22. Logical operators work well in the query environment SQL provides useful functions that: Count Find minimum and maximum values Calculate averages, etc. SQL allows user to limit queries to: Entries having no duplicates Entries whose duplicates may be grouped Advanced SELECT Queries

23. ORDER BY clause useful when listing order important Basic Syntax: Ordering a Listing SELECT columnlist FROM tablelist [WHERE conditionlist] [ORDER BY columnlist [ASC | DESC]]; • Ascending order by default

24. Ordering a Listing • SELECT • P_CODE, • P_DESCRIPT, • P_INDATE, • P_PRICE • FROM PRODUCT ORDER BY P_PRICE DESC;

25. DISTINCT clause produces list of only values that are different from one another Syntax Example: Listing Unique Values SELECT DISTINCT V_CODE FROM PRODUCT; • Access places nulls at the top of the list • Oracle places it at the bottom • Placement of nulls does not affect list contents

26. COUNT function tallies number of non-null values of an attribute Takes one parameter: usually a column name Aggregate Functions SELECT COUNT(P_CODE) FROM PRODUCT and SELECT COUNT(*) FROM PRODUCT willyieldthesameanswerbecausetherearenonullvalues in the P_CODE primarykeycolumn.

27. MAX and MIN find highest (lowest) value in a table Compute MAX value in inner query Compare to each value returned by the query Aggregate Functions • SELECT • P_CODE, • P_DESCRIPT, • P_PRICE • FROM PRODUCT WHERE P_PRICE = MAX(P_PRICE);

28. SUM computes total sum for any specified attribute AVG function format similar to MIN and MAX Aggregate Functions SELECT SUM(CUS_BALANCE) AS TOTBALANCE FROM CUSTOMER; SELECT AVG(P_PRICE) FROM PRODUCT;

29. Frequency distributions created by GROUP BY clause within SELECT statement Basic Syntax: Grouping Data SELECT columnlist FROM tablelist [WHERE conditionlist] [GROUP BY columnlist] [HAVING conditionlist] [ORDER BY columnlist [ASC | DESC] ] ;

30. Grouping Data • The GROUP BY clause is validonlywhenused in conjunctionwithone of the SQL aggregatefunctions, such as COUNT, MIN, MAX, AVG, and SUM. • Forexample, ifyoutrytogrouptheoutputbyusing: SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE; yougenerate a “not a GROUP BY expression” error. However, ifyouwritethepreceding SQL commandsequence in conjunctionwithsomeaggregatefunction, the GROUP BY clauseworksproperly. SELECT V_CODE, COUNT(DISTINCT (P_CODE)) FROM PRODUCT GROUP BY V_CODE;

31. Grouping Data (2)

32. Viewis virtual table based on SELECT query Create view by using CREATE VIEW command Special characteristics of relational view: Name of view can be used anywhere a table name is expected View dynamically updated Restricts users to only specified columns and rows Views may be used as basis for reports Virtual Tables: Creating a View • CREATE VIEW PRICEGT50 AS • SELECT P_DESCRIPT,P_QOH, P_PRICE FROM PRODUCT • WHERE P_PRICE > 50.00;

33. Joining tables is the most important distinction between relational database and other DBs Join is performed when data are retrieved from more than one table at a time Equality comparison between foreign key and primary key of related tables Join tables by listing tables in FROM clause of SELECT statement DBMS creates Cartesian product of every table Joining Database Tables

34. The join condition is generally composed of an equality comparison between the foreign key and the primary key of related tables. For example, suppose that you want to join the two tables VENDOR and PRODUCT. Because V_CODE is the foreign key in the PRODUCT table and the primary key in the VENDOR table, the link is established on V_CODE. Equi-join

35. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; Equi-join

36. SELECT PRODUCT.P_CODE, VENDOR.V_CODE, V_NAME FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.P_CODE; Left Join

37. SELECT PRODUCT.P_CODE, VENDOR.V_CODE, V_NAME FROM VENDOR RIGHT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.P_CODE; Right Join

38. THE END