SQL: A Comprehensive Guide to Database Querying and Management

SQL

The questing beast

Sir Thomas Mallory

SQL

•

A standard

•

ANSI

•

ISO

•

SQL skills are in demand

•

Developed by IBM

•

Most data management systems have adopted an SQL style language

to query data.

SQL

•

A complete database language

•

Data definition

•

Definition of tables and views

•

Data manipulation

•

Specifying queries

•

SELECT

•

Maintaining a database

•

INSERT

•

UPDATE

•

DELETE

SQL

•

Not a complete programming language

•

Use in conjunction with a complete programming language

•

e.g., Java, C#, Python, R

•

Embedded SQL

Data definition

•

Table, views, and indexes can be defined while the system is

operational

•

Base table

•

An autonomous, named table

•

CREATE TABLE

Constraints

•

Primary key

   CONSTRAINT pk_stock PRIMARY KEY(stkcode);

•

Foreign key

   CONSTRAINT fk_stock_nation

   FOREIGN KEY(natcode) REFERENCES nation(natcode);

•

Unique

   CONSTRAINT unq_stock_stkname UNIQUE(stkname);

Check constraint – table & column

•

Table

CREATE TABLE item (

  costPrice DECIMAL(9,2),

  sellPrice DECIMAL(9,2),

  CONSTRAINT profit_check CHECK (sellPrice > costPrice));

•

Column

CREATE TABLE item (

  category CHAR(1) CONSTRAINT category_constraint

    CHECK (category IN ('B', 'L', 'S')));

Data types

Data types

•

BOOLEAN

•

INTEGER

•

31 binary digits

•

SMALLINT

•

15 binary digits

•

FLOAT

•

Scientific work

•

DECIMAL

•

Commercial applications

•

CHAR

 and 

VARCHAR

•

Character strings

•

DATE

, 

TIME

, 

TIMESTAMP

, and 

INTERVAL

•

BLOB

 and 

CLOB

Check the

manual for

full details

Formatting

•

Number

•

FORMAT(x,d) formats the number x with d decimal places with commas

SELECT FORMAT(amount,2) FROM Payments;

•

Date

•

DATE_FORMAT (date, format) provides a flexible way of reporting dates

SELECT DATE_FORMAT(orderDate, '%W, %M %Y') from Orders;

SELECT DATE_FORMAT(orderDate, '%Y-%m-%d') from Orders;

Check the

manual for

full details

Exercise

•

Using the ClassicModels database, report the total value of payments

for each customer to the nearest dollar and list in descending value

When you use format

you create a string, but

you often want to sort

on the numeric value of

the formatted field.

How do you handle

this?

Collation sequence

•

Defines how to sort individual characters in a particular language

•

English

•

A B C … X Y Z

•

Norwegian

•

A B C … X Y Z Æ Ø Å

Collation sequence

•

Can specify a collation sequence at the database, table, and, column

level

•

Good practice to specify at the database level

CREATE DATABASE ClassicModels

COLLATE latin1_general_cs;

c

s indicates

case

sensitivity

Changing a table

•

ALTER TABLE

•

Adding one new column at a time

•

Cannot be used to

•

Change a column’s storage format

•

Delete an unwanted column

•

DROP TABLE

•

Deletes a table

A view

•

CREATE VIEW

•

DROP VIEW

An index

•

CREATE INDEX

•

DROP INDEX

Data manipulation statements

•

INSERT

•

UPDATE

•

DELETE

•

SELECT

INSERT

•

One row

•

Multiple rows

•

With a subquery

INSERT INTO STOCK

(stkcode, stkfirm, stkprice, stkdiv, stkpe)

SELECT code, firm, price, div, pe

FROM download WHERE code IN

('FC','PT','AR','SLG','ILZ','BE','BS','NG',

'CS','ROF');

The SQL way to

copy a table

UPDATE

•

One row

•

Multiple rows

•

All rows

UPDATE: Copy a column

UPDATE table1

  SET column1 =

   (SELECT column2 FROM table2

     WHERE table2.id = table1.id );

DELETE

•

One row

•

Multiple rows

•

All rows

•

Not the same as 

DROP TABLE

Product

•

All rows of the first table concatenated with all possible rows of the

second table

•

Form the product of stock and nation

SELECT * FROM stock, nation;

Product

Find the percentage of Australian stocks in the portfolio.

CREATE VIEW austotal (auscount) AS

  SELECT COUNT(*) FROM nation JOIN stock

   ON nation.natcode = stock.natcode

 WHERE natname = 'Australia';

