SQL Expressions/Examples

 

Creating a Database

To create a database in SQL, use the following syntax:

CREATE DATABASE DatabaseName

Here is an example:

CREATE DATABASE BethesdaCarRental;

Deleting a Database

To delete a database, you use the DROP DATABASE expression followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName;

Here is an example:

DROP DATABASE RealEstate1;

GO

Renaming a Database

To change the name of a database  , Transact-SQL provides sp_renamedb. The formula used would be:

EXEC sp_renamedb ‘ExistingName‘, ‘NewName’

The EXEC sp_renamedb expression is required.

The ExistingName factor is the name of the database that you want to rename.

The NewName factor is the name you want the database to have after renaming it.

Here is an example of renaming a database:

EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental

GO

Creating a Table

To create a table, you can follow this syntax:

CREATE TABLE Country(Column1, Column2, Column3)

or:

CREATE TABLE Country(

Column1,

Column2,

Column3);

Each column is created as:

ColumnName DataType Options

Here is an example:

CREATE TABLE Customers (

               DrvLicNbr nvarchar(32),

               DateIssued DATE,

               DateExpired date,

               FullName nvarchar(50),

               Address NVARCHAR(120),

               City NvarChar(40),

               State NVarChar(50),

               PostalCode nvarchar(20),

               HomePhone nvarchar(20),

               OrganDonor BIT);

GO

Deleting a Table

To delete a table using SQL, use the following syntax:

DROP TABLE TableName

The DROP TABLE expression is required and it is followed by the name of the undesired table. Here is an example:

DROP TABLE Students;

GO

Renaming a Table

To rename a table using code, execute the sp_rename stored procedure using the following syntax:

sp_rename ExistingTableName, TableNewName;

Here is an example:

sp_rename 'StaffMembers', 'Employees';

GO

Creating a View

To programmatically create a view, you use the following SQL syntax:

CREATE VIEW ViewName

AS

SELECT Statement

Example:

CREATE VIEW dbo.ListOfMen

AS

SELECT dbo.Genders.Gender,

       dbo.Persons.FirstName, dbo.Persons.LastName

FROM   dbo.Genders INNER JOIN dbo.Persons

ON     dbo.Genders.GenderID = dbo.Persons.GenderID

WHERE  (dbo.Genders.Gender = N'Male');

GO

Modifying a View

ALTER VIEW ViewName

AS

SELECT Statement

Example:

ALTER VIEW dbo.ListOfMen

AS

SELECT dbo.Persons.FirstName, dbo.Persons.LastName

FROM   dbo.Genders INNER JOIN dbo.Persons

ON     dbo.Genders.GenderID = dbo.Persons.GenderID

WHERE  (dbo.Genders.Gender = N'Male');

Deleting a View

DROP VIEW ViewName

Example:

DROP VIEW dbo.ListOfMen

• Previous Entry: Make PNG transparency work in Internet Explorer
• Next Entry: Transact-SQL: Adding a Column to a Table

1. forex robot says:

   June 29, 2010 at 11:27 am

   What a great resource!
2. kelli_bandi says:

   July 14, 2010 at 10:14 am

   I want to quote your post in my blog. It can?
   And you et an account on Twitter?