1.

For Version
select version();

2. DDL - DDL stands for Data Definition Language. It's a subset of SQL
(Structured Query Language) used to define and manage the structure
of a database and its objects. DDL commands enable users to create,
modify, and delete database objects such as tables, indexes, views, and
schemas.

Create Table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department_id INT,
hire_date DATE,
salary DECIMAL(10,2),
FOREIGN KEY (department_id)
REFERENCESdepartments(department_id)
);

Contraints
CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
unique_column VARCHAR(50) UNIQUE,
foreign_key_column INT,
check_column INT,
not_null_column VARCHAR(50) NOT NULL,
FOREIGN KEY (foreign_key_column) REFERENCES other_table(id),
CHECK (check_column > 0)
);
 ALTER TABLE: Modifies an existing table structure.
ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name

MODIFY column_name datatype;

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE table_name

Change old_COLUMN_name new_column_name datatype;

alter table emp1 rename emp2;

Drop Table:
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

Truncate Table:
TRUNCATE TABLE employees;
Index
CREATE INDEX idx_last_name
ON employees (last_name);
DROP INDEX index_name;

Views and Databases

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

DROP VIEW: Deletes a view from the database.

DROP VIEW view_name;

CREATE DATABASE: Creates a new database.

CREATE DATABASE database_name;

DROP DATABASE: Deletes a database and all its

contents.

DROP DATABASE database_name;

Create Procedure
-- Create the stored procedure
DELIMITER //

CREATE PROCEDURE getEmployeeCount (

IN department_id INT, -- Input parameter for
department ID
OUT employee_count INT -- Output parameter for
employee count
)
BEGIN
-- Declare a variable to store the count
DECLARE count_result INT;

-- Get the count of employees in the specified

department
SELECT COUNT(*) INTO count_result FROM employees
WHERE department_id = department_id;

-- Assign the count to the output parameter

SET employee_count = count_result;
END //

DELIMITER ;

CALL getEmployeeCount(1, @empCount);

SELECT @empCount;
DML

Insert Querry
-- Example: Inserting multiple rows into the "employees"
table
INSERT INTO
employees (employee_id, first_name, last_name,
department_id)
VALUES
(101, 'John', 'Doe', 1),
(102, 'Jane', 'Smith', 2),
(103, 'Alice', 'Johnson', 1),
(104, 'Bob', 'Brown', 3);

Update Query
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 1;

Delete Query
DELETE FROM employees
WHERE department_id = 2;
Data Control Language (DCL):
 GRANT: Provides specific privileges to users or roles.
 REVOKE: Withdraws specific privileges granted to users or roles.

How to create a user ;

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

If you want to create a user that can connect only from a specific
host, you can specify the hostname or IP address:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Get list of user

SELECT User, Host FROM mysql.user;

Drop user
DROP USER 'username'@'hostname';

How to chnge user open bin of mysql in cmd then

Type : mysql -u username -p

Then it will ask for password and enter password

Till not specifying privilege to user the user will have

no privilege
 1. Grant
2.
3. GRANT comma_separated_privileges
4. ON database_name.table_name
5. TO 'user1'@'host1', 'user2'@'host2', ...;
6.

GRANT SELECT, INSERT ON *.* TO 'username'@'host';

GRANT CREATE USER ON *.* TO 'username'@'host' with grant option;

*.* menas all the database and all tables in it

With grant option means also give grant to other
 Privillage

3. Revoke
4. REVOKE privileges
5. ON database_name.table_name
6. FROM 'user'@'host';

Revoke all privilage

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

3. Transaction Control Language

COMMIT:
 The COMMIT command is used to save all the changes made during the current
transaction to the database.
  Once a COMMIT command is executed, the changes become permanent and
cannot be rolled back.
 It marks the successful completion of the transaction.
 Syntax:

>commit;

ROLLBACK:
 The ROLLBACK command is used to undo all the changes made during the
current transaction and revert the database to its state before the transaction
began.
 It is typically used to handle errors or to cancel the changes made if the
transaction cannot be completed successfully.
 Syntax:

>. Rollback;

SAVEPOINT:
 The SAVEPOINT command is used to set a specific point within a transaction to
which you can later roll back if needed.
 It allows you to create intermediate points within a transaction, providing
more granular control over the rollback process.
 Syntax:

 Savepoint savepoint_name;

Example

BEGIN; -- Start a transaction

-- Execute SQL statements...

SAVEPOINT sp1; -- Create a savepoint within the transaction

-- More SQL statements...

ROLLBACK TO SAVEPOINT sp1; -- Rollback to the savepoint if needed

-- More SQL statements...

COMMIT; -- Save the changes if the transaction is successful

Data Query Language

SELECT column1, column2

FROM table_name
WHERE condition
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1 ASC
LIMIT 10;

Operators:
 Arithmetic Operators: +, -, *, /, %
 Comparison Operators: =, <>, !=, >, <, >=, <=
 Logical Operators: AND, OR, NOT
 LIKE: Compares a column's value to a specified pattern using wildcard
characters (% for zero or more characters, _ for a single character).

SELECT column1 + column2 AS sum,

column1 - column2 AS difference,
column1 * column2 AS product,
column1 / column2 AS quotient,
column1 % column2 AS modulus
FROM table_name;
And and or

SELECT column1, column2

FROM table_name
WHERE column1 > 100 AND column2 <= 500;

SELECT column1, column2

FROM table_name
WHERE column1 > 100 OR (column2 < 500 AND column3 = 'value');

Between(range is inclusive)
SELECT product_name, price
FROM products
WHERE price BETWEEN 10 AND 50;

Like opeartor
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'J%' AND last_name LIKE '%s_n%';

Is null ans is not null

SELECT * FROM employees WHERE department_id IS NULL;

IN oprator
SELECT * FROM employees
WHERE department_id IN (1, 2);
Clauses

SELECT customer_id, COUNT(order_id) AS num_orders, SUM(total_amount) AS

total_spent
FROM orders
WHERE region_id = 'specific_region'
AND order_date > '2023-01-01'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
ORDER BY total_spent DESC;

Limit (index value start from 0 , number of value)

Second hight salary limit

select distinct salary as sal from employee order by sal desc limit 1,1;

Join
1. Inner join :
SELECT employees.employee_id, employees.employee_name,
departments.department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
LEFT JOIN (or LEFT OUTER JOIN):

Returns all rows from the left table (table1), and the matched rows from the
right table (table2). If there is no match, NULL values are returned.
Syntax:

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

RIGHT JOIN (or RIGHT OUTER JOIN):

Returns all rows from the right table (table2), and the matched rows from the
left table (table1). If there is no match, NULL values are returned.
Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Full outer join and union

-- Simulate FULL JOIN using LEFT JOIN, RIGHT JOIN, and UNION
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Union all(it does not remove duplicate so it is fast)

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
Intersect(it is for intersection) and except(it is complment)
It applied same as union

COUNT():
SELECT COUNT(*) AS total_rows FROM your_table;

SUM():
SELECT SUM(salary) AS total_salary FROM employees;

AVG():
SELECT AVG(age) AS average_age FROM students;

MIN():
SELECT MIN(sales_amount) AS min_sales FROM sales_data;

MAX():
SELECT MAX(price) AS max_price FROM products;

CONCAT():
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

IF(): //it will crete a new column in output which show high or low
SELECT employee_name, salary,
IF(salary > 50000, 'High', 'Low') AS salary_category
FROM employees;
Case

SELECT
student_name,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END AS grade
FROM grades;

All About select with views

Creating a View:
You can create a view using a SELECT statement. Here's the basic
syntax':

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW customer_details AS

SELECT customer_id, first_name, last_name
FROM customers
WHERE country = 'USA';
Querying a View:
Once a view is created, you can query it just like you would query a
table:

SELECT * FROM view_name;

Example:

SELECT * FROM customer_details;

Modifying a View:
Views can be modified using the ALTER VIEW statement:

ALTER VIEW view_name AS

SELECT new_column1, new_column2, ...
FROM new_table_name
WHERE new_condition;

Example:

ALTER VIEW customer_details AS

SELECT customer_id, first_name, last_name, email
FROM customers
WHERE country = 'Canada';

Dropping a View:
You can remove a view from the database using the DROP VIEW statement:

DROP VIEW view_name;

Example:

DROP VIEW customer_details;

Using Views in Joins:

Views can be used in joins just like tables. This allows you to create complex
queries based on simplified views.

SELECT *
FROM orders
JOIN customer_details ON orders.customer_id = customer_details.customer_id;
SELECT table_name AS view_name
FROM information_schema.views
WHERE table_schema = 'your_database_name';