Databases and Tables in SQL

A database is used to store and organize data so that it can be easily accessed, managed, and updated. In SQL, data is usually stored in tables inside a database.

For example, a school database may contain tables such as Students, Teachers, Courses, and Marks. Each table stores a specific type of information.

What Is a Database in SQL?

A database is a collection of organized data. It can contain multiple tables and other database objects. For example, a database named SchoolDB can contain a Students table:

SchoolDB

└── Students

The Students table can store information such as student ID, name, age, and course.

How to Create a Database in SQL

The CREATE DATABASE statement is used to create a new database in SQL. It provides a place where we can store and manage tables and other related data.

Syntax:

It has the following syntax. 

CREATE DATABASE database_name;

Example:

The following SQL statement creates a database named SchoolDB:

CREATE DATABASE SchoolDB;

Output:

Query OK

The above statement creates a database named SchoolDB.

How to Select a Database

After creating a database, we can use the USE statement to select the database we want to work with. Once selected, we can create tables and perform other SQL operations in that database.

USE SchoolDB;

Output:

Database changed

Now, the SQL statements that create or modify tables will be applied to the SchoolDB database.

What Is a Table in SQL?

A table is a structured collection of data stored inside a database. Data in a table is organized into rows and columns. For example, a Students table may look like this:

Here:

• Columns represent the different types of information.
• Rows represent individual records.
• StudentID, Name, Age, and Course are column names.
• Each row contains information about one student.

How to Create a Table in SQL

The CREATE TABLE statement is used to create a new table in a database. When creating a table, we define the column names and data types to specify what type of data each column can store. 

Syntax:

It has the following syntax. 

CREATE TABLE table_name (    column1 datatype,    column2 datatype,    column3 datatype );

Example:

In the following example, we create a Students table using the SQL command.

CREATE TABLE Students (    StudentID INT,    Name VARCHAR(50),    Age INT,    Course VARCHAR(50) );

Output:

Query OK

Students --------------------------------

StudentID | Name | Age | Course

--------------------------------

Explanation

In the above example, StudentID stores the ID of each student, while INT is used for integer values such as student IDs and ages. The Name column stores the student's name, and VARCHAR(50) allows text values of up to 50 characters. The Age column stores the student's age, and the Course column stores the name of the course.

How to Insert Data into a Table

The INSERT INTO statement is used to add new records or data to a table. It allows us to specify the columns and values that we want to insert into the table.

Example

In the following example, we insert a new record into the Students table using the INSERT INTO statement.

INSERT INTO Students (StudentID, Name, Age, Course) VALUES (101, 'Rahul', 20, 'BCA');

We can also insert multiple records into the table using a single INSERT INTO statement:

INSERT INTO Students (StudentID, Name, Age, Course) VALUES (102, 'Priya', 21, 'B.Tech'), (103, 'Aman', 19, 'MCA');

How to Display Data from a Table

The SELECT statement is used to retrieve data from a table. It allows us to view all or specific columns and records stored in the table.

SELECT * FROM Students;

Output:

Here, the * symbol tells SQL to return all columns from the Students table.

We can also select specific columns:

SELECT Name, Course FROM Students;

Output:

This returns only the Name and Course columns.

Difference between database and table in SQL

A database and a table are related, but they are not the same. Several differences between a database and a table, which are as follows:

For example:

SchoolDB │

├── Students

├── Teachers ├

── Courses

└── Marks