VirtualMV/SQL/Introduction

Home   <

Table of contents

Introduction

Overview

A database is the main tool used by most computer applications to manage, store and retrieve data and information.

For example:

• A bank will need to manage customers, accounts, staff, etc.
• An educational institute: students, staff, courses, timesheets, results, fees and allowances, etc.
• A library: Borrowers, books, loans, staff, timesheets

On the internet many sites use a database to store the content of the pages, or to capture user input.

For example:

• Computer companies (e.g. Dick Smith Electronics,( http://www.dse.co.nz ), or Paradigm Computers (http://www.pp.co.nz) ) use databases to manage their online customers and recieve orders
• Air New Zealand ( http://www.airnz.co.nz )will store its flight information, and allow customers to book flights.
• Moodle ( http://www.moodle.com ): A Learning Management system used by many large educational institutes (e.g. EIT - eitonline).
• Amazon Books ( http://www.amazon.com ) uses a database to manage their books etc and customer orders.
• Wikipedia ( http://www.wikipedia.com ) stores millions of pages that users can create that are accessible by anyone.
• Google ( http://www.google.com )search engine is really just a very large database with links to live pages.

By the end of this page you will be able to: Understand the importance of databases to businesses List the features of SQL Define the basic terminology associated with databases Entity/Table, Record/Row, Attribute/Field/Column

Importance of databases

QuestionWhat is one job that does NOT interact with a database?
Indeed there are very few jobs that do not interact with a database in some way - try to think of just one - an remember if the job receives money that is taxable eventually the Inland Revenue database will be involved!

How do we interact with a database ?

So how do we create a database, manage and retrieve the data. One common way is via a common standard called Structured Query Language (SQL).

There are many implementations of SQL. Three that are important are MySQL, Microsoft-SQL (MSSQL) and Oracle - there are lots of others. Indeed behind Microsoft Access is SQL.

• MySQL - is an Open Source Database used by many small and large systems (e.g. Wikipedia, Moodle), that can be both locally hosted or on the Web. MySQL commands follow standards but have its own administrative commands.
• Other implementations may "tweek" the commands so always check you are getting the results you want.

What is SQL?

SQL:

• stands for Structured Query Language
• allows you to access a database
• is an ANSI standard computer language
• is a relational database manager
• can execute queries against a database
• can retrieve data from a database
• can insert, delete and update records in a database
• is easy to learn
• is implemented in all major Relational Data Base Management Systems (RDBMS)
• Does not handle all the practical details involved in using a RDB. Hence, every RDBMS has additional features (some of which may form part of an extension of SQL for that particular RDBMS).

For more information see the Wikipedia page SQL (2010) has more information about SQL.

SQL is a standard?

• SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems.
• SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
• Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
• Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Brief history

• A prototype DB query and retrieval language was developed by IBM in the early 1970s as "Sequel"
• Other RDB developers and users recognized the advantages of having a standard language for manipulating RDBs
• This led to ANSI adoption of SQL as an enhanced RDB language based on Sequel
• Standardized Versions of SQL
  • Work toward an ANSI standard version started in 1983
  • Further drafts in 1986 and 1988 led to SQL-1 in 1989
  • SQL-2, adopted as an ANSI and ISO standard in 1992, is the version generally used at present
  • The latest ANSI-ISO standard, 1998-1999, SQL-3, has not yet been widely implemented.
    • SQL-3 contains new features that help in the construction of "object-relational" databases, i.e., RDBs that can handle objects in the technical sense: viz., sets of data together with program code that operates on the data.

For more information see the Wikipedia page SQL (2010)

Basic database terminology

Before we can look at some basic SQL we need to have an understanding of some terms associated with a database.

• Database
  • Contains one or more tables
• Entity (Table)
  • Contain rows of data, organised into fields
• Attribute (Field/Column)
  • Where the actual data is stored.

Example 1: Single table (flatfile) database

A flat file database is a database containing a single table. The following table shows students (tblStudent) with their heights:

tblStudent	Attributes (Column/Field)
	StuID	StuFullName	StuHeight
Rows (Records)	1	C. Lyon	1232
	3	L.E.Fant	1500
	2	G. Raff	5232

Example 2: Multiple table (relational) database

A relational database is made up of one or more entities represented as 2-Dimension (2-D) tables. The following tables show staff (tblStaff) and timesheets (tblTsheet). Note that the timesheet table contains a reference (StfID) to the staff table not the staff name.

Staff and Timesheet data

SQL

SQL is made up of five components (or languages):

1. Data Definition Language (DDL): Creates databases and tables e.g.Create table, create index
2. Data Manipulation Language (DML): Adds, deletes and changes data in tables e.g.Insert, Delete, Update
3. Data Query Language (DQL): Allows you to list the data in different ways e.g. Select
4. Data Control Language (DCL): Manages permissions e.g. Grant, revoke
5. Transaction Processing Language (DPL): Manages transactions e.g.Begin work, Rollback, Commit

When learning SQL you will initially concentrate on the first three. Some examples are:

SQL Data Definition Language

DDL creates databases and tables e.g.Create table, create index

CREATE DATABASE dbHeight;
USE dbHeight;
 
CREATE TABLE tblPeople
(
  PerID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PerFullName VARCHAR(40),
  PerHeightmm INT,  
    PRIMARY KEY (PerID)
);

Data Manipulation Language (DML)

DML adds, deletes and changes data in tables e.g.Insert, Delete, Update

INSERT INTO tblPeople (PerFullName, PerHeightmm )VALUES
  ('C. Lyon',1232),
  ('G. Raff',5232);
 
INSERT INTO tblPeople VALUES
  (NULL,'L. E. Fant',1500);
 
DELETE FROM tblPeople WHERE PerFullName = 'G. Raff';

Data Query Language (DQL)

DQL allows you to list the data in different ways e.g. Select

SELECT * FROM tblPeople;
SELECT * FROM tblPeople ORDER BY PerHeightmm;  
SELECT * FROM tblPeople WHERE PerHeightmm > 1400;

References

virtualMV  |  Superquick wiki guide  |  Please give me some feedback

VirtualMV/SQL/Introduction. (2024). In WikiEducator/VirtualMV wiki. Retrieved April 20, 2024, from http:https://wikieducator.org/VirtualMV/SQL/Introduction    (zotero)