Rauf: AI and ML

Keys in Databases

Keys are fundamental in database relationships. Primary keys uniquely identify records, while foreign keys link tables together.

Primary Keys

A primary key is a unique identifier for a record in a table. It must be unique for each record and cannot be NULL.

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

Foreign Keys

A foreign key is a reference to a primary key in another table, establishing a relationship between two tables.

CREATE TABLE reviews (
    review_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    review TEXT,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Subqueries

Subqueries are queries within queries. They allow you to fetch data based on another query’s result.

SELECT name FROM books WHERE book_id = (
    SELECT book_id FROM reviews WHERE review = 'Great Book'
);

Using IN

The IN operator allows you to check if a value is in a list of values.

SELECT name FROM books WHERE book_id IN (
    SELECT book_id FROM reviews WHERE review = 'Excellent'
);

JOINs

JOINs allow you to combine rows from two or more tables based on a related column.

SELECT books.title, authors.name FROM books
JOIN authors ON books.author_id = authors.id;

Using Sets

SQL set operations like UNION, INTERSECT, and EXCEPT help combine or compare result sets.

SELECT title FROM books
UNION
SELECT title FROM articles;

Grouping Results

The GROUP BY clause allows you to group rows that share common values and perform aggregate functions on them.

SELECT author, COUNT(book_id) FROM books
GROUP BY author;