Home

Introduction to the Tables of a Database

 

Tables Fundamentals

 

Introduction

Tables Fundamentals

A computer database is an application that contains one or more lists. A list is a series of values. A simple list can be made of numbers. Another simple list can contain names (of people, or of songs, or of food items). In a formal database, a list is called a table. In some documents, a tale is called an entity. In some other documents, a table is referrred to as a relation.

Microsoft SQL Server provides various means and tools to create tables. First of all, a table must belong to a database. This means that you must first choose the database that will own the table.

 

We are starting a database for a fictitious university. This is a higher learning institution (college) that provides courses in many areas. It delivers Bachelor’s degrees and Master’s degrees. In this series of lessons, we will limit our school to a 4-year college (Bachelor’s degrees).

Students register to the school to get the desired education. To get admitted to the school, a student must provide information such as the name, the date of birth, the gender, etc. Once admitted, to attend a course, a student must enroll in one or various courses. Each course is taught or supervised by a teacher. When a course ends, the teacher must give (or assign) a grade to each student who attended the course. In most colleges and in ours, a student will receive a letter grade as A or B or C, etc.

There is no standard way a university database is made. Each school does it as it wants. We too will use our inspiration to create and manage our university database.

In this first version of our database, named University1, we will create a table for students’ grades so we can start defining what types of grades the students will get. We will also define what the scale is for each letter grade; that is, what ranges of numbers define a letter grade. The table of grades will be stored in a schema named Academics.

Monson University
 

Practical LearningPractical Learning: Introducing Tables

  1. Launch Microsoft SQL Server and connect
  2. In the Object Explorer, expand the Databases node if necessary.
    In the Object Explorer, right-click Databases and click New Database...
  3. In the New Database dialog box, set the Database Name to University1
  4. Click OK
  5. In the Object Explorer, right-click Databases and click Refresh
  6. Click the + button of University1 to expand it
  7. Under University1, click the + button of Security to expand it
  8. Right-click Schemas and click New Schema...
  9. In the Schema Name text box, type Academics
  10. Click OK

Visually Creating a Table

One way to create a table is to design it. To do this, in the Object Explorer, expand the Databases node and expand the database to which the table will belong. Right-click the Tables node and click New Table...

Visually Creating a Table

This would create a Table window.

Author Note

In our lessons, if you right-click the Tables node in the Object Explorer and click New Table..., the window that displays will be called the Table window.

Practical LearningPractical Learning: Visually Starting a Table

Creating a Table With SQL

Creating a Table With SQL

To assist you with creating a table, the structured query language, SQL, provides an operation, called a command, in its Data Definition Language (DDL). The command is CREATE TABLE and it is used is a formula that starts as follows:

CREATE TABLE TableName . . .

The CREATE TABLE expression is required. TableName specifies the name of the new table.

Temporary Tables

After creating a table, it becomes part of its database and you can use that table over and over again. In some cases, you may want to create a table to test something and you would not need to use that table the next time you connect to the server. Such a table is referred to as a temporary table.

To create a temporary table, use the poind sign, #, after CREATE TABLE. Once the table has been created, it would be available as long as you are using the same connection to the server. If you close Microsoft SQL Server, the table would be automatically deleted.

Using Sample Code

To assist you with creating a table, Microsoft SQL Server can generate sample code for you. You can then simply modify or customize it. First display or open an empty Query Editor. To display the Templates Explorer, on the main menu, click View -> Templates Explorer. In the Templates Explorer, expand the Table node. Under Table, drag Create Table and drop it in the Query Editor. Sample code would be generated for you.

Primary Characteristics of a Table

 

The Name of a Table

When creating a table, you must give it a name. If you are visually creating a table, if you decide to close it, you would be asked whether you want to save it. If you click Yes, you would be asked to give it a name.

The name of a table:

Besides these rules, you can make up yours. To avoid confusion, here are the rules we will use to name our tables:

The Schema of a Table

As mentioned already, a schema is an object that contains other objects, such as tables. In fact, every table in Microsoft SQL Server must belong to a schema. When you are creating a table, you have the option of specifying or not indicating its schema. If you don't specify a schema, the default dbo schema would own it.

