Master NextJS 14 with These 20 Essential SQL Commands for Seamless Ser

Hey, there, everyone!🙋‍♂️

If you are a developer and have worked on ReactJS and NextJS, you must have heard about NextJS 14, which introduced SQL on their server side.

You can read their article at https://nextjs.org/blog/security-nextjs-server-components-actions

So, in this article, we are going to explore all the necessary SQL commands that you must know before learning NextJS 14.

Let us take the real-world example of a college! The database name is College, and there are 3 tables in it: students, courses, and enrollments.

The very first command is to create a database.

CREATE DATABASE College;

USE College;

The students contain four fields, which are their ID, name, DOB, and stream, as you can see in the table below:

The command to create tables is:

CREATE TABLE students (

ID INT PRIMARY KEY,

NAME VARCHAR(50),

DOB DATE,

STREAM VARCHAR(50)

);

You must be wondering what we did here. As the words say, we want to create a table named students that has four different fields. Let us see closely.

The first one is ID, as INT means integer and PRIMARY KEY means it should be unique.
The second one is NAME as varchar(50), which means characters of a maximum length of 50.
The third one is DOB (Date of Birth) of type DATE formatted as YYYY-MM-DD.
The fourth one is STREAM of varchar(50), yeah, you guessed it right, means the character of length 50.

Output:

Similarly, the courses table contains three fields, which are course_ID, course_name, department, and credits.

To create a "courses" table,

CREATE TABLE courses (

COURSE_ID INT PRIMARY KEY,

COURSE_NAME VARCHAR(100),

DEPARTMENT VARCHAR(50),

CREDITS INT

);

Output:

Finally, the Enrollment table contains the enrollment_ID, student_ID, course_ID, and enrollment_DATE.

To create an "enrollments" table,

CREATE TABLE enrollments (

ENROLLMENT_ID varchar(20),

STUDENT_ID INT PRIMARY KEY,

COURSE_ID INT PRIMARY KEY,

ENROLLMENT_DATE DATE

);

Output:

Congratulations🎉! We learned to create a database, and by using that database, we have created three tables: students, courses, and enrollments, with different types and fields.

Let's play with this data.

SELECT:

Used to retrieve data from one or more tables.

Example:

  SELECT * FROM students; -- * means all data
  SELECT NAME FROM students; -- NAME means specific name

FROM:
- Specifies the table or tables from which to retrieve data in a SELECT statement.
- Example:
```
  SELECT * FROM enrollments;
  SELECT STUDENT_ID FROM enrollments;
```

WHERE:

Filters the rows to be retrieved based on a specified condition.

Example:

  SELECT * FROM students WHERE ID = 1;
  SELECT * FROM students WHERE ID = 1 AND NAME = 'Ram Das';
  SELECT * FROM students WHERE ID = 1 OR NAME = 'Ram Das';

INSERT INTO:

Adds new rows to a table.

Example:

  INSERT INTO students
      (ID,NAME,DOB,STREAM)
        VALUES
          (3, 'Niraj Chaurasiya', '2004-12-12','Science');

UPDATE:

Modifies existing data in a table.

Example:

  UPDATE students
      SET STREAM = 'Management'
          WHERE NAME = 'Niraj Chaurasiya';

DELETE FROM:
- Removes rows from a table based on a specified condition.
- Example:
```
  DELETE FROM students WHERE id = 3;
```

ORDER BY:

Sorts the result set based on one or more columns in ascending or descending order.

Example:

  SELECT NAME, STREAM FROM students ORDER BY ID ASC;
  SELECT NAME, STREAM FROM students ORDER BY ID DESC;

GROUP BY:
- Groups rows that have the same values in specified columns into summary rows.
- Example:
```
  SELECT NAME, COUNT(ID) FROM students GROUP BY DOB;
```

HAVING:

Filters the results of a GROUP BY clause based on a specified condition.

Example:

  SELECT NAME,
      COUNT(ID)
          FROM students
              GROUP BY NAME
                    HAVING COUNT(ID) > 1;

JOIN:

Combines rows from two or more tables based on a related column.

Example:

  SELECT NAME, STREAM
      FROM students AS s
          JOIN enrollments
              ON s.ID = enrollments.STUDENT_ID;

DISTINCT:
- Returns unique values in a specified column or columns.
- Example:
```
  SELECT DISTINCT STREAM FROM students;
```
AS:
- Renames a column or table with an alias, which can be useful for readability or when performing calculations.
- Example:
```
  SELECT NAME AS n FROM students;
```

LIKE:

Used in a WHERE clause to search for a specified pattern in a column.

Example:

  SELECT * FROM students WHERE NAME LIKE 'N%';
  SELECT * FROM students WHERE NAME LIKE '%N';
  SELECT * FROM students WHERE NAME LIKE '%N%';
  SELECT * FROM students WHERE NAME LIKE '__N%';

IN:
- Filters the result set to only include rows where a specified column matches any value in a list.
- Example:
```
  SELECT * FROM students WHERE ID IN (1, 3);
```
BETWEEN:
- Filters the result set to include only rows where a column's value is within a specified range.
- Example:
```
  SELECT * FROM students WHERE ID BETWEEN 1 AND 2;
```

NULL:

Represents an unknown or undefined value.

Example:

  SELECT * FROM courses WHERE COURSE_NAME IS NULL;

COUNT:
- Returns the number of rows in a result set or the number of occurrences of a specific value.
- Example:
```
  SELECT COUNT(*) FROM students;
```
MAX and MIN:
- Retrieve the maximum or minimum value from a column.
- Example:
```
  SELECT MAX(ID) FROM students;
```
SUM and AVG:
- Calculate the sum or average of values in a numeric column.
- Example:
```
  SELECT SUM(ID) FROM students;
```
AS:
- Used in conjunction with aggregate functions to rename the result.
- Example:
```
  SELECT AVG(ID) AS ID FROM students;
```

So, these are 20 important SQL commands to know before you are going to learn NextJS 14.

Hope, you like these commands and explanations.

Thank you. Follow me on X:

https://twitter.com/loveforrobotics

Recalling SQL Commands For NextJS 14