6 Must know SQL queries

There are few queries in SQL that are used in our day to day life as a backend developer. Maybe you are about to start your career as a backend developer or frontend developer then you should at least know these SQL queries. So let’s talk about these 6 must know SQL queries.

1. Create table query

For sure as a developer one day you have to use this query or say you might be using it. Create table is the most common query, with this query we create tables in the database. The table is a collection of rows and columns. Let’s cut to the chase and write the queries syntax and example.

create table yourTableName (
columnName datatype,
columnName datatype,
);

Example –

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
);

2. Insert query

Insert query is used to add data (rows) in a table. There is not much about it lets make it more clear with syntax and example.

INSERT INTO tableName (columnName1 , columnname2,..)
 VALUES ('firstColumnValue','secondColumnValue,...);

Example –

INSERT INTO Persons ( LastName, FistName)
VALUES ('ABC', 'XYZ');

In case if you are adding values for all the columns of your table then you can omit column names after table name in insert query. For example, if you have only two columns in your table then you can write it as follows.

INSERT INTO Persons 
VALUES ('ABC', 'XYZ');

3. Select query

As a backend developer Select query is also have a great significance . Select query is use to fetch data from a table . There are two ways you can use this query to fetch data.

Using asterisk (*) to fetch all data from the table .

SELECT * FROM tableName

Using a specific column name to fetch data from the table.

With this method, you can select only those columns you need from the table . Even though this method is considered as much faster than using an asterisk to fetch whole data. You can select as many as many columns. Each column name is separated by a comma.

SELECT columnName1 , columnName2 ,columnName3 from tableName

4. Update Query

We use an update query when we want to modify some data which is already in the table. We should know how to use the Where clause before starts using an update query. Its one of the delicate queries that should be handled with caution one wrong clause can alter whole data. Let’s see syntax and example.

UPDATE tableName set columnName1 = newValue , columnName2 = newValue2 WHERE condition

Example –

UPDATE Persons
SET firstName = 'Sheldon', lastName= 'Cooper'
WHERE PersonID = 1;

You can omit the Where clause if you want to update all rows (records).

5. Delete query

As the name says it can be used only for deleting rows (records) from a table. you can not use to delete a table and database. It is also one of the delicate queries. You should know what you are doing when using this query. It’s not so cool to play with delete query its death wish, dead never come back to life. Let’s see by syntax and example.

DELETE FROM tableName WHERE conditon

Example –

DELETE FROM persons WHERE personsID = 1;

If you omit Where condition it will delete whole rows from the table .

6. Join query

The last but not least, the Join query can be very useful when you want to fetch data from multiple tables. There are few types of Joins like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN. Joining two tables is depends on the condition, both tables must have something common so that based on that tables can be joined. All joins have identical syntax, unlike their name. Let’s see the INNER JOIN.

SELECT *
FROM tableName1
INNER JOIN tableName2 Where tableName1.matchingcolumnName = tableName2.matchingColumnName

For example, suppose we have two tables persons and personDetails and they both have personID and detailID same value respectively.

SELECT *
FROM persons
INNER JOIN personDetails Where persons.personID = personDetails.detailID

I Know this not enough about JOINS, I will be creating a separate article for JOINS only.

So that’s all about 6 must know SQL queries Hope this article has helped you . if you have any query about this article share your comments in below comment box.

See also

How to create REST API in PHP ?

Leave a Reply

Your email address will not be published. Required fields are marked *