Lecture 9 - SQL - annotated - "Stand firm in your...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 “Stand firm in your refusal to remain conscious during algebra. In real life, I assure you, there is no such thing as algebra.” -- Fran Lebowitz, Social Studies SQL: Structured Query Language INFO/COM S 230: Intermediate Web Design and Programming SQL “Real” database queries How do we really pose queries to a relational DBMS? Industry standard: using the language SQL. • Soon: How to use SQL via the MySQL DBMS. • After that: How to use SQL from PHP via MySQL. • For now: How to write SQL queries. …via relational algebra To do this, we’ll show how we can implement each of the relational algebra operators in SQL. SQL basics: selection and projection Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Selection σ C (R) in SQL: SELECT * FROM R WHERE C; E.g. SELECT * FROM MOVIES WHERE Length > 150; 201 mins. 2003 The Return of the King 113 mins. 2002 Chicago 2004 2001 2000 Year Length Title 132 mins. Million Dollar Baby 135 mins. A Beautiful Mind 155 mins. Gladiator Conditions We can use the following conditions in the WHERE ” clause: = (equals – note not ==) < (less than) > (greater than) <= (less than or equal) >= (greater than or equal) <> (not equal) Conditions can be parenthesized and can use AND ”, “ OR ”, and “ NOT ”. SELECT * FROM MOVIES WHERE (Length > 150) AND (Title <> ‘The Return of the King’); Strings Can have conditions on strings using LIKE , with special characters % (matches 0 or more characters) and _ (matches any single character). E.g. SELECT * FROM Movies WHERE Title LIKE ‘%King%’ Regular expressions Some variants of SQL (such as MySQL) allow regular expression matching SELECT * FROM Movies WHERE Title REGEXP ‘[aeou]t’; 201 mins. 2003 The Return of the King 113 mins.
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 7

Lecture 9 - SQL - annotated - &quot;Stand firm in your...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online