Select, Insert, Update and Delete SQL examples

I remember when I started learning SQL, I was given examples of employee databases, food inventory, cities etc. They all had one thing in common, all of them had nothing to do with my real life. So I have created this unique example of Friends table. So this can be implemented by everyone and it is really cool. Continue reading to find out how.

Before we begin with the tables and queries, first let us try to understand why we are doing this. Almost all of us have a Facebook account, which helps us connect to our friends, find out what they are doing etc. Now, each of us has more than 100 friends, some even have 4000+ friends (really? do you even know them?). Obviously it is not possible for us to remember each one’s name, their job etc. So what if we could have all of this arranged in a table and then find out specifics about each friend or group of friends. Let us see the example.

Table : Friends

Please note that the names, cities, mobile numbers and companies are randomly chosen. Any resemblance to persons living or dead is purely coincidental.

Using SELECT statement in SQL

SELECT statement is used to perform selection of data from the database. In this simple example we will try out basic SELECT queries that can be used to extract data from a single table as per requirement.

Selecting entire table :

SELECT * FROM friends

(Note : Though SQL is not case sensitive, it is a good convention to capitalize SQL keywords.)

The above SQL will display the entire table as it is.

Finding out the total number of records in a table :

SELECT COUNT(*) FROM friends;

Output :

20

Selecting one column at a time :

SELECT name FROM friends;

Output :

Selecting more than one column at a time :

SELECT name, age FROM friends;

Output :

Filtering output based on a criteria :

Say for e.g. you wish to see the names of only female friends, then you can use the WHERE clause for the same.

SELECT name FROM friends WHERE gender='Female';

Output :

Using combination of filters 

Say you wanted to know the names of female friends who work at Google or Yahoo!

Now note that the critieria should be “Female” AND (Google or Yahoo!).

So the corresponding SQL would be :

SELECT name FROM friends WHERE gender = 'Female' AND (company='Google' OR company = 'Yahoo!');

Output :

Notice how David and Frank were not included in the result due to the gender clause.

Also, note the usage of round brackets to isolate the OR clause and then merge it with the AND clause.

Using INSERT statement in SQL

Firstly know that, while creating a table, we define column names and their data types. While doing so we can also set their default values (optional) and/or mark them unique. Apart from that, we can make a column such that it cannot store null values (NOT NULL column) or it can store NULL values.

Now there are 2 ways to insert data in a table.

Case 1 :

Provide values for all the columns in a table. Let’s take the example of Friends table described above.

Following are the columns used in the table : name, gender, location, age, mobile, company.

Now, suppose you made a new friend named John who lives in New York, works at Google and is 33 years old. His mobile number is 987634525. So you can use the following SQL statement to Insert a new entry in the Friends table :

INSERT INTO friends VALUES ('John','Male','New York', 33, '987634515', 'Google');

Note : Since I exactly knew the sequence of columns in the table, I could write the SQL without mentioning the column names.

If you are not sure about the column sequence, write the column names in the statement itself and then specify the values for them in respective sequence.

INSERT INTO friends (name, age, gender, location, company, mobile) VALUES ('John',33, 'Male', 'New York', 'Google',  '987634515');

This will create a new entry in the table with the tables provided in the INSERT statement.

Case 2 :

Suppose your table Friends has certain columns having the default value specified with them. Say for e.g. default value for gender is Male. Then you can skip that column in the INSERT statement and it will consider the default value for that column.

For e.g.

INSERT INTO friends (name, age, location, company, mobile) VALUES ('John',33, 'New York', 'Google',  '987634515');

The above insert statement will create an entry for John with gender as Male, provided the default value for gender is set to Male for the table Friends.

Note : If no default value is set for a particular column and if it is skipped in the INSERT statment, then the system will assign a NULL value to that column for that particular row.

Using UPDATE statement in SQL

Update statement is used to update the values of one or more columns for one or more rows.

Say you wanted to update the mobile number of Ally, you can use the following SQL query :

UPDATE friends SET mobile = '996452314' WHERE name='Ally';

Note : This update statement will try to update the mobile number of all the  friends whose name is Ally. But since our example table contains only 1 friend named Ally, it will update only one row. It is for this reason that we keep a Primary Key for each table. It helps us uniquely identify a particular record. Say for e.g. if you would’ve had another column named SrNo which kept a track of friends, serial numbers starting from 1 till 20. So instead of using the above query, we would have used :

UPDATE friends SET mobile = '996452314' WHERE srno = 1;

Let us check how to update multiple values of a record :

Say we wanted to update mobile number as well as the company name and location for friend named Ally, we can use the following SQL to do the same :

UPDATE friends SET mobile = '996452314', company='L&T', location='Boston' WHERE name='Ally';

Note : The fields that are being updated are separated by a comma ‘,’.

Using DELETE statement in SQL

Delete statement has a syntax similar to that of an update statement. The difference is DELETE removes the record from the table. Another point worth noting is that since DELETE is used to remove one or more records from the table, we need not specify column names to be deleted in the DELETE statement. But we do need to specify the WHERE clause in order to identify particular resultset to be deleted. WHERE clause is not required if we want to delete all the records from a table.

For e.g. Say you wanted to DELETE the record for all Friends aged above 30 years. Following SQL can be used to do the same :

DELETE FROM friends WHERE age > 30;

Incase you wish to delete all the records from the table, use the following SQL :

DELETE FROM friends;

NOTE : THIS WILL DELETE ALL THE RECORDS FROM THE TABLE NAMED FRIENDS. Please execute such statements ONLY IF you know what you are doing.

Hope this tutorial was helpful.