SQL

1. Introduction to SQL

1.1 What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.
SQL allows you to perform various tasks such as querying data, updating records, deleting records, and creating or modifying database structures.

1.2 SQL Syntax

SQL statements are written in uppercase for readability, though SQL is case-insensitive.
SQL commands are executed using a database management system (DBMS) like MySQL, PostgreSQL, SQL Server, etc.

2. SQL Data Types

SQL provides several data types for defining columns in tables. Here are some common ones:

INT: Integer values.
VARCHAR(size): Variable-length string (e.g., VARCHAR(255)).
TEXT: Large text strings.
DATE: Date values in the format YYYY-MM-DD.
FLOAT: Floating-point numbers.


CREATE TABLE Students (
    StudentID INT,
    Name VARCHAR(50),
    Age INT,
    EnrollmentDate DATE
);

3. Basic SQL Operations

3.1 SELECT Statement

The SELECT statement is used to query data from a table.


SELECT * FROM Students;

The * symbol selects all columns from the table.

3.2 WHERE Clause

The WHERE clause is used to filter records.


SELECT Name, Age FROM Students WHERE Age > 18;

3.3 ORDER BY Clause

The ORDER BY clause is used to sort results in ascending (ASC) or descending (DESC) order.


SELECT Name, Age FROM Students ORDER BY Age DESC;

3.4 DISTINCT Keyword

The DISTINCT keyword is used to select unique values.


SELECT DISTINCT Age FROM Students;

4. SQL Functions

4.1 Aggregate Functions

COUNT(): Returns the number of rows that match the criteria.
SUM(): Returns the sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MAX(): Returns the maximum value in a column.
MIN(): Returns the minimum value in a column.


SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age) FROM Students;

5. SQL Joins

5.1 INNER JOIN

An INNER JOIN returns records that have matching values in both tables.


SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN CourseEnrollments
ON Students.StudentID = CourseEnrollments.StudentID;

5.2 LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left table and matched records from the right table. If no match is found, NULL values are returned for columns from the right table.


SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN CourseEnrollments
ON Students.StudentID = CourseEnrollments.StudentID;

5.3 RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns all records from the right table and matched records from the left table. If no match is found, NULL values are returned for columns from the left table.


SELECT Students.Name, Courses.CourseName
FROM Students
RIGHT JOIN CourseEnrollments
ON Students.StudentID = CourseEnrollments.StudentID;

5.4 FULL OUTER JOIN

A FULL OUTER JOIN returns all records when there is a match in either the left or right table. Rows that don’t match in either table will have NULL values.


SELECT Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN CourseEnrollments
ON Students.StudentID = CourseEnrollments.StudentID;

6. SQL Insert, Update, and Delete

6.1 INSERT INTO Statement

The INSERT INTO statement is used to insert new records into a table.


INSERT INTO Students (StudentID, Name, Age, EnrollmentDate)
VALUES (1, 'John Doe', 20, '2025-01-01');

6.2 UPDATE Statement

The UPDATE statement is used to modify existing records in a table.


UPDATE Students
SET Age = 21
WHERE StudentID = 1;

6.3 DELETE Statement

The DELETE statement is used to remove records from a table.


DELETE FROM Students
WHERE StudentID = 1;

7. SQL Constraints

7.1 PRIMARY KEY

A PRIMARY KEY is a column or a set of columns that uniquely identifies each row in a table.


CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

7.2 FOREIGN KEY

A FOREIGN KEY is a column that creates a relationship between two tables. It links to the primary key in another table.


CREATE TABLE CourseEnrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

7.3 UNIQUE

The UNIQUE constraint ensures that all values in a column are unique.


CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Email VARCHAR(50) UNIQUE
);

7.4 NOT NULL

The NOT NULL constraint ensures that a column cannot have NULL values.


CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

8. SQL Subqueries

8.1 Subquery in SELECT Statement

A subquery is a query within another query.


SELECT Name, Age
FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);

8.2 Subquery in WHERE Clause

A subquery can be used in the WHERE clause to filter results.


SELECT Name, Age
FROM Students
WHERE StudentID IN (SELECT StudentID FROM CourseEnrollments WHERE CourseID = 101);

9. SQL Data Definition Language (DDL)

CREATE TABLE: Defines a new table.
ALTER TABLE: Modifies an existing table.
DROP TABLE: Deletes a table.
CREATE INDEX: Creates an index for searching faster.
DROP INDEX: Deletes an index.

9.1 CREATE TABLE Example


CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Duration INT
);

9.2 ALTER TABLE Example


ALTER TABLE Students
ADD COLUMN Gender VARCHAR(10);

9.3 DROP TABLE Example


DROP TABLE Courses;

10. SQL Transactions

10.1 COMMIT and ROLLBACK

SQL transactions allow you to perform multiple operations as a single unit. If something goes wrong, you can ROLLBACK changes, otherwise, use COMMIT to save them.


START TRANSACTION;

UPDATE Students SET Age = 22 WHERE StudentID = 2;

-- If everything is good, commit the changes
COMMIT;

-- In case of an error, rollback the changes
ROLLBACK;

Assessment: Regular Tests, Assignments, and Final Project Evaluation
Certification: Certificate of Completion from Disha Institute

Number of Days Depends on your practice and feedback. The more you practice and review your work, the faster you will complete the course.
For Batch time and Fess contact to Below Address and Number.
Zamanat Sir

(MCA / Bsc. I.T / ‘A’ / ‘O’ / CCC / Govt. Certified Domain Skill Trainer )

Structured Query Language (SQL)

SQL