Post
Ask Question

The Most Important SQL Queries For Beginners

Most Important SQL Queries For Beginners

In this article, let's discuss the 15 most important SQL queries for beginners including these. How do you get the data from the table? How do you create or drop the table? How do you display the different records? Do you want to? and You can easily do all of this by writing a simple SQL query.

1. How to get det data from all columns of a table 

This is a very simple query to display all data of a table. Note that there is only one character after SELECT:  "*" (which specifies all columns) in this query.

So there is no need to list the column names. Of course, don't forget the FROM and the name of the table you want to get data from. In this example, we are retrieving data from the HUMUN in the table.

SELECT *
FROM human;

2. Get data from certain columns of a table 

The above query will display all data for HUMAN. If you only want to retrieve data from specific columns, list them after SELECT. In this example, we are retrieving data from the id and name columns.

SELECT id, name
FROM human;

3. Filtering data using WHERE clause

In addition to getting data from certain columns, we can also filter data using WHERE. See the below example, There is one condition age  >= 30. So it means that we are looking for a human list that has an age equal to 30 or more.

SELECT id, name, age
FROM human
WHERE age>=30;

 4. Getting data using multiple conditions joined by AND operator

If you want to filter data by multiple conditions like name = 'Rajesh' and age >= 30 then you can use below one

SELECT id, name, age
FROM human
WHERE age >= 30 AND name = 'Rajesh';

5.  Getting data using multiple conditions joined by OR operator

If you want to filter data by multiple conditions but at least one condition met

SELECT id, name, age
FROM human
WHERE age >= 30 OR name = 'Rajesh';

It means getting those record where has name = 'Rajesh' or has age >= 30

6. Retrieve non-repeated records using DISTINCT

If you want to display only unique records means non-repeated records that time you can use DISTINCT after SELECT to get only one of each type of record.

Suppose we have HUMAN table like the one below

Name            |           Age

Rajesh          |           30

Rajesh          |           30

Rajesh          |           25

Rajesh          |           30

To see the above records there is a total of 4 records and 3 are the same out of 4. So if we will use DISTINCT then display only two records.

SELECT DISTINCT name, age
FROM human;

Output: 

Name      |       Age

Rajesh    |        30

Rajesh    |        25

7. Getting records without NULL from the certain column(s)

If you want to get only records that have a NOT NULL value, for that, you can use IS NOT NULL in the condition see the below example.

Suppose records in DB like the ones below 

Name            |           Age

Rajesh          |           28

Subhash       |           NULL

Vishal           |           25

Rajesh          |           NULL

SELECT name, age
FROM human
WHERE age IS NOT NULL;

Output

Name            |           Age

Rajesh          |           28

Vishal           |           25

8. Database records sorting according to the column

Whenever the user wants to display records sorted by name, age, salary, or any other column that time we can use ORDER BY query.

Name            |           Age

Rajesh          |           28

Subhash       |           35

Vishal           |           25

Ajay              |           40

SELECT name,age
FROM human
ORDER BY name;

Output

Name            |           Age

Ajay               |           28

Rajesh           |           35

Subhash       |           25

Vishal            |           40

Or if want to display in descending order then just put only DESC after ORDER BY query 

SELECT name,age
FROM human
ORDER BY name DESC;

You can sort more than columns in a single query

9. Finding matching records with some pattern (LIKE)

You can use LIKE for finding some records with the pattern, See the below records and query 

Name            |           Age

Ajay               |           28

Rajesh           |           35

Subhash       |            25

Vishal            |           40

SELECT name,age
FROM human
WHERE name LIKE '%j%';

Output

Name            |           Age

Ajay               |           28

Rajesh           |           35

We have only records that have the character "j".

Let's see another one

SELECT name,age
FROM human
WHERE name LIKE '%sh%';

Output

Name            |           Age

Rajesh           |           35

Subhash       |            25

Vishal            |           40

So there are 3 records that have "sh" character in the name.

You can use different types of queries like names starting with "Ra%" or end with "%sh" or whatever you want.

10. Join the values from text word columns into one string(CONCAT)

In SQL server there is one function CONCAT to join the string into one.

Name            |           Age

Rajesh          |           28

Subhash       |           30

Vishal           |           25

Mahesh        |           29

Select CONCAT(name, ' - ', age) Detail from human

Output

Detail

Rajesh - 28       

Subhash - 30 

Vishal - 25 

Mahesh - 29   

11. Mathematical operators 

In SQL queries we can use any mathematical operators like (=,-,*,/,% etc...),

Suppose we have a Products table and we need to display Prices after the discount, See the below query

Select ActualPrice, Discount, (ActualPrice - Discount)UserPrice from PRODUCTS

ActualPrice     Discount    UserPrice

300                   50               250

450                   100             350

12. Join data from multiple tables

JOIN is the most important query in SQL,

You can join tables using JOIN queries such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN. This example merges data from the Customer and City tables. The INNER JOIN must be specified after FROM and the name of the first table, Customer. After the INNER JOIN, put the name of the second table, city. Records with data from both tables are matched by ON in the join condition. A record in the city table matches a record in the customer table if the id column of the customer table and the customer_id column of the city table have the same value.

SELECT cust.last_name, c.name
FROM customer cust
INNER JOIN city c
  ON cust.id = c.customer_id;

13. Insert data into a table

Need to specify both the columns and fields value to be inserted

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Suppose we have a CUSTOMER table and need to add some data to the table then we have to write query like the below

INSERT INTO CUSTOMER(CustomerName, Address, City, Country)
VALUES ('Rajesh','12,Mondeal Height','Ahmedabad','India');

14. Update table column value

Name            |           Age

Rajesh          |           28

Subhash       |           30

Vishal           |           25

Mahesh        |           29

UPDATE human
SET name = 'Rajesh Gami'
WHERE name = 'Rajesh';

Output

Name              |           Age

Rajesh Gami   |           28

Subhash         |           30

Vishal             |           25

Mahesh          |           29

15. CREATE table

Syntax

CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype,
   ....
);

Let's add a HUMAN table

CREATE TABLE Human(
    Id int,
    Name varchar(255),
    Address varchar(255),
    Age int
);

 

Free Books

Here are two free eBooks downloads (PDF) on SQL queries for beginners:

SQL Queries Book

SQL Book

Here are some resources to learn SQL programming. SQL Programming on C# Corner 

 


Similar Articles