Repository files navigation

This repository contains a collection of SQL files that I have created while learning the basics of SQL. The files in this repository are intended to help me practice and reinforce my understanding of SQL concepts, and may be useful to others who are also learning SQL. 👇

In addition to the code in this repository, I have found the following resources to be helpful in learning SQL:

• SQL Documentation by MySQL

• MySQL Data Tables Query by Ben

If you have any suggestions for additional files or resources that would be helpful for learning SQL, please feel free to open an issue or submit a pull request.

The code in this repository is licensed under the MIT License.

View

A view is a named query stored in the database. It's like a virtual table that doesn't store any data. A view can be reused whenever needed by calling the view name, allowing reusability of frequently used queries.

A view is a virtual table based on the result of a SQL query, presenting data in a more organized and simplified way without storing the data in a physical table.

Example

CREATEVIEWNewTableASSELECT sname, grade, percentageFROM resultWHERE percentage>65ORDER BY sname;SELECT*FROM NewTable;DROPVIEW NewTable;CREATE OR REPLACEVIEWNewTableASSELECT rollno, sname, grade, percentageFROM resultWHERE percentage>50ORDER BY sname;SELECT*FROM result;

Union

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.

Rules

• Every SELECT statement within UNION must have the same number of columns.
• The columns must also have similar data types.
• The columns in every SELECT statement must also be in the same order.

Example

SELECT cityFROM employeeUNION ALLSELECT cityFROM customerORDER BY city;

Group By

The GROUP BY clause is used to group rows of a query result set by one or more columns. It's a way to categorize data into groups based on common values in one or more columns.

Example

SELECTMAX(salary)FROM employeeGROUP BY jobORDER BYCOUNT(cno);

Having

The HAVING clause is used to filter groups of rows based on conditions specified in the GROUP BY clause. It's similar to the WHERE clause, but it's used to filter groups instead of individual rows.

Example

SELECTCOUNT(rollno), gradeFROM resultWHERE percentage>65GROUP BY gradeHAVING COUNT>=3;

Indexes in MySQL

Indexes are used to find rows with specific column values quickly, improving the speed of data retrieval operations on a database table. It is similar to GROUP BY. Indexes are used to retrieve data from the database more quickly.

Automatic Indexing

Indexing is automatically assigned/created when the following keywords are used:

• Primary Key
• Foreign Key
• Unique

Manual Indexing

We can also create indexing manually using the following syntax:

Regular Index

CREATEINDEXindex_nameON table_name(column_name);

Other Types of Indexes

• Full Text Index
• Descending Index
• Spatial Index

Points to Remember

1. Index limitations: Index cannot be applied to all columns.
2. Index data structures: When an index is created, it will create a separate database and data structures to perform faster data retrieval operations.
3. Performance impact: Insert and update operations become slow when indexing is used. Only data retrieval is faster.
4. Large database benefits: Indexing is useful with large databases.

An index is a data structure that improves the speed of data retrieval operations on a database table. It is analogous to an index in a book.

• Primary Key Index: Automatically created when a primary key is defined. Ensures uniqueness and fast access by the primary key.
• Unique Index: Ensures all values in the indexed column are unique.
• Regular Index (Non-Unique Index): Speeds up queries on columns that are not unique.
• Full-Text Index: Used for full-text searches.
• Spatial Index: Used for spatial data types.

You can create an index using theCREATE INDEX statement:

CREATEINDEXindex_nameON table_name (column1, column2, ...);

Or for a unique index:

CREATEUNIQUE INDEXindex_nameON table_name (column1, column2, ...);

Indexes are automatically used by the MySQL query optimizer to speed upSELECT queries. For example:

SELECT*FROM table_nameWHERE column1='value';

Ifcolumn1 is indexed, this query will be much faster.

Indexes that cover multiple columns:

CREATEINDEXindex_nameON table_name (column1, column2);

Useful when multiple columns are often queried together.

• Speed Up Query Performance: Especially for large tables.
• Enforce Uniqueness: Through unique indexes.
• Efficient Sorting: As indexes can be used to retrieve sorted data quickly.

• Disk Space: Indexes require additional storage.
• Slower Writes: Inserts, updates, and deletes can be slower because the index must be updated.

You can view the indexes on a table with:

SHOW INDEXFROM table_name;

Indexes can be removed if they are no longer needed:

DROPINDEX index_nameON table_name;

Selectivity is the measure of how well an index can distinguish between rows. High selectivity means fewer rows are matched by the indexed value, which usually leads to better performance.

An index is "covering" if the query can be satisfied just using the index without accessing the table data. This can happen if theSELECT clause only includes columns that are part of the index.

Regularly update statistics and consider rebuilding indexes to maintain performance. Use:

ANALYZE TABLE table_name;

for updating statistics.

The most common type of index in MySQL. Good for equality and range queries.

Used for searching text within large datasets. Created with:

CREATE FULLTEXT INDEX index_nameON table_name(column);

Used with geographic data types. Created with:

CREATE SPATIAL INDEX index_nameON table_name(column);

UseEXPLAIN to understand how MySQL executes a query and whether indexes are being used:

EXPLAINSELECT*FROM table_nameWHERE column='value';

In MySQL, the primary key index is a clustered index, meaning the table data is stored in the order of the primary key. Non-clustered indexes store a separate structure.

Indexes can be used on temporary tables for complex queries:

CREATE TEMPORARY TABLE temp_table ( ... );CREATEINDEXindex_nameON temp_table (column);

Force the use of a specific index using index hints:

SELECT*FROM table_name USE INDEX (index_name)WHERE column='value';

• Index columns used frequently inWHERE,ORDER BY,JOIN, andGROUP BY clauses.
• Avoid indexing columns with a low level of selectivity (e.g., boolean columns).
• Monitor and adjust indexes as the database evolves and query patterns change.

In SQL, ajoin is a way to fetch data from two or more tables based on a related column between them. Think of tables as different departments in a company, each with its own set of records. A join helps you pull together relevant information from these departments to form a complete picture.

1. INNER JOIN: Combines rows from both tables where there is a match in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Combines all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Combines all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the left side.
4. FULL JOIN (or FULL OUTER JOIN): Combines rows when there is a match in one of the tables. It returns NULL for unmatched rows in both tables.
5. CROSS JOIN: Returns the Cartesian product of the two tables, meaning every row in the first table is combined with every row in the second table.
6. SELF JOIN: A table is joined with itself.

Let's break down the most essential joins:

Analogy: Imagine you have two lists of friends from different schools. You want to find friends who are in both lists. An INNER JOIN is like making a new list that includes only those who appear in both original lists.

Syntax:

SELECT columnsFROM table1INNER JOIN table2ONtable1.common_column=table2.common_column;

Example:

SELECTemployees.name,departments.department_nameFROM employeesINNER JOIN departmentsONemployees.department_id=departments.id;

Analogy: Suppose you have a list of all students and another list of students who have paid their fees. A LEFT JOIN helps you get a list of all students along with their fee status, showing NULL for those who haven’t paid.

Syntax:

SELECT columnsFROM table1LEFT JOIN table2ONtable1.common_column=table2.common_column;

Example:

SELECTstudents.name,payments.amountFROM studentsLEFT JOIN paymentsONstudents.id=payments.student_id;

Analogy: Similar to LEFT JOIN but reversed. Imagine the fee payment list is complete but some students may not exist in the main student list. A RIGHT JOIN ensures all fee records are shown, even if some students are missing from the main list.

Syntax:

SELECT columnsFROM table1RIGHT JOIN table2ONtable1.common_column=table2.common_column;

Example:

SELECTorders.id,customers.nameFROM ordersRIGHT JOIN customersONorders.customer_id=customers.id;

Analogy: This join is like merging two lists where you want to see all entries, including those that don’t match in both lists. Imagine merging two contact lists where some contacts might be unique to each list.

Syntax:

SELECT columnsFROM table1FULLJOIN table2ONtable1.common_column=table2.common_column;

Example:

SELECTemployees.name,projects.project_nameFROM employeesFULLJOIN projectsONemployees.project_id=projects.id;

(Note: MySQL does not support FULL JOIN directly; you often use UNION to achieve this.)

Analogy: Think of this as a combination of every item on two different menus, creating a new list of all possible meal combinations.

Syntax:

SELECT columnsFROM table1CROSS JOIN table2;

Example:

