MySQL Check (Sample Notebook for preview md: Its a file from jupytext synched Myst markdow with same name ipynb notebook)
A tutorial of SQL Basic Scripting.
- toc: true
- badges: true
- comments: true
- categories: [SQL]
Resources:
- https://www.educba.com/data-science/data-science-tutorials/mysql-tutorial/
- https://www.w3schools.com/mysql/default.asp
;
USE world* from city limit 5; SELECT
Commands completed successfully
(5 row(s) affected)
Total execution time: 00:00:00.016
ID | Name | CountryCode | District | Population |
---|---|---|---|---|
1 | Kabul | AFG | Kabol | 1780000 |
2 | Qandahar | AFG | Qandahar | 237500 |
3 | Herat | AFG | Herat | 186800 |
4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
5 | Amsterdam | NLD | Noord-Holland | 731200 |
# LECTURE: Creating a Database - Part I
;
CREATE DATABASE IF NOT EXISTS Sales
; CREATE SCHEMA IF NOT EXISTS Sales
Commands completed successfully
Commands completed successfully
Total execution time: 00:00:00.019
; USE Sales
Commands completed successfully
Total execution time: 00:00:00.007
CREATE TABLE sales
(
purchase_number INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
date_of_purchase DATE NOT NULL,
customer_id INT,10) NOT NULL
item_code VARCHAR(
; )
Total execution time: 00:00:00.012
MYSQL:
• Data base concepts(Database, DBMS, RDMS)and Workbench
• Installing MYSQL server
• Select Command
• Distinct Keywordis not Case Sensitive
• SQL
• Where Clausein where clause condition
• Using Relational operators
• Using logical operators(AND, OR, NOT)
• Between Operator
• Order by Clause(ASC, DESC)with Text
• Using Between operator
• In operatorand wildcard characters
• Like operator for table column names
• Aliases
• Limit keyword
• Breaking the lengthy SQL statement into Multiple linesin functions
• My SQL Built
• Arithmetic Operatorsand using databases
• Creating, viewing, describing, deleting and inserting records into the table
• Creating tables
• Data Typesis null and is not null
• Null values,
• Delete statementand set keyword
• Update statement and To keyword
• Rename statement and drop column
• Alter statement ,Add ,Modify column ,rename
• Set auto commit
• Commit statement
• Rollback statement
• Truncate statementand multiline comments
• Single line
• Group by clause
• Having Clauseall, intersect and minus operator)
• Set operators(union, union self join)
• Joins(inner join, left join, right join, full join,
• Sub Query(Types of sub query)and foreign key, check, default)
• Integrity Constraints(Not null, unique, primary
• Auto increment
• Insert into and end keyword
• Case, when ,then
• Delimiter
• Views
• Indexes
• Proceeduresand Practice assignments on my SQL
• Any Doubts and query for each step i have entered in the above points one by one till end point in MYSQL language for a novice person with simple queries and easy to understandable definition and small explanation to all above without excluding any?
ca you please explain definition 'd be happy to help! Here are the definitions and queries for each of the topics you've listed:
Sure, I
Database Concepts:is an organized collection of data, while a DBMS (Database Management System) is software that manages the database. An RDBMS (Relational Database Management System) is a type of DBMS that stores data in tables that are related to each other by primary and foreign keys.
A database and Workbench:
Installing MYSQL server from the official MYSQL website and follow the installation wizard. MYSQL Workbench is a graphical user interface for managing databases and can also be downloaded from the official MYSQL website.
To install MYSQL server, you can download it
Select Command:is used to retrieve data from a database. For example, to select all columns from a table called "users", you would use the following query:
The SELECT statement
sql
Copy code* FROM users;
SELECT
Distinct Keyword:is used to select unique values from a column. For example, to select unique values from a column called "city" in a table called "customers", you would use the following query:
The DISTINCT keyword
sql
Copy code;
SELECT DISTINCT city FROM customersis not Case Sensitive:
SQL is not case sensitive, which means that keywords such as SELECT and select are equivalent.
SQL
Where Clause:is used to filter the results of a SELECT statement based on a specified condition. For example, to select all records from a table called "orders" where the "status" column is equal to "shipped", you would use the following query:
The WHERE clause
sql
Copy code* FROM orders WHERE status = 'shipped';
SELECT in Where Clause Condition:
Using Relational Operators as =, <, >, <=, >=, and <> can be used in the WHERE clause to compare values. For example, to select all records from a table called "products" where the "price" column is greater than 10, you would use the following query:
Relational operators such
sql
Copy code* FROM products WHERE price > 10;
SELECT
Using Logical Operators (AND, OR, NOT):as AND, OR, and NOT can be used in the WHERE clause to combine conditions. For example, to select all records from a table called "orders" where the "status" column is equal to "shipped" and the "total" column is greater than 100, you would use the following query:
Logical operators such
sql
Copy code* FROM orders WHERE status = 'shipped' AND total > 100;
SELECT
Between Operator:is used in the WHERE clause to select values within a range. For example, to select all records from a table called "sales" where the "date" column is between January 1, 2022 and March 31, 2022, you would use the following query:
The BETWEEN operator
sql
Copy code* FROM sales WHERE date BETWEEN '2022-01-01' AND '2022-03-31';
SELECT
Order by Clause (ASC, DESC):is used to sort the results of a SELECT statement in ascending (ASC) or descending (DESC) order. For example, to select all records from a table called "customers" and order them by last name in descending order, you would use the following query:
The ORDER BY clause
sql
Copy code* FROM customers ORDER BY last_name DESC;
SELECT with Text:
Using Between Operator with text values. For example, to select all records from a table called "employees" where the "name" column is between "Alice" and "Bob", you would use the following query:
The BETWEEN operator can also be used
sql
Copy code* FROM employees WHERE name
SELECT
Sumendar11th point, could you please continue with remaining steps from below for the same MySQL for a beginner to understand definition and queries of each below:
you ended at
In operatorand wildcard characters
• Like operator for table column names
• Aliases
• Limit keyword
• Breaking the lengthy SQL statement into Multiple linesin functions
• My SQL Built
• Arithmetic Operatorsand using databases
• Creating, viewing, describing, deleting and inserting records into the table
• Creating tables
• Data Typesis null and is not null
• Null values,
• Delete statementand set keyword
• Update statement and To keyword
• Rename statement and drop column
• Alter statement ,Add ,Modify column ,rename
• Set auto commit
• Commit statement
• Rollback statement
• Truncate statementand multiline comments
• Single line
• Group by clause
• Having Clauseall, intersect and minus operator)
• Set operators(union, union self join)
• Joins(inner join, left join, right join, full join,
• Sub Query(Types of sub query)and foreign key, check, default)
• Integrity Constraints(Not null, unique, primary
• Auto increment
• Insert into and end keyword
• Case, when ,then
• Delimiter
• Views
• Indexes
• Proceeduresand Practice assignments on my SQL
• Any Doubts for missing that. Here are explanations and examples for the remaining points:
Sure, I apologize
In operator:in a WHERE clause. It is used to check whether a value is included in a list of values or not.
The IN operator allows you to specify multiple values all employees whose department is either 'HR' or 'IT', we can use the following query:
Example: To retrieve the details of
sql
Copy code* FROM employees WHERE department IN ('HR', 'IT');
SELECT and wildcard characters:
Like operator is used to search for patterns in columns. It is often used with wildcard characters '%' and ''. The '%' character matches zero or more characters, while '' matches exactly one character.
The LIKE operator all employees whose name starts with 'J', we can use the following query:
Example: To retrieve the details of
sql
Copy code* FROM employees WHERE name LIKE 'J%';
SELECT for table column names:
Aliases is a temporary name assigned to a table or column. It can make queries more readable and understandable.
An alias all employees along with their salaries, we can use the following query with aliases:
Example: To retrieve the details of
vbnet
Copy code;
SELECT name AS EmployeeName, salary AS EmployeeSalary FROM employees
Limit keyword:is used to limit the number of records returned in a query result.
The LIMIT keyword
Example: To retrieve the details of the first five employees, we can use the following query:
sql
Copy code* FROM employees LIMIT 5;
SELECT
Breaking the lengthy SQL statement into Multiple lines:
A lengthy SQL statement can be broken into multiple lines to improve its readability.all employees whose age is greater than or equal to 25 and salary is greater than or equal to 50000, we can use the following query with line breaks:
Example: To retrieve the details of
sql
Copy code*
SELECT
FROM employees >= 25
WHERE age >= 50000;
AND salary -in functions:
My SQL Built-in functions to manipulate data. Some common ones include COUNT(), SUM(), AVG(), MAX(), MIN(), etc.
MySQL provides a number of builtin the employees table, we can use the COUNT() function:
Example: To retrieve the total number of employees
sql
Copy code*) FROM employees;
SELECT COUNT(
Arithmetic Operators:as +, -, *, /, %.
MySQL supports arithmetic operators such all employees whose salary is more than twice their age, we can use the following query:
Example: To retrieve the details of
sql
Copy code* FROM employees WHERE salary > 2 * age;
SELECT and using databases:
Creating, viewing, describing, deleting is a collection of tables and other objects that are used to store and manage data. MySQL provides various commands to create, view, describe, delete and use databases.
A database 'mydatabase', we can use the following query:
Example: To create a new database named
sql
Copy code;
CREATE DATABASE mydatabaseand inserting records into the table:
Creating tables is a collection of data that is organized in a specific format. MySQL provides commands to create tables and insert records into the tables.
A table 'employees' with columns 'name', 'age', 'department' and 'salary', we can use the following query:
Example: To create a new table named
sql
Copy code
CREATE TABLE employees (50),
name VARCHAR(
age INT,50),
department VARCHAR(10,2)
salary DECIMAL(;
)
To insert a record into the employees table, we can use the following query:
sql
Copy code'John Doe', 30, 'IT', 60000);
INSERT INTO employees (name, age, department, salary) VALUES (
Data Types:type of data
Data types specify the
Sumendarin same mysql with easy definition and queries
you have been missed remaining can you explain below steps till last point is null and is not null
Null values,
• Delete statementand set keyword
• Update statement and To keyword
• Rename statement and drop column
• Alter statement ,Add ,Modify column ,rename
• Set auto commit
• Commit statement
• Rollback statement
• Truncate statementand multiline comments
• Single line
• Group by clause
• Having Clauseall, intersect and minus operator)
• Set operators(union, union self join)
• Joins(inner join, left join, right join, full join,
• Sub Query(Types of sub query)and foreign key, check, default)
• Integrity Constraints(Not null, unique, primary
• Auto increment
• Insert into and end keyword
• Case, when ,then
• Delimiter
• Views
• Indexes
• Proceeduresand Practice assignments on my SQL
• Any Doubts for the mistake earlier. I will continue from where I left off:
Sure, I apologize is null and is not null:
Null values, or unknown value. The IS NULL and IS NOT NULL operators are used to check for null values in a column.
In SQL, a null value represents a missing all rows where a column is null, you can use the IS NULL operator like this:
To select
sql
Copy code* FROM my_table WHERE my_column IS NULL;
SELECT all rows where a column is not null, you can use the IS NOT NULL operator like this:
To select
sql
Copy code* FROM my_table WHERE my_column IS NOT NULL;
SELECT
Delete statement:is used to delete one or more rows from a table. The basic syntax for the DELETE statement is as follows:
The DELETE statement
sql
Copy code;
DELETE FROM table_name WHERE conditionall rows from a table where the age column is greater than 30, you would use the following statement:
For example, to delete
sql
Copy code> 30;
DELETE FROM my_table WHERE age and set keyword:
Update statement is used to modify one or more columns in one or more rows in a table. The basic syntax for the UPDATE statement is as follows:
The UPDATE statement
sql
Copy code= value1, column2 = value2, ... WHERE condition;
UPDATE table_name SET column1 30 for all rows where the name column is 'John', you would use the following statement:
For example, to update the age column to
sql
Copy code= 30 WHERE name = 'John';
UPDATE my_table SET age and To keyword:
Rename statement is used to rename a table or a column in a table. The basic syntax for the RENAME statement is as follows:
The RENAME statement
css
Copy code;
ALTER TABLE table_name RENAME TO new_table_name
For example, to rename a table called old_table to new_table, you would use the following statement:
css
Copy code;
ALTER TABLE old_table RENAME TO new_tablein a table, you would use the following statement:
To rename a column
sql
Copy code;
ALTER TABLE my_table RENAME COLUMN old_column_name TO new_column_nameand drop column:
Alter statement, Add, Modify column, rename is used to modify the structure of a table, such as adding or modifying columns, renaming columns, or dropping columns.
The ALTER statement
To add a new column to a table, you would use the following statement:
sql
Copy code;
ALTER TABLE my_table ADD new_column_name data_typetype of an existing column, you would use the following statement:
To modify the data
sql
Copy code;
ALTER TABLE my_table MODIFY column_name new_data_typein a table, you would use the following statement:
To rename a column
sql
Copy code;
ALTER TABLE my_table RENAME COLUMN old_column_name TO new_column_namefrom a table, you would use the following statement:
To drop a column
sql
Copy code;
ALTER TABLE my_table DROP column_name
Set auto commit:-commit is a feature in MySQL that automatically commits the current transaction after each SQL statement. To enable auto-commit, you can use the following statement:
Auto
java
Copy code= 1;
SET autocommit -commit, you can use the following statement:
To disable auto
java
Copy code= 0;
SET autocommit
Commit statement:is used to permanently save the changes made to a database.
The COMMIT statement
sql
Copy code;
COMMIT
Rollback statement:is used to undo changes made to a database in the current transaction.
The ROLLBACK statement
sql
Copy code;
ROLLBACK
Truncate statement:is used to remove all
The TRUNCATE statement
Sumendarall points below?
can you complete remaining till end of and multiline comments
• Single line
• Group by clause
• Having Clauseall, intersect and minus operator)
• Set operators(union, union self join)
• Joins(inner join, left join, right join, full join,
• Sub Query(Types of sub query)and foreign key, check, default)
• Integrity Constraints(Not null, unique, primary
• Auto increment
• Insert into and end keyword
• Case, when ,then
• Delimiter
• Views
• Indexes
• Proceedurescontinue with the remaining points. Here are the explanations and examples for each:
Sure, I can and Multiline Comments:
Single Line in SQL are used to write a description or explanation of the code, which makes it easier for others to understand the code. There are two types of comments in SQL: single-line comments and multiline comments.
Comments -line comments start with two hyphens (--). Anything that follows after the double hyphen is ignored by the SQL engine. For example:
Single
sql
Copy code-- This is a single-line comment
* FROM mytable;
SELECT with a forward-slash and an asterisk (/) and end with an asterisk and a forward-slash (/). Anything that is enclosed between these two characters is ignored by the SQL engine. For example:
Multiline comments start
sql
Copy code/*
is a multiline comment.
This
It can span multiple lines.*/
* FROM mytable;
SELECT
Group By Clause:is used with aggregate functions like SUM, COUNT, MAX, MIN, and AVG to group the result-set by one or more columns. This clause is used in a SELECT statement and is typically used to summarize data by grouping related records together. For example:
The GROUP BY clause
sql
Copy code;
SELECT department, SUM(salary) FROM employees GROUP BY departmentand the total salary of all employees in each department. The GROUP BY clause groups the result by department.
In this example, we are selecting the department
Having Clause:is used to filter the results of a GROUP BY clause. It allows you to specify a condition that must be met for a group of records to be included in the result. For example:
The HAVING clause
sql
Copy code> 100000;
SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) and the total salary of all employees in each department. The HAVING clause filters the results to only show departments with a total salary greater than 100000.
In this example, we are selecting the department and MINUS):
Set Operators (UNION, UNION ALL, INTERSECT, or more SELECT statements. There are four set operators in SQL: UNION, UNION ALL, INTERSECT, and MINUS.
Set operators are used to combine the results of two and removes duplicate rows.
UNION: combines the results of two SELECT statements and includes all rows, including duplicates.
UNION ALL: combines the results of two SELECT statements
INTERSECT: returns only the rows that are common to the results of two SELECT statements.from the first SELECT statement that are not in the second SELECT statement.
MINUS: returns the rows
For example:
sql
Copy code;
SELECT column1 FROM table1 UNION SELECT column1 FROM table2from two different tables.
This example combines the results of two SELECT statements that both select column1
Joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN):from two or more tables based on a related column between them. There are different types of joins in SQL, including:
Joins are used to combine rows from both tables.
INNER JOIN: returns only the matching rows all the rows from the left table and matching rows from the right table.
LEFT JOIN: returns all the rows from the right table and matching rows from the left table.
RIGHT JOIN: returns all the rows from both tables, including any non-matching rows.
FULL JOIN: returns in which a table is joined with itself.
SELF JOIN: a join
For example:
sql
Copy code= customers.customer_id;
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id and customer name from the orders and customers tables, where the customer ID in the
This example selects the order ID
Sumendaras too till end in the same way?
can you please conclude remaining below
Sub Query(Types of sub query)and foreign key, check, default)
• Integrity Constraints(Not null, unique, primary
• Auto increment
• Insert into and end keyword
• Case, when ,then
• Delimiter
• Views
• Indexes
• Proceedures
Sub Query(Types of subquery):as an inner query, is a query that is nested inside another query. It is used to retrieve data that will be used as part of the criteria in the main query. There are two types of subqueries:
A subquery, also known -row subquery: This type of subquery is used to retrieve a single value, which is used as part of the criteria in the main query.
Single
Example:*
SELECT
FROM employees> (SELECT AVG(salary) FROM employees);
WHERE salary all employees, which is then used as part of the criteria in the main query to retrieve all employees with a salary greater than the average.
In this example, the subquery retrieves the average salary of -row subquery: This type of subquery is used to retrieve multiple rows, which are then used as part of the criteria in the main query.
Multiple
Example:*
SELECT
FROM employees= 1700);
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id all department IDs from the departments table that have a location ID of 1700, which is then used as part of the criteria in the main query to retrieve all employees who work in those departments.
In this example, the subquery retrieves and foreign key, check, default):
Integrity Constraints(Not null, unique, primary and consistency in the database. They are rules that must be followed by the data in the database.
Integrity constraints are used to ensure data accuracy
NOT NULL: This constraint ensures that a column cannot have a null value.
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50) NOT NULL,
name VARCHAR(
age INT NOT NULL;
)in a column is unique.
UNIQUE: This constraint ensures that each value
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50) UNIQUE
email VARCHAR(;
)in a table is uniquely identified by a specific column or columns.
PRIMARY KEY: This constraint ensures that each row
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50),
name VARCHAR(
age INT;
)in a column of one table match the values in a column of another table.
FOREIGN KEY: This constraint ensures that the values
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50),
name VARCHAR(
department_id INT,id)
FOREIGN KEY (department_id) REFERENCES departments(;
)in a column meet a specific condition.
CHECK: This constraint ensures that the values
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50),
name VARCHAR(
age INT,>= 18)
CONSTRAINT age_check CHECK (age ;
)for a column if no value is specified.
DEFAULT: This constraint sets a default value
Example:
CREATE TABLE employees (id INT PRIMARY KEY,
50),
name VARCHAR(18
age INT DEFAULT ;
)
Auto Increment:is a feature that automatically increments the value of a column by 1 each time a new row is inserted into a table. It is commonly used for primary key columns to ensure that each row has a unique identifier.
Auto increment
Example:
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,
50),
name VARCHAR(
age INT;
)
Insert into:is used to insert new rows into a table.
The INSERT INTO statement
Example:'John', 25);
INSERT INTO employees (name, age) VALUES (with the values 'John' and 25 in the name and age columns, respectively.
This statement inserts a new row into the employees table and End Keyword:
Case, When, Then, is used to conditionally execute a set of statements based on a specific condition. It has the following syntax:
The CASE statement
CASE WHEN condition1 THEN