Creating and dropping SQL Views | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Say now that we want to retrieve data about each department and their employees, you could use queries like we did in the previos postings or you could simply create a view that behaves like another table. If you are having difficulties grasping the concept, the best way to picture this is that you have two table, instead of coding and executing the complex queries you create a view that sits on top of the two tables. You do not have to do complex calculations anymore. Take a look at this: create view billing_employees as select employeeid, firstname,lastname, departmentname from employee a INNER join department b on a.departmentid = b.departmentid where departmentname = 'Billing';This produces the following results:
Now this is what the billing_employees view will return. Now that the view is created we no longer have to refer to employees and department, all we have to do to get all the records of employees in the billing department is issue the following query: SELECT * FROM billing_employees;which will return the following results. Note again that you do not have to recreate the view everytime you need it. You only create it once and reuse it whenever it is needed.
To delete the view from your database, you use the following command: DROP view billing_employees;This will delete the view, but will not delete the data in the tables from which the view was built. Caution must however be taken when using the drop command with actual tables because data in those tables will actually be deleted. Any views built on deleted tables will not function. |
RELATED ARTICLES:
Creating database tables with SQL
To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows: CREATE table_name ( column_name data_type); The table nam
Database Design
After gathering and analyzing your requirements, and depending on whether or not your application needs a database to store information; database design is the next big thing. This comes even before you start coding the application program. The reason for this is that, the application program
Selecting data from multiple tables
So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information a
SQL SELECT Queries
So, we have created your database, added data into the tables and now what? In real world nobody collects data for the sake of collecting something. It has to be something that they can use at a later time. This section is dedicated to retrieving information stored in a da
Creating your business web site
There are thousands of business websites on the World Wide Web. These websites serves as a means to inform your customers, suppliers, media contacts and other interested parties about your business and its offerings. Thus it is important to deliver essential information in a wa
Database schemas, domains and constraints
There is more to a database than just a collection of tables. In addition to tables, there are structural aspects that maintain the integrity of data stored in databases. This tutorial will cover the structural aspect of any relational database. These include schemas, domains and constraints.