SQL

INTRODUCTION TO STRUCTURED QUERY LANGUAGE(SQL)

SQL stands for Structured Query Language and is commonly pronounced

as „SEQUEL‟.
It is the interface between user and the oracle database.
The American National Standard Institute has accepted SQL as the
standard access language for Relational Database management Systems
and all of Oracle access tools are based on this Standard.
The purpose of SQL is to provide an interface to a relational database such
as oracle and all SQL statements.
It has been a common language for communication with Oracle server from
any tool or application.

2
SQL has 9 commands which are common to all RDBMS
CREATE,DROP,ALTER for Tables
INSERT,UPDATE,DELETE for Records
GRANT,REVOKE FOR Permission
SELECT for Query

3
SQL has 9 commands which are common to all RDBMS
CREATE,DROP,ALTER for Tables
INSERT,UPDATE,DELETE for Records
GRANT,REVOKE for Permissions
SELECT for Queries

4
COMPONENTS OF SQL
CREATE - to create Database objects like tables, views
in the database
ALTER – to modify the columns of the table (increasing,
changing data types)
DROP – to delete a table or database
TRUNCATE - remove all records from a table without deleting
table structure.
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT
command
5
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE – delete existing records from a table
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you
can later roll back
ROLLBACK - restore database to original since the last
COMMIT

6
Database Creation
A database is a structured collection of data that is stored in a computer
system. They are used to store and retrieve the data efficiently.

CREATE DATABASE DatabaseName;

• The CREATE DATABASE statement is a DDL (Data Definition

Language) statement used to create a new database in SQL.
• If you are creating your database on Linux or Unix, then database
names are case-sensitive, even though SQL keywords are case-insensitive.
• If you are working on Windows then this restriction does not apply.

7
List Databases

Once the database is created, you can check it in the list of databases using

SHOW DATABASES;

Use Databases

We can now set the created database as the default database by using
the USE statement in SQL

USE DatabaseName;

Alter Database Name

ALTER DATABASE OldDatabaseName

MODIFY NAME = NewDatabaseName;

8
Drop Databases

The SQL DROP DATABASE statement is used to delete an existing

database along with all the data such as tables, views, indexes, stored
procedures, and constraints.

DROP DATABASE DatabaseName;

DROP DATABASE IF EXISTS DatabaseName;

DROP DATABASE testDB3, testDB4;

• Make sure you have taken proper backup of the database before you
delete it.
• Make sure no other application is connected and using this database.
• Make sure you have the necessary privilege to delete the database.

9
Table Creation
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold
(e.g. varchar, integer, date, etc.).

DESC
To see the description(schema) of the created table
DESC <tablename> EX: DESC student
10
Table Creation if not exists
CREATE TABLE IF NOT EXISTS table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

Show Tables
To list down all the tables available in a selected database
SHOW TABLES;

SELECT * FROM [Link];

11
Listing DBA related tables
SELECT owner, table_name FROM DBA_TABLES

Listing User tables

SELECT owner, table_name FROM USER_TABLES

12
Data Types
Each column in a database table is required to have a name and a
data type.

An SQL developer must 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.

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

[Link]

13
Table Creation
CHAR(size) : A FIXED length string (can contain letters, numbers, and
special characters).
The size parameter specifies the column length in characters can be from 0 to 255.
Default is 1

VARCHAR(size) : A VARIABLE length string (can contain letters, numbers,

and special characters).
The size parameter specifies the max string length in chars can be from 0 to 655535

BINARY(size) : Equal to CHAR(), but stores binary byte strings. Default is 1

DECIMAL(size, d) : An exact fixed-point number. The total number of digits

is specified in size.
The number of digits after the decimal point is specified in the d parameter.
The maximum number for size is 65. The maximum number for d is 30.
The default value for size is 10. The default value for d is 0.

DATE : date. Format: YYYY-MM-DD.

The supported range is from '1000-01-01' to '9999-12-31'

14
Key Difference Between Varchar and Varchar2

15
Table Creation Example
The following example creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address, and City:
Example
CREATE TABLE Persons (
PersonID int,
LastName varchar2(255),
FirstName varchar2(255),
Address varchar2(255),
City varchar2(255)
);

16
Create Table Using Another Table
A copy of an existing table can also be created using CREATE TABLE.
The new table gets the same column definitions. All columns or specific
columns can be selected.

If you create a new table using an existing table, the new table will be filled
with the existing values from the old table.

Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;

Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

17
Insert command
SQL INSERT INTO Statement

