SQL stands for Structured Query Language. SQL is a standard computer language for accessing and manipulating databases. SQL commands can be divided into two main sublanguages. The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language to insert, retrieve and modify the data contained within it.
SQL Data Manipulation Language (DML)
SQL (Structured Query Language) is syntax for executing queries. But the SQL language also includes syntax to update, insert, and delete records.
These query and update commands together form the Data Manipulation Language (DML) part of SQL:
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table SQL Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index SQL Data Control Language (DCL)
Data control commands in SQL allow us to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:
• ALTER PASSWORD
• GRANT
• REVOKE
• CREATE SYNONYM
How to create a Database?
Let's use the CREATE DATABASE command to set up our database:
Syntax: CREATE DATABASE databasename Example: CREATE DATABASE product
This Command creates an empty database named "product" on our DBMS. After creating the database, our next step is to create tables that will contain data.
Create a Table:
Syntax: CREATE TABLE table_name (
Columnname1 datatype(size), Columnname2 datatype(size), Columnname3 datatype(size) )
This example demonstrates how we can create a table named "purchase_details", with six columns.
Example: CREATE TABLE purchase_details (
purchase_id integer, product_id varchar(10), product_name varchar(20), quantity integer,
amount float, date datetime )
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:
Data Type Description
integer(size) int(size) smallint(size) tinyint(size)
Hold integers only. The maximum number of digits are specified in parenthesis.
decimal(size,d) numeric(size,d)
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
char(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.
varchar(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
date(yyyymmdd) Holds a date
Inserting values in a table:
The INSERT command in SQL is used to add records to an existing table. Returning to the purchase_details example from the previous section, let's imagine that we need to add a new purchase order to our database.
Syntax: INSERT INTO table_name VALUES (value1, value2 …) Example: INSERT INTO purchase_details
VALUES (1, 'p1','pen', 10, 7.00,'07-20-2006')
We can also specify the columns for which we want to insert data:
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2 ...)
INSERT INTO purchase_details (product_id, product_name) VALUES ('b1','box')
SELECTING VALUES
The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database.
The command shown below retrieves all of the information contained within the
purchase_details table. Note that the asterisk is used as a wildcard in SQL. This literally means "Select everything from the purchase_details table."
Syntax: SELECT * FROM table_name
Example: SELECT * FROM purchase_details
Alternatively, if users want to limit the attributes that are retrieved from the database, say they require a list of the product ids of all products. The following SQL command would retrieve only that information:
SELECT product_id FROM purchase_details UPDATE
The UPDATE command can be used to modify information contained within a table, either in bulk or individually.
Syntax: UPDATE table_name SET column_name=value1 WHERE column_name=value2
Example: update purchase_details set amount=8.50 where product_id='p1' ALTER
The ALTER command can be used to modify the structures of table, either adding new columns or modify existing column.
Syntax: ALTER TABLE table_name
ADD newcolumnname datatype(size) Example: ALTER TABLE purchase_details
ADD product_total varchar(20) CONSTRAINTS
A Constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
TYPES OF CONSTRAINTS PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL
PRIMARY KEY:
A PRIMARY KEY constraint is a unique identifier for a row within a database table.
Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
Syntax: columnname datatype(size) PRIMARY KEY Example:
CREATE TABLE purchase_details (
purchase_id integer PRIMARY KEY not null, product_id varchar(10),
product_name varchar(20), quantity integer,
amount float, date datetime )
In case of existing table:
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(columnname) Example:
ALTER TABLE purchase_details
ADD CONSTRAINT pk PRIMARY KEY(purchase_id) FOREIGN KEY
A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
Syntax: columnname datatype(size) FOREIGN KEY REFERENCES table_name(columnname) Example:
CREATE TABLE stock_details (
stock_id integer PRIMARY KEY not null,
purchase_id integer FOREIGN KEY REFERENCES purchase_details1(purchase_id), stock integer
)
UNIQUE
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.