To visually specify the schema that will contain a table, in the Properties window, click Schema, then click the arrow of its combo box and select the desired schema:

Specifying the Alias of a Table

To specify the schema of a table using code, precede its name with the name of the schema followed by a period. The formula to use is:

CREATE TABLE SchemaName.TableName....

An example would be:

CREATE SCHEMA Registration;
GO
CREATE TABLE Registration.Students . . .

Remember that if you don't specify a particular schema, the default dbo schema takes ownership of the table.

After creating a table, you can change its schema. To do this visually, open the table in Design view. In the Properties window, click the arrow of the Schema combo box and select the desired schema. You will receive a message box (based on the permissions):

Specifying the Schema of a Table

If you still want to change the schema, click Yes.

Practical LearningPractical Learning: Specifying the Schema of a Table

Tables Maintenance

 

Introduction

Table maintenance consists of reviewing or changing some of its aspects. This includes reviewing the list of tables of a database, renaming a table, or deleting it.

Viewing the Properties of a Table

Like every other object of a database or of the computer, a table possesses some characteristics that are proper to it. To view these characteristics, in the Object Explorer, right-click the table and click Properties.

Opening a Table

Most operations require that you open a table before using it. There are various ways a table displays, depending on how you want to examine it:

Author Note

In our lessons, if you right-click a table in the Object Explorer and click Design, the window that displays will be referred to as the Design View of a table.

Modifying the Design of a Table

Probably the most routine operation you will perform on a table is to change its design. This is usually easily done after you have opened the table in Design view. You can then change what you want. After making the change(s), you must save the table. Depending on how your database is configured, you may receive an error as "Saving changes is not permitted...":

Save

To avoid this error and make it possible to modify tables, on the main menu of SQL Server Management Studio, click Tools -> Options... In the left tree list of the Options dialog box, click Designers. In the Table Options, clear the Prevent Saving Changes That Require Table Re-Creation check box.

Practical LearningPractical Learning: Allowing Changes on Tables

  1. On the main menu of SQL Server Management Studio, click Tools -> Options...
  2. In the left tree list of the Options dialog box, click Designers
  3. In the Table Options, clear the Prevent Saving Changes That Require Table Re-Creation check box

    Options

  4. Click OK

Tables Review

To see the list of tables of a database in the Object Explorer, you can click the Tables node:

Viewing the Tables of a Database

To see the list of tables of a database using SQL, in a Query Editor, specify the database (using a USE statement). On the next line, type sp_help and execute it. Here is an example:

Help

Renaming a Table

If you find out that the name of a table is not appropriate, you can change it:

  • To change the name of a table in the SQL Server Management Studio, in the Object Explorer, right-click the table and click Rename. Type the desired name and press Enter
  • To change the name of a table with code, type sp_rename, followed by the current name of the table, a comma, and the new desired name of the table. Then execute the code. The formula to use is:
    sp_rename ExistingTableName, TableNewName;
    The names of tables should be included in single-quotes. Here is an example:
    sp_rename N'StaffMembers', N'Employees';
    GO
    In this case, the database engine would look for a table named StaffMembers in the current or selected database. If it finds it, it would rename it Employees. If the table doesn't exist, you would receive an error
Renaming a Table

Deleting a Table

If you have an undesired table in a database, you can remove it:

You can also use sample code that Microsoft SQL Server can generate for you. First display an empty Query Editor. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty Query Editor. Sample code would be generated for you. You can then simply modify it and execute the statement.

Referring to a Table

In your database development assignments, you will write expressions that involve the names of tables. In those expressions, you will need to specify a particular table you want to use. There are three main ways you can do this. To refer to, or to indicate, a table:

Introduction to the Columns of a Table

   

Overview

As mentioned already, a table is a list of values. Because the values are presented in a vertical arangement, the list is considered a column. We also mentioned that a simple table is made of a single list. A more complex table is made of more than one list. This means that a table can contain many columns. In reality, the columns provide a way to better organize the table. Here is an example:

Name Age Gender Relationship
Judie 18 Female Sister
Ernest 24 Male Cousin
Bill 52 Unknown Uncle
David 36 Male Brother
Hermine 12 Unknown Niece

