View

View is nothing but parsed SQL statement which fetches record at the time of execution.

There are mainly two types of views:

1. Simple View.
2. Complex View.

Also

1. Updatable Views.
2. Read only Views.

Finally, we have Materialized Views.

View is used for the purposes as stated below:

1. Security.
2. Faster response.
3. Solve complex query.

Syntax is:

CREATE or REPLACE view ([olumn1] [column2]...)
AS
SELECT column1 column2...
FROM tablename
[WHERE condition]
[WITH read only] [WITH check option]

Create

A Data Definition Language or Data Description Language (DDL) is a computer language for defining data structures.

CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].

In create there are some statements for referential integrity.

DROP objecttype objectname.

ALTER objecttype objectname parameters

Join

• CARTESIAN JOIN is a join of every row of one table to every row of another table.
• INNER JOIN (sometimes called the “EQUI-JOIN”) is a join that selects only those records from both database tables that have matching values.
• OUTER JOIN selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table.
• SELF JOIN which is a table joined to itself.

Delete

DELETE FROM tablename
WHERE fieldname =/<>/ ... value

Update

START TRANSACTION;

UPDATE tablename
SET fieldname = value
WHERE fieldname = value;

ROLLBACK work;

COMMIT work;

If some error occurs, we can Rollback work but it needs Start transaction. Once we are sure we can commit

Insert

INSERT INTO tablename (fieldname, fieldname, fieldname)
VALUES (value, value, value);

Select

SELECT */fieldname ...
FROM tablename
WHERE fieldname =/!=/<>/>/>=/</<=/in/not in/between/not between/begins with/contains/not contains/ is null/is not null/like/not/like value
ORDER BY fieldname <DESC>...

Import Data

INSERT INTO tablename
(fieldname, fieldname, fieldname)
VALUES
(@fieldname, @fieldname, @fieldname);

Important Format: Standard Data Format (.sdf).

Create Table

CREATE table tablename USING filename

(fieldname fieldtype(length),

fieldname fieldtype(length),

fieldname fieldtype(length));

SQL

Basic SQL:

Character values and strings in single quotes.