Top 10 Most Used SQL Queries

Create a table: We start by creating an empty table by firing the following query:

CREATE TABLE student  
(  
    id INTEGER PRIMARY KEY,  
    name varchar(20),  
    address varchar(50),  
    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 address & student age. Its structure will be like this:

Now we will start taking most used 10 queries one by one.

1. Insert Query:
   
   To insert the records in a table we use Insert Command. The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
   
   Syntax:
   
   INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
   
   Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.
   
   You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows:
   
   Syntax:
   
   INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
   
   Example:
   
   Specify the column names in order after the table name in INSERT INTO statement and then the values you want after VALUES keyword.
   
   
    

   1. INSERT INTO student (id, name, age) VALUES (‘1’, ‘Nitin’, ‘Noida’, 28);  
   After firing this query, our table will look like:
   
   ID Name Address Age
   
     1     Nitin  Noida     26
   
   We can fire more such queries to fill records in our table:
   
    

   Insert into student (id, name, address, age) values (‘2’, 'Amit', ‘New Delhi ‘23’);  
   Insert into student (id, name, address, age) values (‘3’, 'Rohit', ‘Bareilly’ ‘27’); ​

   
   So the table now becomes:
   
   ID Name Address Age
   
   1    Nitin Noida       26
   2    Amit New Delhi 23
   3    Rohit Bareilly    27
    
2. Select Query: Viewing all records from a table. These results tables are called result-sets.
   
   Syntax: The basic syntax of SELECT statement is as follows:
   
   SELECT column1, column2, columnN FROM table_name;
   
   Here, column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax:
   
   SELECT * FROM table_name;
   
   Example:
    

   1. SELECT * FROM student;  
   The result of this query will be a display of all rows present in the table.
   
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 27
   
   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;
   
   Result is:
   
   ID Name Address Age
   
   2 Amit New Delhi 23
   1 Nitin Noida 26
   3 Rohit Bareilly 27
   
   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. Count is useful in counting the number of records.
   
   Syntax: SELECT COUNT(1) FROM table_name;
   
   Example: SELECT COUNT(1) FROM student;
   
   The output of this query will be:
   
   ID Name Address Age
   
     1 Nitin Noida 26
   
   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:
   
   ID Name Address Age
   3 Rohit Bareilly 27
   
   We can also check sum of a numeric column.
   
   For example: SELECT sum(age)FROM student;
   
   It will give 76 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,
   
   Syntax: DELETE FROM student WHERE [condition];
   
   Example: DELETE FROM student WHERE name = ‘Rohit’;
   
   This query will delete the entire row, or more than one row, from table ‘student’ where ‘name’ column have value ‘Rohit’.
   In our case, the result of this query will will look like the following table:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   
   2 Amit New Delhi 23
   
   
5. Changing data in existing records in a table:
   
   Suppose we want to change the age of a student named ‘Rohit’ in our table. We would fire this query:
   
   Syntax:The basic syntax of UPDATE query with WHERE clause is as follows:
   
   UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition];
   
   Example:
   
   UPDATE student SET age = 28 WHERE name = ‘Rohit’;
   
   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:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 28
   
   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 ‘Rohit’. In this case, age of all students with the name ‘Rohit’ will be updated to 28. 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 = ‘Rohit’ 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.
   
   
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 a company, I may be aware of the fact that there is an employee called Nitin in our Company as I might have heard other managers talking about him. Now I want to see entire records of Nitin but I am not sure how he spells his name. Whether it is ‘Nitin’ OR ‘Netin’. In this case we can use ‘LIKE’ operator provided by SQL.
   
   We will fire the following query:
   
   SELECT * FROM student WHERE name LIKE ‘n%n’;
   
   Output of this query will be:
   
   ID Name Address Age
   
     1 Nitin Noida 26
   
   
7. Using more than one condition in WHERE clause to retrieve records:
   
   To understand the requirement of using this parameter, let us first insert two more rows in our table. Try adding two rows in our table ‘student’ with ID as 4 and 5, name as ‘Suchi’ and age as 22 and 24.
   
   Our table now becomes as:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 27
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   Now if we fire our query as:
   
   SELECT * FROM student WHERE name = ‘suchi’;
   
   Then output will be:
   
   ID Name Address Age
   
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   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 = ‘suchi’ AND age = 24;
   
   We get the following output:
   
   ID Name Address Age
   
     5 Suchi Patna 24
   
   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 = ‘suchi’ OR age > 23
   
   Output will be:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   3 Rohit Bareilly 27
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   You can use different conditions like AND, OR , <, > in a combination or individually in WHERE clause to fetch the desired rows.
   
   
8. Viewing only selected columns from a table:
   
   If we fire a query like:
   
   SELECT name FROM student WHERE age > 25;
   
   The following output is displayed:
   
   Name
   
   Nitin
   Rohit
   
   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, address FROM student;
   
   Gives this as output:
   
   Name Address
   
   Nitin Noida
   Amit New Delhi
   Rohit Bareilly
   Suchi Lucknow
   Suchi Patna
   
   You can also change the sequence of columns to be displayed on your screen.
   
   For example:
   
   SELECT age, name FROM student;
   
   It will give the following output:
   
   Age Name
   
   26 Nitin
   23 Amit
   27 Rohit
   22 Suchi
   24 Suchi
   
   
9. Know the structure of table:
   
   It happens with me quite often that I create a table in my database and forget what all columns it has and which column is primary key. With the help of a 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 SQLite3 the 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;
    
    This query gives the Query Cost of all operations.
    
    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 up the reason behind a slow query.