CREATE VIEW total (totalcount) AS

  SELECT COUNT(*) FROM stock;

SELECT auscount/totalcount*100

  AS percentage FROM austotal, total;

Some implementations might give a result of

zero due to use of integer arithmetic.

Investigate use of the FLOAT function.

PRODUCT (alternative)

Find the percentage of Australian stocks in the portfolio.

SELECT FORMAT((SELECT COUNT(*)

  FROM nation JOIN stock

  ON nation.natcode = stock.natcode

  WHERE natname = 'Australia')*100/(SELECT COUNT(*)

    FROM stock),2) AS Percentage;

Join

•

Join creates a new table from two existing tables by matching on a

column common to both tables

•

Equijoin

•

The new table contains two identical columns

SELECT * FROM stock JOIN nation

 ON stock.natcode = nation.natcode;

Join variations

Inner join

SELECT * FROM stock JOIN nation ON stock.natcode =

nation.natcode;

SELECT * FROM stock INNER JOIN nation USING

(natcode);

SELECT * FROM stock JOIN nation USING (natcode);

SELECT * FROM stock NATURAL JOIN nation;

Primary key and foreign key have the same name

Left outer join

•

An inner join plus those rows from t1 not included in the inner join

•

SELECT id, col1, col2 FROM t1 LEFT JOIN

t2 USING (id);

Right outer join

•

An inner join plus those rows from t2 not included in the inner join

SELECT id, col1, col2 FROM t1 RIGHT JOIN

t2 USING (id);

Full outer join

•

An inner join plus those rows from t1 and t2 not included in the

inner join

SELECT id, col1, col2 FROM t1 FULL JOIN

t2 USING (id);

MySQL does not support FULL JOIN.

MySQL: Full outer join

SELECT id, col1, col2 FROM t1 LEFT JOIN t2 USING (id)

UNION

SELECT id, col1, col2 FROM t1 RIGHT JOIN t2 USING (id);

Outer join

•

Left join example

•

List names of all items with details of delivery quantities if any deliveries

have been made

 SELECT itemname, delqty FROM qitem

LEFT JOIN qdel USING (itemname);

•

Right join example

•

List item and quantity sold by department for each sale, including those

departments that have not made sales.

SELECT deptname, itemname, saleqty FROM qsale

RIGHT JOIN qdept USING (deptname);

Theta join

•

Join is a product with a condition clause

•

The condition is not restricted to equality.

•

A theta join is the general version

•

Theta is a variable that can take any value from the set [=, <>, >, ≥, <,

≤]

Theta join

Find the names of employees who earn more than

their boss

WITH wrk AS (SELECT * FROM emp),

     boss AS (SELECT * FROM emp)

SELECT wrk.empfname as Worker, boss.empfname as Boss

  FROM wrk JOIN boss

    ON wrk.bossno = boss.empno

    WHERE wrk.empsalary > boss.empsalary;

Theta join

In an alphabetical list of employees, how many appear before Clare?

WITH A AS (SELECT * FROM emp),

     B AS (SELECT * FROM emp)

SELECT count(*)

  FROM A JOIN  B

    ON A.empfname > B.empfname

    WHERE A.empfname = "Clare"

How many after

Clare?

This query does not match a foreign key and

primary key, but it demonstrates the

principle of a theta join

Correlated subquery

•

The inner query is evaluated many times rather than once

Find those stocks where the quantity is greater than the average for that

country.

SELECT natname, stkfirm, stkqty FROM stock JOIN

nation

   ON stock.natcode = nation.natcode

   WHERE stkqty >

   (SELECT AVG(stkqty) FROM stock

      WHERE stock.natcode = nation.natcode);

Correlated subquery

SELECT natname, stkfirm, stkqty FROM stock JOIN nation

  ON stock.natcode = nation.natcode

  WHERE stkqty >

   (SELECT AVG(stkqty) FROM stock

      WHERE stock.natcode = nation.natcode);

Correlated subquery

•

Clue

•

The need to compare each row of a table against a function (e.g., average or

count) for some rows of a column

•

Must be used with EXISTS and NOT EXISTS

Exercise

•

Using the ClassicModels database, write a correlated subquery to

determine which employees work in the Paris office

CASE statement

•

The CASE statement is used to implement a series of conditional clauses

WITH temp AS

(SELECT customerName, COUNT(*) AS orderCount

FROM Orders JOIN Customers

    ON Customers.customerNumber = Orders.customerNumber

    GROUP BY customerName)

