# Need of Database Data comes in all shapes and sizes, and the ways that we deal with it will vary according to the nature of the data. In some cases, the data is simple—perhaps a single number such as the value of π that might be built into a program that draws circles. The application itself may have this as a hard-coded value for the ratio of the circumference of a circle to its diameter. We call this kind of data constant, as it will never need to change. ### Where can we store this details ? As an initial step, we can go with flat files. A **flat file** is a simple text file that stores data in rows and columns, often separated by commas or tabs. It's like an Excel sheet or a CSV file where all information is laid out in a single table. There’s no inherent structure for relationships or links between data. For example, consider Tamil movies. ``` Karnan Dhanush MariSelvaraj 2021 Drama 70Crore 100Crore Tamil BestActor SooraraiPottru Suriya SudhaKongara 2020 Biopic 50Crore 200Crore Tamil NationalAward Vikram KamalHaasan LokeshKanagaraj 2022 Action 150Crore 400Crore Tamil BestFilm ``` The complexity of the above file will increase, if i am adding more actor details into the file, like this, ``` Karnan Dhanush Natrajan Yogi babu MariSelvaraj 2021 Drama 70Crore 100Crore Tamil BestActor SooraraiPottru Suriya SudhaKongara 2020 Biopic 50Crore 200Crore Tamil NationalAward Vikram KamalHaasan Fahad LokeshKanagaraj 2022 Action 150Crore 400Crore Tamil BestFilm ``` This problem is referred as **Repeating Group.** ### How to resolve Repeating Group ? The repeating groups problem is very common and is the issue that really started the drive toward more sophisticated database management systems. We can attempt to resolve this problem by using ordinary text files with a little more structure. These are still often referred to as flat files, but they are probably better described as structured text files. ``` [Baasha] year=1995 director=Suresh Krissna genre=Action starring=Rajinikanth starring=Nagma starring=Raghuvaran [Sivaji] year=2007 director=S. Shankar genre=Drama, Action starring=Rajinikanth starring=Shriya Saran starring=Vivek [Nayakan] year=1987 director=Mani Ratnam genre=Crime, Drama starring=Kamal Haasan starring=Janagaraj starring=Nassar [Thalapathi] year=1991 director=Mani Ratnam genre=Drama starring=Rajinikanth starring=Mammootty starring=Shobana [Muthu] year=1995 director=K. S. Ravikumar genre=Comedy, Drama starring=Rajinikanth starring=Meena starring=Sarath Babu ``` We have solved the repeating groups problem by introducing some tags to indicate the type of each element in the record. However, now our application must read and interpret a more complex file just to get its data. Updating a record and searching in this kind of structure can be quite difficult. How can we make sure that the descriptions for genre or classification are chosen from a specific subset? How can we easily produce a sorted list of K.S. Ravikumar directed films? What we need is a general purpose way of storing and retrieving data, not a solution invented many times to fit slightly different, but very similar, problems as in a generic data handling system. What we need is a database and a database management system. # Database and Database Management System ### **What is a Database?** Imagine you have a **library**. In this library, all the books are organized and stored in an orderly fashion. Each book has a title, an author, a genre, and so on. Now, to easily find a book, you need some sort of catalog that helps you search by title, author, genre, etc. This **catalog** is like a **database,** a collection of organized information that can be easily accessed, managed, and updated. In the digital world, instead of books, databases store all kinds of information: from customer details, product inventories, or even social media posts! A **database** is simply a way to store this information in a structured and organized manner so that you can retrieve it easily when needed. ### **What is a DBMS?** Now, imagine that your library has a **librarian**. The librarian’s job is to maintain the library, keep track of the books, check out books to readers, and make sure the books are returned on time. In the world of computers, this librarian is like a **DBMS** (Database Management System). A **DBMS** is software that helps manage and control how data is stored, accessed, and updated in a database. It allows people and applications to interact with the database without worrying about how the data is physically stored on disk. Just like the librarian ensures everything is in order, the DBMS ensures that the data is organized, safe, and easy to retrieve. ### **Types of Database Models** Just like there are different ways to organize a library (such as by genre, author, or alphabetically), there are different **models** for organizing and managing data in a database. Let’s take a look at a few common database models and use simple analogies to explain each. #### a. Hierarchical Database Model Now, imagine you organize the library in a **family tree-like structure**. Books are organized by **categories**. For example, you could have a main category called **"Fiction"**, and underneath that, you have sub-categories like **"Science Fiction"** and **"Historical Fiction"**. Each of these sub-categories can have books listed under them. The **hierarchical model** works like this data is organized in a **tree-like structure** with one parent category leading to multiple child categories. or example, in a book catalog: * Fiction * Science Fiction * "Dune" * "The Martian" * Historical Fiction * "The Book Thief" * "War and Peace" This structure is easy to understand, but it’s rigid. What if a book fits into more than one category? For instance, if "Dune" is both **Science Fiction** and **Adventure**, the hierarchical model makes it difficult to store this book in both categories without duplication. #### **b. Network Database Model** The **network model** is like a **web** of relationships between data. Instead of organizing data strictly into a parent-child structure, this model allows data to be linked in more complex ways. Imagine if, in the library, books could belong to multiple categories at once, and the categories themselves could have cross-links. For example: * A book like **"The Martian"** could be part of both **Science Fiction** and **Adventure** categories. * The **Adventure** category might link to **Historical Fiction** because some historical books can also be adventurous. In this model, data points are linked in a more flexible, interconnected way, like a **web of relationships**. This allows for more complex data storage but can also get confusing to manage as the web grows. #### **c. Relational Database Model** The **relational model** is like the **best-organized library**. Imagine every book in the library is in a separate shelf, and each shelf is labeled. Instead of just having one big list or tree, you organize the books into separate categories based on different criteria, like **books**, **authors**, **genres**, and **borrowers**. These separate categories are like **tables**, and you can link them together through **relationships**. For example: * A **Books Table** lists all the books. * An **Authors Table** lists the authors. * A **Genres Table** lists all genres. Each table has a unique **identifier** (like a library card number) to connect the pieces of information. If you want to know which books an author has written, you can look up that author’s unique ID and see which books are related. This model is **flexible** and allows you to store vast amounts of data with **relationships** between them. You can easily query the database, like asking the librarian for all books in the "Science Fiction" genre or by a specific author. # What is Relational Database Model ? The **relational database model** is a way of organizing and storing data in a structured format using tables, which are composed of rows and columns. The primary concept behind this model is the relationship between different data entities. ### Library Analogy Imagine a **relational database** as a **library**, where information is organized in a very structured way to make it easy to find and manage. 1. **Tables as Bookshelves:** * In a library, there are many **bookshelves**. Each bookshelf contains books related to a specific topic (e.g., history, science, or fiction). Similarly, in a relational database, each **table** is like a bookshelf, and it stores data related to a specific category (e.g., customers, orders, products). 2. **Rows as Books:** * Each **row** in a table is like a **book** on a bookshelf. A row contains all the information about a single entity, just like a book contains detailed information on a particular subject. For instance, a row in a **Customer** table might represent all the details about a specific customer (name, address, phone number). 3. **Columns as Information Fields:** * The **columns** in a table are like the **chapters** or sections of a book, each containing specific information. For example, a column for "CustomerName" in the **Customers** table is like a chapter in the book that details the name of the person. Other columns might be like chapters on address, email, or phone number. 4. **Primary Key as ISBN:** * Each **book** in the library has a unique identifier (like an **ISBN** number). This ensures that no two books are the same. In the same way, each **row** in a table has a **primary key**, which is a unique identifier for each record in the table (e.g., "CustomerID" in the **Customers** table). This key ensures that every row can be uniquely identified, just like every book can be found with its ISBN. 5. **Foreign Key as a Reference to Another Book:** * If a book references another book, it’s like having a **footnote** or **reference** to another book on the bookshelf. For example, a **book** in a **history section** might refer to a book in the **geography section**. In the same way, a **foreign key** in one table (like **CustomerID** in the **Orders** table) refers to the primary key of another table (like the **CustomerID** in the **Customers** table). This establishes a connection or relationship between the two tables, just as referencing one book might link to another. 6. **Relationships as Connections Between Bookshelves:** * Imagine that there are **multiple bookshelves** in the library, and some books refer to others across different bookshelves. For instance, a book on **history** might reference a book on **geography**. This is similar to how tables in a relational database are connected by **relationships**. For example, a **Customer** table is connected to an **Orders** table via a foreign key (the **CustomerID**), creating a **relationship** between the two. You can easily find all the books (or records) that are related to each other by these references. 7. **Queries as Searching for Books:** * If you wanted to find specific information in the library, you could go to the **library catalog** and search for books by title, author, or subject. In the relational database, you use **SQL queries** to search for and retrieve specific information from the tables. For instance, if you want to find all orders placed by a specific customer, you can "query" the database in the same way you'd search the library catalog for all books written by a specific author. 8. **Normalization as Organizing the Library Efficiently:** * In a library, books are organized so that similar topics are grouped together to avoid duplication (e.g., you don't store the same book on multiple shelves). Similarly, **normalization** in a relational database ensures that data is stored efficiently, reducing redundancy. For example, customer information might be stored in one table, and order details in another, linked together by a reference (foreign key). This prevents storing the same customer information multiple times, just as you wouldn’t want to have multiple copies of the same book on different shelves. ### What is Data Integrity ? To understand **data integrity** in a relational database, let’s continue with the **library analogy**. Imagine you’re managing a **library** where the books are carefully organized on bookshelves (tables), and you must ensure that the information about the books, their authors, and other details is accurate, consistent, and trustworthy. **Data integrity** is like the set of rules and procedures that ensure the library’s catalog remains correct, reliable, and up-to-date. #### Key Types of Integrity: 1. **Entity Integrity (Uniqueness):** * **Analogy**: Every book in the library must have a unique **ISBN** or identifier so that no two books are confused with each other. This is similar to how in a relational database, every row must have a unique **primary key** that identifies it unambiguously. * **Data Integrity**: In the database, the **primary key** (like **CustomerID** in a **Customer** table) guarantees that every record is unique and can be easily found without duplication. Just as two books can’t share the same ISBN, two rows cannot share the same primary key. 2. **Referential Integrity (Correct Links Between Data):** * **Analogy**: Imagine there’s a **cross-reference** in a history book pointing you to another book in the library on the same topic, and you want to ensure that the book being referred to is actually there on the shelf. You wouldn’t want the reference to point to a book that doesn’t exist. * **Data Integrity**: In a relational database, **referential integrity** ensures that **foreign keys** (the references between tables) correctly point to valid data in other tables. For instance, in the **Orders** table, each order should reference an existing **CustomerID** in the **Customers** table. If a foreign key points to a non-existent record (like a reference to a missing book), it would break the integrity of the system. The database enforces rules to ensure foreign keys always reference valid, existing records. 3. **Domain Integrity (Valid Data in Each Field):** * **Analogy**: Every book in the library has a **category**—such as fiction, non-fiction, or science fiction—and must follow a valid classification system. You can’t put a science fiction book in the history section, just as you can’t assign an **invalid** category to a book. * **Data Integrity**: Similarly, **domain integrity** ensures that each column in a table only contains valid data. For example, the **Email** column in a **Customers** table should contain only valid email addresses (not a number or a date), just like how the **category** field for a book can’t contain something nonsensical like "1234" if it’s supposed to list genres like “fiction” or “history.” 4. **User-Defined Integrity (Custom Rules for Your Data):** * **Analogy**: Imagine that the library has a specific rule, such as: "All books in the **children's section** must be for readers aged 12 or younger." This rule ensures that the books are organized in a way that makes sense to library users. * **Data Integrity**: In a relational database, **user-defined integrity** allows you to define specific rules or constraints based on the needs of your application. For example, you might create a rule that a **product price** in an **Inventory** table must always be greater than zero, just like a library’s custom rule about the children's books. Miro Board: # Installing Postgresql # Installing PostgreSQL on Linux ### **Step 1: Update your Package Manager** Before installing anything, ensure your system's package manager is up-to-date. ```bash sudo apt update ``` **Explanation**: This tells the system to refresh the list of available software so you can install the latest versions. ### **Step 2: Install PostgreSQL** Use the package manager to download and install PostgreSQL. ```bash sudo apt install postgresql postgresql-contrib ``` **Explanation**: * `postgresql`: The main PostgreSQL software. * `postgresql-contrib`: Additional helpful tools and extensions. ### **Step 3: Verify Installation** Check if PostgreSQL is running by checking its status. ```bash sudo systemctl status postgresql ``` **Explanation**: This displays whether the PostgreSQL service is active. If it says **active (running)**, you're good to go! ### **Step 4: Access PostgreSQL** Switch to the default PostgreSQL user and access the database. ```bash sudo -i -u postgres psql ``` **Explanation**: * `sudo -i -u postgres`: Switches to the PostgreSQL user. * `psql`: Opens the PostgreSQL command-line interface. # Installing PostgreSQL on Windows ### Step 1: Download the Installer * Go to the [PostgreSQL website.](https://www.enterprisedb.com/download-postgresql-binaries) * Select **Windows** and download the installer. ### **Step 2: Run the Installer** 1. Open the downloaded file and follow the prompts. 2. During installation: * Choose a directory for PostgreSQL (default is fine). * Set a password for the default PostgreSQL user (`postgres`). **Remember this password!** * Select the components you need. By default, everything essential is selected. * Choose a port (default is 5432). Keep this unless you have a specific reason to change it. #### **Step 3: Verify Installation** 1. Open **pgAdmin** (a graphical tool included in the installation) and connect using the password you set. 2. Alternatively, open the command prompt and type ```bash psql -U postgres ``` **Explanation**: `psql` is the command-line tool for PostgreSQL. It connects to the database using the username `postgres`. # Installing PostgreSQL on Mac ### **Step 1: Use Homebrew** Homebrew is a package manager for macOS. If you don’t have Homebrew installed, install it first by running ```bash /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)" ``` ### **Step 2: Install PostgreSQL** Once Homebrew is installed, run ```bash brew install postgresql ``` **Explanation**: This command downloads and installs PostgreSQL via Homebrew. ### **Step 3: Start PostgreSQL** Start the PostgreSQL service. ``` brew services start postgresql ``` **Explanation**: This ensures PostgreSQL runs in the background, even after restarting your Mac. ### **Step 4: Verify Installation** 1. Check if PostgreSQL is running ```bash brew services list ``` **Explanation**: This shows all services managed by Homebrew and their statuses. 2. Access PostgreSQL: ```bash psql postgres ``` **Explanation**: Opens the PostgreSQL interface connected to the default `postgres` database. # Postgres in Docker ### *docker-compose.yml* ```yaml version: '3.8' services: postgres: image: postgres:15 container_name: postgres_db environment: POSTGRES_USER: admin POSTGRES_PASSWORD: admin_password POSTGRES_DB: my_database ports: - "5432:5432" volumes: - postgres_data:/var/lib/postgresql/data pgadmin: image: dpage/pgadmin4 container_name: pgadmin_container environment: PGADMIN_DEFAULT_EMAIL: admin@pgadmin.com PGADMIN_DEFAULT_PASSWORD: admin_password ports: - "8080:80" depends_on: - postgres volumes: postgres_data: ``` ### Configuration Breakdown * **PostgreSQL Service**: * `image: postgres:15`: Specifies the PostgreSQL version. * `POSTGRES_USER`, `POSTGRES_PASSWORD`, `POSTGRES_DB`: Environment variables to set up the database and credentials. * `ports: "5432:5432"`: Exposes PostgreSQL on port 5432 (default port). * `volumes`: Mounts a Docker volume to persist PostgreSQL data. * **pgAdmin Service**: * `image: dpage/pgadmin4`: Pulls the latest pgAdmin image. * `PGADMIN_DEFAULT_EMAIL` and `PGADMIN_DEFAULT_PASSWORD`: Default login credentials. * `ports: "8080:80"`: Exposes pgAdmin on port 8080. * `depends_on`: Ensures PostgreSQL starts before pgAdmin. ### Start the service ```bash docker-compose up -d ``` # What happens during the initial installation ? When you install and initialize PostgreSQL, several processes occur to set up the database system for use. (This is my flow of understanding) ### 1. **Installation Phase** * **Binary Installation**: PostgreSQL binaries (e.g., `postgres`, `psql`) and utilities are installed. This is handled by package managers like `apt`, `yum`, or directly from source. * **Configuration Files**: Default configuration files like `postgresql.conf`, `pg_hba.conf`, and `pg_ident.conf` are set up. ### 2. **Initialization (`initdb`)** The `initdb` command prepares the environment for the PostgreSQL database cluster. Key processes include: #### **a. Directory Creation**: * The data directory specified (e.g., `/var/lib/postgresql/data`) is created if it doesn’t already exist. * Subdirectories like `base`, `global`, `pg_wal`, `pg_stat`, etc., are created to organize data files and system metadata. #### **b. Configuration File Generation**: * `postgresql.conf`: Main configuration file. * `pg_hba.conf`: Controls client authentication. * `pg_ident.conf`: Maps operating system usernames to database usernames. #### **c. Creation of System Catalog**: * Core system catalogs (e.g., `pg_class`, `pg_attribute`, `pg_database`) are created to manage database objects. * Essential metadata is initialized. #### **d. Default Templates Setup**: * **`template0`**: * This is a pristine template used to create other databases. * Contains only standard objects provided by PostgreSQL. * Frozen to prevent modifications. * **`template1`**: * This template is used by default for database creation. * It can be modified to include custom extensions, schemas, or objects you want in all new databases. * If `template1` is damaged, `template0` can restore it. #### **e. Default Databases**: * **`postgres`**: A default administrative database created for use by DBAs. * **`template0`** and **`template1`**: As described above. * **`postgres`** is often the initial user-created database for experimentation or administration. #### **f. Encoding and Locale**: * Specifies the default character set and locale for the cluster. * All databases inherit these settings unless overridden. #### **g. WAL Initialization**: * The Write-Ahead Logging (WAL) mechanism is set up under `pg_wal`. * Ensures data durability and crash recovery. #### **h. Superuser Role**: * The default superuser role (e.g., `postgres`) is created. It owns all the system objects. ### 3. **Role Management** * Roles are PostgreSQL’s way of handling users and groups. * During initialization: * The **`postgres` superuser role** is created. * Privileges for managing other roles and databases are assigned to this role. ### 4. **Background Processes** (When PostgreSQL Starts) Once initialized, PostgreSQL runs several background processes for database management: #### **a. `postmaster` Process**: * This is the main server process that manages all other subprocesses. #### **b. Background Worker Processes**: * **WAL Writer**: Handles WAL updates. * **Checkpoint Process**: Writes dirty pages to disk to maintain data integrity. * **Autovacuum**: Automatically manages vacuum and analyze tasks. * **Stats Collector**: Collects and updates statistics for query optimization. * **Archiver**: Archives completed WAL segments when configured. # Roles, Users & Groups ### Role As postgres documentation mentions, > A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant *membership* in a role to another role, thus allowing the member role to use privileges assigned to another role. In PostgreSQL, a **role** is a broad concept that represents an entity that can: * **Own objects** in the database (like tables, schemas, or databases). * **Have permissions** to perform operations (like SELECT, INSERT, or DELETE). * Be granted other roles or privileges Roles are of 2 types 1. Login Role 2. No-Login Role All operations that are been executed in postgres will be needing a role. Roles can be granted permissions to perform various actions. Let's create a simple role, ```sql -- Create a role for developers CREATE ROLE developers; -- Grant permissions to the role GRANT SELECT, INSERT ON employees TO developers; ``` * It creates a role named `developers` * The `developers` role cannot log in but can have permissions assigned. * Individual users (like Alice and Bob) can be added to this role to inherit its permissions. ### What is a User then ? A **user** in PostgreSQL is simply a role with the ability to log in. It represents a person or application that connects to the database. **How a User is Created:** * When you create a role and give it the `LOGIN` privilege, it becomes a user. ```sql CREATE ROLE alice WITH LOGIN PASSWORD 'password123'; GRANT developers TO alice; ``` When you create a role and give it the `LOGIN` privilege, it becomes a user. ### **Key Differences Between Roles and Users** | **Aspect** | **Role** | **User** | | -------------------- | ----------------------------------------- | ----------------------------------------------------------------- | | **Definition** | A generic entity for managing privileges. | A role with the `LOGIN` privilege. | | **Purpose** | Can be a user, group, or permission set. | Specifically for someone/something that connects to the database. | | **Login Capability** | Cannot log in by default. | Can log in to the database. | | **Example** | `CREATE ROLE manager;` | `CREATE ROLE john WITH LOGIN;` | ### **When to Use Roles vs Users** 1. **Use Roles as Groups:** * Roles are often used as **groups** to manage permissions efficiently. * For example, you can create a `developers` role and grant it access to a table ```sql CREATE ROLE developers; GRANT SELECT, INSERT ON my_table TO developers; ``` 2. **Use Users for Individuals:** * Users represent actual people or applications connecting to the database. * For example, you can create a user `alice` and assign her to the `developers` group ```sql CREATE ROLE alice WITH LOGIN PASSWORD 'password123'; GRANT developers TO alice; ``` ### **How Roles and Users Work Together** * A **user** (a role with `LOGIN`) can be assigned to one or more **group roles**. * A user inherits the permissions of the roles it belongs to. **Example:** 1. Create a **group role** for developers: ```sql CREATE ROLE developers; GRANT SELECT, INSERT ON my_table TO developers; ``` 2. Create a **user** and assign them to the group ```sql CREATE ROLE alice WITH LOGIN PASSWORD 'password123'; GRANT developers TO alice; ``` 3. When Alice logs in, she automatically gets the permissions of the `developers` role. ### **Why Does PostgreSQL Use Roles Instead of Separate Users and Groups?** PostgreSQL’s unified concept of **roles** simplifies permission management: * A role can act as a **user**, a **group**, or both. * Roles can be nested, meaning one role can inherit privileges from another. This approach avoids having separate systems for users and groups, making the database more flexible and easier to manage. ### **Key Points About Roles** * **No Login by Default**: Roles can only log in if the `LOGIN` attribute is set. * **Inheritance**: Roles can inherit permissions from other roles using `GRANT`. * **Combining Roles**: Users can belong to multiple roles, and their permissions will combine. # More examples on Roles Roles in PostgreSQL are flexible and can represent different entities: a **user**, a **group**, or a permission set. Let’s look at some examples of how to create and use roles for various scenarios. ### **1. Basic Role Without Login** A role without the ability to log in can act as a **permission set** or a **group**. **Example:** Create a role to manage permissions for application developers. ```sql -- Create a role for developers CREATE ROLE developers; -- Grant permissions to the role GRANT SELECT, INSERT ON employees TO developers; ``` * The `developers` role cannot log in but can have permissions assigned. * Individual users (like Alice and Bob) can be added to this role to inherit its permissions. ### **2. Role with Login (User Role)** A role that can log in is considered a **user**. **Example:** Create a user role for an individual database user. ```sql -- Create a role with login capability CREATE ROLE alice WITH LOGIN PASSWORD 'securepassword'; -- Grant specific permissions to the user GRANT SELECT ON employees TO alice; ``` Alice can now log in to the database and has `SELECT` access to the `employees` table. ### **3. Role as a Group** A role can act as a **group** to simplify permission management for multiple users. **Example:** 1. Create a group role for a team of analysts ```sql CREATE ROLE analysts; ``` 2. Grant read-only access to a table ```sql GRANT SELECT ON sales_data TO analysts; ``` 3. Add individual users to the group ```sql CREATE ROLE bob WITH LOGIN PASSWORD 'password123'; CREATE ROLE carol WITH LOGIN PASSWORD 'password123'; -- Add users to the group GRANT analysts TO bob, carol; ``` Now both Bob and Carol inherit `SELECT` privileges on the `sales_data` table from the `analysts` role. ### **4. Role with Inheritance** Roles can be nested, meaning one role can inherit permissions from another. **Example:** 1. Create a base role for read-only access ```sql CREATE ROLE read_access; GRANT SELECT ON employees TO read_access; ``` 2. Create a manager role that inherits from `read_access` and has additional permissions ```sql CREATE ROLE manager; GRANT INSERT, UPDATE ON employees TO manager; GRANT read_access TO manager; ``` 3. Assign a user to the `manager` role ```sql CREATE ROLE dave WITH LOGIN PASSWORD 'manager123'; GRANT manager TO dave; ``` Dave can now perform `SELECT`, `INSERT`, and `UPDATE` operations on the `employees` table because he inherits `read_access` through the `manager` role. ### 5. Temporary Role for a Consultant You might need a temporary role for someone who will have limited access for a short time. **Example:** 1. Create a role for a consultant ```sql CREATE ROLE consultant WITH LOGIN PASSWORD 'consult123' VALID UNTIL '2024-12-31'; ``` 2. Assign limited permissions ```sql GRANT SELECT ON employees TO consultant; ``` The `consultant` role will automatically lose login privileges after December 31, 2024. # Sample Databases # DVD Rental Database ### Source ### Steps to import the database 1. Download the tar file from the given link. 2. Create the dvdrental database. Login to the postgres db, with the user. (here for example we are using the postgres superuser.) ```bash psql -U postgres ``` After entering the password correctly, you will be connected to the PostgreSQL server. ``` postgres=# ```
Second, create a new database called `dvdrental` using `CREATE DABASE` statement: ```sql CREATE DATABASE dvdrental; ```
Output: ``` CREATE DATABASE ``` PostgreSQL will create a new database called `dvdrental`. Third, verify the database creation using the `\l` command. The `\l` command will show all databases in the PostgreSQL server. The output shows that `dvdrental` on the list, meaning that you have created the `dvdrental` database successfully. Now disconnect from the PostgreSQL server and exit the `psql` using the `exit` command 3. Load the dataset into the db using the `pg_restore` ```bash pg_restore -U postgres -d dvdrental dvdrental.tar ``` n this command: * The `-U postgres` instructs `pg_restore` to connect the PostgreSQL server using the `postgres` user. * The `-d dvdrental` specifies the target database to load. It might prompt you to enter the password for the `postgres` user. Enter the password for the `postgres` user and press the Enter (or Return key) It’ll take about seconds to load data stored in the `dvdrental.tar` file into the `dvdrental` database. 4. Verify the database is loaded. Login to the postgres with database as `dvdrental`, ```bash psql -U postgres -d dvdrental; ```
display all tables in the `dvdrental` database ``` \dt ```
Now you have the practice DB. Happy Learning ! # SELECT Query > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this The `SELECT` statement is one of the most fundamental SQL commands and is used to retrieve data from a PostgreSQL database. Let’s break it down with relatable examples to understand how it works. ### **1. Basic SELECT Statement** To fetch data from a table, you use ```sql SELECT column_name FROM table_name; ``` Example: Find the first names of all customers from the `customer` table: ```sql SELECT first_name FROM customer; ```
Notice that we added a semicolon (`;`) at the end of the `SELECT` statement. The semicolon is not a part of the SQL statement; rather, it serves as a signal of PostgreSQL indicating the conclusion of an SQL statement. Additionally, semicolons are used to separate two or more SQL statements. ### 2. Using PostgreSQL SELECT statement to query data from multiple columns example The following query uses the `SELECT` statement to retrieve first name, last name, and email of customers from the `customer` table. ```sql SELECT first_name, last_name, email FROM customer; ```
### 3. Using PostgreSQL SELECT statement to query data from all columns of a table example The following query uses the `SELECT *` statement to retrieve data from all columns of the `customer` table ```sql SELECT * FROM customer; ```
### 4. Why we should not use SELECT \* in a query ? When writing SQL queries to fetch data, you can use an asterisk (`*`) in the `SELECT` statement to quickly grab all the columns from a table. This is convenient, but it’s not the best approach when building applications like in Python, Java, or PHP. Here’s why, **1. Performance Issues** * **Database Load**: If the table has many columns, `SELECT *` pulls everything—even data your application doesn’t need. This can slow down database operations. * **Application Overhead**: Fetching unnecessary data increases the traffic between your database and app, leading to slower load times, especially for large datasets. **2. Clarity and Maintainability** Using `SELECT *` makes the query ambiguous. For example: * If the database structure changes (e.g., columns are added), the app may break because it wasn’t expecting those extra columns. * Specifying columns explicitly helps you and your teammates understand exactly what data is being fetched. Instead of this ```sql SELECT * FROM employees; ``` Do this, ```sql SELECT id, name, department FROM employees; ``` **3. When to Use `*`** * **Ad-hoc Queries**: Perfect for quick checks or one-off investigations directly on the database. * **Learning**: It’s great for exploring tables when you're not yet sure what data is stored. Example: If you're troubleshooting, this is fine ```sql SELECT * FROM customers WHERE city = 'New York'; ``` But in your application code, it's better to fetch only what's required ```sql SELECT customer_id, name, email FROM customers WHERE city = 'New York'; ``` **Real-World Example** Imagine an online store with a `products` table: * Columns: `id`, `name`, `price`, `description`, `inventory_count`. For showing a product list on the homepage, you only need `name` and `price`. Using `SELECT *` fetches unnecessary data like the product description, wasting resources. Efficient Query, ```sql SELECT name, price FROM products; ``` #### **Key Takeaway** Use `SELECT *` sparingly and only for quick tasks. For application development, **always specify the columns you need** to keep your code efficient, clear, and scalable. # Column Aliases > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this ### What are Column Aliases ? A column alias allows you to assign a column or an expression in the select list of a `SELECT` statement a temporary name. The column alias exists temporarily during the execution of the query. The following illustrates the syntax of using a column alias ```sql SELECT column_name AS alias_name FROM table_name; ``` In this syntax, the `column_name` is assigned an alias `alias_name`. The `AS` keyword is optional so you can omit it like this, ```sql SELECT column_name alias_name FROM table_name; ``` The main purpose of column aliases is to make the headings of the output of a query more meaningful. ### 1. Assigning a column alias to a column example The following query returns the first names and last names of first 5 customers from the `customer` table ```sql SELECT first_name, last_name FROM customer LIMIT 5; ```
If you want to rename the `last_name` heading, you can assign it a new name using a column alias like this ```sql SELECT first_name, last_name AS surname FROM customer LIMIT 5; ```
This query assigned the `surname` as the alias of the `last_name` column Or you can make it shorter by removing the `AS` keyword as follows, ```sql SELECT first_name, last_name surname FROM customer LIMIT 5; ```
### 2. Assigning a column alias to an expression example The following query returns the full names of first 5 customers. It constructs the full name by concatenating the first name, space, and the last name ```sql SELECT first_name || ' ' || last_name FROM customer LIMIT 5; ``` Note that in PostgreSQL, you use the `||` as the concatenating operator that concatenates one or more strings into a single string.
As you can see clearly from the output, the heading of the column is not meaningful `?column?` . To fix this, you can assign the expression `first_name || ' ' || last_name` a column alias e.g., `full_name` ```sql SELECT first_name || ' ' || last_name AS full_name FROM customer LIMIT 5; ```
### 3. Column aliases that contain spaces If a column alias contains one or more spaces, you need to surround it with double quotes like this ```sql column_name AS "column alias" ``` For example, ```sql SELECT first_name || ' ' || last_name "full name" FROM customer LIMIT 5; ```
# Order By > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this When you query data from a table, the [`SELECT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-select) statement returns rows in an unspecified order. To sort the rows of the result set, you use the `ORDER BY` clause in the [`SELECT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-select) statement. The `ORDER BY` clause allows you to sort rows returned by a `SELECT` clause in ascending or descending order based on a sort expression. The following illustrates the syntax of the `ORDER BY` clause ```sql SELECT select_list FROM table_name ORDER BY sort_expression1 [ASC | DESC], sort_expression2 [ASC | DESC], ...; ``` In this syntax: * First, specify a sort expression, which can be a column or an expression, that you want to sort after the `ORDER BY` keywords. If you want to sort the result set based on multiple columns or expressions, you need to place a comma (`,`) between two columns or expressions to separate them. * Second, you use the `ASC` option to sort rows in ascending order and the `DESC` option to sort rows in descending order. If you omit the `ASC` or `DESC` option, the `ORDER BY` uses `ASC` by default. PostgreSQL evaluates the clauses in the `SELECT` statement in the following order: `FROM`, `SELECT`, and `ORDER BY.` ### 1. Using PostgreSQL ORDER BY clause to sort rows by one column The following query uses the `ORDER BY` clause to sort customers by their first names in ascending order ```sql SELECT first_name, last_name FROM customer ORDER BY first_name ASC LIMIT 5; ```
Since the `ASC` option is the default, you can omit it in the `ORDER BY` clause like this ```sql SELECT first_name, last_name FROM customer ORDER BY first_name LIMIT 5; ``` ### 2. Using PostgreSQL ORDER BY clause to sort rows by one column in descending order The following statement selects the first name and last name from the `customer` table and sorts the rows by values in the last name column in descending order. ```sql SELECT first_name, last_name FROM customer ORDER BY last_name DESC LIMIT 5; ```
### 3. Using PostgreSQL ORDER BY clause to sort rows by multiple columns The following statement selects the first name and last name from the customer table and sorts the rows by the first name in ascending order and last name in descending order. ``` SELECT first_name, last_name FROM customer ORDER BY first_name ASC, last_name DESC; ``` In this example, the ORDER BY clause sorts rows by values in the first name column first. Then it sorts the sorted rows by values in the last name column. ### 4. Using PostgreSQL ORDER BY clause to sort rows by expressions The [`LENGTH()`](https://neon.tech/postgresql/postgresql-string-functions/postgresql-length-function) function accepts a string and returns the length of that string. The following statement selects the first names and their lengths. It sorts the rows by the lengths of the first names. ```sql SELECT first_name, LENGTH(first_name) len FROM customer ORDER BY len DESC LIMIT 5; ```
Because the `ORDER BY` clause is evaluated after the `SELECT` clause, the column alias `len` is available and can be used in the `ORDER BY` clause. # SELECT DISTINCT > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this The `SELECT DISTINCT` removes duplicate rows from a result set. The `SELECT DISTINCT` clause retains one row for each group of duplicates. The `SELECT DISTINCT` clause can be applied to one or more columns in the select list of the [`SELECT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-select) statement. The following illustrates the syntax of the `DISTINCT` clause. ```sql SELECT DISTINCT column1 FROM table_name; ``` In this syntax, the `SELECT DISTINCT` uses the values in the `column1` column to evaluate the duplicate. If you specify multiple columns, the `SELECT DISTINCT` clause will evaluate the duplicate based on the combination of values in these columns. For example, ```sql SELECT DISTINCT column1, column2 FROM table_name; ``` In this syntax, the `SELECT DISTINCT` uses the combination of values in both `column1` and `column2` columns for evaluating the duplicate. ### PostgreSQL SELECT DISTINCT examples Let's create a new table for practice the `SELECT DISTINCT` clause. First, create the `colors` table that has three columns: `id`, `bcolor` and `fcolor` using the following `CREATE TABLE` statement, ```sql CREATE TABLE colors( id SERIAL PRIMARY KEY, bcolor VARCHAR, fcolor VARCHAR ); ``` Let's insert some data into it, ```sql INSERT INTO colors (bcolor, fcolor) VALUES ('red', 'red'), ('red', 'red'), ('red', NULL), (NULL, 'red'), (NULL, NULL), ('green', 'green'), ('blue', 'blue'), ('blue', 'blue'); ``` Third, retrieve the data from the `colors` table using the SELECT statement. ```sql SELECT id, bcolor, fcolor FROM colors; ``` ``` id | bcolor | fcolor ----+--------+-------- 1 | red | red 2 | red | red 3 | red | null 4 | null | red 5 | null | null 6 | green | green 7 | blue | blue 8 | blue | blue (8 rows) ``` ### 1. PostgreSQL SELECT DISTINCT one column example The following statement selects unique values from the `bcolor` column of the `t1` table and sorts the result set in alphabetical order by using the ORDER BY clause. ```sql SELECT DISTINCT bcolor FROM colors ORDER BY bcolor; ``` Output: ``` bcolor -------- blue green red null (4 rows) ``` The `bcolor` column has 3 red values, two NULL, 1 green value, and two blue values. The `DISTINCT` removes two red values, 1 NULL, and one blue. Note that PostgreSQL treats `NULL`s as duplicates so that it keeps one `NULL` for all `NULL`s when you apply the `SELECT DISTINCT` clause. ### 2. SELECT DISTINCT on multiple columns The following statement applies the `SELECT DISTINCT` clause to both `bcolor` and `fcolor` columns ```sql SELECT DISTINCT bcolor, fcolor FROM colors ORDER BY bcolor, fcolor; ``` Output, ``` bcolor | fcolor --------+-------- blue | blue green | green red | red red | null null | red null | null (6 rows) ``` In this example, the query uses the values from both `bcolor` and `fcolor` columns to evaluate the uniqueness of rows. ### 3. Using the SELECT DISTINCT clause in practice In practice, you often use the `SELECT DISTINCT` clause to analyze the uniqueness of values in a column. For example, you may want to know how many rental rates for films from the `film` table, To achieve this, you can specify the `rental_rate` column in the `SELECT DISTINCT` clause as follows, 
SELECT DISTINCT
  rental_rate
