3. Query Primer.md

Query Primer

• You will gain a basic understanding of how data is retrieved, joined, filtered, grouped, and sorted

Query Mechanics

• Each time a query is sent to the server, the server checks the following things prior to statement execution
  • Do you have premission to execute this premission?
  • Do you have permission to access the desired data?
  • Is the syntax of your statement correct?
• One your server accepts the query, it passes the query onto the query optimizer
  • Who determines the most effecient way to execute your query
    • order in which to perform your joins
    • what indexes are available
    • selects an execution plan
• High Performance MySQL ocovers how to imporove the performance of your mysql server. It covers generating indexes, analyzing execution plans, query hints to influence the optimizer, and tuning your servers startup parameters
• After completing the execution of the query, a result set is returned to the callling application

Query Clauses

SELECT: Determines which columns to include in the results set
FROM: Identifies which talbes to retrieve data from 
WHERE: FIlter out unwanted data
GROUP BY: group rows together by a common column value
HAVING: filter out unwanted groups
ORDER BY: sort rows by one or more columns

The Select Clause

• First clause of a query, the last clause the server evaluates
• The select clause determines which of all possible columsn should be included in the query's result set
• You are not limited to column names in the select clause, you can include things like
  • Literals
  • Expressions
  • Built-in function calls
  • User-defined function calls

SELECT language_id,
	'COMMON' language_usage,
	language_id * 3.14 lang_pi_value,
	upper(name) language_name
FROM language;

• If you query doesn't retrieve data from a table you can ignore the FROM clause

SELECT version(),
	user(),
	database();

Column Aliases

• Somtimes you may want to assign your own labels to you queries
  • You may do it to relabel a column name in your results set
  • You may need to do it to relabel a column in your results set that is generated by an expression or built-in function
• You relabel columns using column aliases
• Sometimes to make your column aliases stand out more you can choose to include the AS keyword before the alias

SELECT language_id,
	'COMMON' AS language_usage,
	language_id * 3.14 AS lang_pi_value,
	upper(name) AS language_name
FROM language;

Removing Duplicates

• Sometimes a query may return duplicate rows of data
• Sometimes you wanted a distinct set of rows
• You can use the DISTINCT keyword directly after the SELECT keyword

SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id;

• If you wanted a list of all actors, it would be better to query the actor table instead of reading the filtering the film_actor table
• Generatoring a distinct set of results can be time consuming since it requires the data to be sorted. Time your time to understand the data before you use distinct hapharzardly

The from clause

• The FROM clause can accept a list of one or more tables
• The FROM clause defines the tables used by a query, along with the means of linking the tables together
  • This defintion comprimises of two separate but related concepts

Tables

• Most people think that the definition of table is a set of rows stored in a database
• It's better to think of tables as just of collection of related rows
• 4 types of tables:
  • Permanent tables: create table
  • Derived tables: subquery tables
  • Temporary tables: data held in memory
  • Virtual Tables: create view

Dervied (subquery-generated) tables

• A query contained within another query
• the subquery is surrounded by parentheses
• can be found in various parts of a select statement
• Within the from clause, the subquery is visible to all other query clauses and can interact with tables also within the from clause

SELECT 
	concat(cust.last_name, ',', cust.first_name) full_name 
FROM 
	(
	SELECT 
		first_name, last_name, email 
	FROM customer 
	WHERE first_name = 'JESSIE'
	) cust;

• The subquery queries the customer table while the containing query references the subquery via an alias
• This is a simplistic but not useful exmaple of a subquery in a FROM clause

Temporary Tables

• Every database system allows the creation of volatile/temporary tables
• Look like permanent tables but data inserted into it dissappears
• Actors whose last name start with J can be stored temporarily

CREATE TEMPORARY TABLE actors_j
	(
	actor_id smallint(5),
	first_name varchar(45),
	last_name varchar(45)
	)
;

INSERT INTO actors_j
SELECT
	actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'J%'
;

• Rows are held in memory and will disappear after session is closed
• Most database serves drop the temp table when the session ends. Oracle db tho will keep the definition of the temp table for future use

Views

• A query that is stored in the data dictionary
• Looks and acts like a table but there is no data associated with it
• When you query against a view, your query is merged against the view defintion to create the final query to execute
• A view defintion that queries against the employee table

