SQL Course Lecture Notes

Special Announcement

• Web Development Course available on college website (Delta batch)
• 4.5 months course covering complete front-end, back-end, and databases
• Includes project building, off-campus interview training, resume building, live sessions
• 20% special offer till August 21, 6:00 PM
• New batch starts on August 31
• Enroll on the college website with the link in the description box

SQL Course Overview

• Complete SQL from scratch to advanced
• Not just commands, but execution and practice questions
• SQL is widely used in companies like Instagram, Uber, Amazon, Netflix
• Useful for software engineering, data-related jobs, development roles
• Comprehensive notes available for all topics covered
• Practice questions provided for revision

Basics of Databases

What is a Database?

• Collection of data in a format that is easily accessible, searchable, addable, and deletable
• Companies like Google, Facebook, Instagram, Microsoft store vast amounts of data in their databases
• Databases are usually digital, stored on computer systems

Database Management System (DBMS)

• Software application that manages databases
• Allows adding, deleting, updating, searching data
• Users interact with the database through DBMS

Types of Databases

• Relational Databases: Data stored in tables, known as RDBMS (Relational Database Management Systems)
  • Examples: MySQL, PostgreSQL, Microsoft SQL Server
• Non-Relational Databases: Data stored without tables, known as NoSQL databases (e.g., MongoDB)

Introduction to SQL

What is SQL?

• Stands for Structured Query Language
• Used to interact with relational databases
• Programming language to perform operations known as CRUD (Create, Read, Update, Delete)
• SQL commands are similar to basic English
• Example operations: creating a database, reading data, updating data, deleting data

SQL Syntax and Operations

Creating and Dropping Databases

• Create Database CREATE DATABASE database_name; CREATE DATABASE IF NOT EXISTS database_name;
• Drop Database DROP DATABASE database_name; DROP DATABASE IF EXISTS database_name;
• Show Databases SHOW DATABASES;
• Use Database USE database_name;

Creating and Manipulating Tables

• Create Table CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... );
• Drop Table DROP TABLE table_name;
• Insert Data into Table INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• Select Data from Table SELECT * FROM table_name; SELECT column1, column2 FROM table_name;
• Update Data in Table UPDATE table_name SET column1 = value1 WHERE condition;
• Delete Data from Table DELETE FROM table_name WHERE condition;
• Table Constraints
  • NOT NULL: Ensures column cannot have null value
  • UNIQUE: Ensures all values in column are unique
  • PRIMARY KEY: Unique identifier for each row, cannot be null
  • FOREIGN KEY: Links two tables together
  • DEFAULT: Sets a default value for a column if none is provided
  • CHECK: Ensures all values in a column satisfy a specific condition

SQL Select Clauses

• WHERE Clause: Filter records SELECT column1, column2 FROM table_name WHERE condition;
• ORDER BY Clause: Sort records SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];
• LIMIT Clause: Limit the number of records SELECT column1, column2 FROM table_name LIMIT number;
• GROUP BY Clause: Group records into summary rows SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
• HAVING Clause: Filter groups SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > value;

SQL Joins

• INNER JOIN: Selects records with matching values in both tables SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
• LEFT JOIN: Selects all records from the left table, and the matched records from the right table SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
• RIGHT JOIN: Selects all records from the right table, and the matched records from the left table SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
• FULL OUTER JOIN: Selects all records when there is a match in either left or right table SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
• SELF JOIN: Joins the table to itself SELECT a.column1, b.column2 FROM table a, table b WHERE a.common_column = b.common_column;

SQL Aggregate Functions

• COUNT(): Returns the number of rows SELECT COUNT(column_name) FROM table_name;
• SUM(): Returns the total sum of a numeric column SELECT SUM(column_name) FROM table_name;
• AVG(): Returns the average of a numeric column SELECT AVG(column_name) FROM table_name;
• MIN(): Returns the minimum value SELECT MIN(column_name) FROM table_name;
• MAX(): Returns the maximum value SELECT MAX(column_name) FROM table_name;

SQL Subqueries

• Subqueries can be placed in various parts of a SQL query: SELECT, FROM, WHERE
• Example: SELECT column1 FROM table1 WHERE column2 > ( SELECT AVG(column2) FROM table1 );

SQL Views

• Virtual table based on the result-set of an SQL statement
• Does not store data, just shows it
• Create View CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
• Drop View DROP VIEW view_name;

• Remember to practice frequently to master SQL commands.
• More practice questions and notes available in the provided links.