SQL Tutorial: Basic to Advanced
1. Introduction to SQL
SQL (Structured Query Language) is used to communicate with databases. It is the standard
language for relational database management systems.
2. Basic SQL Commands
a. SELECT
Used to select data from a database.
Example:
SELECT * FROM Students;
b. WHERE
Used to filter records.
Example:
SELECT * FROM Students WHERE age > 18;
c. INSERT INTO
Used to insert new records.
Example:
INSERT INTO Students (name, age) VALUES ('John', 22);
d. UPDATE
Used to modify existing records.
Example:
UPDATE Students SET age = 23 WHERE name = 'John';
e. DELETE
Used to delete records.
Example:
DELETE FROM Students WHERE name = 'John';
3. Operators
a. IN
Used to specify multiple possible values.
Example:
SELECT * FROM Students WHERE age IN (18, 21, 23);
b. LIKE
Used for pattern matching.
Example:
SELECT * FROM Students WHERE name LIKE 'J%';
c. BETWEEN
Selects values within a range.
Example:
SELECT * FROM Students WHERE age BETWEEN 18 AND 25;
d. AND, OR, NOT
Used to filter with multiple conditions.
Example:
SELECT * FROM Students WHERE age > 18 AND name LIKE 'J%';
4. JOINS
a. INNER JOIN
Returns records with matching values in both tables.
Example:
SELECT [Link], Courses.course_name
FROM Students
INNER JOIN Courses ON Students.course_id = [Link];
b. LEFT JOIN
Returns all records from the left table, and matched records from the right table.
Example:
SELECT [Link], Courses.course_name
FROM Students
LEFT JOIN Courses ON Students.course_id = [Link];
c. RIGHT JOIN
Returns all records from the right table, and matched records from the left table.
Example:
SELECT [Link], Courses.course_name
FROM Students
RIGHT JOIN Courses ON Students.course_id = [Link];
d. FULL OUTER JOIN
Returns all records when there is a match in either left or right table.
Example:
SELECT [Link], Courses.course_name
FROM Students
FULL OUTER JOIN Courses ON Students.course_id = [Link];
5. Subqueries
a. Subquery in SELECT
Example:
SELECT name, (SELECT AVG(age) FROM Students) AS average_age FROM Students;
b. Subquery in WHERE
Example:
SELECT * FROM Students WHERE age > (SELECT AVG(age) FROM Students);
6. Advanced SQL
a. GROUP BY
Groups rows that have the same values.
Example:
SELECT age, COUNT(*) FROM Students GROUP BY age;
b. HAVING
Used to filter groups.
Example:
SELECT age, COUNT(*) FROM Students GROUP BY age HAVING COUNT(*) > 1;
c. ORDER BY
Used to sort the result set.
Example:
SELECT * FROM Students ORDER BY age DESC;
d. CASE
Used for conditional logic.
Example:
SELECT name, age,
CASE
WHEN age < 18 THEN 'Minor'
ELSE 'Adult'
END AS age_group
FROM Students;
7. Views
a. CREATE VIEW
Creates a virtual table.
Example:
CREATE VIEW AdultStudents AS
SELECT * FROM Students WHERE age >= 18;
8. Indexes
a. CREATE INDEX
Used to create an index.
Example:
CREATE INDEX idx_age ON Students(age);
9. Transactions
a. BEGIN, COMMIT, ROLLBACK
Used to manage transactions.
Example:
BEGIN;
UPDATE Students SET age = age + 1;
COMMIT;
-- If error
ROLLBACK;
10. Conclusion
SQL is a powerful tool for managing and querying data. With these basics and advanced
techniques, you're well-equipped to handle complex queries.