Introduction of Structured Query Language: SQL Practical File

SQL Practical File
Introduction of Structured Query Language

SQL is a database computer language designed for the retrieval and management of data in
relational database. SQL stands for Structured Query Language.
SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in relational database.
 Allows users to access data in relational database management systems.
 Allows users to describe the data.
 Allows users to define the data in database and manipulate that data.
 Allows to embed within other languages using SQL modules, libraries & pre-compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures, and views
1|Page Charanjeev Singh

SQL Practical File
SQL Statements
SQL statements are divided into four different categories: Data definition language (DDL), Data
manipulation language (DML), Data Control Language (DCL), Transaction Control Statement
(TCS).
1. Data Definition language (DDL)
Data definition statement are use to define the database structure or table.
Create :- to create new table or database
Alter :- Modifies the structure of database/table.
Drop:- Deletes a database/table.
Truncate :- Remove all table records including allocated table spaces.
Rename:- Rename the database/table.
2. Data Manipulation Language (DML)
Data manipulation commands are those commands which used to manipulate the data or the
information that is been stored into the database.
Insert :- Insert data into a table.
Update :- Updates existing data with new data within a table.
Delete :- Deletes the records rows from the table.
Select :- Retrieves certain records from one or more tables.
3. Data Control Language(DCL)
Data control language provides command to grant and take back authority.
GRANT :- Gives privileges to user for accessing database data.
Revoke :- Take back for given privileges.

SQL Practical File
Transaction Control Statement (TCS)
COMMIT :- Permanent work save into database.
ROLLBACK :- Restore database to original form since the last COMMIT.
SQL commands
1. Create table Command:
The create table statement is used to create a table in a database. A table consists of rows and
columns and must be saved with an appropriate name.
Syntax:
CREATE TABLE<tablename>
(<col_name1><datatype>(<size>),
<col_name1><datatype>(<size>),……)
2. Insert Command:
This command is used to insert or load the values into a table or relation.
Syntax:
INSERT INTO<tablename>
VALUES(‘&<label1>’,’&<label2>,……..);
3. View/Select :
Select statement is used to retrieve rows from the existing table.using select statement we can
retrieve all rows from table or selected rows from a table.
Syntax: SELECT * FROM <tablename>;

SQL Practical File
Example for create, insert and select query:
4. Update
UPDATE statement can be used to make the changes in a table.
It has three clauses
First clause: the actual update clause, where the table that will be update is named
Second clause : is the Set clause where all columns that will be changed by the update
statement are named, along with their new values.
Third clause : is the where clause.
Syntax : UPDATE table name
SET <columnname1= <expression> [,<column name2> = expression>]

……[,columnnameN>=<expression>] [ where <condition>];

SQL Practical File
Example:
5. Alter
Either to add a column or to change the datatype of already existing column in already existing
table,ALTER table statement is used.
Syntax: ALTER TABLE<tablename>
ADD(<new col_name><datatype>(<size>), <new col_name><datatype>(<size>),……..);
6. Delete
The DELETE command deletes the rows from the table that satisfies the condition provided
by WHERE clause, and returns the number of rows deleted.
Syntax: DELETE FROM<table name> WHERE <condition>;

SQL Practical File
Example:
For Alter and delete
7. Rename
Used to rename the already existing table.
Syntax : RENAME<Old tablename> TO<New tablename>;
8. Describe
DESCRIBE command is used to displasy the information about the columns.
Syntax: DESCRIBE<tablename>;

SQL Practical File
Example: for describe and rename query
INTEGRITY CONSTRAINTS
Integrity Constraints are used to apply business rules for the database tables.The constraints
available in SQL are Foreign Key, Not Null, Unique, Check.
1) The constraints can be specified immediately after the column definition. This is called
column-level definition.
2) The constraints can be specified after all the columns are defined. This is called table-
level definition.
1. NOT NULL
This constraint ensures all rows in the table contain a definite value for the column which is
specified as not null. Which means a null value is not allowed.
Syntax: <column name><data type>(<size>) [constraint constraint_name]Not Null;

