Adding Comments to SQL Statements
In line comment start with two –,
example
Select StudentName from Student where ID=’5′ –This shows student name with student ID 5;
Block or Multi-Line comment with /* and */,
example
/* This shows student name with student ID 5 */
Select StudentName from Student where ID=’5′;
Select Command
Select command pull data which match selection critia from database. It can have extension such as order by.
General format: Select [column name] from ServerName.DatabaseName.ado.TableName where [column name]=’value’;
If you run the query in same computer as the server, you can omitt ServerName. If you already login to the database, such as running a query on MSSQL management studio, you can skip DatabaseName.ado.
Example
/* This shows student name with student home address in California and list student name by alphebetical order*/
Select StudentName from Student where state=’California’ order by StudentName;
Wild Cards in Where cause
If you are not sure of the column value in the where causes, you can use “Like” and wild cards.
Wild card “%” means zero or any number of characters
Wild card “_” means one character
Wild card [CharacterList] means any character in the character list
Wild card [^CharacterList] or [!CharacterList] means not any character in the character list
Example
/* Pull out any student with home state starting with NO, such as North Carolina, North Dakota */
Select StudentName from Student where state Like ‘NO%’ order by StudentName;
Order By Cause in Select Statement
You can sort your quary result with order by cause. Default order is increasing order, ASC. You can force it to decending order, DESC. If you need to sort data with multiple critia, you can combine multiple sorting requirements.
Example
/* This shows student name with student home address in California and list student name by city in alphebetical order then by descending zip code order*/
Select StudentName from Student where state=’California’ order by city, zipcode DESC;
Wild Cards in Where cause
Update Table Value
General format: Update ServerName.DatabaseName.ado.TableName Set [ColumnName]=’Value’ where [ColumnName]=’value’;
Example
/* This update student name with student ID 5 to ‘John Doe’*/
Update Student set StudentName=’John Doe’ where StudentID=’5′;
/* If you need to update multiple values, you can chain set statement together. Statement below update student name, student state, student city and student street for student with student ID equal to 5 */
Update Student set StudentName=’John Doe’, set StudentState=’California’, set StudentCity=’Bakersfield’, set StudentStreet=’Deerfield’ where StudentID=’5′;
Insert Record into Database Table
Structure: Insert into TableName (ColumnName1, ColumnName2…) values (Value1, Value 2…);
Example
/* Statement insert a new student into Table Student with Student Name Joe Smith and student state of california, student city of Bakersfield */
Insert into Student (StudentName,StudentState, StudentCity) Values (‘Joe Smith’, ‘California’, ‘Bakersfield’, ‘Deerfield’);
Delete Record in Database Table
Structure: Delete from TableName where ColumnName=Value;
Example
/* Delete all record of student whose StudenID is 5 */
Delete from Student where StudentID=’5′;
Add, Drop column or change Column Name with Alter command
/* Add Column into an Existing table */
Alter Table TableName add columnName datatype;
/* Drop Column from an Existing table */
Alter Table TableName drop Column columnName;
/* Alter a Column’s Data Type in an Existing table */
Alter Table TableName alter Column columnName datatype;
Drop Table (Delete table with its struture and data)
Drop Table TableName;
Example:
/* This statement delete database table Student */
Drop Table Student;
Insert Record into Database Table
Structure: Insert into TableName (ColumnName1, ColumnName2…) values (Value1, Value 2…);
Drop Database (Delete database with all its struture and data)
Drop Database Database_Name;
Example:
/* This statement delete database Class2012 */
Drop Database Class2012;
Chain SQL Statements Together
You can chain SQL Statements together as long as each statement end with “;”
Example
/* This three statement can be run together */
Create table Test;
Alter Table Test add column StudentName character;
Insert into Test (StudenName) values (“John Doe”);
Reference