SQL Basics Lesson 6 PDF

SQL BASICS --- (2)  Data Manipulate Language (DML) Overview SELECT INSERT OUTLINE UPDATE DELETE DDL, DML, DCL, AND THE DATA B A S E D E V E L O P M E N T P RO C E S S W H AT I S D M L ? The SQL data manipulation language (DML...

SQL BASICS --- (2)  Data Manipulate Language (DML) Overview SELECT INSERT OUTLINE UPDATE DELETE DDL, DML, DCL, AND THE DATA B A S E D E V E L O P M E N T P RO C E S S W H AT I S D M L ? The SQL data manipulation language (DML) is used to query and modify database data. SELECT – to query data in the database INSERT – to insert data into a table UPDATE – to update data in a table DELETE – to delete data from a table S Q L Q U E RY In the SQL DML statement: Each clause in a statement should begin on a new line. The beginning of each clause should line up with the beginning of other clauses. If a clause has several parts, they should appear on separate lines and be indented under the start of the clause to show the relationship. Upper case letters are used to represent reserved words. Lower case letters are used to represent user-defined words.  The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria. It is processed according to the following sequence: SELECT S TAT E M E N T SELECT DISTINCT item(s) FROM table(s) WHERE predicate GROUP BY field(s) ORDER BY fields SELECT EXAMPLE: Alphabetical Order Table 16.1 S E L E C T S TAT E M E N T W I T H W H E R E C R I T E R I A PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks S E L E C T S TAT E M E N T W I T H W H E R E C R I T E R I A PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi PName Price Manufacturer SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi D E TA I L S Case insensitive: Constants: Same: SELECT Select select ‘abc’ - yes Same: Product product “abc” - no Different: ‘Seattle’ ‘seattle’ S E L E C T S TAT E M E N T W I T H O R D E R BY C L AU S E You use the ORDER BY clause to sort the records in the resulting list. ASC to sort the results in ascending order DESC to sort the results in descending order. S E L E C T S TAT E M E N T W I T H G RO U P BY C L AU S E The GROUP BY clause is used to create one output row per each group and produces summary values for the selected columns, as shown below.  The INSERT statement adds rows to a table. Can be removed when insert into all columns I N S E RT S TAT E M E N T Example: Insert a new purchase to the database: I N S E RT RU L E S When inserting rows with the INSERT statement, these rules apply: Inserting an empty string (‘ ‘) into a varchar or text column inserts a single space. All char columns are right-padded to the defined length. If an INSERT statement violates a constraint, default or rule, or if it is the wrong data type, the statement fails and SQL Server displays an error message. I N S E RT I N G ROW S W I T H A S E L E C T S TAT E M E N T We can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. C A S E S T U DY: Check the Solution !  The UPDATE statement changes data in existing rows either by adding new data or modifying existing data. UPDATE S TATEMEN T I N C L U D I N G S U B Q U E R I E S I N A N U P DAT E S TAT E M E N T The employees from the Employees table who were hired by the publisher in 2010 are given a promotion to the highest job level for their job type. This is what the UPDATE statement would look like.  The DELETE statement removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is a standard WHERE clause that limits the deletion to select records. DEL ET E S TATEMEN T D E L E T E RU L E S The rules for the DELETE statement are: 1. If you omit a WHERE clause, all rows in the table are removed (except for indexes, the table, constraints). 2. DELETE cannot be used with a view that has a FROM clause naming more than one table. (Delete can affect only one base table at a time.) 3 WAYS TO C O M P L E T E D E L E T I O N THE END