1 / 17

SQL

SQL. Data Manipulation Language (DML) SELECT. SQL DML - SELECT. SELECT [DISTINCT|ALL] {* | [col-expr][,...] FROM table-name [alias] [,...] [WHERE condition] ORDER BY colm [, colm]. SQL DML - SELECT. SELECT attributes (or calculations: +, -, /, *) FROM relation

ursula-norman
Télécharger la présentation

SQL

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) • SELECT

  2. SQL DML - SELECT • SELECT [DISTINCT|ALL] {* | [col-expr][,...] FROM table-name [alias] [,...] [WHERE condition] ORDER BY colm [, colm]

  3. SQL DML - SELECT • SELECT attributes (or calculations: +, -, /, *) FROM relation • SELECT DISTINCT attributes FROM relation

  4. Examples • SELECT stuname FROM student; • SELECT stuid, stuname, credit FROM student; • SELECT stuid, stuname, credit+10 FROM student; • SELECT DISTINCT major FROM student;

  5. SQL DML - SELECT • SELECT attributes (or * wild card) FROM relation WHERE condition

  6. Examples • SELECT * FROM student; • SELECT stuname, major, credit FROM student WHERE stuid = ‘S114’; • SELECT * FROM faculty WHERE dept = ‘MIS’;

  7. SELECT - WHERE condition • AND OR • NOT IN • NOT IN BETWEEN • IS NULL IS NOT NULL • LIKE '%' multiple characters • LIKE ‘_’ single characters

  8. Examples • SELECT * FROM faculty WHERE dept = ‘MIS’ AND rank = ‘full professor’; • SELECT * FROM faculty WHERE dept = ‘MIS’ OR rank = ‘full professor’; • SELECT * FROM faculty WHERE dept = ‘MIS’ NOT rank = ‘full professor’;

  9. SELECT * FROM class WHERE room LIKE ‘B_S%’; • SELECT * FROM class WHERE room NOT LIKE ‘BUS%’; • SELECT productid, productname FROM inventory WHERE onhand BETWEEN 50 and 100;

  10. SELECT companyid, companyname FROM company WHERE companyname BETWEEN ‘G’ AND ‘K’; • SELECT productid, productname FROM inventory WHERE onhand NOT BETWEEN 50 and 100; • SELECT companyid, companyname FROM company WHERE companyname NOT BETWEEN ‘G’ AND ‘K’;

  11. SELECT facname FROM faculty WHERE dept IN (‘MIS’, ‘ACT’); • SELECT facname FROM faculty WHERE rank NOT IN (‘assistant’, ‘lecture’); • SELECT customername FROM customer WHERE emailadd IS NOT NULL;

  12. SELECT customername FROM customer WHERE creditlimit IS NULL;

  13. SELECT - ORDER BY • ORDER BY • ORDER BY ... DESC

  14. Examples • SELECT facname, rank FROM faculty ORDER BY facname; • SELECT facname, rank FROM faculty ORDER BY rank DESC, facname;

  15. SELECT - JOIN Tables • Multiple tables in FROM clause • MUST have join conditions!!!

  16. Examples • SELECT stuname, grade FROM student, enrollment WHERE student.stuid = enrollment.stuid;

  17. SELECT enrollment.course#, stuname, major FROM class, enrollment, student WHERE class.course# = enrollment.course# AND enrollment.stuid = student.stuid AND facid = ‘F114’ ORDER BY enrollment.course#;

More Related