Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
SQL

Introduction to ANSI SQL

 
Updated on Sep 29, 2020

We human beings communicate with each other with the help of language. Similiarly, SQL stands for Structured Query Language is the language that a database understands, and we will communicate to the database using SQL. SQL is a 4TH generation database gateway language standardized by ANSI (American National Standards Institute) for managing data held in a RDBMS (Relational Database Management Systems).

Why learning SQL is important

SQL is used to create, manage, manipulate and query the database objects such as tables, views etc. SQL is the building block for those working with data in database management systems. Once you master SQL, you are good to work in any data domain that can be Data Integration, Data Quality, Data warehousing and Business Intelligence etc. A person with strong SQL background can visualize and understand data much better. Now lets start learning this language in a series of articles. Here we will focus on ANSI SQL commands. Although some examples will contain SQL commands that are specific to Oracle or MS SQL server RDBMS.

SQL COMMANDS

The scope of SQL includes schema creation and modification, data access control, data insert, query, update and delete. SQL Commands are broadly classified into the following categories:

Data Definition Language (DDL)

- CREATE, ALTER, TRUNCATE, DROP

Data Manipulation Language (DML)

- INSERT, UPDATE, DELETE, MERGE

Data Query Language (DQL)

- SELECT

Transaction Control Language (TCL)

- COMMIT, ROLLBACK

Data Control Language (DCL)

- GRANT, REVOKE

ANSI Data types

Each column in an RDBMS table specifies/declares the types of data that the columns stores. This enables RDBMS to use storage space more efficiently by internally storing different types of data in different ways. ANSI SQL includes the following basic data types.

CHARACTER

- CHARACTER(n) or CHAR(n): Fixed width n-character string, padded with spaces as needed.

- CHARACTER VARYING(n) or VARCHAR(n): Variable width string with a maximum size of n characters.

- NATIONAL CHARACTER(n) or NCHAR(n): Fixed width n-character string supporting an international character set(Unicode Character).

- NATIONAL CHARACTER VARYING(n) or NVARCHAR(n): Variable width string with a maximum size of n characters supporting an international character set.

NUMERIC

- SMALLINT, INTEGER OR INT

- FLOAT, REAL and DOUBLE PRECISION

- NUMERIC(precision, scale) or DECIMAL(precision, scale) (e.g. 1234.56)

The number 1234.56 has a precision of 6 and a scale of 2. A scale of 0 indicates that the number is an integer.

DATE

- DATE: Date values (e.g. 1999-12-31).

- TIME: Time values (e.g. 23:30:10). The granularity of the time value is usually a tick (100 nanoseconds).

- TIMESTAMP: Date and a Time put together (e.g. 1999-12-31 23:30:10).

BIT

- BIT: bit values (e.g. 0 or 1)

Additional NON-ANSI LARGE OBJECT datatypes - CLOB, BLOB, LONG, RAW, LONG RAW (Oracle specific); VARBINARY(n) (MSSQL Server specific)

Procedural Language - SQL

Apart from SQL being a set-based declarative language (4GL), it also includes procedural elements. There are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. For example PL/SQL in Oracle, T-SQL in MS SQL server SPL in Teradata etc.

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles