Introduction to SQL Databases & Basic Operations

Welcome to the first part of this comprehensive SQL tutorial. This tutorial is for beginners who want to brush up on their SQL skills and get hands-on experience with SQL commands.

Understanding SQL Databases

A database is a structured collection of data stored and accessed electronically. In SQL, a database can have multiple tables, each storing inter-related data. For instance, in a school database, you might have tables for student marks, student fees, student subjects, etc.

Creating an SQL Database

To create a new database, use the following command:

CREATE DATABASE student;

Deleting a Database

If you need to delete a database, use the DROP DATABASE command:

DROP DATABASE student;

Using a Database

Before working on a database, you need to select it:

USE student;

Creating a Table

To create a table within a database, use the CREATE TABLE command. Here’s an example:

CREATE TABLE student (
  id INT PRIMARY KEY, 
  name VARCHAR(50),
  age INT NOT NULL
);

In this example, we used different data types:

INT: Stores integer values.
VARCHAR(50): Stores variable-length strings up to 50 characters.
NOT NULL: Ensures that the column cannot have a NULL value.

Inserting Data into a Table

To insert data into the table, use the INSERT INTO command:

INSERT INTO student VALUES(1, "RAJ", 24);
INSERT INTO student VALUES(2, "RAM", 27);
INSERT INTO student VALUES(3, "SHAM", 25);

Selecting Data from a Table

To view the data in the table, use the SELECT command:

SELECT * FROM student;

This command selects all columns from the student table.

Overview of SQL Data Types

Understanding data types is crucial in SQL. Here are some common data types:

CHAR and VARCHAR: Store strings.
BLOB: Stores large binary objects.
INT: Stores integers.
TINYINT: Stores small integers.
BIT: Stores bit values.
FLOAT and DOUBLE: Store floating-point numbers.
BOOLEAN: Stores Boolean values.
DATE: Stores dates in the format YYYY-MM-DD.

Signed vs. Unsigned

TINYINT UNSIGNED (0 to 255)
TINYINT (-128 to 127)

Types of SQL Commands

DDL (Data Definition Language): CREATE, ALTER, RENAME, TRUNCATE, DROP
DQL (Data Query Language): SELECT
DML (Data Manipulation Language): INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): START TRANSACTION, COMMIT, ROLLBACK

Create a database:

  CREATE DATABASE db_name;
  CREATE DATABASE IF NOT EXISTS db_name;

Drop a database:

  DROP DATABASE db_name;
  DROP DATABASE IF EXISTS db_name;

Show databases and tables:
```
  SHOW DATABASES;
  SHOW TABLES;
```
Use a database:
```
  USE database;
```

Create a table:

  CREATE TABLE table_name(
    column_name1 datatype constraint,
    column_name2 datatype constraint,
  );

Example:

  CREATE TABLE student (
    id INT PRIMARY KEY, 
    name VARCHAR(50),
    age INT NOT NULL
  );

Drop a table:
```
  DROP TABLE student;
```
Select and view all columns:
```
  SELECT * FROM table_name;
```

Insert data into columns:

  INSERT INTO table_name
  (colname1, colname2)
  VALUES
  (col1_val1, col2_val2), (col1_val2, col2_val2);

Example:

  INSERT INTO student
  (id, name)
  VALUES
  (101, "karan"),
  (102, "Arjun");

Practice Exercises

Exercise 1

Create a database for your company named XYZ, and perform the following operations:

Create a table inside the database to store employee info (id, name, salary).
Add the following info to the database:
- 1, "adam", 25000
- 2, "bob", 30000
- 3, "casey", 40000
Select and view all your table data.

Solution

CREATE DATABASE XYZ_db;
USE XYZ_db;
CREATE TABLE employee_info(
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employee_info
(id, name, salary)
VALUES
(1, "adam", 25000),
(2, "bob", 30000),
(3, "casey", 40000);

SELECT * FROM employee_info;

Exercise 2

Create a database for a library system named LibraryDB, and perform the following operations:

Create a table inside the database to store book info (book_id, title, author, year_published).
Add the following info to the database:
- 1, "1984", "George Orwell", 1949
- 2, "To Kill a Mockingbird", "Harper Lee", 1960
- 3, "The Great Gatsby", "F. Scott Fitzgerald", 1925
Select and view all your table data.

Solution

CREATE DATABASE LibraryDB;
USE LibraryDB;
CREATE TABLE books(
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  year_published INT
);

INSERT INTO books
(book_id, title, author, year_published)
VALUES
(1, "1984", "George Orwell", 1949),
(2, "To Kill a Mockingbird", "Harper Lee", 1960),
(3, "The Great Gatsby", "F. Scott Fitzgerald", 1925);

SELECT * FROM books;

Exercise 3

Create a database for a school system named SchoolDB, and perform the following operations:

Create a table inside the database to store student info (student_id, first_name, last_name, date_of_birth).
Add the following info to the database:
- 1, "John", "Doe", "2005-05-15"
- 2, "Jane", "Smith", "2006-08-22"
- 3, "Jim", "Brown", "2004-12-01"
Select and view all your table data.

Solution

CREATE DATABASE SchoolDB;
USE SchoolDB;
CREATE TABLE students(
  student_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  date_of_birth DATE
);

INSERT INTO students
(student_id, first_name, last_name, date_of_birth)
VALUES
(1, "John", "Doe", "2005-05-15"),
(2, "Jane", "Smith", "2006-08-22"),
(3, "Jim", "Brown", "2004-12-01");

SELECT * FROM students;

This concludes the first part of our SQL tutorial. In the next part, we will delve deeper into more advanced SQL commands and operations. Stay tuned!

SQL Tutorial Part 1: Introduction to SQL Databases and Basic Operations