SQL Notes
Blog post description.
What is Sql and its uses?
SQL, which stands for Structured Query Language, is a standard programming language specifically designed for managing and manipulating databases. It is widely used in relational database management systems (RDBMS), where data is stored in tables that relate to each other. Here are some of the key uses and applications of SQL:
Data Querying: SQL is primarily used for querying data from a database. This means you can retrieve specific data by writing SQL queries, which are instructions to the database to return data that matches given criteria.
Database Management: SQL allows users to create, modify, and manage databases. This includes creating new databases, modifying the structure of existing databases, and setting up database environments.
Data Manipulation: It includes adding, updating, or deleting records in a database. SQL commands like INSERT, UPDATE, and DELETE are used for these purposes.
Data Definition: SQL is used to define data structures in a database. This involves creating tables, defining fields in the tables, and setting data types for each field. It also includes creating indexes to improve data retrieval speeds.
Data Access Control: SQL can be used to control access to data in a database. This includes granting and revoking permissions and roles, ensuring that only authorized users can access or manipulate data.
Transactional Control: SQL supports transactions, which are sequences of operations performed as a single unit. This ensures data integrity, especially in multi-user environments, by allowing operations to be rolled back if something goes wrong.
Data Integration: SQL allows for the integration of data from multiple sources. This can be useful in data warehousing and online analytical processing (OLAP), where data is collected from various sources for in-depth analysis.
Reporting: SQL queries can be used to generate reports from data, providing insights and supporting decision-making in an organization.
Types of SQL commands
SQL commands can be broadly categorized into several types, each serving different purposes in the management and manipulation of databases. Here are the primary types of SQL commands:
Data Definition Language (DDL): These commands are used for defining and modifying the database structure or schema. Some of the key DDL commands include:
CREATE: Used to create new databases, tables, views, indexes, etc.
ALTER: Modifies the structure of an existing database object, like adding or dropping columns in a table.
DROP: Deletes objects from the database, such as tables, views, or databases.
TRUNCATE: Removes all records from a table, including all spaces allocated for the records are removed.
COMMENT: Adds comments to the data dictionary.
RENAME: Renames an existing database object.
Data Manipulation Language (DML): These commands deal with the manipulation of data present in the database. Common DML commands include:
SELECT: Retrieves data from a database table.
INSERT: Inserts new data into a table.
UPDATE: Modifies existing data in a table.
DELETE: Removes data from a table.
Data Control Language (DCL): DCL commands are used to control access to data in the database. These include:
GRANT: Gives users access privileges to the database.
REVOKE: Withdraws access privileges given by the GRANT command.
Transaction Control Language (TCL): These commands are used to manage transactions in the database. They are crucial for maintaining the integrity of data within SQL databases. Key TCL commands are:
COMMIT: Saves all changes made in the transaction.
ROLLBACK: Restores the database to the last committed state.
SAVEPOINT: Sets a savepoint within a transaction, which you can roll back to without affecting the entire transaction.
SET TRANSACTION: Specifies characteristics for the transaction.
Data Query Language (DQL): Technically, DQL is not recognized as a separate category by all database systems, but it's often used to refer specifically to the SELECT command, which is used to query data from the database.
1. Numeric Types
INT: Used to store integer values (whole numbers). The range is -2,147,483,648 to 2,147,483,647 for signed, and 0 to 4,294,967,295 for unsigned.
Example: age INT
FLOAT: A floating-point number with approximate precision. Suitable for fractional numbers.
Example: price FLOAT(7, 2) (total of 7 digits, 2 after the decimal point)
DECIMAL: A fixed-point number with exact precision. Used for storing exact numeric data values.
Example: total DECIMAL(10, 2) (total of 10 digits, 2 after the decimal point)
2. String Types
VARCHAR: A variable-length string between 1 and 65,535 characters. You must define a length when creating a VARCHAR field.
Example: name VARCHAR(50) (string with a maximum length of 50 characters)
CHAR: A fixed-length string between 1 and 255 characters. The length is set when the table is created, and any unused characters are padded with spaces.
Example: initials CHAR(5) (string with a fixed length of 5 characters)
TEXT: A variable-length string with a maximum length of 65,535 characters. TEXT fields are used when you need to store large amounts of text.
Example: description TEXT
3. Date and Time Types
DATE: Stores a date in the format 'YYYY-MM-DD'.
Example: birth_date DATE
DATETIME: Stores a date and time value in the format 'YYYY-MM-DD HH:MM:SS'.
Example: created_at DATETIME
TIMESTAMP: Stores a timestamp value, which is a date and time value represented as the number of seconds since '1970-01-01 00:00:00' UTC. Automatically updated to the current timestamp when the row is created or updated.
Example: last_modified TIMESTAMP
4. Special Types
ENUM: An enumeration, which is a string object that can have only one value, chosen from a list of possible values. Can have up to 65,535 distinct elements.
Example: gender ENUM('Male', 'Female', 'Other')
SET: A string object that can have zero or more values, each of which must be chosen from a list of possible values. Can have up to 64 distinct members.
Example: skills SET('HTML', 'CSS', 'JavaScript')
Mysql Commands
1. CREATE DATABASE: Creating a New Database
Used to create a new database.
Syntax: CREATE DATABASE database_name;
Example: CREATE DATABASE students;
After execution, a new database named "students" will be created.
2. USE: Selecting a Database
Used to select a specific database to work with.
Syntax: USE database_name;
Example: USE students;
After execution, all subsequent commands will be executed in the "students" database.
3. SHOW DATABASES: Listing All Databases
Used to list all databases on the MySQL server.
Syntax: SHOW DATABASES;
Displays a list of all databases.
4. CREATE TABLE: Creating a New Table
Used to create a new table in the database.
Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
Example: CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary INT,
address VARCHAR(200),
age INT
);
5. DESCRIBE: Viewing Table Structure
Used to view the structure of a table.
Syntax: DESCRIBE table_name;
Example: DESCRIBE student;
Displays the columns, types, and other properties of the "student" table.
6. INSERT INTO: Adding Data to a Table
Used to insert data into a table.
Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example: INSERT INTO student (id, name, age) VALUES (1, 'John Doe', 21);
After execution, a new row will be added to the "student" table.
INSERT INTO employees (id, name, department, salary, address, age) VALUES
(1, 'John Doe', 'HR', 55000, '123 Elm St', 30),
(2, 'Jane Smith', 'Marketing', 60000, '456 Oak St', 35),
(3, 'Michael Brown', 'Sales', 45000, '789 Pine St', 28),
(4, 'Emily Davis', 'HR', 52000, '101 Maple St', 42),
(5, 'David Wilson', 'Marketing', 58000, '202 Birch St', 29),
(6, 'Sarah Johnson', 'Sales', 47000, '303 Cedar St', 31),
(7, 'Chris Lee', 'IT', 63000, '404 Walnut St', 25),
(8, 'Jessica Taylor', 'HR', 54000, NULL, 26),
(9, 'Daniel Harris', 'Marketing', 61000, '606 Chestnut St', 40),
(10, 'Laura Clark', 'Sales', 46000, '707 Spruce St', 33);
7. SELECT: Retrieving Data from a Table
Used to retrieve data from a table.
Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
Example: SELECT * FROM employees;
Displays all rows from the "student" table. The asterisk (*) can be used to select all columns.
8. UPDATE: Updating Existing Data
Used to update data in a table.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example: UPDATE employees SET age = 22 WHERE id = 1;
After execution, the age of the student with id 1 will be updated to 22.
9. DELETE: Deleting Data
Used to delete data from a table.
Syntax: DELETE FROM table_name WHERE condition;
Example: DELETE FROM employees WHERE id = 1;
After execution, the student with id 1 will be deleted from the "student" table.
10. DROP TABLE/DROP DATABASE: Deleting Tables and Databases
DROP TABLE: Used to delete a table and all of its data.
Syntax: DROP TABLE table_name;
Example: DROP TABLE student;
After execution, the "student" table will be deleted.
DROP DATABASE: Used to delete a database and all of its tables and data.
Syntax: DROP DATABASE database_name;
Example: DROP DATABASE students;
After execution, the "students" database will be deleted.
11. WHERE Clause
Used to filter records based on specified conditions.
Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
Example: SELECT * FROM employees WHERE department = 'HR';
12. AND Operator
Combines multiple conditions, and retrieves records when all conditions are met.
Syntax: SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND ...;
Example: SELECT * FROM employees WHERE department = 'HR' AND salary > 54000;
13. OR Operator
Retrieves records when at least one of the conditions is met.
Syntax: SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR ...;
Example: SELECT * FROM employees WHERE department = 'HR' OR department = 'Marketing';
14. LIKE Operator
Used for pattern matching with wildcard characters (% for zero or more characters, _ for a single character).
Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
Example: SELECT * FROM employees WHERE name LIKE 'J%'; (selects all employees whose name starts with 'J')
15. BETWEEN Operator
Used to filter the result set within a certain range.
Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example: SELECT * FROM employees WHERE age BETWEEN 20 AND 30;
16. IN Operator
Used to filter the result set based on a list of discrete values.
Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);
Example: SELECT * FROM employees WHERE department IN ('HR', 'Marketing', 'Sales');
17. NOT Operator
Used to exclude records that match the condition.
Syntax: SELECT column1, column2, ... FROM table_name WHERE NOT condition;
Example: SELECT * FROM employees WHERE NOT department = 'HR';
18. IS NULL/IS NOT NULL
Used to check if a value is null or not.
Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name IS NULL;
Example: SELECT * FROM employees WHERE address IS NULL;
Syntax for non-null: SELECT column1, column2, ... FROM table_name WHERE column_name IS NOT NULL;
19. Combining AND, OR, and NOT
You can combine these operators to create complex conditions.
Example: SELECT * FROM employees WHERE (department = 'HR' AND salary > 50000) OR (department = 'Marketing' AND salary > 60000);
20. MIN
The MIN function is used to find the smallest value in a selected column.
Syntax: SELECT MIN(column_name) FROM table_name;
Example: SELECT MIN(salary) FROM ordersemployees;
This query returns the smallest salary from the employees table.
21. MAX
The MAX function is used to find the largest value in a selected column.
Syntax: SELECT MAX(column_name) FROM table_name;
Example: SELECT MAX(salary) FROM employees;
This query returns the largest salary from the employees table.
21. SUM
The SUM function is used to calculate the total sum of a numeric column.
Syntax: SELECT SUM(column_name) FROM table_name;
Example: sql SELECT SUM(salary) FROM employees;
This query calculates the total sum of salaries for all employees.
22. AVG (Average)
The AVG function is used to calculate the average value of a numeric column.
Syntax: SELECT AVG(column_name) FROM table_name;
Example: SELECT AVG(salary) FROM employees;
This query calculates the average salary of the employees.
23. COUNT
The COUNT function is used to count the number of rows that match a specified criterion.
Syntax: SELECT COUNT(column_name) FROM table_name;
Example: SELECT COUNT(*) FROM employees;
This query counts the number of rows (employees) in the employees table.
SELECT COUNT(DISTINCT column_name) FROM table_name;
This variation of the COUNT function counts the number of distinct
24. ORDER BY Clause
Sorts the result set in ascending or descending order.
Syntax:
SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Example:
SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;
This sorts employees first by last name in ascending order, then by first name in descending order.
25. GROUP BY Clause
Groups rows with the same values in specified columns.
Syntax:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;
This counts the number of employees in each department.
26. HAVING Clause
Filters groups created by GROUP BY based on specified conditions.
Syntax:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
This lists departments with more than 10 employees.
27. JOIN (INNER JOIN) Clause
Combines rows from two or more tables based on related columns.
Syntax:
SELECT table1.column1, table2.column2, ... FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
This shows the names of employees and their respective departments.
28. LEFT JOIN (LEFT OUTER JOIN) Clause
Returns all rows from the left table and matched rows from the right table. Unmatched rows in the left table show NULL for right table columns.
Syntax:
SELECT table1.column1, table2.column2, ... FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
This includes employees who might not belong to any department.
29. RIGHT JOIN (RIGHT OUTER JOIN) Clause
Returns all rows from the right table and matched rows from the left table. Unmatched rows in the right table show NULL for left table columns.
Syntax:
SELECT table1.column1, table2.column2, ... FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
This includes all departments, even those without any employees.
30. FULL JOIN (FULL OUTER JOIN) Clause
Combines LEFT JOIN and RIGHT JOIN. Returns rows when there is a match in either left or right table.
Syntax:
SELECT table1.column1, table2.column2, ... FROM table1 FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
This shows all employees and all departments, matched or unmatched.
Create Tables
CREATE TABLE lefttable (
no INT,
name VARCHAR(255)
);
CREATE TABLE righttable (
no INT,
name VARCHAR(255)
);
INSERT INTO lefttable (no, name)
VALUES
(1, 'apple'),
(2, 'banana'),
(3, 'cherry'),
(4, 'date');
INSERT INTO righttable (no, name)
VALUES
(3, 'xylophone'),
(4, 'yacht'),
(5, 'elephant'),
(6, 'fox');
1. INNER JOIN
Returns records that have matching values in both tables.
SELECT lefttable.no, lefttable.name AS left_name, righttable.name AS right_name
FROM lefttable
INNER JOIN righttable ON lefttable.no = righttable.no;
INNER JOIN Output
2. LEFT JOIN
Returns all records from the left table, and the matched records from the right table. The result is `NULL` from the right side if there is no match.
SELECT lefttable.no, lefttable.name AS left_name, righttable.name AS right_name
FROM lefttable
LEFT JOIN righttable ON lefttable.no = righttable.no;
LEFT JOIN Output
3. RIGHT JOIN
Returns all records from the right table, and the matched records from the left table. The result is `NULL` from the left side when there is no match.
SELECT lefttable.no, lefttable.name AS left_name, righttable.name AS right_name
FROM lefttable
RIGHT JOIN righttable ON lefttable.no = righttable.no;
RIGHT JOIN Output
4. FULL OUTER JOIN
Returns all records when there is a match in either left or right table. MySQL does not support the `FULL OUTER JOIN` syntax directly. You can achieve it using a `UNION` of `LEFT JOIN` and `RIGHT JOIN`.
SELECT lefttable.no, lefttable.name AS left_name, righttable.name AS right_name
FROM lefttable
LEFT JOIN righttable ON lefttable.no = righttable.no
UNION
SELECT righttable.no, lefttable.name AS left_name, righttable.name AS right_name
FROM lefttable
RIGHT JOIN righttable ON lefttable.no = righttable.no;
FULL OUTER JOIN Output
Subqueries in SQL
Definition:
Subqueries are nested queries within a larger query. They are used inside SELECT, INSERT, UPDATE, or DELETE statements, or inside another subquery.
Subqueries can return individual values or a list of records; they're frequently used in WHERE and HAVING clauses of an outer SQL statement.
They are useful for performing operations in multiple steps and for increasing the flexibility and complexity of SQL queries.
Basic Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR
(SELECT column_name FROM table_name WHERE condition);
The inner SELECT statement is the subquery.
The outer SQL statement can be a SELECT, INSERT, UPDATE, or DELETE query.
OPERATOR can be comparison operators like =, <, >, IN, etc.
Example 1 - Subquery in WHERE Clause:
SELECT name, salary
FROM employees
WHERE salary >
(SELECT AVG(salary) FROM employees);
This query selects the name and salary of employees whose salary is greater than the average salary of all employees.
Example 2 - Subquery in SELECT Clause:
SELECT name,
(SELECT AVG(salary) FROM employees) AS average_salary
FROM employees;
This query selects the name of all employees and shows the average salary for each row.
Example 3 - Subquery in FROM Clause:
SELECT AVG(salary)
FROM
(SELECT salary FROM employees WHERE department = 'HR') AS dept_salaries;
This query calculates the average salary of employees in the HR department.
Types of Subqueries:
Single-row subquery: Returns zero or one row.
Multiple-row subquery: Returns one or more rows.
Multiple-column subquery: Returns one or more columns.
Correlated subquery: Refers to a column in the outer query. It must be re-executed for each row in the outer query.
Key Points:
Subqueries must be enclosed within parentheses.
Subqueries are executed first, and their results are passed on to the main query.
A subquery can use any comparison operator such as >, <, =, IN, etc.
Correlated subqueries can be particularly powerful but may impact performance negatively if not used judiciously.
Subqueries can be nested inside other subqueries.