Skip to main content

SQL

Introduction​

This unit introduces SQL (Structured Query Language).

What is SQL?​

  • Structured Query Language: Language for interacting with relational databases.
  • Set-oriented, not procedural.

SQL has three primary categories:

  • DDL (Data Definition Language): Define structures (CREATE, ALTER, DROP).
  • DML (Data Manipulation Language): Modify data (INSERT, UPDATE, DELETE).
  • DQL (Data Query Language): Query data (SELECT).

Data Definition Language (DDL)​

Creating Tables​

CREATE TABLE IF NOT EXISTS Users (
  UserId SERIAL PRIMARY KEY,
  UserName VARCHAR(200) UNIQUE,
  EmailId VARCHAR(300) NOT NULL,
  Password VARCHAR(200) NOT NULL,
  isAdmin BOOLEAN NOT NULL
);

Altering Tables​

ALTER TABLE Recipes ADD COLUMN RecipeAddedDate TIMESTAMP;
ALTER TABLE Recipes ADD CONSTRAINT fk_user FOREIGN KEY (AuthorId) REFERENCES Users(UserId);

Dropping Tables​

DROP TABLE IF EXISTS Users;

Best Practice: Always verify before dropping tables to avoid data loss.

Data Manipulation Language (DML)​

Inserting Data​

INSERT INTO Users
  (UserName, EmailId, isAdmin)
VALUES
  ('FoodVoyager', 'food@cook.com', TRUE);

Updating Data​

UPDATE Recipes
SET AuthorId = 3
WHERE RecipeId = 2;

Deleting Data​

DELETE FROM Users
WHERE UserId = 5;

Data Query Language (DQL)​

Basic SELECT​

SELECT * FROM Recipes;
SELECT RecipeName, PrepTimeMins FROM Recipes;

WHERE Clause​

SELECT *
FROM Recipes
WHERE AuthorId = 3;

Filtering and Sorting​

SELECT *
FROM Recipes
WHERE RecipeName LIKE '%chicken%'
ORDER BY PrepTimeMins ASC;

DISTINCT​

SELECT DISTINCT AuthorId FROM Recipes;

Aggregation Functions​

FunctionPurpose
SUM()Add numeric values
AVG()Average numeric values
COUNT()Count rows
MIN()Smallest value
MAX()Largest value

Example:

SELECT COUNT(*) AS "Number of Recipes" FROM Recipes;

Working with Dates​

  • TIMESTAMP stores seconds since 1970-01-01.
  • Date Queries:
SELECT NOW();
SELECT * FROM Recipes WHERE RecipeAddedDate >= '2024-09-27';

Grouping Data​

GROUP BY​

SELECT AuthorId, COUNT(*) AS "Recipe Count"
FROM Recipes
GROUP BY AuthorId;

HAVING​

SELECT AuthorId, COUNT(*) AS "Recipe Count"
FROM Recipes
GROUP BY AuthorId
HAVING COUNT(*) > 2;

Subqueries​

Subqueries allow querying based on another query's result.

Example​

SELECT RecipeName, (PrepTimeMins + CookTimeMins) AS "Total Duration"
FROM Recipes
WHERE
  (PrepTimeMins + CookTimeMins) > (
    SELECT AVG(PrepTimeMins + CookTimeMins)
    FROM Recipes
  );

Best Practices​

  • Normalize data to avoid redundancy.
  • Always define proper primary and foreign keys.
  • Validate data before inserting into databases.
  • Use LIMIT when querying large datasets during development.
  • Backup before making schema changes (ALTER, DROP).

Reflection Questions​

  1. What happens if you omit WHERE in an UPDATE statement?
  2. How do you perform a basic SELECT query with a WHERE condition?

Summary​

  • SQL allows you to define, modify, and query databases.

Disclaimer: Generative AI was used in part to generate these lecture notes.