SQL - Basics

SQL is a standard language for storing, manipulating and retrieving data in 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 "Categories" table:

Every table is broken up into smaller entities called fields. The fields in the Customers table consist of Membe_Numberr, Date, ItemDescription, Year, Month, day and purchaseid. 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 categories 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 Data types.

The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

Each column in database table is required to have a name and data type.

An SQL developer will decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

1. DDL : (Data Definition Language) Used to define the structure of database objects such as tables, views, functions etc. Using DDL commands, we can create, modify and drop any database objects. The commands include.

* Create

Create a new database objects such as tables, views or functions etc.

Syntax to create a new table is:

CREATE TABLEIF NOT EXISTS STUDENTS

(

ID VARCHAR(20) PRIMARY KEY,

FIRST_NAME VARCHAR(100) NOT NULL,

LAST_NAME VARHCAR(100) NOT NULL,

GENDER VARCHAR(10) CHECK(GENDER IN ('M','F','MALE',FEMALE')),

AGE INT,

DOB DATE,

GRADE FLOAT,

IS_ACTIVE BOOLEAN,

CONSTRAINT CH_STUDENTS_AGE CHECK(AGE > 0)

);

Here STUDENTS is the name of the table whereas it has 6 columns namely ID, FIRST_NAME, LAST_NAME, GENDER, AGE and DOB. These columns belong to different data type.

There are also few constraints like PRIMARY KEY, NOT NULL, CHECK used in this table.

IF NOT EXISTS before the table name is an optional clause which can be included while creating table which tells the RDBMS to check if the table already exist. If table exists then RDBMS will skip executing this create statement else the statement will be executed and table will be create. In order to completely understand how to properly create a table, we need to first understand what is Data Type and what are Constraints:

Data Type

Tables in RDBMS consist of rows and columns. Each column has a data type associated to it. Data type is like a data rule applicable to that particular column. Meaning that only the data or values satisfying this data rule can be inserted into this column. There are several types of data types.

However, In this blog we will go through the most basic and most commonly used data type across RDBMS.

Please note : Data types might have different names in different database. And even if the same, the size and other details may be different! Always we need to check the documentation.

Data types.

Type of data/value an object can hold or process is known as data type.

Size of data type.

Size of a data type is the maximum value which an object can hold.

* Amount of data an object can hold.

* SQL supports lots of data types.

(Some data types are not supported by all DBMS software)

Lets note the standard data types, which are supported by all the DBMS software.

Different SQL data types

1. Exact Numeric Data Type.

Used to store integer values, that is numbers without a decimal point. such as 23,-56,+1034 etc.

A. BIT : It is used to store binary digits, that is, 0 or 1.

B. TINYINT : Can store value between 0 and 255.

C. SMALLINT : Can store values between (-32768) and 32767 (or + 32767)

D. INT : (-2147483648) to (+2147483647)

E. BIGINT : (-9223372036854775808) to (+9223372036854775807)

F. DECIMAL : (-10^32)+1 to (+10^32)-1

2. Approximate Numeric Data Type.

Used to store real number values, that is numbers with a decimal point. Such as 23.04, -56.6541214651, +1034.00 etc.

i) FLOAT - (1.7*10^-308) TO (1.7*10^+308)

ii) REAL - (3.4*10^-38) TO (3.4*10^+38)

3. Data & Time Data type

Used to store date and time related data

i) DATE : Used to store the date in DD/MM/YYYY format

ii) TIME : Used to store time in hh:mm:ss format.

iii) DATETIME : Used to store both date and time.

4. String Data Types.

Used to store string values, that is text, such as a sentence or a word or any other related data.

1. Char

2. VARCHAR

---> CHAR : Used to store up to 8000. Its length is fixed and we cannot change it. In CHAR, If the length of a string is less than set or fixed length then it is padded with extra memory space.

----> VARCHAR(SIZE) : Used to store up to 8000. Its length is variable and we can change it. In VARCHAR, If the length of string is less than set or fixed length then it will store as it is without padded with extra memory spaces.

Size : Maximum length of the data type is to be declared example : VARCHAR(20).

BIT = 1 OR 0

Bytes = 8 Bits.

-----------------------------------------------------------------------------------------------------------------------------------

CONSTRAINTS