Based on this, a list is simply an arrangement of information and this information, also called data, is stored in tables.

Because a column is a characteristic of a table, it is also called an attribute.

Visually Creating a Column

You can create a column when designing a table or using code. To visually create a column, while the table is displaying in Design View, specify the characteristics of the column using the section on the right side of the right-pointing row header:

Visually Creating a Column

Creating a Column Using Code

We saw that the primary formula to create a table was:

CREATE TABLE TableName

After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(" amd ends with a closing parenthesis ")". Each column must be separated from the next with a comma. You can include all columns on the same line if possible as follows:

CREATE TABLE [SchemaName.]TableName(Column1, Column2, Column3)

Alternatively, to make your statement easier to read, you should create each column on its own line as follows:

CREATE TABLE [SchemaName.]TableName(
Column1,
Column2,
Column3);

You can also indent the columns definitions:

CREATE TABLE [SchemaName.]Country
(
    Column1,
    Column2,
    Column3
);

There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:

ColumnName DataType Options

We also saw that you could use sample code to create a table. This allows you to have more control over the various columns you want the table to have. To do this, open an empty Query Editor and display the Templates Explorer. Expand the Table node. Under Table, you can drag Create Table, Add Column, or Drop Column, and drop it in the Query Editor. If you use dropped Add Column or Drop Column, you can delete the undesired sections of the code and isolate only the part that handles table creation. Here is an example:

--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
	column1 int, 
	column2 char(10)
)
GO

The Name of a Column

Like everything in a database, a column must be identified; that is, a column must have a name. If you are visually creating a table, to specify the name of a column, use a cell under Column Name.

The name of a column:

After respecting these rules, you can add your own rules. In our lessons, here are the rules we will use to name our columns:

You should avoid using the following reserved words of Transact-SQL when naming a column:

aggregate alter and any application
as assembly backup begin between
bigint binary bit break broker
by case catalog catch certificate
char check checkpoint close commit
compute contains continue contract create
credential cursor database date datetime
datetime2 datetimeoffset deallocate decimal declare
default delete deny disable drop
else enable end endpoint event
exec execute false fetch float
foreign from full fulltext function
geography geometry go goto grant
group having hierarchyid if image
in index insert int into
is kill language like login
master merge message money move
nchar next not ntext null
numeric nvarchar on order output
partition persisted print proc procedure
queue real receive remote resource
return returns revert revoke role
rollback rowversion rule save schema
select send set setuser shutdown
smalldatetime smallint smallmoney sql_variant status
table text then time timestamp
tinyint tran transaction trigger true
try type union unique uniqueidentifier
update use values varbinary varchar
view when while with xml

There are some other words you should avoid. If you really want to use one of those words, include the name in double-quotes, such as "Address", or between square brackets: [ and ]. An example would be [Address]. In reality, you can include any column name in double-quotes or in [].

Practical LearningPractical Learning: Starting a Column

Introduction to the Type of Value of a Column

 

The Data Type of a Column

The most important role of a column is to hold some values. For this reason, a column is also called a field. There are various types of values, including names, numbers, etc. Therefore, when creating a column, you must specify the type of values it will hold.

If you are visually creating a column, to specify its type:

  • In the top section of the table, click the column name. Click the arrow of the combo box under the corresponding Data Type area and select the desired type
  • In the top section of the table, click the column. In the botton section, click the arrow of the Data Type combo box and select the data type

If you are programmatically creating the column, after specifying its name, enter its type:

CREATE TABLE TableName
(
    ColumnName Type
);
GO

In reality, the type specifies the amount of memory that each value of the column will need  to get stored in the computer memory. Since there are various kinds of information a database can deal with, SQL and Transact-SQL provide a set of data types.

Introduction to the Type of Value of a Column

Characters

A character is any kind of symbol: alphabetic, numeric, readable, or not. To indicate that a column would use any symbol, specify its data type as CHAR (or char). Here is an example:

CREATE TABLE LibraryMembers
(
    Gender char
);
GO

