SQL Lecture Notes

Introduction

• Complete SQL course
• Special announcement: Web Development batches available on college website
  • Duration: 4.5 months
  • Covers front-end, back-end, database
  • Includes live sessions on project building, resume building, off-campus applications
  • Special offer: 20% discount till 21st August
  • New batch starts from 31st August
• Download notes link for each topic covered

Course Outline

• Learning SQL from basics to advanced
• Executing and practicing SQL commands
• SQL’s importance in major companies (Instagram, Uber, Amazon, Netflix)
• Goals: Software Engineering roles, Practical SQL, Development-related roles

Database Basics

What is a Database?

• Collection of data
• Stored in an easily accessible format
• Examples: Google, Facebook, Instagram, Microsoft
• Database Types:
  • Relational (stored in tables)
  • Non-relational (NoSQL)
• RDMS (Relational Database Management System)
  • Examples: MySQL, SQL Server
  • Non-relational Examples: MongoDB

DBMS (Database Management System)

• Software application to manage databases
• Operations: Add, delete, update data
• Interact with DBMS using SQL

Key Terms

Database Creation

• Command: CREATE DATABASE database_name;

Database Deletion

• Command: DROP DATABASE database_name;

Using The Database

• Command: USE database_name;

Tables in SQL

Creating Tables

• Syntax: CREATE TABLE table_name (column_name data_type constraints);
• Examples:
  • CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT NOT NULL);
  • CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Name VARCHAR(50), Salary FLOAT);

Basic Operations

• Insert Data: INSERT INTO table_name (columns) VALUES (values);
• Select Data: SELECT columns FROM table_name;
• Update Data: UPDATE table_name SET column=value WHERE condition;
• Delete Data: DELETE FROM table_name WHERE condition;

Data Types

• Numeric Types: INT, FLOAT, DECIMAL
• Character Types: CHAR, VARCHAR
• Date and Time Types: DATE, TIME, DATETIME
• Boolean: TRUE or FALSE

Table Constraints

• NOT NULL: Ensures a column cannot have a NULL value
• UNIQUE: Ensures all values in a column are unique
• PRIMARY KEY: Combination of NOT NULL and UNIQUE
• FOREIGN KEY: Uniquely identifies a row in another table
• DEFAULT: Sets a default value for a column
• CHECK: Ensures all values in a column satisfy a specific condition

Aggregating and Managing Data

Aggregate Functions

• COUNT(): Returns number of rows
• SUM(): Returns sum of a numeric column
• AVG(): Returns average value
• MIN(): Returns minimum value
• MAX(): Returns maximum value

Grouping Data

• GROUP BY: Groups rows sharing a property so aggregate functions can be applied to each group
• HAVING: Filters groups based on conditions

SQL Joins

• Combines rows from two or more tables
• Types of Joins:
  • INNER JOIN: Returns rows with matching values in both tables
  • LEFT JOIN: Returns all rows from the left table, and matched rows from the right table
  • RIGHT JOIN: Returns all rows from the right table, and matched rows from the left table
  • FULL JOIN: Returns rows when there is a match in one of the tables
  • SELF JOIN: A table is joined with itself

Subqueries in SQL

• Nested queries inside a larger query
• Types:
  • Inline Subquery
  • Derived table (subquery in the FROM clause)
  • Correlated subquery (subquery uses values from the outer query)

SQL Views

• Virtual tables based on the result-set of an SQL statement
• Use: Security, Simplifying complex queries
• Create View: CREATE VIEW view_name AS SELECT column(s) FROM table WHERE condition;
• Drop View: DROP VIEW view_name;

Practical Exercise

Activity 1: Create, Alter, and Drop Tables

1. Create Database: CREATE DATABASE College;
2. Create Tables:

   CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT NOT NULL, City VARCHAR(50));
   CREATE TABLE Courses (CourseID INT PRIMARY KEY, CourseName VARCHAR(50));
   CREATE TABLE Enrollments (StudentID INT, CourseID INT, 
            FOREIGN KEY (StudentID) REFERENCES Students(ID), 
            FOREIGN KEY (CourseID) REFERENCES Courses(CourseID));
   

3. Insert Data:

   INSERT INTO Students VALUES (1, 'John Doe', 20, 'New York');
   INSERT INTO Courses VALUES (101, 'Mathematics');
   INSERT INTO Enrollments VALUES (1, 101);
   

Activity 2: Perform SQL JOINs

1. Inner Join: SELECT Students.Name, Courses.CourseName FROM Students INNER JOIN Enrollments ON Students.ID = Enrollments.StudentID INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
2. Left Join: SELECT Students.Name, Courses.CourseName FROM Students LEFT JOIN Enrollments ON Students.ID = Enrollments.StudentID LEFT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
3. Right Join: SELECT Students.Name, Courses.CourseName FROM Students RIGHT JOIN Enrollments ON Students.ID = Enrollments.StudentID RIGHT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
4. Full Join: SELECT Students.Name, Courses.CourseName FROM Students FULL OUTER JOIN Enrollments ON Students.ID = Enrollments.StudentID FULL OUTER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Activity 3: Using SQL Views

1. Create View: CREATE VIEW StudentCourses AS SELECT Students.Name, Courses.CourseName FROM Students INNER JOIN Enrollments ON Students.ID = Enrollments.StudentID INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
2. Select from View: SELECT * FROM StudentCourses;
3. Drop View: DROP VIEW StudentCourses;