FROM
  film
ORDER BY
  rental_rate;
The output indicates that there are only three distinct rental rates 0.99, 2.99, and 4.99. # DB-TASK-001 > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this 1. Retrieve film titles and their rental rates. Use column aliases to rename `title` as "Movie Title" and `rental_rate` as "Rate". 2. List customer names and their email addresses. Alias `first_name` and `last_name` as "First Name" and "Last Name". 3. Get a list of films sorted by rental rate in descending order. If two films have the same rental rate, sort them alphabetically by title. 4. Retrieve actor names sorted by last name, then first name. 5. List all unique replacement costs from the `film` table. 6. List all films' `title` and `length` in minutes. Alias `length` as "Duration (min)". 7. Retrieve customer first and last names along with their `active` status. Alias `active` as "Is Active". 8. Retrieve the list of film categories sorted alphabetically. 9. List films by `length`, sorted in descending order. Include only the title and length. 10. Retrieve all actor names, sorted by their first name in descending order. 11. List all unique `ratings` available in the `film` table. 12. Find all unique rental durations from the `film` table. 13. Retrieve the first unique customer ID based on active status. Include the customer\_id and active columns, and order by customer\_id. 14. List the earliest rental date for each customer. Include customer\_id and rental\_date, and order by customer\_id. 15. List the 10 shortest films by length. Include the title and length. 16. Get the top 5 customers with the highest customer\_id. Include the first and last name. 17. Retrieve all unique values of store\_id from the inventory table. 18. Find all unique replacement\_cost values in the film table. Sort the results in ascending order. 19. List the first rental date for each store. Include store\_id and rental\_date, and sort by store\_id. 20. Retrieve a list of film ratings sorted alphabetically and include only unique values. 21. List films by `rating` in ascending order and `length` in descending order. 22. Retrieve actor names sorted by last\_name in ascending order and first\_name in descending order. 23. List films ordered by `replacement_cost` in ascending order and `rental_rate` in descending order. 24. Retrieve customer names sorted by last\_name ascending and first\_name descending. 25. List all rentals sorted by customer\_id ascending and rental\_date descending. 26. Retrieve a list of films ordered by rental\_duration ascending and title descending. # WHERE ? > 📌 Note: This tutorial uses the dvdrental database. If you dont have the dvdrental, please go through this ### Intro The [`SELECT`](/database-engineering/querying-data/select-query) statement returns all rows from one or more columns in a table. To retrieve rows that satisfy a specified condition, you use a `WHERE` clause. The syntax of the PostgreSQL `WHERE` clause is as follows ```sql SELECT select_list FROM table_name WHERE condition; ``` In this syntax, you place the `WHERE` clause right after the `FROM` clause of the `SELECT` statement. The `WHERE` clause uses the `condition` to filter the rows returned from the `SELECT` clause. The `condition` is a boolean expression that evaluates to true, false, or unknown. The query returns only rows that satisfy the `condition` in the `WHERE` clause. In other words, the query will include only rows that cause the `condition` evaluates to true in the result set. > ⚠️ Warning: If you are using the column aliases , you can't use them in the Query. To form the condition in the `WHERE` clause, you use comparison and logical operators | Operator | Description | | -------- | --------------------------------------------------- | | = | Equal | | > | Greater than | | < | Less than | | >= | Greater than or equal | | <= | Less than or equal | | <> or != | Not equal | | AND | Logical operator AND | | OR | Logical operator OR | | IN | Return true if a value matches any value in a list | | BETWEEN | Return true if a value is between a range of values | | LIKE | Return true if a value matches a pattern | | IS NULL | Return true if a value is NULL | | NOT | Negate the result of other operators | ### Using WHERE clause with the equal (=) operator example The following statement uses the `WHERE` clause to find customers with the first name is `Jamie,` ```sql SELECT first_name, last_name FROM customer WHERE first_name = 'Jamie'; ```
### Using the WHERE clause with the AND operator example The following example uses a `WHERE` clause with the `AND` logical operator to find customers whose first name and last names are `Linda` and `Williams` ```sql SELECT first_name, last_name FROM customer WHERE first_name = 'Linda' AND last_name = 'Williams'; ```
### Using the WHERE clause with the OR operator example The following example uses a WHERE clause with an OR operator to find the customers whose last name is `Rice` or first name is Linda. ```sql SELECT first_name, last_name FROM customer WHERE last_name = 'Rice' OR first_name = 'Linda'; ```
### Using the WHERE clause with the IN operator example If you want to find a value in a list of values, you can use the `IN` operator. The following example uses the WHERE clause with the IN operator to find the customers with first names in the list Ann, Anne, and Annie ```sql SELECT first_name, last_name FROM customer WHERE first_name IN ('Ann', 'Anne', 'Annie'); ```
### Using the WHERE clause with the LIKE operator example To find a string that matches a specified pattern, you use the `LIKE` operator. The following example uses the LIKE operator in the WHERE clause to find customers whose first names start with the word Ann. ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Ann%'; ```
The `%` is called a wildcard that matches any string. The `'Ann%'` pattern matches any strings that start with `'Ann'`. ### Using the WHERE clause with the BETWEEN operator example The following example finds customers whose first names start with the letter `A` and contains 3 to 5 characters by using the `BETWEEN` operator. The `BETWEEN` operator returns true if a value is in a range of values. ```sql SELECT first_name, LENGTH(first_name) name_length FROM customer WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5 ORDER BY name_length LIMIT 5; ```
### Using the WHERE clause with the not equal operator (<>) example This example finds customers whose first names start with `Linda` and last names are not `Motley` ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Linda%' AND last_name <> 'Motley'; ```
Note that you can use the `!=` operator and `<>` operator interchangeably because they are equivalent. # AND Operator In PostgreSQL, a boolean value can have one of three values: `true`, `false`, and null. | | Result | | ------- | ------ | | true | true | | t | true | | 'true' | true | | 'y' | true | | 'yes' | true | | '1' | true | | false | false | | f | false | | 'false' | false | | 'n' | false | | 'no' | false | | '0' | false | A boolean expression is an expression that evaluates to a boolean value. For example, the expression `1=1` is a boolean expression that evaluates to `true` ```sql SELECT 1 = 1 AS result; ``` ``` result -------- t (1 row) ``` The letter `t` in the output indicates the value of `true`. The `AND` operator is a logical operator that combines two boolean expressions. ### Basic PostgreSQL AND operator examples 1. The following example uses the `AND` operator to combine two true values, which returns true ```sql SELECT true AND true AS result; ``` ``` result -------- t (1 row) ``` 2. The following statement uses the `AND` operator to combine true with false, which returns false ```sql SELECT true AND false AS result; ``` ``` result -------- f (1 row) ``` 3. The following example uses the `AND` operator to combine true with null, which returns null ```sql SELECT true AND null AS result; ``` ``` result -------- null (1 row) ``` 4. The following example uses the `AND` operator to combine false with false, which returns false ```sql SELECT false AND false AS result; ``` ``` result -------- f (1 row) ``` 5. The following example uses the `AND` operator to combine false with null, which returns false ```sql SELECT false AND null AS result; ``` ``` result -------- f (1 row) ``` ### Using the AND operator in the WHERE clause The following example uses the `AND` operator in the `WHERE` clause to find the films that have a length greater than 180 and a rental rate less than 1. ```sql SELECT title, length, rental_rate FROM film WHERE length > 180 AND rental_rate < 1; ``` ``` title | length | rental_rate --------------------+--------+------------- Catch Amistad | 183 | 0.99 Haunting Pianist | 181 | 0.99 Intrigue Worst | 181 | 0.99 Love Suicides | 181 | 0.99 Runaway Tenenbaums | 181 | 0.99 Smoochy Control | 184 | 0.99 Sorority Queen | 184 | 0.99 Theory Mermaid | 184 | 0.99 Wild Apollo | 181 | 0.99 Young Language | 183 | 0.99 (10 rows) ``` # OR Operator In PostgreSQL, a boolean value can have one of three values: `true`, `false`, and null. | | Result | | ------- | ------ | | true | true | | t | true | | 'true' | true | | 'y' | true | | 'yes' | true | | '1' | true | | false | false | | f | false | | 'false' | false | | 'n' | false | | 'no' | false | | '0' | false | ### Postgres OR Operator Query Results
SELECT true OR true AS result;true
SELECT true OR false AS result;t
SELECT true OR null AS result;t
SELECT false OR false AS result;f
SELECT false OR null AS result;null
SELECT false OR false AS result;f
SELECT null OR null AS result;null
### Using the OR operator in the WHERE clause The following example uses the `OR` operator in the `WHERE` clause to find the films that have a rental rate is `0.99` or `2.99` ```sql SELECT title, rental_rate FROM film WHERE rental_rate = 0.99 OR rental_rate = 2.99; ``` Output: ``` title | rental_rate -----------------------------+------------- Academy Dinosaur | 0.99 Adaptation Holes | 2.99 Affair Prejudice | 2.99 African Egg | 2.99 ... ``` # LIMIT PostgreSQL `LIMIT` is an optional clause of the [`SELECT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-select) statement that constrains the number of rows returned by the query. Here’s the basic syntax of the `LIMIT` clause ```sql SELECT select_list FROM table_name ORDER BY sort_expression LIMIT row_count; ``` The statement returns `row_count` rows generated by the query. If the `row_count` is zero, the query returns an empty set. If the `row_count` is `NULL`, the query returns the same result set as it does not have the `LIMIT` clause. If you want to skip a number of rows before returning the `row_count` rows, you can use `OFFSET` clause placed after the `LIMIT` clause. ```sql SELECT select_list FROM table_name ORDER BY sort_expression LIMIT row_count OFFSET row_to_skip; ``` 1. **Skipping Rows (OFFSET):** The `OFFSET` part of a query tells PostgreSQL to *skip* a certain number of rows at the start of the results. For example, if you skip 5 rows, the query will start showing results from the 6th row. * If you don’t want to skip any rows, just set `OFFSET` to 0, and it’s like there’s no skipping at all. 2. **Limiting Rows (LIMIT):** The `LIMIT` part tells PostgreSQL to only give you a specific number of rows, no more. For example, if you say `LIMIT 10`, you’ll get 10 rows in the result. 3. **Order of Operations:** PostgreSQL skips rows (using `OFFSET`) *before* it applies the `LIMIT`. So, it decides which rows to ignore before it picks the rows to show. 4. **Order Matters (ORDER BY):** PostgreSQL doesn’t automatically store rows in any particular order. That’s why, when you use `LIMIT`, you should always use `ORDER BY` to control which rows are picked. If you don’t, PostgreSQL will return rows in a random or unpredictable order. **Example for clarity:** * Query: `SELECT * FROM students ORDER BY grade DESC OFFSET 5 LIMIT 3;` * **Step 1:** Sort students by grades from highest to lowest. * **Step 2:** Skip the first 5 students. * **Step 3:** Show the next 3 students. Without the `ORDER BY`, the rows shown could be in any order, which might not be what you want. ### Using PostgreSQL LIMIT to constrain the number of returned rows The following statement uses the `LIMIT` clause to get the first five films sorted by `film_id` ```sql SELECT film_id, title, release_year FROM film ORDER BY film_id LIMIT 5; ```
How it works. * First, sort films by the `film_id` ascending order using the `ORDER BY film_id` clause. * Second, take 5 films from the top using the `LIMIT 5` clause. ### Using the LIMIT clause with the OFFSET clause example To retrieve 4 films starting from the fourth one ordered by `film_id`, you can use both `LIMIT` and `OFFSET` clauses as follows. ```sql SELECT film_id, title, release_year FROM film ORDER BY film_id LIMIT 4 OFFSET 3; ```
How it works. * First, sort films by film id in ascending order. * Second, skip the first three rows using the `OFFSET 3` clause. * Second, take the next four rows using the `LIMIT 4` clause. ### Using LIMIT OFFSET to get top/bottom N rows Typically, you often use the `LIMIT` clause to select rows with the highest or lowest values from a table. The following example uses the `LIMIT` clause to retrieve the top 10 most expensive films by rental rate. ```sql SELECT film_id, title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10; ```
How it works. * First, sort all the films by rental rates from high to low using the `ORDER BY rental_rate` clause. * Second, take only 10 rows from the top using the `LIMIT 10` clause. # FETCH ### Introduction to PostgreSQL FETCH clause To skip a certain number of rows and retrieve a specific number of rows, you often use the [`LIMIT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-limit) clause in the `SELECT` statement. The `LIMIT` clause is widely used by many Relational Database Management Systems such as MySQL, H2, and HSQLDB. However, the `LIMIT` clause is not a SQL standard. To conform with the SQL standard, PostgreSQL supports the `FETCH` clause to skip a certain number of rows and then fetch a specific number of rows. > Note that the `FETCH` clause was introduced as a part of the SQL standard in SQL:2008. The following illustrates the syntax of the PostgreSQL `FETCH` clause ```sql OFFSET row_to_skip { ROW | ROWS } FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY ``` 1. **Skipping Rows (OFFSET):** * The `OFFSET` keyword tells the database how many rows to skip before it starts showing results. * The number you provide (`row_to_skip`) must be a whole number (0 or more). * If you don’t provide a number, it will default to 0, meaning no rows will be skipped. * **Example:** If there are 10 rows and you skip 15, you’ll see no rows because you’ve skipped past all the data. 2. **Getting Rows (FETCH):** * The `FETCH` keyword tells the database how many rows to return after skipping. * The number (`row_count`) must be 1 or more, and it defaults to 1 if you don’t provide it. * You can use `FIRST` or `NEXT` with `ROW` or `ROWS` (e.g., `FETCH FIRST 3 ROWS` is the same as `FETCH NEXT 3 ROWS`). 3. **Ordering Rows (ORDER BY):** * Tables don’t store rows in a specific order, so results without an `ORDER BY` clause can appear in a random order. * Always use `ORDER BY` with `FETCH` to control which rows you get. 4. **Order of Clauses:** * In SQL standards (like SQL:2008), `OFFSET` comes before `FETCH`. * PostgreSQL is flexible and allows you to write them in any order. #### Example ```sql SELECT * FROM employees ORDER BY salary DESC OFFSET 5 FETCH NEXT 3 ROWS; ``` * **Step 1:** Sort employees by highest to lowest salary. * **Step 2:** Skip the first 5 employees. * **Step 3:** Show the next 3 employees. If you skip `ORDER BY`, you might get a random set of rows, which could be confusing. # IN ### Introduction to PostgreSQL IN operator The `IN` operator allows you to check whether a value matches any value in a list of values. Here’s the basic syntax of the `IN` operator ```sql value IN (value1,value2,...) ``` The `IN` operator returns true if the `value` is equal to any value in the list such as `value1` and `value2`. The list of values can be a list of literal values including numbers and strings. ### Using the PostgreSQL IN operator with a list of numbers The following example uses the `IN` operator to retrieve information about the film with id 1, 2, and 3 ```sql SELECT film_id, title FROM film WHERE film_id in (1, 2, 3); ```
The following statement uses the equal (`=`) and `OR` operators instead of the `IN` operator, which is equivalent to the query above. ```sql SELECT film_id, title FROM film WHERE film_id = 1 OR film_id = 2 OR film_id = 3; ``` The query that uses the `IN` operator is shorter and more readable than the query that uses equal (`=`) and `OR` operators. Additionally, PostgreSQL executes the query with the `IN` operator much faster than the same query that uses a list of `OR` operators. ### Using the PostgreSQL IN operator with a list of strings The following example uses the `IN` operator to find the actors who have the last name in the list `'Allen'`, `'Chase'`, and `'Davis'` ```sql SELECT first_name, last_name FROM actor WHERE last_name IN ('Allen', 'Chase', 'Davis') ORDER BY last_name; ```
### Using the PostgreSQL IN operator with a list of dates The following statement uses the IN operator to find payments whose payment dates are in a list of dates: `2007-02-15` and `2007-02-16` 
SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date::date IN ('2007-02-15', '2007-02-16');
### PostgreSQL NOT IN operator To negate the `IN` operator, you use the `NOT IN` operator. Here’s the basic syntax of the `NOT IN` operator ```sql value NOT IN (value1, value2, ...) ``` The `NOT IN` operator returns `true` if the `value` is not equal to any value in the list such as `value1` and `value2`; otherwise, the `NOT IN` operator returns `false`. The `NOT IN` operator is equivalent to a combination of multiple boolean expressions with the [AND operators](/database-engineering/filtering-data/and-operator) ```sql value <> value1 AND value <> value2 AND ... ``` The following example uses the `NOT IN` operator to retrieve films whose id is not 1, 2, or 3, ```sql SELECT film_id, title FROM film WHERE film_id NOT IN (1, 2, 3) ORDER BY film_id; ``` Output: ``` film_id | title ---------+----------------------------- 4 | Affair Prejudice 5 | African Egg 6 | Agent Truman 7 | Airplane Sierra 8 | Airport Pollock ... ``` The following query retrieves the same set of data but uses the not-equal (`<>`) and `AND` operators ```sql SELECT film_id, title FROM film WHERE film_id <> 1 AND film_id <> 2 AND film_id <> 3 ORDER BY film_id; ``` # BETWEEN ### Introduction to the PostgreSQL BETWEEN operator The `BETWEEN` operator allows you to check if a value falls within a range of values. The basic syntax of the `BETWEEN` operator is as follows ```sql value BETWEEN low AND high; ``` If the `value` is greater than or equal to the `low` value and less than or equal to the `high` value, the `BETWEEN` operator returns `true`; otherwise, it returns `false`. You can rewrite the `BETWEEN` operator by using the greater than or equal ( `>=`) and less than or equal to ( `<=`) operators and the logical AND operator. ```sql value >= low AND value <= high ``` If you want to check if a value is outside a specific range, you can use the `NOT BETWEEN` operator as follows, ```sql value NOT BETWEEN low AND high ``` The following expression is equivalent to the expression that uses the `NOT BETWEEN` operators ```sql value < low OR value > high ``` ## PostgreSQL BETWEEN operator examples Let’s take a look at the `payment`table in the [dvdrental](/database-engineering/sample-databases/dvd-rental-database) ### 1) Using the PostgreSQL BETWEEN operator with numbers The following query uses the `BETWEEN` operator to retrieve payments with `payment_id` is between `17503` and `17505` ```sql SELECT payment_id, amount FROM payment WHERE payment_id BETWEEN 17503 AND 17505 ORDER BY payment_id; ``` Output: ``` payment_id | amount ------------+-------- 17503 | 7.99 17504 | 1.99 17505 | 7.99 (3 rows) ``` ### 2) Using the PostgreSQL NOT BETWEEN example The following example uses the `NOT BETWEEN` operator to find payments with the `payment_id` not between `17503` and `17505` ```sql SELECT payment_id, amount FROM payment WHERE payment_id NOT BETWEEN 17503 AND 17505 ORDER BY payment_id; ``` Output: ``` payment_id | amount ------------+-------- 17506 | 2.99 17507 | 7.99 17508 | 5.99 17509 | 5.99 17510 | 5.99 ... ``` ### 3) Using the PostgreSQL BETWEEN with a date range If you want to check a value against a date range, you use the literal date in ISO 8601 format, which is `YYYY-MM-DD`. The following example uses the `BETWEEN` operator to find payments whose payment dates are between `2007-02-15` and `2007-02-20` and amount more than 10. ```sql SELECT payment_id, amount, payment_date FROM payment WHERE payment_date BETWEEN '2007-02-15' AND '2007-02-20' AND amount > 10 ORDER BY payment_date LIMIT 5; ```
# LIKE ### Introduction to PostgreSQL LIKE operator Suppose that you want to find customers, but you don’t remember their names exactly. However, you can recall that their names begin with something like `Jen`. How do you locate the exact customers from the database? You can identify customers in the `customer` table by examining the first name column to see if any values begin with `Jen`. However, this process can be time-consuming, especially when the `customer` table has a large number of rows. Fortunately, you can use the PostgreSQL `LIKE` operator to match the first names of customers with a string using the following query. ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Jen%'; ``` Output: ``` first_name | last_name ------------+----------- Jennifer | Davis Jennie | Terry Jenny | Castro (3 rows) ``` The `WHERE` clause in the query contains an expression, ```sql first_name LIKE 'Jen%' ``` The expression consists of the `first_name`, the `LIKE` operator and a literal string that contains a percent sign `(%`). The string `'Jen%'` is called a pattern. The query returns rows whose values in the `first_name` column begin with `Jen` and are followed by any sequence of characters. This technique is called pattern matching. You construct a pattern by combining literal values with wildcard characters and using the `LIKE` or `NOT LIKE` operator to find the matches. PostgreSQL offers two wildcards: * Percent sign ( `%`) matches any sequence of zero or more characters. * Underscore sign (`_`) matches any single character. Here’s the basic syntax of the `LIKE` operator, ```sql value LIKE pattern ``` The `LIKE` operator returns `true` if the `value` matches the `pattern`. To negate the `LIKE` operator, you use the `NOT` operator as follows ```sql value NOT LIKE pattern ``` The `NOT LIKE` operator returns `true` when the `value` does not match the `pattern`. If the pattern does not contain any wildcard character, the `LIKE` operator behaves like the equal (`=`) operator. ### PostgreSQL LIKE operator examples Let’s take some examples of using the `LIKE` operator ### 1) Basic LIKE operator examples The following statement uses the `LIKE` operator with a pattern that doesn’t have any wildcard characters ```sql SELECT 'Apple' LIKE 'Apple' AS result; ``` Output: ``` result -------- t (1 row) ``` In this example, the `LIKE` operator behaves like the equal to (`=`) operator. The query returns `true` because ‘`Apple' = 'Apple'` is `true`. The following example uses the `LIKE` operator to match any string that starts with the letter `A` ```sql SELECT 'Apple' LIKE 'A%' AS result; ``` Output: ``` result -------- t (1 row) ``` The query returns true because the string `'Apple'` starts with the letter `'A'`. ### 2) Using the LIKE operator with table data The following example uses the `LIKE` operator to find customers whose first names contain the string `er` ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE '%er%' ORDER BY first_name; ```
### 3) Using the LIKE operator a pattern that contains both wildcards The following example uses the `LIKE` operator with a pattern that contains both the percent ( `%`) and underscore (`_`) wildcards ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE '_her%' ORDER BY first_name; ```
The pattern `_her%` matches any strings that satisfy the following conditions: * The first character can be anything. * The following characters must be `'her'`. * There can be any number (including zero) of characters after `'her'`. ### 4) PostgreSQL NOT LIKE examples The following query uses the `NOT LIKE` operator to find customers whose first names do not begin with `Jen` ```sql SELECT first_name, last_name FROM customer WHERE first_name NOT LIKE 'Jen%' ORDER BY first_name; ```
### PostgreSQL extensions of the LIKE operator PostgreSQL `ILIKE` operator, which is similar to the `LIKE` operator, but allows for **case-insensitive matching**. For example ```sql SELECT first_name, last_name FROM customer WHERE first_name ILIKE 'BAR%'; ```
In this example, the `BAR%` pattern matches any string that begins with `BAR`, `Bar`, `BaR`, and so on. If you use the `LIKE` operator instead, the query will return no row. ```sql SELECT first_name, last_name FROM customer WHERE first_name LIKE 'BAR%'; ``` Output: ``` first_name | last_name ------------+----------- (0 rows) ``` PostgreSQL also provides some operators that mirror the functionality of `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE`, as shown in the following table. | Operator | Equivalent | | -------- | ---------- | | \~\~ | LIKE | | \~\~\* | ILIKE | | !\~\~ | NOT LIKE | | !\~\~\* | NOT ILIKE | For example, the following statement uses the `~~` operator to find a customer whose first names start with the string `Dar` ```sql SELECT first_name, last_name FROM customer WHERE first_name ~~ 'Dar%' ORDER BY first_name; ``` Output: ``` first_name | last_name ------------+----------- Darlene | Rose Darrell | Power Darren | Windham Darryl | Ashcraft Daryl | Larue (5 rows) ``` ### PostgreSQL LIKE operator with ESCAPE option Sometimes, the data, that you want to match, contains the wildcard characters `%` and `_`. For example. ``` The rents are now 10% higher than last month The new film will have _ in the title ``` To instruct the `LIKE` operator to treat the wildcard characters `%` and `_` as regular literal characters, you can use the `ESCAPE` option in the `LIKE` operator ```sql string LIKE pattern ESCAPE escape_character; ``` Creating Table, ```sql CREATE TABLE t( message text ); INSERT INTO t(message) VALUES('The rents are now 10% higher than last month'), ('The new film will have _ in the title'); SELECT message FROM t; ``` Output: ``` message ---------------------------------------------- The rents are now 10% higher than last month The new film will have _ in the title (2 rows) ``` The following statement uses the `LIKE` operator with the `ESCAPE` option to treat the `%` followed by the number `10` as a regular character. ```sql SELECT * FROM t WHERE message LIKE '%10$%%' ESCAPE '$'; ``` Output: ``` message ---------------------------------------------- The rents are now 10% higher than last month (1 row) ``` In the pattern `%10$%%`, the first and last `%` are the wildcard characters whereas the `%` appears after the escape character `$` is a regular character. # SIMILAR TO In SQL, when you need to perform pattern matching with more advanced capabilities than the **LIKE** operator, you can use the **SIMILAR TO** operator. The **SIMILAR TO** operator in PostgreSQL offers a way to search for patterns that follow regular expression rules. Imagine you want to find customers in a database whose names contain a combination of specific characters. For example, you might remember that the customer names contain specific combinations of letters, but you don’t know the exact order. Instead of searching with multiple `LIKE` statements or using wildcard characters excessively, you can use the **SIMILAR TO** operator, which can provide much more powerful and flexible pattern matching. ## **How SIMILAR TO Works** The **SIMILAR TO** operator allows you to match a pattern to a string using regular expression syntax, similar to **POSIX regular expressions**. The syntax for **SIMILAR TO** is ```sql value SIMILAR TO pattern ``` Where: * `value` is the string or column to be matched. * `pattern` is the regular expression pattern that you want to compare against. The **SIMILAR TO** operator is used to check if the value matches the given regular expression pattern. If it does, the operator returns `TRUE`; otherwise, it returns `FALSE`. ## **Basic Syntax and Examples** Let’s look at some basic examples of using the **SIMILAR TO** operator in PostgreSQL. ### **1) Basic SIMILAR TO Example** Let’s say you want to find customers whose first names start with either "Jen", "Jon", or "Jan". Instead of writing multiple `LIKE` queries, you can use **SIMILAR TO** with a single pattern. ```sql SELECT first_name, last_name FROM customer WHERE first_name SIMILAR TO 'J(e|o|a)n%'; ``` In this example: * The pattern `J(e|o|a)n%` matches any string that starts with "J" followed by either "e", "o", or "a", and then any sequence of characters (`%`). * The `|` character is used to specify alternatives within the regular expression. ### **2) Using SIMILAR TO with Multiple Patterns** If you want to match several different patterns, **SIMILAR TO** lets you combine them into one regular expression. For example, to find customer names that start with "A" or "B" and have two additional characters, you could use ```sql SELECT first_name, last_name FROM customer WHERE first_name SIMILAR TO '[AB]__'; ``` In this query: * `[AB]` means the first character must be "A" or "B". * `__` means there should be exactly two characters following it. ### **3) SIMILAR TO with Wildcards** You can also use wildcards in the **SIMILAR TO** pattern. For instance, if you want to find any first name starting with "S" and followed by at least one character. ```sql SELECT first_name, last_name FROM customer WHERE first_name SIMILAR TO 'S%'; ``` Here, `%` is the wildcard matching any sequence of characters (just like in **LIKE**). # IS NULL In the database world, NULL means missing information or not applicable. NULL is not a value, therefore, you cannot compare it with other values like numbers or strings. The comparison of NULL with a value will always result in NULL. Additionally, NULL is not equal to NULL so the following expression returns NULL. ```sql SELECT null = null AS result; ``` Output: ``` result -------- null (1 row) ``` ### IS NULL operator To check if a value is NULL or not, you cannot use the equal to (`=`) or not equal to (`<>`) operators. Instead, you use the `IS NULL` operator. Here’s the basic syntax of the `IS NULL` operator. ```sql value IS NULL ``` The `IS NULL` operator returns true if the `value` is NULL or false otherwise. To negate the `IS NULL` operator, you use the `IS NOT NULL` operator. ```sql value IS NOT NULL ``` The `IS NOT NULL` operator returns true if the value is not NULL or false otherwise. ### 1) Basic IS NULL operator example The following example uses the `IS NULL` operator to find the addresses from the `address` table that the `address2` column contains `NULL` ```sql SELECT address, address2 FROM address WHERE address2 IS NULL; ``` ### 2) Using the IS NOT NULL operator example The following example uses the `IS NOT NULL` operator to retrieve the address that has the `address2` not NULL. ```sql SELECT address, address2 FROM address WHERE address2 IS NOT NULL; ``` Notice that the `address2` is empty, not NULL. This is a good example of **bad practice** when it comes to storing empty strings and NULL in the same column. # ESCAPE ## **What is the ESCAPE Clause in SQL?** The **ESCAPE** clause in SQL is used to define an **escape character** in a query. This allows you to escape special characters (like `%`, `_`, `'`, and others) that are typically used for pattern matching or other special functions. By using ESCAPE, you can indicate that certain characters in your pattern should be treated **literally**, rather than having their special meaning in SQL queries. In pattern matching, particularly with `LIKE` and `SIMILAR TO`, certain characters are treated as **wildcards** or **operators**. However, when you need those characters to be part of your search term rather than their wildcard behavior, the ESCAPE clause ensures that the database treats them as regular text. ## **Common Characters You Need to Escape in SQL** ### **1. Single Quote (`'`)** The single quote is one of the most common characters that you need to escape. In SQL, single quotes are used to delimit string literals. To include a single quote inside a string, you need to escape it by doubling it. * **Why**: Single quotes mark the start and end of a string literal. To include a literal single quote, you must "escape" it by doubling it. * **Example**:\ To represent the string `It's a movie`, you would write ```sql 'It''s a movie' ``` ### **2. Backslash (`\`)** The backslash is often used as an **escape character** itself, which means it needs to be handled with care when included in a string or pattern. In databases like PostgreSQL, the backslash needs to be **doubled** (`\\`) to be treated as a literal backslash. * **Example**:\ To include a backslash in a string, you would write ```sql SELECT 'This is a backslash: \\'; ``` In this case, the `\\` ensures that the database treats the backslash as a character, rather than interpreting it as an escape character. ### **3. Percent Sign (`%`)** The percent sign is a wildcard character in SQL's `LIKE` pattern matching, representing **zero or more characters**. If you want to match a literal `%`, you need to escape it. * **How to Escape**: You can use the `ESCAPE` clause to define an escape character. For example, you might use an exclamation mark (`!`) as your escape character. * **Example** ```sql SELECT title FROM film WHERE title LIKE '%!%%' ESCAPE '!'; ``` Here, the exclamation mark (`!`) tells SQL to treat the `%` as a literal character, rather than its wildcard meaning. ### **4. Underscore (`_`)** The underscore (`_`) is another wildcard in SQL’s `LIKE` pattern, but it matches **exactly one character**. To use an underscore literally (i.e., if you’re searching for an actual underscore in your data), you need to escape it. * **How to Escape**: Similar to the percent sign, you can use the `ESCAPE` clause to define an escape character. * **Example** ```sql SELECT title FROM film WHERE title LIKE '%!_%' ESCAPE '!'; ``` In this case, `!` tells SQL to treat `_` as a literal underscore. ### **5. Square Brackets (`[]`)** In some SQL dialects (e.g., SQL Server), square brackets are used for pattern matching to define character ranges. If you need square brackets as literals, you must escape them. * **How to Escape**: In SQL Server, you would use the `ESCAPE` clause or escape brackets by placing a `!` in front of them. * **Example** ```sql SELECT title FROM film WHERE title LIKE '%[[]%' ESCAPE '!'; ``` This will match movie titles that contain the literal square bracket (`[`). #### **Using the ESCAPE Clause in SQL Queries** The **ESCAPE clause** allows you to specify a character that will be used to escape special characters in a `LIKE` pattern. This escape character tells SQL that the next character should be treated literally, and not as part of the SQL pattern matching. **Syntax of the ESCAPE Clause** ```sql SELECT column_name FROM table_name WHERE column_name LIKE 'pattern' ESCAPE 'escape_character'; ``` In this syntax * `pattern` is the search pattern you are using with `LIKE`. * `escape_character` is the character you define to treat special characters literally. | Character | Escape Required | SQL Example | | ---------------------- | ------------------- | --------------------------- | | Single Quote (`'`) | Yes | `'It''s a movie'` | | Percent Sign (`%`) | Yes (with `ESCAPE`) | `LIKE '%!%%' ESCAPE '!'` | | Underscore (`_`) | Yes (with `ESCAPE`) | `LIKE '%!_%' ESCAPE '!'` | | Backslash (`\`) | Yes | `'This is a backslash: \\'` | | Square Brackets (`[]`) | Yes (with `ESCAPE`) | `LIKE '%[[]%' ESCAPE '!'` | The ESCAPE clause is an essential tool for handling special characters in SQL queries, especially when working with pattern matching using `LIKE` or `SIMILAR TO`. By using ESCAPE, you can ensure that your queries are both precise and robust, allowing you to match patterns that include characters with special meanings, such as single quotes, percent signs, underscores, and others. # DB-TASK-002 {% tabs %} {% tab title="1. Question" %} Get all movies (films) that have a rental rate greater than $3. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_rate FROM film WHERE rental_rate > 3; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="2. Question" %} Get all movies that have a rental rate greater than $3 *and* a replacement cost less than $20. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_rate, replacement_cost FROM film WHERE rental_rate > 3 AND replacement_cost < 20; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="3. Question" %} Get all movies that are either rated as 'PG' or have a rental rate of $0.99. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rating, rental_rate FROM film WHERE rating = 'PG' OR rental_rate = 0.99; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="4. Question" %} Show the first 10 movies sorted by rental rate (highest first). {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="5. Question" %} Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order. {% endtab %} {% tab title="Solution" %} Using FETCH ```sql SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 FETCH NEXT 3 ROWS ONLY; ``` Using LIMIT ```sql SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="6. Question" %} Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order. {% endtab %} {% tab title="Solution" %} {% endtab %} {% endtabs %} {% tabs %} {% tab title="7. Question" %} Get all movies with a rental duration between 3 and 7 days. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_duration FROM film WHERE rental_duration BETWEEN 3 AND 7; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="8. Question" %} Get all movies where the title starts with 'A' and ends with 'e' {% endtab %} {% tab title="Solution" %} ```sql SELECT title FROM film WHERE title LIKE 'A%e'; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="9. Question" %} Find all customers who do not have an email address listed. {% endtab %} {% tab title="Solution" %} ```sql SELECT first_name, last_name FROM customer WHERE email IS NULL; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="10. Question" %} Find all movies released in 2006 with a rental rate of $2.99 or $3.99, and their title starts with 'S'. Display the top 5 results.SELECT title, rental\_rate, release\_year {% endtab %} {% tab title="Solution" %} ```sql FROM film WHERE release_year = 2006 AND rental_rate IN (2.99, 3.99) AND title LIKE 'S%' LIMIT 5; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="11. Question" %} Display 10 customers after skipping the first 20, sorted alphabetically by last name. {% endtab %} {% tab title="Solution" %} ```sql SELECT first_name, last_name FROM customer ORDER BY last_name LIMIT 10 OFFSET 20; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="12. Question" %} Get the top 5 movies with the highest replacement cost, skipping the most expensive one. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, replacement_cost FROM film ORDER BY replacement_cost DESC OFFSET 1 FETCH NEXT 5 ROWS ONLY; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="13. Question" %} Find all rentals that occurred between '2005-05-01' and '2005-06-01'. {% endtab %} {% tab title="Solution" %} ```sql SELECT rental_id, rental_date, customer_id FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01'; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="14. Question" %} Get all actors whose last names contain the letters "man". {% endtab %} {% tab title="Solution" %} ```sql SELECT first_name, last_name FROM actor WHERE last_name LIKE '%man%'; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="15. Question" %} Find all movies where the special features are not listed (i.e., `special_features` is `NULL`). {% endtab %} {% tab title="Solution" %} ```sql SELECT title FROM film WHERE special_features IS NULL; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="16. Question" %} Find all movies where the rental duration is more than 7 days. {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_duration FROM film WHERE rental_duration > 7; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="17. Question" %} Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "L". {% endtab %} {% tab title="Solution" %} ```sql SELECT title, rental_rate, rating FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="18. Question" %} Find all movies where the title starts with "A" or "B" and ends with "s". {% endtab %} {% tab title="Solution" %} ```sql SELECT title FROM film WHERE title SIMILAR TO '(A|B)%s'; ``` {% endtab %} {% endtabs %} {% tabs %} {% tab title="19. Question" %} Find all movies where the title contains "Man", "Men", or "Woman". {% endtab %} {% tab title="Solution" %} ```sql SELECT title FROM film WHERE title SIMILAR TO '%(Man|Men|Woman)%'; ``` {% endtab %} {% endtabs %} ### Bonus Q/A 1. Find all movies where the special features are not listed (i.e., `special_features` is `NULL`). 2. Find all movies where the rental duration is more than 7 days. 3. Find all movies that have a rental rate of $4.99 *and* a replacement cost of more than $20. 4. Find all movies that have a rental rate of $0.99 *or* a rating of 'PG-13'. 5. Retrieve the first 5 rows of movies sorted alphabetically by title. 6. Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost. 7. Find all movies where the rating is either 'G', 'PG', or 'PG-13'. 8. Find all movies with a rental rate between $2 and $4. 9. Find all movies with titles that start with 'The'. 10. Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "Love". 11. Find all movies where the title contains the `%` symbol. 12. Find all movies where the title contains an underscore (`_`). 13. Find all movies where the title starts with "A" or "B" and ends with "s". 14. Find all movies where the title contains "Man", "Men", or "Woman". 15. Find all movies with titles that contain digits (e.g., "007", "2", "300"). 16. Find all movies with titles containing a backslash (`\`). 17. Find all movies where the title does contain the words "Love" or "Hate". 18. Find the first 5 movies with titles that end with "er", "or", or "ar". # What is an ER Diagram ? An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how "entities" such as peopl, objects or concepts relate to each other within a system. ER Diagrams are most often used to design or debug relational database in the fields of software engineering, business information system, education and research. Also known as ERDs or ER Models, they use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to depict the interconnectedness of entities, relationships and their attributes. They mirror grammatical structure, with entities as nouns and relationships as verbs. ER diagrams are related to data structure diagrams (DSD), which focus on the relationships of elements within entities instead of relationships between entities themselves. ER diagrams also are often used in conjunction with data flow diagrams (DFD), which map out the flow of information for processes or systems. Diagramming is quick and easy with [draw.io](https://app.diagrams.net/), [Lucidchart](https://www.lucidchart.com), [smartdraw.com](https://www.smartdraw.com/). It's quick, easy and completely free. > 📏 Note: Personal recommendation is [draw.io. ](https://app.diagrams.net/) ### Mapping Natural Language ER components can be equated to parts of speech. * Common Noun: Entity Type, Example: Student * Proper Noun: Entity, Example: Syed Jafer * Verb: Relationship Type, Example: Enrolls ( Such as in course, which would be another entity type ) * Adjective: Attribute for entity, Example: Piano * Adverb: Attribute for relationship, Example: Digitally. > 🥁 You might need to revisit this again at the end of this chapter. Let us consider the example of **University Management System.** # Entity A definable thing - such as a person, object, concept or event, that can have data stored about it. Think of entities as nouns. Examples: a customer, student, car or product. Organisation require information in order to carry out tasks and activities for which they are responsible. The information that these organisations need could be categorized in a number of ways, for example, An entity may represent a category of people, things, events, locations or concepts within the area under consideration. An entity instance is a specific example of an entity. For example, Syed Jafer is an entity instance of an student entity. Denoted with Rectangle.
### **Entity Type** A group of definable things, such as **students**. Examples include: * **Student**: A generic type representing all students. * **Course**: Represents all courses offered by the university. * **Department**: Represents academic departments. **Specific Entities** * A specific student, e.g., *John Doe, Student\_ID: 12345*. * A specific course, e.g., *Calculus I, Course\_ID: MATH101*. * A specific department, e.g., *Mathematics Department*. ### **Entity Set** Represents an entity type at a specific point in time. Examples include: * **Students enrolled in "Fall 2024" semester**: This subset might include *John Doe, Jane Smith, and Alice Brown*. * **Courses offered in "Spring 2025"**: Includes *MATH101, ENG202, and CS301*. * **Departments active as of "2024"**: Includes *Mathematics, Physics, and Computer Science*. **Related Instances** * Instance of **Student**: *John Doe, enrolled in Spring 2024*. * Instance of **Course**: *MATH101, offered by the Mathematics Department*. ### **Entity Categories** > 🥁 For now, dont think about primary key, foreign key or something. **Strong Entity** * **Student**: A strong entity because it can be uniquely identified using its own attributes like *Student\_ID*. * Example attributes: * Student\_ID (Primary Key) * Name * Date\_of\_Birth
**Weak Entity** * **Dependent**: A weak entity related to a student, such as a family member. It depends on the Student entity for identification. Even though it has a unique ID, Conceptually its dependant on student. * Example: *Dependent\_ID* is meaningful only when associated with a specific Student\_ID.
**Associative Entity** * **Enrollment**: An entity that associates Students and Courses. * Example attributes: * Enrollment\_ID (Primary Key) * Student\_ID (Foreign Key) * Course\_ID (Foreign Key) * Semester **The Associative Entity is there to establish a Many to Many Relationship between Two or More Entities.** For Example, A Student can enroll in many courses. Also a course can be enrolled by many students. But we can't able to manage these details without an additional Entity (Table). This Entity is denoted as Associative Entity.
Also, Associative Entity, can contain additional values like duration in above example. ### What Should an Entity Type Be? **SHOULD BE:** • An object that will have many instances in the database • An object that will be composed of multiple attributes • An object that we are trying to model **SHOULD NOT BE:** • A user of the database system • An output of the database system (e.g. a report) # Identifiers Identifiers refers to an attribute that uniquely defines an entity in an entity set. Entity keys can be super, candidate or primary. ### **1. Super Key** * A **super key** is any set of one or more attributes that can uniquely identify a record in a table. * **Example:** In a **Students** table: * Attributes: `StudentID`, `Name`, `Email`, `PhoneNumber` * Super Key: `{StudentID}`, `{StudentID, Name}`, `{StudentID, Email}`, etc. * Any combination that includes `StudentID` uniquely identifies a student. ### **2. Candidate Key** * A **candidate key** is a minimal super key — a super key with no redundant attributes. * **Example:** In a **Students** table: * `{StudentID}` (if `StudentID` uniquely identifies students) * `{Email}` (if all emails are unique for students) ### **3. Primary Key** * A **primary key** is the candidate key selected to uniquely identify records in a table. * **Example:** In a **Students** table: * Primary Key: `{StudentID}` (chosen from candidate keys because it is short and meaningful) ### **4. Foreign Key** * A **foreign key** is an attribute in one table that refers to the primary key of another table, creating a relationship between the two tables. * **Example:** * In an **Enrollments** table: * Attributes: `EnrollmentID`, `StudentID`, `CourseID` * Foreign Keys: * `StudentID` references `StudentID` in the **Students** table. * `CourseID` references `CourseID` in the **Courses** table. ### Why Not Just Use the Primary Key for Everything? 1. **Super Key and Candidate Key**\ These help **identify possible unique identifiers** for the data. Not every candidate key becomes a primary key; the others may have other uses or simply remain as potential unique identifiers. 2. **Primary Key**\ Only one key can serve as the **primary identifier**, but other candidate keys still exist and may be useful for specific queries or constraints. 3. **Foreign Key**\ While primary keys are unique within their own table, foreign keys are used to create **relationships** between tables. Imagine a classroom * **Super Key:** All possible combinations to uniquely identify a student (e.g., roll number + name, roll number alone, or roll number + email). * **Candidate Key:** The minimal identifiers (e.g., roll number, email). * **Primary Key:** The chosen identifier (e.g., roll number). * **Foreign Key:** A way to link the student with their course (e.g., roll number in the student table links to the enrollment table). By defining these keys, we ensure **data uniqueness**, **integrity**, and a well-structured database! ### Declaring a Primary Key ```sql CREATE TABLE students ( sid VARCHAR(10) PRIMARY KEY, name VARCHAR(20), address VARCHAR(20) ); ```
Let's try to insert values into the table, ```sql INSERT INTO students VALUES ('12345-6789', 'Syed Jafer', 'Coimbatore'); ``` After Insertion,
Now, if we try to Insert the same record again, it will result in a duplicate key error.
### Declaring Other Candidate Keys If we want the DBMS to check other uniqueness constraints, we may add to the SQL relation schema any number of lines of the form, ```sql UNIQUE () ``` Uniqueness is not guaranteed for tuples having NULL values in the key attributes. However, NULL values can be prevented by adding a NOT NULL constraint after the declaration of each key attribute. ```sql CREATE TABLE Student ( sid VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, address VARCHAR(20), hobby VARCHAR(20), UNIQUE (name, address) ); ``` Let's Insert one value, ```sql INSERT INTO Student VALUES ( '06027-5112','Syed Jafer','Coimbatore','chess'); ``` Try Inserting the below value again, ```sql INSERT INTO Student VALUES ( '06027-5113','Syed Jafer','Coimbatore','chess'); ```
Like these, candidate key helps us in saving us from entering duplicate values. # Relationship How entities act upon each other or are associated with each other. Think of relationships as verbs. For example, the named student might register for a course. The two entities would be the student and the course, and the relationship depicted is the act of enrolling, connecting the two entities in that way. Relationships are typically shown as diamonds or labels directly on the connecting lines.
## Degree of a relationship set The number of different entity sets participating in a relationship set is called as degree of a relationship set. ### Unary Relationship A **unary relationship** (also known as a **recursive relationship**) is a relationship where an entity in a table is related to another entity in the same table. This occurs when entities of the same type are associated with each other. #### **1. Employee-Manager Relationship** * **Scenario:** In a company, each employee can have a manager, who is also an employee. * **Entity:** `Employee` * **Relationship:** "Manages" (an employee is managed by another employee) **Table Structure:** ```sql CREATE TABLE Employee ( EmployeeID SERIAL PRIMARY KEY, Name TEXT NOT NULL, ManagerID INT REFERENCES Employee(EmployeeID) ); ``` **Explanation:** * `EmployeeID` is the primary key for employees. * `ManagerID` refers to another `EmployeeID` in the same table, creating a recursive relationship. * Example Data: | EmployeeID | Name | ManagerID | | ---------- | ------- | --------- | | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | * Alice is a manager (no `ManagerID` because she's at the top level). * Bob and Charlie report to Alice.
2. **Person-Family Relationship** * **Scenario:** In a family tree, a person can have a parent, who is also a person. * **Entity:** `Person` * **Relationship:** "Is Parent Of" (a person can have a parent or be a parent) **Table Structure:** ```sql CREATE TABLE Person ( PersonID SERIAL PRIMARY KEY, Name TEXT NOT NULL, ParentID INT REFERENCES Person(PersonID) ); ``` **Explanation:** * `PersonID` uniquely identifies each person. * `ParentID` references the `PersonID` of the person's parent. * Example Data: | PersonID | Name | ParentID | | -------- | ----- | -------- | | 1 | John | NULL | | 2 | Alice | 1 | | 3 | Bob | 1 | * John is the parent of Alice and Bob. Unary relationships are useful for modeling **hierarchical** or **self-referencing** relationships. The foreign key within the same table is used to enforce this relationship. ### Binary Relationship A **binary relationship** occurs between two different entities in a database. It defines the association between instances of two separate entity types. There are three main types of binary relationships based on cardinality: **one-to-one (1:1)**, **one-to-many (1:N)**, and **many-to-many (M:N)**. #### **One-to-One (1:1) Relationship** * **Scenario:** A university maintains a database of students and their assigned lockers. Each student is assigned exactly one locker, and each locker is assigned to exactly one student. * **Entities:** * `Student` * `Locker` * **Relationship:** "Assigned to" **Table Structure** ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, Name TEXT NOT NULL ); CREATE TABLE Locker ( LockerID SERIAL PRIMARY KEY, LockerNumber TEXT NOT NULL UNIQUE, StudentID INT UNIQUE REFERENCES Student(StudentID) ); ``` **Explanation:** * The `StudentID` in `Locker` is a foreign key referencing `StudentID` in `Student`. * `StudentID` in `Locker` is unique, ensuring one locker per student. * Example Data: | StudentID | Name | | --------- | ----- | | 1 | Alice | | 2 | Bob | | LockerID | LockerNumber | StudentID | | -------- | ------------ | --------- | | 1 | L001 | 1 | | 2 | L002 | 2 |
**One-to-Many (1:N) Relationship** * **Scenario:** A department in a company has multiple employees. Each employee belongs to one department. * **Entities:** * `Department` * `Employee` * **Relationship:** "Belongs to" **Table Structure** ```sql CREATE TABLE Department ( DepartmentID SERIAL PRIMARY KEY, DepartmentName TEXT NOT NULL ); CREATE TABLE Employee ( EmployeeID SERIAL PRIMARY KEY, Name TEXT NOT NULL, DepartmentID INT REFERENCES Department(DepartmentID) ); ``` **Explanation:** * The `DepartmentID` in `Employee` is a foreign key referencing `DepartmentID` in `Department`. * Multiple employees can belong to the same department, but each employee belongs to only one department. * Example Data: | DepartmentID | DepartmentName | | ------------ | -------------- | | 1 | HR | | 2 | IT | | EmployeeID | Name | DepartmentID | | ---------- | ------- | ------------ | | 1 | Alice | 1 | | 2 | Bob | 2 | | 3 | Charlie | 1 |
**Many-to-Many (M:N) Relationship** * **Scenario:** Students can enroll in multiple courses, and each course can have multiple students. * **Entities:** * `Student` * `Course` * **Relationship:** "Enrolled in" **Table Structure** ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, Name TEXT NOT NULL ); CREATE TABLE Course ( CourseID SERIAL PRIMARY KEY, CourseName TEXT NOT NULL ); CREATE TABLE Enrollment ( StudentID INT REFERENCES Student(StudentID), CourseID INT REFERENCES Course(CourseID), PRIMARY KEY (StudentID, CourseID) ); ``` **Explanation:** * The `Enrollment` table acts as a bridge between `Student` and `Course`. * Each row in `Enrollment` represents a unique combination of a student and a course. * Example Data: | StudentID | Name | | --------- | ----- | | 1 | Alice | | 2 | Bob | | | | | CourseID | CourseName | | -------- | ---------------- | | 1 | Mathematics | | 2 | Computer Science | | StudentID | CourseID | | --------- | -------- | | 1 | 1 | | 1 | 2 | | 2 | 1 |
# Attributes Attributes are the properties or characteristics of an entity in a database. They represent the data stored about the entity and are implemented as columns in a table. Graphically represented as ovals or circles. Here are examples of different types of attributes with explanations, ### **1. Simple Attribute** * **Definition:** Cannot be divided into smaller parts. * **Example:** * **Entity:** `Student` * **Attributes:** `StudentID`, `Name`, `Age` * Explanation: `StudentID` is a simple, indivisible identifier for a student. **Table Example** ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, Name TEXT NOT NULL, Age INT ); ```
### **2. Composite Attribute** * **Definition:** Can be divided into smaller sub-parts, each with independent meaning. * **Example:** * **Entity:** `Student` * **Attribute:** `FullName` (composed of `FirstName` and `LastName`) * Explanation: `FullName` can be divided into `FirstName` and `LastName`. **Table Example** ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL ); ```
### **3. Derived Attribute** * **Definition:** Can be calculated from other attributes. * **Example:** * **Entity:** `Employee` * **Attributes:** `DateOfBirth`, `Age` * Explanation: `Age` is derived from `DateOfBirth`. **Table Example** ```sql CREATE TABLE Employee ( EmployeeID SERIAL PRIMARY KEY, Name TEXT NOT NULL, DateOfBirth DATE ); -- Age can be calculated as: -- SELECT DATE_PART('year', AGE(DateOfBirth)) AS Age FROM Employee; ```
### **4. Multivalued Attribute** * **Definition:** Can have more than one value for a single entity. * **Example:** * **Entity:** `Student` * **Attribute:** `PhoneNumbers` (a student may have multiple phone numbers) * Explanation: A student can have a home phone and a mobile phone. **Table Example** To handle multivalued attributes, we use a separate table ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, Name TEXT NOT NULL ); CREATE TABLE StudentPhone ( StudentID INT REFERENCES Student(StudentID), PhoneNumber TEXT NOT NULL ); ```
### **5. Key Attribute** * **Definition:** Uniquely identifies an entity in a table. * **Example:** * **Entity:** `Student` * **Attribute:** `StudentID` * Explanation: `StudentID` is a unique identifier for each student. **Table Example** ```sql CREATE TABLE Student ( StudentID SERIAL PRIMARY KEY, Name TEXT NOT NULL ); ```
### Summary | **Type** | **Description** | **Example** | | -------- | --------------- | ----------- | | **Simple** | Cannot be divided further. | `Name`, `Age` | | ---------- | -------------------------- | ------------- | | **Composite** | Can be divided into sub-parts. | `FullName` → `FirstName`, `LastName` | | ------------- | ------------------------------ | ------------------------------------ | | **Derived** | Calculated from other attributes. | `Age` from `DateOfBirth` | | ----------- | --------------------------------- | ------------------------ | | **Multivalued** | Can have multiple values for one entity. | `PhoneNumbers` | | --------------- | ---------------------------------------- | -------------- | | **Key** | Uniquely identifies an entity. | `StudentID` | | ------- | ------------------------------ | ----------- | | **Foreign Key** | References the primary key of another entity. | `StudentID` in `Enrollment` table | | --------------- | --------------------------------------------- | --------------------------------- | # Cardinality Defines the numerical attributes of the relationship between two entities or entity sets. The three main cardinal relationships are one-to-one (1:1), one-to-many (1:m), and many-many (m:n). **A one-to-one example**: would be one student associated with one login name. **A one-to-many example** (or many-to-one, depending on the relationship direction): One product line contains multiple products. **Many-to-many example**: Students as a group are associated with multiple faculty members (teachers), and faculty members (teachers) in turn are associated with multiple students.
**Cardinality views:** Cardinality can be shown as look-across or same-side, depending on where the symbols are shown. **Cardinality constraints and obligated (or participation constraint)**: The minimum or maximum numbers that apply to a relationship # Entity Relationship Diagram Tutorial Entity Relationship Diagram Tutorial Here are some best practice tips for constructing an ERD: 1. Purpose and scope: Define the purpose and scope of what you’re analyzing or modeling. Add a description of your Subject Area. Document any assumptions and restrictions then you make. Show the level of detail necessary for your purpose. You might want to draw a conceptual, logical or physical model, depending on the detail needed. (See above for descriptions of those levels.) 2. Entities: Identify the entities that are involved. Identify all of the entities you will use. An entity is a description of something that your system stores information about. This could be a customer, a manager, an invoice, a schedule, etc. Draw a rectangle (or your system’s choice of shape) for each entity you can think of on your page and labeling them as nouns. Keep them spaced out a bit. 3. Relationships: Determine how the entities are all related. Look at two entities, are they related? If so draw a solid line connecting the two entities and label them. Some entities may not be related, and that’s fine. In different notation systems, the relationship could be labeled in a diamond, another rectangle or directly on top of the connecting line. How are the entities related? Write a brief description of how they are related. Each of your entities may not have any relationships, some may have multiple relationships. That is okay. 4. Cardinality: Show whether 1-1, 1-many or many-to-many. 5. Obligated (or participation): Show whether the relationship is mandatory or optional. 6. Attributes: Layer in more detail by adding attributes of entities. Attributes are often shown as ovals. Any key attributes of entities should be added. 7. Analyze and complete the diagram: Execute the tips for effective ER diagrams: a) Make sure the ER diagram supports all the data you need to store. b) Make sure all your entities and relationships and attribute are labeled on your diagram. c) Make sure that each entity only appears once per diagram. Eliminate any redundant entities. d) Examine relationships between entities closely. Are they necessary? Are there any relationships missing? Eliminate any redundant relationships. Don't connect relationships to each other. e) Use colors to highlight important portions of your diagram. f) You can transition relational tables and ER diagrams back and forth, if that helps you achieve your goal. g) If you’re troubleshooting a database problem, watch for holes in relationships or missing entities or attributes
# DB-TASK-003 1. Online Book Store * A **customer** can place multiple **orders**, but each **order** belongs to one **customer**. * A **book** can appear in multiple **orders**, and an **order** can contain multiple **books**. * Each **order** consists of one or more **order details** entries, specifying the quantity and price of each book in that order. * The **stock quantity** of a book should decrease when an order is placed. * A book’s **price** is stored at the time of the order to maintain historical pricing.
**Customer Table** ```sql CREATE TABLE customer ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, address TEXT, phone_number VARCHAR(15) ); ``` **Book Table** ```sql CREATE TABLE book ( book_id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, author VARCHAR(100), isbn VARCHAR(20) UNIQUE, price DECIMAL(10, 2) NOT NULL, stock_quantity INT NOT NULL ); ``` **Order Table** ```sql CREATE TABLE order_table ( order_id SERIAL PRIMARY KEY , order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, customer_id INT NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ); ``` **OrderDetails Table** ```sql CREATE TABLE order_details ( order_details_id SERIAL PRIMARY KEY , order_id INT NOT NULL, book_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES order_table (order_id), FOREIGN KEY (book_id) REFERENCES book(book_id) ); ``` **Insert Sample Data** Insert Customers: ```sql INSERT INTO customer (name, email, address, phone_number) VALUES ('Alice Johnson', 'alice.johnson@example.com', '123 Elm Street, City A', '1234567890'), ('Bob Smith', 'bob.smith@example.com', '456 Oak Avenue, City B', '0987654321'); ``` Insert Books: ```sql INSERT INTO book (title, author, isbn, price, stock_quantity) VALUES ('Physics Fundamentals', 'John Doe', '978-1234567890', 50.00, 10), ('Advanced Physics', 'Jane Roe', '978-0987654321', 60.00, 5), ('Organic Chemistry', 'Emily Stone', '978-1122334455', 45.00, 8), ('Inorganic Chemistry', 'Michael Green', '978-6677889900', 55.00, 12); ``` Insert Orders: ```sql INSERT INTO order_table (order_date, customer_id, total_amount) VALUES ('2024-12-01 10:00:00', 1, 155.00), ('2024-12-02 15:30:00', 2, 105.00); ``` Insert Order Details: ```sql INSERT INTO order_details (order_id, book_id, quantity, price) VALUES (1, 1, 2, 100.00), (1, 3, 1, 45.00), (2, 2, 1, 60.00), (2, 4, 1, 55.00); ``` List all Physics and Chemistry books: ```sql SELECT * FROM book WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%'; ``` Get total stock of Physics and Chemistry books: ```sql SELECT title, stock_quantity FROM book WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%'; ``` Find the total sales for Physics books: ```sql SELECT SUM(price * quantity) AS total_sales FROM order_details od JOIN book b ON od.book_id = b.book_id WHERE b.title LIKE '%Physics%'; ``` 2. Library Management System * A **member** can borrow multiple **books**, but each **loan** belongs to one **member**. * A **book** can be loaned to multiple members over time, but only to one member at a time. * The **return date** should be recorded when the book is returned.
3. School Management System * A **student** can enroll in multiple **classrooms**, and each **classroom** can have multiple **students**. * Each **classroom** is assigned one **teacher**. * A **teacher** can teach multiple **classrooms**. 4. E-Commerce System * A **customer** can place multiple **orders**, and each **order** can have multiple **products** via **OrderDetails**. * A **product** can belong to multiple **categories** via **ProductCategory**. * Each **order** must be associated with a **payment**. # Joins PostgreSQL join is used to combine columns from one or more tables based on the values of the common columns between related tables. The common columns are typically the primary key columns of the first table and the foreign key columns of the second table. #### Scenario 1: ```sql CREATE TABLE basket_a ( a INT PRIMARY KEY, fruit_a VARCHAR (100) NOT NULL ); CREATE TABLE basket_b ( b INT PRIMARY KEY, fruit_b VARCHAR (100) NOT NULL ); INSERT INTO basket_a (a, fruit_a) VALUES (1, 'Apple'), (2, 'Orange'), (3, 'Banana'), (4, 'Cucumber'); INSERT INTO basket_b (b, fruit_b) VALUES (1, 'Orange'), (2, 'Apple'), (3, 'Watermelon'), (4, 'Pear'); ```
#### Inner Join: The following statement joins the first table (`basket_a`) with the second table (`basket_b`) by matching the values in the `fruit_a` and `fruit_b` columns ```sql SELECT a, fruit_a, b, fruit_b FROM basket_a INNER JOIN basket_b ON fruit_a = fruit_b; ```
The inner join examines each row in the first table (`basket_a`). It compares the value in the `fruit_a` column with the value in the `fruit_b` column of each row in the second table (`basket_b`). If these values are equal, the inner join creates a new row that contains columns from both tables and adds this new row to the result set. The following Venn diagram illustrates the inner join,
#### Left Join The following statement uses the left join clause to join the `basket_a` table with the `basket_b` table. In the left join context, the first table is called the left table and the second table is called the right table. 
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b
   ON fruit_a = fruit_b;
