All about Structure Query Language (SQL)

A SQL statement is a query written in Structured Query Language (SQL) that is used to interact with a database. It is used to retrieve or manipulate data stored in a database. SQL statements are composed of clauses, expressions, and predicates that specify the data that is being queried and the operations that are to be performed on that data.

SQL Statements:

Create Database:

A CREATE DATABASE statement in SQL is used to create a new database. It consists of the CREATE DATABASE keyword, followed by the name of the database. Optionally, a character set and collation can be specified. Once the database has been created, tables can be added to it.

Query:
Create Database [database name];

Create Database Inventory;

Alter Database :

If you want to change the name of your database, you can run this command to make changes:

ALTER DATABASE [olddatabase] MODIFY NAME = [newdatabase];

Alter Database Inventory Modify name = Accounts;

Remember : SQL is not a case sensitive language which means you can either write the query in lowercase or in an uppercase

Before creating the table make sure that, you are working on the same database that you want to use.

To change the database:

Create Table:

A CREATE TABLE statement in SQL is used to create a new table in a database. It consists of the CREATE TABLE keyword, followed by the name of the table and a list of column definitions. Each column definition consists of the column name, its data type, and any additional constraints. Once the table has been created, data can be added to it.

DataTypes of SQL contains:
bool, varchar, int, float, bit

Query:
Create table [table name] (
[row name] datatype validation,
[row name] datatype validation,
);

Table 01

Create table vendor(
   id int Primary key,
   name varchar(256) not null,
   email varchar(256) not null,
);

Table 02

Create table customer(
   id int Primary key,
   name varchar(256) not null,
   email varchar(256) null,
   regularity_status bit not null,
   vendor_id int not null,

Foreign Key (vendor_id) 
REFERENCES vendor(id)
);

Insert Statement :

An INSERT statement in SQL is used to add new records to a table. It consists of the INSERT keyword, followed by the table name, and a list of column names and values. For example, to insert a new row into a table called 'customers', the following statement could be used:

Query :

INSERT INTO customer (id, name, email, regularity_status) VALUES (1, 'John Doe', 'john@example.com', 1);

To insert multiple rows, no need to write it again and again. You can use like :

INSERT INTO customer (id, name, email, regularity_status) VALUES (2, 'John Doe', 'john@example.com', 1),
(3, 'John Smith', null, 0),
(4, 'Smith Doe', null, 0),
(5, 'John Kinidy', 'johkanidy@example.com', 1),
(6, 'John Mike', 'johnMike@example.com', 1),
;

Select Statement :

A SELECT statement in SQL is used to retrieve data from a table. It consists of the SELECT keyword, followed by a list of column names, and the FROM keyword, followed by the table name. For example, to retrieve all columns from a table called 'customers', the following statement could be used:

Query:
Select [column name] from [table name];

SELECT * FROM customer;
SELECT name, email from customer;

Update Statement :

An UPDATE statement in SQL is used to modify existing records in a table. It consists of the UPDATE keyword, followed by the table name, and a list of column names and values. For example, to update the email address of a customer with the ID of 1, the following statement could be used:

UPDATE customer SET email = 'newemail@example.com' WHERE id = 1;

Delete Statement :

A DELETE statement in SQL is used to delete records from a table. It consists of the DELETE keyword, followed by the table name, and a WHERE clause that specifies the records to be deleted. For example, to delete all customers with the ID of 1, the following statement could be used:

DELETE FROM customer WHERE id = 1;

Distinct Keyword :

The DISTINCT keyword in SQL is used to return only unique values in a result set. It is typically used in conjunction with the SELECT statement, followed by the column names that should be included in the result set. For example, to return only unique names from a table called 'customers', the following statement could be used:

SELECT DISTINCT name FROM customer;

Where Keyword :

The WHERE clause in SQL is used to filter records in a result set. It is typically used in conjunction with the SELECT statement, followed by a condition that must be met for a record to be included in the result set. For example, to return only customers with the ID of 1, the following statement could be used:

