Database System Basic - 1

Example Database Table

student (sid, name, login, gpa)

enrolled (sid, cid, grade)

course (cid, name)

(P.S. The SQL below all use example data table)

Basic Syntax

Basic

Ex: Which students age > 25?

SELECT name, gpa
  FROM student
  WHERE age > 25

Joins

Ex: Which students got an A in 15-721?

xxxxxxxxxx
SELECT s.name
  FROM enrolled AS e, student AS s
  WHERE e.grade = 'A' AND e.cid = '15 - 721' 
    AND e.sid = s.sid

A. Aggregates

Ex: Get # of students with a "@cs" login?

xxxxxxxxxx
SELECT COUNT(login) AS cnt
  FROM student WHERE login LIKE '%@cs'

xxxxxxxxxx
SELECT COUNT(*) AS cnt
  FROM student WHERE login LIKE '%@cs'

xxxxxxxxxx
SELECT COUNT(1) AS cnt
  FROM student WHERE login LIKE '%@cs'

Multiple Aggregates

xxxxxxxxxx
SELECT AVG(gpa), COUNT(sid)
  FROM student WHERE login LIKE '%@cs'

Distinct Aggregates

xxxxxxxxxx
SELECT COUNT(DISTINCT login)
  FROM student WHERE login LIKE '%@cs'

Example:

Get the average GPA of students enrolled in each course?

xxxxxxxxxx
SELECT AVG(s.gpa), e.cid
  FROM enrolled AS e, student AS s
  WHERE e.sid = s.sid

B. Group By

Project tuples into subsets and calculate aggregates against each subset.

xxxxxxxxxx
SELECT AVG(s.gpa), e.cid
  FROM enrolled AS e, student AS s
  WHERE e.sid = s.sid
  GROUP BY e.cid

Display:

Non-aggregated values in SELECT output clause must appear in GROUP BY clause. For example:

xxxxxxxxxx
SELECT AVG(s.gpa), e.cid, s.name
  FROM enrolled AS e, student AS s
  WHERE e.sid = s.sid
  GROUP BY e.cid, s.name

C. Having

Filters results based on aggregation computation. Like a WHERE clause for a GROUP BY.

xxxxxxxxxx
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;

Display:

D. String Operations

SQL-92:

xxxxxxxxxx
WHERE UPPER(name) = UPPER('KaNyE')

MySQL:

xxxxxxxxxx
WHERE name = "KaNyE"

1. LIKE Operators

LIKE: is used for string matching.

String-matching Operators

'%' --> Matches any substring (include empty strings)

'_' --> Match any one character

xxxxxxxxxx
SELECT * FROM enrolled AS e
  WHERE e.cid LIKE '15-%'

xxxxxxxxxx
SELECT * FROM student AS s
  WHERE s.login LIKE '%@c_'

2. || Operator (use in SQL-92)

xxxxxxxxxx
SELECT name FROM student
  WHERE login = LOWER(name) || '@cs'

But in MySQL use CONCAT()

xxxxxxxxxx
SELECT name FROM student
  WHERE login = CONCAT(LOWER(name), '@cs')

E. Date / Time Operations

SQL-92 & MySQL:

xxxxxxxxxx
SELECT NOW();

...

F. Output Redirection

1. Store

Store query results in another table:

SQL-92:

xxxxxxxxxx
SELECT DISTINCT cid INTO CourseIds
  FROM enrolled;

MySQL:

xxxxxxxxxx
CREATE TABLE CourseIds (SELECT DISTINCT cid FROM enrolled);

2. Insert

Insert tuples from query into another table:

--> Inner SELECT must generate the same columns as the target table

SQL-92:

xxxxxxxxxx
INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled);

G. Output Control

1. ORDER BY <column*> [ASC | DESC]

--> Order the output tuples by the values in one or more of their columns.

xxxxxxxxxx
SELECT sid, grade FROM enrolled
  WHERE cid = '15-721'
  ORDER BY grade

xxxxxxxxxx
SELECT sid, grade FROM enrolled
  WHERE cid = '15-721'
  ORDER BY 1

xxxxxxxxxx
SELECT sid FROM enrolled
  WHERE cid = '15-721'
  ORDER BY grade DESC, sid ASC

#3 Display ==>

xxxxxxxxxx
SELECT sid FROM enrolled
  WHERE cid = '15-721'
  ORDER BY grade DESC, 1 ASC

2. LIMIT [offset]

--> Limit the # of tuples return in output

--> Can set an offset to return a "range"

xxxxxxxxxx
SELECT sid, name FROM student
  WHERE login LIKE '%@cs'
  LIMIT 10

xxxxxxxxxx
SELECT sid, name FROM student
  WHERE login LIKE '%@cs'
  LIMIT 20 OFFSET 10

H. Nested Queries

Queries containing other queries.

Inner queries can appear anywhere in query.

xxxxxxxxxx
SELECT name FROM student WHERE
  sid IN (SELECT sid FROM enrolled)

inside ( ): Inner Query

outside ( ): Outer Query

Ex. Get the names of students in '15-445'

xxxxxxxxxx
SELECT name FROM student 
  WHERE sid IN(
    SELECT sid FROM enrolled
    WHERE cid = '15-445'
  )

NOT EXISTS

Ex. Find all courses that have no students enrolled in it.

xxxxxxxxxx
SELECT * FROM student 
  WHERE NOT EXISTS(
    SELECT * FROM enrolled
    WHERE course.cid = enrolled.cid
  )

Display ==>

I. Window Functions

1. Special window functions:

--> ROW_NUMBER( ) -> # of the current row

--> RANK( ) -> Order position of the current row

xxxxxxxxxx
SELECT *, ROW_NUMBER() OVER () AS row_num
  FROM enrolled

2. OVER Keyword

The OVER keyword specifies how to group togther tuples when computing the window function.

Use PATITION BY to specify group. (also can use ORDER BY)

xxxxxxxxxx
SELECT cid, sid, 
  ROW_NUMBER() OVER (PARTITION BY cid)
  FROM enrolled
  ORDER BY cid

Ex. Find the student with the second highest grade for each course.

xxxxxxxxxx
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY cid
            ORDER BY grade ASC) AS rank
  FROM enrolled) AS ranking
  WHERE ranking.rank = 2

J. Common Table Expressions

Alternative to nested queries and views.

xxxxxxxxxx
WITH cteName (col1, col2) AS (
  SELECT 1, 2
)
SELECT col1 + col2 FROM cteName

Ex. Find student record with the highest id that is enrolled in at least one course.

xxxxxxxxxx
WITH cteSource (maxId) AS (
  SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSourse
  WHERE student.sid = cteSource.maxId

K. CTE - Recursion

Ex. Print the sequence of numbers from 1 to 10

xxxxxxxxxx
WITH RECURSIVE cteSourse (counter) AS (
  (SELECT 1)
  UNION ALL
  (SELECT counter + 1 FROM cteSource
    WHERE counter < 10)
)
SELECT * FROM cteSource