CREATE VIEW cust_vw AS
SELECT customer_id, first_na,e, last_name, active
FROM customer;

SELECT 
	first_name, last_name
FROM customer_vw
WHERE active = 0;

• Views are used for many reasons, including hiding columns from users and simplifying database designs

Table Links

• If more than one table appears in the from clause, then the conditions used to link the tables must be included as well
• This is an ANSI-approved method of joining tables so this applies to various database servers

SELECT 
	customer.first_name, customer.last_name, time(rental.rental_date) rental_time
FROM customer
	INNER JOIN rental
	ON customer.customer_d = rental.customer_id
WHERE date(rental.rental_date) = "2005-06-14"

Defining Table Aliases

• When join multiple tables in a single query, you need to identify which table you are referring too when you reference a column in select, where, group, by, having, order by clauses
• Two ways
  • Reference the table's entire name employee.emp_id
  • Assign and reference a table using an alias

SELECT 
	c.first_name, c.last_name, time(r.rental_date) rental_time
FROM customer c
	INNER JOIN rental r
	ON c.customer_d = r.customer_id
WHERE date(r.rental_date) = "2005-06-14"

SELECT 
	c.first_name, c.last_name, time(r.rental_date) rental_time
FROM customer AS c
	INNER JOIN rental AS r
	ON c.customer_d = r.customer_id
WHERE date(r.rental_date) = "2005-06-14"

The where Clause

• The where clause allows you to filter out unwanted rows in your results set

SELECT
	title
FROM film
WHERE rating = 'G' AND rental_duration >= 7;

• You can include as many filter conditions as required
• Filtering conditions are separated with an and, or or not
• If you need to use both and and or operators, you should use parentheses to group conditions together

SELECT title, rating, rental_duration
FROM film
WHERE 
	(rating = 'G' AND rental_duration >= 7)
OR (rating) = 'PG-13' AND rental_duration < 4);

The group by and having clauses

• When using the group by clause to generate groups of data, you may also use the having clause to filter the grouped data

SELECT
	c.first_name, c.last_name, count(*)
FROM customer c
	INNER JOIN rental r
	ON c.customer_id = r.customer_id
GROUP BY c.first_name, c.last_name
HAVING count(*) >= 40;

The order by Clause

• Sorting your result set using raw column data or expressiosn based on column data

SELECT 
	c.first_name, c.last_name, time(r.rental_date) rental_time
FROM customer c
	INNER JOIN rental r
	ON c.customer_id = c.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name, c.first_name
;

• The order in which columns appear in your order by clause does make a difference

Ascending Versus Descending Sort Order

• By default sorting is by in ascending order (asc),, to sort the data in descending mode use the desc keyword

Sorting via Numeric Placeholders

• Sometimes you can reference a column by their position in the select clause, this is useful if you are sorting an expression

SELECT
	c.first_name, c.last_name, time(r.rental_date) rental_time
FROM customer c
	INNER JOIN rental r
	ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY 3 desc;

Test your Knowledge

• Retrieve the actor ID, first name, and last name for all actors. Sort by last name and then by first name.

SELECT
	actor_id, first_name, last_name
FROM actor
ORDER BY last_name, first_name;

• Retrieve the actor ID, first name, and last name for all actors whose last name equals 'WILLIAMS' or 'DAVIS' .

SELECT
	actor_id, first_name, last_name
FROM actor
WHERE last_name = 'WILLIAMS' or last_name = 'DAVIS';

• Write a query against the rental table that returns the IDs of the customers who ren‐ ted a film on July 5, 2005 (use the rental.rental_date column, and you can use the date() function to ignore the time component). Include a single row for each distinct customer ID.

SELECT 
	DISTINCT(customer_id), rental_date
FROM rental
WHERE date(rental_date) = '2005-07-05';

• Fill in the blanks (denoted by <#> ) for this multitable query to achieve the following results:

SELECT 
	c.email, r.return_date
FROM customer c
	INNER JOIN rental <1>
	ON c.customer_id = <2>
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY <3> <4>;

SELECT
	c.email, r.return_date
FROM customer c
	INNER JOIN rental r
	ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY r.rental_date DESC