Structured Query Language (SQL) tutorial
When you have finished this chapter, you will
- Understand how SQL is used
- Identify course boundaries
- Understand why databases are useful
- Know how to run SQL queries within a SQL Server database.
- Know how to set up a database
- Be able to open to a specific database
- Be able to create basic tables within your selected database
- Be able to choose appropriate table column types
- Be able to check table structures
What you may need for this chapter:
- Familiarity with the Windows operating system
This chapter’s task
Today you will locate the important aspects of the editor you will use to learn SQL for SQL Server databases.
This chapter then leads you through database selection procedures, as well as commands or procedures to create both databases and their component tables.
What is SQL?
Structured Query Language (SQL) is the scripting language that is used by database developers to interact with relational database management systems (RDBMS). That is, any database that consists of relational tables that are linked in some way can use SQL to
- create and delete the database,
- set up and delete tables,
- enter information and
- retrieve information in a meaningful way.
SQL can be used with any RDBMS such as MySQL, mSQL, PostgresSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres and many others. All the important and common SQL statements are supported by these RDBMS. However, each has its own set of proprietary statements and extensions. SQL has the full support of ANSI (American National Standards Institute), which has laid down rules for the language.
SQL can also be used to manage database users, data storage details and the database server. This is the province of the Database Administrator, and will not be considered further within this course.
SQL is a general query language for several different kinds of databases. The SQL tutorial in this course is has been built around Microsoft SQL Server 2000 using MSDE (Microsoft SQL Server 2000 Desktop Engine, MSDE 2000) . However, I have generally tried to avoid the commands specific to SQL Server in this course.
This course will lead you through SQL syntax for simple operations such as creating and deleting basic databases, tables and queries using one table. Once you have explored these SQL basics you will extend the principles you learnt to develop more sophisticated SQL queries. You will then be shown how to extract information from several related tables using SQL.
This course aims to build a strong foundation in the SQL language. You can then extend this knowledge to the specific RDBMS you plan to use.
Why SQL Server ?
Choosing a database system depends on three main factors; the platform on which you work, your finances and what you want to achieve.
I am including Microsoft’s SQL Server in this course because SQL Server is readily available to all students as the free-to download MSDE (Microsoft SQL Server 2000 Desktop Engine, MSDE 2000) from Microsoft. It is also one of the more common available RDBMS.
You will find that MSDE is the same as SQL Server 2000, except that many Wizards and automation features do not exist. All the abilities of SQL Server to interact with SQL queries are, however, maintained. You will find that working with MSDE will reinforce your understanding of the SQL commands underlying your SQL Server database and enhance your debugging capabilities.
The SQL Server 2005 Express Edition replaces MSDE for SQL Server 2005.
What are databases and why do we need them?
In simplest terms, databases are storehouses of data. You probably have an address book that contains the names of your friends and family, and their email addresses. This is technically a database. You can add, update and delete data from this file. If this addressbook is stored on your computer, you could also write a small program to extract, sort and display data on the basis of some search criterion.
Data storage and retrieval has been known since the earliest recorded history. For example, early civilisations charted the seasons over the years, thereby helping with planning the best times to sow or to migrate. The collection of data is important, but only because we can then access and use the data to extract some Information.