Top 10 Most Used SQL Queries

CREATE TABLE student
(
    id INTEGER PRIMARY KEY,
    name varchar(20),
    address varchar(50),
    age INTEGER
);

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN)

INSERT INTO TABLE_NAME (Column1, Column2, .........., ColumnN) VALUES (value1,value2,value3,...value)

INSERT INTO student (id, name, age) VALUES (1, 'Nitin', 28);

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);

SELECT column1, column2, column FROM table_name

SELECT * FROM table_name;

SELECT * FROM student;

SELECT * FROM student ORDER BY age;

SELECT COUNT(1) FROM table_name;

SELECT COUNT(1) FROM student;

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

SELECT SUM(age) FROM student;

DELETE FROM student WHERE [condition];

DELETE FROM student WHERE name = 'Rohit';

5. Changing data in existing records in a table

Syntax. The basic syntax of an UPDATE query with a WHERE clause is as follows.

UPDATE table_nameSET column1 = value1, column2 = value2...., column = valueNWHERE [condition];

Assume that we want to alter a student's age in our table called 'Rohit.' The following query will be used.

Example

UPDATE student SET age = 28 WHERE name = 'Rohit';

You may have found that while the values are characters, we define the term in single quotes. That's an obligation.

Now if we fire

SELECT * FROM student;

We will get the following table as output:

Please pay attention to UPDATE or DELETE queries using the WHERE clause. Suppose there is more than one student called 'Rohit' in our 'class' list. The age of all students called 'Rohit' is modified to 28 in this case. Therefore, the PRIMARY KEY should always be included in the WHERE clause during the modification or deletion.

We must always take note of the column data types when we modify the details. A numeric column can only contain numbers while a text column can contain text. This implies that if we use an UPDATE clause to place age = 'Rohit' in the age list, SQL would make an exception. You can find out more about SQL's error types and exceptions.

6. Viewing records from a table without knowing exact details

In the real world, there is a strong risk of us not understanding precisely what column values are as we communicate with the database. For instance, because I am a data operator in a firm, I might know that in our organization there is an employee called Nitin as I might have heard other managers talking about him. Now I want to see whole Nitin records but I don't know how he's spelling his name. Is 'Nitin' OR 'Netin' If it is? In that case, we could use the SQL-supplied 'LIKE' operator.

We will fire the following query

SELECT * FROM student WHERE name LIKE 'n%n';

The output of this query will be

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

Let us first introduce two more rows into our table to clarify the criteria for using this parameter. Attempt to add two rows to our student table as IDs 4 and 5, called Shuchi, and age twenty-two and twenty-four.

Our table now becomes.

Now if we fire our query as

SELECT * FROM student WHERE name = 'shuchi';

Then the output will be

We have therefore noticed that we could not obtain a unique record simply by using the name-value in the WHERE clause. Here, more than one condition must be combined in WHERE which can simply be done with conditions such as AND or OR. For example, if we fire:

SELECT * FROM student WHERE name = 'shuchi' AND age = 24;

We get the following output

To further enhance the quest, you may even merge AND & OR requirements to the WHERE section. For example, if we fire

SELECT * FROM student WHERE name = 'shuchi' OR age > 23;

The output will be

In a combination or individually in the WHERE clause you can use different conditions such as AND, OR, <, > to get the required line.

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

We can notice that only student names are printed. Here we just have the names of those students whose ages are over 25 because of the WHERE clause.

More than one column name may even be included with a comma in the SELECT statement.

For example

SELECT name,address 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; 

It will give the following output:

9. Know the structure of the table

I also build a table in my database and overlook what all the columns are and which column is the main column. You will know full information about the table layout that you have generated with the aid of a simple question. Various SQL servers provide various commands. For example, in SQLite3 the command.

.schema student;

• Whereas in PostgreSQL it is \d student
• MySQL uses the following command: describe student;
• Where ‘student’ is our table’s name.

10. Checking the performance of the 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.

EXPLAIN can be used to break the times of the different parts of your query before an SQL statement. The explanation behind a sluggish query is good for cataloging.

Read more articles on SQL Server

Conclusion

In this article, we learned about the top 10 most used SQL queries. Make sure to implement each and every query.