SELECTproducts.name,categories.nameFROM productsCROSS JOIN categories;

Analogy: Imagine a list of employees where each employee has a manager who is also an employee in the same list. A SELF JOIN helps you relate employees to their managers.

Syntax:

SELECTA.columns,B.columnsFROM table A, table BWHERE condition;

Example:

SELECTA.employee_nameAS Employee,B.employee_nameAS ManagerFROM employees A, employees BWHEREA.manager_id=B.employee_id;

• INNER JOIN: Fetches matching rows from both tables.
• LEFT JOIN: Fetches all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Fetches all rows from the right table and matching rows from the left table.
• FULL JOIN: Fetches all rows when there is a match in one of the tables.
• CROSS JOIN: Fetches the Cartesian product of both tables.
• SELF JOIN: A table is joined with itself to create a relation within the same table.

To ensure you grasp the prerequisites, please rate your familiarity with the following:

1. Basic SQL queries (SELECT, FROM, WHERE)
2. Primary keys and foreign keys in database tables
3. The concept of relational databases
4. Basic understanding of NULL values in SQL

A subquery is a query nested inside another query. It can be placed in various clauses such as SELECT, FROM, WHERE, and HAVING. Think of it as solving a smaller puzzle (the subquery) to find a piece that fits into the larger puzzle (the main query).

1. Single-Row Subqueries: Returns one row.
2. Multi-Row Subqueries: Returns multiple rows.
3. Scalar Subqueries: Returns a single value.
4. Correlated Subqueries: Subqueries that reference columns from the outer query.
5. Nested Subqueries: Subqueries within subqueries.

Analogy: Imagine asking for the age of the oldest student in a class. The answer is a single value that can be used to find other students of the same age.

Syntax:

SELECT column_nameFROM table_nameWHERE column_name= (SELECT column_nameFROM table_nameWHERE condition);

Example:

SELECT nameFROM employeesWHERE salary= (SELECTMAX(salary)FROM employees);

This query finds the name of the employee with the highest salary.

Analogy: Suppose you want to know which products belong to the categories that have been recently updated. Here, multiple category IDs can be returned.

Syntax:

SELECT column_nameFROM table_nameWHERE column_nameIN (SELECT column_nameFROM table_nameWHERE condition);

Example:

SELECT nameFROM productsWHERE category_idIN (SELECT idFROM categoriesWHERE updated_at>'2023-01-01');

This query finds products in categories updated after a certain date.

Analogy: If you need the average score of a class to compare it with each student's score, a scalar subquery provides that single average value.

Syntax:

SELECT column_name, (SELECT single_value_columnFROM table_nameWHERE condition)AS alias_nameFROM table_name;

Example:

SELECT name, salary, (SELECTAVG(salary)FROM employees)AS avg_salaryFROM employees;

This query compares each employee's salary to the average salary.

Analogy: This is like checking each student's score against the class average while calculating the class average excluding the student being checked. It's a subquery that depends on the outer query.

Syntax:

SELECT column_nameFROM table_name outerWHERE column_name operator (SELECT column_nameFROM table_name innerWHEREouter.column_name=inner.column_name);

Example:

SELECTe1.name,e1.salaryFROM employees e1WHEREe1.salary> (SELECTAVG(e2.salary)FROM employees e2WHEREe1.department_id=e2.department_id);

This query finds employees whose salary is above the average salary in their department.

Analogy: Think of nested subqueries as solving multiple layers of a puzzle. Each layer must be solved to provide the answer for the next layer.

Syntax:

SELECT column_nameFROM table_nameWHERE column_name= (SELECT column_nameFROM table_nameWHERE column_name= (SELECT column_nameFROM table_nameWHERE condition));

Example:

SELECT nameFROM employeesWHERE department_id= (SELECT idFROM departmentsWHERE location_id= (SELECT idFROM locationsWHERE city='San Francisco'));

This query finds employees who work in departments located in San Francisco.

1. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.
2. Single-row subqueries return one row, used with operators like=.
3. Multi-row subqueries return multiple rows, used with operators likeIN.
4. Scalar subqueries return a single value, useful for comparisons.
5. Correlated subqueries depend on the outer query for their values.
6. Nested subqueries are subqueries within other subqueries for complex queries.