Select customerName, orderCount,

    CASE orderCount

  WHEN 1 THEN 'One-time customer'

        WHEN 2 THEN 'Repeated customer'

        WHEN 3 THEN 'Frequent customer'

        ELSE 'Loyal customer'

end customerType

    FROM temp

ORDER BY customerName;

How would you sort by customer value

(e.g., Loyal customer first)

Aggregate functions

•

COUNT

•

SUM

•

AVG

•

MAX

•

MIN

SQL Routines

•

Function

•

Procedure

•

Trigger

•

Improve flexibility, productivity, and enforcement of business rules

SQL function

•

Similar purpose to built-in functions

CREATE FUNCTION km_to_miles(km REAL)

  RETURNS REAL

  DETERMINISTIC

  RETURN 0.6213712*km;

•

Use in SQL

SELECT FORMAT(km_to_miles(100),0);

SELECT km_to_miles(distance)from flight;

MySQL defines a deterministic

MySQL defines a deterministic

function as one that 

function as one that 

always

always

produces the same result for the

produces the same result for the

same input parameters

same input parameters

Exercise

•

Write an SQL function to convert Fahrenheit to Celsius.

SQL procedure

•

A stored procedure is SQL code that is dynamically loaded and

executed by a CALL statement

•

Accounting example

SQL procedure

CREATE TABLE account (

acctno INTEGER,

acctbalance DECIMAL(9,2),

primary key (acctno));

CREATE TABLE transaction (

transid INTEGER,

transamt DECIMAL(9,2),

transdate DATE,

PRIMARY KEY(transid));

CREATE TABLE entry (

transid INTEGER,

acctno INTEGER,

entrytype CHAR(2),

PRIMARY KEY(acctno, transid),

CONSTRAINT fk_account FOREIGN KEY(acctno) REFERENCES account(acctno),

CONSTRAINT fk_transaction FOREIGN KEY(transid) REFERENCES

transaction(transid));

First create a schema:

AccSystem

SQL procedure

DELIMITER //

-- Define the input values

CREATE PROCEDURE transfer (

IN `Credit account` INTEGER,

IN `Debit account`  INTEGER,

IN  Amount          DECIMAL(9,2),

IN `Transaction ID` INTEGER)

LANGUAGE SQL

DETERMINISTIC

BEGIN

-- Save the transaction details

INSERT INTO transaction VALUES (`Transaction ID`, Amount,

CURRENT_DATE);

UPDATE account

-- Increase the credit account

SET acctbalance = acctbalance + Amount

WHERE acctno = `Credit account`;

INSERT INTO entry VALUES (`Transaction ID`, `Credit account`, 'cr');

UPDATE account

-- Decrease the debit account

SET acctbalance = acctbalance - Amount

WHERE acctno = `Debit account`;

INSERT INTO entry VALUES (`Transaction ID`, `Debit account`, 'db');

END //

Need to delimit

the procedure

and SQL

commands

SQL procedure

•

Click on the stored procedure’s rightmost icon for pop-up entry

window

SQL procedure

•

Example

•

Transaction 1 transfers $100 to account 101 (the credit account) from

account 102 (the debit account)

CALL transfer(101,102,100,1);

Trigger

•

A set of actions set off by an SQL statement that changes the state of

the database

•

UPDATE

•

INSERT

•

DELETE

Trigger

•

Automatically log all updates to a log file

•

Create a table for storing log rows

•

Create a trigger

CREATE TABLE stock_log (

   stkcode         CHAR(3),

   old_stkprice    DECIMAL(6,2),

   new_stkprice    DECIMAL(6,2),

   old_stkqty      DECIMAL(8),

   new_stkqty      DECIMAL(8),

   update_stktime

  TIMESTAMP NOT NULL,

PRIMARY KEY(update_stktime));

Trigger

DELIMITER //

CREATE TRIGGER stock_update

AFTER UPDATE ON stock

FOR EACH ROW BEGIN

INSERT INTO stock_log VALUES

(OLD.stkcode, OLD.stkprice, NEW.stkprice, 

OLD.stkqty, NEW.stkqty, CURRENT_TIMESTAMP);

END //

Universal Unique Identifier (UUID)

•

A Universally Unique Identifier (UUID) is a generated number that is

globally unique even if generated by two independent programs on

different computers

•

The probability that a UUID is not unique is close enough to zero to be

negligible

•

Generated from a timestamp (temporal difference) and computer

node id (spatial difference)

•

 A UUID value is a 128-bit number

•

Can be used to create a primary key in a distributed environment

SELECT UUID() AS UUID_Value

Nulls

•

Don’t confuse null with blank or zero

•

