Introduction to SQL

What is SQL?

SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows users to create, read, update, and delete data within a database. SQL provides a way to interact with databases using simple, declarative statements.

Why Learn SQL?

Learning SQL is essential for several reasons:

• Data Management: SQL is the primary language for managing and querying relational databases, which are widely used in various applications.
• Career Opportunities: Proficiency in SQL is a valuable skill for many IT and data-related roles, including database administrators, data analysts, and software developers.
• Data Analysis: SQL enables you to perform complex data analysis and reporting tasks, making it easier to extract valuable insights from large datasets.
• Versatility: SQL is used in many popular database systems, such as MySQL, PostgreSQL, and Microsoft SQL Server, making it a versatile skill across different platforms.

SQL Database and Table Operations Guide

Database Operations

Creating a Database

CREATE DATABASE FirstDB;

Note: FirstDB is the database name.

Using a Database

USE FirstDB;

Note: This selects the database for use.

Dropping a Database

DROP DATABASE FirstDB;

Note: This permanently deletes the database and all its contents.

Altering Database (Set to Read-Only)

ALTER DATABASE FirstDB READ ONLY = 1;

Note: This makes the database read-only, preventing any modifications.

Table Operations

Creating a Table

CREATE TABLE student (
    student_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(50),
    student_address VARCHAR(50),
    hourly_pay DECIMAL(5,2),
    student_date DATE
);

Note: This creates a table named 'student' with specified columns and data types.

Selecting All Data from a Table

SELECT * FROM student;

Note: This retrieves all rows and columns from the 'student' table.

Renaming a Table

RENAME TABLE student TO students;

Note: This changes the table name from 'student' to 'students'.

Altering Table Structure

Adding a New Column

ALTER TABLE students
ADD phone_number VARCHAR(15);

Note: This adds a new column 'phone_number' to the 'students' table.

Renaming a Column

ALTER TABLE students
CHANGE phone_number email VARCHAR(100);

Note: This changes the column name from 'phone_number' to 'email' and modifies its data type.

Modifying a Column's Data Type

ALTER TABLE students 
MODIFY COLUMN email VARCHAR(100);

Note: This changes the data type of the 'email' column to VARCHAR(100).

Changing a Column's Position

ALTER TABLE students
MODIFY email VARCHAR(100) AFTER last_name;

Note: This moves the 'email' column to be after the 'last_name' column.

ALTER TABLE students
MODIFY email VARCHAR(100) FIRST;

Note: This moves the 'email' column to be the first column in the table.

Dropping a Column

ALTER TABLE students
DROP COLUMN email;

Note: This permanently removes the 'email' column from the table.

Combining Multiple Operations

ALTER TABLE students
MODIFY email VARCHAR(100) AFTER last_name;
SELECT * FROM students;

Note: This changes the column position and then displays the new table structure in one operation.