Create SQL View

 

This command is used to build views in a database. You can design a view using a single table or several tables.

The basic Syntax for creating VIEW:

CREATE VIEW view_name AS

SELECT column1, column2, column3...

FROM table_name

WHERE [condition];

 

The name of the VIEW you want to construct is view_name. Also, the WHERE conditions might be omitted. For the records to be included in the VIEW, these conditions must be met.

Creating a view from a single Table:

Query

CREATE VIEW Customer_view AS

SELECT Customer_id, Name, Address

FROM  Customer_Details

WHERE Address = "Miami";

 

As you can see, this CREATE VIEW statement uses the results of the previous SELECT statement to construct a virtual table. To view the output, you can now query the SQL VIEW as follows:

 

SELECT * FROM Customer_view;

Creating View from Multiple Tables

Query

CREATE VIEW Order_view AS

SELECT Customer_Details.Name, Customer_Details.Address, Customer_Order.Product

FROM Customer_Details, Customer_Order

WHERE Customer_Details.Name = Customer_Order.Name;

 

As you can see, this CREATE VIEW statement uses the results of the previous SELECT statement to construct a virtual table. To view the output, you can now query the SQL VIEW as follows:

 

SELECT * FROM Order_view;

Updating The SQL View

It is also possible to make changes to the SQL view that has been established. The SQL VIEW allows us to perform the following actions.

 

●     However, not all views can be updated. If the following criteria are met, then a SQL view can be updated.

●     Only one table is used to define the view.

●     An aggregate function field should not be present in the view.

●     Group by, HAVING, or Distinct clauses are not allowed in the definition of the view.

●     No nested queries should be used to construct the view.

●     A constant, string, or value expression cannot be used in the view's output fields.

●     The view that you want to update depending on another view should be updatable.

Updating a SQL View

CREATE OR REPLACE VIEW statements can be used to change the SQL view.

Syntax

CREATE OR REPLACE VIEW view_name AS

SELECT column1,column2,..

FROM table_name

WHERE condition;

Query

CREATE OR REPLACE VIEW Customer_view AS

SELECT Customer_id, Name, Address, Age

FROM  Customer_Details

WHERE Address = "Miami";

 

A virtual table would be created based on the results of the SELECT statement in the above CREATE OR REPLACE VIEW statement. To view the output, you can now query the SQL VIEW as follows:

 

SELECT * FROM Customer_view;

 

Drop the SQL View

The DROP VIEW statement can be used to remove a view that has been created. As long as we don't have any previously built views, this is necessary.

Syntax

DROP VIEW view_name;

In this case, view name denotes the name of the VIEW you wish to remove.

 

If you wish to remove the created view Customer view, the query is:

Query

DROP VIEW Customer_view;

Data Analysis using SQL: Understanding the Advantages

●     SQL for Data Analysis is a user-friendly language since it is simple to grasp and master.

●     SQL for Data Analysis is a fast query processor and a powerful tool for retrieving large amounts of data from a variety of databases.

●     As SQL for Data Analysis gives standard documentation to users, it supports exceptional handling.

SQL Clauses:

SQL WHERE Clause:

SELECT column1, column2....columnN FROM

table_EID WHERE CONDITION;

SQL LIKEClause:

SELECT column1, column2....columnN FROM

table_EID WHERE column LIKE 'XXXX%‘

 

SELECT FROM table_EID WHERE column LIKE 'XXXX_'

 

There are two wildcards used in conjunction with the LIKE operator:

1. The percent sign (%)

2. The underscore (_)

SQL TOP Clause:

SELECT TOP number|percent column_EID(s) FROM

table_EID WHERE [condition]

SQL UPDATE Statement:

UPDATE table_EID

SET column1 = value1, column2 = value2

....columnN=valueN

[ WHERE CONDITION ];

SQL DELETE Statement:

• DELETE FROM table_EID WHERE

{CONDITION};

 

• DELETE FROM table_EID

 

• DELETE table_EID

SQL ALTER TABLE Statement:

• ALTER TABLE table_EID

ADD

column_EID {data_type};

 

• ALTER TABLE table_EID

DROP Column

column_EID ;

 

• ALTER TABLE table_EID

ALTER Column

column_EID {data_type};

SQL DROP TABLE Statement:

• DROP TABLE table_EID;

• DROP DATABASE database_EID;

SQL TRUNCATE TABLE Statement :

TRUNCATE TABLE table_EID;

SQL COMMIT Statement:

COMMIT;

SQL ROLLBACK Statement :

ROLLBACK;

SQL BETWEEN Clause:

SELECT column1, column2....columnN FROM table_EID WHERE column_EID BETWEEN val-

1 AND val-2;

SQL IN Clause

SELECT column1, column2....columnN

FROM table_EID

WHERE column_EID IN (Val1, Val2... Valn);

SQL Like Clause

SELECT column1, column2....columnN FROM table_EID WHERE column_EID LIKE {

PATTERN}

SQL COUNT Clause

SELECT COUNT(column_EID) FROM table_EID WHERE CONDITION;

SQL DISTINCT Clause

SELECT DISTINCT (column) FROM table_EID;

SQL ORDER BY Clause

SELECT column1, column2....columnN

FROM table_EID

WHERE CONDITION

ORDER BY column_EID {ASC|DESC};

SQL GROUP BY Clause

SELECT SUM(column_EID)

FROM table_EID

WHERE CONDITION

GROUP BY column_EID;

SQL HAVING Clause

SELECT SUM(column_EID)

FROM table_EID

WHERE CONDITION GROUP BY column_EID

HAVING (arithmetic function condition);

 

String functions

They are used to perform an operation on input strings and return an output string.

Following are the string functions defined in SQL:

1.    SUBSTRING(): This function is used to find an alphabet from the mentioned size and the given string.
Syntax: SELECT SUBSTRING('GeeksForGeeks.org', 9, 1);

Output: ‘G’

2.    REPLACE(): This function is used to cut the given string by removing the given substring.
Syntax: REPLACE('123geeks123', '123');

Output: geeks

3.    REVERSE(): This function is used to reverse a string.
Syntax: SELECT REVERSE('geeksforgeeks.org');

Output: ‘gro.skeegrofskeeg’

4.    STUFF(): The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.

Syntax: SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');

Output: SQL Tutorial is fun!