SQL Practical File
2. UNIQUE
This constraint defines a column or combination of columns which uniquely identifies each row
in the table.
Syntax:
<column name><data type>(<size>)
[constraint constraint_name]Unique;
3. PRIMARY KEY
This constraint defines a column or combination of columns which uniquely identifies each row
in the table.A combination of a NOT NULL and UNIQUE
Syntax: //COLUMN LEVEL
<column name><data type>(<size>) PRIMARY KEY;
Syntax: //TABLE LEVEL
PRIMARY KEY(col_name);
Example:

SQL Practical File
4. FOREIGN KEY
This constraint identifies any column referencing the PRIMARY KEY in another table. It
establishes a relationship between two columns in the same table or between different tables.
For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table
which it is referring. One or more columns can be defined as Foreign key.

SQL Practical File
Syntax: //COLUMN LEVEL
<column name><data type>(<size>) REFERENCES<table_name>(<colname>)
[on delete cascade];
Syntax://TABLE LEVEL
FOREIGN KEY(<col_name>)REFERENCES<tablename>(<col_name>)
5. CHECK
This constraint defines a business rule on a column. All the rows must satisfy this rule. The
constraint can be applied for a single column or a group of columns.
Syntax:
<column name><data type>(<size>)
[constraint constraint_name]CHECK (logical expression);
10 | P a g e Charanjeev Singh
SQL Practical File
6. JOINS IN SQL
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field
between them.
EQUI JOIN/INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between
the columns in both tables.
Syntax:
Select<field1>,<field2>……
from <table1>,<table2>………
where<table1.keyfield=table2.keyfield>;
Example:
SQL Practical File
Self Join
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily
renaming at least one table in the SQL statement.
Syntax:
Select<table1.colname>,<table2.colname>……
from (<table1><alias tablename1>),(<table2><alias tablename2>)………
where<condition>;
Example:
Outer Join
LEFT OUTER JOIN - This returns all the matching rows and the unmatched rows of the left table
of the SQL code. RIGHT OUTER JOIN - This returns all the matching rows and the unmatched
rows of the right table of the SQL. FULL OUTER JOIN - This returns all the matching and
unmatched rows from both the tables
SQL Practical File
Syntax:
Select<table1.colname>,<table2.colname>……
from <table1>,<table2>………
where
<table1.colname>=<table2.colname>(+);
Example:
Cartesian /Cross Join
The CROSS JOIN produced a result set which is the product of rows of two associated tables
when no WHERE clause is used with CROSS JOIN. In this join the result set appeared by
multiplying each row of the first table with all rows in the second table if no condition
introduced with CROSS JOIN.
Syntax:
SELECT(<COL1>,<COL2>……..)
FROM(<TABLE1><TABLE2>…..);
SQL Practical File
Example:
7. SET OPERATORS
Set operators are used to join the results of two (or more) SELECT statements.The SET operators
available in Oracle 11g are UNION,UNION ALL,INTERSECT,and MINUS.
All the SET operators share the same degree of precedence among them.
UNION
The UNION set operator returns the combined results of the two SELECT
statements.Essentially,it removes duplicates from the results i.e. only one row will be listed for
each duplicated result
Syntax:
Select<col_list> from <table1> UNION Select<col_list> from <table2>;
INTERSECTION
INTERSECT lists only records that are common to both the SELECT queries.
Syntax:
Select<col_list> from <table1>
SQL Practical File
INTERSECT
Select<col_list> from <table2>;
MINUS
the MINUS set operator removes the second query's results from the output if they are also
found in the first query's results.
Syntax:
Select<col_list> from <table1> MINUS Select<col_list> from <table2>;
Output:-
SQL Practical File
VIEWS IN SQL
A view is nothing more than a SQL statement that is stored in the database with an associated
name. A view is actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table.
Syntax:
Create view<view name>
as select<col1,col2,…..>
from<table name>
[WHERE <condition>]
[GROUP BY<group by criteria>]
[<HAVING CLAUSE>];
Example:
SQL Practical File
JOIN VIEW
A join view is a view which is based in more than one base table. Any insert, update or delete
statement on a view can update only one of the base tables involved in the view. A table is
called a key preserved in a join view, if the primary and unique keys are unique on the views
result set. Join view does not use GROUP BY, ROWNUM and DISTINCT clause.
Dropping a view:
The SQL DROP VIEW statement is the SQL command that removes an entire SQL view. You can
change the definition of a view by dropping and re-creating it. When you drop a view all the
associated grants are also dropped. You can also change the definition of the view by using the
CREATE OR REPLACE command.
Syntax: DROP VIEW VIEW_NAME;
Inline View:
It is a subquery that appears in the ‘from’ clause of the select statement. This subquery is
enclosed in parenthesis and may be given an alias name. The columns selected in the subquery
can be referenced in the parent query.
SQL Practical File
Materialized View:
In a materialized view, a summary table is created from a base table, and all queries that
perform a similar summation against the base table will be transparently re-written to
reference the pre-built summary table.A materialized view is a database object that contains
the results of a query. They are local copies of data located remotely, or are used to create
summary tables based on aggregations of a table’s data.
Materialized views, which store data based on remote tables are also, know as snapshots. A
materialized view can query tables, views, and other materialized views. Collectively these are
called master tables (a replication term) or detail tables (a data warehouse term).
A materialized view provides indirect access to table data by storing the results of a query in a
separate schema object. Unlike an ordinary view, which does not take up any storage space or
contain any data.
NEED for Materialized Views
Materialized views are used in data warehouses so as to increase the speed of queries on a
larger database. In large databases, the queries often involve joins between tables and
aggregation like sum and etc. so to improve the performance materialized views are used.
Materialized views can be used to replicate the data.

