Structured Query Language (SQL) and Database Management Notes

Introduction to Structured Query Language (SQL)

  • SQL stands for Structured Query Language.

  • It is a standard computer language used specifically for accessing and manipulating database systems.

  • SQL is an ANSI (American National Standards Institute) standard computer language.

  • While SQL is a standard, many different versions of the language exist. However, to remain in compliance with the ANSI standard, they must support major keywords in a similar manner, including: SELECT, UPDATE, DELETE, INSERT, WHERE, and others.

  • It is important to note that most SQL database programs also include their own proprietary extensions in addition to the standard SQL commands.

  • SQL works with various database programs including: MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and MySQL.

Core Capabilities of SQL

  • Executing queries against a database.

  • Retrieving data from a database.

  • Inserting new records into a database.

  • Updating existing records within a database.

  • Deleting records from a database.

SQL Database Tables

  • A database typically contains one or more tables.

  • Each table is identified by a specific name (e.g., "Customers" or "Orders").

  • Tables contain records, which are the rows of the table.

  • Tables contain data organized into columns.

Example Table: "Persons"

LastName

FirstName

Address

City

Hansen

Ola

Timoteivn 10

Sandnes

Svendson

Tove

Borgvn 23

Sandnes

Pettersen

Kari

Storgt 20

Stavanger

  • This table contains three records (one for each person) and four columns (LastName, FirstName, Address, and City).

SQL Sub-Languages: DML and DDL

SQL is divided into two distinct parts: the Data Manipulation Language (DML) and the Data Definition Language (DDL).

Data Manipulation Language (DML)

  • DML consists of query and update commands used to handle data within database objects.

  • SELECT: Extracts data from a database table.

  • UPDATE: Updates data in a database table.

  • DELETE: Deletes data from a database table.

  • INSERT INTO: Inserts new data into a database table.

Data Definition Language (DDL)

  • DDL permits the creation or deletion of database tables and other objects.

  • It allows for the definition of indexes (keys), the specification of links between tables, and the imposition of constraints between tables.

  • CREATE DATABASE: Creates a new database.

  • CREATE TABLE: Creates a new database table.

  • ALTER TABLE: Modifies an existing database table.

  • DROP TABLE: Deletes a database table.

  • CREATE INDEX: Creates an index (search key).

  • DROP INDEX: Deletes an index.

Creating and Managing Databases and Tables

Create a Database

  • Syntax: CREATE DATABASE database_name.

Create a Table

  • Syntax: sql CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), .... )     

  • Example 1: Creating a table "Person" without specifying lengths: sql CREATE TABLE Person ( LastName varchar, FirstName varchar, Address varchar, Age int )     

  • Example 2: Creating a table "Person" with specified maximum lengths: sql CREATE TABLE Person ( LastName varchar(30), FirstName varchar, Address varchar, Age int(3) )     

Common Data Types

Data Type

Description

integer(size) / int(size) / smallint(size) / tinyint(size)

Holds integers only. The maximum number of digits is specified in parentheses.

decimal(size,d) / numeric(size,d)

Holds numbers with fractions. "size" is the total number of digits; "d" is the maximum number of digits to the right of the decimal.

char(size)

Holds a fixed-length string (letters, numbers, special characters). Fixed size is in parentheses.

varchar(size)

Holds a variable-length string. Maximum size is specified in parentheses.

date(yyyymmdd)

Holds a date.

Deleting Tables and Databases

  • DROP TABLE: Deletes the table, its structure, attributes, and indexes completely.

    • Syntax: DROP TABLE table_name

  • DROP DATABASE: Deletes the entire database.

    • Syntax: DROP DATABASE database_name

  • TRUNCATE TABLE: Deletes only the data inside the table, keeping the table structure intact.

    • Syntax: TRUNCATE TABLE table_name

Altering Tables

  • The ALTER TABLE statement is used to add or drop columns in an existing table.

  • Add a Column: sql ALTER TABLE Person ADD City varchar(30)     

  • Drop a Column (Note: some systems do not allow this): sql ALTER TABLE Person DROP COLUMN Address     

