WikiGalaxy
Introduction to SQL
What is SQL?
SQL (Structured Query Language) is the standard language used to interact with relational databases. It allows users to perform various operations such as data insertion, querying, updating, and deletion, as well as database creation and modification.
Key Aspects of SQL:
1. Declarative Nature
SQL focuses on what needs to be done rather than how it is done.
Example: To retrieve all students from a database, you simply specify the data you want, and SQL handles the retrieval.
2. Standardized Language
Defined by ANSI/ISO standards, SQL can be used across different database systems with minor syntax variations.
3. Relational Database Support
SQL is primarily designed to manage data in relational databases. Examples include MySQL, PostgreSQL, SQL Server, and Oracle.
Basic Components of SQL:
1. DDL (Data Definition Language)
Used to define and modify the structure of database objects (e.g., tables, indexes).
Common commands:
CREATE,ALTER,DROP.
2. DML (Data Manipulation Language)
Used to manipulate the data within database objects.
Common commands:
SELECT,INSERT,UPDATE,DELETE.
3. DCL (Data Control Language)
Used to control access to the database.
Common commands:
GRANT,REVOKE.
4. TCL (Transaction Control Language)
Used to manage transactions within a database.
Common commands:
COMMIT,ROLLBACK,SAVEPOINT.
SQL Syntax Overview:
SQL commands consist of keywords that are case-insensitive, though it is a common practice to write keywords in uppercase for clarity.
Statements often end with a semicolon (
;).
-- Selecting all records from a table
SELECT * FROM Students;
-- Inserting data into a table
INSERT INTO Students (ID, Name, Age) VALUES (1, 'John Doe', 20);Basic SQL Commands with Examples:
1. Creating a Table
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);This is the description of the point with a heading. The description text is styled with a light gray for clarity.
CREATE TABLE:
Defines a new table.
ID: Integer column with aPRIMARY KEYconstraint to ensure uniqueness.Name,Age, andDepartment: Additional columns with appropriate data types.
2. Inserting Data
INSERT INTO Students (ID, Name, Age, Department) VALUES
(1, 'Alice', 22, 'Computer Science'),
(2, 'Bob', 21, 'Mathematics');INSERT INTO:
Adds new rows of data to the specified table.
Multiple rows can be inserted in one command using a comma-separated list of values.
3. Querying Data
SELECT Name, Department FROM Students WHERE Age > 21;SELECT:
Retrieves specific columns or all (*) from a table.
WHERE:
Filters rows based on the condition (
Age > 21).
4. Updating Data
UPDATE Students SET Age = 23 WHERE Name = 'Alice';UPDATE:
Modifies existing data.
SET:
Specifies the new value for the column.
WHERE:
Ensures only the relevant row is updated.
5. Deleting Data
DELETE FROM Students WHERE Age < 22;DELETE FROM:
Removes rows from a table.
WHERE:
Specifies which rows to delete.
Practical Application Example:
Scenario:
A university wants to manage its student database.
SQL Code:
-- Step 1: Create a table for students
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Major VARCHAR(50),
GPA DECIMAL(3, 2)
);
-- Step 2: Insert sample data
INSERT INTO Students (ID, Name, Age, Major, GPA) VALUES
(1, 'John Smith', 20, 'Physics', 3.8),
(2, 'Jane Doe', 22, 'Biology', 3.6),
(3, 'Emily Davis', 19, 'Chemistry', 3.9);
-- Step 3: Query students with a GPA greater than 3.7
SELECT Name, Major FROM Students WHERE GPA > 3.7;
-- Step 4: Update a student’s major
UPDATE Students SET Major = 'Astrophysics' WHERE Name = 'John Smith';
-- Step 5: Delete a student record
DELETE FROM Students WHERE Name = 'Jane Doe';
Output:
Query Result:
Name: John Smith, Major: Physics
Name: Emily Davis, Major: ChemistryTable After Update:
John Smith: Major updated toAstrophysics.Table After Deletion:
Jane Doeis removed from the table.
Summary:
SQL is a powerful language for managing relational databases.
It is divided into DDL, DML, DCL, and TCL for different operations.
Commands like
CREATE,INSERT,SELECT,UPDATE, andDELETEform the backbone of SQL operations.
Dive deeper into advanced SQL concepts like Joins, Aggregate Functions, and Subqueries.
