Data extraction

1. M Taimoor Khan taimoorkhan@ciit-attock.edu.pk Data extraction

2. Course Objectives • Basic Concepts • Tools • Database architecture and design • Flow of data (DFDs) • Mappings (ERDs) • Formulating queries (Relational algebra) • Implementing Schema • Built-in Functions • Extracting data • Working with Joins • Normalization • Improving performance • Advanced topics

3. Extracting Data • DML • SHOW, DESCRIBE • SELECT • Projecting on certain columns • Applying conditions on records • Other KEYWORDS / OPERATORS

4. Student table

5. Like Operator Q: Display the names and credits of CS programs SELECT crName, crCrdts, prName FROM course WHERE prName like '%CS‘;

6. Pattern Matching • MySQL provides: • standard SQL pattern matching; and • regular expression pattern matching, similar to those used by Unix utilities such as vi, grep and sed. • SQL Pattern matching: • To perform pattern matching, use the LIKE or NOT LIKE comparison operators • By default, patterns are case insensitive. • Special Characters: • _ Used to match any single character. • % Used to match an arbitrary number of characters.

7. SELECT with Multi-conditions SELECT * FROM student WHERE city = ‘attock’; SELECT stuName, city FROM student WHERE postcode = 3344;

8. SELECT stuName, city FROM student WHERE stuName LIKE ‘%ali’ AND sem=4; SELECT * FROM student WHERE stuName = “salman” OR city = “Islamabad”;

9. SELECT * FROM student WHERE ( stuName LIKE “%ali%” OR stuName LIKE “%ghani%” ) AND sem = 4;

10. Not Operator Inverses the predicate’s value Q: list crCode, crName, prName with prName from other than MCS SELECT crCode, crName, prName FROM course WHERE not (prName = ‘MCS’)

11. SELECT crCode, crName, prName FROM course WHERE (prName != ‘MCS’)

12. The BETWEEN Operator Q: List the IDs of the students with course codes having marks between 70 and 80 SELECT stId, crCode, totMrks From ENROLL WHERE totMrks between 70 and 80

13. The IN Operator • Checks in a list of values Q: Give the course names of MCS, BSTN, MS and BCS programs SELECT crName, prName From course Where prName in (‘MCS’, ‘BSTN’, ‘MS’, ‘BCS’)

14. “Order By” Clause • Sorts the rows in a particular order SELECT select_listFROM table_source[ WHERE search_condition] [ ORDER BY order_expression [ ASC | DESC ] [,……n] ]

15. ORDER BY • It is used to arrange the result set on any of the columns in the result set • Ordering can be applied in ascending or descending order SELECT crName, crCrdts, prName FROM course WHERE prName like '%CS‘ ORDER BY crName;

16. SELECT crName, crCrdts, prName FROM course WHERE prName like '%CS‘ ORDER BY crName ASC; SELECT crName, crCrdts, prName FROM course WHERE prName like '%CS‘ ORDER BY crName;

17. Group By Clause • SELECT stName, avg(cgpa) as 'Average CGPA', max(cgpa) as 'Maximum CGPA' from student • SELECT prName, max(cgpa) as ‘Max CGPA', min(cgpa) as ‘Min CGPA' FROM student GROUP BY prName

18. HAVING Clause • We can restrict groups by using having clause; groups satisfying having condition will be selected

19. HAVING Clause SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

20. HAVING Example SELECT prName, min(cgpa), max(cgpa) FROM student GROUP BY prName HAVING max(cgpa) > 3

21. Working with NULLs • NULL means missing value or unknown value. • To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>. • Rather, you must use the IS NULL and IS NOT NULL operators instead.

22. Working with NULLs • For example, to find all your dead pets (what a morbid example!) mysql> select name from pet where death IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec)

23. More examples

24. Selecting Particular Columns mysql> select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec)

25. Sorting Data • To sort a result, use an ORDER BY clause. • For example, to view animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)

26. Sorting Data • To sort in reverse order, add the DESC (descending keyword) mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)

27. Pattern Matching Example • To find names beginning with ‘b’: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+

28. Pattern Matching Example • To find names ending with `fy': mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+

29. Pattern Matching Example • To find names containing a ‘w’: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+

30. Pattern Matching Example • To find names containing exactly five characters, use the _ pattern character: mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+

31. Extracting Data • DML • SHOW, DESCRIBE • SELECT • Projecting on certain columns • Applying conditions on records • Other KEYWORDS / OPERATORS

32. Next Lecture • Built-in Functions