SQL Data Manipulation Language (DML)

1. SQL Data Manipulation Language (DML) SQL Data Manipulation Language (DML)

2. SQL DML • SQL • Structured Query Language • ISO / ANSI standard • SQL = DDL + DML • DML is a part of SQL • Used to manipulate tables, etc. • DML has 4 important statements • SELECT, INSERT, 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. 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)

12. 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)

13. 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)