SQL queries: The Top 10 Most Used

sqlqueriesUpdated on February 28. 2014

Databases are like big swimming pools. If you don’t know how to swim, that big pool could be a daunting, dark place to be. However, that same pool could be a stunning place to be, full of refreshing water on a sunny day if you know how to swim. Similarly, a huge database could be fun and useful if you know these 10 most basic and simple queries of SQL.

Research has proven that these 10 queries are only 3% of entire query set which can be formulated in SQL. But these 3% cover 90% of regular operations on database. Before we start talking about them, it is good to know that these 10 queries work on all types of SQL engines available in the market. For an introduction to SQL, try this course for students new to SQL queries.

Demystifying SQL

sql queriesSQL is just an interface which helps you communicate with your system’s database using queries.  We rotate our car’s steering wheel without bothering about what mechanical reaction might have taken place in our wheel’s axle. We are only concerned about turning our car. Similarly when we use SQL, we only have to fire simple queries to retrieve data from your database without thinking about internal database operations.

sql1

There are different types of SQL engines available free online.  However, for beginners I recommend SQLite3 which you can download here.

And run it from command line by typing sqlite test.db

Once you’ve got it up, you should have a SQL prompt on your screen

Create a table

We start by creating an empty table by firing the following query

CREATE TABLE student (id INTEGER PRIMARY KEY , name TEXT, age INTEGER);

This will create a table called “student” with 0 rows in it. This table will store records of students. It will have student ID , student name & student age. Its structure will be like this:

ID (Primary key)NameAge

Here, the column ID is a primary key. It means that this column can have only unique values. Also it cannot be left blank. Name column can have characters and age column will have age in numbers.

Try this course for more practical SQL skills.

Now we will start taking those 10 queries one by one.

1.     Inserting records in a table

Inserting data in a table is very easy. Just type the following query

INSERT INTO student (id, name, age) VALUES (‘1’, ‘alan’, 28);

Specify the column names in order after the table name in INSERT INTO statement and then values you want after VALUES keyword.

After firing this query, our table will look like:

ID NameAge

1

alan28

We can fire more such queries to fill records in our table.

insert into student (id, name, age) values (‘2’, 'amy', ‘26’);
insert into student (id, name, age) values (‘3’, 'bob', ‘27’);
insert into student (id, name, age) values (‘4’, 'chris', ‘28’);
insert into student (id ,name, age) values (‘5’, 'dan', ‘26’);

So the table now becomes.

ID NameAge
1alan28
2Amy26
3Bob27
4Chris28
5Dan26

2. Viewing all records from a table.

It is simplest of all & most frequently used query. Just type

SELECT * FROM student;

The result of this query will be a display of all rows present in the table.

IDNameAge
1alan28
2Amy26
3Bob27
4Chris28
5Dan26

We can also use ORDER BY clause in our select statement to arrange the displayed result in a particular order. For example,

SELECT * FROM student ORDER BY age;

Will give you:

IDNameAge
2Amy26
5Dan26
3Bob27
1alan28
4Chris28

The output is arranged in increasing order of age. We can use DESC keyword after the column name in query if we want to arrange the display in decreasing order.

3.     Viewing only selected records from a table

If there are a huge number of rows in a table and we do not want all the records to fill our display screen, then SQL gives us an option to view only selected rows.

SELECT COUNT(1) FROM student;

The output of this query will be

IDNameAge

1

Alan28

If we fire:

SELECT COUNT * FROM student;

It will return the number of rows our table has. We can also use MAX & MIN function in our query. For example, if we want to retrieve details of a student with maximum age, we can fire:

SELECT id , name , MAX(age) FROM student;

We will get:

IDNameAge
1Alan28
4Chris28

We can also check sum of a numeric column. For example,

SELECT sum(age)FROM student;

Will give 135 as output.

Remember, we can use MAX, MIN, SUM functions only with numeric columns. Using these functions with text column will throw an error.

4.     Deleting records from a table

To delete the selected rows from a table, just fire the following query:

DELETE FROM student WHERE name = ‘alan’;

This query will delete the entire row, or more than one rows, from table ‘student’ where ‘name’ column have value ‘alan’.

In our case, the result of this query will be following table

ID NameAge
2Amy26
3Bob27
4Chris28
5Dan26

Break time!

sql“A SQL query goes into a bar, walks up to two tables and asks, “Can I join you?”

I got a good chuckle out of that one. Moving on!

5.     Changing data in existing records in a table

Suppose we want to change the age of a student named ‘Amy’ in our table. We would fire this query:

UPDATE student SET age = 22 WHERE name = ‘amy’;

You might have observed that we are specifying name in ‘’ where values are characters. This is a must.

