In: Categories » Computers and technology » Databases » Introduction to SQL
| SQL is an acronym for Structured Query Language and is the standard language for interaction with databases. SQL is both an ISO (International Organization for Standardization) and ANSI (American National Standards Institute) standard, but many database packages contain proprietary extensions that are not part of the standard. As a matter of fact, there are not many database servers that would have 100% support of the SQL standard. Let's first look at some simple interactions that we can perform using the SQL language. Let's say, for example, that we had a table called user_table, which held information on players in a Java game. The information is a unique identification number followed by the player's username, password, and e-mail address. Here is a graphical representation of the table in the database with some sample data in it: Assuming all the data had been previously entered into the database and we wanted to get the password of the player who had the username of george, we would use the following SQL statement to retrieve his password: SELECT password FROM user_table WHERE username = 'george'; As you can see from the previous statement, the great thing about SQL is its similarities to the English language. By that I mean it is very easy to read and understand what the statement is trying to do. In this example, we are "SELECT"ing, which means retrieving, the password field "FROM" the user_table "WHERE" the username is equal to the string "george." When this query is executed, the database would then return the password (which is geo321) from the user with the username george. So what would happen if there were two users with the username george? Well, the database would return a recordset (there is the possibility of getting an actual array to the programming language, but it must first be retrieved row-by-row from the database via the recordset object) containing the passwords for the records that it found. Unless you specifically want users to have the same usernames, the best way around this would be to check for duplicate usernames when you are inputting the users' data into your game or defining the username as a primary key. Let's look at another simple example for listing all the high scores from a game in a descending order (i.e., start by getting the highest score, then the next highest, etc.). Let's assume our user_table also had a field called highscore that recorded the players' high score in a game. The default ordering is in ascending order; therefore the database would retrieve the lowest score first if we selected all the high scores from the database. The solution to this is to use the DESC keyword, which tells the database to order the results in descending order (i.e., highest first). Here is the SQL statement that we would require to do this: SELECT username,highscore FROM user_table ORDER BY highscore DESC
This previous statement would retrieve the username and high score from the user_table ordered by the highscore field in descending order. Note how we can retrieve more than one field in a single statement, separating fields you wish to retrieve with commas. We can also use a *, which is known as a wildcard and simply tells the database to return all the fields from the table rather than a defined number of fields, like in the first example where we just retrieved the password field. Let's now look at one final example of the more powerful features of SQL and databases. This is the use of regular expressions. A regular expression in simple terms is a way to express to SQL a specific pattern of text to look for in the fields in a table. Here is an example statement that would retrieve all the usernames from the user_table that begin with the letter G: SELECT username FROM user_table WHERE username LIKE 'G%'; Notice that all we are really doing differently here is using the LIKE keyword instead of the equals sign. Also, the % acts as a wildcard when using the LIKE keyword. Therefore, the statement will select (retrieve) any usernames that start with G, as the names that start with G will fit the regular expression 'G%'. If we wished to select any names that just contained the letter G anywhere in the string, we would use the following statement: SELECT username FROM user_table WHERE username LIKE "%G%"; As you can see, all we have changed is the regular expression by adding another wildcard (%) before the G, allowing zero or more different characters both before and after the G when the database is searching.
|
legal disclaimer
1) Our website is not responsible for the information contained by this article as well for any and all copyright infringements by authors and writers. E-articles is a free information resource. If you suspect this article for any copyright infringements, please read the Terms of service and contact us to investigate the problem.
2) The E-articles directory team is not responsible for inaccuracies, falsehoods, or any other types of misinformation this tutorial may contain and will not be liable for any loss or damage suffered by a user through the user's reliance on the information gained here. Please read the Terms of service
Useful tools and features
If you like this article (tutorial), please link to it from your web page using the information above. Linking to this page, this is the only way to help us improve our service, the same time providing your visitors with a way to improve their online experience.
related articles
SQL Server 2000 has several components that support the import and export of data. Data Transformation Services (DTS) is used to import and export data between like OLE DB and ODBC sources. A DTS package is defined that specifies the source and target data sources which can then be executed on an ad hoc basis or scheduled to run at predefined times. DTS packages can also specify a query as the source of the data which allows packages to transform data by performing an action like running a query that ret...
2. Basic Terms of Structured Query Language (SQL)
The first questions to ask are what is SQL and how do you use it with databases? SQL has three main roles: 1. Creating a database and defining its structure 2. Querying the database to obtain the data necessary to answer questions 3. Controlling database security Defining database structure includes creating new database tables and fields, setting up rules for data entry, and so on, which is expressed by a SQL sublanguage called Data Control Language (DCL). The next section discusses querying ...
3. Managing and Monitoring SQL Server 2000 Security
Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server authentication. Windows authentication allows the SQL server authentication to be integrated directly with security on a Windows 2000/NT system. Authentication will depend on the security mode that SQL Server 2000 runs under, Windows Authentication mode, or Mixed Mode. If you are running SQL Server 2000, you will need to configure the SQL server to run in mixed mode to allow the non-Win...
4. Managing Monitoring and Troubleshooting SQL Server 2000
SQL Server Agent Jobs SQL Server Agent is a job scheduling agent that aids in the management of a SQL server. SQL Server Agent can be started when the operating system starts and is configured through the SQL Service Manager or Enterprise Manager. The SQL Server Agent is responsible for the execution of jobs. Jobs are made up of multiple steps that have instructions on what to do depending on the success or failure of each step in the job. Jobs are assigned to an operator, a user who is responsible for tha...
5. Managing Monitoring and Troubleshooting SQL Server 2000 Databases
Troubleshooting SQL Databases is an operation that supposes multiple actions. They are described below. Optimizing Database Performance Database performance is an important part of administering a SQL Server 2000 environment. There are many ways that performance can be increased including indexing, locking and recompiling. Indexing Establishing indexes can dramatically increase query performance, but can also adversely affect data modification performance. Any changes to an...
6. Tips for Designing a Better SQL Database
Keeping the following subjects in mind helps ensure that your database design and updates go smoothly. Don’t design a database that copes with the norm. Tempting as it is to design a database that covers most situations, doing so is dangerous. The unexpected happens just when you least expect it, so make sure you design your database to cover all situations that could arise, or at least ensure that it can cope with the unusual situations. Even if a client tells you not to worry about the possibility of two people attem...
7. Why and When to Use a Database
When there are a huge number of alternative ways to store data, why should you trouble yourself creating a database? What advantages does a database hold? The main advantage is fast and efficient data retrieval. A database helps you to organize your data in alogical manner. Database management systems are fine-tuned to rapidly retrieve the data you want in the way you want it. Databases also enable you to break data into specific parts. Retrieving data from a database is called querying. You’ll often see the term SQL query, which brief...
8. Creating SQL Server 2000 Databases
Attaching and Detaching Databases Detaching/attaching is used in moving a database between computers or physical disks. When a database is detached, the links from the server to the data files and transaction logs are removed. When you attach a database, you re-establish those links. When you detach and re-attach, you eliminate the need to restore the database backup manually. When a database is reattached, all files that have changed location must be specified. Filegroups Filegroups ar...
9. Developing Notification Services Applications in SQL server 2005
The process for developing Notification Services applications begins with defining the rules that govern how the application works. Next, you must compile the application. Then you need to construct an interface that allows the user to add subscriptions to the application. Finally, you’ll need to add any custom components that may be needed by the application. Let’s look at each of these steps in more detail. Defining the Application The Notification Services developer uses a combination of XML...