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 quantitySyntax
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_dateUPDATE 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_phoneA 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_numViews 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_numBy 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 vendorsTo run this view, you do the following:
Write Code Syntax
SELECT * FROM v_vendorlocationResult
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_customeremaillistContact 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.
Comments