Today's lecture is on Advanced SQL going beyond what you may or may not know about basic SQL.
Relational Algebra's goal is to describe the answer we want to compute rather than the exact steps on how to do it. This is when the advantage of SQL tells the data system how to do thing and figures out the optimal way to execute the query. The query optimizer takes the SQL query and converts it into the most efficient plan.
SQL was original "SEQUEL" from IBM's System R prototype and IBM releases DB2 in 1983. Back then people want to come up with their own language to do relational calculus, so IBM came up with SQL. The other major language was ingress that came up with Berkley. Ingress had the language called Quell, and IBM run. This is just a Structured Query Language. IBM was the juggernaut back then. '
Oracle was copying what IBM was doing in the 1970s and had people at the right place in the right time, and became a standard in 1986-1987.
SQL is not a dead language, it's like C++ changes specification and adds new feature every so often. The current standard is SQL:2016 with JSON and Polymorphic tables. Major Database companies tries to get certain functionality as part of their standard. Everyone has their proprietary things as part of the standard. The bare minimum you need to have is SQL 92 standards, SELECT INSERT UPDATE DELETE etc. The more advanced databases added much more features from the newer standards.
SQL is a collection of a Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL). SQL is based on bag algebra. You can have lists, sets, or bags. SQL is based on Bag Algebra. List can have duplicates but with a defined order (position). A set is unordered but you cannot have duplicates. A bag has neither has a set position or ordering, but it also allows for duplicates. If you want to define order or duplicates, the database has to
extra work to provide that type of ordering for you.
We will cover aggregation, output control, nested queries, common table expressions, and window functions.
Let's use a sample database with names logs and GPAs and then we have course table and enroll table and grade student got in the class.
Aggegates are functions that return a single value from a bag of tuples.
AVG(col) --> Return average col value
MIN(col) --> Return minimu col value
MAX(col) --> Return maximum col value
SUM(col) --> return sum col values
COUNT(col) --> return # values for col
To select the # of students where login has @cs say
SELECT COUNT(login) AS cnt
FROM student WHERE login LIKE '@cs;
or we can do
SELECT COUNT(*) AS cnt FROM student WHERE login LIKE '@cs';
SELECT COUNT(1) AS cnt FROM student WHERE login LIKE '@cs';
Let's say that we want to get average GPA, get the number of distinct elements for tuples, counting the number of unique logins from the tables/distinct side from the count. no two students can have the same login, but in other cases, we can apply them to other scenarios.
Now we want to get additional data from information. Let's say we want to get the average GPA from the course.
SELECT AVG(s.gpa), e.cid FROM enrolled AS e, student AS s WHERE e.sid = s.sid
The course id is not part of the aggregation, it doesn't know what course ID you actually want.
GROUPBY helps to bucket together the tuples in each output based on one attribute. So we can group by the Id. now and then compute the average GPA to combine the course id and now for each of these, now I'll compute the aggregate. Non-aggregated value in SELECT output clause must appear in the GROUP BY clause.
SELECT AVG(s.gpa), e.cid FROM enrolled AS e, student AS s WHERE e.sid = s.sid GROUP BY e.cid, s.name
Now let's produce average GPA, and filtering results doesn't work because we can't access things in the WHERE clause. The WHERE clause will filter, and after the filtering, we can then compute the aggregation. HAVING means you can reference anything in the output list.
SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING avg_gpa > 3.9;
So basically, HAVING helps to filter out these results.
SQL is case sensitives, and the way you declare this is single quotes except for MySQL and SQLite.
LIKE is used for string matching, matching some wild card in your string with another string, % means that we matches any substring including empty strings and _ matches any one character. Here are some examples:
SELECT * FROM enrolled AS e WHERE e.cid LIKE '15-%'
SELECT * FROM student AS s WHERE s.login LIKE '%c_'
The SQL system says we use 2 double bars '||' to concatenate two or more strings together.
SQL-92:
SELECT name FROM student WHERE login = LOWER(name) || '@cs'
MSSQL:
SELECT name FROM student WHERE login = LOWER(name) + '@cs'
MYSQL:
SELECT name FROM student WHERE login = CONCAT(LOWER(name), '@cs')
There are Operations that can manipulate DATE/TIME attributes, and syntax varies wildly. Let's get the number of days since the beginning of the year. We first get the current time.
This is often denoted as CURRENT_TIMESTAMP().
You can also select date
SELECT DATE('2018-08-29) - DATE('2018-01-01') AS days;
To get the difference of dates in MYSQL, you have to do something different. In mysql, we convert the dates into Unix Timestamps to determine the number of seconds.
SELECT ROUND((UNIX_TIMESTAMP(DATE('2018-08-29')) - UNIX_TIMESTAMP(DATE('2018-01-01))) / (60 * 60 * 24, 0) AS days;
Turns out there's an even easier way just have DATEDIFF
SELECT DATEDIFF(DATE('2018-08-29'), DATE('2018-01-01') AS days;
You have to convert the julian calendar in SQLite (the numbeer of dates since 4000 BC)
SELECT CAST((julianday(CURRENT_TIMESTAMP) - julianday('2019-01-01)) AS INT) AS days;
Every cell phone runs SQLite, Photoshop and Illustrator run SQLite on the inside. There's no copyright on SQLite.
We can take the output of a query and write it into another table. We store query results to another table.
CREATE TABLE CourseIds (SELECT DISTINCT cid FROM enrolled);
In output redirection, we can insert tuples from query inot another table, or inner SELECT must generate the same columns by the target table. Database management systems have different options on what to do. There has to enough columns for the table we're writing into.
Or we can enter the following things in the table: INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);
We can also do output control and to do this we add the ORDER BY clause, specifying how to sort the results generated by a query.
SELECT sid, grade FROM enrolled
WHERE cid = '15-721'
ORDER BY grade DESC.
We order by grade, and then sort by student Id in ascending order, given that the grades are the same. I'm sorting by the grade, but the grade isn't part of the output. It doesn't matter. I can also put any arbitrary expression in ORDERBY as well.
There's a limit clause, where we limit the number of TUPLES returned in the output, like providing the number of tuples, etc. LIMIT limits the number of tuples, and the offset says the offset you should skuo to figure out how many you should limit.
SELECT sid, name FROM student
WHERE login LIKE '@cs'
LIMIT 20 OFFSET 10. Offset can return a "range".
NESTED queries allow you to specify queries iwthin a query and they are difficult to optimize.
SELECT(SELECT 1) as one FROM student; (this can be rewrite this as a join).
Let's get all the student enrolled in CS 15-445 and we know we want the name from the student table.
How do we combine things from outer querie as an inner query? We use the IN operator and do a matching with the student_id. I want to see if there is a match in the set of all the student ids that take a particular course.
SELECT name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
WHERE cid = '15-445'
)
Here are some query tips:
ALL must satisfy expression for all rows in subquery.
ANY Must satisfy expression for at least one row in the sub query.
IN is equivalent to '=ANY()'. and EXISTS means at least one row is returned.
We can read this as "For every single tuple in the row table where id = 15-445 I'm going to do a matchup in the student table where the student ids are the same.
SELECT (SELECT S.name FROM student AS S WHERE S.sid = E.sid) AS sname
FROM enrolled AS E
WHERE cid = '15-445';
This is if you want to go to the E first. Select the students taking a particular class.
We can't reference a column in the aggregation.
SELECT MAX(e.sid), s.name FROM enrolled AS e, student AS s WHERE e.sid = s.sid.
The reason it deosn't work is because there's an AGGREGATION in the GROUP BY and we're referencing a column that's not in the aggregation. Again, an aggregation takes a tuple of inputs and produces a single scalar value as an output.
We use => all (greater than or equal to all)
SELECT sid, name FROM student WHERE sid => ALL (SELECT sid FROM enrolled).
WE can also rewrite it with an in clause, match the student Id, and we can order by the student ID rank them in descending order, and do a limit 1.
SELECT sid, name FROM student WHERE sid IN (
SELECT sid FROM enrolled
ORDER BY sid DESC LIMIT 1
)
Here is how to find all courses that have no student enrolled in it:
SELECT * FROM course WHRE NOT EXISTS (SELECT * FROM enrolled WHERE course.cid = enrolled.cid)
Window Functions perform calculation accross a set of tuples related to a single row. A window function is similar to an aggregation where you will compute some function on tuples. You do this in anincremental fashion, or in a moving output, so we produce teh tuple as an output with a function hame and over clause, defining how we actually want to slice up the data.. FUNCTION is like there aggregation function, OVER is like the GROUP_BY.
RANK - Order of position of TUPLE and ROW_NUMBER is # of current row. Doing this would result in a new special column, ROW_NUM.
SELECT *, ROW_NUMBER() OVER() AS row_num FROM enrolled.
PARTITION BY can specify how we want to group things, where we now group together based by the order. We can also do ORDER_BY.
SELECT cid, sid,
ROW_NUMBER() OVER (PARTITION BY cid)
FROM enrolled
ORDER BY cid
The over function specifies how to group together tuples when computing the window function.
SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid) FROM enrolled ORDER BY cid
SELECT *, ROW_NUMBER OVER (ORDER BY cid) FROM enrolled ORDER BY cid
We can also write to a temporary table that then gets discarded when the query is over. We go over the enroll table and for every single tuple, split them by COURSE ID and sort them by their grade in ascending order, and then we produce RANK() the order that the functions exist in a sorted ranking, and then take the output and do additional filtering based on the rank. We want to reference the RANK() attribute in the outer query.
SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY cid ORDER BY grade ASC) AS rank
FROM enrolled) AS ranking WHERE ranking.rank = 1.
The rank() function will be computed based on which tuple appears first, appears second, etc.
The order is where you appear in the output, and the rank is where you would appear in the sorted order. The RANK function produces the rank of the sort order. Remember, there's no sort order if there's no ranking.
Now let's talk about complex table expressions, and it provides a way to write auxillary statements for use in a larger query.
A groupby() isn't going to get the triples as part of the output anymore.
If we want to compute the max grade from enrolled group by courseID, the original get collapsed in the aggregate function.
SELECT *, min(grade) OVER (PARTITION BY cid) AS rank FROM enrolled;
You can rank each grade based on cid accordingly.
Removing the partition by now just gets 54321.
SELECT *, ROW_NUMBER() OVER() AS row_num from enrolled ORDER BY row_num DESC;
The way cte is going to work is we introduce WITH, which is a query that will execute before the regular query, and then we have AS, and the output of that query is mapped to the neighboring CTE, and we can reference it just as it is an existing database.
WITH ctename AS (SELECT 1) SELECT * FROM cteName
These have a single tuble with 2 columns and add them here:
WITH cteName (col1, col2) AS (SELECT 1, 2) SELECT col1 + col2 FROM cteName
Now let's find student record with the highest id enrolled in the course.
WITH cteSource(maxId) AS (SE:ECT MAX(sid) FROM enrolled) SELECT name FROM student, cteSource WHERE student.sid = cteSource.maxId
Allowing the RECURSIVE keyword after WITH allows the common table expression to reference itself. For example,
WITH RECURSIVE cteSource(counter) AS (
(SELECT 1)
UNION ALL
(SELECT counter + 1 FROM cteSource WHERE counter < 10)
SELECT * FROM cteSource;
We keep running this until our where clause gets picked up. UNION has the union without duplicates. UNION without all removes duplicates.
UNION combines the result of 2 or more select statements.
Here is the PostGRES statement that kills any query over 10 seconds:
SET statement_timeout = '10s';
Here's and infinite loop"
WITH RECURSIVE cteSource(counter) AS (
(SELECT 1)
UNION ALL
(SELECT 1)
SELECT * FROM cteSource;
UNION removes duplicates, UNION ALL does not.
You can't access a CTE table defined by yourself, RECURSIVE lets you do that.
We keep generating tuples until there aren't any more matches.
You should almost always strive to compute your answer as a single SQL statement, and having a CTE can help the SQL query computationally optimize things. CTEs are super common.
Every single database system will support some variant of SQL.
Comments
Post a Comment