More on these terms. Talk about CTAS which is create table as select. What does that even mean? Well, if I describe, for example, the employees table in this case, so I can see the structure of the employees table, I can see that I've got EMPID, EMPNAME, and DEPTID. I can, of course, take a look at that by clicking here as well, same deal. I see the column names, the data types, and every other aspect of the structure, the schema of the table. What if I want to create another table exactly like it except without the data? If I do a select star from employees like this and I run this, I get the table with the data in it. What if I said, create table emp2 as, and I want to create a table as, create table as select. See that's CTAS. Create table as select. When I run this, it's going to create a table called emp2, and emp2 will have the exact same data types and the exact same data as employees. Select star from emp2. It's like making a copy of the employees table. There we go. Emp2 is the same as the employees table. The other benefit of CTAS, if you will, I'm going to do a drop, drop table emp2 that means delete the table. Drop table emp2 like this. Here it is. It says it's dropped. If I do a select star from emp2 now is going to give me an error because it doesn't exist. Emp2 is not there. I'm going to do this. I'm going to say create table emp2 as, now this time I'm going to do employees, except I'm going to do where one equals two. That sounds funny, doesn't it? See this is where one equals two? This is a little trick you can employ to basically make emp2 have the structure, the same structure as the employees table but no data. Well, one will never equal two. This statement will return nothing. Isn't that true? There's no time when one will equal two. This is just a little technique you can use to have a emp2, a copy of the structure, without the data. When I run this now, it created the emp2 table. However, emp2 table has nothing in it. Watch. If I do a select star from emp2, I'll have the structure. Look, structure but no data. That's a create table as select, CTAS. You may see this in documents, you may see this in conversations, you may see it in an email. Data dictionary is really referring to the collection of metadata in the database. This is talking about what tables they are, what the description of those tables they are, what's the name of the database, what's the name of the tables, what are the tables use for, perhaps how many rows are in the table, what they're connected to and so on. Metadata essentially in the database. Data model is how the data is related, how the entities or the tables are related to one another. For example in this instance, DEPTS, department ID, is a primary key and an employee's department ID is a foreign key. The department's table is related to the employees' table via the DEPTID, DEPTID. That'll be an example of a data model in this particular instance on how the employees entity table is related to the DEPTS entity table. Entity and table in this case mean same thing. Entity relationship diagram data model. Dynamic SQL. In applications, you really don't have every possible option that you can use to build your SQL statement. Users may choose to search for whatever they want to search for within an application. You have to build the SQL statement dynamically as the user is doing the selection. For example, if I do a select star from. I'm saying, select everything then I'm doing the from. Now the rest may be dynamic. I can specify a table over here. This can be dynamic 2. I can specify columns that I want over here. Then I can have a where clause, and I can have my where clause which can be dynamic as well. This can be dynamic in nature. I can select which columns I want. I can select which tables I want. I can select how I want to filter the data, my where clause, and when I put this all together, I will have a dynamic SQL statement, and then I will run it at the time that it is completed construction within the application. Dynamic SQL statements are really good in applications because you don't really know what columns, what tables, and what filtering options you would need. Dynamic SQL.