Practical LearningPractical Learning: Setting the Data Type of a Column

  1. Click the box under Data Type header, then click the arrow of its combo box, and select char (it is set to char(10), in the next lesson, we will explain what the number 10 means; for now, ignore it)
  2. To close the table, click its Close button Close
  3. When asked whether you want to save the table, click Yes
  4. Type the name as StudentsGradeScale
  5. Click OK

Natural Numbers Types

Transact-SQL supports various types of natural numbers, also called integers. The length of an integer is the number of bytes it can hold. Transact-SQL supports the following integer types:

Binary Values

Binary Values

The binary data type is used for a column that would hold natural numbers. The value is considered a series of bits and each bit can be treated, checked or used individually. A binary value is usually used to carry various pieces of information where the pieces must be treated as a group; that is, they must be carried together. For example, consider the characteristics of a file. A file can be hidden, compressed, private, locked, etc. A file can have only one, only two, or many of these characteristics. A single binary value can be used to carry a combination of these pieces of information. In most cases, the person (or the system) who (or that) creates the binary value must specify what piece of information each bit holds. Then, when it comes time to use the value, the person (or the system) who (or that) wants it must check the value of a bit or of a combination of bits to see what it produces and retrieve the desired value (if you want to get more information, try to find out how the messages in Microsoft Windows (Win32) work or how file attributes are stored in a file).

The value of a binary type can be stored as a normal integer. Use the binary data type if all values of the column would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.

Decimal Numbers

A decimal is a number that can have a period (or the character used as the decimal separator as set in the Control Panel) between the digits. An example is 12.625 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut.

If you anticipate a column to hold decimal values, specify its data type as numeric or decimal (either decimal or numeric would produce the same effect in Microsoft SQL Server). Here is an example:

CREATE SCHEMA Administration
GO

CREATE TABLE Administration.StudentsGradeScale
(
	LetterGrade char,
	MinRange decimal,
	MaxRange DECIMAL,
	MinPercent Decimal,
	MaxPercent decimal,
	Descriptor nchar
);
GO

Real Numeric Types

A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number. To let you use floating-point numbers on a column, Transact-SQL provides the float and the real data types. You can use any of them. Here is an example:

-- This table holds information to evaluate 
-- the commissions earned by employees of a furniture store
CREATE TABLE EmployeesCommissions
(
    TransactionMinimum real,
    TransactionMaximum REAL,
    CommissionBase Real,
    CommissionRate real
);
GO

Money

If a column will store monetary values, you can specify its data type as money. A field with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807.

Small Money

While the money data type can be used for large quantities of currency values, the smallmoney data type can be applied to a column whose values cannot be lower than -214,748.3648 nor higher than 214,748.3647.

The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.

Boolean Values

A Boolean value is a piece of information stated as being true or false, On or Off, Yes or No, 1 or 0. To support such values, Transact-SQL provides a data type named BIT (or bit) that you can apply to a column. Here is an example:

CREATE TABLE TruckDriver
(
    IsOrganDonor bit
);
GO

A SQL Variant Type

Transact-SQL (and many other languages) provides data types that can be used to hold almost any type of value, including values of types we have already reviewed. Because these types can be vague and difficult to identify, you should hardly use them.

Transact-SQL provides the sql_variant data type. It can be used in place of any of the data types we have seen so far. This means that if you create a column of type sql_variant , the column that store any type of value.

Geometric Value-Based Columns

All of the data types we have used so far provided their values in a single and simple format. In fact, those values were based on types we have studied since elementary school. Transact-SQL supports values that are based on coordinates of a geometrical shape or on space. These are referred to as spatial types.

Transact-SQL provides support for coordinates of a geometric figures. It does this through a data type named geometry. To create a column for such a type, you can select or apply it:

Spatial Type Columns

Geographical Location-Based Columns

Transact-SQL supports geographical locations. This is done using a data type named GEOGRAPHY. To create a column that stores the geographic locations of items, select or apply this data type:

Spatial Type Columns

User-Defined Types

Microsoft SQL Server allows you to create an alternate name for one of the data types we have seen so fare and use that name where you would use the associated data type. You don't create a data type. You define a name that is easier for you to recognize. A user-defined data type (UDT) is a technique of creating a name for a data type for one of the existing Transact-SQL data types.

