Most Used SQL Queries
Introduction
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
- );
1. Insert Query
Syntax:
INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)] VALUES (value1, value2, value3,…valueN);
Syntax:
INSERT INTO TABLE_NAME (Column1, Column2, ………., ColumnN) VALUES (value1,value2,value3,…valueN);
Example:
- INSERT INTO student (id, name, age) VALUES (‘1’, ‘Nitin’, ‘Noida’, 28);
ID | Name | Address | Age |
1 | Nitin | Noida | 26 |
- 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’);
ID | Name | Address | Age |
1 | Nitin | Noida | 26 |
2 | Amit | New Delhi | 23 |
3 | Rohit | Bareily | 27 |
2. Select Query
Syntax: The basic syntax of the SELECT statement is as follows:
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
- SELECT * FROM student;
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;
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.
Example:
- SELECT COUNT(1) FROM student;
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;
ID | Name | Address | Age |
3 | Rohit | Bareily | 27 |
We can also check sum of a numeric column.
For example:
- SELECT sum(age)FROM student;
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
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’.
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];
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;
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
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’;
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;
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
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;
Name |
Nitin |
Rohit |
For example:
- SELECT name, address FROM student;
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;
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