Views Procedures

By the end of this lesson you'll be able to package SQL for reuse: views let you save a query under a name and treat it like a table, and stored procedures let you bundle logic the database can run on command. These are the tools that turn a pile of queries into a clean, shareable database.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every example in this lesson runs against this familiar products table. The views and procedures you build all read from it, so keep its rows in mind.

A view is a saved, named query . You write a SELECT once, give it a name, and from then on you can SELECT from that name as if it were a real table. A view stores no data of its own — it simply re-runs its saved query every time you use it, so it's always in sync with the underlying tables.

A view is a saved search . Think of "Recently added, under $20" saved on a shopping site: you don't keep a copy of those products — the site re-runs the search each time you open it. A view is that saved search, living inside your database.

You create one with CREATE VIEW name AS followed by any SELECT . Here we save the "Electronics only" query as a view called electronics :

Notice the view returns only the three Electronics rows and only the columns you selected. The WHERE category = 'Electronics' rule now lives inside the view, so you never have to type it again.

Views earn their keep in three ways. A view behaves like a table, so you can filter and sort it further — reusing the logic baked inside it:

Views also simplify nightmare queries (hide a 10-table JOIN behind one friendly name) and provide security — you can grant access to a view that exposes only safe columns while the sensitive ones stay hidden:

Fill in the two blanks to create an electronics view and query it. The expected result is in the comments so you can check yourself.

Most views are used for reading, but a simple view — one table, no GROUP BY , DISTINCT , or aggregate functions — is often updatable : an INSERT , UPDATE , or DELETE on the view flows straight through to the real table. As soon as a view summarises or combines rows it becomes read-only , because the database can no longer tell which underlying row your change should affect.

Rule of thumb: if you could point at one exact source row for every row the view shows, it can usually be updated. If the view groups, counts, or joins many rows into one, treat it as read-only.

Change a view's definition with CREATE OR REPLACE VIEW , and remove it with DROP VIEW . Because a view holds no data, dropping it never touches the table it reads from.

A stored procedure is a named block of SQL saved in the database that you run on demand with CALL . Unlike a view (which is always a single SELECT ), a procedure can take parameters , run several statements, and contain logic. You write it once; anyone can run it by name without knowing what's inside.

If a view is a saved search, a procedure is a reusable recipe . You hand it ingredients (the parameters — say, a category name), it follows fixed steps, and gives you the finished dish. The same recipe, different inputs, every time.

This MySQL procedure takes one input parameter and returns the matching products. IN marks a value you pass in; CALL runs it:

Procedures can also return a value through an OUT parameter. Here the procedure counts the products in a category and writes that number into how_many , which you read back afterwards:

Complete the procedure body so cheaper_than returns every product below the price you pass in. Two blanks — the table and the comparison.

Views are written almost identically everywhere, but procedure syntax differs by database engine . MySQL uses DELIMITER and BEGIN ... END ; PostgreSQL wraps the body in a $ ... $ block with a LANGUAGE ; SQL Server uses AS and runs procedures with EXEC . The idea — a named, parameterised block you call by name — is the same; only the wrapping changes.

Not by itself — a standard view re-runs its query each time, so it's about the same speed as writing that query out. It saves your effort, not the database's. (A separate feature, the materialized view , does cache results, but it can go stale.)

Q: What's the difference between a view and a stored procedure?

A view is always a single SELECT you query like a table. A procedure is a block of one or more statements with parameters that you CALL to perform an action. Use a view to reshape data for reading; use a procedure to run logic.

Q: Why won't my procedure run — it worked in another tool?

Almost certainly an engine mismatch. MySQL, PostgreSQL, and SQL Server each have different procedure syntax ( DELIMITER vs $ vs AS / EXEC ). Make sure your playground is set to the same engine the example was written for.

No. A view holds no data of its own, so DROP VIEW only removes the saved query. The tables it read from are completely unaffected.

Put it together — a brief, a blank canvas, and the expected result in the comments. Write the view and the query, then copy them into a playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary