1 / 19

Introduction to Data Manipulation in SQL

Introduction to Data Manipulation in SQL. CIS 4301 Lecture Notes Lecture 16 - 21/03/2006. Simple Movie Database Schema. Movie ( Title,Year ,length,inColor,studioName,producerC#) StarsIn ( MovieTitle,MovieYear,StarName ) MovieStar ( Name ,address,gender,birthdate)

liang
Download Presentation

Introduction to Data Manipulation in 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. Introduction to Data Manipulation in SQL CIS 4301 Lecture Notes Lecture 16 - 21/03/2006

  2. Simple Movie Database Schema Movie(Title,Year,length,inColor,studioName,producerC#) StarsIn(MovieTitle,MovieYear,StarName) MovieStar(Name,address,gender,birthdate) MovieExec(name,address,Cert#,netWorth) Studio(Name,address,presC#) © CIS 4301 - Spring 2006

  3. Simple Queries in SQL SELECT FROM WHERE ORDER BY • Asks for those tuples of one or more relation that satisfy a condition, order the output (default ascending) • Note, most SQL implementations allow duplicate tuples, i.e., operate on multisets (bags) rather than sets © CIS 4301 - Spring 2006

  4. SQL vs. Rel. Algebra SELECT A1, A2, ..., An FROM R1, R2, ..., Rm WHERE condition is equivalent to relational algebra query: A1, A2, ..., An (condition (R1 X R2 X ... X Rm))  Returns unnamed relation whose schema is (A1, A2, ..., An) • SQL is case insensitive • Case makes a difference inside quotes! © CIS 4301 - Spring 2006

  5. Selection and Projection Selection: • Find all movies produced by Disney in 1990 SELECT * FROM Movie WHERE studioName = ‘Disney’ and year = ‘1990’; Projection: • Find all movies produced by Disney in 1990 and return their title and length SELECT title, length FROM Movie WHERE studioName = ‘Disney’ and year = ‘1990’; © CIS 4301 - Spring 2006

  6. Duplicates • Retained unless use keyword DISTINCT • Duplicate removal is an expensive operation • This is a significant difference between SQL and “pure” relational algebra © CIS 4301 - Spring 2006

  7. String Comparison • Use any of the “usual” string comparison operators such as ‘< ’, ‘>’, ‘= ’, … • More useful, pattern matching using “like” • Two wildcards: ‘_’ and ‘%’ • Find all movies whose title starts with ‘Star’ SELECT title FROM Movie WHERE title LIKE ‘Star%’; • Find all movies with possessive (‘s) in their title SELECT title FROM Movie WHERE title LIKE ‘%’’s%’; © CIS 4301 - Spring 2006

  8. Ordering Output • Return tuples in sorted order (by default in ascending order) • Use desc if you want descending order • Find all movies produced by Universal in 1973 and return their title and length; sort movies in ascend. order by length then by title SELECT title, length FROM Movie WHERE studioName = ‘Universal’ AND year = ‘1973’ ORDER BY length, title; © CIS 4301 - Spring 2006

  9. Renaming Attributes • Use keyword AS in SELECT clause SELECT title AS Movie_Title, length AS Movie_Length FROM Movie WHERE studioName = ‘Disney’ and year = ‘1990’; SELECT title AS Title, length*0.016667 AS Length, ‘hrs.’ AS inHours FROM Movie WHERE studioName = ‘Disney’ and year = ‘1990’; © CIS 4301 - Spring 2006

  10. Cartesian Product • Much of the power of rel. query language comes for the ability to combine two or more relations through cartesian product • Form cartesian product by listing each relation in the FROM clause • Consider pairs of tuples satisfying conditions in WHERE clause • Find the name of the producer of ‘Star Wars’ © CIS 4301 - Spring 2006

  11. Disambiguating Attributes • Method 1: Prefixing the relation name • Find stars and movie executives with the same address SELECT MovieStar.name, MovieExec.name FROM MovieStar, MovieExec WHERE MovieStar.address = MovieExec.address; • Output: MovieStar.name MovieExec.name Jane Fonda Ted Turner © CIS 4301 - Spring 2006

  12. Star1.name Star2.name Alec Baldwin Kim Basinger Disambiguating Attributes • Method 2:Tuple variable • Find two stars with the same address SELECT Star1.name, Star2.name FROM MovieStar Star1, MovieStar Star2 WHERE Star1.address = Star2.address AND Star1.name< Star2.name; • Output: tuple variable • Can use tuple variables anytime for convenience and readability! © CIS 4301 - Spring 2006

  13. Star1.name Star2.name Alec Baldwin Kim Basinger Kim Basinger Alec Baldwin Disambiguating Attributes • What would be different if we re-wrote the query as: SELECT Star1.name, Star2.name FROM MovieStar Star1, MovieStar Star2 WHERE Star1.address = Star2.address AND Star1.name <> Star2.name; • Output: © CIS 4301 - Spring 2006

  14. Tuple Variables • Tuples variables are always used (implicitly) in SELECT and WHERE clause • If relation appears only once in the FROM clause, we use the relation name as its own tuple variable • Relation name R in FROM clause is shorthand for R R © CIS 4301 - Spring 2006

  15. Set Operators • Union: union • Intersection: intersect • Set Difference: minus • Eliminate duplicates • Use ALL to retain duplicates: e.g., UNION ALL • Find the names and addresses of movie stars who are not also movie execs © CIS 4301 - Spring 2006

  16. Sample Queries • Find the address of MGM studios. • Find all stars that appeared either in a movie made in 1988 or a movie with “Dollar” in the title. © CIS 4301 - Spring 2006

  17. Sample Queries • Find the names and addresses of all female movie stars who are also movie executives with a net worth over $10M. © CIS 4301 - Spring 2006

  18. Sample Queries • Which movies are longer than “Star Wars?” © CIS 4301 - Spring 2006

  19. Sample Queries • What is the result of the following query if T is empty? SELECT * FROM Movie WHERE length <= 120 OR length => 120; © CIS 4301 - Spring 2006

More Related