SQL VIEWS + Complex Queries, Cross Joins, Unions, and more! |¦| SQL Tutorial


I have dissected many hard drives, and let
me tell you – databases can be a sticky and complicated mess! The number of tables… the unusual column
names… the linking of tables. It can feel overwhelming! What’s an engineer to do!? Well, do I have a solution for you. VIEWS… These virtual tables make it easy to search your database and avoid those super-long queries. It probably sounds too good to be true, right! Well, it’s not… It’s TRUE. It is! … It is! It is! It is! It is!! The date? In the future. The place? Mars… The mission? Learn about views. To start, suppose we have a table called “Martian
Confidential”. This table contains information on every person
who is living on the planet. Let’s take a look at the data in the “Martian
Confidential” table… Two of its columns contain sensitive information. The salary in Solar credits should not be
available to everyone. And the dna_id is extremely sensitive. Unauthorized cloning is a growing problem
in the remote colonies. How do we give engineers access to SOME of
this data, but not these two columns? By creating a view. The first step in making a view is to write
a query returning precisely the rows and columns you want in your view. We have been instructed that it is OK to share
all data except the salary and dna_id columns. Execute… To create a view from this data, insert a
line above the query. Write “CREATE VIEW” and give your view
a name. We will call this view “Martian public”. Then write AS… That’s all there is to it! Run this query… The view is created. You can now treat “Martian_public” as
a table. A quick select shows that the confidential
data is not present. And look what happens if you try to select
the confidential data… You would never know it was there… That data is now a rumor, recognizable only
as deja vu and dismissed just as quickly. This is an important benefit of views: Security. By giving engineers access to views instead
of all the underlying tables, you control the data they can see. Let us introduce another table to our database:
“visitor”. This table holds a list of all people currently
visiting Mars. Here is our task: create a view that contains
a list of all people on Mars. This view should contain their first name,
last name, a unique ID, and their status: Martian or Visitor. We start by writing the query to generate this list. Our first SELECT gives us the information
we need from the Martian table. Each person in this table is a “Martian”,
so in the SELECT clause include a “Martian” string and call the column containing this
string “status.” Execute… This gets us part way there. We also want the names, status, and IDs of
the visitors, too. One way to combine two queries is with the
“UNION” command. This will combine the results of two SELECT
queries into one result set. You only need to be sure to line up columns
of the same type in your two SELECT queries. The two SELECTS need to have the same number
of columns… and the types must be the same. Execute… We are closer, but one problem remains. The id. Some visitors and Martians have the same numeric ID. One solution is to prefix each Martian ID
with the letter “m” and call this column simply “id”. We do this using the CONCAT function. And in the visitor SELECT query, we will put
a “v” in front of each id. Now execute once more… We now have a list of ALL people on the Red
Planet. Their names… status… and a unique ID. We can now turn this into a VIEW. At the top insert “CREATE VIEW” with the view name. We will call this “people_on_mars”. End it with “AS” and execute. Engineers can now easily query the list of
people on Mars by using this view. Execute… This is another benefit of views: Simplicity. Instead of requiring people to query two tables
or know how to use UNION, they can simply write a SELECT using this view. For our next example, we will use three tables
in the Martian database: Base… Inventory… and
Supply The “Base” table holds information on
all the different habitats on Mars. The “Supply” table lists all items available
in the central Martian distribution center. And the “Inventory” shows what supply
items are available at each base, and in what quantity. Here are the data for the three tables. Look closely at the inventory data. If a base does not have a supply item in stock,
it is not listed with a quantity of zero… Instead, it is not in the table at all! One might debate this design choice. Regardless, we have to play the cards we are dealt. Especially if we want to be ready when the
Olympus Mons Spa & Casino opens later this sol. Our task is to create a VIEW called “base_storage” showing the quantity of all supply items in stock at each base. As a first step towards building the query,
SELECT the base_id… supply_id… and supply_name for all possible combinations of Base and supply item. This is done with a “CROSS JOIN”. This type of join performs a cross product
between the two tables. That is, it connects each row from the first
table with each row in the second table. This gives us all possible base/supply combinations. Since there are 5 bases and 10 supply items,
there should be 50 rows in this cross join… And there are. What remains is the quantity of each supply
available at a given base. We can obtain this value with a subquery. In the SELECT clause, embed a SELECT to get
the quantity from the inventory for this row’s base_id and supply_id. Execute… Unfortunately, some of the values are null. We do not want engineers to be concerned about
nulls, so we will replace all nulls with zero. We can do this with the “COALESCE” function. This function returns the first non-null value
it encounters. If the inventory table does not contain the
quantity, it returns null. In this case, the quantity is actually zero. Execute… Grumble. The column name is called “coalesce”. Not a terribly descriptive name. So we alias the output of the “coalesce”
function as “quantity.” Double execute… Now we are talking… This is the query we are looking for. All that remains is to turn this into a “view”. Return to the top and write the “CREATE VIEW” clause. As instructed, we will call this view “base_storage”. Run… Success. Let us now admire this view… And what a wonderful view this is… Our users can now query this view while the
database designers continue to debate. Thank you for VIEWING our video on VIEWs. I hope you learned something NEW. If not, maybe it was a good reVIEW. It was, after all, a nice overVIEW… for
YOU… Subscribe……

