Week 18

CST 363 Week 2

This is the second week in CST-363, also known as Intro to Database Systems.

Questions

Think of example where joining on something other than keys would be needed.

• An example of this would be when you want to join to tables that don't have any keys in common, or when one table doesn't reference the other with foreign keys. In this case you could compare other columns when joining the tables, such as checking if a value in one table is high than the value of the another table.

What is your opinion of SQL as a language?

• I think the basic syntax is easier to learn and understand than other programming languages. I find myself completing simple queries faster than I can solve some basic problems in other programming languages. However, I find more complex SQL queries harder to understand and write. I think this is the case because SQL functions differently from other programming languages. When I first see a complex query I want to approach it like I would when I am working in a general programming language, which does not always work. I think the more I use SQL and learn about it, the more natural it will feel.

Textbook Section 3 Vocabulary

Special Operators and Clauses

BETWEEN: The BETWEEN operator provides an alternative way to determine if a value is between two other values.

• Example: WHERE HireDate BETWEEN '2000-01-01' AND '2020-01-01';

DISTINCT:  The DISTINCT keyword is used with a SELECT statement to return only unique or 'distinct' values.

• Example: SELECT DISTINCT Language

LIKE: The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

• % matches any number of characters. Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat". 
• _ matches exactly one character. Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot". 
• BINARY can be used to make the comparison case-sensitive.
• Example: WHERE Language LIKE BINARY '_A%';

IN: The IN operator is used in a WHERE clause to determine if a value matches one of several values.

• Example: WHERE Language IN ('Dutch', 'Kongo', 'Albanian');

ORDER BY: The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

• The DESC keyword with the ORDER BY clause orders rows in descending order.
• Example: ORDER BY Language;

Simple Functions

Number Functions

ABS(n): Returns the absolute value of n.

LOG(n): Returns the natural logarithm of n.

POW(x, y): Returns x to the power of y

RAND(): Returns a random number between 0 (inclusive) and 1 (exclusive).

ROUND(n, d): Returns n rounded to d decimal places.

SQRT(n): Returns the square root of n.

String Functions

CONCAT(s1, s2, ...): Returns the string that results from concatenating the string arguments.

LOWER(s): Returns the lowercase s.

REPLACE(s, from, to): Returns the string s with all occurrences of from replaced with to.

SUBSTRING(s, pos, len): Returns the substring from s that starts at position pos and has length len.

TRIM(s): Returns the string s without leading and trailing spaces.

UPPER(s): Returns the uppercase s.

Date Functions

CURDATE()

CURTIME()

NOW()

• Returns the current date, time, or date and time in 'YYYY-MM-DD', 'HH:MM:SS', or
  'YYYY-MM-DD HH:MM:SS' format

DATE(expr)

TIME(expr)

• Extracts the date or time from a date or datetime expression expr.

DAY(d)

MONTH(d)

YEAR(d)

•  Returns the day, month, or year from date d.

HOUR(t)

MINUTE(t)

SECOND(t)

• Returns the hour, minute, or second from time t.

DATEDIFF(expr1, expr2)

TIMEDIFF(expr1, expr2)

• Returns expr1 - expr2 in number of days or time values, given expr1 and expr2 are date, time, or datetime values.

Aggregate Functions

COUNT(): counts the number of rows in the set.

MIN(): finds the minimum value in the set.

MAX(): finds the maximum value in the set.

SUM(): sums all the values in the set.

AVG(): computes the arithmetic mean of all the values in the set.

GROUP BY: The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group.

HAVING: The HAVING clause is used with the GROUP BY clause to filter group results. The optional HAVING clause follows the GROUP BY clause and precedes the optional ORDER BY clause. 

Join Queries

Join: A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result. The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types. 

AS: Used to give a column an alias.

INNER JOIN: Selects only matching left and right table rows.

FULL JOIN: Selects all left and right table rows, regardless of match.  

LEFT JOIN: Selects all left table rows, but only matching right table rows.

RIGHT JOIN: Selects all right table rows, but only matching left table rows.

UNION: Combines the results of two SELECT clauses into one result table.

Equijoins, Self-Joins, and Cross-Joins

Equijoin: Compares columns of two tables with the = operator.

Non-Equijoin: Compares columns with an operator other than =, such as < and >.

Self-Join: Joins a table to itself.

Cross-Join: Combines two tables without comparing columns. 

Subqueries

Subquery: A subquery, sometimes called a nested query or inner query, is a query within another SQL query.

Correlated Subquery: A subquery is correlated when the subquery's WHERE clause references a column from the outer query.

EXISTS: Returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. 

NOT EXISTS: Returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.

View Tables

View Table: A view table is a table name associated with a SELECT statement, called the view query.

CREATE VIEW: The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names.

Base Table: A table specified in the view query's FROM clause.

WITH CHECK OPTION: When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.