Structured Query Language DML MIS 520 – Database Theory Fall

Structured Query Language DML MIS 520 – Database Theory Fall 2001 (Day) Lecture 10/11

SQL – Select Select List of Columns and expressions (usually involving columns) From List of Tables & Join Operators Where List of Row conditions joined together by And, Or, Not Group By list of grouping columns Having list of group conditions connected by And, Or, Not Order By list of sorting specifications

Conceptual Evaluation From Tables: Cross product and join operations Restriction on where conditions Group By? 1 2 Yes Sort on Group BY columns 3 No Compute aggregates and reduce each group to 1 row 4 Order By? No Yes Project columns in SELECT finish 7 Sort columns in ORDER BY 6 Restriction on HAVING conditions 5

SQL – DISTINCT Eliminates all the duplicate entries in the table resulting from the query. Syntax: Select [DISTINCT] select list From table[, table, ] [Where expression] [Order By expression] Example: Select DISTINCT studio id, director id From Movies studio id 1 2 2 3 director id 1 2 10 1 3 9

SQL – Order By Used to sort the results based on contents of a column Multiple levels of sort can be done by specifying multiple columns An expression can be used in Order By clause Syntax: Select function(column) From table1 [, table2 ] [Where condition] [Order By {Column alias position} [ASC DESC]]

SQL – Order By Example: Sort Movies by profits in Ascending order Select MovieTitle, Gross, Budget, (Gross – Budget) as profits From movies Order BY profits Movie title Gross Budget Profit 67.5 70 -2.5 Upside Down 54 50 4 Green Warrior 96 80 16 Blue Oranges 28 7 21 Great Escape

Aggregate Queries – Group By Categorizes the query results according to the contents of a column in the database Multiple levels of subgroups can be created by specifying multiple columns Syntax: Select column1, [column2, ] From table [, table ] [Where condition] Group By column1, [column2, .] Having [Condition]

Aggregate Queries – Group By Example: Get # of movies by each director for each studio Select studio id, director id, count(*) From Movies Group By director id, studio id Example: Get # of movies by each studio ordered by studio id Select studio id, count(*) From Movies Group By studio id Order By studio id

Aggregate Queries – Group By Example: Select studio id, Sum(budget) From movies Group by studio id Having Sum(budget) 60 Example: Select studio id, count(*) From Movies Group By studio id Order By studio id

Aggregate Queries Aggregate queries provides a more holistic view of the data by further processing the retrieved data. They can work on – – – On all the rows in a table A subset of rows in a table selected using a where clause Groups of selected data organized using Group By clause. Syntax: Select function(column) From list of tables Where condition Group By list of columns Having condition

Aggregate Queries Functions: – – – – – Sum() Count() Avg() Min() Max() Returns a sum of the column Returns a total number of rows returned by a query Returns the average of a column Returns minimum value of the column returned by query Returns maximum value of the column returned by query Notes 1: Count function does not include columns containing null values in total Notes 2: Count can be used with distinct to count the number of distinct rows Example: Query: Select sum(budget) From movies Where studio id 3 Output: Sum(budget) --------------65.1

SQL – Join A Join is a Query that combines data from multiple tables – Multiple tables are specified in the From Clause – For two tables to be joined in a sensible manner, they need to have data in common Example: Schema: Movies (movie title, director id, release date) People(person fname, person lname, person id) Query: Select movie title, person fname, person lname From Movies, People Where director id person id

SQL – Joining Condition For a useful Join query a joining condition is required – Defined in where clause as relationships between columns – Multiple conditions may be defined if multiple columns shared – More than two tables can be joined in a query Example: Find people who live in same state as studio Schema: Studios(studio id, studio state, studio name, studio city) People(person fname, person lname, person id, person state, person city) Query: Select person fname, person lname, studio name From Movies, People Where studio city person city AND studio state person state

SQL – More than two tables Example: Get title, director, studio, city for all movies in the database Schema: Studios(studio id, studio state, studio name, studio city) People(person fname, person lname, person id, person state, person city) Movies(movie title, director id, studio id) Query: Select M.movie title, M.studio id, P.person fname, P.person lname, S.studio city From Movies M, People P, Studio S Where M.director id P.person id AND M.studio id P.person id

SQL – Self Join Required to compare values within a single column – Need to define aliases for the table names Example: Find actors living in the same state Schema: People(person fname, person lname, person id, person state, person city) Query: Select p1.person id, p1.person fname, p1.person lname, p1.person state From People p1, People p2 Where p1.person state p2.person state AND p1.person id ! p2.person id Note: Distinct operator is critical because if there are more than two people from any state each person will appear as many times as there are people from the state

SQL-92 – Join More verbose than pervious versions of SQL – Need to define aliases for the table names Separates the condition for joining from condition for filtering Example: Find actors living in the same state Schema: People(person fname, person lname, person id, person state, person city) Movies(movie title, director id, studio id) Query: Select movie title, person fname, person lname From Movies INNER JOIN People ON director id person id Select movie title, person fname, person lname From Movies INNER JOIN People ON director id person id Where studio id 1

SQL-92 – Multiple Table Join Example: Get title, director, studio, city for all movies in database Schema: Studios(studio id, studio state, studio name, studio city) People(person fname, person lname, person id, person state, person city) Movies(movie title, director id, studio id) Query: Select Movies.movie title, Movies.studio id, Person.person fname, Person.person lname, Studio.studio city From (People Inner Join (Movies Inner Join Studio On Studio.studio id Movie.studio id) On Movie.director id Person.person id

SQL-92 – Left/Right Join Example: Schema: People(person fname, person lname, person id, person state, person city) Movies(movie id, movie title, director id, studio id) Location(movie id, city, state) Query: Select movie title, city, state From Movies Left Join Locations On Movies.movie id Locations.movie id Includes all non matched movie titles Select movie title, person fname, person lname From Movies Right Join People On Movies.director id Person.person id Includes all people not matching to directors

Nested Queries A sub query is a query nested within another query – The enclosing query also called outer query – Nested query is called inner query There can be multiple levels of nesting Example: Select movie title From movies Where director id IN ( Select person id From People Where person state ‘TX’)

Nested Queries - Types Non-Correlated Sub Queries: – – – Requires data required by outer query before it can be executed Inner query does not contain any reference to outer query Behaves like a function Example: People(person fname, person lname, person id, person state, person city) Movies(movie id, movie title, director id, studio id) Select movie title, studio id From Movies Where director id IN ( Select person id From People Where person state ‘TX’) Steps: 1. 2. 3. Subquery is executed Subquery results are plugged into the outer query The outer query is processed

Nested Queries - Types Correlated Sub Queries: – – Contains reference to the outer query Behaves like a loop Example: People(person fname, person lname, person id, person state, person city) Cast Movies(cast member id, role, movie id) Select person fname, person lname From People p1 Where ‘Pam Green’ in ( Select role From Cast Movies Where p1.person id cast member id ) Steps: – – – – Contents of the table row in outer query are read Sub-query is executed using data in the row being processed. Results of the inner query are passed to the where in the outer query The Outer query is Processed

Equivalent Join Query Example: People(person fname, person lname, person id, person state, person city) Cast Movies(cast member id, role, movie id) Select person fname, person lname From People, Cast Movies Where Cast member id person id And role ‘Pam Green’