Introduction of Structured Query Language: SQL Practical File

Uploaded by

Copyright:

Available Formats

Introduction of Structured Query Language: SQL Practical File

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Introduction of Structured Query Language: SQL Practical File

Uploaded by

Copyright:

Available Formats

SQL Practical File

Introduction of Structured Query Language

 Allows users to access data in relational database management systems.

 Allows users to describe the data.

 Allows users to create and drop databases and tables.

 Allows users to create view, stored procedure, functions in a database.

 Allows users to set permissions on tables, procedures, and views

1|Page Charanjeev Singh

1. Data Definition language (DDL)

Create :- to create new table or database

Alter :- Modifies the structure of database/table.

Drop:- Deletes a database/table.

Truncate :- Remove all table records including allocated table spaces.

Rename:- Rename the database/table.

2. Data Manipulation Language (DML)

Insert :- Insert data into a table.

Update :- Updates existing data with new data within a table.

Delete :- Deletes the records rows from the table.

Select :- Retrieves certain records from one or more tables.

3. Data Control Language(DCL)

GRANT :- Gives privileges to user for accessing database data.

Revoke :- Take back for given privileges.

2|Page Charanjeev Singh

Transaction Control Statement (TCS)

COMMIT :- Permanent work save into database.

ROLLBACK :- Restore database to original form since the last COMMIT.

1. Create table Command:

Syntax: SELECT * FROM <tablename>;

3|Page Charanjeev Singh

Example for create, insert and select query:

UPDATE statement can be used to make the changes in a table.

It has three clauses

Third clause : is the where clause.

Syntax : UPDATE table name

SET <columnname1= <expression> [,<column name2> = expression>]

4|Page Charanjeev Singh

Syntax: ALTER TABLE<tablename>

ADD(<new col_name><datatype>(<size>), <new col_name><datatype>(<size>),……..);

Syntax: DELETE FROM<table name> WHERE <condition>;

5|Page Charanjeev Singh

For Alter and delete

Used to rename the already existing table.

Syntax : RENAME<Old tablename> TO<New tablename>;

DESCRIBE command is used to displasy the information about the columns.

6|Page Charanjeev Singh

Example: for describe and rename query

Syntax: <column name><data type>(<size>) [constraint constraint_name]Not Null;

7|Page Charanjeev Singh

<column name><data type>(<size>)

Syntax: //COLUMN LEVEL

<column name><data type>(<size>) PRIMARY KEY;

Syntax: //TABLE LEVEL

8|Page Charanjeev Singh

9|Page Charanjeev Singh

Syntax: //COLUMN LEVEL

<column name><data type>(<size>) REFERENCES<table_name>(<colname>)

[on delete cascade];

<column name><data type>(<size>)

[constraint constraint_name]CHECK (logical expression);