chetan chauhan

SQL: A Practical Guide for Beginners

Chetan Chauhan's photo
Chetan Chauhan
·

2 min read

Introduction

SQL, which stands for Structured Query Language, is a non-procedural language. This means It's like giving orders in plain English.

Here are the main components of SQL:

  • DDL: Data Definition Language is used to create and modify database structures.

  • DML: Data Manipulation Language is used for inserting, updating, and deleting data.

  • Views: These are virtual tables that simplify complex queries.

  • Transactions: Manage data integrity and consistency.

RDBMS - Relational Database Management System

  • RDBMS, or Relational Database Management System, is the foundation of SQL. It uses tables to store data. Each table has rows (also known as tuples) and columns (fields). No duplicate rows are allowed.

Data Types

SQL supports various data types:

  • CHAR(n): Fixed-length character strings.

  • VARCHAR(n): Variable-length character strings.

  • Number(p, d): Fixed-point numbers with precision.

  • Real, Double Precision: Floating-point numbers.

  • Float(n): Floating-point numbers with precision.

  • Date: Calendar date.

  • Time: Time of day.

SQL Queries

  1. Creating a Table:

     CREATE TABLE students ( student_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );

  1. Inserting Data:

     INSERT INTO students (student_id, first_name, last_name) VALUES (1, 'John', 'Doe');

  1. Inserting Multiple Rows:

     INSERT INTO students (student_id, first_name, last_name)
 VALUES
     (2, 'Alice', 'Johnson'),
     (3, 'Michael', 'Brown'),
     (4, 'Emma', 'Wilson');

  2. Retrieving Data:

     SELECT * FROM students;

  3. Updating Data:

     UPDATE students SET last_name = 'Smith' WHERE student_id = 1;

  4. Updating Data with Multiple Conditions

     UPDATE students
 SET last_name = 'Anderson'
 WHERE student_id IN (2, 3, 4);

  5. Deleting Data:

     DELETE FROM students WHERE student_id = 1;

  6. Joining Tables:

     SELECT students.first_name, courses.course_name FROM students INNER JOIN courses ON students.student_id = courses.student_id;

  7. Aggregating Queries:

     SELECT COUNT(*) AS total_students FROM students;

  8. Aggregating and Grouping data

     SELECT courses.course_name, COUNT(*) AS student_count
 FROM students
 INNER JOIN courses ON students.student_id = courses.student_id
 GROUP BY courses.course_name;

  9. Subquery:

     SELECT first_name, last_name FROM students WHERE student_id = (SELECT MAX(student_id) FROM students);

  10. Views:

    CREATE VIEW student_name AS SELECT first_name, last_name FROM students;

  11. Altering Table

    ALTER TABLE students
ADD birthdate DATE;

Conclusion

SQL is a practical tool for managing and manipulating data in databases. This guide introduced you to the essential SQL for creating, inserting, retrieving, updating, and deleting data.

General ProgrammingSQL