Before creating a user-defined data type, you must be familiar with the existing types. You can do this visually or programmatically.

To visually create a UDT, in the Object Explorer, expand a database, expand its Programmability node, and expand the Types item. Under Types, right-click User-Defined Data Types and click New User-Defined Data Type...

User-Defined Types

This would open the New User-Defined Data Type dialog box. The first piece of information you must provide is the schema that will own the new type. Normally, a default schema is provided and you can just accept it. Otherwise, if you had previously created a schema and you want to use it, click the button on the right side of the Schema text box, select it and click OK.

The two most important pieces of information you must provide are a name for the new type as alias and the Transact-SQL type on which it will be based. The name must follow the rules of names in Transact-SQL. In the Data Type combo box, select the data type of your choice. Of course, you must know what type you want to use. Here is an example:

New User-Defined Data Type

After entering and selecting the desired information, click OK.

To create a UDT with code, the basic formula to use is:

CREATE TYPE AliasName FROM BaseType

To get assistance from template code, open a Query Editor. From the Templates Explorer, expand the User-Defined Data Type node. Drag Create User-Defined Data Type and drop it in the Query Editor. Skeleton code will be generated for you:

-- ================================
-- Create User-defined Data Type
-- ================================
USE <database_name,sysname,AdventureWorks>
GO

-- Create the data type
CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,Phone> 
	FROM <base_type,,nvarchar> (<precision,int,25>) <allow_null,,NULL>

-- Create table using the data type
CREATE TABLE <table_name,sysname,test_data_type>
(
	ID int NOT NULL,
	Phone <schema_name,sysname,dbo>.<type_name,sysname,Phone> NULL
)
GO

You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:

CREATE TYPE NaturalNumber FROM int;
GO

In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:

CREATE TYPE NaturalNumber FROM int;
GO
CREATE TYPE Boolean FROM bit;
GO

Columns Review

  

Introduction

To see the structure of a table in the SQL Server Management Studio, in the Object Explorer, you can expand it:

Tables

To view the columns of a table using SQL code, in a Query Editor, type sp_columns followed by the name of the table the columns belong to. Then execute the code. Here is an example:

Viewing the Properties of Columns of a Table

The bottom section displays the list of columns in the COLUMN_NAME column and other characteristics on the right columns.

The Properties of a Column

A column on a table controls what kind of data is appropriate for that particular column. The characteristics that identify or describe such a table are defined as its properties. Two primary properties are particularly important and they are required for each column: the name and the data type. Besides these, some other properties can be used to further control the behavior of a field.

Besides the name and data type, you can control the columns of a table using the Columns property sheet in the lower section of the table in Design View. These properties sometimes depend on the data type of the column. Therefore, to specify the properties of a column, you must first select it in the upper section of the table. This selection can be done by just clicking either the name or the data type. Then you can click the first field in the lower section, select the desired property and type the necessary value:

Column Properties

As an alternative to using the Design View of a table, to see the characteristics of a column, in the Object Explorer, expand the database and the Tables node. Expand, the Columns node:

Column

Any of these actions would display the Column Properties dialog box:

Column Properties

Another way to see the properites of a column is bay calling COLUMNPROPERTY(). Its syntax is:

COLUMNPROPERTY(id, column, property) RETURNS int/bit;

COLUMNPROPERTY has three sections and all are required. It produces a value that depends on the type of information you want. It can produce an integer or a Boolean (bit) value.

The first section is the integral identifier of the table that holds the column. The second section is the name of the column. The third section specifies the type of information you want. For example, to know the maximum number of characters that a character-based column allows, you can pass the third section as PRECISION (case-insensitive).

Description

Common and enabled for all fields, the description is used for a sentence that describes the column. You can type anything on that field.

Collation

Because different human languages use different mechanisms in their alphabetic characters, this can affect the way some sort operations are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.

To specify the collation of a column when creating in, type COLLATE, followed by the desired collation code. Here is an example:

CREATE TABLE Customers(
    FullName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
);

Columns Maintenance

 

Introduction

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.

When making a change on a column, you are also said to alter the table. One of the operations involved with modifying a column consists of changing its data type. To support this operation, the SQL starts with the following formula:

