Scribd
Upload
2 views2 pages

Advanced SQL Learning Guide

The document is a comprehensive SQL learning guide covering topics from database basics to advanced concepts. It includes explanations of CRUD operations, joins, aggregate functions, normalization, and real-world examples, along with practice and interview questions. Additionally, it suggests mini projects to apply the learned skills.

Uploaded by

Malle Venkatakumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views2 pages

Advanced SQL Learning Guide

The document is a comprehensive SQL learning guide covering topics from database basics to advanced concepts. It includes explanations of CRUD operations, joins, aggregate functions, normalization, and real-world examples, along with practice and interview questions. Additionally, it suggests mini projects to apply the learned skills.

Uploaded by

Malle Venkatakumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Complete SQL Learning Guide (Beginner to Advanced)

1. Database Basics
A database is an organized collection of data. SQL is used to interact with databases.

1 Table: Stores data in rows and columns


2 Primary Key: Unique identifier
3 Foreign Key: Links tables
2. CRUD Operations
INSERT INTO users VALUES (1, 'John');

SELECT * FROM users;

UPDATE users SET name='Sam' WHERE id=1;

DELETE FROM users WHERE id=1;

3. Joins
Joins combine data from multiple tables.
SELECT * FROM A INNER JOIN B ON [Link] = [Link];

4. Aggregate Functions
SELECT COUNT(*), AVG(salary) FROM employees;

5. GROUP BY & HAVING


SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5;

6. Subqueries
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

7. Indexes
Indexes improve query performance.
CREATE INDEX idx_name ON emp(name);

8. Views
CREATE VIEW emp_view AS SELECT name FROM emp;

9. Stored Procedures
CREATE PROCEDURE GetAll() BEGIN SELECT * FROM emp; END;

10. Triggers
CREATE TRIGGER trg AFTER INSERT ON emp FOR EACH ROW BEGIN INSERT INTO log VALUES
('added'); END;

11. Transactions (ACID)


1 Atomicity

2 Consistency

3 Isolation

4 Durability
BEGIN; UPDATE acc SET bal=bal-100; COMMIT;
12. Normalization
1 1NF: Remove repeating groups
2 2NF: Remove partial dependency
3 3NF: Remove transitive dependency
13. Real World Example (E-commerce)
Customers, Orders, Products tables linked using foreign keys.

14. Practice Questions


1 Find second highest salary

2 List duplicate records

3 Get employees with no department

4 Top 5 highest salaries

5 Count users per city

6 Find second highest salary

7 List duplicate records

8 Get employees with no department

9 Top 5 highest salaries

10 Count users per city

11 Find second highest salary

12 List duplicate records

13 Get employees with no department

14 Top 5 highest salaries

15 Count users per city

16 Find second highest salary

17 List duplicate records

18 Get employees with no department

19 Top 5 highest salaries

20 Count users per city

15. Interview Questions


1 What is SQL?

2 Difference between WHERE and HAVING?

3 What are joins?

4 What is normalization?

5 What is index?

16. Mini Projects


1 Student Management System
2 E-commerce Database
3 Employee Payroll System

You might also like