1 / 36

Extracting data

M Taimoor Khan taimoorkhan@ciit-attock.edu.pk. Extracting data. 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

edison
Download Presentation

Extracting data

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. M Taimoor Khan taimoorkhan@ciit-attock.edu.pk Extracting data

  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. 9) Extracting Data • DML • SHOW, DESCRIBE • SELECT • Projecting on certain columns • Applying conditions on records • Other KEYWORDS / OPERATORS

  4. Select Statement • Maximum used command in DML • Used not only to select certain rows but also the columns • Also used for different forms of product, that is, different joins

  5. Select • Selecting rows from one or more tables • SELECT {*|col_name[,….n]} FROM table_name • Example (list all students) • SELECT * FROM student;

  6. PROJECT on certain columns SELECT stName, prName FROM student

  7. Attribute Alias SELECT stName as ‘Student Name’ , prName ‘Program’ FROM Student

  8. Formatting reports Select stId, crCode, mTerm + sMrks ‘Total out of 50’ from enroll

  9. Formatting reports SELECT stName + ‘ studies in ‘ + prName FROM student

  10. Unique values • The DISTINCT keyword is used to return only distinct (different) values

  11. Select Distinct • Just add a DISTINCT keyword to the SELECT statement • SELECT DISTINCT column_name(s) FROM table_name

  12. Select Distinct Q: Get the program names in which students are enrolled SELECT prName FROM Student

  13. Select Distinct Q: Get the program names in which students are enrolled SELECT prName FROM Student SELECT DISTINCT prName FROM Student

  14. The WHERE Clause • We used names to limit columns, but rows cannot be named due to the dynamicity • We limit the rows using conditions • Condition comes after the WHERE clause • Condition is applied on a single or multiple columns providing it with some particular value or set of values

  15. Student table

  16. Example SELECT stuName, sem FROM student WHERE city=“islamabad”;

  17. LIMIT • It limits the number of records in the result set as per the request • Examples • SELECT * FROM student LIMIT 0, 100; • SELECT stuID, stuName FROM student LIMIT 2, 5; SELECT stuID, stuName FROM student WHERE postCode > 21000 LIMIT 0, 10;

  18. Examples

  19. Selecting All Data • The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)

  20. Selecting Particular Rows • You can select only particular rows from your table. • For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)

  21. Selecting Particular Rows • To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1"; • To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = "f"; • To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird";

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

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

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

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

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

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

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

  29. Next Lecture • SELECT with keywords

More Related