ALTER TABLE TableName

When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.

Columns Maintenance

Adding a New Column

After a table has already been created, you can still add a new column to it.

To visually add a new column in SQL Server Management Studio, in the Object Explorer:

New Column

In both cases, in the empty bottom field, enter the necessary information. In SQL, the basic formula to add a new column to a table is:

ALTER TABLE TableName
ADD ColumnName Properties

ColumnName is required. In fact, on the right side of the ADD word, define the column by its name and using all the options we reviewed for columns. Here is an example:

ALTER TABLE StaffMembers
ADD Gender nchar;
GO

When this code executes, a new column named Gender of type nchar will be added to a table named StaffMembers in the current database.

You can also use sample code to add a new column to a table. First display an empty Query Editor and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the Query Editor. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	ADD <new_column_name, sysname, column3>
	    <new_column_datatype,, datetime>
	    <new_column_nullability,, NULL>
GO

Inserting a New Column

To visually insert a new column between two existing one, right-click the column that will succeed it and click Insert Column:

This would create a new empty field. Type the desired name and specify the other options.

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it.

To visually rename a column, in the Object Explorer:

Renaming a Column

In SQL, to change the name of a column, first open an empty Query Editor. In a Query Editor, use the following formula of sp_rename:

sp_rename 'TableName.ColumnName', 'NewColumnName', N'COLUMN'

Then execute the statement. sp_rename and 'COLUMN' are required. TableName is the name of the table that the column belongs to. ColumnName is the current name of the column. NewColumnName is the desired name you want to give to the column. Here is an example:

sp_rename N'StaffMembers.FullName', N'EmployeeName', N'COLUMN';
GO

When this code executes, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.

Changing the Data Type of a Column

To visually change the data type of a column, open the table in Design View, locate the column under Column Name, and change its type under Data Type.

The formula to programmatically change the data type of a column is:

ALTER TABLE TableName
ALTER COLUMN ColumnName NewDataType

Here is an example:

CREATE SCHEMA HumanResources;
GO
CREATE TABLE HumanResources.Genders
(
    Gender char
);
GO
ALTER TABLE HumanResources.Genders
ALTER COLUMN Gender tinyint;
GO

Deleting a Column

If you have an undesired column that you don't want anymore in a table, you can remove it:

Microsoft SQL Server can also generate sample code you can use to delete a column from a table. Before doing this, first display an empty Query Editor and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the Query Editor. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--============================================
-- Drop column template
--
-- This template creates a table, then it  
-- drops one of the columns of the table.
--============================================
USE <database, sysname, AdventureWorks>
GO

-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	DROP COLUMN <new_column_name, sysname, column3> 
GO

Practical LearningPractical Learning: Ending the Lesson

Exercises

  

Lesson Summary Questions

