In our first tutorial we learnt how use simple select statements, and how by applying where statements we could tune our result sets to match our needs. We used the freeware ‘Adventure Works’ database, which is great tool for aiding new ones to get to grips with the basics of SQL. But for most people the real brilliance of SQL comes in creating your own databases, and it is this which we will hope to learn through this tutorial. How to create your own database and tables and fill them with data! First of all we must open SQL Server. Now we have a choice. We can either use the Wizard to create our database or use a simple SQL statement. For creating a simple, straight-forward Database, often the SQL statement is the easiest, and certainly quickest way to get the job done. In a new query window simply enter the following statement: CREATE database [insert name] This should give us a confirmation message as shown in figure #1
If we wished to use the wizard we would first right-click on the databases folder in Object Explorer (to the left in figure #1) and from the drop-down menu click on the first option ‘New Database…’ This will open the wizard. In the general tab (the first to open up) we must enter our desired name for the database. For the purpose of this lesson, that is all that we need concern ourselves with, so now we can simply click ‘OK’. The other options are largely for various tuning techniques and will be considered at a much later date.
Once we have created our database we need to populate it with tables. The simplest way to achieve this is through the Wizard. If we use Object Explorer to open up our new database we will see a folder called ‘tables’. If we right click on this the first option is ‘table…’. Clicking on this will open up the Design Wizard in a new query window as can be seen in Figure #2.
The first column in the Wizard is for the column names. You can fill these as you desire, much like you would in a spreadsheet programme. The second column is for the Data Type. Data Types are a selection of different forms in which data can exist, be it for text, numbers, dates or other categories. In the table below you’ll learn of the most basic and common data types, and their uses.
DATA TYPE USE varchar(20) For text. The number in brackets is the maximum length for each row within the column. INT For numerical values. No decimal point. Date For dates. Decimal(4,2) For numerical values, with decimal places. The first number in brackets represents the total available space for numbers (either side of the decimal point), the number on the right is the number of decimal places. So in this example you could have any number up to 99.99.
As we can see above the data types selected are appropriate to what each column will contain. For example the Name columns use the varchar(50) data type. This should be more than enough for any potential customers! The CustomerID column uses the int data type, as the ID will always be a whole number.
The next column in our wizard is the null column. Nulls exist where there is no available data for the row. For example, in our customer table, it might be that one of our customers will not have an email address. If we select ‘allow nulls’ then that will be fine. If however we unticked this box, an error would be raised if we tried to add a customer without data in the email column.
Once you have completed creating your columns then right click on the highlighted bar above the query window (as seen in Figure #2) and choose ‘Save table’. This will now prompt you to choose a name for the table. Don’t worry too much about this as it – like the database name itself – can be changed later. Once saved the table will now appear in the table folder in the Object Explorer as seen in Figurer #2.
Now you have a database, with one or more tables. Next you need some data! Once again this can be done with SQL statements, but for the purpose of keeping things simple I’ll show you the very straight-forward Wizard. Right click on the table you wish to populate, and choose ‘Edit top 200 rows’. This will produce in the query window a simple spreadsheet like table, which you can type your desired data into. This can be seen in Figure #3.
Above is some completely random data. Any likeness to real-life is of cause completely coincidental! Once you have filled the data to your liking then simply close out of the Wizard and you’ll find you have a database full of data ready to be used. Why not try some simple SQL statements, like those we learnt in the first tutorial, and see how you get on with your very own newly created database!
As ever if you have any questions or queries please feel free to drop me a link at firstname.lastname@example.org . I’ll be back soon with more tutorials, helping you continue advancing your SQL knowledge.