The SELECT Statement and Result Sets

  • The SELECT statement is used to extract data from a table.

  • The resulting data is stored in a tabular format called a "result-set."

  • Syntax: SELECT column_name(s) FROM table_name

  • Selecting Specific Columns: sql SELECT LastName, FirstName FROM Persons     

  • Selecting All Columns: Using the asterisk (*) symbol. sql SELECT * FROM Persons     

Semicolons in SQL

  • The semicolon is the standard way to separate SQL statements in systems that allow multiple statements per call.

  • In some environments, like SQL Server 2005, the semicolon is optional after each statement.

Filtering Data: SELECT DISTINCT and WHERE

SELECT DISTINCT

  • The DISTINCT keyword is used to return only unique values when a column contains duplicate data.

  • Syntax: SELECT DISTINCT column_name(s) FROM table_name

  • Example: If a "Company" column has duplicate entries for "W3Schools", SELECT DISTINCT Company FROM Orders will only return the name once.

The WHERE Clause

  • Used to specify selection criteria to conditionally select data.

  • Syntax: SELECT column FROM table WHERE column operator value

  • Example: SELECT * FROM Persons WHERE City='Sandnes'

Conditional Operators

Operator

Description

=

Equal

<>

Not equal (In some versions, this can be written as !=)

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

LIKE

Search for a specific pattern

Usage of Quotes

  • SQL requires single quotes around text/string values.

  • Numeric values should not be enclosed in quotes.

  • Correct text: SELECT * FROM Persons WHERE FirstName='Tove'

  • Correct numeric: SELECT * FROM Persons WHERE Year > 1965

Advanced Filtering: LIKE and Logical Operators

The LIKE Condition and Wildcards

  • Used to search for a pattern in a column.

  • The percent sign (%) acts as a wildcard representing missing characters.

  • Starts with 'O': WHERE FirstName LIKE 'O%'

  • Ends with 'a': WHERE FirstName LIKE '%a'

  • Contains 'la': WHERE FirstName LIKE '%la%'

AND & OR Operators

  • Used to join multiple conditions in a WHERE clause.

  • AND: Displays a row if all conditions are true.

  • OR: Displays a row if any condition is true.

  • Example AND: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'

  • Example OR: SELECT * FROM Persons WHERE FirstName='Tove' OR LastName='Svendson'

  • Combining AND & OR: Use parentheses for complex logic. sql SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson' &nbsp;&nbsp;&nbsp;&nbsp;

Modifying Records: INSERT, UPDATE, and DELETE

The INSERT INTO Statement

  • Syntax (Values only): INSERT INTO table_name VALUES (value1, value2, ...)

  • Syntax (Specific Columns): INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

  • Example: INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

  • Specific Column Example: INSERT INTO Persons (LastName, Address) VALUES ('Rasmussen', 'Storgt 67') (This leaves other columns like FirstName and City as null or default values).

The UPDATE Statement

  • Used to modify existing data in a table.

  • Syntax: UPDATE table_name SET column_name = new_value WHERE column_name = some_value

  • Updating Single Column: UPDATE Person SET FirstName = 'Nina' WHERE LastName = 'Rasmussen'

  • Updating Multiple Columns: UPDATE Person SET Address = 'Stien 12', City = 'Stavanger' WHERE LastName = 'Rasmussen'

The DELETE Statement

  • Used to remove rows from a table.

  • Syntax: DELETE FROM table_name WHERE column_name = some_value

  • Deleting a Specific Row: DELETE FROM Person WHERE LastName = 'Rasmussen'

  • Deleting All Rows: Removes all data but preserves the table structure, attributes, and indexes.

    • Syntax: DELETE FROM table_name or DELETE * FROM table_name

Sorting and Aliases

The ORDER BY Keyword

  • Used to sort the result-set by one or more columns.

  • Default sorting order is alphabetical/numerical (ASC).

  • Syntax: SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]

  • Alphabetical Sort: SELECT Company, OrderNumber FROM Orders ORDER BY Company

  • Reverse Alphabetical: SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

  • Multi-Column Sort: SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber (Sorts by company first, then by order number within each company).

  • Mixed Sorting: SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

