top of page
Search

Creating And Manipulating Tables In SQL Server | Update, Delete and View In SQL Server




Table of contents

  • Creating and manipulating tables

  • NULL VALUE

  • Alter Table Add Primary Key

  • Two Primary Keys

  • Set Default Column Value

  • Update Table

  • Deleting Tables

  • Views

  • Views to reformate data

  • Views to filter data

  • Views with calculated fields


A CREATE TABLE statement takes a series of parameters and follows a set syntax.


In a CREATE table statement we know that it is creating a Table with a name, but what we do not know is what the information between the ( ) is for.


Well, there are the column names and the column data types that the table contains. A column within a table is simply an item in which we are storing data.


An example of such a statement follows:

Sample Basic Syntax


CREATE TABLE table_name 
( 
column_name1 data_type, 
column_name2 data_type 
)

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type )


EXAMPLE:

Let’s CREATE a new table named Customers2 with nine columns. The table definitions are enclosed with parentheses. Each column name must be unique. Each column is a certain data type. The columns are separated by commas.


Lets also specify that the cust2_id column be an Identity Key. Identity keys automatically generate new unique id number when INSERTING new record. In this case we set the Identity number to start at a seed of 1 with an increment of 1.


Write Exercise Code:

If customers2 table already exists then first DROP the table. 


DROP TABLE customers2

Create new table called customers2. To do this, use the following syntax:


CREATE TABLE customers2 
( 
cust2_id INT NOT NULL IDENTITY(1,1), 
cust_name NCHAR(50) NOT NULL , 
cust_address NCHAR(50) NULL , 
cust_city NCHAR(50) NULL , 
cust_state NCHAR(5) NULL , 
cust_zip NCHAR(10) NULL , 
cust_country NCHAR(50) NULL , 
cust_contact NCHAR(50) NULL , 
cust_email NCHAR(255) NULL 
);

NULL VALUES

When creating a table you must specify which columns will allow NULL VALUES. A NULL VALUE is no VALUE or lack of a VALUE. The following example requires no Null VALUES. In other words, it requires data in every field and assures that no columns will be INSERTED with no VALUE.


Write Exercise Code:

Create new table called orders. To do this, use the following syntax:


CREATE TABLE orders2 
( 
order2_num INT NOT NULL IDENTITY(1,1), 
order_date DATETIME NOT NULL , 
cust2_id INT NOT NULL 
);

The next table to create is named vendors.


Write Exercise Code:

Create new table called vendors2. To do this, use the following syntax:

CREATE TABLE vendors2 
( 
vend2_id INT NOT NULL identity(1,1), 
vend_name NCHAR(50) NOT NULL, 
vend_address NCHAR(50) NULL , 
vend_city NCHAR(50) NULL , 
vend_state NCHAR(5) NULL , 
vend_zip NCHAR(10) NULL , 
vend_country NCHAR(50) NULL 
);

Let’s create several more tables that we will use in the next exercises. The table we will create is products2.


Write Exercise Code:

Create new table called products2. To do this, use the following syntax


CREATE TABLE products2 
( 
prod2_id int NOT NULL identity(1,1), 
Vend2_id INT NOT NULL, 
prod_name NCHAR(255) NOT NULL , 
prod_price MONEY NOT NULL, 
prod_desc NTEXT NULL 
);


Let’s create another table named orderitems2.


Write Exercise Code:

Create a new table called orderitems2. To do this, use the following syntax: 

CREATE TABLE orderitems2 
( 
order2_num INT NOT NULL identity(1,1), 
order_item INT NOT NULL , 
prod2_id INT NOT NULL , 
quantity INT NOT NULL , 
item_price MONEY NOT NULL 
);

Finally, let’s create another table named productnotes2.


Write Exercise Code:

Create new table called productnotes2. To do this, use the following syntax: 


CREATE TABLE productnotes2 
( 
productnote2_id INT NOT NULL IDENTITY(1,1), 
prod2_id INT NOT NULL, 
note_date DATETIME NOT NULL, 
note_text NTEXT NULL 
);


Alter Table Add Primary Key

We just created some tables. Now we want to modify them. To modify a table to add a Primary key use the ALTER TABLE command.


Write Exercise Code:

To do this, use the following syntax: 


ALTER TABLE customers2 
ADD CONSTRAINT pk_customers2 PRIMARY KEY (cust2_id); 

ALTER TABLE orders2 
ADD CONSTRAINT pk_orders2 PRIMARY KEY (order2_num);

ALTER TABLE products2 
ADD CONSTRAINT pk_products2 PRIMARY KEY (prod2_id);
ALTER TABLE vendors2 
ADD CONSTRAINT pk_vendors2 PRIMARY KEY (vend2_id);

ALTER TABLE productnotes2 
ADD CONSTRAINT pk_productnotes2 PRIMARY KEY (productnote2_id);


Two Primary Keys

So far the tables we have created only set one column as the Primary key. That column will always be unique. We can also set more than one column as a Primary Key.


Write Exercise Code:

Here the combined columns must be unique.


ALTER TABLE orderitems2 
ADD CONSTRAINT pk_orderitems2 PRIMARY KEY (order2_num, order_item);

Set Default Column VALUE

You can also set the default VALUE of a particular column. By default no VALUE is specified when a row is inserted. By setting the default VALUE that VALUE will automatically be added when you INSERT new record. The first example, we set the default VALUE of the quantity column to 1 in the orderitems2 table.


Write Exercise Code:

To do this, use the following syntax: 


