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

Name Gender Location Age Mobile Company
Ally Female New York 24 909549867 Abbott Laboratories
Francesco Male Chicago 30 944983414 Ace Hardware
Lorenzo Male San Francisco 23 972002668 Alliance Rubber Company
Chloe Female Dallas 29 913971025 American Express
Frank Male Huntington 32 992498262 Yahoo!
Aaliyah Female Washington 25 901802210 Yahoo!
Henry Male Fort Worth 25 923027416 Cartoon Network Studios
Abigail Female Minneapolis 28 948707621 Trinity Industries
Isabella Female Montgomery 27 931466772 Rockstar, Inc.
Steve Male Buffalo 20 962828054 Starbucks
Nicole Female San Bernardino 31 921672768 Yahoo!
Elizabeth Female New York 26 956032551 Union Bank
Mark Male Philadelphia 26 935676996 Convergys
David Male Houston 31 903598198 Yahoo!
Megan Female Pittsburgh 21 998017182 Staples Inc.
Katie Female San Diego 30 926164693 Paramount Pictures
Amber Female Arlington 24 990167646 Bank of America
Stephen Male Philadelphia 33 924212730 Diamond Foods
Jason Male Huntington 22 950650316 Electronic Arts
Ashley Female Lubbock 32 913088640 Google

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 :

Name
Ally
Francesco
Lorenzo
Chloe
Frank
Aaliyah
Henry
Abigail
Isabella
Steve
Nicole
Elizabeth
Mark
David
Megan
Katie
Amber
Stephen
Jason
Ashley

Selecting more than one column at a time :

SELECT name, age FROM friends;

Output :

Name Age
Ally 24
Francesco 30
Lorenzo 23
Chloe 29
Frank 32
Aaliyah 25
Henry 25
Abigail 28
Isabella 27
Steve 20
Nicole 31
Elizabeth 26
Mark 26
David 31
Megan 21
Katie 30
Amber 24
Stephen 33
Jason 22
Ashley 32

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 :

Name
Ally
Chloe
Aaliyah
Abigail
Isabella
Nicole
Elizabeth
Megan
Katie
Amber
Ashley

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 :

Name
Aaliyah
Nicole
Ashley

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.

Leave a Reply

Your email address will not be published. Required fields are marked *