IS 324/325 Notes for Wednesday October 30, 2013

1. IS 324/325 Notes for Wednesday October 30, 2013

2. Class Notes Exam #1: grading not complete… will e-mail your grade to you on Friday or over the weekend Homework: graded on Friday or over the weekend… grades will be e-mailed to you Reading assignment for Nov 4 has been changed No class on Wednesday, Nov 6

3. Let’s Get Our “SQL Feet” Wet Let’s play in the sandbox: http://edu.konagora.com/SQLsandbox.php#

4. Homework 1 Question 1 List the names and ages (in years) of all the actors who have not died. SELECT first_name, surname, 2013 - born as age FROM people WHERE died IS NULL ;

5. Homework 1 Question 2 List the names and the release year of all the movies that were released in 1956. SELECT title, year_released FROM movies WHERE year_released = 1956 ;

6. Homework 1 Question 3 List  the names and the country code of all the French movies (the 2-letter identified for France is fr). SELECT title, country FROM movies WHERE country = "fr" ;

7. Homework 1 Question 4 List the names, the release year, and the country code for all the American or British movies released between 1940 and 1949 (the 2-letter identified for the United States is us and Great Britain is gb). SELECT title, year_released , country FROM movies WHERE year_released BETWEEN 1940 AND 1949 AND (country = "us" OR country = "gb" );

8. Homework 1 Question 5 List the names of all the movies that begin with the capital letter T. SELECT title FROM movies WHERE title LIKE "T%" ;

9. Practice Query (1a) Can you replace the "OR" clause below? SELECT title, year_released , country FROM movies WHERE year_released BETWEEN 1940 AND 1949 AND (country = "us" OR country = "gb" );

10. Practice Query (1b) Can you replace the "OR" claue below? SELECT title, year_released , country FROM movies WHERE year_released BETWEEN 1940 AND 1949 AND country IN ("us" , "gb" ) ;

11. Practice Query (2a) List all the movies that do not begin with a capital T

12. Practice Query (2b) List all the movies that do not begin with a capital T SELECT title FROM movies WHERE title NOT LIKE "T%" ;

13. Practice Query (3a) List all the movie titles and their release years in this format: XXXXXXXXX was released in YYYY.

14. Practice Query (3b) List all the movie titles and their release years in this format: XXXXXXXXX was released in YYYY. SELECT title || " was released in " || year_released || "." FROM movies ;

15. Practice Query (3c) List all the movie titles and their release years in this format: XXXXXXXXX was released in YYYY. SELECT title || " was released in " || year_released || "." AS Output_Sentence_Demo FROM movies ;

16. Practice Query (4a) List all the movie titles and their county in this format for French and British movies: XXXXXXXXX is a French movie. YYYYYYYYY is a British movie.

17. Practice Query (4b) List all the movie titles and their county in this format for French and British movies: XXXXXXXXX is a French movie. YYYYYYYYY is a British movie. SELECT title || " is a " || CASE country WHEN "fr" THEN "French" WHEN "gb" THEN "British" END || " movie." FROM movies ;

18. Practice Query (4c) SELECT title || " is a " || CASE country WHEN "fr" THEN "French" WHEN "gb" THEN "British" ELSE "country of unknown origin " END || "movie." FROM movies ;

19. Practice Query (5a) How do we remove the duplicates? SELECT country FROM movies ;

20. Practice Query (5b) How do we remove the duplicates? SELECT DISTINCT country FROM movies ;

21. Practice Query (6a) How do we capitalize the first letter of every word in the title? SELECT title FROM movies ;

22. Practice Query (6b) How do we capitalize the first letter of every word in the title? SELECT initcap (title) FROM movies ;

23. Practice Query (7a) Who died most recently?

24. Practice Query (7b) Who died most recently? SELECT first_name || " " || surname, died FROM people WHERE died = ( SELECT max(died) FROM people) ;

25. Practice Query (8a) How many movies are there?

26. Practice Query (8b) How many movies are there? SELECT COUNT (*) FROM movies ;

27. Practice Query (9a) How many movies are there by country? SELECT COUNT (*) FROM movies ;

28. Practice Query (9b) How many movies are there by country? SELECT COUNT (*), country FROM movies GROUP BY country ORDER BY 1 ;

29. Practice Query (9c) How many movies are there by country? Can I put them in order by "number of movies"? SELECT COUNT (*), country FROM movies GROUP BY 1 ; SELECT COUNT (*) AS Num_Movies, country FROM movies GROUP BY Num_Movies ;

30. Practice Query (9c) How many movies are there by country? Can I put them in order by "number of movies"? SELECT COUNT (*), country FROM movies GROUP BY country ORDER BY 1 ; SELECT COUNT (*) AS Num_Movies, country FROM movies GROUP BY country ORDER BY Num_Movies DESC ;

31. Practice Query (10a) What is the title of the oldest movie from each country?

32. Practice Query (10b) What is the title of the oldest movie from each country? SELECT country, title, MIN(year_released) FROM movies GROUP BY country ;

33. Big Data... Almost the Opposite of Relational Model What is it and why is it important? http://www.youtube.com/watch?v=7D1CQ_LOizA http://www.youtube.com/watch?v=R-bypPCIE9g