SQL Tutorials - 2   

3.1 Alter a table
3.1.1 Renaming a table
3.1.2 Renaming a column
3.1.3 Changing a Column's Datatype
3.1.4 Adding a Constraint
3.1.5 Removing a Constraint
3.1.6 Adding a column
3.1.7 Removing a column
3.1.8 Dropping a table
4.1 Inserting Data into a Table
4.1.1 Adding Rows of data
5.1 Creating multiple table
5.1.1 Normalization
5.2.2 Database Design
5.2.3 Keys
5.2.4 Primary Keys
5.2.5 Foreign Keys
5.2.6 Creating Multiple Tables


3.1 Alter a table

Alter a Table ( DDL )
It may be the case that you need to alter a column's name, add a new column, change the data type of a column, or remove the table altogether. DDL provides a way for you to make such changes.

Alter Table syntax
Existing tables can be altered with an ALTER TABLE statement. An ALTER TABLE statement is part of DDL, and is for altering a table schema only.

We will use table users which we have created Previously in topic (2.2.4 Create a table)

3.1.1 Renaming a table

ALTER TABLE users
 RENAME TO all_users;

3.1.2 Renaming a column

ALTER TABLE all_users
 RENAME COLUMN username TO full_name;

3.1.3 Changing a Column's Datatype

ALTER TABLE all_users
ALTER COLUMN full_name TYPE varchar(25);

3.1.4 Adding a Constraint

As previously explained that column names and data types are a required part of each column definition, whereas constraints are optional.

The syntax for adding constraints can vary depending on the type of constraint we're adding. Some types of constraint are considered 'table constraints' (even if they apply to a specific column) and others, such as NOT NULL are considered 'column constraints'

In general, NOT NULL is always a column constraint. The remaining constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK) can be either table or column constraints. The CREATE TABLE command lets you define both column and table constraints, while most other commands (like ALTER TABLE)

ALTER TABLE all_users
ALTER COLUMN full_name
SET NOT NULL;

In the above example we have added a Constraint that a value can't be kept null.

3.1.5 Removing a Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

3.1.6 Adding a column

ALTER TABLE all_users
ADD COLUMN day char(10)
NOT NULL;

3.1.7 Removing a column

ALTER TABLE all_users DROP COLUMN enabled;

3.1.8 Droping a table

DROP TABLE all_users;

4.1 Inserting Data into a Table

In the previous section we have seen eating a database, and creating, altering, and even deleting tables. These things are all concerned with the structure, or schema, of our database.
In this section we're going to focus on that 'data' and explore some of the various ways that we can use Data Manipulation Language (DML) to add, query, change, and remove data.

Data Manipulation Language (DML)
Data Manipulation Statements are used for accessing and manipulating data in the database. Data Manipulation Statements can be categorized into four different types :
1) insert
2) select
3) update
4) delete

4.1.1 Adding Rows of data

  • We will use the below created table. ( This table creation we have seen in the topic - 2.2.4 create a table )
CREATE TABLE users (
id serial UNIQUE NOT NULL,
username char(25),
City varchar(50),
enabled boolean DEFAULT TRUE

);

  • To view the structure of the table is also referred to as the schema of a table.
DESCRIBE users;

Output -



  • Adding data in rows
INSERT INTO users (id, full_name, enabled)
VALUES (1, 'Alissa Jackson', true),
(1, 'Rovman powell', true),
(2, 'Rahul Tewatia', true),
(3, 'Tim David', false);
  • Viewing data in table
SELECT * FROM users;

Output - 




5.1 Creating Multiple Tables

The majority of databases you'll work with as a developer will have more than one table, and those tables will be connected together in various ways to form table relationships. We will explore the reasons for having multiple tables in a database and what point to be considered while defining relationships between different tables.

5.1.1 Normalization

When there is lot of data then there are other issues as well. So, Normalization is the process of structuring and handling the relationship between data to minimize redundancy in the relational table and avoid the unnecessary anomalies properties from the database like insertion, update and delete. It helps to divide large database tables into smaller tables and make a relationship between them. It can remove the redundant data and ease to add, manipulate or delete table fields.

Normalization is a deep topic, and there are complex sets of rules which dictate the extent to which a database is judged to be normalized. These rule-sets, known as 'normal forms', for now there are two important things to remember:

1)The reason for normalization is to reduce data redundancy and improve data integrity
2) The mechanism for carrying out normalization is arranging data in multiple tables and defining relationships between them.

5.2.1 Database Design

The process of database design involves defining entities to represent different sorts of data and designing relationships between tables and row of table (entities).

5.2.2 Keys

Keys are a special type of constraint used to establish relationships and uniqueness. They can be used to identify a specific row in the current table, or to refer to a specific row in another table.

5.2.3 Primary Keys

A necessary part of establishing relationships between two entities or two pieces of data is being able to identify the data correctly. In SQL, uniquely identifying data is critical. A Primary Key is a unique identifier for a row of data.

5.2.4 Foreign Keys

A Foreign Key allows us to associate a row in one table to a row in another table. This is done by setting a column in one table as a Foreign Key and then referring to another table's Primary Key column.

5.2.5 Creating Multiple Tables

We will see with example for creating Primary key and Reference Key. we will choose customer_id as the primary key.
CREATE TABLE Customer(
Customer_id int primary key,
Customer_name varchar(20),
Customer_Address varchar(20),
);


We can add a foreign key to a relation in the following ways 
CREATE TABLE SALES(
    Customer_id int FOREIGN KEY REFERENCES Customer(Customer_id)
    Item_id int,
    Payment_Mode varchar(20), 
)