The left join starts selecting data from the left table. It compares values in the fruit\_a column with the values in the fruit\_b column in the basket\_b table. If these values are equal, the left join creates a new row that contains columns of both tables and adds this new row to the result set. (see the row #1 and #2 in the result set). In case the values do not equal, the left join also creates a new row that contains columns from both tables and adds it to the result set. However, it fills the columns of the right table (`basket_b`) with null. (see the row #3 and #4 in the result set).
Note that the `LEFT JOIN` is the same as the `LEFT OUTER JOIN` so you can use them interchangeably. #### Right Join The right join is a reversed version of the left join. The right join starts selecting data from the right table. It compares each value in the fruit\_b column of every row in the right table with each value in the fruit\_a column of every row in the fruit\_a table. If these values are equal, the right join creates a new row that contains columns from both tables. In case these values are not equal, the right join also creates a new row that contains columns from both tables. However, it fills the columns in the left table with NULL. The following statement uses the right join to join the `basket_a` table with the `basket_b` table, ```sql SELECT a, fruit_a, b, fruit_b FROM basket_a RIGHT JOIN basket_b ON fruit_a = fruit_b; ``` Here is the output,
The `RIGHT JOIN` and `RIGHT OUTER JOIN` are the same therefore you can use them interchangeably. #### Full Outer Join The full outer join or full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides if available. In case there is no match, the columns of the table will be filled with NULL. ```sql SELECT a, fruit_a, b, fruit_b FROM basket_a FULL OUTER JOIN basket_b ON fruit_a = fruit_b; ```
#### Full Outer Join Without Common Items ```sql SELECT a, fruit_a, b, fruit_b FROM basket_a FULL JOIN basket_b ON fruit_a = fruit_b WHERE a IS NULL OR b IS NULL; ```
# Joins With Employee and Departments ```sql CREATE TABLE departments ( dept_id SERIAL PRIMARY KEY, dept_name VARCHAR(50) ); CREATE TABLE employees ( emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(50), dept_id INT REFERENCES departments(dept_id), salary INT ); ``` Insert data into the tables, ```sql INSERT INTO departments (dept_name) VALUES ('HR'), ('Finance'), ('IT'); INSERT INTO employees (emp_name, dept_id, salary) VALUES ('Alice', 1, 50000), ('Bob', 2, 60000), ('Charlie', 3, 70000), ('David', NULL, 55000); ``` #### **INNER JOIN** **Definition:** Combines rows from two tables when there is a matching value in the specified columns. * **Use Case:** When you want only the records that have corresponding entries in both tables. * **Real-World Example:** Show employees and their respective departments. ```sql SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; ```
**Explanation:**\ The `INNER JOIN` only includes employees who belong to a department. Employees without a department will not appear in the result. #### **LEFT JOIN (or LEFT OUTER JOIN)** **Definition:** Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for columns of the right table. * **Use Case:** When you want to see all entries in one table (left) and their matches, if they exist, in the other table. * **Real-World Example:** List all employees, including those who don’t belong to any department. ```sql SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; ```
#### **RIGHT JOIN (or RIGHT OUTER JOIN)** **Definition:** Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for columns of the left table. * **Use Case:** When you want to ensure that all entries from the right table are included, even if there’s no match in the left table. * **Real-World Example:** List all departments, even those with no employees ```sql SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id; ``` All departments appear in the result. If a department has no employees, the employee column will show `NULL`. #### **FULL OUTER JOIN** **Definition:** Combines the results of both `LEFT JOIN` and `RIGHT JOIN`. Returns all rows from both tables, with NULLs for non-matching rows. * **Use Case:** When you need to see everything, even if there are no matches in either table. * **Real-World Example:** List all employees and departments, including unmatched records from both. ```sql SELECT e.emp_name, d.dept_name FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.dept_id; ``` **Explanation:**\ The result will include 1. Employees with departments. 2. Employees without departments (`dept_name = NULL`). 3. Departments with no employees (`emp_name = NULL`). # Joins on E-Commerce #### **Setup: Create Tables** We have three tables: 1. **Customers**: Stores customer details. 2. **Orders**: Stores order details. 3. **Products**: Stores product details. ```sql CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(50) ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), product_id INT, quantity INT ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(50), price DECIMAL(10, 2) ); ``` Populate some data, ```sql INSERT INTO customers (customer_name) VALUES ('Alice'), ('Bob'), ('Charlie'); INSERT INTO products (product_name, price) VALUES ('Laptop', 1000.00), ('Smartphone', 500.00), ('Tablet', 300.00); INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 2), (2, 2, 1), (3, NULL, 3); ``` #### **1. INNER JOIN** **Question:** List all customers who placed orders with the product name and quantity. ```sql SELECT c.customer_name, p.product_name, o.quantity FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id INNER JOIN products p ON o.product_id = p.product_id; ```
#### **2. LEFT JOIN** **Question:** Show all customers and their orders, including orders without a product. ```sql SELECT c.customer_name, p.product_name, o.quantity FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN products p ON o.product_id = p.product_id; ```
#### **3. FULL OUTER JOIN** **Question:** Show all customers, orders, and products, including those with no matches. ```sql SELECT c.customer_name, o.order_id, p.product_name FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id FULL OUTER JOIN products p ON o.product_id = p.product_id; ```