SQL Common Function and Keyword

Table of contents

1. CREATE DATABASE:

CREATE DATABASE library;

This command creates a new database named “library.”

2. USE DATABASE:

USE library;

This command specifies that we want to use the “library” database for subsequent operations.

3. CREATE TABLE:

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publication_year INT,
    available_copies INT
);

This creates a table named “books” with columns for book_id, title, author, publication_year, and available_copies.

4. INSERT DATA:

INSERT INTO books (book_id, title, author, publication_year, available_copies)
VALUES
    (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 10),
    (2, 'To Kill a Mockingbird', 'Harper Lee', 1960, 15),
    (3, '1984', 'George Orwell', 1949, 12);

This inserts three records into the “books” table.

5. SELECT WITH AGGREGATE FUNCTIONS:

-- Count the total number of books
SELECT COUNT(*) AS total_books FROM books;

-- Find the average publication year
SELECT AVG(publication_year) AS avg_publication_year FROM books;

These queries use the COUNT and AVG aggregate functions to calculate the total number of books and the average publication year.

6. SELECT WITH WHERE CLAUSE:

-- Find books published after 1950
SELECT title, author, publication_year
FROM books
WHERE publication_year > 1950;

This query retrieves books published after the year 1950.

7. UPDATE DATA:

-- Update the available copies of 'The Great Gatsby'
UPDATE books
SET available_copies = 8
WHERE title = 'The Great Gatsby';

This updates the number of available copies for the book “The Great Gatsby” to 8.

8. SELECT WITH JOIN:

CREATE TABLE borrowers (
    borrower_id INT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO borrowers (borrower_id, name)
VALUES
    (1, 'Alice'),
    (2, 'Bob');

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    transaction_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (borrower_id) REFERENCES borrowers(borrower_id)
);

INSERT INTO transactions (transaction_id, book_id, borrower_id, transaction_date)
VALUES
    (1, 1, 1, '2023-01-15'),
    (2, 2, 2, '2023-02-20');

This example creates two more tables (“borrowers” and “transactions”) and establishes foreign key relationships. The “transactions” table records when a book is borrowed by a borrower.

-- Retrieve book transactions with borrower names
SELECT transactions.transaction_id, books.title, borrowers.name, transactions.transaction_date
FROM transactions
JOIN books ON transactions.book_id = books.book_id
JOIN borrowers ON transactions.borrower_id = borrowers.borrower_id;

This query uses the JOIN keyword to retrieve information about book transactions, including book titles and borrower names.

9. DELETE DATA:

-- Delete a book from the catalog
DELETE FROM books WHERE book_id = 3;

This deletes the book with book_id 3 from the “books” table.

This example covers a variety of SQL keywords and functions, including CREATE, USE, INSERT, SELECT with aggregate functions, UPDATE, SELECT with a WHERE clause, CREATE and INSERT with foreign keys, JOIN, and DELETE. Each command or query serves a specific purpose in managing and retrieving data from a database.

10. SELECT WITH GROUP BY:

-- Count the number of transactions per book
SELECT books.title, COUNT(transactions.transaction_id) AS transaction_count
FROM books
LEFT JOIN transactions ON books.book_id = transactions.book_id
GROUP BY books.title;

This query uses the GROUP BY clause along with the COUNT function to count the number of transactions for each book.

11. SELECT WITH ORDER BY:

-- Retrieve books ordered by publication year in descending order
SELECT title, author, publication_year
FROM books
ORDER BY publication_year DESC;

This query retrieves books ordered by their publication year in descending order using the ORDER BY clause.

12. SELECT WITH LIKE:

-- Find books with titles containing 'the'
SELECT title, author
FROM books
WHERE title LIKE '%the%';

The LIKE operator is used to find books with titles containing the word ‘the’.

13. SELECT WITH CASE WHEN:

