Computer Science Notes

Structured Query Language - Part I

Structured Query Language (SQL)

Structured Query Language (SQL) is a standard language used for accessing databases. This is a special purpose
programming language used to create a table, manage data, and manipulate data.

SQL provides statements for a variety of tasks, including:

i) Querying data

ii) Inserting, updating, and deleting rows in a table

iii) Creating, replacing, altering, and dropping objects (tables)

iv) Controlling access to the database and its objects (tables)

v) Guaranteeing database consistency and integrity

Types of SQL Statements

The SQL statements are categorized into different categories based upon the purpose. Out of them, we will be studying
only two types:

i) Data Definition Language (DDL) statement:

Data Definition Language (DDL) or Data Description Language (DDL) is a standard for commands that defines the
different structures in a database. DDL statements are used to create structure of a table, modify the existing structure
of the table, and remove the existing table. Some of the DDL statements are CREATE TABLE, ALTER TABLE and DROP
TABLE.

ii) Data Manipulation Language (DML) statement

Data Manipulation Language (DML) statements are used to access and manipulate data in existing tables. The
manipulation includes inserting data into tables, deleting data from the tables, retrieving data, and modifying the
existing data. The common DML statements are UPDATE, DELETE and INSERT.
 Create a table
Changes Database Definition
Removes a table

Add a column
DDL
Remove a column

Change Data type of a column

Changes Table Definition
Add a constraint

SQL Remove a constraint

Change a constraint

Add rows

Change the data only Remove rows

DML
Displays Data Change data values

Data types in MySQL

Data type of an attribute indicates the type of data value that an attribute can have. It also decides the operations that
can be performed on the data of that attribute. For example, arithmetic operations can be performed on numeric data
but not on character data. Commonly used data types in MySQL are numeric types, date and time types, and string types

Data type Description

CHAR(n) • Specifies character type data of length n where n could be any value from 0 to 255.
• CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10 characters. If
data does not have 10 characters (e.g., ‘city’ has four characters), MySQL fills the remaining 6
characters with spaces padded on the right.

VARCHAR(n) • Specifies character type data of length where n could be any value from 0 to 65535.
• But unlike CHAR, VARCHAR(n) is a variable-length data type. That is, declaring VARCHAR (30)
means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the
length of entered string. So ‘city’ in VARCHAR (30) will occupy space needed to store 4 characters
only.
 INT • INT specifies an integer value.
• Each INT value occupies 4 bytes of storage.
• The range of unsigned values allowed in INT type are 0 to 4,294,967,295.
• For values larger than that, we must use BIGINT, which occupies 8 bytes.

FLOAT • Holds numbers with decimal points.

• Each FLOAT value occupies 4 bytes.

DATE • The DATE type is used for dates in 'YYYY-MM-DD' format.

• YYYY is the 4-digit year, MM is the 2-digit month and DD is the 2-digit date.
• The supported range is '1000-01-01' to '9999-12-31'.

SHOW DATABASES statement:

At any point of time, we can view names of all the databases contained in the server by using statement SHOW
DATABASES.

Syntax:

SHOW DATABASES;

CREATE DATABASE statement:

To create a database, we use the CREATE DATABASE statement

Syntax:

CREATE DATABASE <databasename>;

E.g.

CREATE DATABASE school;

USE statement:

To open the database to work on it (to make the database current), USE statement is used.

Syntax:

USE <databaename>

E.g.

USE School;

CREATE TABLE statement:

To create new relations(tables) in the database, the CREATE TABLE statement is used.

Syntax:
CREATE TABLE <tablename>(

attributename1 datatype [constraint],

attributename2 datatype [constraint],

attributenameN datatype [constraint]);

E.g.

mysql> CREATE TABLE Learner (

RollNo INTEGER PRIMARY KEY,

Name VARCHAR(25) );

• The number of columns in a table defines the degree of that relation, which is denoted by N.

• Attribute name specifies the name of the column in the table.

• Datatype specifies the type of data that an attribute can hold.

• Constraint indicates the restrictions imposed on the values of an attribute.

Constraints

Constraints are rules imposed on various attributes of a relation, which help usto ensure validity of the data.

Constraint Purpose
PRIMARY Sets a column or a group of columns as the Primary Key of a table. Therefore, NULLs and duplicate
KEY values in this column are not accepted.
NOT NULL Makes sure that NULLs are not accepted in the specified column.
UNIQUE Makes sure that duplicate values in the specified column are not accepted.
FOREIGN Data will be accepted in this column if same data value exists in a column in another related table. This
KEY other related table name and column name are specified while creating the foreign key constraint.

SHOW TABLES Command:

To view names of all the tables contained in the current database we use SHOW TABLES statement.

Syntax:

SHOW TABLES;

DESCRIBE/DESC statement

The DESCRIBE statement can be used to see the structure of a table as indicated in the Create Statement. It displays the
Column names, their data types, whether Column must contain data, whether the Column is a Primary key etc.

Syntax:

DESCRIBE <table name>;

OR

DESC <table name>;

E.g.

DESC Learner;

ALTER TABLE Statement:

To add/remove an attribute or to modify the datatype of an existing attribute or to add/remove constraint to attributes,
we can change or alter the structure (schema) of the table by using the ALTER TABLE statement.

To add an attribute to a relation:

Syntax:

ALTER TABLE <table name> ADD <attribute name> <Datatype> [constraint];

E.g.

ALTER TABLE item ADD Category CHAR(10);

ALTER TABLE Student ADD EXAMNO INT(3) UNIQUE;

To remove an attribute from a relation:

Syntax:

ALTER TABLE <table name> DROP <attribute name>;

E.g.

ALTER TABLE item DROP Qty;

To add primary key to a table (When it does not have one already):

Syntax:

ALTER TABLE <table name> ADD PRIMARY KEY(attribute1[,attribute2,…];

E.g.

ALTER TABLE Item ADD PRIMARY KEY(ItemNo):

ALTER TABLE Bills ADD PRIMARY KEY(Billno, BillDate);

To remove primary key from a table:

Syntax:

ALTER TABLE <table name> DROP PRIMARY KEY;

E.g.

ALTER TABLE Bills DROP PRIMARY KEY;

To change the data type of an attribute:

Syntax:

ALTER TABLE <table name> MODIFY <attribute name> <data type>;

E.g.

ALTER TABLE Item MODIFY ItemNo VARCHAR(10);

DROP TABLE statement:

We can use a DROP TABLE statement to remove a table permanently from the database.

Syntax:

DROP TABLE <tablename>;

E.g.

DROP TABLE Learner;

DROP DATABASE statement:

We can use a DROP DATABASE statement to remove a database permanently from the system.

Syntax:

DROP DATABASE <databasename>;

E.g.

DROP DATABASE School;