Top 5 SQL Queries You Need to Know

Mastering SQL queries is essential for managing databases effectively. In this article, we'll cover the top 5 SQL queries you need to know and provide practical code examples in Python, Java, and PHP.

1. SELECT - Retrieving Data

The SELECT statement is used to fetch data from a database. It's the most commonly used SQL query and can be combined with various clauses to filter and sort data.

Basic syntax:

SELECT column1, column2, ... FROM table_name;

Python

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute("SELECT column1, column2 FROM table_name")
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

Java

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name", "username", "password");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT column1, column2 FROM table_name");

            while (rs.next()) {
                System.out.println(rs.getString("column1") + ", " + rs.getString("column2"));
            }

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PHP

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "db_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT column1, column2 FROM table_name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["column1"]. ", " . $row["column2"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

2. INSERT INTO - Inserting Data

The INSERT INTO statement is used to insert new records into a table.

Basic syntax:

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

Python, Java, and PHP code examples can be written similarly as shown in the SELECT statement. Replace the SQL query with the INSERT INTO query.

3. UPDATE - Modifying Data

The UPDATE statement is used to modify existing records in a table.

Basic syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Python, Java, and PHP code examples can be written similarly as shown in the SELECT statement. Replace the SQL query with the UPDATE query.

4. DELETE - Deleting Data

The DELETE statement is used to delete existing records from a table.

Basic syntax:

DELETE FROM table_name WHERE condition;

Python, Java, and PHP code examples can be written similarly as shown in the SELECT statement. Replace the SQL query with the DELETE query.

5. JOIN - Combining Tables

The JOIN clause is used to combine rows from two or more tables based on a related column.

Basic syntax:

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

Python, Java, and PHP code examples can be written similarly as shown in the SELECT statement. Replace the SQL query with the JOIN query.

By mastering these top 5 SQL queries, you'll be well on your way to becoming a proficient database manager. Practice executing these queries in different programming languages to enhance your skills and improve your applications.