Introduction to SQL (Structured Query Language)

Introduction to SQL (Structured Query Language)

SQL is the backbone of relational database management. It allows users to interact with and manipulate data stored in databases effectively.

A Brief History of SQL

  • Origin: SQL was developed in the 1970s by IBM during research on relational database technology.
  • Name: It started as SEQUEL but was later renamed SQL (Structured Query Language) due to trademark reasons.

What is SQL?

SQL is a programming language designed to communicate with and manipulate databases. With SQL, users perform essential database tasks, commonly summarized as CRUD:

  • C: Create
  • R: Read
  • U: Update
  • D: Delete

How Does SQL Help?

SQL enables users to:

  1. Retrieve Data: Extract precise information using queries.
  2. Manipulate Data: Add, modify, or remove records in the database.
  3. Define Data: Create and modify the structure of databases (tables, views, indexes).
  4. Control Data: Manage who can access and alter the database by granting or revoking permissions.

Types of SQL Commands

SQL commands are typically grouped based on their functionality:

  1. DQL (Data Query Language):

    • Used to retrieve data from a database.
    • Example: SELECT

  2. DML (Data Manipulation Language):

    • Modifies data stored in the database.
    • Examples: INSERT, UPDATE, DELETE

  3. DDL (Data Definition Language):

    • Defines and modifies the structure of database schema.
    • Examples: CREATE, DROP, RENAME, TRUNCATE, ALTER

  4. DCL (Data Control Language):

    • Manages database permissions and security.
    • Examples: GRANT, REVOKE

  5. TCL (Transaction Control Language):

    • Controls database transactions.
    • Examples: COMMIT, ROLLBACK

Common SQL Commands and Syntax

Here are some basic SQL commands used for database operations:

1. Create a Database

CREATE DATABASE STUDENT;

2. Drop a Database

DROP DATABASE STUDENT;

3. Show All Databases

SHOW DATABASES;

4. Use a Database

USE STUDENT;

5. Create a Table

CREATE TABLE employee(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary INT
);

6. Insert Data Into a Table

INSERT INTO employee(id, name, age, salary) VALUES
(1, 'rahul', 23, 23322),
(2, 'vivek', NULL, 30000),
(3, 'yogesh', NULL, 45000),
(4, 'ram', 27, 50000);

Note: If age is not provided, use NULL or specify values for consistency.

7. Select Data From a Table

SELECT * FROM employee;
SELECT name, salary FROM employee WHERE department = 'HR';

8. Update Existing Records


UPDATE employee SET salary = 50000 WHERE department = 'HR';

9. Alter Table Structure

ALTER TABLE employee ADD dob VARCHAR(20) DEFAULT 'NA';
ALTER TABLE employee DROP COLUMN dob;

10. Truncate a Table

TRUNCATE TABLE employee;

11. Drop a Table

DROP TABLE employee;

12. Delete Specific Records

DELETE FROM employee WHERE id = 3;

Note: Use DELETE and UPDATE commands carefully—they can change or remove large amounts of data if used without proper conditions!

Comments

Post a Comment

Popular posts from this blog

Introduction to DBMS

🚀 Day 1 of My DBMS Learning Journey Quick question: What's the difference between "orange" and "fruit orange"? 🍊 One is DATA. The other is INFORMATION! 🎯 Started my DBMS journey today and mind = blown! 🤯 Here's what I discovered: 📊 Data vs Information • Data = Raw facts like "12", "Orange", "EXY" • Information = Data with context & meaning • Example: "12" is data, but "Age: 12" or "Roll Number: 12" is information! 🗄️ What is a Database? A structured collection of interrelated data that can be: • Stored in tables • Efficiently retrieved & manipulated • Any size (from small to massive!) • Example: College DB with Student, Professor, Timetable tables all connected 💻 What is DBMS? Software that manages databases - acts as an interface between users and data for secure storage, retrieval, and updates! 🗂️ Why File Systems Failed: → Data Redundancy: Same info stored multiple times (wasted m...
Read more

ACID Properties

ACID Properties (Explained Simply) ACID is a set of four properties that help databases execute transactions reliably and correctly , even in tough situations like system crashes or network failures . Together, they make sure your data stays accurate and trustworthy. A — Atomicity (All or Nothing) Atomicity means a transaction is treated as one single unit of work : If all steps succeed → COMMIT If any step fails → ROLLBACK (everything is undone) Example: Transferring money from Account A to Account B should either complete fully or not happen at all—no partial updates. C — Consistency (Rules Must Always Hold) Consistency ensures the database always moves from one valid state to another valid state . This means: Reads return valid data from the database Writes must follow database rules/constraints (like primary keys , foreign keys , checks, etc.) Example: If a foreign key requires a related record to exist, the database won’t allow an update that breaks that relation...
Read more

Understanding First Normal Form (1NF) in Database Design

Understanding First Normal Form (1NF) in Database Design First Normal Form (1NF) is the very first and most fundamental step in the process of database normalization. Applying 1NF helps reduce data redundancy and ensures your data is clean, consistent, and easy to manage. What is 1NF? Atomic Values: Each field in a table contains only a single value—no lists, sets, or multiple values in one cell. No Multivalued Attributes: You shouldn’t have columns that store several items (e.g., a list of courses in one column). Unique Rows: Every row must be uniquely identifiable, often by a primary key. Rules of 1NF Atomicity: Try to ensure each attribute holds a single value. Uniqueness: Every row in the table should be uniquely identifiable (often through a primary key). Examples of Applying 1NF Let’s look at different ways to organize data and whether they follow 1NF: 1. Row Splitting: One Row Per Student-Course Combination How it works: For every unique student-course combination,...
Read more