Lecture 9 - SQL - annotated

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

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

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.

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

View Full Document
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. 2002 Chicago 2004 2001 2000