SQL-Working with tables:

A database most often contains one or more tables. Each table is identified by a name (e.g. “Student” or “Department”) and contains records (rows) with data.

What is a Table?

First of all, we learn about table in SQL, Table is nothing but a simple form of data storage.

Table is a collection of data, presented in terms of rows and columns. In DBMS terms, table is known as relation and row as tuple.

Note: A table has a specified number of columns but can have any number of rows.

Let’s see the below example of the Student table.

In the above table, “Student” is the table name, “city_name”, “gender” and “population” are the column names.

The combination of data of multiple columns forms a row e.g. “California”, “Male” and 90 are the data of one row.

How to create a table:

Create a Table using CREATE statement.

The CREATE TABLE is used to create a table. We must specify the following information.

1. The name of the new table specified after the CREATE TABLE keyword.
2. The name and definition of the columns separated by commas.

CREATE TABLE  [tablename]
(
    [columnname1]  [datatype] [constraints],
    [columnname2]  [datatype] [constraints],
);


CREATE TABLE  Product
(
    product_Id      INT       NOT  NULL ,
    product_name   VARCHAR(50)      NOT NULL,
    product_price   DECIMAL(8,2)    NOT NULL,
    product_desc    VARCHAR(1000)   NULL

);

The product_Id column is of type int and will hold an integer.

The product_name and product_desc columns are of type varchar and will hold characters.

Working NULL values:

To require a value in a column we use the NOT NULL keyword. This means a value is required when data is inserted into the table. The default value is NULL when no keyword is specified for a column.

Create a Table using another table.

We can create a copy of an existing table using the create table command. The new table gets the same column signature as the old table. We can select all columns or some specific columns.

If we create a new table using an old table, the new table will be filled with the existing value from the old table.

The basic syntax for creating a table with the other table is:

CREATE TABLE table_name  AS  
SELECT column1, column2,...   
FROM old_table_name WHERE condition; 

The following SQL creates a copy of the Product table.  

CREATE TABLE Product_Copy AS  
SELECT product_Id , product_name,product_price ,product_desc  
FROM Product; 

Modifying the Table:

The ALTER TABLE statement is used to alter the schema of the tables.

Adding a new column

For example, to add a column to a table is.

ALTER TABLE Product 
ADD vendor_Id int;

This statement adds a column vendor_Id to the Product table. The data type must be specified.

Modifying the column

For example, to add a column to a table is.

ALTER TABLE Product 
ALTER vendor_Id bigint;

This statement modifies the datatype of the column vendor_Id int to bigint oh the Product table.

Dropping a column

To drop a column from the table

ALTER TABLE Product 
DROP column vendor_Id;

This statement removes the entire column vendor_Id from the table Product.

Deleting Table:

Tables are deleted using the DROP TABLE statement.

DROP TABLE Product;

This statement deletes the Product table from the database.

Delete the data.

TRUNCATE TABLE Product;

This statement deletes all the data present in the Product table.;

TRUNCATE Table Vs DROP Table

Drop table command delete the table data along with the structure from database. TRUNCATE TABLE doesn’t delete the structure of the table.

Renaming Table:

To rename a table named old_table to new_table, RENAME statement is used

RENAME TABLE old_table TO new_table;

RENAME Student TO  MCA_Students ;

After this statement, the table “Student” will be changed into the table name “MCA_Students”.

We  can change the name of the table in the SQL database using ALTER TABLE statement:

ALTER TABLE Student RENAME TO  MCA_Students;  

Add or insert the data into the table:

Working with tables means we can store the collection of the data logically organized into row-column format.

How can we store the data into a table?  using The INSERT INTO statement is used to insert new records in a table.

There are 2 ways we can insert the data into a table.

1. In this statement we specify both the column names and values. Syntax as below 

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...);

Ihe following SQL statement inserts a new record in the “Product” table:

Example:

INSERT INTO Product(product_Id, product_name, product_price, product_desc)
VALUES (1, 'iPhone 15', 80000, 'iPhone 15 features a durable color-infused glass and aluminum design.' );

2. If we are adding values for all the columns of the table, we do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Syntax as below.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

INSERT INTO Product( product_name, product_price)
VALUES ('iPhone 15', 80000);

Insert Multiple Rows

It is also possible to insert multiple rows in one statement.

To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:

Example:

INSERT INTO Product(product_Id, product_name, product_price, product_desc) 
VALUES 
(1, 'Sun glasses’, 5000, 'Cooling glasses premium quality'), 
(2, 'Umbrella’, 500, 'Best quality premium color'),
(3, 'Vneck TShirt’, 3000,’Comfortable and affordable’);

At a time 3 records will be inserted.

Update the data in the table – SQL UPDATE Statement:

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Product 
SET product_price= 50000 
WHERE product_Id= 1;

The above SQL statement updates the record(s) in the product table which are matching to the product_Id = 1 with a new product_price.

Update the data from the table – SQL DELETE Statement:

The DELETE statement is used to delete existing records in a table..

Syntax:

DELETE FROM table_name WHERE condition;

The following SQL statement deletes the record(s) from the product table which are matching to the product_Id = 1 

DELETE FROM Product WHERE product_Id=1;

To delete all rows in a table without deleting the table, means that the table structure, attributes, and indexes will be intact.

DELETE FROM table_name;

Example: DELETE FROM Product;

Read the data from the table – SQL SELECT Statement:

The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more tables in the database.

It also retrieves the selected data that follow the conditions we want.

By using this command, we can also access the particular record from the particular column of the table.

Syntax:

SELECT column1, column2, ... 
FROM table_name WHERE condition;

Select ALL columns:

Here we want to return all columns, without specifying every column name, you can use the

SELECT * 

“*” represents all the columns of the table.

 Syntax:

SELECT * FROM product;

Above statement returns all the column and rows data from the product table.

Select specific column:

SELECT product_name,product_price FROM Product;

Above statement returns only 2 columns and all rows’ data from the product table.

Select specific data:

SELECT * FROM product where prouct_id = 1;

Above statement returns the rows data from the product table which are marching product_id = 1.

SELECT DISTINCT Statement:

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Syntax:

SELECT DISTINCT column1, column2, ... 
FROM table_name;

Example:

SELECT DISTINCT product_name FROM Product;

The statement retrieves unique product names from the product table..