DEV Community

Dipsan Kadariya
Dipsan Kadariya

Posted on • Edited on

SQL 101 📊 | A Guide to Basic Commands for Beginners 💡

#sql #database #mysql #webdev

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;
Enter fullscreen mode Exit fullscreen mode

Note: FirstDB is the database name.

Using a Database 

USE FirstDB;
Enter fullscreen mode Exit fullscreen mode

Note: This selects the database for use.

Dropping a Database 

DROP DATABASE FirstDB;
Enter fullscreen mode Exit fullscreen mode

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

Altering Database (Set to Read-Only) 

ALTER DATABASE FirstDB READ ONLY = 1;
Enter fullscreen mode Exit fullscreen mode

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 );
Enter fullscreen mode Exit fullscreen mode

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

Selecting All Data from a Table 

SELECT * FROM student;
Enter fullscreen mode Exit fullscreen mode

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

Renaming a Table 

RENAME TABLE student TO students;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

Renaming a Column 

ALTER TABLE students CHANGE phone_number email VARCHAR(100);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE students MODIFY email VARCHAR(100) FIRST;
Enter fullscreen mode Exit fullscreen mode

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

Dropping a Column 

ALTER TABLE students DROP COLUMN email;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)