Welcome to Mysql Tutorial
Mysql SQL IntroductionAbout SQLSQL stands for Structured Query Language.
SQL is a language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables. And you can drop (delete) them.
More advanced features include creating stored procedures (self contained scripts), views (pre-made queries), and setting permissions on database objects (such as tables, stored procedures, and views).
Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of SQL. Different database vendors have their own variations of the language.
Having said this, to be in compliance with the ANSI standard, they need to at least support the major commands such as DELETE, INSERT, UPDATE, WHERE etc. Also, you will find that many vendors have their own extensions to the language - features that are only supported in their database system.
Furthermore, transact-SQL is an extension to the ANSI standard and provides extra functionality.
Using SQLIf you need to build a website with a database providing the content, you will generally need the following:
The next lesson covers the SQL syntax basics.
SQL SyntaxThe SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a SQL statement.
Generally, a SQL statement begins by stating what to do (for example, "SELECT"), then states which object to do it to (for example, using the "FROM" clause).
SELECT * FROM Individual
It may also have a condition added to the end (for example, with a WHERE clause).
SELECT * FROM Individual
WHERE FirstName = 'Homer'
SQL is not case sensitive - the above examples could just have easily used all lowercase or all uppercase. Different programmers have their own preferences. For readability purposes, many SQL programmers prefer to use uppercase for SQL commands and lowercase for everything else.
The SQL syntax allows you to include line breaks at logical points without it breaking the statement. For example, the above example could have been written all on one line - or across 4 lines.
Also, some database systems require that you use a semicolon at the end of each SQL statement (although this tutorial doesn't use the semicolon).
DML & DDLSQL is divided into two main categories; Data Manipulation Language (DML), and Data Definition Language (DDL). An explanation follows.
Data Manipulation Language (DML)DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database. Many of your SQL statements will begin with one of the following commands:
Examples of DDL commands:
As you can see, the SQL syntax is quite simple. It is also very powerful syntax - you can do a lot of damage with one line of code!
SQL SelectThe SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.
Lets have a look at a simple SELECT statement:
SELECT * FROM Individual
This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.
How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).
Of course, this SQL SELECT statement assumes that there is a table called Individual. If there wasn't, an error would be generated.
Lets have a look at the table the statement is trying to select data from:
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Because our select statement asks to display all columns and all records, we would see the following:
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Select from Multiple TablesYou can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.
We have another table called Occupation, which contains the individual's occupation.
OccupationIdIndividualIdJobTitle 11Engineer 22Accountant 33Cleaner 44Attorney 55Sales Executive SQL statementWe will select from both the Individual table and the Occupation table. We will qualify any column names by prefixing them with its table's name and a dot.
SELECT * FROM Individual, Occupation
WHERE Individual.FirstName = 'Homer'
Result IndividualIdFirstNameLastNameUserNameOccupationIdIndividualIdJobTitle 1FredFlinstonefreddo11Engineer 2HomerSimpsonhomey22Accountant 3HomerBrownnotsofamous33Cleaner 4OzzyOzzbournesabbath44Attorney 5HomerGainnoplacelike55Sales Executive Displaying Less ColumnsIf you don't need every column to be displayed you can single out just the columns you're interested in. It's good programming practice to do this - the more columns your program has to return, the more it will impact its performance.
To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list of the column names.
SQL statement SELECT IndividualId, LastName, UserName FROM Individual
WHERE FirstName = 'Homer'
Result IndividualIdLastNameUserName 2Simpsonhomey 3Brownnotsofamous 5Gainnoplacelike
SQL WhereIn the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is fine if we want to see every record, but what if we were only interested in some records? For example, what if we were only interested in individuals whose first name is "Homer"?
We could use the WHERE clause.
Using the WHERE clause, you can filter out only those records that satisfy a given condition.
SQL WHERE Syntax SELECT * FROM table_name
WHERE column_name = 'criteria'
ExampleSQL WHERE Statement SELECT * FROM Individual
WHERE FirstName = 'Homer'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultGiven there are 3 people with the first name of "Homer", the results will look like this:
IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 3HomerBrownnotsofamous 5HomerGainnoplacelike Multiple ConditionsYou can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.
AND OperatorThe AND operator filters the query to only those records that satisfy both the first condition and the second condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
AND LastName = 'Brown'
Result IndividualIdFirstNameLastNameUserName 3HomerBrownnotsofamous OR OperatorThe OR operator filters the query to only those records that satisfy either one or the other condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
OR LastName = 'Ozzbourne'
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 3HomerBrownnotsofamous 5HomerGainnoplacelike 4OzzyOzzbournesabbath SQL Order ByUsing a SQL SELECT statement can retreive many hundreds or even thousands of records. In some cases you might find it useful to sort the records by a given column. For example, when selecting records from the Individual table, you might like to sort them by the LastName column.
SQL statement SELECT * FROM Individual
ORDER BY LastName
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 3HomerBrownnotsofamous 1FredFlinstonefreddo 5HomerGainnoplacelike 4OzzyOzzbournesabbath 2HomerSimpsonhomey Descending OrderBy default, ORDER BY sorts the column in ascending order - that is, from lowest values to highest values. You could also explicitly state this using the ASC keyword, but it's not necessary.
If you want highest values to appear first, you can use the DESC keyword.
SQL statement SELECT * FROM Individual
ORDER BY LastName DESC
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 4OzzyOzzbournesabbath 5HomerGainnoplacelike 1FredFlinstonefreddo 3HomerBrownnotsofamous Sorting By Multiple ColumnsYou can sort by multiple columns by stating each column in the ORDER BY clause, separating each column name with a comma. SQL will first order the results by the first column, then the second, and so on for as many columns that are included in the ORDER BY clause.
SQL statement SELECT * FROM Individual
ORDER BY FirstName, LastName
Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 3HomerBrownnotsofamous 5HomerGainnoplacelike 2HomerSimpsonhomey 4OzzyOzzbournesabbath SQL TopIn the preceeding lessons on the SELECT statement, the examples have returned all records that have matched our SELECT criteria. This is great if you want to look at every record, but, what if you only want to look at the first few records?
Sounds like you need the SQL TOP clause.
The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when there are thousands of records. Returning thousands of records can impact on performance, and if you are working with a production database, this could have an adverse impact on the users.
Note: The SQL TOP clause is Transact-SQL, and not part of ANSI SQL. Therefore, depending on your database system, you may not be able to use this clause.
SQL statement SELECT TOP 3 * FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous Specifying a PercentageYou have the option of specifying a percentage of the result set instead of an absolute value. You do this with the PERCENT keyword.
SQL statement SELECT TOP 40 PERCENT * FROM Individual
Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey SQL TOP and the ORDER BY clauseIf you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to the ordered result set.
Therefore, if we add an ORDER BY to the above query, we end up with something like this:
SQL statement SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 4OzzyOzzbournesabbath SQL DistinctOnce a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many Individuals share first names and surnames. Most of the time this isn't a problem. But sometimes you will want to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.
SQL statement SELECT DISTINCT(FirstName) FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultUsing the DISTINCT keyword, all customers with a name of "Homer" are counted as one.
FirstName Fred Homer Ozzy SQL InThe SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.
SQL Syntax SELECT * FROM table_name
WHERE column_name IN (value1,value2,...)
ExampleSQL statement SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')
Source Table IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 4OzzyOzzbournesabbath You might have noticed that this returns the same result as the following SQL statement:
SELECT * FROM Individual
WHERE LastName = 'Simpson'
OR LastName = 'Ozzbourne'
OR LastName = 'Flinstone'
Yes, we could just have easily used that statement but the SQL IN statement is more concise.
SQL IN and SubqueriesNow, where the SQL IN operator becomes really useful is when you need to compare a value against the result of another query.
For example, lets say we have another table called "Publisher". This table contains users who are allowed to contribute to the website via an administration console. All users in the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher table.
Source TablesIndividual Table
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Publisher Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor Our task is to return a list of usernames from all publishers who have an access level of "Contributor".
You may notice that the usernames are in the Individual table but the access level is stored in the Publisher table. Also, there could potentially be many contributors. This is a good candidate for the SQL IN operator!
SQL statement SELECT UserName FROM Individual
WHERE IndividualId IN
(SELECT IndividualId
FROM Publisher
WHERE AccessLevel = 'Contributor')
Result UserName homey notsofamous sabbath In this example there aren't many records in the Publisher table, but imagine if there were thousands - the IN statement is great for this sort of thing.
SQL AliasIn SQL, an alias is a name that you give a table. This can make it easier to work with table names - especially when they are long. You could name the alias anything, but usually you'd make it short.
You may be thinking "a table already has a name, why give it another one?". Well, there are some good reasons for creating an alias. The main reasons are:
Alias Syntax SELECT * FROM table_name AS alias_name
Example SQL Statement SELECT o.JobTitle FROM Individual AS i, Occupation AS o
WHERE i.FirstName = 'Homer'
ORDER BY o.JobTitle
SQL JoinThe SQL JOIN refers to using the JOIN keyword in a SQL statement in order to query data from two tables.
When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables.
You can use multiple joins in the same SQL statement to query data from as many tables as you like.
Join TypesDepending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle way
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Left Join: SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Right Join: SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Full Join: SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Example Inner Join Statement SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:
SELECT * FROM Individual AS Ind
INNER JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
SQL Inner JoinAs discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records where there is at least one row in both tables that match the join condition.
Example SQL statement SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = '2'
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 2HomerSimpsonhomey2Contributor Next lesson covers the SQL OUTER JOIN.
SQL Outer JoinThis lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.
Left Outer JoinUse this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.
Example SQL statement SELECT * FROM Individual AS Ind
LEFT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor 5HomerGainnoplacelikeNULLNULL Right Outer JoinUse this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.
Example SQL statement SELECT * FROM Individual AS Ind
RIGHT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor NULLNULLNULLNULL10Administrator Full Outer JoinUse this when you want to all rows, even if there's no matching rows in the right table.
Example SQL statement SELECT * FROM Individual AS Ind
FULL JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor 5HomerGainnoplacelikeNULLNULL NULLNULLNULLNULL10Administrator SQL InsertUp until now, this tutorial has covered the SELECT statement and variations on it. We are now about to learn a new statement - the INSERT statement.
The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.
SQL statement INSERT INTO Individual
VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultNow if we do a SELECT on the Individual table, we can see the new record added to the bottom of the result set.
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillhillbenny SQL UpdateThe SQL UPDATE statement allows you to update an existing record in the database.
The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.
SQL statement UPDATE Individual
SET UserName = 'funnyman'
WHERE IndividualId = '6'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillhillbenny ResultNow if we select this record, we can see the updated value.
IndividualIdFirstNameLastNameUserName 6BennyHillfunnyman Updating Multiple FieldsTo update multiple fields, separate each field assignment with a comma.
SQL statement UPDATE Individual
SET UserName = 'getserious', FirstName = 'Onetree'
WHERE IndividualId = '6'
Result IndividualIdFirstNameLastNameUserName 6OnetreeHillgetserious SQL DeleteThe SQL DELETE statement allows you to delete a record from the database.
The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.
SQL statement DELETE
FROM Individual
WHERE IndividualId = '6'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillfunnyman ResultNow if we select all records from the table, we see that record 6 has been deleted.
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike SQL FunctionsSQL has a number of functions to assist you in your database programming.
Functions are a self contained script/program built for a specific purpose. Generally, the value returned by a function will depend on the context in which it is being used. Often, a SQL function will be used within a query and this is what provides it with it's context.
Transact-SQL provides 3 different types of functions:
Rowset FunctionsThese return an object that can be used in place of a table reference in a SQL statement Aggregate Functions Perform a calculation on a set of values and return a single value. Aggregate functions can be used in the following:
In the next lesson you will learn about one of the more commonly used SQL functions - the COUNT function.
SQL CountA commonly used aggregate function in SQL is COUNT. COUNT returns the number of rows that match the given criteria.
COUNT(*)If we only want to see how many records are in a table (but not actually view those records), we could use COUNT(*). COUNT(*) returns everything - including null values and duplicates.
SQL statement SELECT COUNT(*) FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6Bono u2 Result 6 COUNT(column name)If we want to see how many non-null values are in a given column, we use COUNT(column name) where column name is the name of the column we want to test.
SQL statement SELECT COUNT(LastName) FROM Individual
Source Table IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6Bono u2 Result 5 Combining COUNT & DISTINCTIf we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT function.
SQL statement SELECT COUNT(DISTINCT(FirstName)) FROM Individual
Result4SQL CreateMost database systems provide a way for you to create database objects via a WYSIWYG interface. For example, Microsoft's SQL Server has Enterprise Manager. The Enterprise Manager gives you a kind of graphical representation of your database system. You can browse through your databases, view the tables and their contents etc.
Despite having a tool like Enterprise Manager to make these tasks easier, there can be good reasons for wanting to perform some of these tasks programatically. Possible reasons could include:
Here are the CREATE commands supported by SQL Server:
SQL syntax CREATE DATABASE database_name
Example CodeThis statement creates a database called "Payroll". Because no arguments have been specified, the database data files and transaction logs will be created automatically in the default location.
CREATE DATABASE Payroll
Adding ArgumentsThere are a number of optional arguments that you can supply with the CREATE DATABASE command. You should check your database system's documentation for the specific arguments supported and their usage, but here's an example of supplying arguments when creating a database using Microsoft's SQL Server.
Example CodeIn this example, we are supplying the name and location of the database's data file and transaction log. We are also specifying the initial size of these files (with the SIZE argument), the maximum size it can grow to (with the MAXSIZE argument) and the growth increment of each file (using the FILEGROWTH) argument.
USE master
GO
CREATE DATABASE Payroll
ON
( NAME = Payroll_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\payrolldat.mdf',
SIZE = 20MB,
MAXSIZE = 70MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'Payroll_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\payroll.ldf',
SIZE = 10MB,
MAXSIZE = 40MB,
FILEGROWTH = 5MB )
GO
SQL Create TableYou create a table using the CREATE TABLE command.
SQL syntax CREATE TABLE table_name
(column_name_1 datatype,
column_name_2 datatype,
...
)
Example CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
UserName Char(10)
)
ResultThis results in an empty table. You can now use an INSERT statement to add data to the table.
IndividualIdFirstNameLastNameUserName Data TypesYou'll notice we explicitly stated the data type in our CREATE TABLE statement. This is because, when you create a column, you need to tell the database what type of data it can hold.
The exact data types and how their expressed differs with each database system and vendor, but you'll find that generally, there will be support for fixed length strings (eg char), variable length strings (eg varchar), date/time values (eg datetime), numbers and integers (eg, bigint, int, smallint, tinyint, numeric).
The following base data types are available in SQL Server 2000.
bigint Binary bit char cursor datetime Decimal float image int money Nchar ntext nvarchar real smalldatetime Smallint smallmoney text timestamp tinyint Varbinary Varchar uniqueidentifier SQL Create IndexIndexes can be created against a table to make searches more efficient. A database index is similar to an index of a book - a book index allows you to find information without having to read through the whole book. A database index enables the database application to find data quickly without having to scan the whole table.
Indexes can have a slight impact on performance so you should only create indexes against tables and columns that will be frequently searched against. For example, if users of your application often search against the LastName field then that field is a great candidate for an index.
You create an index using the CREATE INDEX command.
SQL syntax CREATE INDEX index_name
ON table_name (column_name)
ExampleCREATE INDEX IndividualIndex
ON Individual (LastName)
SQL Alter TableIn an earlier lesson, we created a table with the CREATE TABLE command. In this lesson, we will modify the table using the ALTER TABLE command.
Add a ColumnSQL syntax ALTER TABLE table_name
ADD column_name datatype
Example SQL Statement ALTER TABLE Individual
ADD age int
Change the DatatypeSQL syntax ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example SQL Statement ALTER TABLE Individual
ALTER COLUMN age numeric
Drop a Column'Dropping' a column means removing or deleting that column.
SQL syntax ALTER TABLE table_name
DROP COLUMN column_name
Example SQL Statement ALTER TABLE Individual
DROP COLUMN age
SQL is a language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables. And you can drop (delete) them.
More advanced features include creating stored procedures (self contained scripts), views (pre-made queries), and setting permissions on database objects (such as tables, stored procedures, and views).
Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of SQL. Different database vendors have their own variations of the language.
Having said this, to be in compliance with the ANSI standard, they need to at least support the major commands such as DELETE, INSERT, UPDATE, WHERE etc. Also, you will find that many vendors have their own extensions to the language - features that are only supported in their database system.
Furthermore, transact-SQL is an extension to the ANSI standard and provides extra functionality.
Using SQLIf you need to build a website with a database providing the content, you will generally need the following:
- A server side scripting language (i.e. ColdFusion, PHP, ASP/.NET)
- A database query language (i.e. SQL)
- A client side markup language and style sheets (i.e. HTML/CSS)
The next lesson covers the SQL syntax basics.
SQL SyntaxThe SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a SQL statement.
Generally, a SQL statement begins by stating what to do (for example, "SELECT"), then states which object to do it to (for example, using the "FROM" clause).
SELECT * FROM Individual
It may also have a condition added to the end (for example, with a WHERE clause).
SELECT * FROM Individual
WHERE FirstName = 'Homer'
SQL is not case sensitive - the above examples could just have easily used all lowercase or all uppercase. Different programmers have their own preferences. For readability purposes, many SQL programmers prefer to use uppercase for SQL commands and lowercase for everything else.
The SQL syntax allows you to include line breaks at logical points without it breaking the statement. For example, the above example could have been written all on one line - or across 4 lines.
Also, some database systems require that you use a semicolon at the end of each SQL statement (although this tutorial doesn't use the semicolon).
DML & DDLSQL is divided into two main categories; Data Manipulation Language (DML), and Data Definition Language (DDL). An explanation follows.
Data Manipulation Language (DML)DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database. Many of your SQL statements will begin with one of the following commands:
- SELECT - Retrieves data from the database
- INSERT - Inserts new data into the database
- UPDATE - Updates existing data in the database
- DELETE - Deletes existing data from the database
Examples of DDL commands:
- CREATE DATABASE - Creates a new database
- ALTER DATABASE - Modifies the database
- DROP DATABASE - Drops (deletes) a database
- CREATE TABLE - Creates a new table
- ALTER TABLE - Modifies the table
- DROP TABLE - Drops (deletes) a table
As you can see, the SQL syntax is quite simple. It is also very powerful syntax - you can do a lot of damage with one line of code!
SQL SelectThe SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.
Lets have a look at a simple SELECT statement:
SELECT * FROM Individual
This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.
How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).
Of course, this SQL SELECT statement assumes that there is a table called Individual. If there wasn't, an error would be generated.
Lets have a look at the table the statement is trying to select data from:
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Because our select statement asks to display all columns and all records, we would see the following:
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Select from Multiple TablesYou can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.
We have another table called Occupation, which contains the individual's occupation.
OccupationIdIndividualIdJobTitle 11Engineer 22Accountant 33Cleaner 44Attorney 55Sales Executive SQL statementWe will select from both the Individual table and the Occupation table. We will qualify any column names by prefixing them with its table's name and a dot.
SELECT * FROM Individual, Occupation
WHERE Individual.FirstName = 'Homer'
Result IndividualIdFirstNameLastNameUserNameOccupationIdIndividualIdJobTitle 1FredFlinstonefreddo11Engineer 2HomerSimpsonhomey22Accountant 3HomerBrownnotsofamous33Cleaner 4OzzyOzzbournesabbath44Attorney 5HomerGainnoplacelike55Sales Executive Displaying Less ColumnsIf you don't need every column to be displayed you can single out just the columns you're interested in. It's good programming practice to do this - the more columns your program has to return, the more it will impact its performance.
To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list of the column names.
SQL statement SELECT IndividualId, LastName, UserName FROM Individual
WHERE FirstName = 'Homer'
Result IndividualIdLastNameUserName 2Simpsonhomey 3Brownnotsofamous 5Gainnoplacelike
SQL WhereIn the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is fine if we want to see every record, but what if we were only interested in some records? For example, what if we were only interested in individuals whose first name is "Homer"?
We could use the WHERE clause.
Using the WHERE clause, you can filter out only those records that satisfy a given condition.
SQL WHERE Syntax SELECT * FROM table_name
WHERE column_name = 'criteria'
ExampleSQL WHERE Statement SELECT * FROM Individual
WHERE FirstName = 'Homer'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultGiven there are 3 people with the first name of "Homer", the results will look like this:
IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 3HomerBrownnotsofamous 5HomerGainnoplacelike Multiple ConditionsYou can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.
AND OperatorThe AND operator filters the query to only those records that satisfy both the first condition and the second condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
AND LastName = 'Brown'
Result IndividualIdFirstNameLastNameUserName 3HomerBrownnotsofamous OR OperatorThe OR operator filters the query to only those records that satisfy either one or the other condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
OR LastName = 'Ozzbourne'
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 3HomerBrownnotsofamous 5HomerGainnoplacelike 4OzzyOzzbournesabbath SQL Order ByUsing a SQL SELECT statement can retreive many hundreds or even thousands of records. In some cases you might find it useful to sort the records by a given column. For example, when selecting records from the Individual table, you might like to sort them by the LastName column.
SQL statement SELECT * FROM Individual
ORDER BY LastName
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 3HomerBrownnotsofamous 1FredFlinstonefreddo 5HomerGainnoplacelike 4OzzyOzzbournesabbath 2HomerSimpsonhomey Descending OrderBy default, ORDER BY sorts the column in ascending order - that is, from lowest values to highest values. You could also explicitly state this using the ASC keyword, but it's not necessary.
If you want highest values to appear first, you can use the DESC keyword.
SQL statement SELECT * FROM Individual
ORDER BY LastName DESC
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 4OzzyOzzbournesabbath 5HomerGainnoplacelike 1FredFlinstonefreddo 3HomerBrownnotsofamous Sorting By Multiple ColumnsYou can sort by multiple columns by stating each column in the ORDER BY clause, separating each column name with a comma. SQL will first order the results by the first column, then the second, and so on for as many columns that are included in the ORDER BY clause.
SQL statement SELECT * FROM Individual
ORDER BY FirstName, LastName
Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 3HomerBrownnotsofamous 5HomerGainnoplacelike 2HomerSimpsonhomey 4OzzyOzzbournesabbath SQL TopIn the preceeding lessons on the SELECT statement, the examples have returned all records that have matched our SELECT criteria. This is great if you want to look at every record, but, what if you only want to look at the first few records?
Sounds like you need the SQL TOP clause.
The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when there are thousands of records. Returning thousands of records can impact on performance, and if you are working with a production database, this could have an adverse impact on the users.
Note: The SQL TOP clause is Transact-SQL, and not part of ANSI SQL. Therefore, depending on your database system, you may not be able to use this clause.
SQL statement SELECT TOP 3 * FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous Specifying a PercentageYou have the option of specifying a percentage of the result set instead of an absolute value. You do this with the PERCENT keyword.
SQL statement SELECT TOP 40 PERCENT * FROM Individual
Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey SQL TOP and the ORDER BY clauseIf you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to the ordered result set.
Therefore, if we add an ORDER BY to the above query, we end up with something like this:
SQL statement SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC
Result IndividualIdFirstNameLastNameUserName 2HomerSimpsonhomey 4OzzyOzzbournesabbath SQL DistinctOnce a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many Individuals share first names and surnames. Most of the time this isn't a problem. But sometimes you will want to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.
SQL statement SELECT DISTINCT(FirstName) FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultUsing the DISTINCT keyword, all customers with a name of "Homer" are counted as one.
FirstName Fred Homer Ozzy SQL InThe SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.
SQL Syntax SELECT * FROM table_name
WHERE column_name IN (value1,value2,...)
ExampleSQL statement SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')
Source Table IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Result IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 4OzzyOzzbournesabbath You might have noticed that this returns the same result as the following SQL statement:
SELECT * FROM Individual
WHERE LastName = 'Simpson'
OR LastName = 'Ozzbourne'
OR LastName = 'Flinstone'
Yes, we could just have easily used that statement but the SQL IN statement is more concise.
SQL IN and SubqueriesNow, where the SQL IN operator becomes really useful is when you need to compare a value against the result of another query.
For example, lets say we have another table called "Publisher". This table contains users who are allowed to contribute to the website via an administration console. All users in the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher table.
Source TablesIndividual Table
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Publisher Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor Our task is to return a list of usernames from all publishers who have an access level of "Contributor".
You may notice that the usernames are in the Individual table but the access level is stored in the Publisher table. Also, there could potentially be many contributors. This is a good candidate for the SQL IN operator!
SQL statement SELECT UserName FROM Individual
WHERE IndividualId IN
(SELECT IndividualId
FROM Publisher
WHERE AccessLevel = 'Contributor')
Result UserName homey notsofamous sabbath In this example there aren't many records in the Publisher table, but imagine if there were thousands - the IN statement is great for this sort of thing.
SQL AliasIn SQL, an alias is a name that you give a table. This can make it easier to work with table names - especially when they are long. You could name the alias anything, but usually you'd make it short.
You may be thinking "a table already has a name, why give it another one?". Well, there are some good reasons for creating an alias. The main reasons are:
- Queries can sometimes get very long. Aliases can make your query easier to read.
- You may find yourself referencing the same table name over and over again - this will occur if you're working with multiple tables and you need to refer to columns from those tables. It can be annoying to have to write the whole name all the time - especially if it's a long one.
- You may need to work with multiple instances of the same table, for example, a self join. If you're not familiar with joins, they are covered later in this tutorial.
Alias Syntax SELECT * FROM table_name AS alias_name
Example SQL Statement SELECT o.JobTitle FROM Individual AS i, Occupation AS o
WHERE i.FirstName = 'Homer'
ORDER BY o.JobTitle
SQL JoinThe SQL JOIN refers to using the JOIN keyword in a SQL statement in order to query data from two tables.
When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables.
You can use multiple joins in the same SQL statement to query data from as many tables as you like.
Join TypesDepending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle way
- INNER JOIN: This will only return rows when there is at least one row in both tables that match the join condition.
- LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.
- RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.
- FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of the tables.
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Left Join: SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Right Join: SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Full Join: SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Example Inner Join Statement SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:
SELECT * FROM Individual AS Ind
INNER JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
SQL Inner JoinAs discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records where there is at least one row in both tables that match the join condition.
Example SQL statement SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = '2'
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 2HomerSimpsonhomey2Contributor Next lesson covers the SQL OUTER JOIN.
SQL Outer JoinThis lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.
Left Outer JoinUse this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.
Example SQL statement SELECT * FROM Individual AS Ind
LEFT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor 5HomerGainnoplacelikeNULLNULL Right Outer JoinUse this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.
Example SQL statement SELECT * FROM Individual AS Ind
RIGHT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor NULLNULLNULLNULL10Administrator Full Outer JoinUse this when you want to all rows, even if there's no matching rows in the right table.
Example SQL statement SELECT * FROM Individual AS Ind
FULL JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId
Source TablesLeft Table
IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike Right Table
IndividualIdAccessLevel 1Administrator 2Contributor 3Contributor 4Contributor 10Administrator Result IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel 1FredFlinstonefreddo1Administrator 2HomerSimpsonhomey2Contributor 3HomerBrownnotsofamous3Contributor 4OzzyOsbournesabbath4Contributor 5HomerGainnoplacelikeNULLNULL NULLNULLNULLNULL10Administrator SQL InsertUp until now, this tutorial has covered the SELECT statement and variations on it. We are now about to learn a new statement - the INSERT statement.
The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.
SQL statement INSERT INTO Individual
VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike ResultNow if we do a SELECT on the Individual table, we can see the new record added to the bottom of the result set.
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillhillbenny SQL UpdateThe SQL UPDATE statement allows you to update an existing record in the database.
The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.
SQL statement UPDATE Individual
SET UserName = 'funnyman'
WHERE IndividualId = '6'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillhillbenny ResultNow if we select this record, we can see the updated value.
IndividualIdFirstNameLastNameUserName 6BennyHillfunnyman Updating Multiple FieldsTo update multiple fields, separate each field assignment with a comma.
SQL statement UPDATE Individual
SET UserName = 'getserious', FirstName = 'Onetree'
WHERE IndividualId = '6'
Result IndividualIdFirstNameLastNameUserName 6OnetreeHillgetserious SQL DeleteThe SQL DELETE statement allows you to delete a record from the database.
The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.
SQL statement DELETE
FROM Individual
WHERE IndividualId = '6'
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6BennyHillfunnyman ResultNow if we select all records from the table, we see that record 6 has been deleted.
IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike SQL FunctionsSQL has a number of functions to assist you in your database programming.
Functions are a self contained script/program built for a specific purpose. Generally, the value returned by a function will depend on the context in which it is being used. Often, a SQL function will be used within a query and this is what provides it with it's context.
Transact-SQL provides 3 different types of functions:
Rowset FunctionsThese return an object that can be used in place of a table reference in a SQL statement Aggregate Functions Perform a calculation on a set of values and return a single value. Aggregate functions can be used in the following:
- The select list of a SELECT statement
- A COMPUTE or COMPUTE BY clause
- A HAVING clause
- Configuration Functions
- Cursor Functions
- Date and Time Functions
- Mathematical Functions
- Metadata Functions
- Security Functions
- String Functions
- System Functions
- System Statistical Functions
- Text and Image Functions
In the next lesson you will learn about one of the more commonly used SQL functions - the COUNT function.
SQL CountA commonly used aggregate function in SQL is COUNT. COUNT returns the number of rows that match the given criteria.
COUNT(*)If we only want to see how many records are in a table (but not actually view those records), we could use COUNT(*). COUNT(*) returns everything - including null values and duplicates.
SQL statement SELECT COUNT(*) FROM Individual
Source Table IndividualIdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6Bono u2 Result 6 COUNT(column name)If we want to see how many non-null values are in a given column, we use COUNT(column name) where column name is the name of the column we want to test.
SQL statement SELECT COUNT(LastName) FROM Individual
Source Table IdFirstNameLastNameUserName 1FredFlinstonefreddo 2HomerSimpsonhomey 3HomerBrownnotsofamous 4OzzyOzzbournesabbath 5HomerGainnoplacelike 6Bono u2 Result 5 Combining COUNT & DISTINCTIf we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT function.
SQL statement SELECT COUNT(DISTINCT(FirstName)) FROM Individual
Result4SQL CreateMost database systems provide a way for you to create database objects via a WYSIWYG interface. For example, Microsoft's SQL Server has Enterprise Manager. The Enterprise Manager gives you a kind of graphical representation of your database system. You can browse through your databases, view the tables and their contents etc.
Despite having a tool like Enterprise Manager to make these tasks easier, there can be good reasons for wanting to perform some of these tasks programatically. Possible reasons could include:
- Your application allows users to create objects on the fly.
- You have multiple environments (for example development, staging, production). It's much easier, and less error prone, to run a script against 3 environments than to open up Enterprise Manager and repeat the steps in 3 environments - especially if you have lots of changes.
- You don't have access to Enterprise Manager (or your database system's equivalent).
Here are the CREATE commands supported by SQL Server:
- CREATE ACTION
- CREATE CACHE
- CREATE CELL CALCULATION
- CREATE CUBE
- CREATE DATABASE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE INDEX
- CREATE MEMBER
- CREATE MINING MODEL
- CREATE PROCEDURE
- CREATE RULE
- CREATE SCHEMA
- CREATE SET
- CREATE STATISTICS
- CREATE TABLE
- CREATE TRIGGER
- CREATE UNIQUE CLUSTERED INDEX
- CREATE VIEW
SQL syntax CREATE DATABASE database_name
Example CodeThis statement creates a database called "Payroll". Because no arguments have been specified, the database data files and transaction logs will be created automatically in the default location.
CREATE DATABASE Payroll
Adding ArgumentsThere are a number of optional arguments that you can supply with the CREATE DATABASE command. You should check your database system's documentation for the specific arguments supported and their usage, but here's an example of supplying arguments when creating a database using Microsoft's SQL Server.
Example CodeIn this example, we are supplying the name and location of the database's data file and transaction log. We are also specifying the initial size of these files (with the SIZE argument), the maximum size it can grow to (with the MAXSIZE argument) and the growth increment of each file (using the FILEGROWTH) argument.
USE master
GO
CREATE DATABASE Payroll
ON
( NAME = Payroll_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\payrolldat.mdf',
SIZE = 20MB,
MAXSIZE = 70MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'Payroll_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\payroll.ldf',
SIZE = 10MB,
MAXSIZE = 40MB,
FILEGROWTH = 5MB )
GO
SQL Create TableYou create a table using the CREATE TABLE command.
SQL syntax CREATE TABLE table_name
(column_name_1 datatype,
column_name_2 datatype,
...
)
Example CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
UserName Char(10)
)
ResultThis results in an empty table. You can now use an INSERT statement to add data to the table.
IndividualIdFirstNameLastNameUserName Data TypesYou'll notice we explicitly stated the data type in our CREATE TABLE statement. This is because, when you create a column, you need to tell the database what type of data it can hold.
The exact data types and how their expressed differs with each database system and vendor, but you'll find that generally, there will be support for fixed length strings (eg char), variable length strings (eg varchar), date/time values (eg datetime), numbers and integers (eg, bigint, int, smallint, tinyint, numeric).
The following base data types are available in SQL Server 2000.
bigint Binary bit char cursor datetime Decimal float image int money Nchar ntext nvarchar real smalldatetime Smallint smallmoney text timestamp tinyint Varbinary Varchar uniqueidentifier SQL Create IndexIndexes can be created against a table to make searches more efficient. A database index is similar to an index of a book - a book index allows you to find information without having to read through the whole book. A database index enables the database application to find data quickly without having to scan the whole table.
Indexes can have a slight impact on performance so you should only create indexes against tables and columns that will be frequently searched against. For example, if users of your application often search against the LastName field then that field is a great candidate for an index.
You create an index using the CREATE INDEX command.
SQL syntax CREATE INDEX index_name
ON table_name (column_name)
ExampleCREATE INDEX IndividualIndex
ON Individual (LastName)
SQL Alter TableIn an earlier lesson, we created a table with the CREATE TABLE command. In this lesson, we will modify the table using the ALTER TABLE command.
Add a ColumnSQL syntax ALTER TABLE table_name
ADD column_name datatype
Example SQL Statement ALTER TABLE Individual
ADD age int
Change the DatatypeSQL syntax ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example SQL Statement ALTER TABLE Individual
ALTER COLUMN age numeric
Drop a Column'Dropping' a column means removing or deleting that column.
SQL syntax ALTER TABLE table_name
DROP COLUMN column_name
Example SQL Statement ALTER TABLE Individual
DROP COLUMN age