Lesson Review Questions

  1. What are the main components of a table in a database (Select 2)?
    1. Data Types
    2. Attributes
    3. Columns
    4. Rows
    5. Views
  2. Which of the following are keywords in Transact-SQL (Select 2)?
    1. ASM
    2. TYPE
    3. EXPLICIT
    4. DOUBLE
    5. ALTER
  3. Which of the following are keywords in Transact-SQL (Select 2)?
    1. COLUMNS
    2. DROP
    3. CONTINUE
    4. REMOVE
    5. TABLE
  4. What is the formula to create a table?
    1. CREATE TABLE [SchemaName.]TableName
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
    2. CREATE OBJECT [SchemaName.]TableName
      (
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      )
    3. CREATE TABLE [SchemaName.]TableName
      (
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      )
    4. EXECUTE TABLE [SchemaName.]TableName
      BEGIN
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      END
    5. CREATE OBJECT:[SchemaName.]TableName
      AS
      BEGIN
          ColumnName_1 DataType Options,
          ColumnName_2 DataType Options,
          ColumnName_n DataType Options
      END
  5. What is wrong with the following code?
    CREATE TABLE #Table
    (
    	EmplNbr nchar(6),
    	name nvarchar(60)
    )
    1. Nothing
    2. The name Table cannot be used as the name of a table because Table is a reserved keyword
    3. The code must specify that EmplNbr is unique, which it does not
    4. The name of a column must start in uppercase, as in Name
    5. The creation of a table must end with a semi-colon
  6. What is the name of the stored procedure used to change the name of a table?
    1. sp_rename
    2. sp_changename
    3. sp_move
    4. sp_adjust
    5. sp_table
  7. What is the expression used to modify the structure of a table?
    1. ADJUST TABLE TableName
    2. MODIFY TABLE TableName
    3. SETUP TABLE TableName
    4. ALTER TABLE TableName
    5. CHANGE TABLE TableName
  8. What is wrong with the following code?
    CREATE TABLE Contractors
    (
        ContractCode char,
        [Date Hired] date,
        Hourly Salary money
    );
    1. Nothing
    2. You cannot use a space in the name of a column
    3. When creating a table, you cannot use a mixture of one-word names and names that contain spaces
    4. There is a comma missing after money
    5. The expression Hourly Salary is not well-formed
  9. What's the formula to add a new column to an existing table?
    1. ADD ColumnName Properties
      ALTER TABLE TableName
      
    2. SELECT TABLE TableName
      INSERT ColumnName Properties
    3. ALTER TABLE TableName
      ADD ColumnName Properties
    4. EXECUTE sp_addcolumn ColumnName Properties
      TO TABLE TableName
      
    5. CHANGE TABLE TableName
      INSERT COLUMN ColumnName Properties
  10. What symbol is used to create a temporary table?
    1. @
    2. #
    3. %
    4. &
    5. !
  11. What object contains all the data of a database?
    1. database
    2. table
    3. view
    4. trigger
    5. index
  12. What is the expression used to start creating a table?
    1. ADD TABLE TableName
    2. CREATE TABLE TableName
    3. SETUP TABLE TableName
    4. SELECT TABLE TableName
    5. TRIGGER TABLE TableName
  13. What is the formula used to rename a table?
    1. RENAME ExistingTableName TO TableNewName;
    2. EXECUTE sp_change ExistingTableName, TableNewName;
    3. sp_changename ExistingTableName, TableNewName;
    4. sp_rename ExistingTableName, TableNewName;
    5. CHANGE FROM ExistingTableName TO TableNewName;
  14. What's the basic formula to delete a table?
    1. DELETE OBJECT:TableName
    2. REMOVE TABLE TableName
    3. DROP TABLE TableName
    4. DROP TABLE SET TableName = NULL
    5. DELETE TABLE = TableName

Answers

  1. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Right Answer
    5. Wrong Answer
  2. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  3. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  4. Answers
    1. Right Answer: There is nothing wrong with that code
    2. Wrong Answer: Yes, Table is a keyword but if you are creating the table as a temporary object, you can precede it with #
    3. Wrong Answer: Although that would be a good idea, it is not a requirement
    4. Wrong Answer: That's not a rule
    5. Wrong Answer: That's a good idea but not a rule
  5. Answers
    1. Right Answer: sp_rename is used to rename an object, such as a table
    2. Wrong Answer: There is no such a stored procedure as sp_changename
    3. Wrong Answer:
    4. Wrong Answer:
    5. Wrong Answer:
  6. Answers
    1. Wrong Answer: There is no such an expression as AJUST TABLE
    2. Wrong Answer: There is no MODIFY keyword in Transact-SQL
    3. Wrong Answer: There is no SETUP TABLE in Transact-SQL
    4. Right: To modify a table, you start with ALTER TABLE followed by appropriate options
    5. Wrong Answer: There is no CHANGE TABLE expression in Transact-SQL
  7. Answers
    1. Wrong Answer: There is something wrong with the code
    2. Wrong Answer: Yes, you can use space in a name, providing you follow some rules
    3. Wrong Answer: Yes, you can use a mixture of one-word names and names that contain spaces
    4. Wrong Answer: There must be no comma after the last column
    5. Right: Either the column's name should be in one word as in HourlySalary or its name must be included in square brackets as in [Hourly Salary]. [HourlySalary] would be valid too
  8. Answers
    1. Right: Normally, as long as you follow some rules, any type of column can be added to an existing table
    2. Wrong Answer: Not just string-based columns
    3. Wrong Answer: The rules for adding new columns have nothing to do with unique values
    4. Wrong Answer: Yes, a column that uses a CHECK constraint can be added to an existing table
    5. Wrong Answer: The name of a new column has nothing to do with adding it to an existing table
  9. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  10. Answers
    1. Wrong Answer: @ is used with variables
    2. Right Answer: To create a temporary table, start its name with #
    3. Wrong Answer: % has nothing to do with temporary tables
    4. Wrong Answer: & has nothing to do with creating a temporary table
    5. Wrong Answer: ! has nothing to do with temporary tables
  11. Answers
    1. Wrong Answer:
    2. Right Answer: That's the right formula
    3. Wrong Answer:
    4. Wrong Answer:
    5. Wrong Answer:
  12. Answers
    1. Wrong Answer:
    2. Wrong Answer:
    3. Wrong Answer:
    4. Right Answer: That's the right formula
    5. Wrong Answer:
  13. Answers
    1. Wrong Answer: There is no such expression as ADD TABLE
    2. Right Answer: To create a table, you start with CREATE TABLE followed by the appropriate options
    3. Wrong Answer: There is no SETUP TABLE expression to create a table
    4. Wrong Answer: It is wrong to use SELECT in such an expression
    5. Wrong Answer: The TRIGGER and the TABLE keywords are not combined like that
  14. Answers
    1. Wrong Answer:
    2. Wrong Answer:
    3. Right Answer: That,s the right formula
    4. Wrong Answer:
    5. Wrong Answer:

