Download SQL Introduction for Data Analyst and more Thesis Computer Science in PDF only on Docsity!
SQL Fundamentals – Complete Detailed
Study Notes (Beginner to Advanced
Foundation)
1. Introduction to Databases
A database is an organized collection of structured data stored electronically. It allows efficient storage, retrieval, and management of data. Databases are essential in modern applications such as banking systems, e-commerce platforms, and enterprise software. Relational databases store data in tables consisting of rows and columns. Each table represents an entity, and relationships can exist between multiple tables. SQL is used to interact with these databases in a standardized manner.
2. What is SQL?
SQL stands for Structured Query Language and is used to communicate with relational databases. It allows users to perform operations such as querying data, inserting records, updating information, and deleting entries. SQL is declarative, meaning users specify what they want rather than how to achieve it. It is supported by popular systems like MySQL, PostgreSQL, Oracle, and SQL Server. Understanding SQL is essential for developers, analysts, and data professionals.
3. SQL Command Categories
SQL commands are divided into categories based on functionality. DDL (Data Definition Language) defines database structure using CREATE, ALTER, and DROP. DML (Data Manipulation Language) modifies data using INSERT, UPDATE, DELETE. DQL (Data Query Language) retrieves data using SELECT. DCL (Data Control Language) controls permissions using GRANT and REVOKE. TCL (Transaction Control Language) manages transactions using COMMIT and ROLLBACK.
4. Creating and Managing Databases
Databases are created using CREATE DATABASE statement. Example: CREATE DATABASE company; Switching databases is done using USE command. Deleting a database is done using DROP DATABASE. Database design should consider normalization and relationships.
5. Table Creation in Detail
Tables define the structure of data with columns and data types. Each column has a data type such as INT, VARCHAR, DATE, or FLOAT. Primary keys uniquely identify each row in a table. Example: CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), salary FLOAT, hire_date DATE );
6. Data Types in SQL
INT is used for integers. VARCHAR is used for variable-length text. DATE stores date values. FLOAT stores decimal values. Choosing correct data type improves performance and storage efficiency.
7. Inserting Data (Advanced)
INSERT INTO is used to add records. Example: INSERT INTO employees (id, name, salary) VALUES (1, 'Amit', 50000); Multiple rows can be inserted in one query. Example: INSERT INTO employees VALUES (2, 'Rahul', 60000), (3, 'Neha', 55000); Always ensure correct column order.
13. Constraints in Detail
PRIMARY KEY uniquely identifies rows. FOREIGN KEY maintains relationships between tables. NOT NULL ensures values cannot be empty. UNIQUE ensures all values are distinct. CHECK enforces conditions on data.
14. Joins (Introduction)
Joins combine data from multiple tables. INNER JOIN returns matching rows. LEFT JOIN returns all rows from left table. RIGHT JOIN returns all rows from right table. Joins are essential for relational database queries.
15. Aggregate Functions
Aggregate functions perform calculations on data. COUNT() counts rows. SUM() calculates total. AVG() calculates average. MIN() and MAX() find smallest and largest values.
16. GROUP BY and HAVING
GROUP BY groups rows based on column values. Example: SELECT department, COUNT() FROM employees GROUP BY department; HAVING filters grouped data. Example: HAVING COUNT() > 2;
17. Subqueries
A subquery is a query inside another query. Example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); They are useful for complex filtering and analysis.
18. Transactions
Transactions ensure data consistency. COMMIT saves changes. ROLLBACK reverts changes. ACID properties ensure reliability.
19. Indexes
Indexes improve query performance. They speed up data retrieval. Example: CREATE INDEX idx_name ON employees(name); Too many indexes can slow down inserts.
20. Real-World Example
A company stores employee data in a database. SQL queries are used to analyze salaries, departments, and performance. Managers use reports generated through SQL for decision-making.
21. Best Practices
Use meaningful table and column names. Always use WHERE in UPDATE and DELETE. Normalize data to reduce redundancy. Write optimized queries for performance.
22. Summary
SQL is essential for managing relational data. It supports data definition, manipulation, and analysis. Strong SQL skills are required for data-related careers.