Aliases (AS)

  • Used to give a column or a table a temporary, more readable name.

  • Column Alias Syntax: SELECT column_name AS alias_name FROM table_name

    • Example: SELECT LastName AS Family, FirstName AS Name FROM Persons

  • Table Alias Syntax: SELECT column_name FROM table_name AS alias_name

    • Example: SELECT LastName, FirstName FROM Persons AS Employees

Joins and Keys

Primary Keys

  • A primary key is a column where each row has a unique value.

  • The purpose is to bind data together across tables without repeated redundancy.

  • Example: In an "Employees" table, Employee_ID distinguishes individuals with the same name.

Linking Tables

  • Tables are related using column values.

  • Example: The Orders table may have an Employee_ID column that refers back to the primary key in the Employees table.

Selecting From Multiple Tables

  • You can select data from two tables by specifying both in the FROM clause and matching their keys in the WHERE clause.

  • Example: SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID = Orders.Employee_ID

Types of Joins

  1. INNER JOIN: Returns all rows from both tables where there is a match in the join condition.

    • Syntax: SELECT fields FROM table1 INNER JOIN table2 ON table1.key = table2.key

  2. LEFT JOIN: Returns all rows from the first (left) table, even if there are no matches in the second (right) table.

    • Syntax: SELECT fields FROM table1 LEFT JOIN table2 ON table1.key = table2.key

  3. RIGHT JOIN: Returns all rows from the second (right) table, even if there are no matches in the first (left) table.

    • Syntax: SELECT fields FROM table1 RIGHT JOIN table2 ON table1.key = table2.key

SQL Functions and GROUP BY

Built-in Functions

  • Syntax: SELECT function(column) FROM table

  • Aggregate Functions: Operate against a collection of values and return a single value.

    • AVG(column): Returns the average value.

    • COUNT(*): Returns the number of selected rows.

    • FIRST(column): Returns the value of the first record (not supported in MS SQL Server 2000).

    • LAST(column): Returns the value of the last record (not supported in MS SQL Server 2000).

    • MAX(column): Returns the highest value.

    • MIN(column): Returns the lowest value.

    • SUM(column): Returns the total sum.

  • Scalar Functions: Operate against a single value and return a single value based on the input.

The GROUP BY Clause

  • The GROUP BY statement was added because aggregate functions (like SUM) return the total for the whole column; GROUP BY allows finding sums/averages for individual groups of column values.

  • Rule: If an aggregate function is used in a SELECT statement alongside other columns, those columns must be included in a GROUP BY clause.

  • Example: SELECT Company, SUM(Amount) FROM Sales GROUP BY Company

    • Correct result: Lists each company and its specific sum.

    • Invalid usage: SELECT Company, SUM(Amount) FROM Sales without the GROUP BY will produce the grand total for all rows next to each company name, which is often logically incorrect in some SQL versions.

SQL Views

Definition of a View

  • A VIEW is a virtual table based on the result-set of a SELECT statement.

  • It contains rows and columns like a real table, but the fields are derived from one or more real tables in the database.

  • A view does not store the data itself; the database engine recreates the data every time a user queries the view.

  • Modifying data through functions or joins in a view does not affect the underlying table structure.

CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Examples from Northwind Database

  1. Current Product List: Filters active products. sql CREATE VIEW [Current Product List] AS SELECT ProductID, ProductName FROM Products WHERE Discontinued = No &nbsp;&nbsp;&nbsp;&nbsp;

  2. Products Above Average Price: Uses a subquery to compare prices. sql CREATE VIEW [Products Above Average Price] AS SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products) &nbsp;&nbsp;&nbsp;&nbsp;

  3. Category Sales for 1997: Calculates totals by grouping; notably, it can select data from another view. sql CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY CategoryName &nbsp;&nbsp;&nbsp;&nbsp;

Querying a View

  • You can query a view exactly like a regular table: SELECT * FROM [Current Product List].

  • Conditions can be added: SELECT * FROM [Category Sales For 1997] WHERE CategoryName='Beverages'.