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.