SQL – the real things

1. SQL – the real things Chapter 4

2. Structured Query Language • Structured Query Language (SQL) was developed by the IBM Corporation in the late 1970s. • SQL was endorsed as a United States national standard by the American National Standards Institute (ANSI) in 1992 [SQL-92].

3. SQL as a Data Sublanguage • SQL is not a full featured programming language as are C, C#, and Java. • SQL is a data sublanguage for creating and processing database data and metadata. • SQL is ubiquitous in enterprise-class DBMS products. • SQL programming is a critical skill.

4. The SQL SELECT Statement • The simplest sturcute for SQL query states is the SQL SELECT statement: SELECT {ColumnName(s)} FROM {TableName(s)} • All SQL statements end with a semi-colon (;), unless you are using Microsoft products such as SQL Server or Access

5. Specific Columns on One Table SELECT PERSON_GIVEN_NAME, PERSON_FAMILY_NAME, PERSON_ADDRESS_COUNTRY FROM PERSON; Get every person’s given name, family name, and country

6. Change the column order SELECT PERSON.PERSON_FAMILY_NAME, PERSON.PERSON_GIVEN_NAME, PERSON.PERSON_ADDRESS_COUNTRY FROM PERSON; Get every person’s family name, given name, and country

7. The DISTINCT Keyword SELECT PERSON_FAMILY_NAME FROM PERSON; • SELECT DISTINCT PERSON_FAMILY_NAME FROM PERSON; DISTINCT removes all duplicated rows

8. Show everything SELECT * FROM LANGUAGE; This one shows the entire table

9. Specific Rows from One Table SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON.PERSON_FAMILY_NAME ='Schmidt'; NOTE: SQL wants a plain ASCII single quote: ' NOT ‘!

10. Sorting the Results: ORDER BY SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_FAMILY_NAME ='Schmidt‘ ORDER BY PERSON_GIVEN_NAME

11. Sorting the Results: ORDER BY SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_FAMILY_NAME ='Schmidt‘ ORDER BY PERSON_GIVEN_NAME DESC NOTE: The default sorting order is ASC – does not have to be specified.

12. WHERE Clause Options: AND SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_FAMILY_NAME='Schmidt' AND PERSON_GIVEN_NAME = 'Karl'

13. WHERE Clause Options: OR SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_FAMILY_NAME='Schmidt' OR PERSON.PERSON_ADDRESS_STATE_PROV='CO‘

14. WHERE Clause Options: OR SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_ADDRESS_STATE_PROV ='CA' OR PERSON_ADDRESS_STATE_PROV='CO'

15. WHERE Clause Options: IN SELECT PERSON_FAMILY_NAME, PERSON_GIVEN_NAME FROM PERSON WHERE PERSON_ADDRESS_STATE_PROV in ('CA', 'CO') NOTE: This is the same result but very different approach!

16. WHERE Clause Options:LIKE and Wildcards • The SQL keyword LIKE can be combined with wildcard symbols: • SQL 92 Standard (SQL Server, Oracle, etc.): • _ = Exactly one character • % = Any set of one or more characters • MS Access (based on MS DOS) • ? = Exactly one character • * = Any set of one or more characters

17. Like In MS Access -- list languages with the letter c in names SELECT LANGUAGE.* FROM [LANGUAGE] WHERE LANGUAGE.[language_name] Like '*c*'; In other DBMS and standard, this should be SELECT LANGUAGE.* FROM [LANGUAGE] WHERE LANGUAGE.[language_name] Like ‘%c%';

18. Subquery • Generally, another SELECT statement can be part of the FROM clause or the Where clause. When it is part of the WHERE clause, we call it a subquery. • For example – list movie titles that are more expensive than average VHS price SELECT.MOVIE_TITLE FROM MOVIE WHERE RETAIL_PRICE_VHS> ( SELECT avg(MOVIE.RETAIL_PRICE_VHS) FROM MOVIE);

