• The query language for RDBMS
• SQL has many aspects
  • DDL, DML, transactions
• SQL and DBMS
  • SQL is a high-level description of what a user wants
  • Given SQL query, DBMS figures out how best to execute it automatically

SELECT title, instructor FROM Class WHERE dept = 'CS';

Basic SQL SELECT statement

If we want to remove duplicates, we need to add DISTINCT after SELECT

SELECT DISTINCT * FROM Enroll;
# ^ those commands are actually case-insensitive, but the name of the tables are not

SELECT DISTINCT name, GPA # distinct will only remove duplicates for the selected tuple
FROM Enroll E,Student S # join Enroll and Student and rename the column name
WHERE dept='CS' AND E.sid=S.sid; 

SELECT name, GPA
FROM Student
WHERE addr LIKE '%Wilshire%';
# LIKE: instead of interpreting as a regular string, interpret them as string with 
# special commands
# % : string with any length
# _ : one character

2 queries are logical equivalent if and only if the 2 queries returns exactly the same tuples under any database instances

Set Operators in SQL

• UNION, INTERSECT, EXCEPT
  • Can be applied to relations or to the result of SELECT statements
• Schemas of input relations should be the same
  • In practice, just having the compatible types is fine
• Set operators follow set semantics and remove duplicates
  • No efficiency penalty for duplicate eliminate for set operation
  • To keep duplicates, use UNION ALL, INTERSECT ALL, EXCEPT ALL
  • MySQL supports only UNION, but not INTERSECT or EXCEPT
    • People often use a "subquery"to simulate EXCEPT ("NOT IN")