SELECT * FROM customer WHERE id = 1;

And, OR, NOT Keyword :

AND, OR, and NOT are logical operators in SQL that are used to combine multiple conditions in a WHERE clause. AND requires that both conditions be true for a record to be included in the result set, OR requires that one of the conditions be true, and NOT requires that the condition be false. For example, to return only customers with the ID of 1 or 2, the following statement could be used:

SELECT * FROM customer WHERE id = 1 OR id = 2;
SELECT * FROM customer WHERE id = 1 and email = 'newemail@example.com';

NULL Function :

The NULL keyword in SQL is used to represent a missing or unknown value. It is typically used in conjunction with the WHERE clause, followed by an operator such as IS NULL or IS NOT NULL. For example, to return only customers with a NULL email address, the following statement could be used:

SELECT * FROM customer WHERE email IS NULL;

Like Keyword :

The LIKE operator in SQL is used to compare a column value to a pattern. It is typically used in conjunction with the WHERE clause, followed by a string pattern that can include wildcards. For example, to return only customers whose name starts with 'John', the following statement could be used:

SELECT * FROM customer WHERE name LIKE 'John%';

Like statement have two wildcard % and _ When we want to retrieve all the letters before this specific word then we use % before the specific word. It could be like:

SELECT * FROM customer WHERE name LIKE '%Doe';

Similarly, When we want to retrieve a word that contains oe but that must have only one letter before this specific word then we use:

SELECT * FROM customer WHERE name LIKE '_oe';

Top Keyword :

The TOP keyword in SQL is used to limit the number of rows returned in a result set. It is typically used in conjunction with the SELECT statement, followed by a number that indicates how many rows should be returned. For example, to return the top 10 customers, the following statement could be used:

SELECT TOP 10 * FROM customer;

Aggregate functions :
count(), add(), avg()

The COUNT, SUM, and AVG functions in SQL are used to perform aggregate calculations on a set of records. COUNT returns the number of records in a result set, SUM adds up the values of a specified column, and AVG calculates the average of a specified column. For example, to calculate the average age of customers, the following statement could be used:

SELECT AVG(age) FROM customer;

Similarly, to calculate the sum of customers, the following statement could be used:

SELECT Sum(regularity_status) FROM customer;

To calculate the count of customers, the following statement could be used:

SELECT Count(name) FROM customer;

Min, Max functions :

The MIN and MAX functions in SQL are used to find the minimum and maximum values of a specified column. They are typically used in conjunction with the SELECT statement, followed by the name of the column. For example, to find the oldest and youngest customers, the following statements could be used:

SELECT MIN(age) FROM customer;

SELECT MAX(age) FROM customer;

Between keyword :

The BETWEEN operator in SQL is used to filter records that fall within a specified range. It is typically used in conjunction with the WHERE clause, followed by two values that indicate the start and end of the range. For example, to return only customers between the ages of 18 and 30, the following statement could be used:

SELECT * FROM customers WHERE age BETWEEN 18 AND 30;

Alias keyword :

An alias in SQL is used to give a column or table a temporary name. It is typically used in conjunction with the SELECT statement, followed by the name of the column or table and the desired alias. For example, to give the customers table the alias 'c', the following statement could be used:

SELECT name as Customer_Name FROM customer;

SELECT c.name FROM customer as c;

UNION, INTERSECT, and EXCEPT Operators :

The UNION, INTERSECT, and EXCEPT operators in SQL are used to combine the results of multiple SELECT statements. UNION combines the results of two SELECT statements, INTERSECT returns only the records found in both SELECT statements, and EXCEPT returns only the records found in the first SELECT statement but not the second. For example, to combine the results of two SELECT statements, the following statement could be used:

SELECT * FROM customer UNION SELECT * FROM vendor;

The UNION ALL operator in SQL is used to combine the results of two SELECT statements. It is similar to the UNION operator, except that it does not remove duplicate records from the result set. For example, to combine the results of two SELECT statements, the following statement could be used:

