SQL Introduction

What is SQL?

SQL stands for Structured Query Language and is a declarative programming language used to access and manipulate data in RDBMS (Relational Database Management Systems). SQL was developed by IBM in 70’s for their mainframe platform. Several years later SQL became standardized by both American National Standards Institute (ANSI-SQL) and International Organization for Standardization (ISO-SQL). According to ANSI SQL is pronounced “es queue el”, but many software and database developers with background in MS SQL Server pronounce it “sequel”.

What is RDBMS?

A Relational Database Management System is a piece of software used to store and manage data in database objects called tables. A relational database table is a tabular data structure arranged in columns and rows. The table columns also known as table fields have unique names and different attributes defining the column type, default value, indexes and several other column characteristics. The rows of the relational database table are the actual data entries.

Most popular SQL RDBMS

The most popular RDBMS are MS SQL Server from Microsoft, Oracle from Oracle Corp., DB2 from IBM, MySQL from MySQL, and MS Access from Microsoft. Most commercial database vendors have developed their proprietary SQL extension based on ANSI-SQL standard. For example the SQL version utilized by MS SQL Server is called Transact-SQL or simply T-SQL, The Oracle’s version is called PL/SQL (short for Procedural Language/SQL), and MS Access use Jet-SQL.

What can you do with SQL?

o SQL queries are used to retrieve data from database tables. The SQL queries use the SELECT SQL keyword which is part of the Data Query Language (DQL). If we have a table called “Orders” and you want to select all entries where the order value is greater than $100 ordered by the order value, you can do it with the following SQL SELECT query:

SELECT OrderID, ProductID, CustomerID, OrderDate, OrderValue

FROM Orders

WHERE OrderValue > 200

ORDER BY OrderValue;

The FROM SQL clause specifies from which table(s) we are retrieving data. The WHERE SQL clause specifies search criteria (in our case to retrieve only records with OrderValue greater than $200). The ORDER BY clause specifies that the returned data has to be order by the OrderValue column. The WHERE and ORDER BY clauses are optional.

o You can manipulate data stored in relational database tables, by using the INSERT, UPDATE and DELETE SQL keywords. These three SQL commands are part of the Data Manipulation Language (DML).

— To insert data into a table called “Orders” you can use a SQL statement similar to the one below:

INSERT INTO Orders (ProductID, CustomerID, OrderDate, OrderValue)

VALUES (10, 108, ’12/12/2007′, 99.95);

— To modify data in a table you can use a statement like this:

UPDATE Orders

SET OrderValue = 199.99

WHERE CustomerID = 10 AND OrderDate = ’12/12/2007′;

— To delete data from database table use a statement like the one below:

DELETE Orders

WHERE CustomerID = 10;

o You can create, modify or delete database objects (example of database objects are database tables, views, stored procedures, etc.), by using the CREATE, ALTER and DROP SQL keywords. These three SQL keywords are part of the Data Definition Language (DDL). For example to create table “Orders” you can use the following SQL statement:

CREATE Orders

(

OrderID INT IDENTITY(1, 1) PRIMARY KEY,

ProductID INT,

CustomerID ID,

OrderDate DATE,

OrderValue Currency

)

o You can control database objects privileges by using the GRANT and REVOKE keywords, part of the Data Control Language (DCL). For example to allow the user with username “User1” to select data from table “Orders” you can use the following SQL statement:

GRANT SELECT ON Orders TO User1

Why SQL?

Today every software professional needs at least a basic understanding of how SQL works. If you are new to SQL, you might feel overwhelmed and confused in the beginning, but as you progress you will discover how powerful and elegant SQL is.



Source by Peter Todorov

Leave a Reply

Your email address will not be published. Required fields are marked *