-- Classify books as old or new based on publication year
SELECT title, publication_year,
    CASE
        WHEN publication_year < 2000 THEN 'Old'
        ELSE 'New'
    END AS classification
FROM books;

The CASE WHEN statement is used to create a new column that classifies books as ‘Old’ or ‘New’ based on their publication year.

14. SELECT DISTINCT:

-- Retrieve distinct authors from the books table
SELECT DISTINCT author
FROM books;

The DISTINCT keyword is used to retrieve unique values. In this case, it retrieves distinct author names from the “books” table.

15. SELECT WITH LIMIT:

-- Retrieve the first two rows from the books table
SELECT *
FROM books
LIMIT 2;

The LIMIT clause is used to restrict the number of rows returned by a query. In this example, it retrieves the first two rows from the “books” table.

16. SELECT WITH BETWEEN:

-- Retrieve books published between 1990 and 2000
SELECT title, author, publication_year
FROM books
WHERE publication_year BETWEEN 1990 AND 2000;

The BETWEEN operator is used to filter results within a specific range. This query retrieves books published between 1990 and 2000.

These additional examples introduce more SQL functions and keywords, such as GROUP BY, ORDER BY, LIKE, CASE WHEN, DISTINCT, LIMIT, and BETWEEN. These functionalities provide powerful tools for querying and manipulating data in a SQL database.

17. Mathematical Functions:

-- Calculate the square root of available copies for each book
SELECT title, author, available_copies, SQRT(available_copies) AS sqrt_copies
FROM books;

In this query, the SQRT function is used to calculate the square root of the available copies for each book.

18. String Functions:

-- Concatenate title and author, and convert to uppercase
SELECT title, author, UPPER(CONCAT(title, ' by ', author)) AS book_info
FROM books;

This query uses the CONCAT and UPPER functions to concatenate the title and author, and then convert the result to uppercase.

19. Date Functions:

-- Calculate the age of each book based on the publication year
SELECT title, publication_year, YEAR(CURRENT_DATE()) - publication_year AS book_age
FROM books;

Here, the YEAR function is used to calculate the age of each book by subtracting the publication year from the current year.

20. Aggregate Functions with GROUP BY:

-- Find the average available copies per author
SELECT author, AVG(available_copies) AS avg_copies
FROM books
GROUP BY author;

This query uses the AVG function with the GROUP BY clause to find the average available copies for each author.

21. Subqueries:

-- Find books where the available copies exceed the average for the author
SELECT title, author, available_copies
FROM books
WHERE available_copies > (
    SELECT AVG(available_copies)
    FROM books AS b
    WHERE b.author = books.author
);

This query uses a subquery to find books where the available copies exceed the average for the respective author.

22. CASE Statement with Aggregate Function:

-- Classify authors based on the total available copies of their books
SELECT author,
    CASE
        WHEN SUM(available_copies) > 20 THEN 'Popular'
        ELSE 'Less Popular'
    END AS popularity_status
FROM books
GROUP BY author;

In this example, the CASE statement is used with the SUM aggregate function to classify authors based on the total available copies of their books.

23. Window Functions (Ranking):

-- Rank books based on available copies within each author's work
SELECT title, author, available_copies,
    RANK() OVER (PARTITION BY author ORDER BY available_copies DESC) AS rank_within_author
FROM books;

The RANK window function is used to rank books based on available copies within each author’s work.

24. COALESCE Function:

-- Display the title and author, and replace NULL values in publication year with 'Unknown'
SELECT title, author, COALESCE(publication_year, 'Unknown') AS pub_year
FROM books;

The COALESCE function is used to replace NULL values in the publication year column with the string ‘Unknown’.

These examples demonstrate a variety of SQL functions and keywords, including mathematical functions, string functions, date functions, aggregate functions with GROUP BY, subqueries, the CASE statement, window functions, and the COALESCE function. Each of these features contributes to the flexibility and power of SQL for querying and analyzing data in a relational database.