SELECT * FROM customers1 UNION ALL SELECT * FROM customers2;

The above statement will combine the results of the two SELECT statements and return all records, including any duplicates.

SELECT * FROM customer INTERSECT SELECT * FROM vendor;

SELECT * FROM customer EXCEPT SELECT * FROM vendor;

ORDER BY clause :

The ORDER BY clause in SQL is used to sort the records in a result set. It is typically used in conjunction with the SELECT statement, followed by the name of the column that should be used for sorting. For example, to sort customers by their age, the following statement could be used:

SELECT * FROM customer ORDER BY age;

Here, we can also sort the result based on ascending or descending order by just add a keyword asc or desc :

SELECT * FROM customer ORDER BY name desc;

By default, order by can get result in ascending order.

IN operator :

The IN operator in SQL is used to check if a value is present in a list of values. It is typically used in conjunction with the WHERE clause, followed by a list of comma-separated values. For example, to check if a customer's age is 18, 21, or 25, the following statement could be used:

SELECT * FROM customer WHERE age IN (18, 21, 25);

IS operator :

The IS operator in SQL is used to compare a value to another value or to a known constant. It is typically used in conjunction with the WHERE clause, followed by the value to be compared and the value or constant it should be compared to. For example, to check if a customer's age is equal to 18, the following statement could be used:

SELECT * FROM customers WHERE age IS 18;

Group by clause :

The GROUP BY clause in SQL is used to group records together based on a specified column or expression. It is typically used in conjunction with the SELECT statement, followed by the name of the column or expression to be used for grouping. For example, to group customers by their age, the following statement could be used:

SELECT name, count(name) from customer group by name;

HAVING clause :

The HAVING clause in SQL is used to filter records in a result set after they have been grouped. It is typically used in conjunction with the GROUP BY clause, followed by a condition that must be met for a record to be included in the result set. For example, to return only groups of customers where the average age is greater than 18, the following statement could be used:

SELECT name, count(name) as Similar_names from customer group by name having count(name) > 2;

Case clause :

The CASE statement in SQL is used to create conditional logic in a query. It is typically used in conjunction with the SELECT statement, followed by a condition and the value to be returned if the condition is met. For example, to assign a letter grade to each customer based on their age, the following statement could be used:

SELECT *, 
CASE WHEN age < 18 THEN 'Teen' 
WHEN age BETWEEN 18 AND 25 THEN 'Young Adulthood' 
WHEN age > 25 THEN 'Adulthood' 
END AS Adolescence 
FROM customer;

Joins :

A JOIN statement in SQL is used to combine data from two or more tables based on a common field. It typically consists of the names of two or more tables, followed by an ON clause that specifies the join conditions. For example, to join the customers and orders tables on the customer_id field, the following statement could be used:

SELECT * FROM customer 
JOIN orders 
ON customer.vendor_id = vendor.id;

Types of SQL Joins:

There are several types of JOINs in SQL, each of which determines how the joined tables are related and what data is returned.

• INNER JOIN: This type of JOIN returns only rows from both tables that match the join conditions.
• LEFT JOIN: This type of JOIN returns all rows from the left table, and only the rows from the right table that match the join conditions.
• RIGHT JOIN: This type of JOIN returns all rows from the right table, and only the rows from the left table that match the join conditions.
• FULL OUTER JOIN: This type of JOIN returns all rows from both tables, regardless of whether they match the join conditions or not.

Other types of JOINs in SQL include:

•** CROSS JOIN:** This type of JOIN returns all possible combinations of rows from both tables, regardless of whether they match the join conditions or not.
• SELF JOIN: This type of JOIN is used to join a table to itself. It is typically used to compare values in a single table.
• NATURAL JOIN: This type of JOIN joins two tables based on columns with the same name in each table.
• UNION JOIN: This type of JOIN combines the results of two SELECT statements into a single result set.