Most Used SQL Queries

Introduction

SQL Server is an interface which helps to communicate with the system’s database using queries. With this article, I will explain the top 10 most used SQL Queries. SQL queries are SQL’s mechanism to communicate with the database to result in us the desired output.

Given below is a dumpy table, that I will be using to explain to you the practical implementation of each of the discussed SQL queries.

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(since the resultset has zero rows). This table holds student information. The student id, name of the student, address of the student, and age of the student.

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

1. Insert Query

We use Insert Command to insert records into a table. The SQL INSERT INTO declaration is used to include new database rows in the database table.

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.

If you add value for all columns of the table you may not have to specify the column(s) name in the SQL query. Nonetheless, notice that the order of the values is identical to that of the table columns. It will be the following SQL INSERT INTO syntax:

Syntax:

INSERT INTO TABLE_NAME (Column1, Column2, ………., ColumnN) VALUES (value1,value2,value3,…valueN);

Example:

As per the syntax of INSERT INTO syntax, we specify the name of the table “INSERT INTO” keyword, followed the order of columns, in which you want the values that need to be inserted. Post the “VALUES’ keyword enter the column values in order of the columns specified earlier.

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	Bareily	27

2. Select Query

Viewing all records from a table. These results tables are called result-sets.

Syntax: The basic syntax of the 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:

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	Bareily	27

We may also use ORDER BY to organize the presented outcome in a similar order in our chosen sentence. For example,

SELECT * FROM student ORDER BY age;

Result is:

ID	Name	Address	Age
1	Amit	New Delhi	23
2	Nitin	Noida	26
3	Rohit	Bareily	27

The production is arranged in an increasing age sequence. When we choose to structure the view in decreasing order we should use the DESC keyword after the column name in the query.

3. Viewing only selected records from a table

When we do not want all records to fill our display screen, SQL offers the choice to show only selected rows while there are a number of rows in a database. Count is good for recording record numbers.

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:

The number of rows our table has shall be returned. In our query, we may even use MAX & MIN. For eg, whether a student with a maximum age needs to be provided with information, we can fire:

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

We will get:

ID	Name	Address	Age
3	Rohit	Bareily	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 only use numerical columns for MAX, MIN, and SUM functions. The text column utilizes certain features to trigger 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 has value ‘Rohit’.

In our case, the result of this query 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

Syntax: The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_nameSET column1 = value1, column2 = value2…., columnN = 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:

ID	Name	Address	Age
1	Nitin	Noida	26
2	Amit	New Delhi	23
3	Rohit	Bareily	27

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

Output of this query will be:

ID	Name	Address	Age
1	Nitin	Noida	26

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 ID 4 and 5, called Shuchi and age twenty-two and twenty-four.

Our table now becomes:

Id	Name	Address	Age
1	Nitin	Noida	26
2	Amit	New Delhi	23
3	Rohit	Bareily	27
4	Shuchi	Livknow	24
5	Shuchi	Patna	24

Now if we fire our query as:

SELECT * FROM student WHERE name = ‘shuchi’;

Then the output will be:

ID	Name	Address	Age
4	Shuchi	Lucknow	22
5	Shuchi	Patna	24

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 that 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:

ID	Name	Address	Age
5	Shuchi	Patna	24

To order 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

Output will be:

ID	Name	Address	Age
1	Nitin	Noida	26
3	Rohit	Bareily	27
4	Shuchi	Lucknow	22
5	Shuchi	Patna	24

In a combination or individually in WHERE clause you can use different conditions 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:

Name

Nitin

Rohit

We can notice that only student names are printed. Here we just have names of those students whose age is 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:

Name	Address
Nitin	Noida
Amit	New Delhi
Rohit	Bareily
Shuchi	Lucknow
Shuchi	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	Shuchi
24	Shuchi

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 is:

.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 a SQL statement. The explanation behind a sluggish query is good for cataloging.

Conclusion

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

SQL Server DBA Training from Beginner to Advanced

Most Used SQL Queries

Introduction

1. Insert Query

2. Select Query

3. Viewing only selected records from a table

4. Deleting records from a table

5. Changing data in existing records in a table

6. Viewing records from a table without knowing exact details

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

8. Viewing only selected columns from a table

9. Know the structure of the table

10. Checking the performance of the query

Conclusion

Login with your site account

Register a new account

Modal title