Single Table Queries Part 1

Video Transcription

Hello everyone, In this video series, we’re going to be taking a look at some simple queries. Now, in this, in this video series, we’ll cover quite a few things, everything you need to know about a full simple query, including how to how to select columns, pick tables out the table that you want to actually pull data from, and filtering those results from those tables. Now, in a lot of programming languages, like Python, for example, everything is an object. And very similar idea here with databases, and particularly with SQL Server, almost every single thing inside of it is considered an object. So each of your tables there, we’ll be talking about views, procedures, and there’s even some functions, and all sorts of other things that we’ll be covering in this course.

But everything is treated as an object. But those objects are contained in what we call a schema. So in if you’re talking about programming languages, like for example, C sharp, your schemas considered your namespace, so everything lives inside of this, you can kind of almost treat this as like a folder, right. And inside, on your computer, you probably have your courses organized or your your information and documents for each of your courses, each of those courses underneath a single folder. And inside of those, you have individual documents and things like that. And so very similar idea of what we have with databases, but instead of calling it a folder, we’re going to refer to that as a schema. Now, a schema itself cannot have other schemas inside of it. So if we’re talking about folders, it’s a folder that can’t have any sub folders. But it is going to contain all of the objects associated with the database. So all of your tables, any stored procedures, and queries that we have saved out, and everything that is associated with it.

So how do we actually refer to the schema, very similar to how we refer to classes and objects in your programming language. So if you’re talking about Python, it’s the package name, dot and then the item or member inside of that particular Python package like a class or a function, and similar idea of what you see in Java. So in this case, we have and will, in the examples that I’ll show here and a little bit, we have a sale schema. And in that sales schema, we have a series of tables that we’re going to work with. So here I am selecting everything, so select star, and we’ll talk about this query here in a minute, but select everything from the sales dot orders table. So sales being the schema and orders being the actual table name. So what do we need to actually have a table. So we, in our early videos, we created a very simple table, we talked about what a table actually contains. So we have attributes which are columns, and then we have rows as well.

So each row representing a actual Single, single record inside of that table. But a table itself is going to have a table must belong to a schema, you can’t have just this orphan table out there, that doesn’t belong to anything. So a table must belong to a schema, which is essentially going to break down to being your database, right? A table must also have at least one column, right? So we can’t have a, we can have a table with no records in it. So no rows, but we cannot have a table that has no columns, because otherwise we have nothing to actually define the data that’s actually being stored there. So as far as the column goes, each column must have a unique name. And that only has to be unique within the actual table itself. So if I have Table A, which has a phone number or email for example, Table A can have email and Table B can also have email. But within each table, we cannot have two columns that are the same name, because otherwise we cannot uniquely identify a particular attribute for any record. So must be unique name. We must also define a data type.

So this is particularly with SQL. Each attribute must have a defined data type. So no change whatsoever. If you’re coming at this from the Java, the Java point of view, but if you’re coming from Python, unfortunately, we do have to actually define the data types here for for each of our columns, and we must also define whether it is null or not null. And the null ability at the null ability modifier here is going to indicate to SQL Server or your database whether or not this column is optional. Hey, so no allows records to be inserted into this table without that column present. So if I have a record, and the phone number is optional, for this particular table, I can insert a record about let’s say, a person. And that person doesn’t have to have a phone number in order to be inserted into this table. Not Null is going to make that column required for all records that exist inside of that table. Now all of this is actually specifically for SQL. We will talk about way later and into the course, I will talk about something called no SQL, which has a little bit more relaxed requirements as far as what the tables are defined, and the types and things like that, which is a little bit more related to what you would expect from kind of like the Pythonic and the Python environments. But these are the minimum requirements that we need in order to actually have a table be a table. So what about a query, right, we’ve talked about and executed a few queries before.

So now we’re really going to kind of dive into what a query is, and kind of define all the individual parts. So SQL itself is a declarative language, meaning that we are going to define what we want not how to get it, which is kind of a backwards thing of what we actually are used to, right. So the the data already that the SQL Server itself, right, the seek the server management system, their job or its job is only to or it SQL Servers job or the or the databases job is going to be responsible for knowing how to retrieve the data, right? The data is stored on the computer somewhere, all your SQL knows as you are connected to, to that particular database, and the database is going to handle retrieving the actual data, all of the SQL is going to actually define or the query is going to define is what you actually want out of that. So what data do you want? Not how do you actually retrieve that data, which is a little bit different compared to how you are working with Python or Java, right? If you are, for example, wanting to read in a text file, and then write out contents to it, you actually have to tell the computer where that file is, you have to actually physically tell you have to tell the language, how to open that file, how to read that file, and then how to write to that file. Which is completely different in most database languages especially. And what we’re working with here is that we are not having to tell the database, how to write it, where and where to actually store it and everything like that. The database itself knows how to actually handle all those operations, which makes our lives as database database engineers to make our lives significantly easier.

SQL itself is a set based language, meaning that for things like C sharp, Java, Python, it’s not procedural or really like any other language that you’ve actually worked with. Really, order itself is not always super important, although we will talk about order on how things work with the actual SQL language, because the SQL statements are consumed in a specific order, but you can actually have them in whatever order you’d like. So very rarely does order actually matter versus a actual program written in Java, C sharp, Python, whatever language really, order absolutely matters, right? It’s top down. Or if you’re looking to a function, it works on line one, line two line three, and so on. But SQL is quite a little bit different than that. And when we’re working with any kind of data and our database, particularly with SQL, everything is going to be dealing with sets, right, a set of data, meaning things are unique. And we have, we can have duplicates in that. But we’ll be diving into a little bit more about what that set is going to kind of mean here in here in a moment. So common problems. So as we get started with working with SQL and SQL Server, there are some common pitfalls that some students or or, or people who are new to writing SQL fall into.

So one is that you disregard one of these properties, right? The fact that sequel is set oriented, and declarative, so we are trying to reverse you’re, you’re not completely reversed on us as far as how we’re actually writing SQL code. But it’s not line by line by line, right. And this becomes significantly more important, as we add more and more to our queries, as our queries get more and more complex. If you’re thinking about it in a as a procedural language, from you know, top down, then it’s not necessarily going to work out, the logic won’t actually end up executing as you expect it to, and you’ll end up with a lot of different results or results that you don’t expect. So Okay. Oops, sorry, one second to those who are recording. Okay, so, in this class will be I will be referring to or you’ll see these acronyms, quite often. DDL and DML. Okay. So DML, which is what we’ll be working with, for the majority of this course, is referred as stands for data manipulation language. Okay. So with DML, this is all of the query statements that you’re going to use to retrieve data or modified data. So inserting data into your tables, updating data, or updating records that already exist, deleting them, merging them, and just flat out retrieving them like select. On the other hand, we also have data definition language, or DDL. And this deals with primarily creating data. And not just necessarily creating data, but creating databases and creating tables, views and stored procedures and things like that also fall into this category. But we won’t get to those particular parts until later in the course. This first section of the course will be focusing primarily on just the SQL for database manipulation, or data manipulation.