Multiple meanings

•

Unknown data

•

Inapplicable data

•

No value supplied

•

Value undefined

•

Creates confusion because the user must make an inference

•

One expert advises that NOT NULL be used for all columns to avoid

confusion

Security

•

Data are a valuable resource

•

Access should be controlled

•

SQL security procedures

•

CREATE VIEW

•

Authorization commands

Authorization

•

Based on privilege concept

•

You cannot execute an operation without the appropriate privilege

•

DBA has all privileges

GRANT

•

Defines a user’s privileges

•

Format

GRANT privileges ON object TO users

   [WITH GRANT OPTION];

•

An object is a base table or view

•

The keyword 

privilege

 can be 

ALL PRIVILEGES

 or chosen from

•

SELECT

•

UPDATE

•

DELETE

•

INSERT

•

Privileges can be granted to everybody using the keyword 

PUBLIC

 or to

selected users by specifying their user identifier

GRANT

•

The UPDATE privilege can specify particular columns in a base table

or view

•

Some privileges apply only to base tables

ALTER

INDEX

•

WITH GRANT OPTION

•

Permits a user to pass privileges to another user

Using GRANT

•

Give Alice all rights to the STOCK table.

GRANT ALL PRIVILEGES ON stock TO alice;

•

Permit the accounting staff, Todd and Nancy, to update the price of a

stock.

GRANT UPDATE (stkprice) ON stock TO todd, nancy;

•

Give all staff the privilege to select rows from ITEM.

GRANT SELECT ON item TO PUBLIC;

•

Give Alice all rights to view STK.

GRANT SELECT, UPDATE, DELETE, INSERT ON stk

   TO alice;

REVOKE

•

Removes privileges

•

Format

REVOKE privileges ON object FROM users;

•

Cascading REVOKE

•

Reverses use of the 

WITH GRANT OPTION

•

When a user’s privileges are revoked, all users whose privileges were

established using 

WITH GRANT OPTION 

are also revoked

Using REVOKE

•

Remove Sophie's ability to select from ITEM.

REVOKE SELECT ON item FROM sophie;

•

Nancy is no longer permitted to update stock prices.

REVOKE UPDATE ON stock FROM nancy;

Injection attack

•

An injection attack takes advantage of parameterized queries to make

unauthorized queries

•

The attacker creates or alters existing SQL commands

•

The application takes the attacker’s input and combines it to build an

unintended SQL query

Avoidance

•

Limit the authorization of the connection

•

SELECT only

•

Check the input is of the expected data type

•

Use parameterized queries

The catalog

•

A relational database containing definitions of base tables, view, etc.

•

Can be interrogated using SQL

•

Called systems tables rather than base tables

•

MySQL

Information_schema

Interrogating the catalog

•

Find the table(s) with the most rows.

SELECT TABLE_NAME, TABLE_ROWS

FROM 

Information_schema.

TABLES

WHERE TABLE_ROWS = (SELECT MAX(TABLE_ROWS)

  FROM 

Information_schema.

TABLES);

•

What columns in what tables store dates?

SELECT TABLE_NAME, COLUMN_NAME

FROM 

Information_schema.

COLUMNS

WHERE DATA_TYPE = 'date'

     ORDER BY TABLE_NAME, COLUMN_NAME;

MySQL catalog

queries

Natural language processing

Open Database Connectivity (ODBC)

Embedded SQL

•

SQL is not a stand-alone programming language

•

SQL statements can be embedded in application programs

•

The incompatibility between the table processing of SQL and record-

at-time processing in procedural languages is addressed using a

cursor

LibreOffice/MS Access

•

Strengths

•

Interface

•

SQL DML

•

Referential integrity

•

Fast execution

•

Views (queries)

•

Updateable views

•

Weaknesses

•

No support for 

GRANT

 and 

REVOKE

•

Domains

•

No support for 

COMMIT

 and

ROLLBACK

•

Limited concurrency control

User-defined data types

•

May be used in the same way as built-in data types

•

A UDT is defined by

•

Specifying a set of declarations of the stored attributes that represent the

value of the UDT

•

The operations that define the equality and ordering relationships of the UDT

•

The operations and derived attributes that represent the behavior of the UDT

The future of SQL

•

One of the more successful standardization stories

•

Highly portable

•

Across operating systems

•

Across applications and organizations

•

Mainstay of transaction processing systems for now and the

immediate future

Key points

•

SQL routines

•

Function

•

Procedure

•

Triggers

•

Security

•

GRANT

•

REVOKE

•

Connectivity

•

Embedded SQL