1 / 14

SQL Data Manipulation Language (DML)

SQL Data Manipulation Language (DML). Using Microsoft SQL Server. SQL DML. SQL Structured Query Language ISO / ANSI standard Microsoft has its own dialect of SQL, called T-SQL SQL = DDL + DML DML is a part of SQL Used to manipulate tables, etc. DML has 4 important statements

maia
Download Presentation

SQL Data Manipulation Language (DML)

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 Data Manipulation Language (DML) Using Microsoft SQL Server SQL Data Manipulation Language (DML)

  2. SQL DML • SQL • Structured Query Language • ISO / ANSI standard • Microsoft has its own dialect of SQL, called T-SQL • SQL = DDL + DML • DML is a part of SQL • Used to manipulate tables, etc. • DML has 4 important statements • SELECT, INSER, UPDATE, DELETE SQL Data Manipulation Language (DML)

  3. INSERTAdding data to a single table • Single row insert INSERT INTO tablename (col1, col2) VALUES (value1, value2) • Multi-row insert INSERT INTO tablename (col1, col2) VALUES (value1, value2) (value1b, value2b) • Copy insert • Copy date from one table to another • INSER INTO tablename … SELECT FROM anotherTable SQL Data Manipulation Language (DML)

  4. UPDATEUpdate existing data • UPDATE tablename SET column = newValue • All the values in column will now be newValue • UPDATE tablename SET column = newValue WHERE someCondition • Only rows where someCondition is true, are updated SQL Data Manipulation Language (DML)

  5. DELETE Delete data from a table • DELETE FROM someTable • All data are deleted from someTable • Be careful! • The empty table is left • Not like DROP someTable, where the table is deleted • DELETE FROM someTable WHERE someCondition • Only rows where someCondition is true, are deleted SQL Data Manipulation Language (DML)

  6. SELECTGet date from a table • Syntax • Select * fromsomeTable • Will return all columns (*) and all rows • The result might be empty • Selectattrib1, attrib2from someTable • Will return only the specified columns • Selectattrib1, … fromsomeTablewheresomeCondition • Will return only the rows where someCondition is true SQL Data Manipulation Language (DML)

  7. The WHERE clause • The WHERE clause determines which rows will be in the result. • The WHERE clause is a so-called boolean expression • Evaluates to TRUE or FALSE • Lots of operators used in the WHERE clause • Comparing =, <, >=, !=, etc. • Combining AND, OR, NOT • Ranges BETWEEN • Strings LIKE • Attrib LIKE ’A%’ anything starting with A • Attrib LIKE ’A_C’ anything that has A then something and then C • Sets IN • Attrib IN (’Monday’, ’Tuesday’, ’Wednesday’) • Null IS • Attrib IS NULL SQL Data Manipulation Language (DML)

  8. ORDER BY • We cannot rely on the order in a table • However, the result of a SELECT statement can be ordered • SELECT … ORDER BY attrib ASC • The result is ordered by attrib ascending • DESC means descending • SELECT … ORDER BY attrib1, attrib2 • Primary order: attrib1 • Secondary order: attrib2 SQL Data Manipulation Language (DML)

  9. Simple functions • MAX, MIN • Largest and smallest • AVG • Average, used on numbers • COUNT • Number of rows • SELECT COUNT(*), MAX(price), MIN(price), AVG(price) FROM book SQL Data Manipulation Language (DML)

  10. AggregatesFunctions used on groups of data • You can aggregate (group) the data, apply the functions on each group • SELECT country, COUNT(*), AVG(price) FROM book GROUP BY country • All rows are grouped by country • COUNT and AVG is performed for each group SQL Data Manipulation Language (DML)

  11. HAVINGConditions on groups • WHERE • Condition on rows • HAVING • Condition on groups • SELECT country, AVG(price) FROM book GROUP BY country HAVING COUNT(*) > 4 • Only groups with more than 4 rows are used SQL Data Manipulation Language (DML)

  12. DISTINCT • Sometimes similar rows appears in the output • DISTINCT can remove them • SELECT DISTINCT countryID FROM book • SELECT COUNT(DISTINCT countryID) FROM book SQL Data Manipulation Language (DML)

  13. Mixing data typesCAST and CONVERT • CAST(expression AS dataType) • Used to type cast the result of expression to dataType • Example • SELECT ”The customer has …” + CAST(ID AS varchar) … • The ID is cast to a varchar to be compatible with the text ”The customer has …” • CONVERT(dataType, expression) SQL SELECT sub-queries

  14. SQL DML is a declarative language • SQL DML is a declarative language • You say (declare) what you want without thinking about how to get it • Very much like ordering in a restaurant • The DBMS (Database Management System) carries out your request • In the restaurant the kitchen staff carries out your request SQL Data Manipulation Language (DML)

More Related