Creating the first database and tables

Creating a database and tables involves defining the structure and organization of the data that you want to store. I’ll guide you through creating a simple example using SQL. Please note that the exact syntax may vary slightly depending on the database system you are using (e.g., MySQL, PostgreSQL, SQL Server).

To learn about the queries, see the module: https://nishantmunjal.com/lesson/select-insert-update-delete/

Example Scenario:

Let’s create a database for a library, where we want to store information about books and authors.

SQL Syntax:

Creating a Database:

CREATE DATABASE LibraryDatabase;

##This statement creates a new database named LibraryDatabase

Using the Database:

USE LibraryDatabase;

##This statement tells the database system to use the LibraryDatabase for subsequent queries.

Creating a Table for Authors:

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthYear INT
);

##This statement creates a table named Authors with columns for AuthorID, FirstName, LastName, and BirthYear.

Creating a Table for Books:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    ISBN VARCHAR(13),
    PublishYear INT,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

##This statement creates a table named Books with columns for BookID, Title, ISBN, PublishYear, and AuthorID. The AuthorID column is a foreign key referencing the Authors table.

Complete MySql Commands

-- Creating the Database
CREATE DATABASE LibraryDatabase;

-- Using the Database
USE LibraryDatabase;

-- Creating a Table for Authors
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthYear INT
);

-- Creating a Table for Books
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    ISBN VARCHAR(13),
    PublishYear INT,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);