19. Subquery (2) SELECT MOVIE_TITLE, MPAA_RATING_CODE FROM MOVIE where MPAA_RATING_CODE in (select MPAA_RATING_CODE from MPAA_RATING where MPAA_RATING_CODE like 'PG%‘ )

20. Join • Like subquery, join allows you to take advantage of this “Integrated” characteristic of databases • When pulling (displaying) data from two tables, you must use join • Join start from PRODUCT, see page 128 and 129 for an example and discussion on PRODUCT or CARTESIAN PRODUCT • JOIN, without indexes, is the most expensive operation

21. Join example • List the movie title and genre description SELECT MOVIE_TITLE, MOVIE_GENRE_DESCRIPTION FROM MOVIE_GENRE INNER JOIN MOVIE ON MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE; Or SELECT MOVIE_TITLE, MOVIE_GENRE_DESCRIPTION FROM MOVIE_GENRE, MOVIE Where MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE; These two SQL statements ALWAYS give the same result

22. Join Result

23. Join example -- more • List the movie titles and genre descriptions for movies with title starting with letter c SELECT MOVIE_TITLE, MOVIE_GENRE_DESCRIPTION FROM MOVIE_GENRE INNER JOIN MOVIE ON MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE where movie_title like 'c*‘ SELECT MOVIE_TITLE, MOVIE_GENRE_DESCRIPTION FROM MOVIE_GENRE, MOVIE Where MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE and movie_title like 'c*'

24. Subqueries versus Joins • Subqueries and joins both process multiple tables. • Since SQL statement can only “output” data on the top level, columns in a subquery cannot be displayed if they are not from the top level tables. • Join can be used together with subquery. • All the other clause such as WHERE and ORDER BY can be combined with JOINs and subqueries

25. Outer Join • The following statement SELECT MOVIE_GENRE_DESCRIPTION, MOVIE_TITLE FROM MOVIE_GENRE Inner JOIN MOVIE ON MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE; Shows a genre description iff a movie of the genre exists • This statement SELECT MOVIE_GENRE_DESCRIPTION, MOVIE_TITLE FROM MOVIE_GENRE LEFT OUTER JOIN MOVIE ON MOVIE_GENRE.MOVIE_GENRE_CODE = MOVIE.MOVIE_GENRE_CODE Shows ALL genre description regardless if a movie of the genre exists, if no movie exists, show NULL on the title

26. Inner Join vs. Outer Join

27. Self Join SELECT A.PERSON_ID, B.PERSON_ID FROM EMPLOYEE AS B INNER JOIN EMPLOYEE AS A ON B.SUPERVISOR_PERSON_ID = A.PERSON_ID; Shows the manager’s id and her people. A few things to notice: (1) we can rename our tables, and (2) we can join a table with itself only after renaming it

28. More interesting self join SELECT PA.PERSON_GIVEN_NAME + ' '+ PA.PERSON_FAMILY_NAME + ' manages ' + PB.PERSON_GIVEN_NAME + ' '+ PB.PERSON_FAMILY_NAME as Manager FROM PERSON AS PB INNER JOIN (PERSON AS PA INNER JOIN (EMPLOYEE AS B INNER JOIN EMPLOYEE AS A ON B.SUPERVISOR_PERSON_ID = A.PERSON_ID) ON PA.PERSON_ID = A.PERSON_ID) ON PB.PERSON_ID = B.PERSON_ID;

29. Even more self join • List movies with the same price SELECT M2.MOVIE_TITLE, Movie.MOVIE_TITLE, MOVIE.RETAIL_PRICE_VHS, M2.RETAIL_PRICE_VHS FROM MOVIE INNER JOIN MOVIE AS M2 ON (MOVIE.RETAIL_PRICE_VHS = M2.RETAIL_PRICE_VHS) and movie.movie_id < m2.movie_id;

30. SQL Built-in Functions • There are five SQL Built-in Functions: • COUNT • SUM • AVG • MIN • MAX

31. Buildin Function Example SELECT Count(*) AS Movie_Count, Avg(RETAIL_PRICE_DVD) AS Average_DVD_Price, Min(RETAIL_PRICE_DVD) AS min_DVD_Price FROM MOVIE; Notice, that this always return just one row. If two or more rows are necessary, we need to use GROUP BY

32. Build in Function With – GROUP BY SELECT MOVIE_GENRE_CODE, Count(*) AS Movie_Count, Avg(RETAIL_PRICE_DVD) AS Average_DVD_Price, Min(RETAIL_PRICE_DVD) AS min_DVD_Price FROM MOVIE group by MOVIE_GENRE_CODE

33. Build in Function With – GROUP BY SELECT MOVIE_GENRE_CODE, Count(*) AS Movie_Count, Avg(RETAIL_PRICE_DVD) AS Average_DVD_Price, Min(RETAIL_PRICE_DVD) AS min_DVD_Price FROM MOVIE group by MOVIE_GENRE_CODE HAVING count(*) > 2

34. DDL – Create table CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id))

35. Update operators in SQL - Insert • Insert with values INSERT INTO TEMP (PNo, Color) Values(‘P200’, ‘Green’) • Insert is implemented by INSERT INTO INSERT INTO TEMP (PNo, Color) SELECT PNumber, Color FROM P WHERE COLOR = ‘Red’;

36. Update operators in SQL - Delete • Delete is implemented by DELETE DELETE FROM SP WHERE PNO = ‘P2’; • Deletes all shipments for part P2 • To delete P2 from part, you need to delete P2 records from SP first

37. Update operators in SQL - Update • Update is implemented by UPDATE UPDATE S SET STATUS = 2 * STATUS, CITY = ‘Rome’ WHERE CITY = ‘Paris’; • Doubles the status of the Parisian suppliers and moves them to Rome.

38. Some books • SQL Cookbook (Cookbooks (O'Reilly)) • SQL in a Nutshell (In a Nutshell (O'Reilly)) • Sams Teach Yourself SQL in 10 Minutes (4th Edition) • Microsoft SQL Server 2012 T-SQL Fundamentals