SQL Lecture Notes

Course Overview

• Complete SQL course along with special announcement for interested web development students.
• Delta Batch: Comprehensive front-end, back-end, and database development over 4.5-month course.
• Live sessions: Project building, resume preparation, off-campus applications.
• Delta batch link available, 20% offer until 21st August, new batch from 31st August.

SQL Course Content

• SQL Language: Used by companies like Instagram, Uber, Amazon, Netflix for database access.
• Purpose: Software engineer prep, learning practical SQL for data-related jobs, development roles.
• Notes and practice questions: Available for download to revise commands.

Basic Database Concepts

Databases

• Databases: Collection of data stored in an easily accessible format.
• Example Companies: Google, Facebook, Instagram, Microsoft.
• Uses: Storing user information like email, password.

Database Management System (DBMS)

• DBMS: Software to manage databases (add, delete, update data).
• Interaction: Users access databases via DBMS.
• Layer: DBMS acts as a layer between user and database.

Types of Databases

• Relational Databases (RDBMS): Stores data in table format, uses SQL.
• Examples: MySQL, Oracle Database, Microsoft SQL Server.
• Non-Relational Databases (NoSQL): Does not use table format, examples include MongoDB.
• Focus: Course focuses on relational databases.

SQL Key Terms

Structured Query Language (SQL)

• SQL: Structured Query Language for interacting with relational databases.
• Purpose: Perform CRUD operations (Create, Read, Update, Delete) on databases.

SQL Operations

• Create: Create databases or tables.
• Read: Fetch data from databases.
• Update: Modify existing data.
• Delete: Remove data records.
• Case Sensitivity: SQL can be written in either uppercase or lowercase.
• Syntax examples:
  • CREATE DATABASE database_name;
  • DROP DATABASE IF EXISTS database_name;
  • SHOW DATABASES;

SQL Commands and Keywords

CRUD Operations

• Create Table: CREATE TABLE table_name (column definitions);
• Alter Table: Modify table structure (add/drop/modify columns).
• Truncate Table: Remove all rows from a table, TRUNCATE TABLE table_name;

Data Types and Constraints

• Data Types: Integer, Character, Date, etc.
• Primary Key: Unique and not null value, identifies each row.
• Foreign Key: References a primary key in another table.
• Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT.

SQL Queries

• Select Statement: SELECT columns FROM table_name;
• Where Clause: Filter data, WHERE condition;
• Order By Clause: Sort data, ORDER BY column;
• Group By Clause: Group data, GROUP BY column;
• Having Clause: Condition with group by, HAVING condition;
• Limit Clause: Limit number of rows returned, LIMIT number;
• Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN().

Joins

• Inner Join: Returns matching records between tables.
• Left Join: All from left table, matching from right.
• Right Join: All from right table, matching from left.
• Full Join: All from both tables, returns NULL for non-matching records.
• Self Join: Table joined with itself.

Subqueries

• Subqueries: Nested queries within a larger SQL query.
• Usage: Nested in WHERE, FROM, SELECT clauses.

Views

• Views: Virtual tables created using a query.
• Syntax: CREATE VIEW view_name AS SELECT query;
• Used for: Simplifying complex queries, providing security.

Practice Question Example

• Scenario: Create database, tables and perform CRUD operations.
• Example Task: Create table employees, insert data, update records, delete records using conditions.

Tips for SQL Practice

• Hands-on Practice: Essential for mastering SQL.
• Review Notes: Download and frequently review provided notes.
• Explore Live Sessions: Participate in live projects and sessions for deeper understanding.

---

• Note: This is a summarized guide. For detailed commands and examples, refer to full course materials and notes.
• Practice regularly to solidify understanding and improve SQL command fluency.