Introduction to SQL

Introduction to SQL

SQL is a standard language for accessing and manipulating databases.

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
• To use a server-side scripting language, like PHP or ASP
• To use SQL to get the data you want
• To use HTML / CSS to style the page

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Look at the "Customers" table:

Example:

SELECT * FROM Customers;

Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works.

Applications of SQL

As mentioned before, SQL is one of the most widely used query language over the databases. I'm going to list few of them here:

·     Allows users to access data in the relational database management systems.

·     Allows users to describe the data.

·     Allows users to define the data in a database and manipulate that data.

·     Allows to embed within other languages using SQL modules, libraries & pre-compilers.

·     Allows users to create and drop databases and tables.

·     Allows users to create view, stored procedure, functions in a database.

·     Allows users to set permissions on tables, procedures and views.

Advanced SQL queries

·     NOT NULL Constraint ? Ensures that a column cannot have NULL value.

·     DEFAULT Constraint ? Provides a default value for a column when none is specified.

·     UNIQUE Constraint ? Ensures that all values in a column are different.

·     PRIMARY Key ? Uniquely identifies each row/record in a database table.

·     FOREIGN Key ? Uniquely identifies a row/record in any of the given database table.

·     CHECK Constraint ? The CHECK constraint ensures that all the values in a column satisfies certain conditions.

·     INDEX ? Used to create and retrieve data from the database very quickly.

Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.

Table Joins, a must

All of the queries up until this point have been useful with the exception of one major limitation - that is, you've been selecting from only one table at a time with your SELECT statement. It is time to introduce you to one of the most beneficial features of SQL & relational database systems - the "Join". To put it simply, the "Join" makes relational database systems "relational".

Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement.

A "Join" can be recognized in a SQL SELECT statement if it has more than one table after the FROM keyword.

For example:

SELECT "list-of-columns"



FROM table1,table2



WHERE "search-condition(s)"

Joins can be explained easier by demonstrating what would happen if you worked with one table only, and didn't have the ability to use "joins". This single table database is also sometimes referred to as a "flat table". Let's say you have a one-table database that is used to keep track of all of your customers and what they purchase from your store:

Key Points

·     Use CREATE and DROP to create and delete tables.

·     Use INSERT to add data.

·     Use UPDATE to modify existing data.

·     Use DELETE to remove data.

·     It is simpler and safer to modify data when every record has a unique primary key.

·     Do not create dangling references by deleting records that other records refer to

CREATE TABLE Person(id text, personal text, family text);

CREATE TABLE Site(name text, lat real, long real);

CREATE TABLE Visited(id integer, site text, dated text);

CREATE TABLE Survey(taken integer, person text, quant text, reading real);

 ********************************##############***************************