Introduces data types, explaining their importance as well as how to identify the data type of any given field
- [Instructor] Now, a little bit about datatypes. It might be obvious to you whether something is a number, date, or text, but it's not obvious to a database. Whoever sets up the database has to tell it explicitly for each field. This is true of all databases apart from MS Access. You can't create a field in fact without giving it a datatype. There are more than 30 datatypes, but they can be grouped into number, text, and date. And actually, you may have had similar problems in Excel without realizing it.
Let's say you've got some numeric data. They're obviously numeric to the naked eye but Excel stubbornly refuses to accept that it's anything other than text and you find that your numbers are lying to the left. You have to go and reformat the cells and run various tricks to make Excel accept that the data are actually numeric. Well databases are a bit similar. If you store a number as text, when you try to pull the number back out of a database, you can't work with it as you would expect to. Let's say you stored the number 66 in a cost column but you've defined the cost column as having a datatype of text.
Now when you try and run an average on that field, the database throws an error. Hang on, it says, you can't average text. Let's look again at the actor table. We seem to have three basic datatypes here. We have something numeric, two that appear to be some kind of text, and then this final last update column is some sort of date-time combination. And I say seems because you can't always tell by looking at the data. There are far too many datatypes for you to be able to tell just by looking at the column generally.
There are nearly 20 datatypes that are numeric, more than ten for text, and five for date and time and that is just my SQL. If you have access to the database itself, it's easy to work out which datatypes you're using. You can just look at the field definitions. In phpMyAdmin they're under the Structure tab. Now here are our field names listed, vertically this time instead of horizontally, and next to them we have a type column which is datatype. And we can see it's smallint, varchar twice, and then timestamp.
smallint is short for small integer and integer just means whole number. Now there are various other integer type datatypes. There's tinyint, for example, and there's bigint. Depending on which version of SQL you're using, there may be more than the three and this points us to the answer of the question, Why are there so many? Database offers a range of datatypes but some of them are far more memory hungry than others. So, if you have a bigint datatype field and you store just the number zero in it, it's going to take up more memory than if you have a smallint datatype with the number zero in it, even though they're storing the same amount of data.
So the name of the game in database design is to choose the smallest memory option that meets your needs for a field. Now if you don't have access to a database, you can often find the information you need in a different way. In my SQL and oracle, you can use a very handy keyword called DESCRIBE. And you see it returns us data very similar to what we saw in the Structure tab. Now unfortunately that keyword doesn't work for Microsoft or PostgreSQL.
But generally speaking, there's another method for them. Now let me show you something about the databases that we have listed. I've got one here called information schema and another called performance schema. And both of these have been created automatically by my SQL and I believe they're created automatically in Microsoft and Postgre as well but not in Oracle. These other databases that are listed here with the shorter names are databases that I have set up so you won't see those in your system but you may well see information and performance schema.
Now the information schema database automatically contains descriptions of your data. So every time you create a new table or a new field, a new row gets entered into a table somewhere in information schema. Performance schema, by contrast, automatically contains details about how fast or otherwise your database is performing its actions. And in some database software, you have to actually switch performance schema on before it will contain anything at all.
And let's have a quick look at information schema. You can see there's all these tables here and as I say, these all generated automatically. But here we have one called tables. And here's another one that's called columns and columns here should be understood to be fields. So without you needing to do anything at all, this table contains descriptions of all the fields that you're using, as well as some fields that you're not going to be using at all.
And the way to access it is to say, SELECT * FROM `COLUMNS` WHERE table_name = 'actor'. And this time we're using actor as a string because we want to return rows where actor is in this column here. It's not a table name in this query, which is why we're using single quotes and not back-ticks. And there we can see, we've got four rows returned. There are our four fields and here's our datatype. So even if you can't see your database, it might be useful to have a root-about and understand which datatypes you're dealing with in your fields.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries