Comprehensive Guide To SQL (Structured Query Language)
What is SQL
2. MySQL Installation
MySQL is a popular relational database management system that uses SQL for managing databases. Here's a general guide for installation:
Once installed, you're ready to interact with the MySQL database using SQL commands.
3. Types of SQL Commands
SQL commands are categorized into different types, based on their functionality:
These commands provide full control over the database, allowing you to create, modify, and secure data.
4. Databases and Related SQL Commands
5. Tables and Related SQL Commands
Tables are where the actual data is stored in a relational database.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
DROP TABLE employees;
6. Building a Database – From Tables to Data Insertion
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'HR', 60000.00);
SELECT * FROM employees;
7. Datatypes in SQL
SQL provides different types of data that can be stored in a table:
These datatypes ensure that the data entered into the tables are valid and consistent.
8. Keys in SQL
Keys are essential in SQL for identifying rows in tables and creating relationships between tables.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50)
);
9. Constraints in SQL
Constraints enforce rules on data in the table.
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10, 2) CHECK(salary > 0) );
10. Foreign Key in SQL
A Foreign Key is used to create a relationship between two tables by linking a column from one table to the primary key of another table.
Example:
CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) ); CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
Lets study more About DML
DML (Data Manipulation Language):
These commands are used for managing data within tables. They are used to retrieve, insert, update, and delete data.
SELECT * FROM employees;
Copy code
SELECT * FROM employees;
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'HR', 50000);
UPDATE employees
SET salary = 55000
WHERE id = 1;
DELETE FROM employees
WHERE id = 1;
2. DDL (Data Definition Language):
These commands are used to define and manage the structure of database objects like tables, indexes, and views.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
ALTER TABLE employees
ADD email VARCHAR(100);
DROP TABLE employees;
TRUNCATE TABLE employees;
3. DCL (Data Control Language):
These commands are used to control access to data within the database, ensuring only authorized users can perform specific actions.
GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';
REVOKE SELECT ON employees FROM 'user1'@'localhost';
4. TCL (Transaction Control Language):
TCL commands handle transactions within a database. A transaction is a sequence of operations performed as a single logical unit of work.
INSERT INTO employees (id, name, department, salary)
VALUES (2, 'Bob', 'Finance', 60000);
COMMIT;
INSERT INTO employees (id, name, department, salary)
VALUES (3, 'Eve', 'IT', 70000);
ROLLBACK;
SAVEPOINT beforeUpdate;
UPDATE employees
SET salary = 65000
WHERE id = 2;
ROLLBACK TO beforeUpdate;
Summary:
These SQL commands provide comprehensive control over data and the structure of a relational database.
we'll cover SQL queries and clauses, focusing on commands that allow you to manipulate and retrieve data from tables.
1. UPDATE Command in SQL
The UPDATE command modifies existing records in a table.
UPDATE employees SET salary = 55000 WHERE id = 2;
This command updates the salary of the employee with id = 2 to 55000. If you omit the WHERE clause, all rows in the table will be updated.
2. DELETE Command in SQL
The DELETE command removes rows from a table based on a specified condition.
Example:
DELETE FROM employees WHERE id = 3;
This deletes the employee with id = 3. Again, without the WHERE clause, all rows will be deleted, though the table structure will remain.
3. SELECT Command in SQL
The SELECT command is used to retrieve data from one or more tables.
You can also retrieve all columns using:
SELECT * FROM employees;
SELECT name, department
FROM employees
WHERE salary > 50000;
This retrieves the names and departments of employees whose salary is greater than 50000.
4. WHERE Clause in SQL
The WHERE clause filters records, allowing you to specify conditions for selecting, updating, or deleting records.
SELECT * FROM employees WHERE department = 'IT';
This retrieves all employees from the IT department.
You can also combine conditions using logical operators:
Example using AND and OR:
SELECT * FROM employees
WHERE department = 'IT' AND salary > 50000;
This retrieves employees who are in the IT department and have a salary greater than 50,000.
5. ALTER Command in SQL
The ALTER command is used to modify the structure of an existing table.
ALTER TABLE employees
ADD phone_number VARCHAR(15);
ALTER TABLE employees
MODIFY salary DECIMAL(12, 2);
ALTER TABLE employees
DROP COLUMN phone_number;
6. RENAME Command in SQL
The RENAME command is used to rename a table or a column.
领英推荐
ALTER TABLE employees
DROP COLUMN phone_number;
ALTER TABLE employees
DROP COLUMN phone_number;
7. TRUNCATE Command in SQL
The TRUNCATE command removes all rows from a table, but unlike DELETE, it doesn't log each deleted row, making it faster. The table structure is preserved.
ALTER TABLE employees
DROP COLUMN phone_number;
8. Difference Between TRUNCATE, DELETE, & DROP in SQL
TRUNCATE TABLE employees;
DELETE FROM employees WHERE id = 2;
DROP TABLE employees;
9. DISTINCT Keyword in SQL
The DISTINCT keyword is used to return only unique values, filtering out duplicates.
SELECT DISTINCT department
FROM employees;
10. Operators in SQL
SQL uses various operators to filter and compare data. Here are the most common types:
SELECT salary + 1000
FROM employees
WHERE id = 2;
SELECT * FROM employees
WHERE salary >= 50000;
SELECT * FROM employees
WHERE department = 'HR' AND salary > 50000;
11. Clauses in SQL
SQL clauses are used to specify conditions and organize query results.
SELECT * FROM employees
ORDER BY salary DESC;
SELECT * FROM employees
LIMIT 5;
Summary
These are the building blocks of SQL that will help you effectively manipulate and retrieve data from your database. Experiment with these commands to get comfortable using them in different scenarios.
we’ll cover more advanced SQL concepts, including aggregate functions, joins, and sub queries.
These are essential for performing more complex queries on databases.
1. Aggregate Functions in SQL
Here are the most commonly used aggregate functions:
SELECT COUNT(*)
FROM employees;
SELECT SUM(salary)
FROM employees
WHERE department = 'IT';
SELECT AVG(salary)
FROM employees;
SELECT MIN(salary)
FROM employees;
SELECT MAX(salary)
FROM employees;
2. GROUP BY and HAVING Clauses in SQL
The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like “total salary by department.” The HAVING clause is used to filter records after the GROUP BY.
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;
3. Difference Between HAVING and WHERE Clause in SQL
SELECT * FROM employees WHERE salary > 50000;
SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;
4. Practice Questions on Clauses and Aggregate Functions
Here are some practice questions to solidify your understanding:
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
SELECT AVG(salary)
FROM employees
WHERE department = 'IT';
SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000;
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
5. General Order of SQL Commands
When writing SQL queries, commands follow a specific order of execution:
SELECT department, AVG(salary) FROM employees WHERE salary > 30000 GROUP BY department HAVING AVG(salary) > 50000 ORDER BY AVG(salary) DESC;
6. Joins in SQL
SQL JOIN clauses are used to combine rows from two or more tables based on a related column.
Types of Joins:
a. INNER JOIN:
Returns only the rows where there is a match in both tables.
SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
This returns employees and their corresponding department names, but only for employees who have a matching department.
b. LEFT (OUTER) JOIN:
Returns all rows from the left table, and matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.
SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
c. RIGHT (OUTER) JOIN:
Returns all rows from the right table, and matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.
This returns all departments, including those with no employees.
SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
d. FULL (OUTER) JOIN:
Returns rows when there is a match in either left or right table. Non-matching rows will have NULL values for columns where there is no match.
Example:
This returns all employees and all departments, with NULL values where there is no match.
SELECT employees.name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
7. Subqueries in SQL
A subquery is a query within another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
This retrieves the names of employees whose salary is greater than the average salary of all employees.
8. Nth Highest Salary in SQL
Finding the Nth highest salary in SQL can be done using LIMIT or OFFSET in a subquery.
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Summary
These concepts help you perform complex queries and join data across multiple tables efficiently. Practice writing queries using these commands to strengthen your understanding.