SQL- CREATE, INSERT, SELECT, UPDATE and ALTER

minimal explanation, find it is useful and extracting the information from the created table.

In the previous session just went through the SQL history and syntax of creating a table.

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Every table is broken up into smaller entities called fields, The fields in the CUSTOMERS table consist of ID, NAME, AGE , ADDRESS , and SALARY

Source: CUSTOMERS Table

A field is a column in a table that is designed to maintain specific information about every record in the table.

Now we got another doubt what is a record?

A record, also called a row of data, is each individual entry that exists in a table. For Example, there are 7 records in the above CUSTOMERS table.

A Record is well known as a row of the table

A record is a horizontal entity in a table

here this table consists of seven records which include by seeing the table we can say that table namely: ID, NAME, AGE, ADDRESS, SALARY are the rows of the table. Each row carries one certain information about that employee.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

here are each column has its own characteristics, the specific entity of the data which is available in the table.

Now let us create a table to let you know the syntax behind it.

Introduction to some of the statements:

CREATE:-

CREATE TABLE celebs ( id INTEGER, name TEXT, age INTEGER );

1. CREATE TABLE is tells to the SQL you want to create a new table
2. celebs is the name of the table
3. into the celebs we are passing the parameters defining each column.
4. id is the first column, name is the second column, age is the third column these three columns holds three different data types id stores the integer datatype, name stores the character datatype and the last one age stores the integer data types.

its creation of empty table which consists no values as follows

INSERT:-

Data scheme

Now we will try to insert the data into the table our table name is celebs

INSERT INTO celebs (id, name, age) VALUES (1, ‘Justin Bieber’, 22);

HERE INSERT statement inserts a new row(id, name, age)into a table. insert a statement is used to inserting the data into the table row-wise.

INSERT INTO is a clause that adds the specified row or rows

celebs is the name of the table the row is added into it,

(id,name, age) likewise parameters will be accepted into the table or else it can show syntax .maka a note of it.

VALUES is the clause that indicates the data being inserted

(1,'Maya',22 is a parameter identifying the values being inserted.

here parameter is inserted can be seen 1 is an integer,’Maya’ is text, and 22 is an integer by this we can say that there will be error passed parameters and table which we have created previously.

Till now we have created an empty named celebs table and inserted the data row-wise into the table. now how to retrieve the data which we have insert into the table.

SELECT:-

Now the turn of select statement that retrieves the data from a database.

for example, we can say that

=>SELECT name FROM celebs;

=>SELECT age FROM celebs;

like we will try to retrieve each column in the column of the celebs table.

here above syntax says obtaining the data from a certain column that is name column.

can also query data from all columns in a table? yes with SELECT

=>SELECT * FROM celebs;

* is a special wildcard character that we have been using. It allows you to select every column in a table without having to name each one individually. Here, the result set contains every column in the celebs table.

ALTER:-

what if we want one or more columns into a table that is already available, Yes that is possible, have a trial.

By using the ALTER the statement adds a new column to the table.

for example, our celebs table I want to add one more column here is the

=>syntax: ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

.ALTER TABLE is a clause that lets you make the specified changes.

.celebs is the name of the table that is being changed.

. ADD COLUMN is a clause that lets you add a new column to a table:

* twitter_handle is the name of the new column

* TEXT is the data type for a new column

[Bonus Point]: NULL is a special value in SQL that represents missing or unknown data. here the row that existed before the column was added have NULL values for twitter_handles .

yeah!!

=>ALTER TABLE celebs
=> ADD COLUMN twitter_handle TEXT;

above syntax says that altering the table by adding the column name as twitter_handle which is TEXT data, now we want to structure of the table

=>SELECT *FROM celebs;

then here we go,

As far as we are discussed about the CREATE statement, SELECT statement, INSERT statement and ALTER statement.

UPDATE:-

right!!! Now we have created the table insert the data and alter the table now what else neede with updation of the data which we entered into the table

The UPDATE statement edits a row in a table.you can use the UPDATE statement when you want to change existing records. The statement below updates the record with an id value of 4 to have the twitter_handle @taylorswift13

=>UPDATE celebs

=>SET twitter_handles = ‘@taylorswift13’

=>WHERE ID=4;

UPDATE is a clause that edits a row in the table.

celebs is the name of the table

SET is a clause that indicates the which you want to edit column

• twitter_handle is the name of the column
• @taylorswift13 is the new value inserted into the twitter_handle column.

=>'WHERE' IS A CLAUSE THAT INDICATES WHICH ROWA TO UPDATE WITH THE NEW COLUMN NAME.

ID =4 is the row that twitter handle updated to @taylorswift13

Now we gather the previous experience for viewing the whole table is just select *from table name right…

let us try to put on little pieces of code, go on

UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE ID = 4;
SELECT *FROM celebs;

Till now we have gone through the some of the statements,

In the next post, I will try to explore some of the statements in SQL useful for data extraction from data servers. Please stay tuned!!! see yah…

My previous blog link please find: https://medium.com/@jyothiffu/sql-structured-query-language-2348a83cd9c3

**********************KEEP ON PRACTISE*************************