Now if we fire:

SELECT * FROM student;

We will get the following table as output:

IDNameAge
2Amy22
3Bob27
4Chris28
5Dan26

Be careful while you are firing UPDATE or DELETE queries with the help of the WHERE clause. Suppose in our table ‘student’ there is more than one student with name ‘Amy’. In this case, age of all students with the name ‘Amy’ will be updated to 22. That is why it is always preferred to use PRIMARY KEY in WHERE clause while updating or deleting.

We also need to take care of datatypes in a column while we are changing data in it. A numeric column can have only numbers in it while a text column can have text. This means that if we try to put age = ‘Amy’ in age column using UPDATE statement, SQL will throw an exception. You can learn more about types of errors and exceptions that occur in SQL. More on this with another popular example in this course.

6.    Viewing records from a table without knowing exact details

In real life, when we interact with database, there are major chances that we do not know any of the column values exactly. For example, if I am a data operator in school, I may be aware of the fact that there is a student called Dan in our school as I might have heard other teachers talking about him. Now I want to see entire records of Dan but I am not sure how he spells his name. Whether it is ‘Dan’ OR ‘Den’. In this case we can use ‘LIKE’ operator provided by SQL.

We will fire the following query.

SELECT * FROM student WHERE name LIKE ‘d%n’;

Output of this query will be

IDNameAge
5dan26

7.     Using more than one condition in WHERE clause to retrieve records

To understand the requirement of using this parameter, let us first insert one more row in our table. Now you should try it on your own and I will not tell you the syntax. Try adding a row in our table ‘student’ with ID as 6, name as ‘dan’ and age as 24.

Our table now becomes as

IDNameAge
2Amy22
3Bob27
4Chris28
5Dan26
6Dan24

Now if we fire our query as

SELECT * FROM student WHERE name = ‘dan’;

Then output will be

IDNameAge
5Dan26
6Dan24

So now, we have observed that we were unable to fetch a unique record just by using the name value in WHERE clause. Here arises a need to combine more than one condition in WHERE clause which can be easily done using conditional keywords like AND and OR. For example, if we fire:

SELECT * FROM student WHERE name = ‘dan’ AND age = 24;

We get the following output

ID NameAge
6Dan24

You can also combine AND & OR conditions in WHERE clause to refine your search further more. For example if we fire

SELECT * FROM student WHERE name = ‘dan’ OR age > 25

Output will be

IDNameAge
3Bob27
4Chris28
5Dan26
6Dan24

You can use different conditions like AND , OR , < , > in a combination or individually in WHERE clause to fetch the desired rows. Try doing it yourself.

Learn how to use SQL’s best practices to query and manipulate data held in Oracle relational databases in this course.

8.     Viewing only selected columns from a table

If we fire a query like

SELECT name FROM student WHERE age > 25;

Following output is displayed

Name
Bob
Chris
Dan

We can observe that only names of students are printed. Here we got names of only those students whose age is greater than 25 because of a specified condition in WHERE clause.

We can also use more than one column names in SELECT statement separating them with a ,

For example:

SELECT name , age FROM student;

Gives this as output:

NameAge
Amy22
Bob27
Chris28
Dan26
Dan24

You can also change the sequence of columns to be displayed on your screen. For example:

SELECT age , name FROM student;

Will give the following output

AgeName
22Amy
27Bob
28Chris
26Dan
24Dan

9.     Know the structure of table

It happens with me quite many times that I create a table in my database and I forget what all columns it has and which column is primary key. I thank designers of various SQL engines to keep people like me in their mind while they designed it. With the help of a very simple query you can know complete details about the structure of the table you created.

Different SQL engines have different commands for it. For example in SQLite3the command is

.schema student;

Where as in PostgreSQL it is \d student

MySQL uses the following command describe student;

Where ‘student’ is our table’s name.

10. Checking performance of query

This is an advanced query. It’s particularly useful if you need to figure out why a query is so slow.Just fire the query

EXPLAIN QUERY PLAN SELECT * FROM student;

You can use EXPLAIN before a SQL statement to obtain breakup of the timings of the various parts of query. It’s useful for digging the reason behind a slow query.

In PostgreSQL, you can use EXPLAIN and EXPLAIN ANALYZE before your query. The Beginner’s Guide to PostgreSQL can quide you through the process.

In SQLite3, you can use EXPLAIN QUERY PLAN before your query. To learn more, try Introduction to SQL Training.

And that tops off the Top 10! For more comprehensive training, try these courses below:

Learn how to query and manage data in the Oracle Database Using SQL. Practical, concept-building examples and quizzes.

Oracle SQL fundamentals

Microsoft SQL Server 2012 Certification Training Exam 70-463