Basic SELECT Statements
You can create queries to retrieve information from your database by using the SQL Query Tool in the Enterprise Manager, ISQL/w, ISQL, MSQuery, other tools, and third-party utilities. In this section, you will look at using the SELECT statement to retrieve rows and columns from tables in your database. The SELECT statement consists of three basic components: SELECT, FROM, and WHERE. The basic syntax follows:
SELECT column_list FROM table_list WHERE search_criteria
The SELECT portion of the statement specifies the columns you want to retrieve. The FROM clause specifies the table(s) from which the columns are to be retrieved. The WHERE clause can be used to limit the amount of data returned by your query. The complete syntax for the SELECT statement follows:
SELECT [ALL | DISTINCT] column_list
[INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
[[, {table_name2 | view_name2}[(optimizer_hints)]
[..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]
SELECT * FROM table_name is the most basic of all queries. When you use an asterisk (*) for column_list, it retrieves all columns from the table. In the pubs database, for example, you can run this query to select all columns and rows from the authors table:
SELECT *
FROM employee
To select specific columns from a table, you must designate the columns in column_list. Each column must be separated by a comma ",". You should not place a comma after the final column.
SELECT column_name [, column_name...] FROM table_name
SELECT fname, lname, EMPLOYEE_id FROM employee
This query selects the first name, last name, and employee ID for each employee in the employee table. When you execute a query with SELECT *, the column order is the same as the column order specified in the CREATE TABLE statement. When you select columns from a table, the column_list order does not have to be the same as the table column order. You can rearrange the column order in your query output by rearranging the columns in column_list. You can take the previous query and rearrange the column order. The same information is returned, but it is displayed in a different column order.
SELECT EMPLOYEE_id, lname, fname FROM employee
Changing Column Headings
When the results of a query are displayed, the column headings are the names used in column_list. Instead of using column headings, such as lname and fname, you can produce more readable column headings, such as FirstName and LastName, by aliasing the column headings. You can alias column headings using SQL Server 2000 syntax or ANSI SQL syntax.
SQL Server 2000 supports both the ANSI '92 SQL syntax standard as well as its own Microsoft SQL Server 2000 flavor. You can alias columns with SQL Server syntax in two ways:
SELECT column_heading = column_name FROM table_name
Or
SELECT column_name [AS] column_heading FROM table_name
You can rewrite your query by using the following SQL Server 6.5 syntax:
SELECT EmployeeID = EMPLOYEE_id, LastName = lname, FirstName = fname
FROM employee
You also can rewrite your query by using ANSI SQL syntax:
SELECT EMPLOYEE_id AS EmployeeID, lname AS LastName, fname AS FirstName
FROM employee
Both queries have the same result:
NOTE: If the alias you used has spaces or is a SQL Server keyword, you must enclose the alias in single quotation marks. Here is an example using spaces:
SELECT lname AS /Last Name', fname AS 'First Name' FROM employee
Here is an example using a SQL keyword:
SELECT 'count' = Count(*) FROM employee
Using Literals
You also can use literals to make output more readable. A literal is a string surrounded by single quotation marks included in column_list and displayed as another column in the query result. It looks a lot like creating a label next to your column of information. The syntax for including a literal value follows:
SELECT 'literal' [, 'literal'...]
SELECT fname, lname, 'Employee ID:', EMPLOYEE_id FROM employee
fname lname EMPLOYEE_id
This query returns first name, last name, a column containing the literal string Employee ID:, and the employee ID for all employees in the employee table.
Manipulating Data
You can manipulate data in your query results to produce new columns that display computed values, new string values, converted dates, and more. Your query results can be manipulated using arithmetic operators, mathematical functions, string functions, datetime functions, and system functions. You also can use the CONVERT function to convert from one datatype to another for easier data manipulation.
At this position we will have information data manipulation commands in SQL. What are they? With data manipulation commands user can populate tables with new data, update existing data in tables, and delete data from tables. Simple database queries can also performed within a data manipulation command. There are three data manipulation command in SQL:
Also, SELECT command, which can be used with data manipulation commands, is discussed in more detail before.
Populating Tables with New Data using SQL
Populating a table with data is simply the process of entering new data into a table, whether through a manual process using individual commands, or through batch processes using programs or other related software. Shortly, populating a table is the process of loading new data to a table.
Many factors may affect what data and how much data can be put into a table when populating tables with data. Some major factors include existing table constraints, the physical table size, and column datatypes, the length of columns, and other integrity constraints such as primary and foreign keys.
Please keep in mind that SQL statements can be in upper or lowercase. The data, depending upon how stored in the database is case sensitive. These examples use both lower and uppercase just to show that it does not make any difference on the outcome.
Inserting Data into a Table
Use the INSERT statement to insert new data into a table. There are a few options with the INSERT statement; here is a basic syntax to this command:
Insert into schema.table_name VALUES ('value1', 'value2', [NULL] );
Using this INSERT statement syntax, you must include every column in the specified table in the VALUES list. Notice that each value in this list is separated by a comma. The values inserted into the table must be enclosed by quotation marks for character and date datatypes. Quotation marks are not required for number datatypes or NULL values using the NULL keyword. A value should be present for each column in the table. In the following code piece, we insert a new record into the BOOKS_TBL table.
Table structure:
Books_tbl
Sample INSERT statement:
Insert into books_tbl Values ('725', "Database Programming with C#', 50.00);
1 row created
In this example you insert three values into a table with three columns. The inserted values are in the same order as the columns listed in the table. The first two values are inserted using quotation marks, because the datatypes of the corresponding columns are of character type. The third value's associated column, PRICE, is a number datatype and does not require quotation marks, although they are optional.
The simplest form of the INSERT statement requires that you explicitly supply a value for each column; those values must be in the correct sequence. If you want to supply the values in a different order, or if you do not want to supply an explicit value for a column, you can use another variant of the INSERT statement.
The following INSERT statement has a list of column names before the VALUES clause, and that list includes only a subset of the column names in the table; the values list then only needs to have values for the columns listed.
insert into publishers(state, pub_id) values('CA', '5458')
What happens to the columns that aren't mentioned in the column list? If you insert a new row, every column must have some value. If you don't supply a value, SQL Server must be able to determine one. In order for SQL Server to determine a value, every column not mentioned in the list of columns must meet one of the following criteria:
- The column has a default value attached to it.
- The column is an identity column.
- The column allows NULLs.
The column is of type timestamp.
There is not a value supplied for the publisher name, the city, or the country in the preceding INSERT statement. In the publishers table, the publisher name and city columns both allow NULLs, and the country has a default value of USA. After executing the INSERT statement, you can run the following query to see the row that you have just inserted.
select * from publishers where pub_id = '5458'
Notice the NULL values and the default value for country:
pub_id pub_name city state country
------ -------- --------- ---- ------
5458 (null) (null) CA USA
If you tried to execute an INSERT statement, leaving out values for columns that did not meet one of the listed criteria, you get an error. This INSERT does not supply a value for the pub_id column:
insert into publishers(pub_name, city, state) values('The Best Books', 'New Orleans', 'LA')
Msg 233, Level 16, State 2
The column pub_id in table publishers may not be null. The error message is not entirely complete. The problem is not that the pub_id column doesn't allow NULLs, but that it also does not have a default value and it isn't an identity column.
DEFAULT VALUES
There is one more variation of the simple, single row INSERT statement, which is used when you don't want to include a list of column names, but do want SQL Server to use default values where they exist (this includes NULLs and identity values). You can use the keyword DEFAULT in the actual values list, as a way of telling SQL Server that it should determine what value should be used. For example:
insert into publishers values('5458', DEFAULT, DEFAULT,'CA', DEFAULT)
If every column in a table has some kind of default value that SQL Server can determine, there is one more variation you can use. You can simply tell SQL Server to use all default values by using the keywords DEFAULT VALUES, as in the following INSERT statement:
insert into publishers default values
The statement won't run either, because it violates the NOT NULL constraint on the pub_id column. See when client applications like Microsoft Visual Basic or PowerBuilder connect to a database and add data, they are using the INSERT/VALUES statements at which you just looked. In addition, unbound grid type controls used in the visual front-end tools do not automatically add data to the database. A developer would have to write some code to loop through all of the values changed in the grid and then INSERT/VALUES those rows into the database.
continue article