Months later when you look at your queries again we'll talk about the basic query structure involving the two main sequel keywords we'll show you how you can add an alias to a field to give your.
Column headings more sensible titles and finally to save you tearing your hair out we're going to show you a few of the common mistakes people make when writing basic sequel queries so let's get.
Started so here we are in sequel server management studio and we're ready to start writing some new queries before I get stuck into that I'd like to make a.
Few changes to some of the settings of sequel server management studio things that'll hopefully make your query writing life that little bit easier so to do that add to the Tools menu at the.
Guys to read watching on the screen so if I head to the environment section and the fonts and colors option I can simply change the font size by clicking the drop down arrow I'm gonna make it.
Relatively large make it as clear for you guys as possible so there we go and I'm also going to turn on some line numbering as well line numbers appear at the left hand side of your query and.
Help you identify where errors are happening in your code so to do that I'm going to head to the text editor tab and choose all languages and there should be a simple check box for line numbers the.
Last feature I'd like to quickly mention is something called intellisense it's a bit of a silly name some people call it them autocomplete there's a feature that was added to sequel Server 2008 it's a.
Handy thing to have it helps you to complete sentences so you don't have to type in a complete sentence you can select options from a list which appears and by default it's turned on some.
Posts Related:
People I know find it hugely annoying
Can prefer to type out all their code longhand so just in case you did ever once turn it off head to the transact-sql section and.Then look for the intellisense option and if you wanted to disable it you can uncheck this box I just you find it quite handy so I'm going to leave mine turned on and you'll be able to see what.
Effect that has shortly so to accept all those settings simply click OK and now we're finally ready to start writing some queries when you're ready to start writing.
Queries there are two main ways you can do it one is the head to this tempting looking new query button up in the top left hand corner and click on that alternatively you can hold down the ctrl.
Key on the keyboard and press NF for new both techniques will perform exactly the same action you'll end up with a new query window taking up most of your screen and here are the line numbers I.
Was talking about earlier these will continue building up as I typing more text I want to quickly introduce you to the database we're going to be working on for this example so I head over here.
To the object Explorer window and expand the database folder of the server that I'm connected to there's a movies database in here expand the movies database that's a tables folder in here.
And finally a list of all of my tables these are the things we're going to be working on I'm going to start by writing some queries based on the film table so it gives me some data about what films.
Are stored in the database before I start writing the actual query itself it's useful to add comments comments a fantastic idea in programming they give you a hint or a reminder when you come.
Back to look at your code six months
Later on if you remind about what's been going on in your queries or what you intended to do and also for other people looking at your code it helps them as.Well so in sequel you can start a query by typing in two dashes on that same line then whatever I type in next is simply a comment here's a really interesting comment not particularly.
Informative I know but you make your it's much more informative than that in sequel server you can also add multiple line comments so I hit enter to the next line now to start a multi-line comment.
Type in a forward slash and then asterisk and then going to hit enter a couple of times and type in an asterisk and a forward slash and that closes my multi-line comment so anything I write.
Between these two lines and will be a comment so this is something where you can create things like greater by wise owl show our list of films so that might be useful comment about what this query.
Does I'm going to select and delete that top comment a quick note about out one of the new features in sequel Server 2008 something called outlining when you.
Create blocks of code sequel server automatically recognizes that things should go in a block and creates these little collapsible sections this is outlining you can actually turn this.
Feature off if you don't like it I'm gonna leave mine turned on for now I think it's quite useful so finally having added some comments we can finally get into writing our queries.
Properly the most basic type of query you can write in SQL involves two main sequel keywords the first is called select which i've typed there in all caps capital letters are necessary in.
Sequel server it's completely case insensitive but it is good practice to put sequel keywords in all caps you'll spot them because they are also highlighted in blue if I hit enter a.
Couple of times the next main keyword I want to use is the from keyword now the from keyword slightly oddly in sequel you don't necessarily write your code in the order it read back particularly in.
This version of SQL when you want to use these intellisense lists that I was talking about sometimes it makes sense to fill in later parts of the statement before you fill in earlier parts so what.
I'd like to do with this from statement is to refer to a particular table is one called TBL film now normally had I done a couple of extra things if I started typing.
TBL on to look for the word TBL film here it would appear in the list the reason it hasn't appeared yet it was at this point my squee reason related twenty particular databases defaulted to.
The master database of this server so I'm going to backspace TBL films the time being there are two ways to associate your query with a particular database now one is to simply select.
From this drop-down list from one of the databases of the store on the server but choose movies here what will happen when I go back to my from statement and start to write in TBL you.
Now see that it's populated with a list all the tables from that database which is really handy this is why I love this the intellisense list it just makes typing errors so much less common and.
It's also great for lazy people like me who don't like typing in the best of times to type in this highlighted word now I can either press return or enter or I can double click on it with the.
Mouse I'm going to press the tab key actually to type in my highlighted word another way to associate a query with a database if you don't remember to select the database from the list it can be.
More useful to write a statement at the top of the query so before you start selecting records you can tell this query to use a particular database and here again the intellisense list appears.
To show me a list of movies this time I'm going to double click on the word movies to enter that so there we go I've told my career to use movies I'm going to select some field from this.
Particular table now for people who use sequels Server 2005 or earlier you will have this lovely intellisense list so I want to show you a neat little trick news to avoid having to type things in.
I'm going to remove the word TBL film here instead of typing it in at all I'm going to show you the Uniting drag names from the object Explorer so I can simply drag TBL films on the left hand side to.
The right and there we go one slightly different thing that it does is it adds a qualifier to the start of the table name dbl this is also this is known as the schema is not.
Particularly important for the sort of basic queries we're writing but beware that it doesn't make any difference for us whether it's there or not at this early stage so the next job is to start.
Listing some fields you add fields to a query after the Select keyword so I'm going to click under there just now and press tab to end my code run space again if I know.
The names of my fields I can simply start typing them in and that beautiful intellisense feature will appear again to prompt me we're there with what my field names are so I know that all the.
Fields in this table begin with a word film so I can select film name from the list now by pressing enter or tab there we go after the first field that you enter you must use a comma to separate.
Each subsequent field that you add there's a bit of debate about whether you add comments the end of the line and then carry on typing in your field names on the next line like so or whether you.