SQL: Data Manipulation Commands & Queries

1. DATABASE DESIGN & DEVELOPMENT data manipulation Zatil Ridh'wah Hj Darot

2. introduction to structured query language (sql) queries and sql

3. introduction to sql • SQL functions fit into two broad categories: • Data definition language • _____________________and define access rights to those database objects. • E.g. CREATE TABLE, NOT NULL, PRIMARY KEY, etc. • Data manipulation language • Commands to insert, update, delete and retrieve data within the database tables. • E.g. INSERT, UPDATE, DELETE, SELECT, WHERE, GROUP BY etc.

4. DATA MANIPULATION COMMANDS • INSERT • UPDATE • DELETE • SELECT

5. ADDING TABLE ROWS • INSERT • Used to enter data into table • Syntax: • INSERT INTO tablename VALUES (value1, value2, … , valueN);

6. ADDING TABLE ROWS (CONT'D) • When entering values, notice that: • Row contents are entered between parentheses • Character and date values are entered _________________ • Numerical entries are not enclosed in apostrophes • Attribute entries are separated by commas • A value is required for each column • Use NULL for unknown values

7. UPDATING TABLE ROWS • UPDATE • Modify data in a table • Syntax: • If more than one attribute is to be updated in row, ________________with commas.

8. EXAMPLE - UPDATING TABLE ROWS • UPDATE PRODUCT SETP_InvDate = ‘18-Jan-2012’ WHEREP_Code = ‘13-Q2/P2’; (_________________for the product whose code is 13-Q2/P2) • UPDATE PRODUCT SETP_InvDate = ‘18-Jan-2012’, P_Price = 17.99, WHEREP_Code = ‘13-Q2/P2’; (modify the invoice date and product price for the product whose code is 13-Q2/P2)

9. DELETING TABLE ROWS • DELETE • Deletes a table row • Syntax: • WHERE condition is optional • If WHERE condition is not specified, ____________________ table will be deleted

10. EXAMPLE - DELETING TABLE ROWS • DELETE PRODUCT WHERE P_Code = ‘BRT-345’; (delete the row (record) of the product whose code is BRT-345) • UPDATE PRODUCT WHERE P_Min = 5; (delete all rows of those products whose P_Min is equal to 5 from the PRODUCT table)

11. listing table rows • SELECT • Used to list contents of table • Syntax: • _______________ represents one or more attributes, separated by commas • Asterisk can be used as wildcard character to list all attributes

12. example - select • Select all or specific fields • SELECT * FROM student; ( * - displays all fields in students table) • SELECT surname, firstname, course_code FROM student;

13. select queries • Fine-tune SELECT command __________________to search criteria using: • Conditional restrictions • Arithmetic operators • Logical operators • Special operators

14. queries • A query is a means of ______________________ • The record selection may be filtered: • Only some fields, not all Example: All records from an Employee table but not the office extension numbers • Only certain records fitting the criteria Example: Only those employees living in the UK • A combination of both Example: Only the names and home phone numbers of employees (but no other details) from London

15. sql select queries • Select query • Select fields • Select records • Perform calculations • Table queried unchanged • Data source for Forms/Reports

16. selecting rows with conditional restrictions • Select ____________________by placing restrictions on rows to be included in output • Add conditional restrictions to SELECT statement, using WHERE clause • Syntax:

18. sql language elements • Comparison Operators = < > <= >= <> != • Aritmetic Operators + - * / • Functions Count Sum Avg Max Min • Boolean Operators AND OR NOT

19. example - select ... where • Select specific fields and records • SELECT surname, firstname, course_code FROM student WHERE course_code = “BUS2038” ;

20. selecting rows with conditional restrictions (cont'd) • Using comparison operators on dates • Date procedures are often more software-specific than other SQL procedures • Using computed columns and column aliases • SQL accepts any ________________ (or formulas) in the computed columns • Alias • Alternate name given to a column or table in any SQL statement

21. arithmetics operators: the rule of precednce • Perform operations within parentheses • Perform power operations • Perform ________________________ • Perform additions and subtractions

22. logical operators: and, or, and not • Searching data involves multiple conditions • Logical operators: AND, OR, and NOT • Can be combined • Parentheses enforce precedence order -Conditions in parentheses are always executed first • Boolean algebra_____________________dedicated to use of logical operators • NOT negates result of conditional expression

23. example - select ... where ... and • SELECT surname, firstname, gender, course_code, dob FROM student WHERE gender = “M” AND firstname = “Hassan” ;

24. special operators • BETWEEN • checks whether attribute value is within a range • IS NULL • checks whether attribute value is null • LIKE • o checks whether attribute ____________________given string pattern • IN • checks whether attribute value matches any value within a value list • EXISTS • checks if subquery returns any rows

25. example - like & wildcards

26. deleting a table from the database • DROP • Deletes table from database • Syntax: -DROP TABLE tablename; • Can drop a table only if it is not the “one” side of __________________ • Otherwise, RDBMS generates an error message • Foreign key integrity violation

27. additional select query keywords • Logical operators work well in the query environment • SQL provides useful functions that: • Count • Find ___________________ values • Calculate averages, etc. • SQL allows user to limit queries to: • Entries having no duplicates • Entries whose duplicates may be grouped

28. ordering a listing • ORDER BY clause is useful when listing order is important • Syntax: • Ascending order by default

29. aggregATE FUNCTIONS • COUNT function tallies number __________________of an attribute • Takes one parameter: usually a column name • MAX and MIN find highest (lowest) value in a table • Compute MAX value in inner query • Compare to each value returned by the query • SUM computes total sum for any specified attribute • AVG function format is similar to MIN and MAX

30. GROUPING DATA • Frequency distributions created by GROUP BY clause within SELECT statement • Syntax:

31. CONVENTIONS IN SQL • Preferably write SQL in several lines rather than in one line for readability • Field names that have space e.g. Student ID • In Access, use [ ] e.g. [Student ID] • Use the wildcard character, * to display all fields • ___________________ are required to be enclosed by single or double quotes

32. SUMMARY • SQL commands can be divided into two overall categories: • Data definition language commands • Data manipulation language commands • DML commands allow you to add, modify, and delete rows from tables • The basic DML commands: • SELECT, INSERT, UPDATE, DELETE • SELECT statement is main data retrieval command in SQL

33. SUMMARY (CONT'D) • WHERE clause can be used with SELECT, UPDATE, and DELETE statements • Aggregate functions • Special functions that perform arithmetic computations over a set of rows • ORDER BY clause • Used to sort output of SELECT statement • Can sort by one or more columns • Ascending or descending order

34. SUMMARY

35. reSOURCE • Database Principles: Fundamentals of Design, Implementation, and Management Tenth Edition