SQL Commands and Database Concepts: A Comprehensive Guide

Posted on Sep 10, 2024 in Computers

DBS201 Test 1 Review

SQL Commands

CREATE

Used to create database objects like:

  • Collection
  • Table
  • View

DROP

Used to delete database objects.

ALTER

Used to modify existing database objects.

INSERT

Used to add new data into a table.

  • Can be used as INSERT INTO.

SELECT

Used to retrieve data from a table.

Syntax:

SELECT attribute(s)
FROM table(s)
WHERE condition(s)
ORDER BY attribute(s)

At minimum, you must select the attribute and the table it’s from.

  • * can be used as a wildcard character to select all columns.
  • Comparison operators are the same as in C programming language.
  • Data is ordered by its insertion order by default.
  • To change the order, use the ORDER BY clause. For example:

SELECT *
FROM PART
ORDER BY ON_HAND

Creating a Database

Use the CREATE COLLECTION command:

CREATE COLLECTION collectionname

The same syntax is used to delete a database, but with DROP instead of CREATE.

Creating a Table

Syntax:

CREATE TABLE tablename (
  field1 datatype fieldsize,
  field2 datatype fieldsize, …
  CHECK ...,
  PRIMARY KEY (fieldname(s)),
  FOREIGN KEY (fieldname) REFERENCES tablename (PKfieldname)
)

The same syntax is used to drop tables, but with DROP instead of CREATE.

Inserting Data into Tables

Use the INSERT INTO command. For example:

INSERT INTO table_name (fieldname1, fieldname2) VALUES (value1, value2)

  • When inserting data, include all column names and their corresponding values.
  • If a field allows null values and you don’t want to enter data, use NULL.

Database Concepts

Primary Key

  • Must be unique and not null.
  • Provides fast access to a row in a table.
  • Guarantees the uniqueness of the primary key field.
  • Requires a primary key value for every row in the table.

Not Null

  • Indicates that a value must exist for the field.
  • NOT NULL: The user must specify a value.
  • NOT NULL WITH DEFAULT: The user must specify a value, or the system will assign a default value (spaces for character fields, 0 for numeric fields, and the current date for date fields).
  • NOT NULL WITH DEFAULT "value": The user must specify a value, or the system will insert the specified “value”.

Unique

  • Ensures that a value appears only once in a column.
  • The database prevents adding or modifying rows if it results in duplicate values in the unique field.
  • Useful for fields like Social Insurance Number, Driver’s License Number, Ontario Health Card Number, etc.

Check

  • Ensures that a value meets specific validation criteria.
  • The database validates the field value against a defined condition. Rows are rejected if the validation fails.
  • Common validations include age ranges, salary ranges, and grade values.

Example:

CREATE TABLE TEST (
  AGE DECIMAL (3,0) NOT NULL,
  SALARY DECIMAL (7,2) NOT NULL WITH DEFAULT,
  GRADE CHARACTER (1),
  CONSTRAINT TEST_AGE_CK CHECK (AGE >= 18 AND AGE <= 49),
  CONSTRAINT TEST_SALARY_CK CHECK (SALARY BETWEEN 20000 AND 40000),
  CONSTRAINT TEST_GRADE_CK CHECK (GRADE IN('A', 'B', 'C', 'D', 'F', 'I'))
)

Foreign Key

  • Enforces referential integrity between two tables.
  • Used to retrieve information from another table using its primary key.
  • Example: A PATIENT record has a DoctorNum as a foreign key, referencing the primary key (DoctorNum) of the DOCTOR table.

Updating Data in Tables

Use the UPDATE command. For example:

UPDATE MARINA
  SET Dock_Number = 'AAB'
  WHERE Marina_Num = 'M-2407'

Deleting Data from Tables

Use the DELETE statement. For example:

DELETE FROM tablename
  WHERE condition

Joining Tables

To retrieve data from multiple tables, use the JOIN clause. For example:

SELECT p.Product_ID, p.Product_Name, p.SID, s.Supplier_Name
  FROM DBS201SQL.PRODUCT p, DBS201SQL.SUPPLIER s
  WHERE p.SID = s.Supp_ID

Views

  • Virtual tables that do not store data physically.
  • Created based on a SELECT statement.

Syntax to create a view:

CREATE VIEW viewname
  AS (SELECT statement)

To use a view, execute a SELECT statement against it:

SELECT * FROM viewname

  • Views cannot be directly modified.
  • To change a view, drop and recreate it.

Syntax to drop a view:

DROP VIEW viewname

Calculated Fields

  • Can be added to a SELECT statement.
  • Represent simple mathematical or string expressions.

Example:

SELECT Assign_chg_hr, Assign_hours, Assign_chg_hr * Assign_hours AS total_charge
  FROM seneca.dbs201.assignment

Copying Data

Syntax:

INSERT INTO newtablename
  SELECT *
  FROM existingtablename

Database Objects

Schema

A group of related database objects, including tables, views, and indexes. It provides a logical grouping and namespace for database objects.

Table

A collection of data organized in rows and columns. Each row represents a record, and each column represents a specific attribute.

Row

A horizontal line in a table representing a single record.

Column

A vertical line in a table representing a specific attribute and its data type.

Library

Similar to a schema, it groups related objects. In some contexts, schemas and libraries are treated interchangeably.

Physical File

A file system object that stores database data. Tables are often implemented as physical files.

Record

A collection of related fields that represent a single entity in a table.

Field

A single piece of information within a record, representing a specific attribute. In SQL, fields are referred to as columns.