Lesson Summary Questions

  1. What permissions can be used on a table (Select 3)?
    1. CONNECT
    2. SELECT
    3. DELETE
    4. DESIGN
    5. UPDATE
  2. Which ones of the following are database permissions (SELECT 3)?
    1. ALTER ANY TABLE
    2. ALTER ANY USER
    3. JUSTIFY
    4. AUTHENTICATE
    5. TAKE OWNERSHIP
  3. What permission of a database is related to a table?
    1. CREATE TABLE
    2. ALTER ANY TABLE
    3. REFERENCE TABLE
    4. SHOW TABLES
    5. UPDATE RECORDS

Answers

  1. Answers
    1. Wrong Answer: The CONNECT permission is not part of a table
    2. Right Answer: The SELECT permission can be used on a table
    3. Right Answer: DELETE is valid table permission
    4. Wrong Answer: There is no so a permission as DESIGN
    5. Right Answer: The UPDATE permission exists for tables
  2. Answers
    1. Wrong Answer: There is not an ALTER ANY TABLE permission in Transact-SQL
    2. Right Answer: The ALTER ANY USER permission allows a person to permissions on a user account
    3. Wrong Answer: There is no permission named JUSTIFY in Transact-SQL
    4. Right Answer: AUTHENTICATE is a valid permission
    5. RRight Answer: There is a permission named TAKE OWNERSHIP
  3. Answers
    1. Right Answer: The CREATE TABLE permission allows to left a user create tables on a database or it can be used to prevent a user from creating tables
    2. Wrong Answer: There is no ALTER ANY TABLE permission in Transact-SQL
    3. Wrong Answer: There is no permission named REFERENCE TABLE
    4. Wrong Answer: There is no SHOW TABLES permission
    5. Right Answer: There is no UPDATE RECORDS permission

Practice Questions

 

Monson University

  1. Using Microsoft SQL Server Management Studio, create a table named Departments in the Administration schema of the MonsonUniversity database. The table should have the following fields
  2. Create another table named Employees and that belongs to the Administration schema

Kolo Bank

  1. Using a Query Editor, create a database named KoloBank. Name its primary file as KoloBank.mdf and its log file as KoloBank.ldf. Store both files in a folder named KoloBank1 on the C: drive (you must (manually) create that folder)
  2. In the KoloBank database, create a schema named HumanResources

Fun Department Store

  1. Using PowerShell, create a database named FunDS. Name its primary file as FunDepartmentStore.mdf and its log file as FunDepartmentStore.ldf. Store both files in a folder named FunDS1 on the C: drive (you must create that folder)
  2. In the FunDS database, create a schema named Inventory

Previous Copyright © 2003-2013, FunctionX Next