ALTER TABLE orderitems2 
ADD DEFAULT 1 FOR quantity

Syntax

Here we set the default VALUE on the order_date column to the system function GetDate() in the orders2 table. This will automatically add the current date/time when new record is added.


Write Exercise Code:

To do this, use the following syntax:



ALTER TABLE orders2 
ADD DEFAULT GETDATE() FOR 
order_date

UPDATE Table

To update a table, again, we use the ALTER table statement. The ALTER TABLE statement is used to add or drop columns in an existing table.


Write Exercise Code:

The following adds a column to the vendors2 table. An example of such a statement follows: 


ALTER TABLE vendors2 
ADD vend_phone CHAR(20)

Write Exercise Code:

The following drops a column to the vendors2 table. An example of such a statement follows:


Syntax 

ALTER TABLE vendors2 
DROP COLUMN vend_phone

A common use for the ALTER statement is to define foreign keys. The following examples add foreign keys to the tables you created earlier.


Write Exercise Code:

To do this, use the following syntax to add foreign key on order2_num:


ALTER TABLE orderitems2 
ADD CONSTRAINT fk_orderitems_orders2 FOREIGN KEY (order2_num) 
REFERENCES orders2 (order2_num);

Write Exercise Code:

To do this, use the following syntax to add foreign key on prod2_id:


ALTER TABLE orderitems2 
ADD CONSTRAINT fk_orderitems_products2 
FOREIGN KEY (prod2_id) 
REFERENCES products2 (prod2_id);

Write Exercise Code:

To do this, use the following syntax to add foreign key on cust2_id:


ALTER TABLE orders2 
ADD CONSTRAINT fk_orders_customers2 
FOREIGN KEY (cust2_id) 
REFERENCES customers2 (cust2_id);

Write Exercise Code:

To do this, use the following syntax to add foreign key on vend2_id:


ALTER TABLE products2 
ADD CONSTRAINT fk_products_vendors2 
FOREIGN KEY (vend2_id) 
REFERENCES vendors2 (vend2_id);

Write Exercise Code:

To do this, use the following syntax to add foreign key on prod2_id:


ALTER TABLE productnotes2 
ADD CONSTRAINT fk_productnotes_products2 
FOREIGN KEY (prod2_id) 
REFERENCES products2 (prod2_id);

DELETING Tables

To remove a table from a database you use the DROP statement. Once it is dropped you cannot retrieve. The entire table with data is removed from your database. The only way to restore is from a backup.


Write Exercise Code:

Let’s drop all the new tables we create from the SQLClass database.


An example of such a statement follows: 


DROP Table orderItems2 
DROP Table productnotes2 
DROP Table orders2 
DROP Table products2 
DROP Table vendors2 
DROP Table customers2


Views


Example:

Use the SQLClass database


Views are virtual tables that contain queries that retrieve data when used. Take a look at the following SQL query.


The following query was used in a previous lesson. It is a select statement that retrieves data from three separate tables. You would have to know and understand the table structures and how to create the query and join the appropriate tables.


Syntax:

SELECT cust_name, cust_contact 
FROM customers 
inner join orders on customers.cust_id = orders.cust_id 
Inner Join orderitems on orderitems.order_num = orders.order_num

Views allow you to simplify this process by placing this query in a virtual table called v_productscustomers.


Example:

To create a view you do the following. It is good practice to begin view with v_ to clearly identify it as a view.


Write Code Syntax

Create View v_productcustomers 
AS SELECT cust_name, cust_contact, orderitems.prod_id 
FROM customers 
inner join orders on customers.cust_id = orders.cust_id 
Inner Join orderitems on orderitems.order_num = orders.order_num

By creating a view called v_productcustomers, you simplify the process.


Example:

To use the view in a select statement and filter the data by using the WHERE clause we do the following:


Write Code Syntax

SELECT cust_name, cust_contact 
FROM v_productcustomers 
WHERE prod_id = 'TNT2'


Views to Reformat Data

Views can also be used to reformat data. The following SELECT statement returns the vendor name and location in a single combined calculated column.


Example

The following query combines columns. It also uses the RTrim function to move any spaces.


Write Code Syntax

SELECT RTrim(vend_name)+ ' (' + RTRim(vend_country) + ')' AS vend_title 
FROM vendors 
ORDER BY vend_name

Example

If you regularly need this data returned formatted you might want to create a view that performs this concatenation each time it is needed.


To run this statement as a view, you do the following:


Write Code Syntax

CREATE VIEW v_vendorlocation AS 
SELECT RTrim(vend_name)+ ' (' + RTRim(vend_country) + ')' AS vend_title 
FROM vendors

To run this view, you do the following:


Write Code Syntax

SELECT * FROM v_vendorlocation

Result

vend_title Bear Emporium (USA)

Bears R Us (USA)

Doll House Inc. (USA)

Fun and Games (England)

Furball Inc. (USA)

Jouets et ours (France)



Views to Filter Data

Views can also be useful when using the WHERE clause. You might want to filter out customers without emails.


Example

To run this statement as a view, you do the following:


Write Code Syntax 

CREATE VIEW v_customeremaillist AS 
SELECT cust_id, cust_name, cust_email 
FROM customers 
WHERE cust_email IS NOT NULL

Example

To run this view, you do the following:


Write Code Syntax

SELECT * FROM 
v_customeremaillist


Contact Us at realcode4you@gmail.com to get help in database coursework, database project, database assignment, database queries, ER diagram and database schema diagram or any other database related topics with an affordable price.
10 views0 comments
bottom of page