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.
SQL 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.
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)||Name||Age|
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.
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:
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.
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.
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:
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
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:
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
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:
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
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
Now if we fire our query as
SELECT * FROM student WHERE name = ‘dan’;
Then output will be
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
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
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.
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
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 ,
SELECT name , age FROM student;
Gives this as output:
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
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
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: