Principles of Database Systems (H) Lecture 2 Notes

Introduction to SQL

To manage data in a database, a full-blown language will introduce too much complexity to both users and database systems. What we need is a special DSL that specializes in data query and manipulation, hence SQL was invented. The SQL is composed of four parts:

• Data Manipulation Language, DML
• Data Definition Language, DDL
• Data Query Language, DQL
• Data Control Language, DCL

The most common used one is the DQL, or more specifically, the SELECT statement:

SELECT column_name FROM table_name WHERE conditions

The DDL, deals with tables and other database objects. There are three statements: CREATE, ALTER, and DROP:

-- Create a table
CREATE TABLE Users (
	id INT AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(50) NOT NULL,
	email VARCHAR(100) NOT NULL,
	birthdate DATE,
	is_active BOOLEAN DEFAULT TRUE
);

ALTER TABLE table_name
-- Add column
ADD COLUMN birth_date DATE;
-- Modify column's datatype
MODIFY COLUMN salary DECIMAL(10,2);
-- Change column's name
CHANGE COLUMN old_column_name new_column_name datatype;
-- Delete a column
DROP COLUMN birth_date;
-- Add a primary key
ADD PRIMARY KEY (column_name);

-- Add a foreign key
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

-- Rename the table
RENAME TO staff;

The DML includes INSERT, UPDATE, and DELETE:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

UPDATE table_name SET column=value WHERE conditions;

DELETE FROM table_name WHERE conditions

Caution

The SQL language lacks standardization, and multiple variants exist. In this course, we will stick to the one that PostgreSQL uses.

Caution

The principle of “all rows are distinct” can be enforced for tables in SQL, but not enforced for query results in SQL.

Caution

The SQL keyword and identifier are case-insensitive.

Text datatypes:

• char(length): Fixed-length strings.
• varchar(max_length): Variable-length strings that are limited by a maximum length.
• varchar2(max_length): Only appears in Oracle SQL.
• text: Stores big text (~10KB).
• clob: Stores much bigger texts (up to 4GB).
• nchar/nvarchar: Unicode equivalent of char/varchar.

Numerical datatypes:

• int: 4-byte integer type.
• float: IEEE-compliant 4-byte floating point type.
• numeric(precision, scale): precision is the number of digits, and the scale is the number of these digits after the decimal point.

Data datatypes:

• date: Date and times (down to seconds with Oracle, not with others).
• datatime: Also date and times, down to seconds (other than Oracle, DB2, and PostgreSQL)
• timestamp: down to second, replace datetime in DB2/PostgreSQL.

Binary datatypes

• raw(max_length): Fixed-length binaries.
• varbinary(max_length): Variable-length binaries that can have a maximum length of max_length bits.
• blob: Binary equivalent of clob.
• bytea: Same as blob, used in PostgreSQL.

By default, columns are permitted to have a null as it value. To prohibit this, use NOT NULL decorator when creating the column.

Some DBMS systems support adding comments to database objects, e.g. in PostgreSQL:

COMMENT ON object_type object_name is string|null

Constraints

For example, we want create a table for storing personal information:

CREATE TABLE people
(
	peopleid int,
	first_name varchar(30),
	surname varchar(30),
	born numeric(4),
	died numeric(4)
)

But this will allow that rows contain arbitrary null data, so we need to add not null constraints for mandatory data:

CREATE TABLE people
(
	peopleid int not null,
	first_name varchar(30),
	surname varchar(30) not null,
	born numeric(4),
	died numeric(4)
)

And we need to add a primary key, so we use the primary key constraint:

CREATE TABLE people
(
	peopleid int not null
		primary key,
	first_name varchar(30),
	surname varchar(30) not null,
	born numeric(4),
	died numeric(4)
)

Note that the primary key constraint implies not null. SQL will not warn about redundant constraints. Another potential problem is the capitalization. For example, the three strings:

• "FOO"
• "foo"
• "Foo"

is considered to be not equal in Oracle, PostgreSQL, and DB2, but they are equal in SQL Server, MySQL, and SQLite. So we need to standardize the names, using unique constraint for uniqueness and check for standardization:

CREATE TABLE people
(
	peopleid int not null
		primary key,
	first_name varchar(30),
		check(first_name = upper(first_name)),
	surname varchar(30) not null,
		check(surname = upper(surname)),
	born numeric(4),
	died numeric(4),
	unique(first_name, surname)
)