100 Comments

  1. Love this video series! Could you do a tutorial on ER diagrams and how to transform them to actual database? Can't wait to learn what's next!

  2. Most of this I knew…in terms of the query…but I never knew how to create a view from it! Great video, Great channel and I will continue subscribing 👍

  3. I am in the middle of the video, but I am asking how to prevent access to database tables? if views can hide sensitive columns but the users still has access to the table? any answer?

  4. Cyborg girl! I don't remember when I 1st saw you … probably the Hello World vid
    So consistent, in character & funny 🙂
    You're doing an amazing job … with the character & editing etc.

    Only programmers can feel this out of time & place feeling
    maybe we are watching is 3000 years into the future …

    Feels sad though, something is missing
    You're not connecting with your subscribers/viewers on a personal level!
    What's your story? etc. etc.
    Who's behind this channel etc.?

    310K Subscribers! Not a small number yet no celebrations on milestones etc.
    It's almost if you are a real Cyborg which feels sad …

  5. I hadn't ever seen a video on Youtube with "more than 200" likes and "0" dislikes !  This is in itself an achievement !

  6. I use “isnull()” instead of “coalesce()”. Is one better than the other, or is the latter more compatible between SQL server types?

  7. here's a suggestion for you. Cover when union queries are better option than multiple or subqueries. I've seen programmer mis-use both and cleaned up lots of really bad SQL. More advanced join topic, self-join queries and why they can blow up the database server consuming 100% of the CPU.

  8. 6:00 USB 6.0 cable? I'm glad that in the future, the USB Implementers Forum will come back to it's senses about naming their standards 😜

  9. Examples from the video:
    https://www.db-fiddle.com/f/tDULuaaBegoN3kSw5fgbb3/8

    Thanks for the tutorial!
    The production value of these videos are great. SQL tutorials have never been so entertaining.

  10. I got one question, when defining View base_storage… how to make statement like WHERE base_id = b.base_id? From me, these two looks the same from tables base and supply already, why need to define it again? If not with the WHERE statement, what might happen?

  11. Please make videos on Django and React. You have a unique way of teaching and putting information in our mind.

  12. when you query a view will the execution always reference every table related to the view? For example let's say table A and B create view AB and table C and D create view CD and we created view ABCD and I queried against view ABCD, will all tables A-D be referenced in the execution of view ABCD?

  13. omg i loved this!!! i am a huge science fiction nerd and i instantly wanted to learn about databases that included stuff about mars. this was fun!

  14. No SQL interface that I have used has such nifty sound effects (ie, sci-fi/space-aged). In fact, the interfaces that I have used don't make any sounds at all. Very dull.

  15. Do you love Socratica Programming videos? We have a Kickstarter to make 20 Advanced Python Lessons. Support what you love! http://bit.ly/PythonKickstarter

  16. Mam, plz make complete sql server tutorials from basic to advance level and make it a playlist of sql tutorial. You r teaching really very very well . Keep it up

  17. It's official! The Socratica Python Kickstarter was a success! Thank you to all of our supporters. Because of you, many more Python videos coming soon!! 💜🦉

  18. Hi this tutorial was too good . Please posted video for trigger nd procedure nd function nd event scheduler where we want use this four ?? Pls explain more

  19. hi, if you can create a playlist with the order I should follow to learn all the SQL commands and databases; it would be the best SQL course I've seen online so far and would like to recommend your videos. I just don't know what to learn first whether vies, indexes, etc, etc.

    thanks 🙂

  20. Thanks for viewing our video on View
    I hope you learn something New
    If not, may b it was a good review
    It was, after all a nice overview
    for me … I subscribed !!!

    Your video is a Teacher of all times !!!

  21. Missed out another great benefit of views – a kind of "DRY principle". We have a massive student records database at our college with hundreds of tables. When it was first implemented we built a whole load of views on top of the tables to standardise things, such as determining the enrolment start and end dates for a learner. We have built pretty much every report and anything else based on these views instead of the base tables – and that could be thousands of reports. When the information systems team then decide to change how we work out the end date of an enrolment, instead of having to go into thousands of reports to update them, we just change it in the view and all the reports are updated in one go.

  22. Wow, THIS is how courses should be done. Not just plain information, but contextualized with relateable scenarios and jokes, and adequate soundrack + sound fx. I'm in love with this series!

  23. You have unique and excellent method of teaching. I have never seen such kind of educational channel on youtube.
    Keep it up guys.👍👍

  24. 5:10. Is a Mars Bar sold on Mars still a Mars Bar or does it become a Mar’s bar due to copyright infringement? Will it still taste the same?

  25. 6:23. I am a old user rediscovering SQL and I am wondering why you created a subquery under the select clause, instead of making a nested right under your existing query.

Leave a Reply

Your email address will not be published. Required fields are marked *