1 / 38

SQL – the real things

SQL – the real things. Chapter 4. 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 ].

zia
Download Presentation

SQL – the real things

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. 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

More Related