Introduction to SQL

by K. Yue

1. Introduction

• SQL (Structured Query Language): defacto standard for relational databases.
• Contains core specifications and extensions.
• Unfaithful implementation of the pure relational model: e.g.
  • row, column and table instead of tuple, attribute and relation.
  • results may not be a set.
• DBMS specific extensions are common.
• Contains both DDL (Data Definition Language) and DML (Data Manipulation Language)
• We are supposed to be familiar with basic SQL.
• Useful resources:
  • MySQL 5.6 Manual.
  • w3schools SQL.

2. Exercises and Examples of Basic SQL

We will use MySQL. Either download your own version of MySQL or connect to the MySQL server at dcm.uhcl.edu (port 3306).

• Note that different DBMS support different versions of SQL. They may not fully support the standards and may include extensions.
• For example, MySQL does not support EXCEPT, which is in ANSI SQL 92.
• When developing database in a given DBMS, portability issue is a consideration for your choice of SQL statements.
• SQL is not based on a pure relational model. For example:
  • Query results may contain duplicate rows.
  • Order may be important.
• Easy to learn.
• Essentially non-procedural.

DDL:

• Make sure that you are familiar with the core SQL DDL commands. Refer to, for example: http://www.w3schools.com/sql/default.asp.
• Basic
  • CREATE TABLE
  • CREATE DATABASE
  • CREATE VIEW
  • CREATE INDEX
  • DROP
  • ALTER
• Constraint: to implement certain constraints in your data model.
  • NOT NULL:
  • UNIQUE: KEY; the set of attributes must be unique.
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK: for a Boolean conditions on the columns.
  • DEFAULT: insert a default.
• Other options:
  • AUTO INCREMENT
  • TOP: LIMIT in MySQL, number of results returned.

Examples:

-- Drop tables before creating them again.
-- Note that Supply needs to be dropped first.
-- Otherwise referential integrity may be violated.
DROP TABLE SUPPLY;
DROP TABLE PART;
DROP TABLE SUPPLIER;

-- Create tables
CREATE TABLE SUPPLIER (
  SNUM varchar(9),
  SNAME varchar(15),
  SCITY varchar(15),
  STATUS integer(3),
  primary key (SNUM)
);

CREATE TABLE PART (
  PNUM varchar(9),
  PNAME varchar(15),
  COLOR varchar(12),
  WEIGHT integer(5),
  primary key (PNUM)
);

CREATE TABLE SUPPLY (
  SNUM varchar(9),
  PNUM varchar(9),
  QUANTITY integer(6),
  primary key (SNUM, PNUM),
  foreign key (SNUM) references SUPPLIER(SNUM),
  foreign key (PNUM) references PART(PNUM)
);

Examples:

CREATE VIEW Houston_Supplier AS
SELECT *
FROM SUPPLIER
WHERE SCity = 'Houston';

mysql> SELECT *
-> from Houston_Supplier;
+------+-------+---------+--------+
| SNUM | SNAME | SCITY | STATUS |
+------+-------+---------+--------+
| S2     | DEF    | Houston  | 20      |
| S3     | Go go | Houston  | 12       |
+------+-------+---------+--------+
2 rows in set (0.01 sec)

DML

• Make sure you are familiar with the core DML statements.
• Update commands:
  • INSERT
  • UPDATE
  • DELETE
• Retrieval commands:
• SELECT
• Various JOIN: JOIN, LEFT JOIN, RIGHT JOIN, ...

The Select Statement

• Basic data retrieval statement in SQL
• Not to be confused with the select statement in Relational Algebra.
• Basid format:

SELECT <columns>
FROM <relations>
WHERE <condition>;

• Some special operations:
  • BETWEEN lower_range AND upper_range
  • EXISTS: not null
  • IN: membership test for a set/table
  • LIKE:inexact string matching.
    • wild cards:
      • % match any and all following characters.
      • _: match any one character.

Example:

-- All suppliers with Status between 5 to 15.
SELECT *
FROM Supplier
WHERE Status BETWEEN 5 AND 15;

-- All suppliers with status of 1, 5, 10 or 20.
-- Order by status.
SELECT *
FROM Supplier
WHERE Status IN (1, 5, 10, 20)
ORDER BY Status;

-- All suppliers with the character 'o' in the City.
SELECT *
FROM Supplier
WHERE SCity LIKE '%o%';

Group by and having:

• Allow aggregate functions (group functions, column functions) to be performed by the groups defined.
• Output one tuple per group.
• Example aggregate functions: MAX, MIN, AVG, COUNT, SUM.
• HAVING clause: allows aggregrate functions in the condition. WHER clause does not.
• Using GROUP BY, the columns of the SELECT clause can only have:
  • Columns named in GROUP BY.
  • Aggregate functions on other columns in the tables.
  • Constant expressions.

Example:

-- Average status of suppliers grouped by city.
select SCITY, 'Number of suppliers =', COUNT(*) ,
'Average Status = ', AVG(STATUS)
from Supplier
group by SCity;

-- Average weight of parts, grouped by color, counting only parts with weight more than 3
-- and showing only results of color with average weidht more than 5.
select Color, COUNT(*) as 'count',
AVG(Weight) as 'Average Weight'
from Part
and weight > 3
group by Color
having Avg(Weight) > 5;