Constraints refers to limitation or restriction applied to a column in a table. Constraints are very important to maintain data integrity among tables. If you want to make sure that wrong data is not inserted into your table then these kind of sanity checks can be applied by using CONSTRAINTS. Let’s look some of the most widely used constraints in RDBMS:

• CHECK: CHECK constraint allows you to control the values that can be inserted into a column. Let’s say if you have a column “AGE” and you want to make sure that only positive values are being is inserted into this column then you can use a CHECK constraint on this column to apply this rule. So if anyone tries to insert a negative value into this column then RDBMS will throw an error.

• NOT NULL: By applying NOT NULL constraint on a column, you make sure that this column will never have NULL or empty values.

• UNIQUE: Unique constraints are used to make sure that values inserted into a column across all the rows have unique or distinct values. It can help you to eliminate any duplicate data in a column. Remember NULL values are allowed in a UNIQUE constraint column. And two NULL values are not same hence multiple rows with NULL values are allowed.

• PRIMARY KEY: Primary key constraints is basically a combination of UNIQUE + NOT NULL constraint. It will ensure that all the values in the column are unique and there are no NULL values. A table can only have one primary key constraint. Primary key constraint can either be applied to a single column or to a combination of multiple columns in the table.

• FOREIGN KEY: FOREIGN KEY constraint can be used to form relationship between tables. It basically helps to create a parent child relationship between 2 tables. Such that the child table references a column value from the parent table. So that only the values present in parent table can be inserted into the child table.

Other Words - The FOREIGN KEY constraint is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Ex: On technical level.

A table of customer orders might have a "User" Column with a foreign key attribute that links it to the "user_id" column in a users table. This way, each row in attribute that links it to the user_id column in a user table. This way, each row in the orders table can be associated with a specific user from the users table. And no orders can enter system without a valid user being associated with them.

* Alter

Alter is used to modify the structure of an existing table. Alter can be used to rename a table or rename a column. Alter can also be used to add new column or change the data type of an existing column. Using Alter we can also add new constraints or remove a constraint from a table.

Syntax to alter table is :

ALTER TABLE STUDENTS DROP COLUMN GRADE; -- Drop a column. ALTER TABLE STUDENTS ADD COLUMN REGISTER_NO VARCHAR(100); -- Add new column. ALTER TABLE STUDENTS ALTER COLUMN IS_ACTIVE TYPE VARCHAR(1); -- Change data type of a column. ALTER TABLE STUDENTS RENAME COLUMN IS_ACTIVE TO ACTIVE; -- Rename a column. ALTER TABLE STUDENTS ADD CONSTRAINT UNQ_STD UNIQUE (REGISTER_NO); -- Add new constraint ALTER TABLE STUDENTS DROP CONSTRAINT UNQ_STD; -- Drop a constraint. ALTER TABLE STUDENTS RENAME TO STUDENTS123; -- Rename a table.

* Drop

Drop as the name suggest, is used to remove a database object such as a table, view, functions etc from the database.

Syntax to drop table is :

DROP TABLE STUDENTS;

* TRUNCATE :

Truncate is used to remove all the data from a table at once.

Syntax to truncate table is:

TRUNCATE TABLE STUDENTS;

DML (Data Manipulation Language)

DML commands are used to load, modify and remove data from the database. The commands include:

* INSERT

Insert command can be used to load data into the table. The syntax is: (considering the original table structure we created above in CREATE command) INSERT INTO STUDENTS (ID, FIRST_NAME, LAST_NAME, GENDER, AGE, DOB, GRADE, IS_ACTIVE) VALUES ('STD10251','Minnaminnie','Cleft','Female',8,TO_DATE('2012-02-23', 'YYYY-MM-DD'), 3, TRUE); -- Mention the column names. INSERT INTO STUDENTS VALUES ('STD10252','Effie','Emlyn','Female',8,TO_DATE('2012-03-28', 'YYYY-MM-DD'), 3, TRUE); -- Do not mention column names. INSERT INTO STUDENTS VALUES ('STD10253','Kerry','Aysik','Female',8,TO_DATE('2012-01-09', 'YYYY-MM-DD'), 3, TRUE), ('STD10254','Jo','Mansfield','Male',8,TO_DATE('2012-03-26', 'YYYY-MM-DD'), 3, TRUE), ('STD10255','Elianore','Macon','Female',8,TO_DATE('2012-04-01', 'YYYY-MM-DD'), 3, FALSE); -- Insert multiple records.