• The INSERT INTO statement is used to add new data to a database.

• The INSERT INTO statement adds a new record to a table.
• INSERT INTO can contain values for some or all of its columns.
• INSERT INTO can be combined with a SELECT to insert a record.

18
Insert command
In the first method there is no need to specify the column name where the
data will be inserted, you need only their values.

INSERT INTO table_name

VALUES (value1, value2, value3....);

The second method specifies both the column name and values which you
want to insert.

INSERT INTO table_name (column1, column2, column3....)

VALUES (value1, value2, value3.....);

19
Insert command
Inserting data through SELECT Statement

SQL INSERT INTO SELECT Syntax

INSERT INTO table_name [(column1, column2, .... column)]

SELECT column1, column2, .... Column N

FROM table_name
[WHERE condition];

20
Inserting Top N Rows

The LIMIT clause filters the number of rows from the query. You can use
this to filter the top N records that should be added to the target table.

INSERT INTO BUYERS

SELECT * FROM CUSTOMERS
ORDER BY ID ASC LIMIT 3;

21
Insert Multiple Records
INSERT INTO
Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES

('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),

('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),

('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

22
Insert Values Through Procedure

Insert into Customer exec spGetCustDetails

Create a stored procedure that returns data of table “Customer”.

create procedure spGetCustDetails as

begin
select * from Customer
end

• The number of columns returned by the stored procedure should match with the
inserting table (Customer).
• We can also specify the columns in the query to insert particular columns data.

Note:
It is not possible to insert stored procedure result set data into a new table. Thus,
create a table based on the result set returned by the stored procedure.

23
Update command
SQL UPDATE statement is used to change the data of the records held by
tables. Which rows is to be update, it is decided by a condition. To specify
condition, we use WHERE clause.

The UPDATE statement can be written in following form:

UPDATE table_name
SET [column_name1= value1,... column_nameN = valueN]
[WHERE condition]

Let's see the Syntax:

UPDATE table_name
SET column_name = expression
WHERE conditions

24
Update command
UPDATE students
SET User_Name = 'beinghuman'
WHERE Student_Id = 3

25
Updating Multiple Fields:
If you are going to update multiple fields, you should separate each field
assignment with a comma.
SQL UPDATE statement for multiple fields:

UPDATE students
SET User_Name = 'beserious', First_Name = 'Johnny'
WHERE Student_Id = 3

26
Delete command
The SQL DELETE statement is used to delete rows from a table.
Generally DELETE statement removes one or more records from a table.

SQL DELETE Syntax

DELETE FROM table_name [WHERE condition];

DELETE FROM EMPLOYEE WHERE ID=101;

DELETE * FROM EMPLOYEE;

27
ALTER command
The ALTER TABLE statement in SQL allows you to add, modify, and delete
columns of an existing table.
This statement also allows database users to add and remove various SQL
constraints on the existing tables.

Syntax of ALTER TABLE ADD Column statement in SQL

ALTER TABLE table_name

ADD (Columnname_1 datatype,
Columnname_2 datatype, …
Columnname_n datatype);

EX: ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40));

DROP COLUMN is used to drop column in a table.

ALTER TABLE table_name DROP COLUMN column_name;

EX: ALTER TABLE Student DROP COLUMN COURSE;
28
ALTER command
ALTER TABLE MODIFY Column statement in SQL
The MODIFY keyword is used for changing the column definition of the
existing table.

ALTER TABLE table_name

MODIFY (column_Name1 column-definition,
column_Name2 column-definition,
.....
column_NameN column-definition);
EX: ALTER TABLE Student MODIFY COURSE varchar(40);

ALTER TABLE RENAME Column statement in SQL

The RENAME keyword is used for changing the name of columns or fields of
the existing table.

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

EX: ALTER TABLE Student RENAME COLUMN Age to StdAge;

29
Rename command
Any database user can easily change the name by using the RENAME
TABLE and ALTER TABLE statement in Structured Query Language.

Syntax of RENAME statement in SQL

RENAME old_table _name To new_table_name ;

30
Drop command
A SQL DROP TABLE statement is used to delete a table definition and
all data from a table.

This is very important to know that once a table is deleted all the
information available in the table is lost forever, so we have to be very
careful when using this command.

DROP TABLE table_name;

DROP TABLE STUDENTS;

31
Integrity Constraints
Integrity constraints are a set of rules. It is used to maintain the quality of
information.
Integrity constraints ensure that the data insertion, updating, and other
processes have to be performed in such a way that data integrity is not
affected.
Thus, integrity constraint is used to guard against accidental damage to
the database.

32
1. Domain constraints
Domain constraints can be defined as the definition of a valid set of values
for an attribute.
The data type of domain includes string, character, integer, time, date,
currency, etc.
The value of the attribute must be available in the corresponding domain.

1. UNIQUE
2. NOT NULL
3. CHECK
4. DEFAULT

33
Domain constraints

NOT NULL: This is to restrict null values in specified column.

Null is not a zero or a space.

UNIQUE: restricts duplicate values (rows).

CHECK: this constraint is to check the value entered for a

column is whether correct or not

DEFAULT: used to set a default value for a column.

The default value will be added to all new records,
if no other value is specified.

34
NOTNULL
used to set a default value for a column. The default value will be added
to all new records,if no other value is specified.

CREATE TABLE table_Name ( column1 data_type(size) NOT NULL,

column2 data_type(size) NOT NULL, .... );

CREATE TABLE EMPLOYEES( EMPID INTEGER NOT NULL,

EName VARCHAR2(10) NOT NULL, DOJ DATE);
SQL NOT NULL on ALTER table
We can also add a NOT NULL constraint in the existing table using the ALTER
statement.

For example, if the EMPLOYEES table has already been created then add NOT
NULL constraints to the “DOJ” column use ALTER statements in SQL as follows:

ALTER TABLE EMPLOYEES modify DOJ DATE NOT NULL;

35
NOTNULL

36
UNIQUE
A unique key is a set of one or more than one fields/columns of a table
that uniquely identify a record in a database table.

CREATE TABLE table_Name ( column1 data_type(size) UNIQUE,

column2 data_type(size), .... );

CREATE TABLE EMPLOYEES( EMPID INTEGER UNIQUE,

EName VARCHAR2(10) NOT NULL, DOJ DATE);
Defining UNIQUE key on multiple columns
CONSTRAINT Std_uid UNIQUE (S_Id, Name)

ALTER TABLE students ADD UNIQUE (S_Id)

ALTER TABLE students ADD CONSTRAINT Std_uid UNIQUE (S_Id, Name)

37
UNIQUE

38
DEFAULT
used to set a default value for a column. The default value will be added
to all new records,if no other value is specified.

CREATE TABLE tablename ( Columnname DEFAULT 'defaultvalue' );

CREATE TABLE Student ( ID int NOT NULL, Name varchar(255), Age int,
Location varchar(255) DEFAULT „AP');

DROP a DEFAULT Constraint :

ALTER TABLE tablename ALTER COLUMN columnname DROP DEFAULT;

Example
ALTER TABLE Student ALTER COLUMN Location DROP

Dropping the default constraint will not affect the current data in the table, it will
only apply to new rows.

39
DEFAULT

40
CHECK
Check Constraint is used to specify a predicate that every tuple must
satisfy in a given relation. It limits the values that a column can hold in a
relation.
The predicate in check constraint can hold a sub query.
Check constraint defined on an attribute restricts the range of values for
that attribute.
If the value being added to an attribute of a tuple violates the check
constraint, the check constraint evaluates to false and the corresponding
update is aborted.
Check constraint is generally specified with the CREATE TABLE
command in SQL.

41
CHECK
CREATE TABLE pets( ID INT NOT NULL,
Name VARCHAR(30) NOT NULL,
Breed VARCHAR(20) NOT NULL,
GENDER VARCHAR(9),
check(GENDER in ('Male', 'Female', 'Unknown')) );

CREATE TABLE student( StudentID INT NOT NULL,

Name VARCHAR(30) NOT NULL,
Age INT NOT NULL,
GENDER VARCHAR(9),
check(Age >= 18) );

42
CHECK

Different options to use Check constraint:

With alter: Check constraint can also be added to an already created relation
using the syntax
alter table TABLE_NAME modify COLUMN_NAME check(Predicate);

Giving variable name to check constraint:Check constraints can be given

a variable name using the syntax
alter table TABLE_NAME add constraint CHECK_CONST check (Predicate);

Remove check constraint: Check constraint can be removed from the

relation in the database from SQL server using the syntax:
alter table TABLE_NAME drop constraint CHECK_CONSTRAINT_NAME;

Drop check constraint: Check constraint can be dropped from the relation
in the database in MySQL using the syntax:
alter table TABLE_NAME drop check CHECK_CONSTRAINT_NAME;

43
44