The ALTER TABLE statement is part of the Data Definition Language (DDL). The statement is used to modify a table after it has been created. Today, I would like to talk about the three integrity concerns that data modelers deal with. Integrity Concerns Entity Data Integrity – Adding primary keys to makes sure rows are unique. Referential Data Integrity – Adding foreign keys to make sure relationships are maintained. Domain Data Integrity – Creating constraints or triggers enforce the range of data values. Again, we will be working with the…
Crafting Databases – Part 3
The CREATE TABLE statement is part of the Data Definition Language (DDL). It is used to define a structure to hold rows of data or tuples if we use Relational Algebra concepts. Each table is defined by a set of fields of a certain data type and precision. Data Types can be categorized as the following. Exact numerics Approximate numerics Character strings Unicode character strings Binary strings Date and time Other data types The power of a database comes from creating user defined datatypes (UDT). Some of the benefits of…
Crafting Databases – Part 2
The CREATE LOGIN and CREATE USER statements are part of the Data Definition Language (DDL) defined by Codd. Basically, a server login has to be defined so that person can connect to the server and a user login has to be created to map the server login to a particular database. I am going to continue my talk with the BSA hypothetical business problem from Part 1. Server logins allow access at the server level which can be defined using Windows Authentication or a SQL Server Authentication. Windows Authentication assumes…
Crafting Databases – Part 1
The CREATE DATABASE SQL statement is part of the Data Definition Language (DML) defined by Codd and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files. Before I talk about crafting a database, I want to classify SQL statements by four known groups. Data Definition Language (DDL) statements are SQL statements that support the definition or declaration of database objects. CREATE ALTER DROP Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database. SELECT…
Selecting Data – Part 1
The SELECT reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database as recordsets. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. All reserved words that I introduce below will be in CAPS below. Today, I would like to review simple SELECT queries using the AdventureWorks Sample database supplied by Microsoft. The most basic use of the SELECT statement is to return a constant as…
Managing Files
The most basic storage unit of any operating system is a file. Files can contain anything from textual data in this article, a bitmap image of your baby daughter, a MS Excel worksheet containing your finances or a Visual Basic program you are currently working on. Therefore it is very important to know how to manipulate files after they are created. Common actions that are performed on files are copy, rename, move and delete. Each file has properties such as creation date, last date modified, and attributes such as read…
Output Clause
The OUTPUT clause was introduce with the release of SQL Server 2005. This clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be inserted into a table or variable to implement business requirements such as confirmation messages, archiving, and atomic transactions. Today, I would like to use the AdventureWorks Sample database as the basis of our discussion. The first example creates a new Country By Continent table to store all the countries in South America. The OUTPUT clause…
Deleting Data
The DELETE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to remove existing data in one table in the database. Today, I would like to review the different ways to remove data using the AdventureWorks Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the DELETE. 1 – One way to purge data from a table is to use the DELETE statement without a where clause. Only use this syntax when…
Updating Data
The UPDATE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to modify existing row data in one table in the database. Today, I would like to review the different ways to update data using the Adventure Works Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the UPDATE. 1 – The simplest form of the UPDATE statement modifies one column in a given table filtered by one where clause. The following…
Inserting Data
The INSERT reservered word is part of the Data Manipulation Language (DML) defined by Codd in 1970 and is used to add data to a table in the database. Today, I would like to review the different ways to insert data using the AdventureWorks Sample database supplied by Microsoft. 1 – Many times a table is defined with a surrogate key, an autoincrement column. You can specify values for this column by setting the INDENTITY INSERT property of the table. The following code adds a row to the Human Resources…