* UPDATE

Update commands is used to modify the data in the table.

The syntax is:

UPDATE STUDENTS SET

FIRST_NAME = 'James' WHERE ID = 'STD10253'; -- Update single column.

UPDATE STUDENTS SET

FIRST_NAME = 'Rohan', GRADE = 4 WHERE ID = 'STD10251'; -- Update multiple columns at once.

SQL Redshift Functions

TO_CHAR function is used to typecast a numeric or date input to character type with a format model (optional).

Syntax

TO_CHAR(number1, [format], [nls_parameter])

SQL Conversion Functions

1. TO_CHAR()

Redshift Functions.

1. DATE_TRUNC function

The DATE_TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, week, or month. DATE_TRUNC returns the first day of the specified year, the first day of the specified month, or the Monday of the specified week.

Syntax

DATE_TRUNC('datepart', timestamp)

Arguments

datepart : The date part to which to truncate the timestamp value. For valid formats, see Date parts for date or timestamp functions.

timestamp : A timestamp column or an expression that implicitly converts to a timestamp.

Return type

TIMESTAMP

Example

In the following example, the DATE_TRUNC function uses the 'week' date part to return the date for the Monday of each week.

Select date_trunc('week',saletime), sum(pricepaid)from sales where salestime like '2008-09%' group by date_trunc('week',saletime) order by 1;

EXTRACT function

The EXTRACT function returns a date or time part, such as a day, month, or year, hour, minute, second, millisecond, or microsecond from a TIMESTAMP value or expression, TIME, or TIMETZ.

Syntax

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp | time | timetz } )

Arguments

datepart : For possible values, see Date parts for date or timestamp functions.literalA timestamp value, enclosed in single quotation marks and preceded by the TIMESTAMP keyword.

literal : A timestamp value, enclosed in single quotation marks and preceded by the TIMESTAMP keyword.timestamp | times | timestzA TIMESTAMP, TIMESTAMPTZ, TIME, or TIMETZ column, or an expression that implicitly converts to a TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, or TIMETZ.

Return type

INTEGER if the argument is TIMESTAMP, TIME, or TIMETZ

DOUBLE PRECISION if the argument is TIMESTAMPTZ

Examples with a timestamp column

The following example determines the week numbers for sales in which the price paid was $10,000 or more.

Select salesid, extract(week from saletime) as weeknum

from sales where pricepaid > 999 order by 2;

The following example returns the minute value from a literal timestamp value.

It will be Continued................................................................................................

----------------------------------------------------------------------------------------------------------------------------------------

Rough Page

How to use MS SQL datatype

• You need to define in advance, the type of data a column or variable can store. Determining data type also restricts the user from entering any unexpected or invalid data.

• You can make efficient use of memory by assigning an appropriate data type to variable or column which will allocate only the required amount of system memory for the respective column’s data.

• MS SQL offers a broad category of basic data types in SQL as per user’s needs like Date, binary images, etc.

Why use DataTypes?

Let’s, take a sample of simple Sign up page of website application.Three input fields are First Name, Last Name & Contact number.

Here we should note that in real time:

• “First/Last Name” will always be alphabetic.

• “Contact” will always be numeric.

* From the above picture it worth defining “First/Last Name” as a character and “Contact” as an integer.

It is evident that in any application, all fields have one or the other type of data. E.g., numeric, alphabetic, date, and many more.

Also, note that different datatype has different memory requirement. Therefore, it makes more sense to define the column or variable with the data type it will hold for efficient use of memory.

Data type available in MS SQL Server

• Exact numeric

• Approximate numeric

• Date and time

• Character strings

• Unicode character strings

• Binary strings

• Other data types

Exact Numeric Data Types in SQL

Exact numeric has nine types of sub data types in SQL server.

Approximate Numeric Data Types in SQL

SQL Approximate Numeric category includes floating point and real values. These datatypes in SQL are mostly used in scientific calculations.

Approximate Numeric Data Type

Date and Time Data Types in SQL

It stores data of type Date and time.

Date and Time Data Type